public static void LoadContracts()
{
xlInterface.init("contract.xls");
string cName = "";
string table = "Sheet1$";
string qry = "SELECT * FROM " + "[" + table + "];";
DataTable dt = xlInterface.DoQuery(qry);
foreach (DataColumn c in dt.Columns)
{
cName = c.ColumnName;
}
string sString;
xlList = new List <xlData>();
int rows = 2;
sString = "select ";
for (int i = 0; i < rows - 1; i++)
{
if (i > 0)
{
sString += ", ";
}
xlData id = new xlData();
id.ID = cName;
xlList.Add(id);
sString += cName;
sString += " ";
}
sString += " from [Sheet1$];";
dt = xlInterface.DoQuery(sString);
int lSize = dt.Rows.Count;
Globals.CONTRACT_ARRAY = new string[lSize];
string pid = "";
int pi = 0;
foreach (DataRow drow in dt.Rows)
{
for (int j = 0; j < xlList.Count; j++)
{
pid = drow[xlList[j].ID].ToString();
Globals.CONTRACT_ARRAY[pi] = pid;
pi++;
}
}
}
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();
}
}
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();
}
}
public static void LoadPlayers()
{
xlInterface.init("player.xls");
string[] cName;
string table = "Sheet1$";
string qry = "SELECT * FROM " + "[" + table + "];";
DataTable dt = xlInterface.DoQuery(qry);
cName = new string[dt.Columns.Count];
int i = 0;
foreach (DataColumn c in dt.Columns)
{
cName[i++] = c.ColumnName;
}
string sString;
xlList = new List <xlData>();
int rows = cName.Length + 1;
sString = "select ";
for (i = 0; i < rows - 1; i++)
{
if (i > 0)
{
sString += ", ";
}
xlData id = new xlData();
id.ID = cName[i];
xlList.Add(id);
sString += cName[i];
sString += " ";
}
sString += " from [Sheet1$];";
//dt = xlInterface.DoQuery(sString);
int lSize = dt.Rows.Count;
Globals.PLAYER_ARRAY = new string[lSize, 9];
string pid = "";
int pi = 0;
foreach (DataRow drow in dt.Rows)
{
for (int j = 0; j < xlList.Count; j++)
{
pid = drow[xlList[j].ID].ToString();
if (j == 3)
{
int age;
pid = drow[xlList[j].ID].ToString().Substring(8);
age = (100 - Convert.ToInt32(pid)) + 14;
pid = age.ToString();
}
Globals.PLAYER_ARRAY[pi, j] = pid;
}
pi++;
}
}
public static void LoadSportsnet()
{
xlInterface.init("sportsnet.xls");
string[] cName;
string table = "Sheet1$";
string qry = "SELECT * FROM " + "[" + table + "];";
DataTable dt = xlInterface.DoQuery(qry);
cName = new string[dt.Columns.Count];
int i = 0;
foreach (DataColumn c in dt.Columns)
{
cName[i++] = c.ColumnName;
}
string sString;
xlList = new List <xlData>();
int rows = cName.Length + 1;
sString = "select ";
for (i = 0; i < rows - 1; i++)
{
if (i > 0)
{
sString += ", ";
}
xlData id = new xlData();
id.ID = cName[i];
xlList.Add(id);
sString += cName[i];
sString += " ";
}
sString += " from [Sheet1$];";
dt = xlInterface.DoQuery(sString);
int lSize = dt.Rows.Count;
Globals.SPORTSNET_ARRAY = new string[lSize, 2];
string pid = "";
int pi = 0;
foreach (DataRow drow in dt.Rows)
{
for (int j = 0; j < xlList.Count; j++)
{
pid = drow[xlList[j].ID].ToString();
Globals.SPORTSNET_ARRAY[pi, j] = pid;
}
pi++;
}
xlInterface.init("injury.xls");
table = "Sheet1$";
qry = "SELECT * FROM " + "[" + table + "];";
dt = xlInterface.DoQuery(qry);
cName = new string[dt.Columns.Count];
i = 0;
foreach (DataColumn c in dt.Columns)
{
cName[i++] = c.ColumnName;
}
xlList = new List <xlData>();
rows = cName.Length + 1;
sString = "select ";
for (i = 0; i < rows - 1; i++)
{
if (i > 0)
{
sString += ", ";
}
xlData id = new xlData();
id.ID = cName[i];
xlList.Add(id);
sString += cName[i];
sString += " ";
}
sString += " from [Sheet1$];";
dt = xlInterface.DoQuery(sString);
lSize = dt.Rows.Count;
Globals.INJURY_ARRAY = new string[lSize, 2];
pid = "";
pi = 0;
foreach (DataRow drow in dt.Rows)
{
for (int j = 0; j < xlList.Count; j++)
{
pid = drow[xlList[j].ID].ToString();
Globals.INJURY_ARRAY[pi, j] = pid;
}
pi++;
}
}
public static void LoadSportsnet()
{
xlInterface.init("sportsnet.xls");
string[] cName;
string table = "Sheet1$";
string qry = "SELECT * FROM " + "[" + table + "];";
DataTable dt = xlInterface.DoQuery(qry);
cName = new string[dt.Columns.Count];
int i = 0;
foreach (DataColumn c in dt.Columns)
{
cName[i++] = c.ColumnName;
}
string sString;
xlList = new List<xlData>();
int rows = cName.Length + 1;
sString = "select ";
for (i = 0; i < rows - 1; i++)
{
if (i > 0)
sString += ", ";
xlData id = new xlData();
id.ID = cName[i];
xlList.Add(id);
sString += cName[i];
sString += " ";
}
sString += " from [Sheet1$];";
dt = xlInterface.DoQuery(sString);
int lSize = dt.Rows.Count;
Globals.SPORTSNET_ARRAY = new string[lSize, 2];
string pid = "";
int pi = 0;
foreach (DataRow drow in dt.Rows)
{
for (int j = 0; j < xlList.Count; j++)
{
pid = drow[xlList[j].ID].ToString();
Globals.SPORTSNET_ARRAY[pi, j] = pid;
}
pi++;
}
xlInterface.init("injury.xls");
table = "Sheet1$";
qry = "SELECT * FROM " + "[" + table + "];";
dt = xlInterface.DoQuery(qry);
cName = new string[dt.Columns.Count];
i = 0;
foreach (DataColumn c in dt.Columns)
{
cName[i++] = c.ColumnName;
}
xlList = new List<xlData>();
rows = cName.Length + 1;
sString = "select ";
for (i = 0; i < rows - 1; i++)
{
if (i > 0)
sString += ", ";
xlData id = new xlData();
id.ID = cName[i];
xlList.Add(id);
sString += cName[i];
sString += " ";
}
sString += " from [Sheet1$];";
dt = xlInterface.DoQuery(sString);
lSize = dt.Rows.Count;
Globals.INJURY_ARRAY = new string[lSize, 2];
pid = "";
pi = 0;
foreach (DataRow drow in dt.Rows)
{
for (int j = 0; j < xlList.Count; j++)
{
pid = drow[xlList[j].ID].ToString();
Globals.INJURY_ARRAY[pi, j] = pid;
}
pi++;
}
}
public static void LoadPlayers()
{
xlInterface.init("player.xls");
string[] cName;
string table = "Sheet1$";
string qry = "SELECT * FROM " + "[" + table + "];";
DataTable dt = xlInterface.DoQuery(qry);
cName = new string[dt.Columns.Count];
int i = 0;
foreach (DataColumn c in dt.Columns)
{
cName[i++] = c.ColumnName;
}
string sString;
xlList = new List<xlData>();
int rows = cName.Length + 1;
sString = "select ";
for (i = 0; i < rows - 1; i++)
{
if (i > 0)
sString += ", ";
xlData id = new xlData();
id.ID = cName[i];
xlList.Add(id);
sString += cName[i];
sString += " ";
}
sString += " from [Sheet1$];";
//dt = xlInterface.DoQuery(sString);
int lSize = dt.Rows.Count;
Globals.PLAYER_ARRAY = new string[lSize, 9];
string pid = "";
int pi = 0;
foreach (DataRow drow in dt.Rows)
{
for (int j = 0; j < xlList.Count; j++)
{
pid = drow[xlList[j].ID].ToString();
if (j == 3)
{
int age;
pid = drow[xlList[j].ID].ToString().Substring(8);
age = (100 - Convert.ToInt32(pid)) + 14;
pid = age.ToString();
}
Globals.PLAYER_ARRAY[pi, j] = pid;
}
pi++;
}
}
public static void LoadContracts()
{
xlInterface.init("contract.xls");
string cName = "";
string table = "Sheet1$";
string qry = "SELECT * FROM " + "[" + table + "];";
DataTable dt = xlInterface.DoQuery(qry);
foreach (DataColumn c in dt.Columns)
{
cName = c.ColumnName;
}
string sString;
xlList = new List<xlData>();
int rows = 2;
sString = "select ";
for (int i = 0; i < rows - 1; i++)
{
if (i > 0)
sString += ", ";
xlData id = new xlData();
id.ID = cName;
xlList.Add(id);
sString += cName;
sString += " ";
}
sString += " from [Sheet1$];";
dt = xlInterface.DoQuery(sString);
int lSize = dt.Rows.Count;
Globals.CONTRACT_ARRAY = new string[lSize];
string pid = "";
int pi = 0;
foreach (DataRow drow in dt.Rows)
{
for (int j = 0; j < xlList.Count; j++)
{
pid = drow[xlList[j].ID].ToString();
Globals.CONTRACT_ARRAY[pi] = pid;
pi++;
}
}
}
void EnterData()
{
cwList = new List<xlData>();
int rows = dgCWTransfer.RowCount;
string sString;
string rName;
sString = "INSERT INTO ";
sString += tbName.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();
}
}
}
void EnterData()
{
cwList = new List <xlData>();
int rows = dgCWTransfer.RowCount;
string sString;
string rName;
sString = "INSERT INTO ";
sString += tbName.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();
}
}
}
