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);
}
}
}
