//private MySql.Data.MySqlClient.MySqlConnection conn;
/*public DepartmentPersistence()
* {
* string feashConn;
* feashConn = "server=localhost;port=3306;database=fea_starhub;username=root;password=135246;";
* try
* {
* conn = new MySql.Data.MySqlClient.MySqlConnection();
* conn.ConnectionString = feashConn;
* conn.Open();
* }
* catch (MySql.Data.MySqlClient.MySqlException ex)
* { throw ex; }
* }*/
public ArrayList allDepts()
{
MySql.Data.MySqlClient.MySqlConnection conn;
string feashConn;
feashConn = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;
conn = new MySql.Data.MySqlClient.MySqlConnection();
try
{
conn.ConnectionString = feashConn;
conn.Open();
ArrayList d = new ArrayList();
MySql.Data.MySqlClient.MySqlDataReader getReader = null;
string getString = "SELECT * FROM Dept_ProgramID;";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(getString, conn);
getReader = cmd.ExecuteReader();
while (getReader.Read())
{
getSingleDept adept = new getSingleDept();
adept.DName = getReader.IsDBNull(0) == false?getReader.GetString(0) : null;
adept.DEntity = getReader.IsDBNull(1) == false?getReader.GetString(1) : null;
adept.ProgramID = getReader.IsDBNull(2) == false?getReader.GetString(2) : null;
d.Add(adept);
}
return(d);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{ throw ex; }
finally { conn.Close(); }
}
public bool Login(string email, string password)
{
int count = DbConnecter.instance.Count("age", "member", "email = '" + email + "' AND password = '******';");
if (count == 0)
{
return(false);
}
else
{
string sql = "SELECT * FROM member WHERE email = '" + email + "' AND password = '******';";
MySql.Data.MySqlClient.MySqlDataReader reader = DbConnecter.instance.Reader(sql);
reader.Read();
member.email = email;
member.password = password;
member.gender = reader.GetInt32(2);
member.age = reader.GetInt32(3);
member.child = reader.GetInt32(4);
member.productBookmarks = "";
if (reader.IsDBNull(5) == false)
{
member.productBookmarks = reader.GetString(5);
}
member.ingredientBookmarks = "";
if (reader.IsDBNull(6) == false)
{
member.ingredientBookmarks = reader.GetString(6);
}
reader.Close();
DbConnecter.instance.CloseConnection();
return(true);
}
}
public getSingleDept oneProgID(string als)
{
MySql.Data.MySqlClient.MySqlConnection conn;
string feashConn;
feashConn = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;
conn = new MySql.Data.MySqlClient.MySqlConnection();
try
{
conn.ConnectionString = feashConn;
conn.Open();
getSingleDept d = new getSingleDept();
MySql.Data.MySqlClient.MySqlDataReader getReader = null;
string getString = "SELECT * FROM Dept_ProgramID WHERE DeptProgramID = '" + als.ToString() + "';";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(getString, conn);
getReader = cmd.ExecuteReader();
if (getReader.Read())
{
d.DName = getReader.IsDBNull(0) == false?getReader.GetString(0) : null;
d.DEntity = getReader.IsDBNull(1) == false?getReader.GetString(1) : null;
d.ProgramID = getReader.IsDBNull(2) == false?getReader.GetString(2) : null;
return(d);
}
else
{
return(null);
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{ throw ex; }
finally { conn.Close(); }
}
public void findAll()
{
db = new Database();
brands.Clear();
brandQuery = new MySqlCommand();
brandQuery.Connection = db.Connection();
brandQuery.CommandText = "SELECT * FROM brand ORDER BY brandname ASC";
this.brandResult = brandQuery.ExecuteReader();
while (brandResult.Read())
{
if (!brandResult.IsDBNull(0))
{
idbrand = brandResult.GetInt32(0);
}
if (!brandResult.IsDBNull(1))
{
brandname = brandResult.GetString(1);
}
brands.Add(new Brand { idbrand = this.idbrand, brandname = this.brandname});
}
db.Close();
}
public void findAll()
{
db = new Database();
person_user_computerQuery = new MySqlCommand();
person_user_computerQuery.Connection = db.Connection();
person_user_computerQuery.CommandText = "SELECT * FROM person_user_computer";
person_user_computerResult = person_user_computerQuery.ExecuteReader();
while (person_user_computerResult.Read())
{
if (!person_user_computerResult.IsDBNull(0))
{
idperson_user = person_user_computerResult.GetInt32(0);
}
if (!person_user_computerResult.IsDBNull(1))
{
person = new Person();
person.idperson = person_user_computerResult.GetInt32(1);
person.findByIdperson();
}
if (!person_user_computerResult.IsDBNull(2))
{
user = new User();
user.iduser = person_user_computerResult.GetInt32(2);
user.findByIdUser();
}
//if (!person_user_computerResult.IsDBNull(3))
//{
// person_userstart = person_user_computerResult.GetDateTime(3);
//}
//else
//{
// person_userstart = null;
//}
//if (!person_user_computerResult.IsDBNull(4))
//{
// person_userfinish = person_user_computerResult.GetDateTime(4);
//}
//else
//{
// person_userfinish = null;
//}
persons_users_computers.Add(new Person_User_Computer { idperson_user = this.idperson_user, person = this.person, user = this.user });
}
db.Close();
}
private bool Get_Support_Tickets()
{
try
{
//MySQL_ITIS A = new MySQL_ITIS();
if (MySQL_ITIS.openConnection() == false)
{
throw new Exception("MySQL Connection is not open!");
}
if (MySQL_ITIS.cmd != null)
{
MySQL_ITIS.cmd.Dispose();
}
string _Query = null;
_Query = Pub.isIT_Support() ?
"SELECT * FROM IT_WORKSTATIONS.tickets_new;" :
string.Format("SELECT * FROM IT_WORKSTATIONS.tickets_new WHERE tickets_new.requestee LIKE '{0}' and DATE_FILED >= '2021-05-01';", Pub.MyDisplayName());
//_Query = "SELECT * FROM IT_WORKSTATIONS.tickets_new;";
MySQL_ITIS.cmd = new MySqlCommand(_Query);
MySQL_ITIS.conn.Open();
MySQL_ITIS.cmd.Connection = MySQL_ITIS.conn;
MySql.Data.MySqlClient.MySqlDataReader r = MySQL_ITIS.cmd.ExecuteReader();
Support_Tickets = new List <Support_Ticket>();
while (r.Read())
{
Support_Tickets.Add(new Support_Ticket
{
_TicketID = int.Parse(r.GetString("ticketid")).ToString("000000"),
_Date_Filed = DateTime.Parse(r.GetString("Date_Filed")).ToString("MM/dd/yyyy"),
_Requestee = r.GetString("Requestee"),
_Description = r.IsDBNull(3) ? null : r.GetString("description"),
_Status = r.GetString("status"),
_Last_Update = r.IsDBNull(5) ? null : DateTime.Parse(r.GetString("Last_Update")).ToString("MM/dd/yyyy"),
_Assigned_IT = r.IsDBNull(6) ? null : r.GetString("Assigned_IT"),
_IT_Remarks = r.IsDBNull(7) ? null : r.GetString("IT_Remarks"),
TAG = Set_TAG(r.GetString("status"))
});
}
MySQL_ITIS.conn.Close(); MySQL_ITIS.conn.Dispose();
return(true);
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.Message, "CSF TICKETING", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
}
public static String SafeGetString(MySqlDataReader reader, string colName)
{
if (!reader.IsDBNull(reader.GetOrdinal(colName)))
return reader.GetString(colName);
else
return string.Empty;
}
public static int SafeGetInt(MySqlDataReader reader, string colName)
{
if (!reader.IsDBNull(reader.GetOrdinal(colName)))
return reader.GetInt32(colName);
else
return default(int);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
con.Open();
cmduser = new MySqlCommand("select login_prev,login_curr from login_hist where login_name='" + Session["loginname"].ToString()+"'" , con);
druser = cmduser.ExecuteReader();
druser.Read();
if (druser.IsDBNull(0))
{
logdate.Text = druser.GetDateTime("login_curr").ToShortDateString();
logtime.Text = druser.GetDateTime("login_curr").ToShortTimeString();
}
else
{
logdate.Text = druser.GetDateTime("login_prev").ToShortDateString();
logtime.Text = druser.GetDateTime("login_prev").ToShortTimeString();
}
druser.Close();
}
catch (Exception ex)
{
CreateLogFile log1 = new CreateLogFile();
log1.ErrorLog(Server.MapPath("../Logs/Errorlog"), "Page Load method of Admin_Index for " + Session["loginname"] + ":" + ex.Message);
}
finally
{
con.Close();
}
}
/*----------------------UTILITY------------------------*/
public static String SafeGetString(MySqlDataReader reader, int colIndex)
{
if (!reader.IsDBNull(colIndex))
return reader.GetString(colIndex);
else
return string.Empty;
}
public static DateTime SafeGetDateTime(MySqlDataReader reader, int colIndex)
{
if (!reader.IsDBNull(colIndex))
return reader.GetDateTime(colIndex);
else
return default(DateTime);
}
public static byte[] convertBlobToBufferData(String column,MySqlDataReader rdr)
{
int bufferSize = 1024; // Number of bytes to read at a time
byte[] ImageData = new byte[bufferSize];
long nBytesReturned, startIndex = 0;
int ordinal = rdr.GetOrdinal(column);
string image = rdr.IsDBNull(ordinal) ? null : rdr.GetString(column);
if (image != null)
{
startIndex = 0;
nBytesReturned = rdr.GetBytes(
ordinal, // Column index of BLOB column
startIndex, // Start position of the byte to read
ImageData, // Byte array to recieve BLOB data
0, // Start index of the array
bufferSize // Size of buffer
);
while (nBytesReturned == bufferSize)
{
startIndex += bufferSize;
nBytesReturned = rdr.GetBytes(ordinal, startIndex, ImageData, 0, bufferSize); // Number of bytes returned is assigned to nBytesReturned
}
return ImageData;
}
else
{
return null;
}
}
public static DateTime SafeGetDateTime(MySqlDataReader reader, string colName)
{
if (!reader.IsDBNull(reader.GetOrdinal(colName)))
return reader.GetDateTime(colName);
else
return default(DateTime);
}
public Tag Map(MySqlDataReader reader)
{
Tag tag = new Tag();
tag.Id = reader.GetInt32("id");
tag.Tagname = reader.GetString("tagname");
tag.TagCount = reader.IsDBNull(2) ? (int?)null : reader.GetInt32(2);
return tag;
}
public static String SafeGetString(MySqlDataReader reader, String columnname)
{
int colIndex = reader.GetOrdinal(columnname);
if (!reader.IsDBNull(colIndex))
return reader.GetString(colIndex);
else
return String.Empty;
}
public static void Read( object O,TPropertyMapper M,MySqlDataReader reader)
{
foreach (TPropertyMapping Map in M)
{
if (!reader.IsDBNull(Map.FieldNum))
Map.PInfo.SetValue(O, Convert.ChangeType(reader[Map.FieldName], Map.PInfo.PropertyType), null);
}
}
public String checkNull(MySqlDataReader reader, Int16 index)
{
if (reader.IsDBNull(index))
{
return null;
}
return reader.GetValue(index).ToString();
}
public static PyObject DBColumnToPyObject(int index, ref MySqlDataReader reader)
{
Type type = reader.GetFieldType(index);
switch (type.Name)
{
case "String":
return new PyString(reader.IsDBNull(index) == true ? "" : reader.GetString(index));
case "UInt32":
case "Int32":
case "UInt16":
case "Int16":
case "SByte":
case "Byte":
return new PyInt(reader.IsDBNull(index) == true ? 0 : reader.GetInt32(index));
case "UInt64":
case "Int64":
return new PyLongLong(reader.IsDBNull(index) == true ? 0 : reader.GetInt64(index));
case "Byte[]":
return new PyBuffer(reader.IsDBNull(index) == true ? new byte[0] : (byte[])reader.GetValue(index));
case "Double":
return new PyFloat(reader.IsDBNull(index) == true ? 0.0 : reader.GetDouble(index));
case "Decimal":
return new PyFloat(reader.IsDBNull(index) == true ? 0.0 : (double)reader.GetDecimal(index));
case "Boolean":
return new PyBool(reader.IsDBNull(index) == true ? false : reader.GetBoolean(index));
default:
Log.Error("Database", "Unhandled MySQL type " + type.Name);
break;
}
return null;
}
public static Agente GetAgente(MySqlDataReader rdr)
{
if (rdr.IsDBNull(rdr.GetOrdinal("CODAGENT")))
return null;
Agente a = new Agente();
a.CodAgent = rdr.GetInt32("CODAGENT");
a.NomAgent = rdr.GetString("NOMAGENT");
return a;
}
public void LoadFromDB(MySqlDataReader reader)
{
if (reader.Read())
{
Nickname = (reader.IsDBNull(0) ? null : reader.GetString(0));
Level = reader.GetInt16(1);
Exp = reader.GetInt16(2);
}
}
public CoulomnInformations(MySqlDataReader reader)
{
this._Field = reader.GetString(reader.GetOrdinal("Field"));
this._Type = reader.GetString(reader.GetOrdinal("Type"));
if (!reader.IsDBNull(reader.GetOrdinal("Collation")))
{
this._Collation = reader.GetString(reader.GetOrdinal("Collation"));
}
this._Null = reader.GetString(reader.GetOrdinal("Null"));
this._Key = reader.GetString(reader.GetOrdinal("Key"));
if (!reader.IsDBNull(reader.GetOrdinal("Default")))
{
this._Default = reader.GetString(reader.GetOrdinal("Default"));
}
this._Extra = reader.GetString(reader.GetOrdinal("Extra"));
this._Privileges = reader.GetString(reader.GetOrdinal("Privileges"));
this._Comment = reader.GetString(reader.GetOrdinal("Comment"));
}
public static Usuario GetUsuario(MySqlDataReader rdr)
{
if (rdr.IsDBNull(rdr.GetOrdinal("CODUSU"))) return null;
Usuario u = new Usuario();
u.CodUsu = rdr.GetInt32("CODUSU");
u.NomUsu = rdr.GetString("NOMUSU");
u.Login = rdr.GetString("LOGIN");
u.PasswordPropio = rdr.GetString("PASSWORD_PROPIO");
u.NivelAriges = rdr.GetInt32("NIVEL_ARIGES");
return u;
}
public static Albaran GetAlbaran(MySqlDataReader rdr)
{
if (rdr.IsDBNull(rdr.GetOrdinal("NUMALBAR")))
return null;
Albaran a = new Albaran();
a.CodTipom = rdr.GetString("CODTIPOM");
a.NumAlbar = rdr.GetInt32("NUMALBAR");
a.FechaAlb = rdr.GetDateTime("FECHAALB");
a.TotalAlb = rdr.GetDecimal("TOTALALB");
return a;
}
internal PlaniData(MySqlDataReader r)
{
StringBuilder sb = new StringBuilder();
Gala = r.GetUInt16(0);
Sys = r.GetUInt16(1);
Pla = r.GetUInt16(2);
Planityp = r.GetString(3);
Objekttyp = r.GetString(4);
Ownername = r.GetString(5);
Ownerally = r.IsDBNull(6) ? "" : r.GetString(6);
Planiname = r.GetString(7);
}
private void loadFeatured()
{
command = connection.CreateCommand();
command.CommandText = "SELECT a.id, a.title, a.url, a.author, a.date, a.content FROM Liked l JOIN Articles a ON l.article_id = a.id GROUP BY l.article_id ORDER BY COUNT(l.article_id) DESC";
result = command.ExecuteReader();
while(result.Read() == true)
{
HtmlGenericControl article = new HtmlGenericControl("article");
HtmlGenericControl date = new HtmlGenericControl("div");
date.Attributes["class"] = "date";
date.InnerHtml = result.GetString("date").Substring(0, 10);
article.Controls.Add(date);
HtmlGenericControl title = new HtmlGenericControl("h2");
HtmlGenericControl titleLink = new HtmlGenericControl("a");
titleLink.Attributes["href"] = result.GetString("url");
titleLink.InnerHtml = result.GetString("title");
title.Controls.Add(titleLink);
article.Controls.Add(title);
if(result.IsDBNull(3) == false)
{
HtmlGenericControl author = new HtmlGenericControl("div");
author.Attributes["class"] = "author";
author.InnerHtml = "by <b>" + result.GetString("author") + "</b>";
article.Controls.Add(author);
}
HtmlGenericControl content = new HtmlGenericControl("div");
content.Attributes["class"] = "content";
content.InnerHtml = "<p>" + result.GetString("content") + "</p>";
article.Controls.Add(content);
HtmlGenericControl actionBar = new HtmlGenericControl("div");
actionBar.Attributes["class"] = "action-bar";
HtmlAnchor likeButton = new HtmlAnchor();
likeButton.Attributes["onclick"] = "PageMethods.like(\"" + result.GetString("id") + "\")";
likeButton.InnerHtml = "Like";
actionBar.Controls.Add(likeButton);
article.Controls.Add(actionBar);
featured.Controls.Add(article);
}
result.Close();
}
public void WriteReview(int amount)
{
int productID = 1;
for (int i = 0; i < amount; i++)
{
string email = "changyeong" + i;
int score = UnityEngine.Random.Range(1, 6);
int first = UnityEngine.Random.Range(0, 5);
int second = UnityEngine.Random.Range(0, 6);
int third = UnityEngine.Random.Range(0, 3);
int fourth = UnityEngine.Random.Range(0, 6);
int fifth = UnityEngine.Random.Range(0, 5);
string sql = "SELECT productReviews FROM member WHERE email = '" + email + "';";
MySql.Data.MySqlClient.MySqlDataReader reader = DbConnecter.instance.Reader(sql);
reader.Read();
string productReviews = "";
if (reader.IsDBNull(0) == false)
{
productReviews = reader.GetString(0);
}
reader.Close();
productReviews = productReviews + productID + ';';
sql = "INSERT INTO review VALUES(" + "'" + email + "', " + productID + ", " + score + ", " + first + ", " + second + ", " + third + ", " + fourth + ", " + fifth + ");";
DbConnecter.instance.ExecuteSQL(sql, false);
sql = "UPDATE member SET productReviews = '" + productReviews + "' WHERE email = '" + email + "';";
DbConnecter.instance.ExecuteSQL(sql, false);
sql = "SELECT COUNT(*), AVG(CAST(score as FLOAT)) FROM review GROUP BY productID HAVING productID = " + productID + ";";
reader = DbConnecter.instance.Reader(sql);
reader.Read();
int reviewer = reader.GetInt32(0);
float grade = (float)reader.GetDouble(1);
reader.Close();
sql = "UPDATE product SET grade = " + grade + ", reviewer = " + reviewer + " WHERE id = " + productID + ";";
DbConnecter.instance.ExecuteSQL(sql, true);
DbConnecter.instance.CloseConnection();
Debug.Log(i);
}
}
public static DateTime GetMySQLDateTime(MySqlDataReader mySQLDataReader, String column)
{
DateTime dateTime = DateTime.MinValue;
try
{
int index = mySQLDataReader.GetOrdinal(column);
if (!mySQLDataReader.IsDBNull(index))
{
MySqlDateTime mySQLDateTime = mySQLDataReader.GetMySqlDateTime(column);
if (mySQLDateTime.Day > 0)
{
dateTime = mySQLDateTime.GetDateTime();
}
}
}
catch (Exception ex)
{
log.Error(ex.Message);
}
return dateTime;
}
public void findAll()
{
db = new Database();
models.Clear();
modelQuery = new MySqlCommand();
modelQuery.Connection = db.Connection();
modelQuery.CommandText = "SELECT * FROM model ORDER BY modeldescription ASC";
this.modelResult = modelQuery.ExecuteReader();
while (modelResult.Read())
{
if (!modelResult.IsDBNull(0))
{
idmodel = modelResult.GetInt32(0);
}
if (!modelResult.IsDBNull(1))
{
modeldescription = modelResult.GetString(1);
}
if (!modelResult.IsDBNull(2))
{
idbrand = modelResult.GetInt32(2);
}
models.Add(new Model { idmodel = this.idmodel, modeldescription = this.modeldescription});
}
db.Close();
}
private TrackingEntry readEntry(MySqlDataReader reader)
{
TrackingEntry currentEntry = new TrackingEntry();
currentEntry.entryID = reader.GetInt32(0);
currentEntry.taggerID = reader.GetInt32(1);
currentEntry.type = reader.GetChar(2);
currentEntry.isTagged = reader.GetBoolean(3);
currentEntry.taggerName = reader.GetString(4);
currentEntry.entryDate = ((DateTime)reader.GetMySqlDateTime(5)).ToString("yyyy-MM-dd HH:mm:ss");
if (reader.IsDBNull(6))
currentEntry.tagNumber = -1;
else
currentEntry.tagNumber = reader.GetInt32(6);
currentEntry.species = reader.GetString(7);
if (reader.IsDBNull(8))
currentEntry.city = "";
else
currentEntry.city = reader.GetString(8);
if (reader.IsDBNull(9))
currentEntry.state = "";
else
currentEntry.state = reader.GetString(9);
if (reader.IsDBNull(10))
currentEntry.country = "";
else
currentEntry.country = reader.GetString(10);
if (reader.IsDBNull(11))
currentEntry.longitude = "";
else
currentEntry.longitude = reader.GetString(11);
if (reader.IsDBNull(12))
currentEntry.latitude = "";
else
currentEntry.latitude = reader.GetString(12);
if (reader.IsDBNull(13))
currentEntry.temperature = "";
else
currentEntry.temperature = reader.GetString(13);
if (reader.IsDBNull(14))
currentEntry.precipitation = "";
else
currentEntry.precipitation = reader.GetString(14);
if (reader.IsDBNull(15))
currentEntry.windSpeed = "";
else
currentEntry.windSpeed = reader.GetString(15);
if (reader.IsDBNull(16))
currentEntry.windDirection = "";
else
currentEntry.windDirection = reader.GetString(16);
return currentEntry;
}
public void findAll()
{
db = new Database();
officeQuery = new MySqlCommand();
officeQuery.Connection = db.Connection();
officeQuery.CommandText = "SELECT * FROM office ORDER BY officedescription ASC";
officeResult = officeQuery.ExecuteReader();
while (officeResult.Read())
{
if (!officeResult.IsDBNull(0))
{
idoffice = officeResult.GetInt32(0);
}
if (!officeResult.IsDBNull(1))
{
officedescription = officeResult.GetString(1);
}
offices.Add(new Office { idoffice = this.idoffice, officedescription = this.officedescription });
}
db.Close();
}
public void setProperty(MySqlDataReader reader)
{
this._Name = reader.GetString(reader.GetOrdinal("Name"));
this._Engine = reader.GetString(reader.GetOrdinal("Engine"));
this._Version = reader.GetInt64(reader.GetOrdinal("Version"));
this._Row_format = reader.GetString(reader.GetOrdinal("Row_format"));
if (!reader.IsDBNull(reader.GetOrdinal("Rows")))
{
this._Rows = reader.GetInt64(reader.GetOrdinal("Rows"));
}
this._Avg_row_length = reader.GetInt64(reader.GetOrdinal("Avg_row_length"));
this._Data_length = reader.GetInt64(reader.GetOrdinal("Data_length"));
this._Max_data_length = reader.GetInt64(reader.GetOrdinal("Max_data_length"));
this._Index_length = reader.GetInt64(reader.GetOrdinal("Index_length"));
this._Data_free = reader.GetInt64(reader.GetOrdinal("Data_free"));
if (!reader.IsDBNull(reader.GetOrdinal("Auto_increment")))
{
this._Auto_increment = reader.GetInt64(reader.GetOrdinal("Auto_increment"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Create_time")))
{
this._Create_time = reader.GetDateTime(reader.GetOrdinal("Create_time"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Update_time")))
{
this._Update_time = reader.GetDateTime(reader.GetOrdinal("Update_time"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Check_time")))
{
this._Check_time = reader.GetDateTime(reader.GetOrdinal("Check_time"));
}
this._Collation = reader.GetString(reader.GetOrdinal("Collation"));
if (!reader.IsDBNull(reader.GetOrdinal("Checksum")))
{
this._Checksum = reader.GetInt64(reader.GetOrdinal("Checksum"));
}
this._Create_options = reader.GetString(reader.GetOrdinal("Create_options"));
this._Comment = reader.GetString(reader.GetOrdinal("Comment"));
}
private static UserRecord MapUserRecord(MySqlDataReader dr)
{
var record = new UserRecord();
record.Id = dr.GetInt64("id");
record.LastUpdated = dr.GetDateTime("lastUpdated");
record.Losses = dr.GetInt32("losses");
record.RecordType = (RecordType)dr.GetInt32("type");
record.Ties = dr.GetInt32("ties");
record.UserId = dr.GetInt64("userId");
record.UserName = dr.GetString("userName");
record.Wins = dr.GetInt32("wins");
if (!dr.IsDBNull(dr.GetOrdinal("season")))
{
record.Season = dr.GetInt32("season");
}
if (!dr.IsDBNull(dr.GetOrdinal("seasonType")))
{
record.SeasonType = (SeasonType) dr.GetInt32("seasonType");
}
if (!dr.IsDBNull(dr.GetOrdinal("week")))
{
record.Week = dr.GetInt32("week");
}
return record;
}
//
// GetUserFromReader
// A helper function that takes the current row from the MySqlDataReader
// and hydrates a MembershiUser from the values. Called by the
// MembershipUser.GetUser implementation.
//
private MembershipUser GetUserFromReader(MySqlDataReader reader)
{
object providerUserKey = new Guid(reader.GetValue(0).ToString());
string username = reader.IsDBNull(1) ? "" : reader.GetString(1);
string email = reader.IsDBNull(2) ? "" : reader.GetString(2);
string passwordQuestion = reader.IsDBNull(3) ? "" : reader.GetString(3);
string comment = reader.IsDBNull(4) ? "" : reader.GetString(4);
bool isApproved = reader.IsDBNull(5) ? false : reader.GetBoolean(5);
bool isLockedOut = reader.IsDBNull(6) ? false : reader.GetBoolean(6);
DateTime creationDate = reader.IsDBNull(7) ? DateTime.Now : reader.GetDateTime(7);
DateTime lastLoginDate = reader.IsDBNull(8) ? DateTime.Now : reader.GetDateTime(8);
DateTime lastActivityDate = reader.IsDBNull(9) ? DateTime.Now : reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.IsDBNull(10) ? DateTime.Now : reader.GetDateTime(10);
DateTime lastLockedOutDate = reader.IsDBNull(11) ? DateTime.Now : reader.GetDateTime(11);
return new MembershipUser(
this.Name,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate
);
}
private void countByCategorydescription()
{
db = new Database();
actiontypeQuery = new MySqlCommand();
actiontypeQuery.Connection = db.Connection();
actiontypeQuery.CommandText = "SELECT COUNT(*) FROM actiontype WHERE actiontypedescription = @Description";
actiontypeQuery.Prepare();
actiontypeQuery.Parameters.AddWithValue("@Description", actiontypedescription);
actiontypeResult = actiontypeQuery.ExecuteReader();
while (actiontypeResult.Read())
{
if (!actiontypeResult.IsDBNull(0))
{
countCategory = actiontypeResult.GetInt32(0);
}
}
}
public void findByIdcategory()
{
db = new Database();
actiontypeQuery = new MySqlCommand();
actiontypeQuery.Connection = db.Connection();
actiontypeQuery.CommandText = "SELECT * FROM actiontype WHERE idactiontype = @idactiontype";
actiontypeQuery.Prepare();
actiontypeQuery.Parameters.AddWithValue("@IDActiontype", idactiontype);
actiontypeResult = actiontypeQuery.ExecuteReader();
while (actiontypeResult.Read())
{
if (!actiontypeResult.IsDBNull(0))
{
idactiontype = actiontypeResult.GetInt32(0);
}
if (!actiontypeResult.IsDBNull(1))
{
actiontypedescription = actiontypeResult.GetString(1);
}
}
}
private XmlDocument GetCreditRules(XmlDocument Params, bool doSchemaVal, bool doEnc)
{
RafLog.Log.LogToFile("Call GetCreditRules");
RafLog.Log.LogToFile("Requesting IP: " + HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"]);
RafLog.Log.LogToFile("Forwarded For IP: " + HttpContext.Current.Request.ServerVariables["HTTP_X_FORWARDED_FOR"]);
//Log("ParamsString: " + ParamsString);
if (Params.OuterXml != null)
{
RafLog.Log.LogToFile("ParamsString: " + Params.OuterXml);
}
else
{
RafLog.Log.LogToFile("ParamsString: Empty");
}
#region Docs
// Doc for return
XmlDocument XmlResponse = new XmlDocument();
XmlNode root = XmlResponse.AppendChild(XmlResponse.CreateNode(XmlNodeType.Element, "RAF", ""));
XmlNode Req = root.AppendChild(XmlResponse.CreateNode(XmlNodeType.Element, "Request", ""));
XmlNode Par = Req.AppendChild(XmlResponse.CreateNode(XmlNodeType.Element, "Parameters", ""));
XmlNode Res = root.AppendChild(XmlResponse.CreateNode(XmlNodeType.Element, "Response", ""));
XmlNode ResID = Res.AppendChild(XmlResponse.CreateNode(XmlNodeType.Element, "ResponseID", ""));
XmlNode sT = Res.AppendChild(XmlResponse.CreateNode(XmlNodeType.Element, "Status", ""));
XmlNode sTSucc = sT.AppendChild(XmlResponse.CreateNode(XmlNodeType.Element, "Success", ""));
sTSucc.InnerText = "false";
XmlNode ErrCode = sT.AppendChild(XmlResponse.CreateNode(XmlNodeType.Element, "ErrorCode", ""));
ErrCode.InnerText = "0";
XmlNode ErrName = sT.AppendChild(XmlResponse.CreateNode(XmlNodeType.Element, "ErrorName", ""));
ErrName.InnerText = "DEFAULT_ERROR";
XmlNode ErrMsg = sT.AppendChild(XmlResponse.CreateNode(XmlNodeType.Element, "ErrorMessage", ""));
ErrMsg.InnerText = "Unknown Error";
XmlNode Credit = Res.AppendChild(XmlResponse.CreateNode(XmlNodeType.Element, "Credit", ""));
//Docs for getting the sp results
string stringResp = "";
XmlDocument dbResponse = new XmlDocument();
//XmlDocument Params = new XmlDocument();
XmlDocument creditRulesXml = new XmlDocument();
//Docs for Validating
XmlDocument XDocValidate = new XmlDocument();
XmlDocument XDocValidate2 = new XmlDocument();
#endregion
//////////////////////////////////////////////////////////////////
RafLog.Log.LogToFile("CleanXml");
#region CleanXml
try
{
if (Params.FirstChild.Attributes["xmlns"] != null)
{
string tmpX = Params.OuterXml;
Regex RegexObj = new Regex("\\s?xmlns=\".+?\"");
Params.InnerXml = RegexObj.Replace(tmpX, "");
RafLog.Log.LogToFile("Cleaned: " + tmpX);
}
}
catch (Exception x1)
{
RafLog.Log.LogToFile("Failed cleaning XML. Error: " + x1.Message);
}
#endregion
RafLog.Log.LogToFile("A");
try
{
RafLog.Log.LogToFile("A1");
//Validate XmlData
if (doSchemaVal)
{
XDocValidate = ValXML.validateCreditRulesCall(Params);
RafLog.Log.LogToFile("A2");
//If Error, Return
if (
(XDocValidate.FirstChild.SelectSingleNode("Error").Attributes["Value"] != null) &&
(XDocValidate.FirstChild.SelectSingleNode("Error").Attributes["Value"].Value != "")
)
{
RafLog.Log.LogToFile("A3");
ErrCode.InnerText = "100";
ErrName.InnerText = "INVALID_XML_CALL";
ErrMsg.InnerText = XDocValidate.FirstChild.SelectSingleNode("Error").Attributes["Value"].Value;
//IfError
//
RafLog.Log.LogToFile("Invalid XML");
RafLog.Log.LogToFile(XmlResponse.OuterXml);
return(XmlResponse);
}
}
RafLog.Log.LogToFile("B");
Par.InnerXml = Params.SelectSingleNode("/RAF/Request/Parameters").InnerXml;
string connStr = ConfigurationManager.ConnectionStrings["dbConnectionRAFMain"].ConnectionString;
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr))
{
RafLog.Log.LogToFile("C");
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
//cmd.CommandText = "CALL proc_getAssetRules(?p_xml, ?p_asset, ?p_assettype , ?p_lang , ?p_user , ?p_passphrase);Select @p_xml;";
//cmd.Parameters.Add("@p_xml", MySqlDbType.VarChar);
cmd.CommandText = "CALL proc_getCreditRules(?p_creditType, ?p_customerID, ?p_country , ?p_lang , ?p_user , ?p_passphrase);";
cmd.Parameters.Add("?p_creditType", MySqlDbType.UInt16).Value = Convert.ToInt32(Params.SelectSingleNode("/RAF/Request/Parameters/prmCreditTypeId").InnerText);
cmd.Parameters.Add("?p_customerID", MySqlDbType.VarChar).Value = Params.SelectSingleNode("/RAF/Request/Parameters/prmCustomerId").InnerText;
cmd.Parameters.Add("?p_country", MySqlDbType.VarChar).Value = Params.SelectSingleNode("/RAF/Request/Parameters/prmCustomerIdCountry").InnerText;
cmd.Parameters.Add("?p_lang", MySqlDbType.VarChar).Value = Params.SelectSingleNode("/RAF/Request/Parameters/prmLanguage").InnerText;
cmd.Parameters.Add("?p_user", MySqlDbType.VarChar).Value = Params.SelectSingleNode("/RAF/Request/Login/user").InnerText;
cmd.Parameters.Add("?p_passphrase", MySqlDbType.VarChar).Value = Params.SelectSingleNode("/RAF/Request/Login/passphrase").InnerText;
RafLog.Log.LogToFile("C9");
cmd.Connection.Open();
RafLog.Log.LogToFile("C1");
MySql.Data.MySqlClient.MySqlDataReader rdr = cmd.ExecuteReader();
RafLog.Log.LogToFile("C2");
if (rdr.Read())
{
if (rdr.FieldCount > 0 && !rdr.IsDBNull(0))
{
stringResp = rdr.GetString(0);
}
}
}
RafLog.Log.LogToFile("D");
RafLog.Log.LogToFile("DB Response : " + stringResp);
//Validate Credit Rules
//First lets see if its an xml
try
{
RafLog.Log.LogToFile("D1");
dbResponse.LoadXml(stringResp);
}
catch (XmlException xx)
{
RafLog.Log.LogToFile("D2");
ErrName.InnerText = "MALFORMED_XML_FROM_DB";
ErrCode.InnerText = "200";
ErrMsg.InnerText = "Invalid Rules";
RafLog.Log.LogToFile("MALFORMED_XML_FROM_DB");
RafLog.Log.LogToFile(XmlResponse.OuterXml);
RafLog.Log.LogToFile("D3");
}
catch (Exception xx2)
{
RafLog.Log.LogToFile("D4");
ErrName.InnerText = "MALFORMED_XML_FROM_DB";
ErrCode.InnerText = "200";
ErrMsg.InnerText = "Invalid Rules";
RafLog.Log.LogToFile("MALFORMED_XML_FROM_DB");
RafLog.Log.LogToFile(XmlResponse.OuterXml);
RafLog.Log.LogToFile("D5");
}
RafLog.Log.LogToFile("FLOW1");
RafLog.Log.LogToFile("ErrCode.InnerText " + ErrCode.InnerText);
//Now Load the status into our main xml to catch any errors the Sp might have produced
if (ErrCode.InnerText == "0")
{
RafLog.Log.LogToFile("EA");
if (dbResponse.SelectSingleNode("/Response/Status") != null)
{
RafLog.Log.LogToFile("E");
if (dbResponse.SelectSingleNode("/Response/Status/ErrorName") != null)
{
RafLog.Log.LogToFile("E1");
ErrName.InnerText = dbResponse.SelectSingleNode("/Response/Status/ErrorName").InnerText;
}
if (dbResponse.SelectSingleNode("/Response/Status/ErrorCode") != null)
{
RafLog.Log.LogToFile("E2");
ErrCode.InnerText = dbResponse.SelectSingleNode("/Response/Status/ErrorCode").InnerText;
}
if (dbResponse.SelectSingleNode("/Response/Status/ErrorMessage") != null)
{
RafLog.Log.LogToFile("E3");
ErrMsg.InnerText = dbResponse.SelectSingleNode("/Response/Status/ErrorMessage").InnerText;
}
}
}
RafLog.Log.LogToFile("FLOW2");
//Validate XmlData
if (ErrCode.InnerText == "0")
{
RafLog.Log.LogToFile("F1");
creditRulesXml.LoadXml(dbResponse.SelectSingleNode("/Response/Credit").OuterXml);
XDocValidate2 = ValXML.validateCreditRules(creditRulesXml);
//If Error, Return
if (
(XDocValidate2.FirstChild.SelectSingleNode("Error").Attributes["Value"] != null) &&
(XDocValidate2.FirstChild.SelectSingleNode("Error").Attributes["Value"].Value != "")
)
{
RafLog.Log.LogToFile("F2");
ErrName.InnerText = "MALFORMED_RULES";
ErrCode.InnerText = "201";
ErrMsg.InnerText = XDocValidate2.FirstChild.SelectSingleNode("Error").Attributes["Value"].Value;
//try to respond something at least
Credit.InnerXml = creditRulesXml.InnerXml;
//IfError
//
RafLog.Log.LogToFile("MALFORMED_RULES");
RafLog.Log.LogToFile(XmlResponse.OuterXml);
RafLog.Log.LogToFile("F3");
return(XmlResponse);
}
}
RafLog.Log.LogToFile("FLOW3");
if (ErrCode.InnerText == "0")
{
RafLog.Log.LogToFile("G1");
if (dbResponse.SelectSingleNode("/Response/ResponseID") != null)
{
ResID.InnerText = dbResponse.SelectSingleNode("/Response/ResponseID").InnerText;
}
Credit.InnerXml = creditRulesXml.FirstChild.InnerXml;
//Status
sTSucc.InnerText = "true";
}
RafLog.Log.LogToFile("FLOW4");
//IfError
if (ErrCode.InnerText != "0")
{
RafLog.Log.LogToFile(XmlResponse.OuterXml);
}
RafLog.Log.LogToFile("FLOW5");
}
catch (Exception ex)
{
ErrMsg.InnerText = ex.Message;
ErrCode.InnerText = "1";
RafLog.Log.LogToFile("ERROR: " + ex.Message);
}
RafLog.Log.LogToFile("XmlResponse: " + XmlResponse.OuterXml);
return(XmlResponse);
}
private static Tests.Models.ChangeTrackingEmployee Load(MySql.Data.MySqlClient.MySqlDataReader reader)
{
var result = new Tests.Models.ChangeTrackingEmployee();
if (reader.IsDBNull(0))
{
result.CreatedDate = null;
}
else
{
result.CreatedDate = reader.GetDateTime(0);
}
if (reader.IsDBNull(1))
{
result.EmployeeKey = null;
}
else
{
result.EmployeeKey = (System.Nullable <System.Int64>)reader.GetValue(1);
}
if (reader.IsDBNull(2))
{
result.FirstName = null;
}
else
{
result.FirstName = reader.GetString(2);
}
if (reader.IsDBNull(3))
{
result.LastName = null;
}
else
{
result.LastName = reader.GetString(3);
}
if (reader.IsDBNull(4))
{
result.ManagerKey = null;
}
else
{
result.ManagerKey = reader.GetInt32(4);
}
if (reader.IsDBNull(5))
{
result.MiddleName = null;
}
else
{
result.MiddleName = reader.GetString(5);
}
if (reader.IsDBNull(6))
{
result.Title = null;
}
else
{
result.Title = reader.GetString(6);
}
if (reader.IsDBNull(7))
{
result.UpdatedDate = null;
}
else
{
result.UpdatedDate = reader.GetDateTime(7);
}
((System.ComponentModel.IChangeTracking)result).AcceptChanges();
return(result);
}
public static T GetValueOrDefault <T>(this MySqlDataReader dataReader, int columnIndex)
{
int index = Convert.ToInt32(columnIndex);
return(!dataReader.IsDBNull(index) ? (T)dataReader.GetValue(index) : default(T));
}
public gEmployee getEmployee(string als)
{
gEmployee e = new gEmployee();
MySql.Data.MySqlClient.MySqlDataReader getReader = null;
string getString = "SELECT * FROM Emp_details WHERE eAlias = '" + als.ToString() + "';";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(getString, conn);
getReader = cmd.ExecuteReader();
if (getReader.Read())
{
e.Alias = getReader.IsDBNull(0) == false?getReader.GetString(0) : null;
e.GIN = getReader.IsDBNull(1) == false?getReader.GetString(1) : null;
e.DisplayName = getReader.IsDBNull(2) == false?getReader.GetString(2) : null;
e.UserPrincipalName = getReader.IsDBNull(3) == false?getReader.GetString(3) : null;
e.JobCode = getReader.IsDBNull(4) == false?getReader.GetString(4) : null;
e.MobilePhone = getReader.IsDBNull(5) == false?getReader.GetString(5) : null;
e.GOLDMedalOwner = getReader.IsDBNull(6) == false?getReader.GetString(6) : null;
e.QuestOTC = getReader.IsDBNull(7) == false?getReader.GetFloat(7) : 0;
e.Country = getReader.IsDBNull(8) == false?getReader.GetString(8) : null;
e.Department = getReader.IsDBNull(9) == false?getReader.GetString(9) : null;
e.Entity = getReader.IsDBNull(10) == false?getReader.GetString(10) : null;
e.ProgramID = getReader.IsDBNull(11) == false?getReader.GetString(11) : null;
e.CardNo = getReader.IsDBNull(12) == false?getReader.GetString(12) : null;
e.PIN = getReader.IsDBNull(13) == false?getReader.GetString(13) : null;
return(e);
}
else
{
return(null);
}
}