private void btImport_Click(object sender, EventArgs e)
{
xlInterface.init(odExcelFile.FileName);
string sString;
string rName = "part";
xlList = new List<xlData>();
int rows = dgXLTransfer.RowCount;
sString = "select ";
for (int i = 0; i < rows-1; i++)
{
if (i > 0)
sString += ", ";
rName = dgXLTransfer.Rows[i].Cells[0].Value as string;
xlData id = new xlData();
id.ID = rName;
xlList.Add(id);
sString += rName;
sString += " ";
}
sString += " from [Sheet1$];";
DataTable dt = xlInterface.DoQuery(sString);
foreach (DataRow drow in dt.Rows)
{
for (int j = 0; j < xlList.Count; j++)
{
xlList[j].Data = drow[xlList[j].ID].ToString();
}
/*part = drow["id"].ToString();
description = drow["description"].ToString();
qty_on_hand = Convert.ToDouble(drow["qty_on_hand"].ToString());
purchase_price = Convert.ToDouble(drow["purchase_price"].ToString());*/
EnterData();
}
}
void EnterData()
{
cwList = new List<xlData>();
int rows = dgCWTransfer.RowCount;
string sString;
string rName;
sString = "INSERT INTO ";
sString += cbTable.Text + " (";
for (int i = 0; i < rows-1; i++)
{
if (i > 0)
sString += ", ";
rName = dgCWTransfer.Rows[i].Cells[0].Value as string;
xlData id = new xlData();
id.ID = rName;
id.Data = xlList[i].Data;
cwList.Add(id);
sString += rName;
sString += " ";
}
sString += ") VALUES ( ";
for (int i = 0; i < rows-1; i++)
{
if (i > 0)
sString += ", ";
sString += "@";
sString += cwList[i].ID;
sString += " ";
}
sString += " );";
MySqlConnection con = null;
MySqlDataReader reader = null;
try
{
string host = MysqlInterface.host;
string dbase = MysqlInterface.dbase;
string user = MysqlInterface.user;
string password = MysqlInterface.password;
String str = @"server=" + host + ";database=" + dbase + ";userid=" + user + "; password="******";";
con = new MySqlConnection(str);
con.Open(); //open the connection
//This is the mysql command that we will query into the db.
//It uses Prepared statements and the Placeholder is @name.
//Using prepared statements is faster and secure.
//TO INSERT values into the database using prepares statements
//String cmdText = "INSERT INTO part (id, description, qty_on_hand, purchase_price) VALUES(@part,@description,@qty_on_hand, @purchase_price)";
MySqlCommand cmd = new MySqlCommand(sString, con);
cmd.Prepare();
for (int i = 0; i < rows - 1; i++)
{
cmd.Parameters.AddWithValue("@" + cwList[i].ID, cwList[i].Data);
}
/*
cmd.Parameters.AddWithValue("@part", part);
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@qty_on_hand", qty_on_hand);
cmd.Parameters.AddWithValue("@purchase_price", purchase_price);*/
cmd.ExecuteNonQuery(); //execute the mysql command
Console.WriteLine("Inserting data to the database. Done!\n");
}
catch (MySqlException err)
{
Console.WriteLine("Error: " + err.ToString());
}
finally
{
if (con != null)
{
con.Close(); //close the connection
}
if (reader != null)
{
reader.Close();
}
}
}
