public static void Login(string username, string passhash)
{
if (Validate(username, passhash))
{
time = string.Format("{0:yyyy.MM.dd 0:HH:mm:ss tt}", DateTime.Now);
rdr = new MySqlCommand("SELECT lastin FROM login WHERE username='******';", conn).ExecuteReader();
while (rdr.Read()) {
Console.WriteLine("User's last sign in was: " + rdr["lastin"]);
}
rdr.Close();
new MySqlCommand("UPDATE login SET lastin='" + time + "' WHERE username='******';", conn).ExecuteNonQuery();
}
Console.WriteLine("HHHHhhhh");
//TODO:
//
//If the username exists, check that the password hash matches.
//
//If the username does not exist, be like "No user found".
//
//Else, If the password hash matches, Sign in.
//Set the current player to active
//forward/bind socket to control an Object?
//Note the timestamp and IP of the login in the database
//Send all of the necessary information back to the client:
}
public Course(MySqlDataReader dReader)
{
lectures = new List<Section>();
labs = new List<Section>();
iterations = new List<List<Section>>();
dReader.Read();
dept = dReader.GetValue(0).ToString();
number = (int)dReader.GetValue(1);
creditHours = (int)dReader.GetValue(4);
do
{
if (dReader[6].ToString() == "LEC")
{
if (!CheckSections(lectures, dReader))
{
lectures.Add(new Section(dReader));
}
}
else if (dReader[6].ToString() != "DSO")
{
if (!CheckSections(labs, dReader))
{
labs.Add(new Section(dReader));
}
}
} while (dReader.Read());
}
// overload 2/9/2011 fix load history in new month
public bool CheckMonthlyDocIsApprove(POSMySQL.POSControl.CDBUtil dbUtil, MySql.Data.MySqlClient.MySqlConnection conn,
DateTime dateFrom, DateTime dateTo)
{
this.DocumentTypeId = 7;
string sql = " SELECT DocumentDate, DocumentID FROM document WHERE documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
" AND DocumentStatus=1 AND DocumentDate BETWEEN '" + dateFrom.ToString("yyyy-MM-dd", dateProvider) + "' AND '" + dateTo.ToString("yyyy-MM-dd", dateProvider) + "'";
MySql.Data.MySqlClient.MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
this.DocumentDate = reader.GetDateTime("DocumentDate");
this.DocumentId = reader.GetInt32("DocumentID");
reader.Close();
return(false); // Approved
}
else
{
sql = " SELECT DocumentDate, DocumentID FROM document WHERE documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
" AND DocumentStatus=2 AND DocumentDate BETWEEN '" + dateFrom.ToString("yyyy-MM-dd", dateProvider) + "' AND '" + dateTo.ToString("yyyy-MM-dd", dateProvider) + "'";
reader.Close();
reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
this.DocumentDate = reader.GetDateTime("DocumentDate");
this.DocumentId = reader.GetInt32("DocumentID");
reader.Close();
return(true); // Not Approve
}
else
{
reader.Close();
}
}
return(false);
}
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 UUID GenerateUUID(string Table)
{
string ID = "";
bool done = false;
connection = new MySqlConnection(MyConString);
command = connection.CreateCommand();
while (!done)
{
ID = GenUUID();
command.CommandText = "SELECT * FROM " + Table + " WHERE " + (Table == "BearFood" ? "StorageID" : "BearID") + " = '" + ID + "'";
connection.Open();
Reader = command.ExecuteReader();
if(Reader.Read() == true)
{
Reader.GetString("BearsID");
done = false;
}
else
{
done = true;
}
connection.Close();
}
UUIDs = ID;
return this;
}
public static ArrayList MySQL_DateBase(string server, string username, string password, string port = "3306")
{
//Ip+端口+数据库名+用户名+密码
string connectStr = "server=" + server + ";port=" + port + ";database=information_schema" + ";user="******";password="******";";
ArrayList datebase = new ArrayList();
MySqlConnection conn = new MySqlConnection(connectStr);;
try
{
conn.Open();//跟数据库建立连接，并打开连接
string sql = "select schema_name from information_schema.schemata";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = cmd.ExecuteReader();
while (msqlReader.Read())
{ //do something with each record
// Console.WriteLine(" Datebase: " + msqlReader[0]);
if ((msqlReader[0].ToString() != "information_schema") && (msqlReader[0].ToString() != "mysql") && (msqlReader[0].ToString() != "performance_schema") && (msqlReader[0].ToString() != "sys"))
{
datebase.Add(msqlReader[0]);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
conn.Clone();
}
return(datebase);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString.Count==0) { evtid = "2"; }
else
{ evtid = Request.QueryString["eventid"]; }
try
{
con.Open();
cmddispevt = new MySqlCommand("select name,detail,img1path,img2path,img3path from events where rowid=" + evtid, con);
drdispevt = cmddispevt.ExecuteReader();
drdispevt.Read();
EventHead = drdispevt.GetString("name");
EventStory = drdispevt.GetString("detail");
img1.Src = "../"+drdispevt.GetString("img1path") + "thumbs/img1.jpg";
img2.Src = "../" + drdispevt.GetString("img2path") + "thumbs/img2.jpg";
img3.Src = "../" + drdispevt.GetString("img3path") + "thumbs/img3.jpg";
aimg1.HRef = "../" + drdispevt.GetString("img1path") + "slides/img1.jpg";
aimg2.HRef = "../" + drdispevt.GetString("img2path") + "slides/img2.jpg";
aimg3.HRef = "../" + drdispevt.GetString("img3path") + "slides/img3.jpg";
drdispevt.Close();
con.Close();
}
catch (Exception ex)
{
CreateLogFile errlog = new CreateLogFile();
errlog.ErrorLog(Server.MapPath("../Logs/Errorlog"), "Page Load of AboutSSNAA Page for " + Session["loginname"] + ":" + ex.Message);
}
}
public static Atributos BuscaUsuario(int BuscaUsuario)
{
try
{
Con = Conexao.GetConnection();
Conexao.AbrirConexao(Con);
Sql = "Select * From TbMorador where CodMorador = " + BuscaUsuario + "";
Cmd = new MySqlCommand(Sql, Con);
Dr = Cmd.ExecuteReader();
if (Dr.Read())
{
Morador.NomeMorador = Convert.ToString(Dr["NomeMorador"]).Trim();
Morador.Bloco = Convert.ToString(Dr["Bloco"]).Trim();
Morador.Apartamento = Convert.ToString(Dr["Apartamento"]).Trim();
Morador.Senha = Convert.ToString(Dr["Senha"]).Trim();
return Morador;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw new Exception("Erro Ao Busca Usuario :" + ex);
}
}
public Food loadFoodDetails(int foodId)
{
Food p = new Food();
MySqlConnection conn = mysqlDbConnect.GetConnection();
MySql.Data.MySqlClient.MySqlDataReader mySqlReade = null;
String sqlString = "SELECT * FROM food WHERE foodId='" + foodId + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReade = cmd.ExecuteReader();
if (mySqlReade.Read())
{
p.foodId = mySqlReade.GetInt32(0);
p.foodName = mySqlReade.GetString(1);
p.foodPrice = mySqlReade.GetInt32(2);
return(p);
}
else
{
return(null);
}
}
public Values getValues(int id)
{
Values v = new Values();
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
string sqlString = $"SELECT * FROM Products WHERE id ={id}";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySQLReader = cmd.ExecuteReader();
if (mySQLReader.Read())
{
v.id = mySQLReader.GetInt32(0);
v.product_name = mySQLReader.GetString(1);
v.stock_quantity = mySQLReader.GetInt32(2);
v.des_box = mySQLReader.GetString(3);
v.URL = mySQLReader.GetString(4);
v.categories = mySQLReader.GetString(5);
return(v);
}
else
{
return(null);
}
}
public bool Logar(string nickname, string senha)
{
Conexao();
bool logado = false;
string sql = "select id, nickname, senha from Usuario where nickname= @nickname ";
command = new MySqlCommand(sql, Conexao());
par = new MySqlParameter("@nickname", nickname);
par.MySqlDbType = MySqlDbType.VarChar;
command.Parameters.Add(par);
reader = command.ExecuteReader();
string senhaUsuario;
if (reader.Read())
{
senhaUsuario = reader.GetValue(reader.GetOrdinal("SENHA")).ToString();
if (senhaUsuario.Equals(senha))
{
logado = true;
}
}
FecharConexao();
return logado;
// command = new System.Data.SqlClient.SqlCommand(sql, conexão());
}
public List<Tarifa> LoadTarifa()
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
lisTrf = new List<Tarifa>();
cn.Open();
sql = "select * from asada.view_tarifas";
cmd = new MySqlCommand(sql, cn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
trf = new Tarifa();
trf.Code = reader.GetString(0);
trf.FixAmount = float.Parse(reader.GetString(1));
trf.MetAmount = float.Parse(reader.GetString(2));
trf.RateHidrant = float.Parse(reader.GetString(3));
trf.TypeAppString = reader.GetString(4);
lisTrf.Add(trf);
}
reader.Close();
return lisTrf;
}
}
catch (Exception)
{
throw;
}
}
public void napuni()
{
Baza baza = new Baza();
string kabinet, mejl, konsultacije = "";
string biografija = "Нема описа.";
string aktivnost = "Нема описа.";
upit = "SELECT * FROM profesori WHERE idprofesor = " + id;
using (reader = baza.izvrsiUpit(upit))
{
while (reader.Read())
{
if (reader.GetString("biografija") != "") biografija = reader.GetString("biografija");
if (reader.GetString("aktivnost") != "") aktivnost = reader.GetString("aktivnost");
profesor = new ProfesorC(reader.GetString("idProfesor"), reader.GetString("imePrezime"), reader.GetString("titula"), reader.GetString("zvanje"), reader.GetString("slika"), reader.GetString("konsultacije"), reader.GetString("kabinet"), reader.GetString("mail"), biografija, aktivnost);
gridProfesor.DataContext = profesor;
}
}
upit1 = "SELECT * FROM predmeti WHERE id_predm IN (SELECT id_predm FROM pplink WHERE id_prof = " + id + ")";
using (reader = baza.izvrsiUpit(upit1))
{
while (reader.Read())
{
Predmet p = new Predmet(reader.GetString("id_predm"), reader.GetString("naziv"));
lvPredmeti.Items.Add(p);
}
}
}
public Values getValues(int id)
{
Values v = new Values();
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
string sqlString = $"SELECT * FROM products WHERE id ={id}";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySQLReader = cmd.ExecuteReader();
if (mySQLReader.Read())
{
v.id = mySQLReader.GetInt32(0);
v.productName = mySQLReader.GetString(1);
v.productCategory = mySQLReader.GetString(2);
v.productDescription = mySQLReader.GetString(3);
v.productImage = mySQLReader.GetString(4);
return(v);
}
else
{
return(null);
}
}
public List<TournamentModel> GetAvailableTournament()
{
oMySQLData.MySqlConnection oCon = new oMySQLData.MySqlConnection("Server=localhost;Database=betting;Uid=root;Pwd=Mysqlm@rch101984;");
oCon.Open();
string fetchQuery = "SELECT * FROM tbltournaments";
List<TournamentModel> tournaments = new List<TournamentModel>();
oMySQLData.MySqlCommand cmd = new oMySQLData.MySqlCommand(fetchQuery, oCon);
cmd.ExecuteNonQuery();
oMySQLData.MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
TournamentModel tournament = new TournamentModel();
tournament.TournamentID = Convert.ToInt32(reader["fldTournamentID"]);
tournament.TournamentName = reader["fldTournamentName"].ToString();
tournament.TournamentDate = Convert.ToDateTime(reader["fldTournamentDate"]);
tournaments.Add(tournament);
}
oCon.Close();
return tournaments;
}
private static void show(MySqlDataReader mySqlDataReader)
{
Console.WriteLine ("show");
while (mySqlDataReader.Read()) {
showRow (mySqlDataReader);
}
}
//when check name and password, get the user's information
public PropertyClass CheckNameAndPasswd(string name,string password)
{
PropertyClass accountClass=null;
string strSql="select * from account where accountName='"+name.Trim()+"' and accountPassword='******'";
try{
sdr=db.GetDataReader(strSql);
sdr.Read();
if(sdr.HasRows)
{
accountClass=new PropertyClass();
accountClass.UserId=sdr.GetInt32("id");
accountClass.AccountName=name.Trim();
accountClass.AccountPassword=password.Trim();
accountClass.Email=sdr["email"].ToString();
accountClass.Job=sdr.GetInt32("job");;
accountClass.Superviser=sdr.GetInt32("superviser");
}
}
catch(Exception ex)
{
}
finally
{
sdr.Close();
}
return accountClass;
}
public Drink loadDrinkDetails(int drinkId)
{
Drink p = new Drink();
MySqlConnection conn = mysqlDbConnect.GetConnection();
MySql.Data.MySqlClient.MySqlDataReader mySqlReade = null;
String sqlString = "SELECT * FROM drink WHERE drinkId='" + drinkId + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReade = cmd.ExecuteReader();
if (mySqlReade.Read())
{
p.drinkId = mySqlReade.GetInt32(0);
p.drinkName = mySqlReade.GetString(1);
p.drinkPrice = mySqlReade.GetInt32(2);
return(p);
}
else
{
return(null);
}
}
protected void Page_Load(object sender, EventArgs e)
{
nid = Request.QueryString["news_id"];
try
{
con.Open();
cmd = new MySqlCommand("select * from news where newsid='" + nid + "'", con);
dr = cmd.ExecuteReader();
if (dr.Read())
{
article.Text = "<h1 style='font-size:25px'>" + dr.GetString("headline").Substring(0, 1).ToUpper() + dr.GetString("headline").Substring(1).ToLower() + "</h1><br/><br/><img alt='' src='../images/tag.png' /> Alumni News<br/><br/>";
article.Text += " <p style='text-align: justify;'>" + dr.GetString("story") + "</p>";
article.Text += "<p style='text-align: justify;'><a href='http://alumni.ssn.edu.in/alumni-stories.php'>http://alumni.ssn.edu.in/alumni-stories.php</a></p>";
dr.Close();
con.Close();
}
else
{
rerr.Visible = true;
}
}
catch (Exception ex)
{
CreateLogFile log = new CreateLogFile();
log.ErrorLog(Server.MapPath("../Logs/Errorlog"), "Page load method of NewsRead page for " + Session["loginname"] + ":" + ex.Message);
}
}
void LogIn(NetworkMessage netMsg, InfoMessage msg)
{
bool loggedIn = false;
string query = "SELECT * FROM shogi.players WHERE players_email = \"" + msg.email + "\"";
if (con.State.ToString () != "Open")
con.Open ();
using (con) {
using (cmd = new MySqlCommand (query, con)) {
rdr = cmd.ExecuteReader();
if (rdr.HasRows) {
rdr.Read ();
if (msg.password == rdr["players_password"].ToString ()) {
incomeMessages.text += netMsg.conn.address + " logged in as " + msg.email + "\n";
loggedIn = true;
}
else
incomeMessages.text += netMsg.conn.address + " failed logging in for wrong password " + msg.password + "\n";
}
else
incomeMessages.text += netMsg.conn.address + " failed logging in for no entry " + msg.email + "\n";
rdr.Dispose ();
}
string queryIP = "UPDATE shogi.players SET players_ip = \"" + netMsg.conn.address + "\" WHERE players_email = \"" + msg.email + "\"";
if (loggedIn) {
using (cmd = new MySqlCommand (queryIP, con))
cmd.ExecuteNonQuery ();
msg.goal = "loggedin";
NetworkServer.SendToClient(netMsg.conn.connectionId, MyMsgType.Info, msg);
}
}
}
protected void btn_show_Click(object sender, EventArgs e)
{
conn = new MySql.Data.MySqlClient.MySqlConnection(ConnString);
conn.Open();
try
{
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM assigned_employee where reqid='" + tb_reqid.Text + "'";
reader = cmd.ExecuteReader();
//int x =reader.ro;
//Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('COUNT IS '+'"+x+"');</script>");
//("COUNT IS " + x);
//DataTable dt = new DataTable();
//dt.Load(reader);
//int numberOfResults = dt.Rows.Count;
//Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('COUNT IS '+'" + numberOfResults + "');</script>");
//for (int z=0; z< numberOfResults; z++)
//{
while (reader.Read())
{
tb_assignedto.Text = tb_assignedto.Text + reader.GetString("name") + "\n";
}
//}
}
catch { }
conn.Close();
}
public Customer getCustomer(long contactNo)
{
Customer p = new Customer();
MySqlConnection conn = mysqlDbConnect.GetConnection();
MySql.Data.MySqlClient.MySqlDataReader mySqlReade = null;
String sqlString = "SELECT * FROM customer WHERE contactNo = '" + contactNo.ToString() + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReade = cmd.ExecuteReader();
if (mySqlReade.Read())
{
p.contactNo = mySqlReade.GetInt32(0);
p.firstName = mySqlReade.GetString(1);
p.lastName = mySqlReade.GetString(2);
p.email = mySqlReade.GetString(3);
p.address = mySqlReade.GetString(4);
p.password = mySqlReade.GetString(5);
return(p);
}
else
{
return(null);
}
}
public static ArrayList MySQL_Column(string server, string username, string password, string database, string table, string port = "3306")
{
//Ip+端口+数据库名+用户名+密码
string connectStr = "server=" + server + ";port=" + port + ";database=information_schema" + ";user="******";password="******";";
ArrayList columns = new ArrayList();
MySqlConnection conn = new MySqlConnection(connectStr);;
try
{
conn.Open();//跟数据库建立连接，并打开连接
string sql = "select column_name from information_schema.columns where table_schema='" + database + "' and table_name='" + table + "'";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = cmd.ExecuteReader();
while (msqlReader.Read())
{ //do something with each record
columns.Add(msqlReader[0]);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
conn.Clone();
}
return(columns);
}
public ArrayList getCustomer()
{
ArrayList CustomerArray = new ArrayList();
MySqlConnection conn = mysqlDbConnect.GetConnection();
MySql.Data.MySqlClient.MySqlDataReader mySqlReade = null;
String sqlString = "SELECT * FROM customer";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReade = cmd.ExecuteReader();
while (mySqlReade.Read())
{
Customer p = new Customer();
p.contactNo = mySqlReade.GetInt32(0);
p.firstName = mySqlReade.GetString(1);
p.lastName = mySqlReade.GetString(2);
p.email = mySqlReade.GetString(3);
p.address = mySqlReade.GetString(4);
p.password = mySqlReade.GetString(5);
CustomerArray.Add(p);
}
return(CustomerArray);
}
// retorna o valor da Id para salvar os dados e manter a referencia do sumario
public int BuscarIdSumario(int IdPlano)
{
try
{
string QueryPesquisarIdSumario = "select * from sumarioexecutivo where id_Plano=@IdPlano;";
int idSumario=0;
Dal.ConectarBanco();
ComandoPesquisarIdSumario = new MySqlCommand(QueryPesquisarIdSumario);
ComandoPesquisarIdSumario.Connection = Dal.Conn;
ComandoPesquisarIdSumario.Parameters.AddWithValue("@IdPlano", IdPlano);
ConsultarIdSumario = ComandoPesquisarIdSumario.ExecuteReader();
while (ConsultarIdSumario.Read())
{
idSumario = int.Parse(ConsultarIdSumario["id"].ToString());
}
return idSumario;
}
catch
{
int idSumario = 0;
return idSumario;
}
finally
{
ConsultarIdSumario.Close();
Dal.FecharConexao();
}
}
public bool deleteCustomer(long contactNo)
{
Customer p = new Customer();
MySqlConnection conn = mysqlDbConnect.GetConnection();
MySql.Data.MySqlClient.MySqlDataReader mySqlReade = null;
String sqlString = "SELECT * FROM customer WHERE contactNo = '" + contactNo.ToString() + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReade = cmd.ExecuteReader();
if (mySqlReade.Read())
{
mySqlReade.Close();
sqlString = "DELETE FROM customer WHERE contactNo = '" + contactNo.ToString() + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
return(true);
}
else
{
return(false);
}
}
// This is used for saving the messages to the database.
// The messages need to have correct userIDs - used to establish who sent them - and correct caseIdDs - used to establish which case the message belongs to.
public String[] getUserIDcaseID()
{
// Array is tidier than multiple strings, make use of indexes.
String[] IDs = new String[2];
IDs[0] = uID; // Populated with the Session method during Page_Load - no need to query DB for this.
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["projectConnectionString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
// Use case_name to obtain case ID from the allcases table.
queryStr = "SELECT case_id FROM project.allcases WHERE case_name=?cname";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.Parameters.AddWithValue("?cname", case_name);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
// Grab caseID.
IDs[1] = reader.GetString(reader.GetOrdinal("case_id"));
}
reader.Close();
// Return the array, therefore method can be called and assigned to varaible due to return - efficient.
return(IDs);
}
private bool DoSQLQueryTest()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
String queryStr = "";
queryStr = "SELECT COUNT(id) as countindex FROM database.metadata WHERE metadata.codeuser='******' ";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
countindex = 0;
while (reader.HasRows && reader.Read())
{
countindex = reader.GetInt32(reader.GetOrdinal("countindex"));
}
if (countindex != 0)
{
return(true);
}
reader.Close();
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(false);
}
//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(); }
}
private void DoSQLCountComment()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
String queryStr = "";
queryStr = "SELECT COUNT(id) as countcom FROM database.comment WHERE comment.codeuser='******' AND comment.idmetadata= (SELECT id from database.metadata WHERE metadata.codeuser='******') ";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
count = "";
while (reader.HasRows && reader.Read())
{
count = reader.GetString(reader.GetOrdinal("countcom"));
}
if (reader.HasRows)
{
LaCountComment.Text = count;
}
reader.Close();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
//funcionando
public int BuscarIdPlanoDeMarketing(int IdPlano)
{
try
{
string QueryPesquisarIdSumario = "select * from planodemarketing where id_Plano=@IdPlano;";
int idSumario = 0;
Dal.ConectarBanco();
ComandoPesquisarIdPlanoDeMarketing = new MySqlCommand(QueryPesquisarIdSumario);
ComandoPesquisarIdPlanoDeMarketing.Connection = Dal.Conn;
ComandoPesquisarIdPlanoDeMarketing.Parameters.AddWithValue("@IdPlano", IdPlano);
ReaderPEsquisarIdPlanoDeMarketing = ComandoPesquisarIdPlanoDeMarketing.ExecuteReader();
while (ReaderPEsquisarIdPlanoDeMarketing.Read())
{
idSumario = int.Parse(ReaderPEsquisarIdPlanoDeMarketing["id"].ToString());
}
return idSumario;
}
catch
{
int idSumario = 0;
return idSumario;
}
finally
{
ReaderPEsquisarIdPlanoDeMarketing.Close();
Dal.FecharConexao();
}
}
/// <summary>
/// Check if the account exist in the database.
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
public void CheckUserAccount(string username, string password)
{
Reader.Close();
try
{
CheckUser:
{
Reader = mysql_command.ExecuteReader(); // Initalize our reader
if (!Reader.IsClosed)
{
if (Reader.Read())
{ // Reader is reading so we must close it once its finished
if (username == (EngineDatabase.DatabaseManager.Reader["user"].ToString())
&& password == (EngineDatabase.DatabaseManager.Reader["password"].ToString())) // Check the user in the database
{
id = (Int32)Reader["id"]; // Store the player's id into Int32 var
Player[id].id = (Int32)Reader["id"]; ; // Store the player's id into player struct
Player[id].isFirstLogin = (Int32)Reader["first_login"]; // Store the first_login boolean into the player struct
switch (Player[id].isFirstLogin) // Check if the player already connected before
{
case 0: // He did not connected before
InsertEntries(); // Create a new account in the database (ingame part is still in progress...)
break;
case 1: // He connected before
ReadEntries(); // Load existing account data
break;
}
}
else {
Player[id].isConnected =
false;
}
mysql_connection.Close();
}
}
}
switch(mysql_connection.State)
{
case ConnectionState.Open:
goto CheckUser; // Check user account
case ConnectionState.Closed:
if (Player[id].isConnected)
error("User already logged."); // Well its not considered as error anymore...
else {
mysql_connection.Open();
goto CheckUser; // Check user account
}
break;
case ConnectionState.Broken:
error("Connection between MySQL and StreetEngine is broken.");
break;
}
}
catch (Exception x) { error(x.ToString()); }
}
protected void Page_Load(object sender, EventArgs e)
{
topics_ids = new ArrayList();
dbConnection = new MySqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());
dbConnection.Open();
slctTopics = new MySqlCommand("SELECT t_id FROM topics", dbConnection);
dReaderTopics = slctTopics.ExecuteReader();
while (dReaderTopics.Read())
{
topics_ids.Add(dReaderTopics[0].ToString());
counter++;
}
dReaderTopics.Close();
slctTopics.Dispose();
while (i < counter)
{
updtTopicsNumberOfVideos = new MySqlCommand("UPDATE topics SET t_nb_videos=(SELECT count(*) FROM videos WHERE t_id=" + topics_ids[i].ToString() + ") WHERE t_id=" + topics_ids[i].ToString(), dbConnection);
updtTopicsNumberOfVideos.ExecuteNonQuery();
updtTopicsNumberOfVideos.Dispose();
i++;
}
}
public LoginCode CheckLogin(string username, string password)
{
string tusername = "", tpassword = "";
string sql = "SELECT * from user where user='******'";
MySqlCommand cmd = new MySqlCommand(sql, this.myCon);
myCon.Open();
myRead = cmd.ExecuteReader();
myCon.Close();
while (myRead.Read())
{
tusername = myRead.GetString("username");
tpassword = myRead.GetString("password");
}
if (tusername != null)
{
if (tpassword == password)
{
return LoginCode.LOGIN_SUCCESS;
}
}
else
{
}
return LoginCode.PASSWORD_INCORRECT;
}
public List <string> ChatGetUsername()
{
//string chatInfo;
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ConnectionString;
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(queryString, conn);
List <string> storeDate = new List <string>();
queryString = "SELECT username FROM dububase.chat";
cmd.CommandText = queryString;
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
storeDate.Add((reader["username"].ToString()));
}
return(storeDate);
}
catch (System.Data.SqlClient.SqlException ex)
{
string errorMsg = "Error";
errorMsg += ex.Message;
throw new Exception(errorMsg);
}
finally
{
reader.Close();
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 int Login(TextBox utilizador, TextBox password)
{
Conetar();
int valor = 0;
string query = "SELECT * FROM conta WHERE utilizador='" + utilizador.Text + "' AND password='******';";
_cmdDataBase = new MySqlCommand(query, _conDataBase);
try
{
_conDataBase.Open();
_myReader = _cmdDataBase.ExecuteReader();
while (_myReader.Read())
{
}
if (_myReader.HasRows)
{
MessageBox.Show("Login Correto");
valor = 1;
}
else
{
MessageBox.Show("Login Incorreto! Volte a Introduzir as suas Credenciais");
valor = 0;
}
_myReader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return valor;
}
private void RetreiveProduct()
{
///Get product list from database
try
{
CheckStateDB();
String sqlGet = "SELECT product_id FROM product";
cmd = new MySqlCommand(sqlGet, conn);
reader = cmd.ExecuteReader();
if (reader.HasRows == false)
{
throw new Exception("No row were found!");
}
else
{
while (reader.Read())
{
this.prodList.Items.Add(reader.GetString("product_id"));
}
}
reader.Close();
}
catch (Exception ex)
{
ErrorLogCreate(ex);
LockWindow(true);
}
}
private void DoSQLAffiche1()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT * FROM database.event WHERE event.iduser='******' AND event.idex='2'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
namec1 = reader.GetString(reader.GetOrdinal("name"));
monthstart1 = reader.GetInt32(reader.GetOrdinal("monthstart"));
yearstart1 = reader.GetInt32(reader.GetOrdinal("yearstart"));
daystart1 = reader.GetInt32(reader.GetOrdinal("daystart"));
yearend1 = reader.GetInt32(reader.GetOrdinal("yearend"));
monthend1 = reader.GetInt32(reader.GetOrdinal("monthend"));
dayend1 = reader.GetInt32(reader.GetOrdinal("dayend"));
hstart1 = reader.GetInt32(reader.GetOrdinal("hstart"));
hend1 = reader.GetInt32(reader.GetOrdinal("hend"));
minstart1 = reader.GetInt32(reader.GetOrdinal("minstart"));
minend1 = reader.GetInt32(reader.GetOrdinal("minend"));
}
Labelnamec1.Text = namec1;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
reader.Close();
conn.Close();
}
List <UserBetModel> GetUserBetList(int userID, int tournamentID)
{
oCon.Open();
string fetchQuery = "SELECT * FROM view_userbetdetails WHERE fldBettorID = " + userID + " AND fldTournamentID = " + tournamentID;
List <UserBetModel> bets = new List <UserBetModel>();
oMySQLData.MySqlCommand cmd = new oMySQLData.MySqlCommand(fetchQuery, oCon);
cmd.ExecuteNonQuery();
oMySQLData.MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
UserBetModel bet = new UserBetModel();
bet.BettorID = Convert.ToInt32(reader["fldBettorID"]);
bet.TournamentID = Convert.ToInt32(reader["fldTournamentID"]);
bet.MatchID = Convert.ToInt32(reader["fldMatchID"]);
bet.TeamID = Convert.ToInt32(reader["fldTeamBetID"]);
bet.PlaceBet = Convert.ToInt32(reader["fldPlaceBetPoints"]);
bets.Add(bet);
}
oCon.Close();
return(bets);
}
//When Login has been Pressed
protected void SubmitEventMethod(object sender, EventArgs e)
{
string username = Username.Text;
string pass = Password.Text;
query = "SELECT * FROM 17agileteam6db.users WHERE staff_no ='" + username + "' AND pass = '******';"; //checks user name and password
DB db = new DB();
reader = db.Select(query);
while (reader.HasRows && reader.Read())
{
Session["role"] = reader.GetString(reader.GetOrdinal("role"));
Session["firstName"] = reader.GetString(reader.GetOrdinal("first_name"));
Session["lastName"] = reader.GetString(reader.GetOrdinal("last_name"));
}
if (reader.HasRows)
{
Session["loggedin"] = "Loggedin";
Session["StaffNo"] = Username.Text;
Response.BufferOutput = true;
Response.Redirect("Index.aspx", false);
}
else
{
//If username and password not found return false
Session["failed"] = "failed";
Response.Redirect("Login.aspx", false);
}
reader.Close();
}
public static void show(MySqlDataReader mySqlDataReader)
{
// METODO QUE MUESTRA TODO EL CONETNIDO DE LA TABLA
Console.WriteLine ("FILAS : "); // EL SHOW MUESTRA EL SHOWROW (FILA). EL SHOWROW MUESTRA LA FILA
while (mySqlDataReader.Read())
showRow (mySqlDataReader);
}
public List <Person> getPersons()
{
List <Person> personList = new List <Person>();
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
string sqlString = "SELECT * from tbl_personnel";
var cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
try
{
mySqlReader = cmd.ExecuteReader();
while (mySqlReader.Read())
{
Person p = new Person();
p.ID = mySqlReader.GetInt32(0);
p.FirstName = mySqlReader.GetString(1);
p.LastName = mySqlReader.GetString(2);
p.PayRate = mySqlReader.GetFloat(3);
p.StartDate = mySqlReader.GetDateTime(4);
p.EndDate = mySqlReader.GetDateTime(5);
personList.Add(p);
}
}
catch (Exception ex)
{
Console.WriteLine("MySQL exception");
Console.WriteLine(ex);
Console.WriteLine(ex.Data);
}
return(personList);
}
public static void PopulateListView(ListView lst, String strQuery)
{
ListViewItem lstItem = null;
int x;
lst.Items.Clear();
conn.Open();
comm.Connection = conn;
comm.CommandText = strQuery;
reader = comm.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
lstItem = lst.Items.Add(reader.GetValue(0).ToString());
for (x = 1; x < reader.FieldCount; x++)
{
lstItem.SubItems.Add(reader.GetValue(x).ToString());
}
}
}
conn.Close();
}
private void ReviewRecipeData(String recId)
{
///Get recipe data from database by recipe id
try
{
CheckStateDB();
String sql_get = "SELECT recipe_name, detail, materialCode FROM recipe WHERE recipe_id = @recipe_id";
cmd = new MySqlCommand(sql_get, conn);
cmd.Parameters.AddWithValue("@recipe_id", recId);
reader = cmd.ExecuteReader();
while (reader.Read())
{
this.recipeName.Text = reader.GetString("recipe_name");
this.recipeDetail.Text = reader.GetString("detail");
this.recipeMaterial.Text = reader.GetString("materialCode");
}
reader.Close();
}
catch (Exception e)
{
ErrorLogCreate(e);
MessageBox.Show("เกิดข้อผิดพลาด ข้อมูล error บันทึกอยู่ในไฟล์ log กรุณาแจ้งข้อมูลดังกล่าวแก่ทีมติดตั้ง"
, "ข้อผิดพลาด", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
public bool deletePerson(long ID)
{
bool recordDeleted = false;
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
string sqlString = "SELECT * from tbl_personnel where ID = " + ID.ToString();
var cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
try
{
mySqlReader = cmd.ExecuteReader();
if (mySqlReader.Read())
{
mySqlReader.Close();
sqlString = "DELETE FROM tbl_personnel where ID = " + ID.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
recordDeleted = true;
}
}
catch (Exception ex)
{
Console.WriteLine("SQL Exception in Delete");
Console.WriteLine(ex);
Console.WriteLine(ex.Data);
}
return(recordDeleted);
}
public List<Sector> LoadSector()
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon=new Connection(this.user)).Parameters()))
{
listSec = new List<Sector>();
cn.Open();
sql = "select * from asada.view_sectores";
cmd = new MySqlCommand(sql, cn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
sec = new Sector();
sec.Code = reader.GetString(0);
sec.Consecutive = reader.GetInt32(1);
sec.Description = reader.GetString(2);
listSec.Add(sec);
}
reader.Close();
return listSec;
}
}
catch (Exception)
{
throw;
}
}
protected void LoginUser(object sender, EventArgs e)
{
string connectionString = @"Data Source=db4free.net; Database=centresportif420; user=centresportif420; password=stephane420;";
using (MySqlConnection cn = new MySqlConnection(connectionString))
{
cn.Open();
queryStr = "SELECT * FROM centresportif420.personne WHERE codebarre='" + Server.HtmlEncode(((TextBox)(Login1.FindControl("UserName"))).Text) + "' AND motdepasse='" + Server.HtmlEncode(((TextBox)(Login1.FindControl("Password"))).Text) + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, cn);
reader = cmd.ExecuteReader();
name = "";
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("nom"));
role = reader.GetString(reader.GetOrdinal("role"));
idpersonne = reader.GetString(reader.GetOrdinal("idpersonne"));
Session["idpersonne"] = idpersonne;
Session["urole"] = role;
}
if (reader.HasRows)
{
Session["uname"] = name;
Response.BufferOutput = true;
Response.Redirect("~/Account/Membre.aspx", false);
FormsAuthentication.SetAuthCookie(Server.HtmlEncode(((TextBox)(Login1.FindControl("UserName"))).Text), true);
}
else
{
Response.Redirect("~/Account/Login.aspx", false);
}
reader.Close();
cn.Close();
}
}
public dynamic GetUserBetPointsDetails(int userID)
{
oCon.Open();
string fetchQuery = "SELECT * FROM view_userdetails WHERE UserID = " + userID;
List <UserPointsModel> userbets = new List <UserPointsModel>();
oMySQLData.MySqlCommand cmd = new oMySQLData.MySqlCommand(fetchQuery, oCon);
cmd.ExecuteNonQuery();
oMySQLData.MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
UserPointsModel userbet = new UserPointsModel();
userbet.UserID = Convert.ToInt32(reader["UserID"]);
userbet.TournamentID = Convert.ToInt32(reader["TournamentID"]);
userbet.TotalPoints = Convert.ToInt32(reader["TotalBetPoints"]);
userbet.TournamentPoints = Convert.ToInt32(reader["TournamentPoints"]);
userbets.Add(userbet);
}
oCon.Close();
return(userbets);
}
public List <EUsuario> getAll()
{
EUsuario objUser = null;
List <EUsuario> lista = new List <EUsuario>();
string sql = "SELECT * FROM USUARIOS ";
using (conexion cnx = new conexion())
{
cnx.cadena = ConfigSAE.Instanciar.cadenaSAE();
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand())
{
cmd.CommandText = sql;
cmd.Connection = cnx.getConexion();
if (cnx.abrirConexion())
{
MySql.Data.MySqlClient.MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
objUser = mapearObjeto(dr);
lista.Add(objUser);
}
cnx.cerrarConexion();
}
}
return(lista);
}
}
private static void show(MySqlDataReader mysqlDataReader)
{
Console.WriteLine ();
while (mysqlDataReader.Read()) {
Console.WriteLine ("{0} {1}", mysqlDataReader ["id"], mysqlDataReader ["nombre"]);
}
}
//管理员创建用户
public int registUser(UserBean bean)
{
try
{
string selectSql = "select * from users as u where u.ACCOUNT='" + @bean.Account + "'";
reade = MySqlHelper.ExecuteReader(MySqlHelper.Conn, CommandType.Text,selectSql, new MySqlParameter("@bean.Account", bean.Account));
if (reade.Read()) return HAS;
string pawMd = Session.MD5Encrypt(bean.Password);
string sql = "insert into users(ACCOUNT,USER_NAME,TYPE,PASSWORD,STATUS,CREATE_ID,CREATE_TIME,PHONE) values('" +
@bean.Account + "','" + @bean.UserName + "'," + @bean.Type + ",'" + @pawMd + "'," + @bean.Status + "," +
@bean.CreateId + ",'" + @bean.CreateTime + "','"[email protected]+"')";
MySqlParameter[] parameter =
{
new MySqlParameter("@bean.Account",bean.Account),
new MySqlParameter("@bean.UserName",bean.UserName),
new MySqlParameter("@bean.Type",bean.Type),
new MySqlParameter("@pawMd",pawMd),
new MySqlParameter("@bean.Status",bean.Status),
new MySqlParameter("@bean.CreateId",bean.CreateId),
new MySqlParameter("@bean.CreateTime",bean.CreateTime.ToString("yyyy-MM-dd hh:mm:ss")),
new MySqlParameter("@bean.Phone",bean.Phone),
};
MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn, CommandType.Text,sql, parameter);
}
catch (Exception e)
{
e.GetBaseException();
return SYSTEM_EXCEPTION;
}
return SUCCESS;
}
private void DoSQLQuery1()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT * FROM database.tbl_user WHERE tbl_user.id='" + Session["Id"] + "' ";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
education = reader.GetString(reader.GetOrdinal("education"));
localisation = reader.GetString(reader.GetOrdinal("localisation"));
}
if (reader.HasRows)
{
// Session["uname"] = name;
// userName = (String)(Session["utname"]);
// Label14.Text = userName;
LabelEducation.Text = education;
LabelLocation.Text = localisation;
}
reader.Close();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public EParametros getParamentro()
{
EParametros objPar = null;
// List<EParametros> lista = new List<EParametros>();
string sql = "SELECT * FROM afparametros WHERE codigo = 1";
using (conexion cnx = new conexion())
{
cnx.cadena = Configuracion.Instanciar.conexionBD();
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand())
{
cmd.CommandText = sql;
cmd.Connection = cnx.getConexion();
if (cnx.abrirConexion())
{
MySql.Data.MySqlClient.MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
objPar = mapearObjeto(dr);
//lista.Add(objPar);
}
cnx.cerrarConexion();
}
}
return(objPar);
}
}
public System.Data.DataTable DataReaderToDataTable(MySqlDataReader Reader)
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.DataColumn dc;
System.Data.DataRow dr;
ArrayList arr = new ArrayList();
int i;
for(i=0;i<Reader.FieldCount;i++)
{
dc = new System.Data.DataColumn();
dc.ColumnName = Reader.GetName(i);
arr.Add(dc.ColumnName);
dt.Columns.Add(dc);
}
while(Reader.Read())
{
dr = dt.NewRow();
for (i=0;i<Reader.FieldCount;i++)
{
dr[(string)arr[i]] = Reader[i].ToString();
}
dt.Rows.Add(dr);
}
Reader.Close();
return dt;
}
private void DoSQLQueryPieFournisseur()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT * FROM database.detail WHERE detail.t1='" + Session["AnnPieFournisseur"] + "' AND detail.titre='Solde fournisseurs'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
fournisseur = reader.GetString(reader.GetOrdinal("t1"));
DebitF1 = reader.GetInt32(reader.GetOrdinal("t2"));
CreditF1 = reader.GetInt32(reader.GetOrdinal("t3"));
SoldeF1 = reader.GetInt32(reader.GetOrdinal("t4"));
}
reader.Close();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
private void DoSQLQueryPieBanque()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT * FROM database.detail WHERE detail.titre='Solde banque'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
BIATD = reader.GetInt32(reader.GetOrdinal("t1"));
BIATE = reader.GetInt32(reader.GetOrdinal("t2"));
STB = reader.GetInt32(reader.GetOrdinal("t3"));
}
reader.Close();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public void LoadTreeView()
{
treeView1.Nodes.Clear();
//-----------------------------------------------Fill first level--------------------------------------------------------------------
con.SqlQuery("SELECT * FROM TEAMS ");
reader = con.QueryEx();
while (reader.Read())
{
treeView1.Nodes.Add(reader["Id"].ToString(),reader["Name"].ToString());
}
con.ConnectionClose();
//------------------------------------------------------Fill second level-------------------------------------------------------------
con.SqlQuery("SELECT * FROM workers ");
reader = con.QueryEx();
while (reader.Read())
{
treeView1.Nodes[reader["TeamId"].ToString()].Nodes.Add(reader["Id"].ToString(), reader["Name"].ToString());
}
con.ConnectionClose();
//------------------------------------------------------------Fill third level-------------------------------------------------------
con.SqlQuery("SELECT workers.TeamId, tasks.WorkerId, tasks.Name, tasks.Id FROM tasks INNER JOIN workers on tasks.WorkerId = workers.Id ");
reader = con.QueryEx();
while (reader.Read())
{
treeView1.Nodes[reader["TeamId"].ToString()].Nodes[reader["WorkerId"].ToString()].Nodes.Add(reader["Id"].ToString(), reader["Name"].ToString());
}
con.ConnectionClose();
}
public bool IsNameExist(string name)
{
string strSql="select * from account where accountName='"+name.Trim()+"'";
try{
sdr=db.GetDataReader(strSql);
sdr.Read();
if(sdr.HasRows)
{
sdr.Close();
return true;
}
else
{
sdr.Close();
return false;
}
}
catch(Exception e)
{
}
finally
{
sdr.Close();
}
return true;
}
public void add_date_firstDay(string date, int line, string first, string sec, string thi, string four, string fiv, string six, string sev, string eig, string nin, string ten, string ele,string twe)
{
DateTime dt = Convert.ToDateTime(date);
//string connect = "datasource = 127.0.0.1; port = 3306;Connection Timeout=30; Min Pool Size=20; Max Pool Size=200; username = root; password = ;";
MySqlConnection conn = new MySqlConnection(connect);
MySqlCommand sda = new MySqlCommand(@"insert into shedulling.tablelayout1 values
('" + dt + "','" + line + "','" + first + "','" + sec + "','" + thi + "','" + four + "','" + fiv + "','" + six + "','" + sev + "','" + eig + "','" + nin + "','" + ten + "', '" + ele + "','"+twe+ "')", conn);
MySqlDataReader reader;
try
{
conn.Open();
reader = sda.ExecuteReader();
while (reader.Read())
{
}
reader.Close();
conn.Close();
conn.Dispose();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
