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 void gravar() { if (validacao()) { MySqlConnection con = new MySqlConnection(Config.Conexao); string sql = "insert into parcela_receber(id_conta_receber,valor,data_vencimento,desconto,acrescimo,situacao) values(@a,@b,@c,@d,@e,@g)"; MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.AddWithValue("@a",conta.id_conta_receber); cmd.Parameters.AddWithValue("@b", Convert.ToDecimal(txtvalor.Text)); cmd.Parameters.AddWithValue("@c",txtdata.Value); cmd.Parameters.AddWithValue("@d",0); cmd.Parameters.AddWithValue("@e", 0); cmd.Parameters.AddWithValue("@g","ABERTA"); con.Open(); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); string sit = ""; if (!conta.situacao.Equals("CANCELADA")) sit = ", situacao='ABERTA'"; sql = "update conta_receber set valor_total=valor_total+" + Convert.ToDecimal(txtvalor.Text) + sit + " where id_conta_receber =" + conta.id_conta_receber; cmd.CommandText = sql; cmd.ExecuteNonQuery(); form.filtragem(); MessageBox.Show("Parcela adicionada com sucesso","",MessageBoxButtons.OK,MessageBoxIcon.Information); con.Close(); this.Close(); } }
static void Main(string[] args) { for (;;) { Thread.Sleep(10000); const string connectionString = "Server=localhost; Uid=root; Pwd=123;"; MySqlConnection connection = new MySqlConnection(connectionString); connection.Open(); MySqlCommand command = new MySqlCommand("USE test_db;", connection); command.ExecuteNonQuery(); command.CommandText = "DROP TABLE IF EXISTS `authorization`;"; command.ExecuteNonQuery(); command.CommandText = "CREATE TABLE authorization (id INT(4) PRIMARY KEY AUTO_INCREMENT, value TEXT);"; command.ExecuteNonQuery(); string textFromFile = File.ReadAllText(@"C:\Login.txt", Encoding.Default); command.CommandText = "INSERT INTO authorization(`value`) VALUES (@param)"; MySqlParameter param = new MySqlParameter("@param", MySqlDbType.Text); param.Value = textFromFile; command.Parameters.Add(param); command.ExecuteNonQuery(); command.CommandText = "SELECT value FROM authorization WHERE id=1"; string textFromDb = (string)command.ExecuteScalar(); Console.WriteLine(textFromDb); connection.Close(); Console.WriteLine("Готово"); } }
public static void Main(string[] args) { string databaseServer = "127.0.0.1"; string databaseUsername = "******"; string databasePassword = ""; string databaseName = ""; using (MySqlConnection connection = new MySqlConnection(@"server=" + databaseServer + ";username="******";password="******";database=" + databaseName)) { connection.Open(); string query = null; using (MySqlCommand command = new MySqlCommand(query, connection)) { query = "CREATE DATABASE IF NOT EXISTS `laptops-terneuzen` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `laptops-terneuzen`;"; command.CommandText = query; command.ExecuteNonQuery(); query = "DROP TABLE IF EXISTS `gebruikers`; CREATE TABLE IF NOT EXISTS `gebruikers` (`id` tinyint(4) NOT NULL AUTO_INCREMENT, `gebruikersnaam` tinytext NOT NULL, `wachtwoord` tinytext NOT NULL, `rechten` tinyint(4) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;"; command.CommandText = query; command.ExecuteNonQuery(); query = "DROP TABLE IF EXISTS `inventarisatie`; CREATE TABLE IF NOT EXISTS `inventarisatie` (`id` tinyint(4) NOT NULL AUTO_INCREMENT, `h-nummer` tinytext NOT NULL, `merk` tinytext NOT NULL, `serie-nummer` tinytext NOT NULL, `locatie` tinytext NOT NULL, `status` tinyint(4) NOT NULL, `in-bezit-van` tinytext NOT NULL, `olc-nummer` tinyint(4) NOT NULL, `uitgifte` tinyint(1) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;"; command.CommandText = query; command.ExecuteNonQuery(); query = "DROP TABLE IF EXISTS `uitgifte`; CREATE TABLE IF NOT EXISTS `uitgifte` (`id` tinyint(4) NOT NULL AUTO_INCREMENT, `leerling-nummer` tinyint(4) NOT NULL, `h-nummer` tinytext NOT NULL, `olc-nummer` tinyint(4) NOT NULL, `tijd` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `leerling-nummer` (`leerling-nummer`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;"; command.CommandText = query; command.ExecuteNonQuery(); Console.ReadLine(); } } }
public static MySqlConnection GetConnection(string tableName, bool isFirst) { _tableName = tableName; string cs = @"server=localhost;userid=root;password=root;database=lecast"; MySqlConnection conn = null; try { conn = new MySqlConnection(cs); conn.Open(); if (isFirst) { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = "drop table if exists " + tableName; cmd.ExecuteNonQuery(); cmd.CommandText = "create table " + tableName + "(user VARCHAR(100), sku VARCHAR(20), category VARCHAR(20), query VARCHAR(255), click_time TIMESTAMP, query_time TIMESTAMP)"; cmd.ExecuteNonQuery(); } } catch (MySqlException ex) { Console.WriteLine("Error: {0}", ex.ToString()); } return conn; }
public void InsertTest() { try { // do the insert MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id,name) VALUES(10,'Test')", conn); int cnt = cmd.ExecuteNonQuery(); Assert.AreEqual( 1, cnt, "Insert Count" ); // make sure we get the right value back out cmd.CommandText = "SELECT name FROM Test WHERE id=10"; string name = (string)cmd.ExecuteScalar(); Assert.AreEqual( "Test", name, "Insert result" ); // now do the insert with parameters cmd.CommandText = "INSERT INTO Test (id,name) VALUES(?id, ?name)"; cmd.Parameters.Add( new MySqlParameter("?id", 11)); cmd.Parameters.Add( new MySqlParameter("?name", "Test2")); cnt = cmd.ExecuteNonQuery(); Assert.AreEqual( 1, cnt, "Insert with Parameters Count" ); // make sure we get the right value back out cmd.Parameters.Clear(); cmd.CommandText = "SELECT name FROM Test WHERE id=11"; name = (string)cmd.ExecuteScalar(); Assert.AreEqual( "Test2", name, "Insert with parameters result" ); } catch (MySqlException ex) { Assert.Fail( ex.Message ); } }
public void desconto() { if (validar()) { decimal valor = Convert.ToDecimal(txtvalor.Text); string sql = "update parcela_pagar set desconto = desconto+" + valor; sql += ",valor=valor-"+valor; sql += " where id_parcela_pagar=" + parcela.id_parcela_pagar; MySqlConnection con = new MySqlConnection(Config.Conexao); MySqlCommand cmd = new MySqlCommand(sql, con); con.Open(); cmd.ExecuteNonQuery(); if (valor == parcela.valor) { sql = "update parcela_pagar set situacao='RECEBIDA' where id_parcela_pagar=" + parcela.id_parcela_pagar; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } sql = "update conta_pagar set valor_total=valor_total-"+valor; sql += " where id_conta_pagar="+conta.id_conta_pagar; cmd.CommandText = sql; cmd.ExecuteNonQuery(); con.Close(); form.pesquisa(); form.verificar_pagamento(); form.lista_tudo(); MessageBox.Show("Desconto inserido com sucesso", "Atencao", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } }
private void initialize() { string databaseServer = "127.0.0.1"; string databaseUsername = "******"; string databasePassword = ""; string databaseName = ""; using (MySqlConnection connection = new MySqlConnection(@"server=" + databaseServer + ";username="******";password="******";database=" + databaseName)) { connection.Open(); string query = null; using (MySqlCommand command = new MySqlCommand(query, connection)) { query = "CREATE DATABASE IF NOT EXISTS `laptops-terneuzen` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `laptops-terneuzen`;"; command.CommandText = query; command.ExecuteNonQuery(); query = "DROP TABLE IF EXISTS `gebruikers`;CREATE TABLE IF NOT EXISTS `gebruikers` ( `id` tinyint(4) NOT NULL AUTO_INCREMENT, `gebruikersnaam` varchar(20) NOT NULL, `wachtwoord` varchar(40) NOT NULL, `rechten` varchar(1) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`,`gebruikersnaam`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;"; command.CommandText = query; command.ExecuteNonQuery(); query = "DROP TABLE IF EXISTS `inventarisatie`;CREATE TABLE IF NOT EXISTS `inventarisatie` (`id` tinyint(4) NOT NULL AUTO_INCREMENT, `merk` varchar(6) NOT NULL, `serienummer` varchar(6) NOT NULL, `hnummer` varchar(6) NOT NULL, `ruimte` varchar(4) NOT NULL, `locatie` varchar(1) NOT NULL, `status` char(1) NOT NULL, `inbezitvan` varchar(20) NOT NULL, `olcnummer` varchar(3) NOT NULL, `uitgifte` tinyint(1) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hnummer` (`hnummer`), UNIQUE KEY `serienummer` (`serienummer`), UNIQUE KEY `olcnummer` (`olcnummer`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;"; command.CommandText = query; command.ExecuteNonQuery(); query = "DROP TABLE IF EXISTS `uitgifte`;CREATE TABLE IF NOT EXISTS `uitgifte` ( `id` tinyint(4) NOT NULL AUTO_INCREMENT, `leerling-nummer` varchar(6) NOT NULL, `hnummer` varchar(6) NOT NULL, `olcnummer` char(3) NOT NULL, `tijd` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`,`hnummer`,`olcnummer`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;"; command.CommandText = query; command.ExecuteNonQuery(); MessageBox.Show("Datatabases zijn aangemaakt"); } } }
protected override void OnLoad (EventArgs e) { base.OnLoad (e); MySqlCommand cmd; IDataReader r; MySqlConnection con = new MySqlConnection ("Server=127.0.0.1;Database=dht;Uid=dht;Pwd=sgv54hnf3ha142s3dfg4ad5sh;"); con.Open (); try { string port = Request.QueryString ["port"]; ushort tmp_i; if (port != null && port.Length > 0 && ushort.TryParse (port, out tmp_i)) { cmd = new MySqlCommand ("update nodes set lastupdate=CURRENT_TIMESTAMP where endpoint=@ep", con); cmd.Parameters.AddWithValue ("@ep", Request.UserHostAddress + " " + port); if (cmd.ExecuteNonQuery () <= 0) { cmd.CommandText = "insert into nodes(endpoint) values(@ep)"; cmd.ExecuteNonQuery (); } } cmd = new MySqlCommand ("SELECT id, endpoint FROM nodes WHERE lastupdate>(subtime(CURRENT_TIMESTAMP,'12:00:00')) order by rand() LIMIT 20", con); r = cmd.ExecuteReader (); while (r.Read ()) Response.Output.WriteLine (r.GetString (1)); r.Close (); } finally { con.Close (); } }
public void InsertTest() { execSQL("CREATE TABLE Test (id int NOT NULL, name VARCHAR(100))"); // do the insert MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id,name) VALUES(10,'Test')", conn); int cnt = cmd.ExecuteNonQuery(); Assert.AreEqual( 1, cnt, "Insert Count" ); // make sure we get the right value back out cmd.CommandText = "SELECT name FROM Test WHERE id=10"; string name = (string)cmd.ExecuteScalar(); Assert.AreEqual( "Test", name, "Insert result" ); // now do the insert with parameters cmd.CommandText = "INSERT INTO Test (id,name) VALUES(?id, ?name)"; cmd.Parameters.Add( new MySqlParameter("?id", 11)); cmd.Parameters.Add( new MySqlParameter("?name", "Test2")); cnt = cmd.ExecuteNonQuery(); Assert.AreEqual( 1, cnt, "Insert with Parameters Count" ); // make sure we get the right value back out cmd.Parameters.Clear(); cmd.CommandText = "SELECT name FROM Test WHERE id=11"; name = (string)cmd.ExecuteScalar(); Assert.AreEqual( "Test2", name, "Insert with parameters result" ); }
public int clear(CuentaDTO cuenta) { int res = 0; try { connection.Open(); MySqlCommand myCommand = new MySqlCommand(); myCommand.Connection = connection; myCommand.CommandText = "DELETE FROM correo WHERE cuenta_idcuenta=?idcuenta"; myCommand.Parameters.Add("?idcuenta", MySqlDbType.Int16).Value = cuenta.IdCuenta; myCommand.ExecuteNonQuery(); myCommand.CommandText = "DELETE FROM origendestino WHERE 1=1"; myCommand.ExecuteNonQuery(); myCommand.CommandText = "DELETE FROM adjunto WHERE 1=1"; myCommand.ExecuteNonQuery(); connection.Close(); res = 1; return res; } catch (MySqlException ex) { return -1; } }
public void InsertBinary() { int lenIn = 400000; byte[] dataIn = Utils.CreateBlob(lenIn); st.execSQL("DROP TABLE IF EXISTS Test"); st.execSQL("CREATE TABLE Test (id INT NOT NULL, blob1 LONGBLOB, PRIMARY KEY(id))"); MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?b1)", st.conn); cmd.Parameters.Add(new MySqlParameter("?id", 1)); cmd.Parameters.Add(new MySqlParameter("?b1", dataIn)); int rows = cmd.ExecuteNonQuery(); byte[] dataIn2 = Utils.CreateBlob(lenIn); cmd.Parameters[0].Value = 2; cmd.Parameters[1].Value = dataIn2; rows += cmd.ExecuteNonQuery(); Assert.True(rows == 2, "Checking insert rowcount"); cmd.CommandText = "SELECT * FROM Test"; using (MySqlDataReader reader = cmd.ExecuteReader()) { Assert.True(reader.HasRows == true, "Checking HasRows"); reader.Read(); byte[] dataOut = new byte[lenIn]; long lenOut = reader.GetBytes(1, 0, dataOut, 0, lenIn); Assert.True(lenIn == lenOut, "Checking length of binary data (row 1)"); // now see if the buffer is intact for (int x = 0; x < dataIn.Length; x++) Assert.True(dataIn[x] == dataOut[x], "Checking first binary array at " + x); // now we test chunking int pos = 0; int lenToRead = dataIn.Length; while (lenToRead > 0) { int size = Math.Min(lenToRead, 1024); int read = (int)reader.GetBytes(1, pos, dataOut, pos, size); lenToRead -= read; pos += read; } // now see if the buffer is intact for (int x = 0; x < dataIn.Length; x++) Assert.True(dataIn[x] == dataOut[x], "Checking first binary array at " + x); reader.Read(); lenOut = reader.GetBytes(1, 0, dataOut, 0, lenIn); Assert.True(lenIn == lenOut, "Checking length of binary data (row 2)"); // now see if the buffer is intact for (int x = 0; x < dataIn2.Length; x++) Assert.True(dataIn2[x] == dataOut[x], "Checking second binary array at " + x); } }
public void TestMultiPacket() { int len = 20000000; // currently do not test this with compression if (conn.UseCompression) return; using (MySqlConnection c = new MySqlConnection(GetConnectionString(true))) { c.Open(); byte[] dataIn = Utils.CreateBlob(len); byte[] dataIn2 = Utils.CreateBlob(len); MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, NULL, ?blob, NULL )", c); cmd.CommandTimeout = 0; cmd.Parameters.Add(new MySqlParameter("?id", 1)); cmd.Parameters.Add(new MySqlParameter("?blob", dataIn)); try { cmd.ExecuteNonQuery(); } catch (Exception ex) { Assert.Fail(ex.Message); } cmd.Parameters[0].Value = 2; cmd.Parameters[1].Value = dataIn2; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * FROM Test"; try { using (MySqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); byte[] dataOut = new byte[len]; long count = reader.GetBytes(2, 0, dataOut, 0, len); Assert.AreEqual(len, count); for (int i = 0; i < len; i++) Assert.AreEqual(dataIn[i], dataOut[i]); reader.Read(); count = reader.GetBytes(2, 0, dataOut, 0, len); Assert.AreEqual(len, count); for (int i = 0; i < len; i++) Assert.AreEqual(dataIn2[i], dataOut[i]); } } catch (Exception ex) { Assert.Fail(ex.Message); } } }
public void TestMultiPacket() { int len = 20000000; st.suExecSQL("SET GLOBAL max_allowed_packet=64000000"); // currently do not test this with compression if (st.conn.UseCompression) return; using (MySqlConnection c = new MySqlConnection(st.GetConnectionString(true))) { c.Open(); byte[] dataIn = Utils.CreateBlob(len); byte[] dataIn2 = Utils.CreateBlob(len); MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, NULL, ?blob, NULL )", c); cmd.CommandTimeout = 0; cmd.Parameters.Add(new MySqlParameter("?id", 1)); cmd.Parameters.Add(new MySqlParameter("?blob", dataIn)); cmd.ExecuteNonQuery(); cmd.Parameters[0].Value = 2; cmd.Parameters[1].Value = dataIn2; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * FROM Test"; using (MySqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); byte[] dataOut = new byte[len]; long count = reader.GetBytes(2, 0, dataOut, 0, len); Assert.Equal(len, count); int i = 0; try { for (; i < len; i++) Assert.Equal(dataIn[i], dataOut[i]); } catch (Exception) { int z = i; } reader.Read(); count = reader.GetBytes(2, 0, dataOut, 0, len); Assert.Equal(len, count); for (int x = 0; x < len; x++) Assert.Equal(dataIn2[x], dataOut[x]); } } }
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); }
public void TestQuoting() { MySqlCommand cmd = new MySqlCommand("", conn); cmd.CommandText = "INSERT INTO Test VALUES (?id, ?name, NULL,NULL,NULL)"; cmd.Parameters.Add(new MySqlParameter("?id", 1)); cmd.Parameters.Add(new MySqlParameter("?name", "my ' value")); cmd.ExecuteNonQuery(); cmd.Parameters[0].Value = 2; cmd.Parameters[1].Value = @"my "" value"; cmd.ExecuteNonQuery(); cmd.Parameters[0].Value = 3; cmd.Parameters[1].Value = @"my ` value"; cmd.ExecuteNonQuery(); cmd.Parameters[0].Value = 4; cmd.Parameters[1].Value = @"my ´ value"; cmd.ExecuteNonQuery(); cmd.Parameters[0].Value = 5; cmd.Parameters[1].Value = @"my \ value"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * FROM Test"; MySqlDataReader reader = null; try { reader = cmd.ExecuteReader(); reader.Read(); Assert.AreEqual("my ' value", reader.GetString(1)); reader.Read(); Assert.AreEqual(@"my "" value", reader.GetString(1)); reader.Read(); Assert.AreEqual("my ` value", reader.GetString(1)); reader.Read(); Assert.AreEqual("my ´ value", reader.GetString(1)); reader.Read(); Assert.AreEqual(@"my \ value", reader.GetString(1)); } catch (Exception ex) { Assert.Fail(ex.Message); } finally { if (reader != null) reader.Close(); } }
protected void Confirm_Click(object sender, EventArgs e) { conn = new MySqlConnection(GetConnectionString()); String orderid = TextBox7.Text; List<String> ProductID = new List<String>(); List<int> Quantity = new List<int>(); try { conn.Open(); // MySqlCommand comm=new MySqlCommand("Update Order_Detail_Store set Order_Received='Completed' where Order_ID='"+orderid+"'",conn); MySqlCommand comm=new MySqlCommand("Select Order_Received from Order_Detail_Store where Order_ID='"+orderid+"'",conn); MySqlDataReader dr=comm.ExecuteReader(); dr.Read(); String flag=dr.GetValue(0).ToString(); dr.Close(); if(flag.Equals("Pending")) { comm.CommandText="Update Order_Detail_Store set Order_Received='Completed' where Order_ID='"+orderid+"'"; comm.ExecuteNonQuery(); comm.CommandText = "Update Order_Detail_Store_Central set Order_Received='Completed' where Order_ID='" + orderid + "'"; comm.ExecuteNonQuery(); comm.CommandText = "Select Product_ID,Quantity from Order_Product_Store where Order_ID='"+orderid+"'"; MySqlDataReader dr1 = comm.ExecuteReader(); while (dr1.Read()) { ProductID.Add(dr1.GetValue(0).ToString()); Quantity.Add(int.Parse(dr1.GetValue(1).ToString())); } dr1.Close(); int i=0; foreach (String pid in ProductID) { int quant = Quantity.ElementAt(i); i++; comm.CommandText = "Update Current_Store_Products set Stock=Stock+" + quant + " where Product_ID='" + pid + "'"; comm.ExecuteNonQuery(); } } } catch (Exception ex) { Response.Write(ex.Message); } finally { conn.Close(); } TextBox7.Text=""; }
protected void Button1_Click(object sender, EventArgs e) { String aux = "INSERT INTO sala_apartada values(null," + TextBox2.Text + ", " + TextBox3.Text + ", '" + TextBox6.Text + "', '" + TextBox7.Text + "');"; try { MySqlConnection Cn; MySqlCommand Cm; String cadena; Cn = new MySqlConnection(); cadena = "Server=localhost; user=root; database=Laboratorio"; Cn.ConnectionString = cadena; Cn.Open(); /////Insertar///// Cm = new MySqlCommand(aux, Cn); Cm.ExecuteNonQuery(); Cn.Close(); Label1.Text = "Datos Guardados"; //Response.Write(@"<script language='javascript'>alert('realizada')</script>"); } catch (MySqlException es) { // Response.Write(@"<script language='javascript'>alert('Error')</script>"); Label1.Text = "No se Pudo regsitrar Entrada"; } }
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(); }
public void create() { db = new Database(); if (officeVerification()) { try { officeQuery = new MySqlCommand(); officeQuery.Connection = db.Connection(); officeQuery.CommandText = "INSERT INTO office(officedescription) VALUES(@Description)"; officeQuery.Prepare(); officeQuery.Parameters.AddWithValue("@Description", officedescription); officeQuery.ExecuteNonQuery(); MessageBox.Show("A Descrição Cargo Assinatura " + officedescription + " foi criada com sucesso."); } catch (MySqlException ex) { MessageBox.Show("Ocurreu um erro"); Console.WriteLine("Error: {0}", ex.ToString()); } finally { db.Close(); } } }
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; } }
/// <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); } }
protected void btnEliminarGrupo_Click(object sender, ImageClickEventArgs e) { if (this.ListaGrupos1.Selected != "-1") { //foreach (string grpcode in this.ListaGrupos1.MultiSelected) //{ FuncionalidadClassLib.Grupo grp = FuncionalidadClassLib.ManejadorGrupo.consultargrupo(ListaGrupos1.Selected, datos.ds.Tables["grupos"], datos.ds.Tables["centroscomputo"]); grp.ListaApirantes = FuncionalidadClassLib.manejadorAspirante.ConsultarAspirGrupo(grp.CodigoGrupo, datos.ds); foreach (FuncionalidadClassLib.Aspirante asp in grp.ListaApirantes) { asp.CodigoGrupo = "GrpNone"; FuncionalidadClassLib.manejadorAspirante.modificarAspirante(asp, datos.ds.Tables["aspirantes"]); } this.datos.Conectar(); this.datos.ActualizarBD(); this.datos.Desconectar(); FuncionalidadClassLib.ManejadorGrupo.EliminarGrupo(grp, datos.ds.Tables["grupos"]); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("DELETE FROM GRUPOS WHERE CODGRUPO=?CODIGO", new MySql.Data.MySqlClient.MySqlConnection(datos.CadenaConexion)); cmd.Parameters.Add("?CODIGO", this.ListaGrupos1.Selected); cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); this.datos.Conectar(); this.datos.ActualizarBD(); this.datos.Desconectar(); //} this.ListaGrupos1.RellenarGrid(); } }
public static void AddCharacter(Models.Character character) { var query = new MySqlCommand("INSERT INTO characters (id, account, nickname, level, experience, sex, breed, skincolor," + " haircolor, pupilcolor, skincolorfactor, haircolorfactor, cloth, face, title) VALUES " + "(@id, @account, @nickname, @level, @experience, @sex, @breed, @skincolor, @haircolor," + " @pupilcolor, @skincolorfactor, @haircolorfactor, @cloth, @face, @title)", DatabaseManager.Connection); query.Parameters.Add(new MySqlParameter("@id", character.ID)); query.Parameters.Add(new MySqlParameter("@account", character.Account)); query.Parameters.Add(new MySqlParameter("@nickname", character.Nickname)); query.Parameters.Add(new MySqlParameter("@level", character.Level)); query.Parameters.Add(new MySqlParameter("@experience", character.Experience)); query.Parameters.Add(new MySqlParameter("@sex", character.Sex)); query.Parameters.Add(new MySqlParameter("@breed", character.Breed)); query.Parameters.Add(new MySqlParameter("@skincolor", character.SkinColor)); query.Parameters.Add(new MySqlParameter("@haircolor", character.HairColor)); query.Parameters.Add(new MySqlParameter("@pupilcolor", character.PupilColor)); query.Parameters.Add(new MySqlParameter("@skincolorfactor", character.SkinColorFactor)); query.Parameters.Add(new MySqlParameter("@haircolorfactor", character.HairColorFactor)); query.Parameters.Add(new MySqlParameter("@cloth", character.Cloth)); query.Parameters.Add(new MySqlParameter("@face", character.Face)); query.Parameters.Add(new MySqlParameter("@title", character.Title)); query.ExecuteNonQuery(); Characters.Add(character); }
protected void register_click(object sender, EventArgs e) { //gather strings from the webpage string staffID = RegisterStaffID_textbox.Text; string firstName = RegisterFirstName_textbox.Text; string lastName = RegisterLastName_textbox.Text; string password = RegisterPassword_textbox.Text; string position = RegisterPosition_DropDownList.Text; //check if any of the text fields are empty. if not, execture the INSERT query. if (RegisterStaffID_textbox.Text.Equals("") | RegisterFirstName_textbox.Text.Equals("") | RegisterLastName_textbox.Equals("") | RegisterPassword_textbox.Text.Equals("")) { string script = "alert(\"Please Make sure none of the text fields are empty.\");"; ScriptManager.RegisterStartupScript(this, GetType(), "ServerControlScript", script, true); } else { MySql.Data.MySqlClient.MySqlCommand registerCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO users VALUES('" + staffID + "', '" + firstName + "', '" + lastName + "', '" + password + "', '" + position + "')", conn); conn.Open(); registerCommand.ExecuteNonQuery(); conn.Close(); } }
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(); } }
/// <summary> /// Used to set password if the password set on first login is active /// </summary> /// <param name="Account">Account name</param> /// <param name="Pass">given password</param> public static void SetPass(string Account, string Pass) { MySqlCommand Cmd = new MySqlCommand("UPDATE `accounts` SET `Password` = \"" + Pass + "\" WHERE `AccountID` = \"" + Account + "\"", DatabaseConnection.NewConnection()); Cmd.ExecuteNonQuery(); Cmd.Connection.Close(); Cmd.Dispose(); }
internal void CreateStaff(Staff staff) { using (MySqlConnection conn = new MySqlConnection(PredatorConstants.CONNECTION_STRING)) { if (MySqlConnectionManager.OpenConnection(conn)) { string commandText = @"INSERT INTO staff (fName, lName, username, password, email, accessLevel, creationDate) VALUES(@FNAME, @LNAME, @USERNAME, @PASSWORD, @EMAIL, @ACCESSLEVEL, @CREATIONDATE)"; MySqlCommand command = new MySqlCommand(commandText, conn); command.Parameters.Add("@FNAME", MySqlDbType.VarChar); ; command.Parameters["@FNAME"].Value = staff.fName; command.Parameters.Add("@LNAME", MySqlDbType.VarChar); command.Parameters["@LNAME"].Value = staff.lName; command.Parameters.Add("@USERNAME", MySqlDbType.VarChar); command.Parameters["@USERNAME"].Value = staff.username; command.Parameters.Add("@PASSWORD", MySqlDbType.VarChar); command.Parameters["@PASSWORD"].Value = staff.password; command.Parameters.Add("@EMAIL", MySqlDbType.VarChar); command.Parameters["@EMAIL"].Value = staff.email; command.Parameters.Add("@ACCESSLEVEL", MySqlDbType.Int32); command.Parameters["@ACCESSLEVEL"].Value = staff.accessLevel; command.Parameters.Add("@CREATIONDATE", MySqlDbType.DateTime); command.Parameters["@CREATIONDATE"].Value = staff.creationDate; command.ExecuteNonQuery(); MySqlConnectionManager.CloseConnection(conn); } } }
public int Agregar(ClsMateriasPrimas pMateriasPrimas) { int retorno = 0; MySqlCommand cmd = new MySqlCommand(); cmd.Connection = BdConexion.ObtenerConexion(); cmd.CommandText = "guardarMateriasPrimas"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@id", pMateriasPrimas.Codigo); cmd.Parameters["@id"].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("@nom", pMateriasPrimas.Nombre); cmd.Parameters["@nom"].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("@min", pMateriasPrimas.Stock_Minimo); cmd.Parameters["@min"].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("@max", pMateriasPrimas.Stock_Maximo); cmd.Parameters["@max"].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("@exis", pMateriasPrimas.Existencias); cmd.Parameters["@exis"].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("@disp", pMateriasPrimas.Disponibilidad); cmd.Parameters["@disp"].Direction = ParameterDirection.Input; retorno = cmd.ExecuteNonQuery(); return retorno; }
public void DeleteInschrijving(int studentID, int evenementID) { MySqlTransaction trans = null; try { conn.Open(); trans = conn.BeginTransaction(); string insertString = @"delete from inschrijving where student_id=@studentid and evenement_id=@evenementid"; MySqlCommand cmd = new MySqlCommand(insertString, conn); MySqlParameter studentidParam = new MySqlParameter("@studentid", MySqlDbType.Int32); MySqlParameter evenementidParam = new MySqlParameter("@evenementid", MySqlDbType.Int32); studentidParam.Value = studentID; evenementidParam.Value = evenementID; cmd.Parameters.Add(studentidParam); cmd.Parameters.Add(evenementidParam); cmd.Prepare(); cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception e) { trans.Rollback(); Console.Write("Inschrijving niet verwijderd: " + e); throw e; } finally { conn.Close(); } }
public void addEntry(TrackingEntry entry) { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = "INSERT INTO tracking_entry VALUES (NULL, @taggerID, @type, @isTagged," + " @taggerName, @entryDate, @tagNumber, @species, @city, @state, @country," + " @longitude, @latitude, @temperature, @precipitation, @windSpeed," + " @windDirection);"; cmd.Prepare(); cmd.Parameters.AddWithValue("@taggerID", entry.taggerID); cmd.Parameters.AddWithValue("@type", entry.type); cmd.Parameters.AddWithValue("@isTagged", entry.isTagged); cmd.Parameters.AddWithValue("@taggerName", entry.taggerName); cmd.Parameters.AddWithValue("@entryDate", entry.entryDate); cmd.Parameters.AddWithValue("@tagNumber", entry.tagNumber); cmd.Parameters.AddWithValue("@species", entry.species); cmd.Parameters.AddWithValue("@city", entry.city); cmd.Parameters.AddWithValue("@state", entry.state); cmd.Parameters.AddWithValue("@country", entry.country); cmd.Parameters.AddWithValue("@longitude", entry.longitude); cmd.Parameters.AddWithValue("@latitude", entry.latitude); cmd.Parameters.AddWithValue("@temperature", entry.temperature); cmd.Parameters.AddWithValue("@precipitation", entry.precipitation); cmd.Parameters.AddWithValue("@windSpeed", entry.windSpeed); cmd.Parameters.AddWithValue("@windDirection", entry.windDirection); cmd.ExecuteNonQuery(); }
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(); }
protected void btnEliminarAspir_Click(object sender, ImageClickEventArgs e) { //if (this.ListaAspir1.MultiSelected != null) //{ // foreach (string codaspir in this.ListaAspir1.MultiSelected) // { if (this.ListaAspir1.Selected != "-1") { FuncionalidadClassLib.manejadorAspirante.eliminarAspirante( this.ListaAspir1.Selected, this.datos.ds.Tables["aspirantes"]); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("DELETE FROM ASPIRANTES WHERE CODIGO=?CODIGO", new MySql.Data.MySqlClient.MySqlConnection(datos.CadenaConexion)); cmd.Parameters.Add("?CODIGO", this.ListaAspir1.Selected); cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); this.datos.Conectar(); this.datos.ActualizarBD(); this.datos.Desconectar(); this.ListaAspir1.RellenarGrid(); } }
protected void btnChange_Click(object sender, EventArgs e) { int userId = Convert.ToInt16(Session["ID"]); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); cmd.Connection = conn; cmd.CommandText = "update users set password=@password where ID=" + userId + ""; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SqlParameter("@password", (txtPassword.Text).Trim())); conn.Open(); int result = cmd.ExecuteNonQuery(); conn.Close(); if (result > 0) { lblShow.Text = "Password Update Successfully"; chkPassword.Checked = false; txtConfirm.Visible = Label1.Visible = false; btnChange.Enabled = false; } }
public static int execS(String sql) { if (MainClass.usedb) { int lines = 0; MySql.Data.MySqlClient.MySqlCommand cmd = null; MySql.Data.MySqlClient.MySqlConnection conn = null; try { conn = createConnection(); cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn); cmd.CommandTimeout = (60 * 1000) * 3; lines = cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("execS" + ex.Message + ex.StackTrace); return(-1); } finally { if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Close(); conn.Dispose(); conn = null; } } return(lines); } else { return(0); } }
protected void ok(object sender, EventArgs e) { UserInfo temp = (UserInfo)Session["currentUser"]; BillPayEntry bpstatus = new BillPayEntry(); if (temp.MyBillPayments == null) { } else { // foreach (BillPayEntry i in temp.MyBillPayments) //{ // i.Status = 1; //} MySql.Data.MySqlClient.MySqlConnection co; string connection = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString(); co = new MySql.Data.MySqlClient.MySqlConnection(connection); co.Open(); try { string query = $"UPDATE `c432017fa01tirunagarus`.`tirunagarus_WADfl17_RapidBillPay` SET `status` = 1 WHERE `emailAddress` = '{temp.EmailAddress}'; "; MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(query, co); int rows = command.ExecuteNonQuery(); co.Close(); } catch (Exception ex) { Console.WriteLine("session abandon: " + ex.Message); } } Session.Abandon(); //abandons the current session user. no need of argument // Session.Remove("currentUser"); Response.Redirect("~/default.aspx"); }
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"); }
private void button1_Click(object sender, EventArgs e) { //mysql tables string erzeugen string createTableQuery = string.Format(@"CREATE TABLE IF NOT EXISTS `{0}` ( `id` int(5) unsigned NOT NULL AUTO_INCREMENT, `Fanteam` VARCHAR(50) NOT NULL, `Rotowire` VARCHAR(50) NOT NULL, `TeamR` VARCHAR(50) NOT NULL, `TeamF` VARCHAR(50) NOT NULL, `Spielerid` smallint(5) unsigned NOT NULL DEFAULT '0', `Datum` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), KEY `Spielerid` (`Spielerid`)) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;", "Referenz"); //mysql connection erzeugen MySqlConnection connection = new MySqlConnection("server=bddiyuk2rbzdbj9pfb9r-mysql.services.clever-cloud.com;database=bddiyuk2rbzdbj9pfb9r;uid=uxayl6sbtpqdhepa;password=QZPnMNX6OIJrkYTkes3F"); connection.Open(); //mysql DB erzeugen var cmd = new MySql.Data.MySqlClient.MySqlCommand(createTableQuery, connection); cmd.ExecuteNonQuery(); connection.Close(); //db auslesen //liste Wettbewerb erzeugen List <string> dbListe = new List <string>(); MySqlCommand command = connection.CreateCommand(); command.CommandText = "SELECT Fanteam FROM Referenz"; MySqlDataReader Reader; connection.Open(); Reader = command.ExecuteReader(); while (Reader.Read()) { string row = ""; for (int i = 0; i < Reader.FieldCount; i++) { row += Reader.GetValue(i).ToString(); dbListe.Add(row); } } connection.Close(); //db auslesen //liste Wettbewerb erzeugen List <string> dbListe2 = new List <string>(); command = connection.CreateCommand(); command.CommandText = "SELECT TeamR FROM Referenz"; connection.Open(); Reader = command.ExecuteReader(); while (Reader.Read()) { string row = ""; for (int i = 0; i < Reader.FieldCount; i++) { row += Reader.GetValue(i).ToString(); dbListe2.Add(row); } } connection.Close(); //CSV in Array speichern string whole_file; string link = String.Join(",", listBox1.Items.OfType <Object>().Select(i => i.ToString()).ToArray()); if (link == "") { info_lbl.Text = "Bitte CSV einfügen!!"; return; } whole_file = System.IO.File.ReadAllText(@link); whole_file = whole_file.Replace('\n', '\r'); string[] lines = whole_file.Split(new char[] { '\r' }, StringSplitOptions.RemoveEmptyEntries); int num_rows = lines.Length; int num_cols = lines[0].Split(';').Length; string[,] Tabelle = new string[num_rows, 10]; string[,] values = new string[num_rows, num_cols]; for (int r = 0; r < num_rows; r++) { string[] line_r = lines[r].Split(';'); for (int c = 0; c < num_cols; c++) { values[r, c] = line_r[c]; } } //db mit csv Prüfen, nur einträge hinzufügen, welche noch nicht vorhanden sind List <string> newlistSpieler = new List <string>(); List <string> newllistTeam = new List <string>(); List <string> newlistSpieler1 = new List <string>(); List <string> newllistTeam1 = new List <string>(); for (int i = 0; i < num_rows; i++) { newlistSpieler.Add(values[i, 0]); newllistTeam.Add(values[i, 2]); newlistSpieler1.Add(values[i, 0]); newllistTeam1.Add(values[i, 2]); } for (int i = 0; i < newlistSpieler.Count(); i++) { for (int j = 0; j < dbListe.Count(); j++) { if (newlistSpieler[i] == dbListe[j] && newllistTeam[i] == dbListe2[j]) { newlistSpieler1.Remove(newlistSpieler[i]); newllistTeam1.Remove(newllistTeam[i]); } } } List <string> newlistSpieler2 = new List <string>(); for (int i = 0; i < newlistSpieler1.Count(); i++) { for (int j = 0; j < num_rows; j++) { if (newlistSpieler1[i] == values[j, 0] && newllistTeam1[i] == values[j, 2]) { newlistSpieler2.Add(values[j, 0]); newlistSpieler2.Add(values[j, 1]); newlistSpieler2.Add(values[j, 2]); newlistSpieler2.Add(values[j, 3]); } } } //letzte db id auslesen command = connection.CreateCommand(); command.CommandText = "SELECT * FROM Referenz"; connection.Open(); Reader = command.ExecuteReader(); while (Reader.Read()) { string row = ""; //Komplette dB auslesen //for (int i = 0; i < Reader.FieldCount; i++) //letzte id auslesen for (int i = 0; i < 1; i++) { row += Reader.GetValue(i).ToString(); } info_lbl.Text = row; } connection.Close(); //daten in db connection.Open(); int h; if (info_lbl.Text.Length == 0) { h = 1; } else { h = Int32.Parse(info_lbl.Text) + 1; } string st2; string st3; int k = 0; string datum = System.DateTime.Now.ToShortDateString(); for (int i = 0; i < newlistSpieler2.Count() / 4; i++) { cmd.CommandText = "INSERT INTO Referenz(id, Fanteam, Rotowire, TeamR, TeamF, Spielerid, Datum) VALUES(@id, @Fanteam, @Rotowire, @TeamR, @TeamF, @Spielerid, @Datum)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@id", h + i); st2 = newlistSpieler2[k]; st3 = st2.Trim(); cmd.Parameters.AddWithValue("@Fanteam", st3); k = k + 1; st2 = newlistSpieler2[k]; st3 = st2.Trim(); cmd.Parameters.AddWithValue("@Rotowire", st3); k = k + 1; st2 = newlistSpieler2[k]; st3 = st2.Trim(); cmd.Parameters.AddWithValue("@TeamR", st3); k = k + 1; st2 = newlistSpieler2[k]; st3 = st2.Trim(); cmd.Parameters.AddWithValue("@TeamF", st3); k = k + 1; cmd.Parameters.AddWithValue("@Spielerid", h + i); cmd.Parameters.AddWithValue("@Datum", datum); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } connection.Close(); info_lbl.Text = "finish!"; }
public bool insert_into_table(DUT_Str dut) { try { MySql.Data.MySqlClient.MySqlDataReader myData; string dut_id = "0"; //get dut_id from serial_number and test_time. bool dataExist = false; cmd.CommandText = string.Format("SELECT Id FROM dut WHERE SerialNumber='{0}' AND TestTime='{1}'", dut.SerailNumber, dut.TestTime); myData = cmd.ExecuteReader(); while (myData.Read()) { //Trace.WriteLine("DUT ID: ", myData[0].ToString()); dut_id = myData[0].ToString(); dataExist = true; } myData.Close(); if (dataExist) { LastError = "DUT already exists"; return(false); } //foreach (string rawCount in dut.RawCountX) //{ // string SQL_insert_query = string.Format("INSERT INTO rawcountaverage (DUTID, ValueIndex, RawCountAverage) VALUES ({0},{1},{2})", dut_id, index, rawCount); // index++; // cmd.CommandText = SQL_insert_query; // cmd.ExecuteNonQuery(); //} //index = 1; //foreach (string noise in dut.NoiseX) //{ // string SQL_insert_query = string.Format("INSERT INTO rawcountnoise (DUTID, ValueIndex, RawCountNoise) VALUES ({0},{1},{2})", dut_id, index, noise); // index++; // cmd.CommandText = SQL_insert_query; // cmd.ExecuteNonQuery(); //} //index = 1; //foreach (string idac in dut.IDAC) //{ // string SQL_insert_query = string.Format("INSERT INTO idacvalue (DUTID, ValueIndex, IDACValue) VALUES ({0},{1},{2})", dut_id, index, idac); // index++; // cmd.CommandText = SQL_insert_query; // cmd.ExecuteNonQuery(); //} //insert into dut table. string SQL_insert_query_dut = "INSERT INTO dut (SerialNumber,TestStation,PartType,ErrorCode,IDDValue,FirmwareVersion,TestTime) "; SQL_insert_query_dut += "VALUES ('" + dut.SerailNumber + "'," + "'" + dut.TestStation + "'," + "'" + dut.PartType + "'," + dut.ErrorCode + "," + dut.IDDValue + "," + dut.FwRev + ",'" + dut.TestTime + "')"; cmd.CommandText = SQL_insert_query_dut; cmd.ExecuteNonQuery(); //get DUTid from current record cmd.CommandText = string.Format("SELECT Id FROM dut WHERE SerialNumber='{0}' AND TestTime='{1}'", dut.SerailNumber, dut.TestTime); myData = cmd.ExecuteReader(); while (myData.Read()) { //Trace.WriteLine("DUT ID: ", myData[0].ToString()); dut_id = myData[0].ToString(); } myData.Close(); int index; //insert RawCount Value if (dut.RawCountX.Capacity > 0) { index = 1; string SQL_insert_query_rawcount = "INSERT INTO rawcountaverage (DUTID, ValueIndex, RawCountAverage) VALUES "; foreach (string rawcount in dut.RawCountX) { SQL_insert_query_rawcount += "(" + dut_id + "," + index.ToString() + "," + rawcount + "),"; index++; } SQL_insert_query_rawcount = SQL_insert_query_rawcount.Substring(0, SQL_insert_query_rawcount.Length - 1); SQL_insert_query_rawcount += ";"; //Trace.WriteLine(SQL_insert_query_rawcount); cmd.CommandText = SQL_insert_query_rawcount; cmd.ExecuteNonQuery(); } //insert Noise Value if (dut.NoiseX.Capacity > 0) { index = 1; string SQL_insert_query_noise = "INSERT INTO rawcountnoise (DUTID, ValueIndex, RawCountNoise) VALUES "; foreach (string noise in dut.NoiseX) { SQL_insert_query_noise += "(" + dut_id + "," + index.ToString() + "," + noise + "),"; index++; } SQL_insert_query_noise = SQL_insert_query_noise.Substring(0, SQL_insert_query_noise.Length - 1); SQL_insert_query_noise += ";"; //Trace.WriteLine(SQL_insert_query_noise); cmd.CommandText = SQL_insert_query_noise; cmd.ExecuteNonQuery(); } //insert IDAC Value if (dut.IDAC.Capacity > 0) { index = 1; string SQL_insert_query_idac = "INSERT INTO idacvalue (DUTID, ValueIndex, IDACValue) VALUES "; foreach (string idac in dut.IDAC) { SQL_insert_query_idac += "(" + dut_id + "," + index.ToString() + "," + idac + "),"; index++; } SQL_insert_query_idac = SQL_insert_query_idac.Substring(0, SQL_insert_query_idac.Length - 1); SQL_insert_query_idac += ";"; //Trace.WriteLine(SQL_insert_query_idac); cmd.CommandText = SQL_insert_query_idac; cmd.ExecuteNonQuery(); } return(true); } catch (Exception ex) { LastError = ex.Message; Trace.WriteLine(ex.Message); return(false); } }
/// <summary> /// Executa operações de update e delete diretamente na base de dados /// </summary> /// <param name="context"></param> /// <param name="query"></param> /// <returns></returns> public static int ExecuteNonQuery(DbContext context, string query) { MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(query, (MySqlConnection)context.Database.Connection); return(command.ExecuteNonQuery()); }
private void button1_Click(object sender, EventArgs e) { //Prüfen ob alle Einträge gemacht sind!! if (FanteamUrl_input.Text.Length == 0) { status_lbl.Text = "URL eingeben"; return; } if (AnzahlSeiten_input.Text.Length == 0) { status_lbl.Text = "Anzahl Seiten eingeben"; return; } if (TurnierID_eingabe.Text.Length == 0) { status_lbl.Text = "TurnierID eingeben"; return; } if (Wettbewerb_combobox.Text.Length == 0) { status_lbl.Text = "Wettbewerb auswählen"; return; } if (FanteamUrl_input.Text.Length != 0 && AnzahlSeiten_input.Text.Length != 0 && TurnierID_eingabe.Text.Length != 0 && Wettbewerb_combobox.Text.Length != 0) { //mysql tables string erzeugen string abcd = string.Format(@"CREATE TABLE IF NOT EXISTS `{0}` ( `id` int(5) unsigned NOT NULL AUTO_INCREMENT, `Wettbewerb` VARCHAR(50) NOT NULL, `Spieler` VARCHAR(50) NOT NULL, `Position` VARCHAR(50) NOT NULL, `home` VARCHAR(50) NOT NULL, `away` VARCHAR(50) NOT NULL, `Form` VARCHAR(50) NOT NULL, `Punkte` VARCHAR(50) NOT NULL, `Preis` VARCHAR(50) NOT NULL, `Spielerid` smallint(5) unsigned NOT NULL DEFAULT '0', `Datum` VARCHAR(50) NOT NULL, `TurnierID` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), KEY `Spielerid` (`Spielerid`)) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;", "FanteamPreise"); //mysql connection erzeugen MySqlConnection con = new MySqlConnection("server=buqhvbltjab1w9h2bks9-mysql.services.clever-cloud.com;Port=20444;database=buqhvbltjab1w9h2bks9;uid=uxayl6sbtpqdhepa;password=QZPnMNX6OIJrkYTkes3F"); con.Open(); //mysql DB erzeugen var cmda = new MySql.Data.MySqlClient.MySqlCommand(abcd, con); cmda.ExecuteNonQuery(); con.Close(); //db auslesen //liste Wettbewerb erzeugen List <string> dbListe1 = new List <string>(); MySqlCommand command1 = con.CreateCommand(); command1.CommandText = "SELECT TurnierID FROM FanteamPreise"; MySqlDataReader Reader1; con.Open(); Reader1 = command1.ExecuteReader(); while (Reader1.Read()) { string row1 = ""; for (int i = 0; i < Reader1.FieldCount; i++) { row1 += Reader1.GetValue(i).ToString(); dbListe1.Add(row1); } } con.Close(); dbListe1 = dbListe1.Distinct().ToList(); int abb = 0; for (int i = 0; i < dbListe1.Count(); i++) { if (dbListe1[i] == TurnierID_eingabe.Text) { abb = 1; } } if (abb != 0) { status_lbl.Text = "Turnier bereits in DB geladen."; return; } if (abb == 0) { //Spielerliste erzeugen List <string> Spielerliste = new List <string>(); //Firefox öffnen IWebDriver driver = new FirefoxDriver(); //URL öffnen driver.Url = FanteamUrl_input.Text; //10 sec warten System.Threading.Thread.Sleep(10000); //javaScript einbinden IJavaScriptExecutor js = driver as IJavaScriptExecutor; //Anzahl Seiten auslesen int x = Int32.Parse(AnzahlSeiten_input.Text); //Daten scrappen for (int j = 0; j < x; j++) { //Tabelle in Webelement speichern IWebElement table = (IWebElement)js.ExecuteScript("return document.querySelector('.ft-view-port').shadowRoot.querySelector('.not-safari').shadowRoot.querySelector('.choices')"); //Webelement in String speichern string table_str = table.Text; //String vereinzeln und in array speichern string[] Spieler_ar = table_str.Split(new Char[] { '\n' }); //Spieler in Liste eintragen for (int i = 5; i < Spieler_ar.Length; i++) { Spielerliste.Add(Spieler_ar[i]); } //nächste Seite js.ExecuteScript("return document.querySelector('.ft-view-port').shadowRoot.querySelector('.not-safari').shadowRoot.querySelector('.section-choises > ft-loading:nth-child(1) > ft-pagination:nth-child(2)').shadowRoot.querySelector('.pagination > li:nth-child(4) > ft-button:nth-child(1)').click()"); } driver.Close(); // Daten in SQL string createTableQuery = string.Format(@"CREATE TABLE IF NOT EXISTS `{0}` ( `id` int(5) unsigned NOT NULL AUTO_INCREMENT, `Wettbewerb` VARCHAR(50) NOT NULL, `Spieler` VARCHAR(50) NOT NULL, `Position` VARCHAR(50) NOT NULL, `home` VARCHAR(50) NOT NULL, `away` VARCHAR(50) NOT NULL, `Form` VARCHAR(50) NOT NULL, `Punkte` VARCHAR(50) NOT NULL, `Preis` VARCHAR(50) NOT NULL, `Spielerid` smallint(5) unsigned NOT NULL DEFAULT '0', `Datum` VARCHAR(50) NOT NULL, `TurnierID` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), KEY `Spielerid` (`Spielerid`)) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;", "FanteamPreise"); //mysql connection erzeugen MySqlConnection connection = new MySqlConnection("server=buqhvbltjab1w9h2bks9-mysql.services.clever-cloud.com;Port=20444;database=buqhvbltjab1w9h2bks9;uid=uxayl6sbtpqdhepa;password=QZPnMNX6OIJrkYTkes3F"); connection.Open(); //mysql DB erzeugen var cmd = new MySql.Data.MySqlClient.MySqlCommand(createTableQuery, connection); cmd.ExecuteNonQuery(); connection.Close(); //letzte db id auslesen MySqlCommand command = connection.CreateCommand(); command.CommandText = "SELECT * FROM FanteamPreise"; MySqlDataReader Reader; connection.Open(); Reader = command.ExecuteReader(); while (Reader.Read()) { string row = ""; //Komplette dB auslesen //for (int i = 0; i < Reader.FieldCount; i++) //letzte id auslesen for (int i = 0; i < 1; i++) { row += Reader.GetValue(i).ToString(); } status_lbl.Text = row; } connection.Close(); //daten in db connection.Open(); int h; if (status_lbl.Text.Length == 0) { h = 1; } else { h = Int32.Parse(status_lbl.Text) + 1; } int k = 0; string st2; string st3; string datum = System.DateTime.Now.ToShortDateString(); for (int i = 0; i < Spielerliste.Count / 7; i++) { cmd.CommandText = "INSERT INTO FanteamPreise(id, Spieler, Position, home, away, Form, Punkte, Preis, Spielerid, Wettbewerb, Datum, TurnierID) VALUES(@id, @Spieler, @Position, @home, @away, @Form, @Punkte, @Preis, @Spielerid, @Wettbewerb, @Datum, @TurnierID)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@id", h + i); st2 = Spielerliste[k]; st3 = st2.Trim(); cmd.Parameters.AddWithValue("@Position", st3); k = k + 1; st2 = Spielerliste[k]; st3 = st2.Trim(); cmd.Parameters.AddWithValue("@Spieler", st3); k = k + 1; st2 = Spielerliste[k]; st3 = st2.Trim(); cmd.Parameters.AddWithValue("@home", st3); k = k + 1; st2 = Spielerliste[k]; st3 = st2.Trim(); cmd.Parameters.AddWithValue("@away", st3); k = k + 1; st2 = Spielerliste[k]; st3 = st2.Trim(); cmd.Parameters.AddWithValue("@Form", st3); k = k + 1; st2 = Spielerliste[k]; st3 = st2.Trim(); cmd.Parameters.AddWithValue("@Punkte", st3); k = k + 1; st2 = Spielerliste[k]; st3 = st2.Trim(); cmd.Parameters.AddWithValue("@Preis", st3); k = k + 1; cmd.Parameters.AddWithValue("@Spielerid", k); cmd.Parameters.AddWithValue("@Wettbewerb", Wettbewerb_combobox.Text); cmd.Parameters.AddWithValue("@Datum", datum); cmd.Parameters.AddWithValue("@TurnierID", TurnierID_eingabe.Text); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } connection.Close(); status_lbl.Text = "finished!"; } } }
protected void Add_Teacher_Submit(object sender, EventArgs e1) { //Response.Write("<script>alert( 'Hi, Soham Pagla')</script>"); ScriptManager.RegisterStartupScript(this, this.GetType(), "alerts", "javascript:alert('hai')", true); DateTime now = DateTime.Now; MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connection); string queryStr, qs1, queryStrr, qs2; MySql.Data.MySqlClient.MySqlCommand cmd, cmd1, cmd2, cmd3; string sid1, sid2, sid3, sid4, sid5; string RollOrStaffId; string Name = this.st_name.Value; string Email_ID = this.st_email.Value; string User_Type; string Pwd; string Y_O_Join = this.st_year.Value; string DOB = this.st_dob.Value; string User_Img = Image_Upload2.FileName; if (Image_Upload2.HasFile) { string fileExtention = System.IO.Path.GetExtension(Image_Upload2.FileName); if (fileExtention.ToLower() != ".jpg" && fileExtention.ToLower() != ".jpeg") { this.err_msg_staff.Visible = true; this.submit_msg_staff.Text = ".jpg and .jpeg file only"; submit_msg_staff.ForeColor = System.Drawing.Color.Red; } else { this.err_msg_staff.Visible = false; Image_Upload2.SaveAs(Server.MapPath("~/Picture/" + Image_Upload2.FileName)); sid1 = this.st_dept.Value; sid2 = this.st_year.Value; var chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"; var stringChars = new char[8]; var random = new Random(); for (int i = 0; i < stringChars.Length; i++) { stringChars[i] = chars[random.Next(chars.Length)]; } var finalString = new String(stringChars); conn.Open(); try { queryStrr = "Select Staff_ID FROM student.staff Where Department='" + this.st_dept.Value + "' Order By Staff_ID Desc limit 1 ;"; string s4 = string.Empty;; cmd1 = new MySql.Data.MySqlClient.MySqlCommand(queryStrr, conn); MySqlDataReader reader = cmd1.ExecuteReader(); while (reader.Read()) { s4 += reader.GetString("Staff_ID"); } if (string.IsNullOrEmpty(s4)) { sid3 = sid1 + sid2 + "5001"; } else { sid4 = s4.Substring(s4.Length - 4); int i1 = Int32.Parse(sid4); int i2 = i1 + 1; sid5 = i2.ToString(); sid3 = sid1 + sid2 + sid5; } reader.Close(); qs1 = "SELECT * FROM student.staff WHERE Email_ID = '" + this.st_email.Value + "';"; cmd2 = new MySql.Data.MySqlClient.MySqlCommand(qs1, conn); MySqlDataReader rd = cmd2.ExecuteReader(); if (!rd.Read()) { rd.Close(); queryStr = "Insert into student.staff(Staff_ID,Name,Email_ID,Alt_Email,Contact_No,D_O_B,Address,Country,Pin_no,Gender,Department,Date_of_joining) values ('" + sid3 + "','" + this.st_name.Value + "','" + this.st_email.Value + "','" + this.st_alt_email.Value + "','" + this.st_contact.Value + "','" + this.st_dob.Value + "','" + this.st_ads.Value + "','" + this.st_country.Value + "','" + this.st_pin.Value + "','" + this.st_gender.Text + "','" + this.st_dept.Value + "','" + this.st_year.Value + "')"; // queryStr = "Insert into student.staff(Staff_ID,Name,Email_ID,Alt_Email,Contact_No,D_O_B,Address,Country,Pin_no,Gender,Department,Date_of_joining) values ('123','" + this.st_name.Value + "','" + this.st_email.Value + "','" + this.st_alt_email.Value + "','" + this.st_contact.Value + "','" + this.st_dob.Value + "','" + this.st_ads.Value + "','" + this.st_country.Value + "','" + this.st_pin.Value + "','" + this.st_gender.Text + "','" + this.st_dept.Value + "','" + this.st_year.Value + "')"; cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn); cmd.ExecuteNonQuery(); RollOrStaffId = sid3; Pwd = finalString; User_Type = "Staff"; qs2 = "INSERT INTO student.user (RollOrStaffId,Name,Email_ID,User_Type,Pwd,Y_O_Join,DOB,User_Img) VALUES (@RollOrStaffId, @Name, @Email_ID, @User_Type, @Pwd, @Y_O_Join, @DOB, @User_Img) ;"; cmd3 = new MySql.Data.MySqlClient.MySqlCommand(qs2, conn); cmd3.Parameters.AddWithValue("RollOrStaffId", @RollOrStaffId); cmd3.Parameters.AddWithValue("Name", @Name); cmd3.Parameters.AddWithValue("Email_ID", @Email_ID); cmd3.Parameters.AddWithValue("User_Type", @User_Type); cmd3.Parameters.AddWithValue("Pwd", @Pwd); cmd3.Parameters.AddWithValue("Y_O_Join", @Y_O_Join); cmd3.Parameters.AddWithValue("DOB", @DOB); cmd3.Parameters.AddWithValue("User_Img", @User_Img); cmd3.ExecuteNonQuery(); Response.Write("<script>alert( 'Sucessfully Added To The Database')</script>"); } else { Response.Write("<script>alert('This Email Is Already Exist In Our Database'); </script>"); this.st_email.Value = "Enter Another Email Id.."; } } catch (Exception) { throw; } finally { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } } } else { submit_msg_staff.Text = "No File Selected"; } }
protected void Add_Student_Submit(object sender, EventArgs e) { //Response.Redirect("~/test.aspx"); //Page.Title = "Add New Student"; string student_name = name.Text; string s_father_name = fname.Value; string s_mother_name = mname.Value; string s_email = email.Value; string s_alt = alt_email.Value; string s_contact = contact.Value; string s_dob = birthDate.Value.ToString(); string s_address = adds.Value; string s_pin = pin.Value; string s_gen = gender.Text; string RollOrStaffId; string Name = this.name.Text; string Email_ID = this.email.Value; string User_Type; string Pwd; string Y_O_Join = this.std_session.Value; string DOB = this.birthDate.Value; string User_Img = Image_Upload1.FileName; if (Image_Upload1.HasFile) { string fileExtention = System.IO.Path.GetExtension(Image_Upload1.FileName); if (fileExtention.ToLower() != ".jpg" && fileExtention.ToLower() != ".jpeg") { this.err_msg.Visible = true; this.submit_msg.Text = ".jpg and .jpeg file only"; submit_msg.ForeColor = System.Drawing.Color.Red; } else { this.err_msg.Visible = false; Image_Upload1.SaveAs(Server.MapPath("~/Picture/" + Image_Upload1.FileName)); string s1 = std_dept.Value; // string date = DateTime.Today.ToShortDateString(); string s2 = this.std_session.Value; string s3, s5, s6; MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connection); string queryStr, queryStrr, qs1, qs2; MySql.Data.MySqlClient.MySqlCommand cmd, cmd1, cmd2, cmd3; conn.Open(); var chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"; var stringChars = new char[8]; var random = new Random(); for (int i = 0; i < stringChars.Length; i++) { stringChars[i] = chars[random.Next(chars.Length)]; } var finalString = new String(stringChars); try { queryStrr = "Select Roll_No FROM student.student Where Department='" + this.std_dept.Value + "' Order By Student_ID Desc limit 1 ;"; string s4 = string.Empty;; cmd1 = new MySql.Data.MySqlClient.MySqlCommand(queryStrr, conn); MySqlDataReader reader = cmd1.ExecuteReader(); while (reader.Read()) { s4 += reader.GetString("Roll_No"); } if (string.IsNullOrEmpty(s4)) { s3 = s1 + s2 + "7001"; } else { //Response.Write("<script>alert( 'hi')</script>"); s5 = s4.Substring(s4.Length - 4); int i1 = Int32.Parse(s5); int i2 = i1 + 1; s6 = i2.ToString(); s3 = s1 + s2 + s6; //Response.Write("<script>alert( 'Sucessfully Added To The Database')</script>"); } reader.Close(); qs1 = "SELECT * FROM student.student WHERE Email_ID = '" + Email_ID + "'; "; cmd2 = new MySql.Data.MySqlClient.MySqlCommand(qs1, conn); MySqlDataReader mail_rd = cmd2.ExecuteReader(); if (!mail_rd.Read()) { queryStr = "Insert into student.student(Name,F_Name,M_Name,Email_ID,Alt_Email_ID,Date_Of_Birth,Contact_No,Address,Pin_no,Country,Gender,Roll_No,Department,Pwd,Session) values ('" + this.name.Text + "','" + this.fname.Value + "','" + this.mname.Value + "','" + this.email.Value + "','" + this.alt_email.Value + "','" + this.birthDate.Value + "','" + this.contact.Value + "','" + this.adds.Value + "','" + this.pin.Value + "','" + this.country.Value + "','" + this.gender.Text + "','" + s3 + "','" + this.std_dept.Value + "','" + finalString + "','" + this.std_session.Value + "');"; cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn); cmd.ExecuteNonQuery(); RollOrStaffId = s3; Pwd = finalString; User_Type = "Student"; qs2 = "INSERT INTO student.user (RollOrStaffId,Name,Email_ID,User_Type,Pwd,Y_O_Join,DOB,User_Img) VALUES (@RollOrStaffId, @Name, @Email_ID, @User_Type, @Pwd, @Y_O_Join, @DOB, @User_Img) ;"; cmd3 = new MySql.Data.MySqlClient.MySqlCommand(qs2, conn); cmd3.Parameters.AddWithValue("RollOrStaffId", @RollOrStaffId); cmd3.Parameters.AddWithValue("Name", @Name); cmd3.Parameters.AddWithValue("Email_ID", @Email_ID); cmd3.Parameters.AddWithValue("User_Type", @User_Type); cmd3.Parameters.AddWithValue("Pwd", @Pwd); cmd3.Parameters.AddWithValue("Y_O_Join", @Y_O_Join); cmd3.Parameters.AddWithValue("DOB", @DOB); cmd3.Parameters.AddWithValue("User_Img", @User_Img); cmd3.ExecuteNonQuery(); Response.Write("<script>alert( 'Sucessfully Added To The Database')</script>"); //string mymailid = "*****@*****.**"; //string mypwd = "soh123@am"; //string mailbody = "Name: " + this.name +", Roll No: "+ s3 + ", Password: "******"Account Details of New Admission "; //msg.Body = mailbody; //SmtpClient sc = new SmtpClient("smtp.gmail.com"); //sc.Port = 578; //sc.EnableSsl = true; //sc.Credentials = new NetworkCredential(mymailid,mypwd); //sc.Send(msg); //Response.Write("Mail Sent... :) "); } else { Response.Write("<script>alert('This Email Is Already Exist In Our Database'); </script>"); } } catch (Exception) { throw; } finally { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } } } else { submit_msg.Text = "No File Selected"; } }
protected void validateCreate(object sender, EventArgs e) { if (privacy_checkbox.Checked) { aUser.StateOrProvince = state_text_box.SelectedValue; aUser.StreetAddress = cust_mail_address.Text; aUser.ZipCode = zipcode.Text; aUser.FirstName = first_name.Text; aUser.LastName = last_name.Text; aUser.Homephone = homephone.Text; aUser.CellPhone = cellphone.Text; aUser.EmailAddress = email_signup.Text; aUser.Password = password_signup.Text; aUser.SecurityQuestion = security_questions.SelectedValue; aUser.SecurityQuestionAnswer = security_answers.Text; ((List <UserInfo>)Application["AllUsersList"]).Add(aUser); string msgTo = email_signup.Text; string msgSubject = "New Signing Up Notification"; string msgBody = "Dear User " + msgTo + ",<br /><br />" + "Thank you for signing up with us. <br/> <br />" + "You can now access your Rapid Bill Pay account at <a href='http://dcm.uhcl.edu/c432017fa01tirunagarus/'>Rapid Bill Pay </a> " + "<br /><br />" + "Thank you again for your Signing Up. If you have any questions, please contact us at" + "<a href='http://dcm.uhcl.edu/c432017fa01tirunagarus/contactus.aspx'>Contact Us </a> " + "<br /><br />" + "With Best Wishes, <br />" + "Sumanjali Tirunagaru"; MailMessage mailObj = new MailMessage(); mailObj.Body = msgBody; mailObj.From = new MailAddress("*****@*****.**", "Admin Team"); mailObj.To.Add(new MailAddress(msgTo)); mailObj.Subject = msgSubject; mailObj.IsBodyHtml = true; SmtpClient smtpClient = new System.Net.Mail.SmtpClient("smtp.gmail.com", 587); smtpClient.UseDefaultCredentials = false; smtpClient.Credentials = new System.Net.NetworkCredential("*****@*****.**", "!hahahaha"); smtpClient.EnableSsl = true; try { smtpClient.Send(mailObj); } catch (Exception ex) { } //db updating MySql.Data.MySqlClient.MySqlConnection co; string connection = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString(); co = new MySql.Data.MySqlClient.MySqlConnection(connection); co.Open(); string query = "INSERT INTO `c432017fa01tirunagarus`.`tirunagarus_WADfl17_UserInfo`(`emailAddress`,`lastName`,`firstName`,`homePhone`,`password`,`cellPhone`,`securityQuestion`,`securityQuestionAnswer`,`StreetAddress`,`ZipCode`) VALUES ('" + aUser.EmailAddress + "','" + aUser.FirstName + "','" + aUser.LastName + "','" + aUser.Homephone + "','" + aUser.Password + "','" + aUser.CellPhone + "','" + aUser.SecurityQuestion + "','" + aUser.SecurityQuestionAnswer + "','" + aUser.StreetAddress + "','" + aUser.ZipCode + "') "; MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(query, co); int rows = command.ExecuteNonQuery(); co.Close(); string strconfirm = "<script>if(window.confirm('Thanks your for signing up. You can now login using the Log in option. An email has also been sent to email address you provided during sign up')){window.location.href='default.aspx'}</script>"; ClientScript.RegisterStartupScript(this.GetType(), "Confirm", strconfirm, false); } else { ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Please Accept Terms And Conditions')", true); } }
public virtual void Configure(MySqlConnection connection) { bool firstConfigure = false; // if we have not already configured our server variables // then do so now if (serverProps == null) { firstConfigure = true; // if we are in a pool and the user has said it's ok to cache the // properties, then grab it from the pool if (Pool != null && Settings.CacheServerProperties) { if (Pool.ServerProperties == null) { Pool.ServerProperties = LoadServerProperties(connection); } serverProps = Pool.ServerProperties; } else { serverProps = LoadServerProperties(connection); } LoadCharacterSets(connection); } #if AUTHENTICATED string licenseType = serverProps["license"]; if (licenseType == null || licenseType.Length == 0 || licenseType != "commercial") { throw new MySqlException("This client library licensed only for use with commercially-licensed MySQL servers."); } #endif // if the user has indicated that we are not to reset // the connection and this is not our first time through, // then we are done. if (!Settings.ConnectionReset && !firstConfigure) { return; } string charSet = connectionString.CharacterSet; if (charSet == null || charSet.Length == 0) { if (serverCharSetIndex >= 0) { charSet = (string)charSets[serverCharSetIndex]; } else { charSet = serverCharSet; } } // now tell the server which character set we will send queries in and which charset we // want results in MySqlCommand charSetCmd = new MySqlCommand("SET character_set_results=NULL", connection); charSetCmd.InternallyCreated = true; object clientCharSet = serverProps["character_set_client"]; object connCharSet = serverProps["character_set_connection"]; if ((clientCharSet != null && clientCharSet.ToString() != charSet) || (connCharSet != null && connCharSet.ToString() != charSet)) { MySqlCommand setNamesCmd = new MySqlCommand("SET NAMES " + charSet, connection); setNamesCmd.InternallyCreated = true; setNamesCmd.ExecuteNonQuery(); } charSetCmd.ExecuteNonQuery(); if (charSet != null) { Encoding = CharSetMap.GetEncoding(Version, charSet); } else { Encoding = CharSetMap.GetEncoding(Version, "latin1"); } handler.Configure(); }
public int Execute() { bool flag = false; if (this.connection == null) { throw new InvalidOperationException(Resources.ConnectionNotSet); } if (this.query == null || this.query.Length == 0) { return(0); } if (this.connection.State != ConnectionState.Open) { flag = true; this.connection.Open(); } bool allowUserVariables = this.connection.Settings.AllowUserVariables; this.connection.Settings.AllowUserVariables = true; int result; try { string expr_83 = StringUtility.ToUpperInvariant(this.connection.driver.Property("sql_mode")); bool ansiQuotes = expr_83.IndexOf("ANSI_QUOTES") != -1; bool noBackslashEscapes = expr_83.IndexOf("NO_BACKSLASH_ESCAPES") != -1; List <ScriptStatement> arg_BF_0 = this.BreakIntoStatements(ansiQuotes, noBackslashEscapes); int num = 0; MySqlCommand mySqlCommand = new MySqlCommand(null, this.connection); foreach (ScriptStatement current in arg_BF_0) { if (!string.IsNullOrEmpty(current.text)) { mySqlCommand.CommandText = current.text; try { mySqlCommand.ExecuteNonQuery(); num++; this.OnQueryExecuted(current); } catch (Exception ex) { if (this.Error == null) { throw; } if (!this.OnScriptError(ex)) { break; } } } } this.OnScriptCompleted(); result = num; } finally { this.connection.Settings.AllowUserVariables = allowUserVariables; if (flag) { this.connection.Close(); } } return(result); }
private void button2_Click(object sender, EventArgs e) { //mysql tables string erzeugen string createTableQuery = string.Format(@"CREATE TABLE IF NOT EXISTS `{0}` ( `id` int(5) unsigned NOT NULL AUTO_INCREMENT, `Fanteam` VARCHAR(50) NOT NULL, `Rotowire` VARCHAR(50) NOT NULL, `TeamR` VARCHAR(50) NOT NULL, `TeamF` VARCHAR(50) NOT NULL, `Spielerid` smallint(5) unsigned NOT NULL DEFAULT '0', `Datum` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), KEY `Spielerid` (`Spielerid`)) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;", "Referenz"); //mysql connection erzeugen MySqlConnection connection = new MySqlConnection("server=bddiyuk2rbzdbj9pfb9r-mysql.services.clever-cloud.com;database=bddiyuk2rbzdbj9pfb9r;uid=uxayl6sbtpqdhepa;password=QZPnMNX6OIJrkYTkes3F"); connection.Open(); //mysql DB erzeugen var cmd = new MySql.Data.MySqlClient.MySqlCommand(createTableQuery, connection); cmd.ExecuteNonQuery(); connection.Close(); //db auslesen //Spielerliste Fanteam erzeugen List <string> SpielerFanteam = new List <string>(); List <string> SpielerFanteam1 = new List <string>(); MySqlCommand command = connection.CreateCommand(); command.CommandText = "SELECT Spieler FROM FanteamPreise"; MySqlDataReader Reader; connection.Open(); Reader = command.ExecuteReader(); while (Reader.Read()) { string row = ""; for (int i = 0; i < Reader.FieldCount; i++) { row += Reader.GetValue(i).ToString(); SpielerFanteam.Add(row); SpielerFanteam1.Add(row); } } SpielerFanteam = SpielerFanteam.Distinct().ToList(); SpielerFanteam1 = SpielerFanteam1.Distinct().ToList(); //array für ausgabe erzeugen und Spieler fanteam hinzufügen string[,] array = new string[SpielerFanteam.Count(), 3]; for (int i = 0; i < SpielerFanteam.Count(); i++) { array[i, 0] = SpielerFanteam[i]; } connection.Close(); //db auslesen //heim und auswärts auslesen List <string> All = new List <string>(); List <string> All1 = new List <string>(); command.CommandText = "SELECT * FROM FanteamPreise"; connection.Open(); Reader = command.ExecuteReader(); while (Reader.Read()) { string row = ""; for (int i = 0; i < Reader.FieldCount; i++) { row = Reader.GetValue(i).ToString(); All.Add(row); All1.Add(row); } } connection.Close(); for (int i = 0; i < SpielerFanteam.Count(); i++) { for (int j = 0; j < All.Count(); j++) { if (array[i, 0] == All[j]) { array[i, 1] = All[j + 2]; array[i, 2] = All[j + 3]; break; } } } //db auslesen //Referenzliste auslesen List <string> Referenz = new List <string>(); command.CommandText = "SELECT * FROM Referenz"; connection.Open(); Reader = command.ExecuteReader(); while (Reader.Read()) { string row = ""; for (int i = 0; i < Reader.FieldCount; i++) { row = Reader.GetValue(i).ToString(); Referenz.Add(row); } } connection.Close(); for (int i = 0; i < SpielerFanteam.Count(); i++) { for (int j = 0; j < Referenz.Count() - 3; j++) { if (array[i, 0] == Referenz[j] && array[i, 1] == Referenz[j + 3] || array[i, 0] == Referenz[j] && array[i, 2] == Referenz[j + 3]) { array[i, 0] = ""; array[i, 1] = ""; array[i, 2] = ""; } } } List <string> neu = new List <string>(); for (int i = 0; i < SpielerFanteam.Count(); i++) { if (array[i, 0] != "") { neu.Add(array[i, 0]); neu.Add(array[i, 1]); neu.Add(array[i, 2]); } } string[,] array2 = new string[neu.Count(), 3]; int k = 0; for (int i = 0; i < neu.Count() / 3; i++) { for (int j = 0; j < 3; j++) { array2[i, j] = neu[k]; k++; } } /* * //db auslesen * //liste Wettbewerb erzeugen * List<string> AuswärtsFanteam = new List<string>(); * List<string> AuswärtsFanteam1 = new List<string>(); * command.CommandText = "SELECT away FROM FanteamPreise"; * connection.Open(); * Reader = command.ExecuteReader(); * while (Reader.Read()) * { * string row = ""; * for (int i = 0; i < Reader.FieldCount; i++) * { * row += Reader.GetValue(i).ToString(); * AuswärtsFanteam.Add(row); * AuswärtsFanteam1.Add(row); * } * } * connection.Close(); * * //db auslesen * //liste Wettbewerb erzeugen * List<string> NameReferenzFanteam = new List<string>(); * List<string> NameReferenzFanteam1 = new List<string>(); * command.CommandText = "SELECT Fanteam FROM Referenz"; * connection.Open(); * Reader = command.ExecuteReader(); * while (Reader.Read()) * { * string row = ""; * for (int i = 0; i < Reader.FieldCount; i++) * { * row += Reader.GetValue(i).ToString(); * NameReferenzFanteam.Add(row); * NameReferenzFanteam1.Add(row); * } * } * connection.Close(); * * //db auslesen * //liste Wettbewerb erzeugen * List<string> TeamReferenzFanteam = new List<string>(); * List<string> TeamReferenzFanteam1 = new List<string>(); * command.CommandText = "SELECT TeamF FROM Referenz"; * connection.Open(); * Reader = command.ExecuteReader(); * while (Reader.Read()) * { * string row = ""; * for (int i = 0; i < Reader.FieldCount; i++) * { * row += Reader.GetValue(i).ToString(); * TeamReferenzFanteam.Add(row); * TeamReferenzFanteam1.Add(row); * } * } * connection.Close(); * * * for (int i=0;i<TeamReferenzFanteam.Count();i++) * { * for(int j=0; j<AuswärtsFanteam.Count();j++) * { * if((SpielerFanteam[j]==NameReferenzFanteam[i] && TeamReferenzFanteam[i]== HeimFanteam[j])|| (SpielerFanteam[j] == NameReferenzFanteam[i] && TeamReferenzFanteam[i] == AuswärtsFanteam[j])) * { * SpielerFanteam1.Remove(SpielerFanteam[j]); * HeimFanteam1.Remove(HeimFanteam[j]); * AuswärtsFanteam1.Remove(AuswärtsFanteam[j]); * * } * * } * } */ using (StreamWriter writer = new StreamWriter(@"C:\FantasySports\Referenztabelle\Referenz.csv")) { for (int l = 0; l < neu.Count() / 3; l++) { writer.WriteLine(array2[l, 0] + ";" + array2[l, 1] + ";" + array2[l, 2]); } } info_lbl.Text = "Fertig"; }
protected void MakeArrangement(object sender, EventArgs e) { MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connection); string queryStr, qst1, qst2; MySql.Data.MySqlClient.MySqlCommand cmd; string Time = this.mk_ar_time.Value.ToString(); string Date = this.txtDate.Text; conn.Open(); try { qst1 = "Select Roll_No FROM student.student Where Department='" + this.mk_ar_dept1.Value + "'; "; string s1 = string.Empty;; cmd = new MySql.Data.MySqlClient.MySqlCommand(qst1, conn); MySqlDataReader reader1 = cmd.ExecuteReader(); while (reader1.Read()) { s1 += reader1.GetString("Roll_No"); } conn.Close(); conn.Open(); qst2 = "Select Roll_No FROM student.student Where Department='" + this.mk_ar_dept2.Value + "'; "; string s2 = string.Empty;; cmd = new MySql.Data.MySqlClient.MySqlCommand(qst2, conn); MySqlDataReader reader2 = cmd.ExecuteReader(); while (reader2.Read()) { s2 += reader2.GetString("Roll_No"); } conn.Close(); conn.Open(); string strtroll1 = this.mk_ar_strtroll1.Value; string strtroll2 = this.mk_ar_strtroll2.Value; string sr1, sr2; sr1 = strtroll1; sr2 = strtroll2; //if (s1 != strtroll1) //{ // this.mk_ar_strtroll1.Value = "Enter Valid Roll No."; //} //else if (s2 != strtroll2) { // this.mk_ar_strtroll2.Value = "Enter Valid Roll No."; //} //else //{ int r = 0; int row = Int32.Parse(this.mk_ar_row.Value); int col = Int32.Parse(this.mk_ar_col.Value); string[] arr = new string[10]; for (int k = 0; k < 10; k++) { arr[k] = "NULL"; } string stroll1, stroll2; int l, ii1, ii2, i1, i2; string sub1, sub2; while (r < row) { if (r % 2 == 0) { for (l = 0; l < col; l++) { if (l % 2 == 0) { arr[l] = strtroll1; stroll1 = strtroll1.Substring(strtroll1.Length - 4); sub1 = strtroll1.Substring(0, 7); i1 = Int32.Parse(stroll1); i2 = i1 + row; strtroll1 = i2.ToString(); strtroll1 = sub1 + strtroll1; } else { arr[l] = strtroll2; stroll2 = strtroll2.Substring(strtroll2.Length - 4); sub2 = strtroll2.Substring(0, 7); ii1 = Int32.Parse(stroll2); ii2 = ii1 + row; strtroll2 = ii2.ToString(); strtroll2 = sub2 + strtroll2; } } queryStr = "Insert into student.arrangement(Date, Time, Exam_Type,Room_no, Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10) values (@Date, @Time ,'" + this.mk_ar_exmtype.Value + "','" + this.mk_ar_roomno.Value + "','" + arr[0] + "','" + arr[1] + "','" + arr[2] + "','" + arr[3] + "','" + arr[4] + "','" + arr[5] + "','" + arr[6] + "','" + arr[7] + "','" + arr[8] + "','" + arr[9] + "');"; cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn); cmd.Parameters.AddWithValue("Date", @Date); cmd.Parameters.AddWithValue("Time", @Time); cmd.ExecuteNonQuery(); } else { for (l = 0; l < col; l++) { if (l % 2 == 0) { arr[l] = strtroll2; stroll2 = strtroll2.Substring(strtroll2.Length - 4); sub2 = strtroll2.Substring(0, 7); i1 = Int32.Parse(stroll2); i2 = i1 + row; strtroll2 = i2.ToString(); strtroll2 = sub2 + strtroll2; } else { arr[l] = strtroll1; stroll1 = strtroll1.Substring(strtroll1.Length - 4); sub1 = strtroll1.Substring(0, 7); ii1 = Int32.Parse(stroll1); ii2 = ii1 + row; strtroll1 = ii2.ToString(); strtroll1 = sub1 + strtroll1; } } queryStr = "Insert into student.arrangement(Date, Time, Exam_Type,Room_no, Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10) values (@Date, @Time, '" + this.mk_ar_exmtype.Value + "','" + this.mk_ar_roomno.Value + "','" + arr[0] + "','" + arr[1] + "','" + arr[2] + "','" + arr[3] + "','" + arr[4] + "','" + arr[5] + "','" + arr[6] + "','" + arr[7] + "','" + arr[8] + "','" + arr[9] + "');"; cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn); cmd.Parameters.AddWithValue("Date", @Date); cmd.Parameters.AddWithValue("Time", @Time); cmd.ExecuteNonQuery(); } r = r + 1; stroll1 = sr1.Substring(sr1.Length - 4); sub1 = sr1.Substring(0, 7); i1 = Int32.Parse(stroll1); i2 = i1 + 1; strtroll1 = i2.ToString(); strtroll1 = sub1 + strtroll1; sr1 = strtroll1; stroll2 = sr2.Substring(sr2.Length - 4); sub2 = sr2.Substring(0, 7); ii1 = Int32.Parse(stroll2); ii2 = ii1 + 1; strtroll2 = ii2.ToString(); strtroll2 = sub2 + strtroll2; sr2 = strtroll2; } } catch (Exception) { throw; } finally { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } }
public bool CheckRequestData(VehicleContainer inputModel) { ///////////////////////// процедура приема (сначала пишется файл - 7 строк, затем обработка для распихивания по таблицам) ConnectStr conStrKl1 = new ConnectStr(); conStrKl1.ConStr(1); cstrU1 = conStrKl1.StP; if (inputModel.ID < 2832035 || inputModel.ID >= 2832037) { using (MySqlConnection sqlConnectionT1 = new MySqlConnection(cstrU1)) { long IP = Convert.ToInt64(inputModel.ID); sqlConnectionT1.Open(); MySqlTransaction transaction = sqlConnectionT1.BeginTransaction(); string NamF = DateTime.Now.ToString("yyyyMMdd") + "_" + DateTime.Now.ToString("HHmmss") + "_" + inputModel.CreatedBy.PlatformId + "_" + inputModel.ID; ////string NamF = DateTime.Now + "_" + inputModel.ID; //using (FileStream stream = new FileStream(@"C:\Users\cherednikov\Desktop\АКТЫ\" + NamF.ToString() + ".xml", FileMode.CreateNew)) using (FileStream stream = new FileStream(@"F:\archiv\XML\" + NamF.ToString() + ".xml", FileMode.CreateNew)) { XmlSerializer serializer = new XmlSerializer(typeof(VehicleContainer)); serializer.Serialize(stream, inputModel); stream.Flush(); //stream.Close(); } //string aaa = @"C:\Users\cherednikov\Desktop\АКТЫ\" + DateTime.Now.AddDays(-1).ToString("yyyyMMdd"); string aaa = @"F:\archiv\XML\" + DateTime.Now.AddDays(-1).ToString("yyyyMMdd"); //CopyFolderYesterdayFiles(@"C:\Users\cherednikov\Desktop\АКТЫ", aaa); CopyFolderYesterdayFiles(@"F:\archiv\XML", aaa); KlSch = new string[4]; int j = 0; //////////////////////////////////// класс-схема и индекс ////////////////////////////////////// foreach (Classification Cl in inputModel.Classification) { KlSch[j] = Cl.Index.ToString(); j = j + 1; KlSch[j] = Cl.Schema.ToString(); j = j + 1; } //////////////////////////////////////////////////////////////////////////////////////// using (MySql.Data.MySqlClient.MySqlCommand cmd1 = new MySql.Data.MySqlClient.MySqlCommand(St1(inputModel), sqlConnectionT1)) { if (sqlConnectionT1.State == System.Data.ConnectionState.Closed) { sqlConnectionT1.Open(); } cmd1.ExecuteNonQuery(); } PDK = new string[inputModel.AxleCount, 30]; foreach (AxleContainer a in inputModel.Axles) { using (MySql.Data.MySqlClient.MySqlCommand cmd1 = new MySql.Data.MySqlClient.MySqlCommand(StrAxel(a, IP), sqlConnectionT1)) { if (sqlConnectionT1.State == System.Data.ConnectionState.Closed) { sqlConnectionT1.Open(); } cmd1.ExecuteNonQuery(); } } Im = new string[15]; co = 0; foreach (BinaryContainer b in inputModel.Binaries) { string BS = @"F:\\archivACT\\Photo\\" + DateTime.Now.ToString("dd_MM_yyyy") + @"\\" + IP.ToString() + "_" + b.CreatedBy.PlatformId.ToString() + "_" + b.Created.ToString("yyyyMMdd_HHmmss") + "_" + co.ToString(); if (b.Name != "Video") { if (b == null) { continue; } Im[co] = b.ID.ToString(); co = co + 1; CO = CO + 1; //b.CreatedBy.PlatformId // b.Data; //if (!System.IO.Directory.Exists(@"C:\Users\cherednikov\Desktop\АКТЫ\" + DateTime.Now.ToString("dd_MM_yyyy") + @"\")) if (!System.IO.Directory.Exists(@"F:\archivACT\Photo\" + DateTime.Now.ToString("dd_MM_yyyy") + @"\")) { //System.IO.Directory.CreateDirectory(@"C:\Users\cherednikov\Desktop\АКТЫ\" + DateTime.Now.ToString("dd_MM_yyyy") + @"\"); System.IO.Directory.CreateDirectory(@"F:\archivACT\Photo\" + DateTime.Now.ToString("dd_MM_yyyy") + @"\"); } // System.DrawingCore.Image imgg = byteArrayToImage(b.Data); //string BS = @"C:\Users\cherednikov\Desktop\АКТЫ\" + DateTime.Now.ToString("dd_MM_yyyy") + @"\"+IP.ToString()+"_"+b.CreatedBy.PlatformId.ToString()+"_"+b.Created.ToString("yyyyMMdd_HHmmss") + "_" + co.ToString(); BS = @"F:\\archivACT\\Photo\\" + DateTime.Now.ToString("dd_MM_yyyy") + @"\\" + IP.ToString() + "_" + b.Name.ToString() + "_" + b.CreatedBy.PlatformId.ToString() + "_" + b.Created.ToString("yyyyMMdd_HHmmss") + "_" + CO.ToString(); //Bitmap aa = byteArrayToImage(b.Data); //aa.Save(@"C:\Users\cherednikov\Desktop\АКТЫ\123.png", System.DrawingCore.Imaging.ImageFormat.Png); File.WriteAllBytes(BS + ".png", b.Data); //aa.Save(BS + ".png", System.DrawingCore.Imaging.ImageFormat.Png); ////imgg.Save(BS + "_" + co + ".Jpeg", System.DrawingCore.Imaging.ImageFormat.Jpeg); //BinStr = @BS.ToString(); using (MySql.Data.MySqlClient.MySqlCommand cmd1 = new MySql.Data.MySqlClient.MySqlCommand(StrBinryN(b, IP, BS + ".png"), sqlConnectionT1)) { if (sqlConnectionT1.State == System.Data.ConnectionState.Closed) { sqlConnectionT1.Open(); } cmd1.ExecuteNonQuery(); } //using (MySql.Data.MySqlClient.MySqlCommand cmd1 = new MySql.Data.MySqlClient.MySqlCommand(StrBinry(b, IP), sqlConnectionT1)) //{ // if (sqlConnectionT1.State == System.Data.ConnectionState.Closed) // { sqlConnectionT1.Open(); } // cmd1.ExecuteNonQuery(); //} } else { Im[co] = b.ID.ToString(); co = co + 1; CO = CO + 1; File.WriteAllBytes(@"F:\\archivACT\\Photo\\" + DateTime.Now.ToString("dd_MM_yyyy") + @"\\" + IP.ToString() + "_" + b.CreatedBy.PlatformId.ToString() + "_" + b.Created.ToString("yyyyMMdd_HHmmss") + "_" + co.ToString() + ".avi", b.Data); using (MySql.Data.MySqlClient.MySqlCommand cmd1 = new MySql.Data.MySqlClient.MySqlCommand(StrBinryN(b, IP, @"F:\\archivACT\\Photo\\" + DateTime.Now.ToString("dd_MM_yyyy") + @"\\" + IP.ToString() + "_" + b.CreatedBy.PlatformId.ToString() + "_" + b.Created.ToString("yyyyMMdd_HHmmss") + "_" + CO.ToString() + ".avi"), sqlConnectionT1)) { if (sqlConnectionT1.State == System.Data.ConnectionState.Closed) { sqlConnectionT1.Open(); } cmd1.ExecuteNonQuery(); } //using (MySql.Data.MySqlClient.MySqlCommand cmd1 = new MySql.Data.MySqlClient.MySqlCommand(StrBinry(b, IP), sqlConnectionT1)) //{ // if (sqlConnectionT1.State == System.Data.ConnectionState.Closed) // { sqlConnectionT1.Open(); } // cmd1.ExecuteNonQuery(); //} } } using (MySql.Data.MySqlClient.MySqlCommand cmd1 = new MySql.Data.MySqlClient.MySqlCommand(StrPROEZD(inputModel, IP), sqlConnectionT1)) { if (sqlConnectionT1.State == System.Data.ConnectionState.Closed) { sqlConnectionT1.Open(); } cmd1.ExecuteNonQuery(); } //////////////////////////////////////////////////////////////////////////////////////////// transaction.Commit(); sqlConnectionT1.Close(); } } return(true); }
protected void MakeDutyRoster(object sender, EventArgs e) { Response.Write("<script>alert('Hello, SK')</script>"); string Date = this.mk_pub_dr_date.Text; string Time = this.mk_pub_dr_time.Text; string Room_no = this.mk_pub_dr_rno.Text; string Exam_Type = this.mk_pub_dr_etype.Value; string Staff_Id, Staff_Name; string s1 = string.Empty; string s2 = string.Empty; string s3 = string.Empty; string s4 = string.Empty; int n = 1; int i = 0; int j = 1; int row = 2; MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connection); string queryStr, qstr1, qstr2, qstr3; MySql.Data.MySqlClient.MySqlCommand cmd, cmd1, cmd2, cmd3; conn.Open(); Response.Write("<script>alert('Hello, Google')</script>"); try { Response.Write("<script>alert('Hello, Saikat')</script>"); //qstr1 = "SELECT Col1, Col2 FROM student.arrangement WHERE Room_no = '" + Room_no + "' AND Date = '" + Date + "' AND Time = '" + Time + "' LIMIT 1;"; qstr1 = "SELECT Col1, Col2 FROM student.arrangement WHERE Room_no = '111' LIMIT 1;"; cmd1 = new MySql.Data.MySqlClient.MySqlCommand(qstr1, conn); MySqlDataReader dept_rd = cmd1.ExecuteReader(); if (dept_rd.Read()) { s1 += dept_rd.GetString("Col1"); s2 += dept_rd.GetString("Col2"); } dept_rd.Close(); Response.Write("<script>alert('" + s1 + "')</script>"); Response.Write("<script>alert('" + s2 + "')</script>"); if (s1.IsNullOrEmpty() || s2.IsNullOrEmpty()) { Response.Write("<script>alert('Hello')</script>"); conn.Close(); } else { Response.Write("<script>alert( 'hi')</script>"); s1 = s1.Substring(0, s1.Length - 8); s2 = s2.Substring(0, s2.Length - 8); do { qstr2 = "SELECT * FROM student.staff WHERE Department NOT IN ('" + s1 + "') AND Department NOT IN ('" + s2 + "') LIMIT 0,1;"; cmd2 = new MySql.Data.MySqlClient.MySqlCommand(qstr2, conn); MySqlDataReader get_sid = cmd2.ExecuteReader(); while (get_sid.Read()) { s3 += get_sid.GetString("Staff_ID"); s4 += get_sid.GetString("Name"); } //row = int.Parse(cmd2.ExecuteReader().ToString()); ; if (get_sid.Read()) { get_sid.Close(); qstr3 = "SELECT Staff_Id FROM student.duty_roster WHERE Staff_Id = '" + s3 + "';"; cmd3 = new MySql.Data.MySqlClient.MySqlCommand(qstr3, conn); MySqlDataReader ch_sid = cmd3.ExecuteReader(); if (!ch_sid.Read()) { ch_sid.Close(); conn.Open(); Staff_Id = s3; Staff_Name = s4; queryStr = "INSERT INTO student.duty_roster (Date, Time, Room_no, Exam_Type, Staff_Id, Staff_Name) VALUES (@Date, @Time, @Room_no, @Exam_Type, @Staff_Id, @Staff_Name);"; cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn); cmd.Parameters.AddWithValue("Date", @Date); cmd.Parameters.AddWithValue("Time", @Time); cmd.Parameters.AddWithValue("Room_no", @Room_no); cmd.Parameters.AddWithValue("Exam_Type", @Exam_Type); cmd.Parameters.AddWithValue("Staff_Id", @Staff_Id); cmd.Parameters.AddWithValue("Staff_Name", @Staff_Name); cmd.ExecuteNonQuery(); } else { ch_sid.Close(); Response.Write("<script>alert'Already Exist'</script>"); } } else { get_sid.Close(); Response.Write("<script>alert'Not Inserted'</script>"); } get_sid.Close(); i = i + 1; j = j + 1; }while(n < row); //queryStr = "INSERT INTO student.duty_roster (Date, Time, Room_no, Exam_Type, Staff_Id, Staff_Name) VALUES (@Date, @Time, @Room_no, @Exam_Type, @Staff_Id, @Staff_Name);"; //cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn); //cmd.Parameters.AddWithValue("Date", @Date); //cmd.Parameters.AddWithValue("Time", @Time); //cmd.Parameters.AddWithValue("Room_no", @Room_no); //cmd.Parameters.AddWithValue("Exam_Type", @Exam_Type); //cmd.Parameters.AddWithValue("Staff_Id", @Staff_Id); //cmd.Parameters.AddWithValue("Staff_Name", @Staff_Name); //cmd.ExecuteNonQuery(); conn.Close(); } } catch (Exception e1) { throw e1; } finally { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } }
private void PopulateOptiniosOfProducts() { MySqlConnection con = ServerConnect(); con.Open(); MySqlDataReader reader = null; MySql.Data.MySqlClient.MySqlCommand myCommand; for (int i = 1; i < numberOfLinesInTheFile; i++) { string nameOfVariation = excel_getValue("DM" + i), nameOfOption = "", currentColumn = "DM", maxColumn = textBox1.Text; int option_ID = 0; int[] valuerInAsciiOfColumns = new int[2]; int[] maxValuerInAsciiOfColumns = new int[2]; string currentValuerOfColumInString = "", subOption = ""; int i1 = 0; foreach (char c in maxColumn) { maxValuerInAsciiOfColumns[i1] = System.Convert.ToInt32(c); i1++; } if ((nameOfVariation == "cor") || (nameOfVariation == "Cor") || (nameOfVariation == "Cores")) { nameOfOption = "Color"; } myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT option_id FROM oc_option_description WHERE name ='" + nameOfOption + "'", con); reader = myCommand.ExecuteReader(); while (reader.Read()) { option_ID = int.Parse(reader[0].ToString()); } reader.Close(); int i2 = 0; foreach (char c in currentColumn) { valuerInAsciiOfColumns[i2] = System.Convert.ToInt32(c); i2++; } while (currentValuerOfColumInString != maxColumn) { if (valuerInAsciiOfColumns[1] < 90) { valuerInAsciiOfColumns[1]++; } else { valuerInAsciiOfColumns[0]++; valuerInAsciiOfColumns[0] = 65; } currentValuerOfColumInString = char.ConvertFromUtf32(valuerInAsciiOfColumns[0]) + char.ConvertFromUtf32(valuerInAsciiOfColumns[1]); subOption = excel_getValue(currentValuerOfColumInString + i); if (subOption != "") { currentValuerOfColumInString = maxColumn; } } int currentOptionValue_ID = 0; myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT MAX(option_value_id) FROM oc_option_value", con); reader = myCommand.ExecuteReader(); while (reader.Read()) { currentOptionValue_ID = int.Parse(reader[0].ToString()); } reader.Close(); currentOptionValue_ID++; myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_option_value VALUES ('" + currentOptionValue_ID + "','" + option_ID + "', '', '0')", con); myCommand.ExecuteNonQuery(); string subOptionAlreadyInDatabase = ""; myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT name FROM oc_option_value_description WHERE name ='" + subOption + "'", con); reader = myCommand.ExecuteReader(); while (reader.Read()) { subOptionAlreadyInDatabase = reader[0].ToString(); } reader.Close(); if (subOptionAlreadyInDatabase == "") { myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_option_value_description VALUES ('" + currentOptionValue_ID + "','1', '" + option_ID + "', '" + subOption + "')", con); myCommand.ExecuteNonQuery(); } } }
public string getText(string langName, string pageName, string hebText, int pagestatus, int textType) { if (textValue != null) { } else { MySql.Data.MySqlClient.MySqlDataReader MyReader = null; string ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString; using (MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(ConnStr)) { con.Open(); // check if page name string sql = String.Format("Select PageID From pages2 where pagename='{0}'", pageName); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, con); MyReader = cmd.ExecuteReader(); if (MyReader.Read()) { pageName = MyReader["PageID"].ToString(); MyReader.Close(); } else { MyReader.Close(); sql = String.Format("insert into pages2 (pagename) Values ('{0}')", pageName); cmd.CommandText = sql; cmd.ExecuteNonQuery(); sql = "select last_insert_id() as myid"; cmd.CommandText = sql; MyReader = cmd.ExecuteReader(); if (MyReader.Read()) { pageName = MyReader["myid"].ToString(); } MyReader.Close(); } //looking for the translation in dictionary sql = String.Format("SELECT `{0}` As EngText FROM langtext2 WHERE `TextName`='{1}' AND `PageID`={2}", langName, textName.Replace("'", "''"), pageName);// "SELECT `" + langName + "`, `1` As EngText FROM langtext2 WHERE `TextName`='" + textName.Replace("'", "''") + "' AND `PageID`=" + pageName; cmd.CommandText = sql; MyReader = cmd.ExecuteReader(); if (MyReader.Read()) { //if (MyReader[langName].ToString() != "") //{ // textValue = MyReader[langName].ToString(); //} //else //{ textValue = MyReader["EngText"].ToString(); //} } else //if the text is not in the database - adds it. { if (textName != "") { using (MySql.Data.MySqlClient.MySqlConnection con2 = new MySql.Data.MySqlClient.MySqlConnection(ConnStr)) { con2.Open(); MySqlCommand cmd2 = new MySqlCommand(); cmd2.Connection = con2; if (hebText != "") { if (langName.ToLower() != "1") { cmd2.CommandText = "INSERT INTO langtext2 (TextName,PageID, `" + langName + "`,`1`,`TextType`,`PageStatus`) VALUES ('" + textName.Replace("'", "''") + "'," + pageName + ",'" + hebText.Replace("'", "''") + "','" + textName.Replace("'", "''") + "'," + textType + "," + pagestatus + ")"; } else { cmd2.CommandText = "INSERT INTO langtext2 (TextName,PageID, `" + langName + "`,`TextType`,`PageStatus`) VALUES ('" + textName.Replace("'", "''") + "','" + pageName + "','" + hebText.Replace("'", "''") + "'," + textType + "," + pagestatus + ")"; } textValue = hebText; } else { if (langName.ToLower() != "1") { cmd2.CommandText = "INSERT INTO langtext2 (TextName,PageID, `" + langName + "`,`1`,`TextType`,`PageStatus`) VALUES ('" + textName.Replace("'", "''") + "'," + pageName + ",'####" + textName.Replace("'", "''") + "####','" + textName.Replace("'", "''") + "'," + textType + "," + pagestatus + ")"; } else { cmd2.CommandText = "INSERT INTO langtext2 (TextName,PageID, `" + langName + "`,`TextType`,`PageStatus`) VALUES ('" + textName.Replace("'", "''") + "'," + pageName + ",'####" + textName.Replace("'", "''") + "####'," + textType + "," + pagestatus + ")"; } textValue = "####" + textName + "####"; } cmd2.ExecuteNonQuery(); con2.Close(); } } } MyReader.Close(); cmd.CommandText = "UPDATE langtext2 SET lastUsed='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE `TextName`='" + textName.Replace("'", "''") + "' AND `PageID`=" + pageName;; cmd.ExecuteNonQuery(); con.Close(); } } return(textValue); }
private bool hasRelateProductAndCategory(string currentProductRelated, string currentCategoryName, MySqlConnection con) { MySqlDataReader reader = null; MySql.Data.MySqlClient.MySqlCommand myCommand; int product_IDToGiveACategory = 0; int category_ID = 0; try { if (!(con.State == ConnectionState.Open)) { con.Open(); } myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT product_id FROM oc_product WHERE model='" + currentProductRelated + "'", con); reader = myCommand.ExecuteReader(); while (reader.Read()) { int.TryParse(reader[0].ToString(), out product_IDToGiveACategory); } reader.Close(); myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT category_id FROM oc_category_description WHERE name='" + currentCategoryName + "'", con); reader = myCommand.ExecuteReader(); while (reader.Read()) { int.TryParse(reader[0].ToString(), out category_ID); } reader.Close(); if (category_ID == 0 || product_IDToGiveACategory == 0) { return(false); } string relationAlreadyExist = ""; myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT product_id FROM oc_product_to_category WHERE product_id ='" + product_IDToGiveACategory + "' AND category_id = '" + category_ID + "'", con); reader = myCommand.ExecuteReader(); while (reader.Read()) { relationAlreadyExist = reader[0].ToString(); } reader.Close(); if (relationAlreadyExist == "") { myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_product_to_category VALUES ('" + product_IDToGiveACategory + "','" + category_ID + "')", con); myCommand.ExecuteNonQuery(); } return(true); } catch (Exception e) { MessageBox.Show(e.Message); return(false); } finally { con.Close(); } }
public Form3() { InitializeComponent(); //mysql tables string erzeugen string createTableQuery = string.Format(@"CREATE TABLE IF NOT EXISTS `{0}` ( `id` int(5) unsigned NOT NULL AUTO_INCREMENT, `Wettbewerb` VARCHAR(50) NOT NULL, `Spieler` VARCHAR(50) NOT NULL, `Position` VARCHAR(50) NOT NULL, `home` VARCHAR(50) NOT NULL, `away` VARCHAR(50) NOT NULL, `Form` VARCHAR(50) NOT NULL, `Punkte` VARCHAR(50) NOT NULL, `Preis` VARCHAR(50) NOT NULL, `Spielerid` smallint(5) unsigned NOT NULL DEFAULT '0', `Datum` VARCHAR(50) NOT NULL, `TurnierID` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), KEY `Spielerid` (`Spielerid`)) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;", "FanteamPreise"); //mysql connection erzeugen MySqlConnection connection = new MySqlConnection("server=buqhvbltjab1w9h2bks9-mysql.services.clever-cloud.com;Port=20444;database=buqhvbltjab1w9h2bks9;uid=uxayl6sbtpqdhepa;password=QZPnMNX6OIJrkYTkes3F"); connection.Open(); //mysql DB erzeugen var cmd = new MySql.Data.MySqlClient.MySqlCommand(createTableQuery, connection); cmd.ExecuteNonQuery(); connection.Close(); //db auslesen //liste Wettbewerb erzeugen List <string> dbListe = new List <string>(); MySqlCommand command = connection.CreateCommand(); command.CommandText = "SELECT Wettbewerb FROM FanteamPreise"; MySqlDataReader Reader; connection.Open(); Reader = command.ExecuteReader(); while (Reader.Read()) { string row = ""; for (int i = 0; i < Reader.FieldCount; i++) { row += Reader.GetValue(i).ToString(); dbListe.Add(row); } } connection.Close(); dbListe = dbListe.Distinct().ToList(); for (int i = 0; i < dbListe.Count; i++) { Wettbewerb_combobox.Items.Add(dbListe[i]); } //db auslesen //liste Turnierform erzeugen }
private void button1_Click_1(object sender, EventArgs e) { string categoryName = "", categoryNamePT = "", categoryAlreadyInDatabase = "", parentName = "", metaTitle = "", metaTitlePT, metaKeyword = "", metaKeywordPT = ""; long currentCategory_ID = 0; int parent_ID = 0, categoryAddToDatabase = 0; if (openFileDialog1.ShowDialog() == DialogResult.OK) { System.IO.StreamReader sr = new System.IO.StreamReader(openFileDialog1.FileName); GetTheNumberOfLinesInTheFile(openFileDialog1, "C"); excel_init(openFileDialog1.InitialDirectory + openFileDialog1.FileName); MySqlConnection con = ServerConnect(); string date_addedAndModified = System.DateTime.Now.ToString("yyyy.MM.dd"); try { con.Open(); for (int i = 2; i < numberOfLinesInTheFile; i++) { if (excel_getValue("A" + i) == "Category") { string tempCategoryFullName = excel_getValue("B" + i).Replace("/Malas_em_Cortica", ""); //cork fix string[] categoryPath = tempCategoryFullName.Split('/'); bool isParent = false; int maxCategory_ID = 0, top = 0; categoryName = excel_getValue("G" + i); if (categoryName == "") { categoryName = excel_getValue("C" + i); } categoryNamePT = excel_getValue("P" + i); metaTitle = categoryName; metaTitlePT = categoryNamePT; metaKeyword = excel_getValue("BO" + i); metaKeyword = metaKeyword.Replace(" ", ", "); metaKeywordPT = excel_getValue("BX" + i); try { parentName = categoryPath[(categoryPath.Length - 2)].ToString(); } catch { parentName = categoryPath[(categoryPath.Length - 1)].ToString(); } parentName = parentName.Replace("\"", " "); MySqlDataReader reader = null; MySql.Data.MySqlClient.MySqlCommand myCommand; if (categoryName.Contains("\"")) { MessageBox.Show("ERROR"); return; } myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT category_id FROM oc_category_description WHERE name='" + parentName + "'", con); reader = myCommand.ExecuteReader(); string stringRead = ""; while (reader.Read()) { stringRead = reader[0].ToString(); } reader.Close(); if (stringRead != "") { parent_ID = int.Parse(stringRead); top = 1; } else { top = 0; parent_ID = 0; } MySqlCommand comm = con.CreateCommand(); comm.CommandText = "SELECT category_id FROM oc_category_description WHERE name=?categoryName"; comm.Parameters.Add("?categoryName", categoryName); reader = comm.ExecuteReader(); while (reader.Read()) { categoryAlreadyInDatabase = reader[0].ToString(); } reader.Close(); myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT category_id FROM oc_category WHERE category_id='" + categoryAlreadyInDatabase + "'AND parent_id ='" + parent_ID + "'", con); reader = myCommand.ExecuteReader(); while (reader.Read()) { categoryAlreadyInDatabase = reader[0].ToString(); } reader.Close(); if (categoryAlreadyInDatabase != "") { continue; } myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_category (`image`, `parent_id`, `top`, `column`, `sort_order`, `status`, `date_added`, `date_modified`) VALUES ('','" + parent_ID + "','" + top + "','0','0','1','" + date_addedAndModified + "','" + date_addedAndModified + "')", con); myCommand.ExecuteNonQuery(); currentCategory_ID = myCommand.LastInsertedId; myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_category_description (category_id, language_id, name, meta_title, meta_keyword) VALUES ('" + currentCategory_ID + "', '1', '" + categoryName + "', '" + metaTitle + "', '" + metaKeyword + "')", con); myCommand.ExecuteNonQuery(); myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_category_description (category_id, language_id, name, meta_title, meta_keyword) VALUES ('" + currentCategory_ID + "', '2', '" + categoryNamePT + "', '" + metaKeywordPT + "', '" + metaKeywordPT + "')", con); myCommand.ExecuteNonQuery(); myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_category_to_store VALUES ('" + currentCategory_ID + "','0')", con); myCommand.ExecuteNonQuery(); myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_category_path VALUES ('" + currentCategory_ID + "','" + currentCategory_ID + "', '0')", con); myCommand.ExecuteNonQuery(); categoryAddToDatabase++; reader.Close(); } categoryName = ""; categoryNamePT = ""; categoryAlreadyInDatabase = ""; parentName = ""; metaTitle = ""; metaTitlePT = ""; metaKeyword = ""; metaKeywordPT = ""; currentCategory_ID = 0; parent_ID = 0; categoryAddToDatabase = 0; } con.Close(); MessageBox.Show("Has been export - " + categoryAddToDatabase); } catch (MySqlException ex) { MessageBox.Show(ex.Message); } finally { sr.Close(); excel_close(); } } }
protected void scheduleTransaction(object sender, EventArgs e) { BillPayEntry bp = new BillPayEntry(); bp.PaymentTransactionDate = date_text_box.Text; bp.RecipientBusinessName = bussiness_name.Text; bp.RecipientBusinessAddress = address_bussiness.Text; bp.AmountPaid = payment_amount.Text; bp.PaymentDetails = amount_description.Text; bp.Status = 0; UserInfo temp = (UserInfo)Session["currentUser"]; bp.Emailaddress = temp.EmailAddress; if (temp.MyBillPayments == null) { List <BillPayEntry> newpay = new List <BillPayEntry>(); newpay.Add(bp); temp.MyBillPayments = newpay; } else { temp.MyBillPayments.Add(bp); } string stat = "null"; if (bp.Status == 0) { stat = "In Progress"; } TableRow row = new TableRow { Cells = { new TableCell { Text = bp.PaymentTransactionDate }, new TableCell { Text = bp.RecipientBusinessName }, new TableCell { Text = bp.RecipientBusinessAddress }, new TableCell { Text = bp.AmountPaid }, new TableCell { Text = stat } } }; transaction_details.Rows.AddAt(2, row); string msgTo = bp.Emailaddress; string msgSubject = "New Transaction Scheduling Notification"; string msgBody = "Dear User " + msgTo + ",<br /><br />" + "You have scheduled a transaction in Rapid Bill Pay on" + bp.PaymentTransactionDate + " <br/> <br />" + "You can check the status of the transaction by visiting <a href='http://dcm.uhcl.edu/c432017fa01tirunagarus/transactionDetails.aspx'> Compose a trasaction</a> " + "<br /><br />" + "Thank you again for using <a href='http://dcm.uhcl.edu/c432017fa01tirunagarus/'>Rapid Bill Pay</a> " + "<br /><br />" + "With Best Wishes, <br />" + "Sumanjali Tirunagaru"; MailMessage mailObj = new MailMessage(); mailObj.Body = msgBody; mailObj.From = new MailAddress("*****@*****.**", "Admin Team"); mailObj.To.Add(new MailAddress(msgTo)); mailObj.Subject = msgSubject; mailObj.IsBodyHtml = true; SmtpClient smtpClient = new System.Net.Mail.SmtpClient("smtp.gmail.com", 587); smtpClient.UseDefaultCredentials = false; smtpClient.Credentials = new System.Net.NetworkCredential("*****@*****.**", "!hahahaha"); smtpClient.EnableSsl = true; try { smtpClient.Send(mailObj); } catch (Exception ex) { } //db updating MySql.Data.MySqlClient.MySqlConnection co; string connection = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString(); co = new MySql.Data.MySqlClient.MySqlConnection(connection); co.Open(); string query = "INSERT INTO `c432017fa01tirunagarus`.`tirunagarus_WADfl17_RapidBillPay`(`emailAddress`, `paymentTransactionDate`, `recipientBusinessName`, `recipientBusinessAddress`, `amountPaid`, `paymentDetails`, `status`) VALUES ('" + bp.Emailaddress + "','" + bp.PaymentTransactionDate + "','" + bp.RecipientBusinessName + "','" + bp.RecipientBusinessAddress + "','" + bp.AmountPaid + "','" + bp.PaymentDetails + "'," + bp.Status + ") "; MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(query, co); command.ExecuteNonQuery(); co.Close(); Response.Redirect("~/transactionDetails.aspx"); }
private void button1_Click(object sender, EventArgs e) { System.IO.StreamReader sr; string productName, productName_PT, productDescription, productDescription_PT, productMetaDescription, productMetaDescription_PT, meta_keyword, meta_keyword_PT; int productAddToDatabase = 0, productEnabled = 1; string model; if (openFileDialog1.ShowDialog() == DialogResult.OK) { try { sr = new System.IO.StreamReader(openFileDialog1.FileName); } catch (Exception ex) { MessageBox.Show("Error - " + ex.Message); return; } if (nupLinesImport.Value == 0) { GetTheNumberOfLinesInTheFile(openFileDialog1, "N"); } else { numberOfLinesInTheFile = (int)nupLinesImport.Value; } excel_init(openFileDialog1.InitialDirectory + openFileDialog1.FileName); MessageBox.Show("File open"); // PopulateOptiniosOfProducts(); try { PopulateManufacturer(); MySqlConnection con = ServerConnect(); con.Open(); for (int i = 2; i < numberOfLinesInTheFile; i++) { model = excel_getValue("N" + i); MySql.Data.MySqlClient.MySqlCommand myCommand; //MySqlDataReader reader = myCommand.ExecuteReader(); MySqlDataReader reader; int max_Product_id = 0; /* * while (reader.Read()) * { * max_Product_id = Int32.Parse(reader[0].ToString()); * * } * reader.Close(); */ int isVisible, weight_Class, quantity, stock_status_id, shipping, points, length, width, height, viewed, lenght_class_id; string imageUrl, date_available, date_added, date_modified; string tempStringIsvisible = excel_getValue("P" + i); isVisible = Int16.Parse(tempStringIsvisible); if (excel_getValue("AK" + i) == "gram") { weight_Class = 2; } else { weight_Class = 1; } string tempStringWeight = excel_getValue("AL" + i); if (tempStringWeight != "") { weight = tempStringWeight; } currentProduct_id = max_Product_id + 1; quantity = 1; stock_status_id = 7; string tempStrigImage = excel_getValue("EH" + i); if (tempStrigImage != "") { string imageName = tempStrigImage; imageUrl = "data/products/" + imageName; if (!GetimageOfProduct(imageName)) { imageUrl = ""; productEnabled = 0; } } else { imageUrl = ""; productEnabled = 0; } string tempStringManufacter = excel_getValue("AE" + i); if (tempStringManufacter != "") { MySql.Data.MySqlClient.MySqlCommand manufacturerQuery = new MySql.Data.MySqlClient.MySqlCommand ("SELECT manufacturer_id FROM oc_manufacturer WHERE name = '" + tempStringManufacter + "'", con); MySqlDataReader manufacturerReader = manufacturerQuery.ExecuteReader(); while (manufacturerReader.Read()) { manufacturer_id = Int32.Parse(manufacturerReader[0].ToString()); } manufacturerReader.Close(); } else { manufacturer_id = 0; } shipping = 1; string priceString = excel_getValue("Q" + i); if (priceString != "") { if (priceString != "0") { price = priceString.Replace(',', '.'); } else { price = "0"; } } string excelProductTax = excel_getValue("W" + i); if (excelProductTax == "normal") { tax_class_id = basicTaxID; } else if (excelProductTax == "reduced") { tax_class_id = reduceTaxID; } else { MessageBox.Show("Problem with product taxes"); } date_available = System.DateTime.Now.ToString("yyyy.MM.dd"); length = 0; height = 0; width = 0; lenght_class_id = 1; date_added = date_available; date_modified = date_available; viewed = 0; /* myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT model FROM oc_product WHERE model ='" + model + "'", con); * reader = myCommand.ExecuteReader(); * model_AlreadyInDatabase = ""; * while (reader.Read()) * { * model_AlreadyInDatabase = reader[0].ToString(); * } * reader.Close(); * if (model_AlreadyInDatabase == "") * { */ productAddToDatabase++; myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_product (model, quantity, stock_status_id, image, manufacturer_id, shipping, price, tax_class_id," + "date_available, weight, weight_class_id, length, width, height, length_class_id, subtract, minimum, sort_order, status, viewed, date_added, date_modified) VALUES ('" + model + "','" + quantity + "', '" + stock_status_id + "', '" + imageUrl + "', '" + manufacturer_id + "', '" + shipping + "', '" + price + "', '" + tax_class_id + "', '" + date_available + "', '" + weight + "','" + weight_class_id + "', '" + length + "', '" + width + "', '" + height + "', '" + lenght_class_id + "', '0', '1', '0', '" + productEnabled + "', '" + viewed + "','" + date_added + "', '" + date_modified + "')", con); myCommand.ExecuteNonQuery(); currentProduct_id = myCommand.LastInsertedId; //} //finish oc_products string listOfImages = excel_getValue("DW" + i); string[] imagesInDetailName = listOfImages.Split(';'); for (int i2 = 0; i2 < imagesInDetailName.Length; i2++) { if (imagesInDetailName[i2] != "") { if (!GetimageOfProduct(imagesInDetailName[i2])) { continue; } imagesInDetailName[i2] = "data/products/" + imagesInDetailName[i2]; myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT MAX(product_image_id) FROM oc_product_image", con); reader = myCommand.ExecuteReader(); string tempImage_ID = ""; while (reader.Read()) { tempImage_ID = reader[0].ToString(); } if (tempImage_ID == "") { maxImage_id = 0; } else { maxImage_id = int.Parse(tempImage_ID); } reader.Close(); currentImage_id = maxImage_id + 1; /* myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT product_id FROM oc_product_image WHERE image='" + imagesInDetailName[i2] + "' AND product_id ='" + currentProduct_id + "' ", con); + + reader = myCommand.ExecuteReader(); + imageAlreadyInDatabase = ""; + while (reader.Read()) + { + imageAlreadyInDatabase = reader[0].ToString(); + } + reader.Close(); + + + if (imageAlreadyInDatabase == "") + { */ myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_product_image VALUES ('" + currentImage_id + "','" + currentProduct_id + "','" + imagesInDetailName[i2] + "', '') ", con); myCommand.ExecuteNonQuery(); // } } } //finish oc_product_image productName = excel_getValue("AQ" + i); productName_PT = excel_getValue("AZ" + i); productDescription = excel_getValue("CE" + i); productDescription_PT = excel_getValue("CN" + i); productMetaDescription = productName; productMetaDescription_PT = productName_PT; meta_keyword = excel_getValue("CO" + i); meta_keyword_PT = excel_getValue("CX" + i); if (productName == "") { productName = "Missing name "; } if (productDescription == "") { productDescription = "Description missing"; } productName = productName.Replace("'", ""); productDescription = productDescription.Replace("'", ""); productMetaDescription = productMetaDescription.Replace("'", ""); /* * * myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT product_id FROM oc_product_description WHERE description ='" + productDescription + "' AND name='" + productName + "'", con); + reader = myCommand.ExecuteReader(); + databaseProductDescriptionEqual = ""; + while (reader.Read()) + { + databaseProductDescriptionEqual = reader[0].ToString(); + } + reader.Close(); + if (databaseProductDescriptionEqual == "") + { * */ productName = productName.Replace("'", "''"); productDescription = productDescription.Replace("'", "''"); productMetaDescription = productMetaDescription.Replace("'", "''"); meta_keyword = meta_keyword.Replace("'", "''"); /* productName = productName.Replace("'s", "''s"); * productDescription = productDescription.Replace("'s", "''s"); * productMetaDescription = productMetaDescription.Replace("'s", "''s"); * meta_keyword = meta_keyword.Replace("'s", "''s"); */ myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_product_description VALUES ('" + currentProduct_id + "', '1', '" + productName + "', '" + productDescription + "', '', '" + productMetaDescription + "', '', '" + meta_keyword + "')", con); myCommand.ExecuteNonQuery(); productName_PT = productName_PT.Replace("'", "''"); productDescription_PT = productDescription_PT.Replace("'", "''"); productMetaDescription_PT = productMetaDescription_PT.Replace("'", "''"); meta_keyword_PT = meta_keyword_PT.Replace("'", "''"); myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_product_description VALUES ('" + currentProduct_id + "', '2', '" + productName_PT + "', '" + productDescription_PT + "', '', '" + productMetaDescription_PT + "', '', '" + meta_keyword_PT + "')", con); myCommand.ExecuteNonQuery(); // } //finish oc_product_description /* myCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT product_id FROM oc_product_to_store WHERE product_id='" + currentProduct_id + "' ", con); + reader = myCommand.ExecuteReader(); + + + product_id_AlreadyInDatabase = ""; + while (reader.Read()) + { + product_id_AlreadyInDatabase = reader[0].ToString(); + } + reader.Close(); + + if (product_id_AlreadyInDatabase == "") + { */ myCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO oc_product_to_store VALUES ( '" + currentProduct_id + "','0') ", con); myCommand.ExecuteNonQuery(); // } //finish oc_product_to_store maxManufacturer_ID = 0; manufacturer_id = 0; weight_class_id = 0; tax_class_id = 0; weight = "0"; price = "0"; imageAlreadyInDatabase = ""; productName = ""; productDescription = ""; databaseProductDescriptionEqual = ""; product_id_AlreadyInDatabase = ""; model_AlreadyInDatabase = ""; maxImage_id = 0; currentProduct_id = 0; currentImage_id = 0; productEnabled = 1; } con.Close(); MessageBox.Show("Finish - Products Add -" + productAddToDatabase); sr.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } }