public int InserirPedido(int numCliente, List<Item> pedido)
{
int NumPedido = 0;
int idPedido = 0;
MySqlConnection conn = new MySqlConnection(connectionString);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
conn.Open();
cmd.CommandText = "Select Max(numero) + 1 from tb_Pedidos";
NumPedido = int.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = "Insert into tb_Pedidos (numero, id_cliente, data) Values(" + NumPedido + "," + numCliente + ", sysdate()); select Max(id) from tb_Pedidos;";
idPedido = int.Parse(cmd.ExecuteScalar().ToString());
foreach (Item item in pedido)
{
cmd.CommandText = "insert into tb_items (nome, descricao, preco, quantidade, id_pedido, urlImagem) Values ('" + item.descricao + "', Null,"+ item.preco.ToString().Replace(",",".") + "," + item.quantidade + "," + idPedido + ", Null);";
cmd.ExecuteNonQuery();
}
conn.Close();
return NumPedido;
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int Update(Policy entity)
{
string sql = "UPDATE tb_policy SET agentType=@agentType,subject=@subject,content=@content,sender=@sender,attachment=@attachment,attachmentName=@attachmentName,creatTime=@creatTime,";
sql = sql + " type=@type,validateStartTime=@validateStartTime,validateEndTime=@validateEndTime,isValidate=@isValidate,isDelete=@isDelete,deleteTime=@deleteTime,toAll=@toAll where sequence=@sequence ";
//string sql = "UPDATE cimuser SET userNickName=@userNickName WHERE userid=@userid";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@agentType", entity.agentType);
command.Parameters.AddWithValue("@sequence", entity.sequence);
command.Parameters.AddWithValue("@subject", entity.subject);
command.Parameters.AddWithValue("@content", entity.content);
command.Parameters.AddWithValue("@sender", entity.sender);
command.Parameters.AddWithValue("@attachment", entity.attachment);
command.Parameters.AddWithValue("@attachmentName", entity.attachmentName);
command.Parameters.AddWithValue("@creatTime", entity.creatTime);
command.Parameters.AddWithValue("@type", entity.type);
command.Parameters.AddWithValue("@validateStartTime", entity.validateStartTime);
command.Parameters.AddWithValue("@validateEndTime", entity.validateEndTime);
command.Parameters.AddWithValue("@isValidate", entity.isValidate);
command.Parameters.AddWithValue("@isDelete", entity.isDelete);
command.Parameters.AddWithValue("@deleteTime", entity.deleteTime);
command.Parameters.AddWithValue("@toAll", entity.toAll);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
protected void Button_click(object sender, EventArgs e)
{
MySqlConnection bazaPovezava = new MySqlConnection(bazaConnString);
try
{
bazaPovezava.Open();
string SQLcommand = "INSERT INTO User(username, firstname, lastname, password, email, city, country) VALUES(?un, ?fn, ?ln, ?pw, ?em, ?ci, ?co);";
MySqlCommand bazaUkaz = new MySqlCommand(SQLcommand, bazaPovezava);
bazaUkaz.Parameters.Add(new MySqlParameter("?un", username.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?fn", firstname.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?ln", surname.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?pw", pass.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?em", email.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?ci", city.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?co", country.Text));
bazaUkaz.ExecuteNonQuery();
}
catch(Exception ex)
{
Console.WriteLine(ex);
}
finally
{
bazaPovezava.Close();
}
}
public static void addgenres(string genre)
{
bool HasRows;
string MyConString = "SERVER=192.168.5.106;" +
"DATABASE=jukebox;" +
"UID=jukebox;" +
"PASSWORD=;";
MySqlConnection connection = new MySqlConnection(MyConString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "SELECT uid FROM genres WHERE genre='" + MySqlEscape(genre.Trim()) + "'";
Reader = command.ExecuteReader();
HasRows = Reader.HasRows;
Reader.Close();
connection.Close();
if (!HasRows)
{
connection.Open();
string sql = "INSERT INTO genres (genre) VALUES ('" + MySqlEscape(genre.Trim()) + "')";
command = new MySqlCommand(sql, connection);
command.ExecuteNonQuery();
connection.Close();
}
}
protected void Button2_Click(object sender, EventArgs e)
{
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection();
conn.ConnectionString = myConnectionString;
conn.Open();
MySqlCommand cmd3 = new MySqlCommand("INSERT INTO pokoje (id_pokoj, Nr_pokoj, Ilosc_lozek, Ilosc_osob, Typ_pokoj, Cena_pokoj) VALUES (@id_pokoj,@nrpokoj,@ilosclozek,@iloscosob,@typpokoj,@cenapokoj)", conn);
cmd3.CommandType = CommandType.Text;
cmd3.Parameters.AddWithValue("@nrpokoj", TextBox_up_Nr0.Text);
cmd3.Parameters.AddWithValue("@ilosclozek", TextBox_up_IL0.Text);
cmd3.Parameters.AddWithValue("@iloscosob", TextBox_up_IO0.Text);
cmd3.Parameters.AddWithValue("@typpokoj", DropDownList_up_Typ0.Text);
cmd3.Parameters.AddWithValue("@cenapokoj", TextBox_up_Cena0.Text);
cmd3.Parameters.AddWithValue("@id_pokoj", TextBox_up_Id0.Text);
cmd3.ExecuteNonQuery();
conn.Close();
//INSERT INTO pozycja_rezerwacji(Rezerwacja_id_rezerwacja, Pokoje_id_pokoj) VALUES (8, 7);
MySqlCommand cmd4 = new MySqlCommand("INSERT INTO pozycja_rezerwacji(Rezerwacja_id_rezerwacja, Pokoje_id_pokoj) VALUES (8, @id_pokoj)", conn);
cmd4.CommandType = CommandType.Text;
conn.Open();
cmd4.Parameters.AddWithValue("@id_pokoj", TextBox_up_Id0.Text);
cmd4.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Blad.Visible = true;
}
}
protected void Page_Load(object sender, EventArgs e)
{
string str = DB.dataConnect.connectStr;
MySqlConnection connect = new MySqlConnection(str);
connect.Open();
MySqlCommand com = new MySqlCommand("SELECT * from news", connect);
MySqlDataReader read = com.ExecuteReader();
while (read.Read())
{
string result = Label1.Text + "" + read.GetString(1) + "<a href='../Pages/shownews.aspx?title=" + read.GetString(1) + "'</a> อ่านต่อ...</a>";
Label1.Text = result+"<br/>";
}
com = new MySqlCommand("SELECT * from marquee", connect);
connect.Close();
connect.Open();
MySqlDataReader rd = com.ExecuteReader();
while (rd.Read())
{
string result = lblMarquee.Text + "" + rd.GetString(1) + "<a href='../Pages/showDetail.aspx?title=" + rd.GetString(1) + "'</a> อ่านต่อ... </a>";
}
}
public void alterUser(TextBox textBoxGammelPassord, TextBox textBoxEpost, TextBox textBoxNyPassord, TextBox textBoxAdresse, TextBox textBoxTelefon, TextBox textBoxID, String bondeID)
{
String dbconnect = myconnectionstring;
MySqlConnection dbconn = new MySqlConnection(dbconnect);
if (textBoxGammelPassord.Text == gammeltpassordLocal)
{
MySqlCommand cmd = dbconn.CreateCommand();
cmd.CommandText = "UPDATE login SET epost='" + textBoxEpost.Text + "', passord= '" + textBoxNyPassord.Text + "'WHERE bondeID= '" + bondeID + "'";
dbconn.Open();
cmd.ExecuteNonQuery();
dbconn.Close();
MySqlCommand cmd2 = dbconn.CreateCommand();
cmd2.CommandText = "UPDATE Kontakt SET adresse= '" + textBoxAdresse.Text + "', telefonnr= '" + textBoxTelefon.Text + "' WHERE bondeID= '" + bondeID + "'";
dbconn.Open();
cmd2.ExecuteNonQuery();
dbconn.Close();
MessageBox.Show(textBoxTelefon.Text);
getinfobruker(textBoxGammelPassord, textBoxEpost, textBoxNyPassord, textBoxAdresse, textBoxTelefon ,textBoxID, bondeID);
}
else
{
MessageBox.Show("Feil passord");
}
}
public void AddColumn(string tableName, string columnName, string columnDescription)
{
using (Connection = new MySqlConnection(GetConnectionString()))
{
var q1 = string.Format(CheckColumnQuery, tableName, columnName);
using (CheckColumnCommand = new MySqlCommand(q1, Connection))
{
Connection.Open();
var res = int.Parse(Convert.ToString(CheckColumnCommand.ExecuteScalar()));
Connection.Close();
if (res > 0)
{
return;
}
}
var q3 = string.Format(Query, tableName, columnName, columnDescription);
using (Command = new MySqlCommand(q3, Connection))
{
Command.CommandTimeout = 12 * 3600;
Connection.Open();
Command.ExecuteNonQuery();
Connection.Close();
}
var q2 = string.Format(IndexQuery, "Index" + columnName, tableName, columnName);
using (IndexCommand = new MySqlCommand(q2, Connection))
{
Connection.Open();
IndexCommand.ExecuteNonQuery();
Connection.Close();
}
}
}
public void InvalidCast()
{
MySqlConnection con = rootConn;
string sql = @"drop function if exists MyTwice; create function MyTwice( val int ) returns int begin return val * 2; end;";
MySqlCommand cmd = new MySqlCommand(sql, con);
cmd.ExecuteNonQuery();
cmd.CommandText = "drop procedure if exists spMyTwice; create procedure spMyTwice( out result int, val int ) begin set result = val * 2; end;";
cmd.ExecuteNonQuery();
try
{
cmd.CommandText = "drop user 'tester2'@'localhost'";
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
cmd.CommandText = "CREATE USER 'tester2'@'localhost' IDENTIFIED BY '123';";
cmd.ExecuteNonQuery();
cmd.CommandText = "grant execute on function `MyTwice` to 'tester2'@'localhost';";
cmd.ExecuteNonQuery();
cmd.CommandText = "grant execute on procedure `spMyTwice` to 'tester2'@'localhost'";
cmd.ExecuteNonQuery();
cmd.CommandText = "grant select on table mysql.proc to 'tester2'@'localhost'";
cmd.ExecuteNonQuery();
cmd.CommandText = "flush privileges";
cmd.ExecuteNonQuery();
MySqlConnection con2 = new MySqlConnection(
rootConn.ConnectionString);
con2.Settings.UserID = "tester2";
con2.Settings.Password = "******";
// Invoke the function
cmd.Connection = con2;
con2.Open();
cmd.CommandText = "MyTwice";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("val", System.DBNull.Value));
cmd.Parameters.Add("@p", MySqlDbType.Int32);
cmd.Parameters[1].Direction = ParameterDirection.ReturnValue;
cmd.Parameters[0].Value = 20;
cmd.ExecuteNonQuery();
con2.Close();
Assert.AreEqual(cmd.Parameters[1].Value, 40);
con2.Open();
cmd.CommandText = "spMyTwice";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add(new MySqlParameter("result", System.DBNull.Value));
cmd.Parameters.Add("val", MySqlDbType.Int32);
cmd.Parameters[0].Direction = ParameterDirection.Output;
cmd.Parameters[1].Value = 20;
cmd.ExecuteNonQuery();
con2.Close();
Assert.AreEqual(cmd.Parameters[0].Value, 40);
}
protected void submitMethod(object sender, EventArgs e)
{
try {
string constr = "Data Source=localhost; port=3306; Initial Catalog=ci2454_eb04539;User Id=eb04539;password=eb04539";// ConfigurationManager.ConnectionStrings["ci2454_eb04539ConnectionString"].ConnectionString;
MySqlConnection con = new MySqlConnection(constr);
con.Open();
string[] cantidad = Request.Form.GetValues("cantidad");
string[] medida = Request.Form.GetValues("medida");
string[] ingrediente = Request.Form.GetValues("ingrediente");
MySqlCommand cmd = new MySqlCommand("INSERT INTO recetas (nombre, instrucciones, creador, imagen, tips, categoria) " + "VALUES('"+ nombreReceta.Value + "','"+ instrucciones.Value + "','"+ "1" + "','" + imagen.Value + "','"+ tips.Value +"','"+ categorias.Value + "')", con);
cmd.ExecuteReader();
con.Close();
con.Open();
MySqlCommand cmd2 = new MySqlCommand("SELECT * FROM recetas Where imagen='"+imagen.Value+"'", con);
cmd2.Connection = con;
String idReceta = Convert.ToString(cmd2.ExecuteScalar());
System.Diagnostics.Debug.WriteLine(idReceta);
con.Close();
con.Open();
for (int i = 0; i < ingrediente.Length;++i) {
MySqlCommand cmd3 = new MySqlCommand("INSERT INTO ingredientes (nombre) VALUES('" + ingrediente[i] + "')");
cmd3.Connection = con;
cmd3.ExecuteReader();
}
// con.Open();
// for (int i = 0; i < ingrediente.Length;++i) {
// MySqlCommand cmd3 = new MySqlCommand("INSERT INTO rec_ing (receta, ingrediente, cantidad, unidadDeMedida) " + "VALUES('" + idReceta + "','" + ingrediente[i] + "','" + cantidad[i] + "','" + medida[i] + "')", con);
// cmd3.ExecuteReader();
// }
// con.Close();
} catch (NullReferenceException ex) {
System.Diagnostics.Debug.WriteLine("ERROR");
}
}
private void btnActualizar_Click(object sender, EventArgs e)
{
try
{
string S_Cconn = "Server=192.168.1.100;UID= root; Database= bd_turnos; Password=GrupoSGT014;";
MySqlConnection SQL_conexion = new MySqlConnection();
SQL_conexion.ConnectionString = S_Cconn;
SQL_conexion.Open();
MySqlDataAdapter SQL_da = new MySqlDataAdapter("Select * from tabm_usuario", SQL_conexion);
DataTable DT_dat = new DataTable();
SQL_da.Fill(DT_dat);
this.dgvUsuarios.DataSource = DT_dat;
SQL_conexion.Close();
//Numero de usuarios en el sistema
SQL_conexion.Open();
MySqlCommand SQL_cmd = new MySqlCommand("select count(id_usuario)as Cuenta_users from tabm_usuario ", SQL_conexion);
MySqlDataReader dataReader = SQL_cmd.ExecuteReader();
while (dataReader.Read()) { lblTotal.Text = dataReader["Cuenta_users"] + ""; }
SQL_conexion.Close();
//Numero Usuarios Administrador
SQL_conexion.Open();
MySqlCommand SQL_Permiso1 = new MySqlCommand("select count(usur_permiso)as Permiso_user from tabm_usuario where usur_Permiso= 1", SQL_conexion);
MySqlDataReader dRPermiso = SQL_Permiso1.ExecuteReader();
while (dRPermiso.Read()) { lblAdmin.Text = dRPermiso["Permiso_user"] + ""; }
SQL_conexion.Close();
//numero Usuarios Ventanilla
SQL_conexion.Open();
MySqlCommand SQL_Permiso2 = new MySqlCommand("select count(usur_permiso)as Permiso_user from tabm_usuario where usur_Permiso= 2", SQL_conexion);
MySqlDataReader dRPermiso2 = SQL_Permiso2.ExecuteReader();
while (dRPermiso2.Read()) { lblNormales.Text = dRPermiso2["Permiso_user"] + ""; }
SQL_conexion.Close();
//Numero Usuarios Activos
SQL_conexion.Open();
MySqlCommand SQL_Estado = new MySqlCommand("select count(usur_estado)as usur_estado from tabm_usuario where usur_estado= 'Activo'" , SQL_conexion);
MySqlDataReader dREstado = SQL_Estado.ExecuteReader();
while (dREstado.Read()) { lblActivos.Text = dREstado["usur_estado"] + ""; }
SQL_conexion.Close();
}
catch (Exception ei)
{
MessageBox.Show("Ocurrio un error" + ei.Message);
}
}
/// <summary>
/// Constructor that sets up and runs queries from the database.
/// The database connection is setup in the web.config file.
/// </summary>
/// <param name="queryString">This should be an actual sql query like "select * from table where id = 1"</param>
/// <param name="type">This is the type of query being executed (select/update/insert/delete)</param>
public DatabaseQuery(String queryString, Type type)
{
System.Configuration.Configuration config =
System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/Study Participant Portal");
System.Configuration.ConnectionStringSettings connString;
connString = config.ConnectionStrings.ConnectionStrings["ApplicationServices"];
System.Configuration.KeyValueConfigurationElement setting = config.AppSettings.Settings["userName"];
MySqlConnection connection = new MySqlConnection(connString.ToString());
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = queryString;
connection.Open();
switch (type) {
//The select statement populates a list of a list of strings.
case Type.Select:
Reader = command.ExecuteReader();
while (Reader.Read()) {
record = new List<string>();
for (int i = 0; i < Reader.FieldCount; i++) {
item = Reader.GetValue(i).ToString();
record.Add(item);
}
results.Add(record);
}
break;
//for an insert, we set the auto increment ID that was inserted into the database
// as the lastInsertID field so we have access to it in the code.
case Type.Insert:
command.ExecuteNonQuery();
connection.Close();
connection.Open();
command.CommandText = "select Last_Insert_ID()";
MySqlDataReader Reader2 = command.ExecuteReader();
if (Reader2.Read()) {
lastInsertID = Convert.ToInt32(Reader2.GetValue(0));
}
break;
case Type.Update:
command.ExecuteNonQuery();
break;
case Type.Delete:
command.ExecuteNonQuery();
break;
}
connection.Close();
}
private void button1_Click(object sender, EventArgs e)
{
string MyConnectionString = "Server=localhost;Database=EMS;Uid=root;Pwd='';";
MySqlConnection connection = new MySqlConnection(MyConnectionString);
connection.Open();
MySqlCommand cmd = connection.CreateCommand();
MySqlCommand cmd2 = connection.CreateCommand();
MySqlCommand cmd3 = connection.CreateCommand();
cmd.CommandText = "INSERT INTO sensor(sensor_id,sensor_type,contract_id,refresh_time,sensor_status) VALUES(@sensor,@type,@cont,@time,@status)";
cmd.Parameters.AddWithValue("@sensor", id.Text);
cmd.Parameters.AddWithValue("@type", type.SelectedItem.ToString());
cmd.Parameters.AddWithValue("@cont",contract.Text.ToString());
DateTime time = Convert.ToDateTime(refreshtime.Text);
cmd.Parameters.AddWithValue("@time", time);
if (contract.Text != null)
{
cmd.Parameters.AddWithValue("@status", true);
}else{
cmd.Parameters.AddWithValue("@status", true);
}
cmd.ExecuteNonQuery();
connection.Close();
if (contract.Text != null)
{
cmd2.CommandText = "INSERT INTO contract(contract_id,sensor_id,established_date,expire_date,agent_id,Service_provider) VALUES(@contract_id,@sensor_id,@established_date,@expire_date,@agent_id,@Service_provider)";
cmd2.Parameters.AddWithValue("@contract_id", contract.Text.ToString());
cmd2.Parameters.AddWithValue("@sensor_id", id.Text.ToString());
string date1 = establised.Text;
string date2 = establised.Text;
DateTime dt1 = Convert.ToDateTime(date1);
DateTime dt2 = Convert.ToDateTime(date2);
cmd2.Parameters.AddWithValue("@established_date", dt1.ToString());
cmd2.Parameters.AddWithValue("@expire_date", dt2.ToString());
cmd2.Parameters.AddWithValue("@agent_id", agent.SelectedItem.ToString());
cmd2.Parameters.AddWithValue("@Service_provider", service.SelectedItem.ToString());
connection.Open();
cmd2.ExecuteNonQuery();
connection.Close();
}
Login.warning ww = new Login.warning("Sensor Successfully Added!", this);
ww.Show();
}
protected void Page_Load(object sender, EventArgs e)
{
string A1 = "", A2 = "", A3 = "";
if (Request.Params["A1"] != null)
{
A1 = Request.Params["A1"];
Response.Write("A1 = " + A1 + "\n");
}
if (Request.Params["A2"] != null)
{
A2 = Request.Params["A2"];
Response.Write("A2 = " + A2 + "\n");
}
if (Request.Params["A3"] != null)
{
A3 = Request.Params["A3"];
Response.Write("A3 = " + A3 + "\n");
}
MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection("Server=42.121.126.238;Database=nnkkdatabase;Uid=root;Pwd=60battle23;CharSet=gb2312");
con.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("insert into stats (`time`, A1, A2, A3, IP) values (now(), @A1, @A2, @A3, @IP)", con);
cmd.Parameters.AddWithValue("@A1", A1);
cmd.Parameters.AddWithValue("@A2", A2);
cmd.Parameters.AddWithValue("@A3", A3);
cmd.Parameters.AddWithValue("@IP", Request.UserHostAddress);
cmd.ExecuteNonQuery();
con.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
userID = (String)Session["trainerID"];
String query = "SELECT * FROM webapppersonalfit.trainer AS T WHERE T.userID=" + userID + ";";
cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
reader = cmd.ExecuteReader();
if (reader.HasRows && reader.Read())
{
Username.Text = reader.GetString(reader.GetOrdinal("name"));
short_intro.Text = reader.GetString(reader.GetOrdinal("short_intro"));
long_intro.Text = reader.GetString(reader.GetOrdinal("long_intro"));
avatar.ImageUrl = reader.GetString(reader.GetOrdinal("url_pic"));
}
addSpanOntoPlaceHolder();
conn.Close();
reader.Close();
}
catch (Exception ex)
{
//
}
}
protected void submitButtonEventHandler(object sender, EventArgs e)
{
try
{
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
queryString = "INSERT INTO webapppersonalfit.transaction (progname, userid, amount, timestamp) "
+ "VALUES(?progname, ?userid, ?amount, ?timestamp)";
String date = DateTime.UtcNow.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, conn);
cmd.Parameters.AddWithValue("?progname", progname);
cmd.Parameters.AddWithValue("?userid", userid);
cmd.Parameters.AddWithValue("?amount", amount);
cmd.Parameters.AddWithValue("?timestamp", date);
cmd.ExecuteReader();
conn.Close();
SetTrainerClientDatabase(progname, trainerid, userid);
Response.Redirect("TransactionConfirmationPage.aspx");
}
catch (Exception ex)
{
}
}
protected void OnTestClicked(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder bld
= new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
bld.Server = _server.Text;
bld.UserID = _user.Text;
bld.Password = _pass.Text;
_connectionstring = bld.ConnectionString;
MySql.Data.MySqlClient.MySqlConnection con
= new MySql.Data.MySqlClient.MySqlConnection();
con.ConnectionString = _connectionstring;
MessageDialog dlg;
try{
con.Open();
if (con.State != System.Data.ConnectionState.Open)
{
dlg = new MessageDialog(this, DialogFlags.Modal, MessageType.Error, ButtonsType.Ok, "オープンできません。" + con.State.ToString());
dlg.Show();
}
else
{
dlg = new MessageDialog(this, DialogFlags.Modal, MessageType.Info, ButtonsType.Ok, "Success. " + con.State.ToString());
dlg.Show();
con.Close();
}
}
catch (MySql.Data.MySqlClient.MySqlException ex) {
dlg = new MessageDialog(this, DialogFlags.Modal, MessageType.Error, ButtonsType.Ok, "Can not open MySQL." + ex.ErrorCode.ToString());
dlg.Show();
}
}
private void LoginWithPasswordHashFunction()
{
List <String> salthashList = null;
List <String> namesList = null;
try
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
String query = "SELECT slowHashSalt, firstname, middlename, lastname FROM webAppPersonalFit.userregistration WHERE username=?uname";
cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("?uname", usernameTextBox.Text);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
if (salthashList == null)
{
salthashList = new List <String>();
namesList = new List <String>();
}
String saltHashes = reader.GetString(reader.GetOrdinal("slowHashSalt"));
//Console.WriteLine(saltHashes);
salthashList.Add(saltHashes);
String fullname = reader.GetString(reader.GetOrdinal("firstname")) + " " + reader.GetString(reader.GetOrdinal("lastname"));
namesList.Add(fullname);
}
if (salthashList != null)
{
for (int i = 0; i < salthashList.Count; i++)
{
bool validUser = PasswordStorage.VerifyPassword(passwordTextBox.Text, salthashList[i]);
if (validUser == true)
{
Session["UserName"] = namesList[i];
Response.BufferOutput = true;
Response.Redirect("LoggedIn.aspx", false);
}
else
{
passwordTextBox.Text = "User not authenticated";
}
}
}
reader.Close();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
private void registerUser()
{
try {
firstName = textBoxFName.Text;
lastName = textBoxLName.Text;
pass = textBoxPass.Text;
email = textBoxEmail.Text;
street = textBoxStreet.Text;
phone = textBoxPhone.Text;
System.Diagnostics.Debug.WriteLine("SomeText2 " + pass);
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "INSERT INTO customers (email,firstName,lastName,password,address,phone) values('" + email + "','" + firstName + "','" + lastName + "','" + pass + "','" + street + "','" + phone + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
}catch (Exception e) {
}
System.Diagnostics.Debug.WriteLine("regester user with phone: " + phone);
sms s = new sms();
s.Sendsms("+", "You have been registered att Movie Store");
clearBoxes();
}
protected void Button1_Click(object sender, EventArgs e)
{
queryStr = "";
id = (string)Session["myID"];
int myId = Convert.ToInt32(id);
title = textBoxTitle.Text;
artists = textBoxDistribution.Text;
picture = textBoxPicture.Text;
price = textBoxPrice.Text;
quantity = textBoxQuantity.Text;
imdbLink = textBoxIMBD.Text;
category = ddlCategory.SelectedItem.Text;
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "update movies set title='" + title + "', category='" + category + "',artists='" + artists + "',price='" + price + "',quantity='" + quantity + "',imdbLink='" + imdbLink + "',picture='" + picture + "' where id=" + myId + ";";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
System.Diagnostics.Debug.WriteLine(queryStr);
Response.Redirect("AdminTools.aspx");
}
private void registerUser()
{
String queryString = "";
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
queryString = "INSERT INTO webapppersonalfit.userregistration (firstname, middlename, lastname, email, phonenumber, username, userpassword)"
+ "VALUES('"
+ firstNameTextBox.Text + "','"
+ middleNameTextBox.Text + "','"
+ lastNameTextBox.Text + "','"
+ emailTextBox.Text + "','"
+ phoneNumberTextBox.Text + "','"
+ usernameTextBox.Text + "','"
+ passwordTextBox.Text
+ "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, conn);
cmd.ExecuteReader();
conn.Close();
}
protected void addOrder()
{
string allTitles = null;
int lastID = 0;
string price = totalPrice + "";
string emailCustomer = actualCustomer.Email;
for (int i = 0; i < movies.Count; i++)
{
allTitles = allTitles + movies[i].Title + "; ";
}
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "insert into bookings (totalPrice,bookingdate,user_email) values ('" + price + "',now(),'" + emailCustomer + "');SELECT LAST_INSERT_ID();";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
lastID = reader.GetInt32(reader.GetOrdinal("LAST_INSERT_ID()"));
}
conn.Close();
System.Diagnostics.Debug.WriteLine("Last id: " + lastID);
for (int j = 0; j < movies.Count; j++)
{
insertMoviesIntoBooking(lastID, Convert.ToInt32(movies[j].Id));
insertNewRateRow(Convert.ToInt32(movies[j].Id), emailCustomer);
}
}
public static void Main()
{
string connectionString = ConfigurationManager.ConnectionStrings["library"].ConnectionString;
MySqlConnection dbConnection = new MySqlConnection(connectionString);
// MySqlConnection dbConnection = new MySqlConnection("Server=localhost; Port=3306;Database=library; Uid = root; Pwd = root; pooling = true");
dbConnection.Open();
using (dbConnection)
{
int newBook = AddNewBookToDBTable(dbConnection, "King Lion", "James Clavel", DateTime.Parse("2015.10.10"), 1234567890123);
int newBook1 = AddNewBookToDBTable(dbConnection, "Untouchables", "Unknown", DateTime.Parse("2015.10.10"), 1234567890123);
int newBook2 = AddNewBookToDBTable(dbConnection, "C# intro", "Svetlin Nakov", DateTime.Parse("2015.10.10"), 1234567890123);
Console.WriteLine("Inserted new product with Id: {0}", newBook);
Console.WriteLine(new string('-', 30));
ListAllBooksFromDBTable(dbConnection);
Console.WriteLine(new string('-', 30));
Console.Write("Please enter text to search a book:");
string input = Console.ReadLine();
Console.WriteLine(new string('-', 30));
Console.WriteLine("Products that contain: {0}", input);
SearchAllBooksThatContainString(dbConnection, input);
}
}
protected void Button10_Click(object sender, EventArgs e)
{
queryStr = "";
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
if (user.Equals("customer"))
{
System.Diagnostics.Debug.WriteLine(actualCustomerNew.Email + " userrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr " + user);
queryStr = "update customers set customerName='" + textBoxName.Text + "', customerPass='******', custommerStreet='" + textBoxStreet.Text + "', customerPhone='" + textBoxPhone.Text + "' where customerEmail='" + actualCustomerNew.Email + "'";
newCustomer = new Customers(actualCustomerNew.Email, textBoxName.Text, textBoxPass.Text, actualCustomerNew.Pass, textBoxStreet.Text, textBoxPhone.Text);
Session["myCustomer"] = newCustomer;
}
else if (user.Equals("admin"))
{
queryStr = "update administrator set administratorName='" + textBoxName.Text + "', administratorPass='******' where administratorEmail='" + actualAdminNew.Email + "'";
newAdministrator = new Administrator(actualAdminNew.Id, textBoxName.Text, actualAdminNew.Email, textBoxPass.Text);
Session["myAdministrator"] = newAdministrator;
}
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
Response.Redirect("Default.aspx");
}
/// <summary>
/// Initializes a new instance of the <see cref="ManagerGeneralItemStagesGui"/> class.
/// </summary>
/// <param name="itemid">The itemid.</param>
public ManagerGeneralItemStagesGui(string itemid)
{
//Login.close = 1;
InitializeComponent();
this.WindowStartupLocation = WindowStartupLocation.CenterScreen;
this.itemID = itemid;
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = "select itemName from item where itemid='" + itemID + "'";
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
MySqlDataReader dr = MSQLcrcommand1.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
itemName = dr.GetString(0);
}
}
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
type_comboBox.Items.Add("רישום");
type_comboBox.Items.Add("בעבודה");
type_comboBox.Items.Add("תיקון");
type_comboBox.Items.Add("פסול");
type_comboBox.Items.Add("גמר ייצור");
type_comboBox.Items.Add("הסתיים");
type_comboBox.SelectedIndex = 0;
itemidlabel.Content = itemID;
itemnamelabel.Content = itemName;
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = ("SELECT itemStageOrder as `מספר שלב`,stageName as `שם שלב` ,stage_discription as `תאור השלב` FROM item WHERE itemid='" + itemID + "' and itemStatus='רישום' ");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static string FetchPublicContent(string password)
{
using (MySqlConnection con = new MySqlConnection(Database.ConnectionString))
{
con.Open();
using (MySqlCommand command = con.CreateCommand())
{
command.Parameters.AddWithValue("@docID", requestID);
command.Parameters.AddWithValue("@password", password);
command.CommandText =
"SELECT Revisions.Content from Revisions join Documents on Revisions.docID=Documents.docID where Revisions.docID=@docID and Revisions.revisionID=(Select Max(revisionID) from Revisions where docID=@docID) AND publicPassword = @password" ;
MySqlDataReader reader = command.ExecuteReader();
if(reader.HasRows)
{
reader.Read();
return LiveDocs.livedocs.Editor.ParseMarkup((String)reader[0]);
}
else
{
throw new Exception("Password not correct or document does not exist");
}
}
}
return null;
}
public const string mysqlConnection = DBConstant.mysqlConnection;//"User Id=root;Host=115.29.229.134;Database=chinaunion;password=c513324665;charset=utf8";
/// <summary>
/// 添加数据
/// </summary>
/// <returns></returns>
public int Add(Policy entity)
{
string sql = "INSERT INTO tb_policy (agentType,subject,content,sender,attachment,attachmentName,creatTime,type, validateStartTime,validateEndTime, isValidate, isDelete, deleteTime,toAll) VALUE (@agentType,@subject,@content,@sender,@attachment,@attachmentName,@creatTime,@type, @validateStartTime,@validateEndTime, @isValidate, @isDelete, @deleteTime,@toAll)";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@agentType", entity.agentType);
command.Parameters.AddWithValue("@subject", entity.subject);
command.Parameters.AddWithValue("@content", entity.content);
command.Parameters.AddWithValue("@sender", entity.sender);
command.Parameters.AddWithValue("@attachment", entity.attachment);
command.Parameters.AddWithValue("@attachmentName", entity.attachmentName);
command.Parameters.AddWithValue("@creatTime", entity.creatTime);
command.Parameters.AddWithValue("@type", entity.type);
command.Parameters.AddWithValue("@validateStartTime", entity.validateStartTime);
command.Parameters.AddWithValue("@validateEndTime", entity.validateEndTime);
command.Parameters.AddWithValue("@isValidate", entity.isValidate);
command.Parameters.AddWithValue("@isDelete", entity.isDelete);
command.Parameters.AddWithValue("@deleteTime", entity.deleteTime);
command.Parameters.AddWithValue("@toAll", entity.toAll);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
private void btn_load_Click(object sender, EventArgs e)
{
MySqlConnection connection;
var sql = String.Format("SELECT * FROM creature_ai_scripts WHERE creature_id = {0}", UInt32.Parse(tb_entry.Text));
try
{
connection = new MySqlConnection("server=127.0.0.1;uid=root;pwd=;database=world2;");
connection.Open();
MySqlCommand cmd = new MySqlCommand(sql, connection);
var data = cmd.ExecuteReader();
if (!data.HasRows)
return;
while(data.Read())
{
tc_content.TabPages.Add("AI");
var newTab = tc_content.TabPages[tc_content.TabCount - 1];
var aiTab = new AiTab();
aiTab.Dock = DockStyle.Fill;
aiTab.FromDatabase(data);
newTab.Controls.Add(aiTab);
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Wykonuje iSelecta na bazie, zwraca MySqlDataReader z wynikami
/// </summary>
/// <param name="iSelect">string z SELECTEM</param>
/// <param name="iGetReaderData">Funkcja(MySqlDataReader), ogarniająca dane</param>
public static void WykonajSelecta(string iSelect, Action<MySqlDataReader> iGetReaderData)
{
//MySqlDataReader readerToReturn;
string MyConnectionString = "Server=localhost;Database=mydb1;Uid=root;";
MySqlConnection con = new MySqlConnection(MyConnectionString);
con.Open();
try
{
MySqlCommand cmd = con.CreateCommand();
cmd.CommandText = iSelect;
MySqlDataReader reader = cmd.ExecuteReader();
//Delegata, który ogarnie dane
iGetReaderData(reader);
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
//no i chuj, żeby zamknąć połączenie trzeba tutaj wjebać delegata
//return readerToReturn;
}
protected void AddNewMealToDatabase()
{
try
{
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
queryString = "INSERT INTO webapppersonalfit.meals (name, time, meals, progname) "
+ "VALUES(?name, ?time, ?meals, ?progname)";
String mealname = TextBox5.Text;
String mealtime = TextBox6.Text;
String description = TextBox7.Text;
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, conn);
cmd.Parameters.AddWithValue("?name", mealname);
cmd.Parameters.AddWithValue("?time", mealtime);
cmd.Parameters.AddWithValue("?meals", description);
cmd.Parameters.AddWithValue("?progname", programname);
cmd.ExecuteReader();
conn.Close();
}
catch (Exception e)
{
}
}
public static bool IsFullPublic()
{
bool isPublic = false;
bool isFullyPublic = false;
using (MySqlConnection con = new MySqlConnection(Database.ConnectionString))
{
con.Open();
using (MySqlCommand command = con.CreateCommand())
{
command.Parameters.AddWithValue("@docID", requestID);
command.CommandText =
"SELECT public,publicPassword FROM documents WHERE docID = @docID";
MySqlDataReader r = command.ExecuteReader();
while(r.Read())
{
isPublic = Convert.ToBoolean(r["public"]);
if(isPublic && (r["publicPassword"].ToString() == "" || r["publicPassword"].ToString() == "public"))
{
isFullyPublic = true;
}
}
}
}
return isFullyPublic;
}
private void ConnectDatabase()
{
string connStr = "server=" + caspar_database_server_hostname + ";database=" + caspar_database_server_database + ";uid=" +
caspar_database_server_username + ";password="******";";
connection = new MySqlConnection(connStr);
connection.Open();
}
/* =====================================================================================
* ================== G E T V A L U E S B Y O T H E R V A L U E S ==================
* ===================================================================================== */
static string[] getCharakByNameAndKodas(string name, string kodas)
{
string[] result = { "", "" };
MySqlConnection con = new MySqlConnection(connectionStringAlt);
con.Open();
//query
string query = "SELECT pr_z.pr_pavad as 'name',g_v.prid, g_v.g_kodas as 'kodas' FROM pg_zodynas INNER JOIN pg_seima on pg_seima.pg_id_v=pg_zodynas.pg_id INNER JOIN goods_v g_v on g_v.pgs_id = pg_seima.pgs_id INNER JOIN pr_zodynas AS pr_z ON g_v.pr_id=pr_z.pr_id AND g_v.del_date IS NULL WHERE pg_zodynas.pavaddgs = '" + name + "'";
MySqlCommand cmd = new MySqlCommand(query, con);
//executing query
MySqlDataReader data = cmd.ExecuteReader();
while (data.Read())
{
if (data["kodas"].ToString() == kodas)
result[0] = data["name"].ToString();
{
result[1] = data["prid"].ToString();
}
}
data.Close();
con.Close();
return result;
}
//test connection for server
public static bool TestConnection()
{
try
{
//set database connection
using (MySqlConnection con = new MySqlConnection(Big.Config.GetConnectionString()))
{
//open connection
con.Open();
return true;
}
}
catch (MySqlException ex)
{
//revert settings
Properties.Settings.Default.HOST = String.Empty;
Properties.Settings.Default.DATABASE = String.Empty;
Properties.Settings.Default.USERNAME = String.Empty;
Properties.Settings.Default.PASSWORD = String.Empty;
Properties.Settings.Default.DSN = String.Empty;
//save settings
Properties.Settings.Default.Save();
//error configuration
MessageBox.Show("Error: Cannot connect to server" + ex.Message.ToString(), "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
public MySqlTransformationProvider(Dialect dialect, string connectionString)
: base(dialect, connectionString)
{
_connection = new MySqlConnection(_connectionString);
_connection.ConnectionString = _connectionString;
_connection.Open();
}
public List<Categoria> ObterCategoriasCadastradas(string idioma)
{
List<Categoria> lista = new List<Categoria>();
MySqlConnection conn = new MySqlConnection(connectionString);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT id, nome, urlImagem FROM tb_categorias order by nome";
conn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
lista.Add(new Categoria
{
id = (int)dr["id"],
nome = Tradutor.Traduzir(dr["nome"].ToString(), idioma),
urlImagem = dr["urlImagem"].ToString()
});
}
}
conn.Close();
return lista;
}
public bool actionCreate(Task task)
{
var conn = new MySqlConnection(TaskDAO.StringConnection);
try
{
conn.Open();
var sql = "INSERT INTO tasks (title, description, created_at, user_id) VALUES (@title, @description, @created_at, @user_id)";
var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@title", task.Title);
cmd.Parameters.AddWithValue("@description", task.Description);
cmd.Parameters.AddWithValue("@created_at", DateTime.Now);
cmd.Parameters.AddWithValue("@user_id", task.User.Id);
cmd.ExecuteNonQuery();
return true;
} catch(Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
finally
{
conn.Close();
}
}
public void Initialise(string connectionString)
{
m_connectionString = connectionString;
try
{
m_log.Info("[REGION DB]: MySql - connecting: " + Util.GetDisplayConnectionString(m_connectionString));
}
catch (Exception e)
{
m_log.Debug("Exception: password not found in connection string\n" + e.ToString());
}
GetWaitTimeout();
using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
{
dbcon.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(dbcon, assem, "EstateStore");
m.Update();
Type t = typeof(EstateSettings);
m_Fields = t.GetFields(BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.DeclaredOnly);
foreach (FieldInfo f in m_Fields)
{
if (f.Name.Substring(0, 2) == "m_")
m_FieldMap[f.Name.Substring(2)] = f;
}
}
}
protected void loadDataToExerciseTable()
{
thColorStyle.BackColor = System.Drawing.ColorTranslator.FromHtml("#d9534f");
trColorSytle.BackColor = System.Drawing.ColorTranslator.FromHtml("#343a40");
try
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
String trainerID = (String)Session["trainerID"];
String query = "SELECT * FROM webapppersonalfit.program WHERE trainer_ID=" + trainerID + ";";
cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
String progName = reader.GetString(reader.GetOrdinal("prog_name"));
String cardTitle = reader.GetString(reader.GetOrdinal("duration"));
String cardContent = reader.GetString(reader.GetOrdinal("focus"));
//String picUrl = reader.GetString(reader.GetOrdinal("url_pic"));
String picUrl = "";
addCardOntoPlaceHolder(progName, cardTitle, cardContent, picUrl);
}
reader.Close();
conn.Close();
}
catch (Exception e)
{
//passwordTextBox.Value = e.ToString();
}
}
public MySqlConnection GetDBConnection()
{
try
{
MySqlConnection db;
if (_databaseQueue.Count > 0)
{
db = _databaseQueue.Dequeue();
System.Threading.ThreadPool.QueueUserWorkItem(ProcessDatabaseQueue);
}
else
{
db = new MySqlConnection(Config.GetConnectionString());
db.Open();
}
return db;
}
catch (MySqlException e)
{
MySqlConnection db = new MySqlConnection();
Logger.WriteLog(e.Message, Logger.LogType.Error);
db.Dispose();
return db;
}
}
//m mayucula en main obligatoriamente.
public static void Main(string[] args)
{
MySqlConnection mysqlconection = new MySqlConnection (
"Database=dbprueba;Data Source=localhost;User id=root; Password=sistemas");
mysqlconection.Open ();
MySqlCommand mysqlcommand = mysqlconection.CreateCommand ();
mysqlcommand.CommandText = "select * from articulo";
// "select a.categoria as articulocategoria, c.nombre as categorianombre, count(*)" +
// "from articulo a " +
// "left join categoria c on a.categoria= c.id " +
// "group by articulocategoria, categorianombre";
MySqlDataReader mysqldatareader = mysqlcommand.ExecuteReader ();
//---------------------------------------------------------------
updateDatabase (mysqlconection);
showColumnNames (mysqldatareader);
show (mysqldatareader);
//---------------------------------------------------------------
mysqldatareader.Close ();
mysqlconection.Close ();
}
public static void EjecutaQueryMySql(string sql)
{
MySql.Data.MySqlClient.MySqlConnection cnn = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["cnnMysql"].ToString());
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, cnn);
cmd.CommandType = CommandType.Text;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
// Use this for initialization
void Awake()
{
string connectionString = "server=" + host + ";database=" + database + ";uid=" + user + ";pwd=" + password + ";";
mysqlConn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
mysqlConn.Open();
//Testing method in DBMethods:
//dbm.GetGeneralInfo(mysqlConn, "R001");
}
private void LoginWithPasswordHashFunction()
{
String saltHash = null;
try
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
String query = "SELECT userID, slowHashSalt, firstname, middlename, lastname, isPT FROM webAppPersonalFit.userregistration WHERE username=?uname";
cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("?uname", usernameTextBox.Value);
reader = cmd.ExecuteReader();
if (reader.HasRows && reader.Read())
{
String saltHashes = reader.GetString(reader.GetOrdinal("slowHashSalt"));
//Console.WriteLine(saltHashes);
saltHash = saltHashes;
userID = "" + reader.GetInt16(reader.GetOrdinal("userID"));
isPT = Convert.ToInt32(reader.GetByte(reader.GetOrdinal("isPT")));
}
else
{
ValidationTextBoxLabel.Text = "Invalid Username or Password.";
}
if (saltHash != null)
{
bool validUser = PasswordStorage.VerifyPassword(passwordTextBox.Value, saltHash);
if (validUser == true)
{
Session[userID] = userID;
Response.BufferOutput = true;
Server.Transfer("TrainerCatalog.aspx", true);
}
else
{
ValidationTextBoxLabel.Text = "Invalid Username or Password.";
}
}
ValidationTextBoxLabel.Text = "Invalid Username or Password.";
reader.Close();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
protected void updateQuantity(int q, string id)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "update movies set quantity = '" + q + "' where id = '" + id + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
}
// Use this for initialization
void Start()
{
MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString;
myConnectionString = "server=vituttaa.soikkelionhomo.pw;uid=parina;" +
"pwd=sciparina;database=sciparina;";
ArrayList list = new ArrayList();
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection();
conn.ConnectionString = myConnectionString;
conn.Open();
string query = "SELECT url FROM kuva ORDER BY RAND() LIMIT 20";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
list.Add(reader[0]);
}
reader.Close();
conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Debug.Log(ex.ToString());
}
WebClient webClient = new WebClient();
System.Random rnd = new System.Random();
string asd = @"C:\temp2\";
Debug.Log(asd + rnd.Next(1, 124214));
foreach (string i in list)
{
webClient.DownloadFile(i, asd + rnd.Next(1, 124214) + ".png");
}
}
protected void btnSave_Click()
{
String sConnString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(sConnString);
conn.Open();
qurstr = "INSERT INTO CarReviewDB.Users(username,Password,Fname,Mname,Lname,Address,Email,Phone)" +
"VALUES('" + txtUsername.Text + "','" + txtPassword.Text + "','" + txtFirst.Text + "','" + txtMiddle.Text + "','" + txtLast.Text + "','" + txtAddress.Text + "','" + txtEmail.Text + "','" + txtContact.Text + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(qurstr, conn);
cmd.ExecuteReader();
conn.Close();
txtUsername.Text = txtPassword.Text = txtFirst.Text = txtMiddle.Text = txtLast.Text = txtAddress.Text = txtEmail.Text = txtContact.Text = "";
}
private void addElement()
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["webAppConn"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryString = "INSERT INTO magazyn.capacitors (Name, Capacity, Passive_Id)" +
"VALUES('NICHICON20','20','2')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, conn);
cmd.ExecuteReader();
conn.Close();
}
protected void btnSave_Click(object sender, EventArgs e)
{
int user_id = Convert.ToInt16(Session["ID"]);
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "InsertUser";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@id", user_id));
cmd.Parameters.Add(new SqlParameter("@username", (txtUsername.Text).Trim()));
cmd.Parameters.Add(new SqlParameter("@Password", (txtPassword.Text).Trim()));
cmd.Parameters.Add(new SqlParameter("@FName", (txtFirst.Text).Trim()));
cmd.Parameters.Add(new SqlParameter("@MName", (txtMiddle.Text).Trim()));
cmd.Parameters.Add(new SqlParameter("@LName", (txtLast.Text).Trim()));
cmd.Parameters.Add(new SqlParameter("@Address", (txtAddress.Text).Trim()));
cmd.Parameters.Add(new SqlParameter("@Email", (txtEmail.Text).Trim()));
cmd.Parameters.Add(new SqlParameter("@Phone", (txtContact.Text).Trim()));
cmd.Parameters.Add("@output", MySql.Data.MySqlClient.MySqlDbType.VarChar, 100).Direction = ParameterDirection.Output;
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
Response.Redirect("Profile.aspx?msg=update");
}
protected void login_click(object sender, EventArgs e)
{
string staffID = LoginStaffID_textbox.Text;
string password = LoginPassword_textbox.Text;
MySql.Data.MySqlClient.MySqlCommand loginCommand1 = new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM users WHERE StaffID = " + staffID + " AND password = "******";", conn);
MySql.Data.MySqlClient.MySqlDataReader loginReader = null;
conn.Open();
loginReader = loginCommand1.ExecuteReader();
if (loginReader.HasRows)
{
//login was successful
Response.Redirect("Staff.aspx?name=" + loginReader.GetString(0) + "&position=" + loginReader.GetString(1));
}
else
{
//login was unsuccessful
}
conn.Close();
}
public static DataTable RegresaTablaMySql(string sql)
{
MySql.Data.MySqlClient.MySqlConnection cnn = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["cnnMysql"].ToString());
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, cnn);
cmd.CommandType = CommandType.Text;
MySql.Data.MySqlClient.MySqlDataAdapter adpt = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
System.Data.DataTable content = new System.Data.DataTable();
cnn.Open();
adpt.Fill(content);
cnn.Close();
return(content);
}
protected void insertMoviesIntoBooking(int idB, int idM)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "insert into bookings_has_movies (Bookings_id,movies_id) values (" + idB + " , " + idM + " )";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
}
private void registerBook()
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "INSERT INTO movies (id,title,category,artists,price,quantity,imdbLink,picture) " + "values( " + 1 + ",'" + textBoxTitle.Text + "','" + category + "','" + textBoxDistribution.Text + "','" + textBoxPrice.Text + "','" + textBoxQuantity.Text + "','" + textBoxIMBD.Text + "','" + textBoxPicture.Text + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
clearBoxes();
}
public void registerUser()
{
String conStr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(conStr);
conn.Open();
queryStr = "";
queryStr = "INSERT INTO simple_asp_form.user (first_name, mobile, email) VALUES ('" +
txtFirstName.Text + "','" + txtMobile.Text + "','" + txtEmail.Text + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
Response.Redirect("Default.aspx");
}
protected void AddNewProgramToDatabaseButtonEventHandler(object sender, EventArgs e)
{
Label lbl = new Label();
try
{
String queryString = "";
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
String trainer_id = (String)Session["UserID"];
String prog_name = progname.Text;
String prog_duration = progduration.Text;
String prog_price = progprice.Text;
String prog_focus = progfocus.Text;
String prog_body = progbody.Text;
programname = prog_name;
queryString = "INSERT INTO webapppersonalfit.program (trainer_id, prog_name, price, duration, focus) "
+ "VALUES(?trainerid, ?progname, ?price, ?duration, ?focus)";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, conn);
cmd.Parameters.AddWithValue("?trainerid", trainer_id);
cmd.Parameters.AddWithValue("?progname", prog_name);
cmd.Parameters.AddWithValue("?price", prog_price);
cmd.Parameters.AddWithValue("?duration", prog_duration);
cmd.Parameters.AddWithValue("?focus", prog_focus);
cmd.ExecuteReader();
conn.Close();
lbl.Text = lbl.Text = "Program is submitted";
PlaceHolder2.Controls.Add(lbl);
}
catch (Exception ex)
{
lbl.Text = lbl.Text = "Program is not submitted";
PlaceHolder2.Controls.Add(lbl);
}
}
protected string getAllQuantity(string s)
{
string q = "0";
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT * from movies where id= '" + s + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
q = reader.GetString(reader.GetOrdinal("quantity"));
}
conn.Close();
return(q);
}
protected void Button10_Click(object sender, EventArgs e)
{
queryStr = "";
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
System.Diagnostics.Debug.WriteLine(actualCustomerNew.Email + " userrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr " + user);
queryStr = "update user set firstName='" + textBoxFirstName.Text + "',lastName='" + textBoxLastName.Text + "', password='******', address='" + textBoxStreet.Text + "', phone='" + textBoxPhone.Text + "' where email='" + actualCustomerNew.Email + "'";
newCustomer = new Customers(actualCustomerNew.Email, textBoxFirstName.Text, textBoxPass.Text, actualCustomerNew.Pass, textBoxStreet.Text, textBoxPhone.Text);
Session["myCustomer"] = newCustomer;
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
Response.Redirect("Default.aspx");
}
protected void Page_Load(object sender, EventArgs e)
{
countChildren = 0;
countRomance = 0;
countThriller = 0;
countScience = 0;
if (!IsPostBack)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = " select category from movies, bookings, bookings_has_movies where movies.id = bookings_has_movies.movies_id and bookings.idBookings = bookings_has_movies.Bookings_id";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
category = reader.GetString(reader.GetOrdinal("category"));
System.Diagnostics.Debug.WriteLine(category);
switch (category)
{
case "Science":
countScience = countScience + 1;
break; /* optional */
case "Children":
countChildren = countChildren + 1;
break;
case "Romance":
countRomance = countRomance + 1;
break; /* optional */
case "Thriller":
countThriller = countThriller + 1;
break;
}
}
conn.Close();
}
}
private void DoSQLQuery()
{
try
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
//String query = "SELECT * FROM webapppersonalfit.userregistration WHERE username='******' AND userpassword='******'";
String query = "SELECT * FROM webapppersonalfit.userregistration WHERE username=?uname AND userpassword=?pword";
cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
//usernameTextBox.Text => return string => string comparison to against the SQL injection
cmd.Parameters.AddWithValue("?uname", usernameTextBox.Text);
cmd.Parameters.AddWithValue("?pword", passwordTextBox.Text);
reader = cmd.ExecuteReader();
name = "";
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("firstname")) + " " + reader.GetString(reader.GetOrdinal("lastname"));
}
if (reader.HasRows)
{
Session["UserName"] = name;
Response.BufferOutput = true;
Response.Redirect("LoggedIn.aspx", false);
}
else
{
passwordTextBox.Text = "Invalid User";
}
reader.Close();
conn.Close();
}
catch (Exception e)
{
passwordTextBox.Text = e.ToString();
}
}
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();
}
}
}