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