public MySqlTableContext( DataTable dataTable, MySqlConnection connection )
{
this.Connection = connection;
this.DataTable = dataTable;
this.DataAdapter = new MySqlDataAdapter(
string.Format( "SELECT * FROM {0} WHERE 1=0",
this.DataTable.TableName ), this.Connection );
this.DataAdapter.UpdateBatchSize = 50;
// Using workaround for MySQL Connector bug described at:
// http://bugs.mysql.com/bug.php?id=39815
// Dispose the builder before setting adapter commands.
MySqlCommandBuilder builder = new MySqlCommandBuilder( this.DataAdapter );
MySqlCommand updateCommand = builder.GetUpdateCommand();
MySqlCommand insertCommand = builder.GetInsertCommand();
MySqlCommand deleteCommand = builder.GetDeleteCommand();
builder.Dispose();
this.DataAdapter.UpdateCommand = updateCommand;
this.DataAdapter.InsertCommand = insertCommand;
this.DataAdapter.DeleteCommand = deleteCommand;
this.DataAdapter.RowUpdating += new MySqlRowUpdatingEventHandler( DataAdapter_RowUpdating );
this.DataAdapter.RowUpdated += this.OnRowUpdated;
// Create a command to fetch the last inserted id
identityCommand = this.Connection.CreateCommand();
identityCommand.CommandText = "SELECT LAST_INSERT_ID()";
this.RefreshIdentitySeed();
}
public void AutoIncrementColumns()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id int(10) unsigned NOT NULL auto_increment primary key)");
execSQL("INSERT INTO Test VALUES(NULL)");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
DataRow row = ds.Tables[0].NewRow();
ds.Tables[0].Rows.Add(row);
try
{
da.Update(ds);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
ds.Clear();
da.Fill(ds);
Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
Assert.AreEqual(2, ds.Tables[0].Rows[1]["id"]);
cb.Dispose();
}
protected virtual void Dispose(bool disposing)
{
if (disposed)
{
return;
}
if (disposing)
{
if (data != null)
{
data.Dispose();
}
if (ds != null)
{
ds.Dispose();
}
if (da != null)
{
da.Dispose();
}
if (cb != null)
{
cb.Dispose();
}
if (com != null)
{
com.Dispose();
}
if (comm != null)
{
comm.Dispose();
}
}
disposed = true;
}
public void UpdateDecimalColumns()
{
execSQL("CREATE TABLE Test (id int not null auto_increment primary key, " +
"dec1 decimal(10,1))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row["id"] = DBNull.Value;
row["dec1"] = 23.4;
dt.Rows.Add(row);
da.Update(dt);
dt.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual(23.4, dt.Rows[0]["dec1"]);
cb.Dispose();
}
public bool UpdateTable(DataTable table, string tableName)
{
try
{
TableHelper.SetDefaultColumnValues(table);
var con = CONNECTION.OpenCon();
var adapter = new MySqlDataAdapter(string.Format(@"SELECT * FROM {0}", tableName), con);
var cmd = new MySqlCommandBuilder(adapter);
adapter.Update(table);
cmd.Dispose();
adapter.Dispose();
CONNECTION.CloseCon(con);
return true;
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable Error!",
Ex = ex,
});
return false;
}
}
public void QuietOpenAndClose()
{
execSQL("CREATE TABLE Test (id INT, PRIMARY KEY(id))");
execSQL("INSERT INTO Test VALUES(1)");
using (MySqlConnection c = new MySqlConnection(GetConnectionString(true)))
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
Assert.IsTrue(c.State == ConnectionState.Closed);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.IsTrue(c.State == ConnectionState.Closed);
Assert.AreEqual(1, dt.Rows.Count);
dt.Rows[0][0] = 2;
DataRow[] rows = new DataRow[1];
rows[0] = dt.Rows[0];
da.Update(dt);
Assert.IsTrue(c.State == ConnectionState.Closed);
dt.Clear();
c.Open();
Assert.IsTrue(c.State == ConnectionState.Open);
da.Fill(dt);
Assert.IsTrue(c.State == ConnectionState.Open);
Assert.AreEqual(1, dt.Rows.Count);
cb.Dispose();
}
}
public void UpdateDataSet()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id INT NOT NULL, blob1 LONGBLOB, text1 LONGTEXT, PRIMARY KEY(id))");
execSQL("INSERT INTO Test VALUES( 1, NULL, 'Text field' )");
try
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
string s = (string)dt.Rows[0][2];
Assert.AreEqual("Text field", s);
byte[] inBuf = Utils.CreateBlob(512);
dt.Rows[0].BeginEdit();
dt.Rows[0]["blob1"] = inBuf;
dt.Rows[0].EndEdit();
DataTable changes = dt.GetChanges();
da.Update(changes);
dt.AcceptChanges();
dt.Clear();
da.Fill(dt);
cb.Dispose();
byte[] outBuf = (byte[])dt.Rows[0]["blob1"];
Assert.AreEqual(inBuf.Length, outBuf.Length,
"checking length of updated buffer");
for (int y = 0; y < inBuf.Length; y++)
Assert.AreEqual(inBuf[y], outBuf[y], "checking array data");
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
}
public void InsertDateTimeValue()
{
using (MySqlConnection c = new MySqlConnection(conn.ConnectionString +
";allow zero datetime=yes"))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, dt FROM Test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("id", typeof(int)));
dt.Columns.Add(new DataColumn("dt", typeof(DateTime)));
da.Fill(dt);
DateTime now = DateTime.Now;
DataRow row = dt.NewRow();
row["id"] = 1;
row["dt"] = now;
dt.Rows.Add(row);
da.Update(dt);
dt.Clear();
da.Fill(dt);
cb.Dispose();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(now.Date, ((DateTime)dt.Rows[0]["dt"]).Date);
}
}
public static void ImportTeams(string file)
{
int teamsImported = 0;
int importErrors = 0;
int teamsToImport = 0;
SetStatusBarMsg("Importing teams info...", "Yellow");
BackgroundWorker worker = new BackgroundWorker();
worker.WorkerReportsProgress = true;
worker.DoWork += delegate(object s, DoWorkEventArgs args)
{
OracleConnection cn = null;
OracleCommand cmd = null;
OracleDataReader rdr = null;
OracleDataAdapter adp = null;
OracleCommandBuilder bldr = null;
MySqlConnection cnMySql = null;
MySqlCommand cmdMySql = null;
MySqlDataReader rdrMySql = null;
MySqlDataAdapter adpMySql = null;
MySqlCommandBuilder bldrMySql = null;
//DataTable tblPlayer = null;
DataTable tbl = null;
DataRow row;
string sql;
//long teamId;
int i;
int totalPicks = 0;
DataSet dsTeams = new DataSet();
dsTeams.ReadXml(file);
try
{
cn = createConnectionSDR();
teamsToImport = dsTeams.Tables["team"].Rows.Count;
foreach (DataRow xmlRow in dsTeams.Tables["team"].Rows)
{
totalPicks = 0;
if (xmlRow["teamid"].ToString().Trim() != "")
{
if (ConfigurationManager.AppSettings["DraftType"].ToUpper() == "NFL")
{
#region Picks
try
{
sql = "select count(*) from draftorder where teamid = " + xmlRow["teamid"];
cmd = new OracleCommand(sql, cn);
adp = new OracleDataAdapter(cmd);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
totalPicks = int.Parse(tbl.Rows[0][0].ToString());
}
}
finally
{
cmd.Dispose();
adp.Dispose();
tbl.Dispose();
}
#endregion
#region 6 Matrix Notes
//import the 4 matrix notes
for (i = 1; i <= 6; i++)
{
if (xmlRow["note" + i.ToString()].ToString().Trim() != "")
{
sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = " + i.ToString() + " and referenceid = " + xmlRow["teamid"];
cmd = new OracleCommand(sql, cn);
adp = new OracleDataAdapter(cmd);
bldr = new OracleCommandBuilder(adp);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count == 0)
{
row = tbl.Rows.Add();
row["referencetype"] = 2;
row["referenceid"] = xmlRow["teamid"];
row["tidbitorder"] = i;
row["enabled"] = 1;
}
else
{
row = tbl.Rows[0];
}
row["text"] = xmlRow["note" + i.ToString()].ToString();
adp.Update(tbl.GetChanges());
tbl.AcceptChanges();
cmd.Dispose();
adp.Dispose();
bldr.Dispose();
tbl.Dispose();
}
}
#endregion
#region MySql team ranks/results
cnMySql = createConnectionMySql();
sql = "select * from teams where id = " + xmlRow["teamid"];
cmdMySql = new MySqlCommand(sql, cnMySql);
adpMySql = new MySqlDataAdapter(cmdMySql);
bldrMySql = new MySqlCommandBuilder(adpMySql);
tbl = new DataTable();
adpMySql.Fill(tbl);
if (tbl.Rows.Count > 0)
{
row = tbl.Rows[0];
row["totalpicks"] = totalPicks;
row["overallrecord"] = xmlRow["record"];
row["divisionresult"] = xmlRow["divresult"];
row["playoffs"] = xmlRow["playoffs"];
row["offrankppg"] = xmlRow["offrankppg"];
row["offrankypg"] = xmlRow["offrankypg"];
row["offrankturns"] = xmlRow["offrankturns"];
row["offrankrush"] = xmlRow["offrankrushyds"];
row["offrankpass"] = xmlRow["offrankpassyds"];
row["defrankppg"] = xmlRow["defrankppg"];
row["defrankypg"] = xmlRow["defrankypg"];
row["defranktakeaways"] = xmlRow["defranktakeaways"];
row["defrankrush"] = xmlRow["defrankrushing"];
row["defrankpass"] = xmlRow["defrankpassing"];
row["teamneeds"] = xmlRow["melsneeds"];
adpMySql.Update(tbl.GetChanges());
tbl.AcceptChanges();
cmdMySql.Dispose();
adpMySql.Dispose();
bldrMySql.Dispose();
tbl.Dispose();
}
#endregion
}
else if (ConfigurationManager.AppSettings["DraftType"].ToUpper() == "NBA")
{
#region 2 Matrix Notes
//import the 4 matrix notes
for (i = 1; i <= 2; i++)
{
if (xmlRow["note" + i.ToString()].ToString().Trim() != "")
{
sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = " + i.ToString() + " and referenceid = " + xmlRow["teamid"];
cmd = new OracleCommand(sql, cn);
adp = new OracleDataAdapter(cmd);
bldr = new OracleCommandBuilder(adp);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count == 0)
{
row = tbl.Rows.Add();
row["referencetype"] = 2;
row["referenceid"] = xmlRow["teamid"];
row["tidbitorder"] = i;
}
else
{
row = tbl.Rows[0];
}
row["text"] = xmlRow["note" + i.ToString()].ToString();
row["enabled"] = 1;
adp.Update(tbl.GetChanges());
tbl.AcceptChanges();
cmd.Dispose();
adp.Dispose();
bldr.Dispose();
tbl.Dispose();
}
}
#endregion
#region Finish
sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = 21 and referenceid = " + xmlRow["teamid"];
cmd = new OracleCommand(sql, cn);
adp = new OracleDataAdapter(cmd);
bldr = new OracleCommandBuilder(adp);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count == 0)
{
row = tbl.Rows.Add();
row["referencetype"] = 2;
row["referenceid"] = xmlRow["teamid"];
row["tidbitorder"] = 21;
}
else
{
row = tbl.Rows[0];
}
row["text"] = xmlRow["divresult"].ToString();
row["enabled"] = 1;
adp.Update(tbl.GetChanges());
tbl.AcceptChanges();
cmd.Dispose();
adp.Dispose();
bldr.Dispose();
tbl.Dispose();
#endregion
#region Record
sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = 20 and referenceid = " + xmlRow["teamid"];
cmd = new OracleCommand(sql, cn);
adp = new OracleDataAdapter(cmd);
bldr = new OracleCommandBuilder(adp);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count == 0)
{
row = tbl.Rows.Add();
row["referencetype"] = 2;
row["referenceid"] = xmlRow["teamid"];
row["tidbitorder"] = 20;
}
else
{
row = tbl.Rows[0];
}
row["text"] = xmlRow["record"].ToString();
row["enabled"] = 1;
adp.Update(tbl.GetChanges());
tbl.AcceptChanges();
cmd.Dispose();
adp.Dispose();
bldr.Dispose();
tbl.Dispose();
#endregion
#region Lineup
sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = 30 and referenceid = " + xmlRow["teamid"];
cmd = new OracleCommand(sql, cn);
adp = new OracleDataAdapter(cmd);
bldr = new OracleCommandBuilder(adp);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count == 0)
{
row = tbl.Rows.Add();
row["referencetype"] = 2;
row["referenceid"] = xmlRow["teamid"];
row["tidbitorder"] = 30;
}
else
{
row = tbl.Rows[0];
}
row["text"] = xmlRow["lineup"].ToString();
row["enabled"] = 1;
adp.Update(tbl.GetChanges());
tbl.AcceptChanges();
cmd.Dispose();
adp.Dispose();
bldr.Dispose();
tbl.Dispose();
#endregion
}
teamsImported++;
}
worker.ReportProgress(teamsImported / teamsToImport);
} //foreach team
} //try
catch (Exception ex)
{
importErrors++;
}
finally
{
if (cmd != null) cmd.Dispose();
if (adp != null) adp.Dispose();
if (bldr != null) bldr.Dispose();
if (rdr != null) rdr.Dispose();
if (cn != null) cn.Close(); cn.Dispose();
//log.Close();
}
}; //dowork
worker.RunWorkerCompleted += delegate(object s, RunWorkerCompletedEventArgs args)
{
SetStatusBarMsg(teamsImported.ToString() + " of " + teamsToImport.ToString() + " teams successfully imported at " + DateTime.Now.ToLongTimeString(), "Green");
GlobalCollections.Instance.LoadTeams();
};
worker.ProgressChanged += delegate(object s, ProgressChangedEventArgs args)
{
SetStatusBarMsg(teamsImported.ToString() + " of " + teamsToImport.ToString() + " teams imported.", "Yellow");
};
worker.RunWorkerAsync(file);
}
public static void GetSchoolsFromSDR()
{
int teamsToImport = 0;
int teamsImported = 0;
SetStatusBarMsg("Importing schools...", "Yellow");
BackgroundWorker worker = new BackgroundWorker();
worker.WorkerReportsProgress = true;
worker.DoWork += delegate(object s, DoWorkEventArgs args)
{
OracleConnection cn = null;
OracleCommand cmd = null;
OracleDataReader rdr = null;
DataTable tbl = null;
try
{
cn = createConnectionSDR();
if (cn != null)
{
String sql = "select * from espnews.news_teams where league_id = 'NCAAB'";
cmd = new OracleCommand(sql, cn);
rdr = cmd.ExecuteReader();
tbl = new DataTable();
tbl.Load(rdr);
rdr.Close();
rdr.Dispose();
MySqlConnection myCn = createConnectionMySql();
teamsToImport = tbl.Rows.Count;
foreach (DataRow row in tbl.Rows)
{
sql = "select * from teams where id = " + row["team_id"].ToString();
MySqlCommand myCmd = new MySqlCommand(sql, myCn);
MySqlDataAdapter myAdp = new MySqlDataAdapter(myCmd);
MySqlCommandBuilder myBldr = new MySqlCommandBuilder(myAdp);
DataTable myTbl = new DataTable();
DataRow myRow;
try
{
myAdp.Fill(myTbl);
if (myTbl.Rows.Count == 0)
{
myRow = myTbl.Rows.Add();
}
else
{
myRow = myTbl.Rows[0];
}
myRow["id"] = Convert.ToInt32(row["team_id"]);
myRow["name"] = row["team_name"].ToString();
myRow["tricode"] = row["abbrev_4"].ToString();
myRow["city"] = row["city_st_name"].ToString();
myRow["league"] = row["league_id"].ToString();
DataSet dsLogos = getSchoolLogos(new Int32[] { Convert.ToInt32(row["team_id"].ToString()) });
if (dsLogos != null)
{
myRow["logo"] = "\\\\HEADSHOT01\\Images\\" + dsLogos.Tables[0].Rows[0]["IMAGEPATH"].ToString().ToUpper().Replace(".TGA", "_256.TGA");
myRow["swatch"] = "\\\\HEADSHOT01\\Images\\" + dsLogos.Tables[0].Rows[0]["SWATCHPATH"].ToString().ToUpper();
}
myAdp.Update(myTbl.GetChanges());
myTbl.AcceptChanges();
teamsImported++;
worker.ReportProgress(teamsImported / teamsToImport);
}
finally
{
if (myCmd != null) myCmd.Dispose();
if (myTbl != null) myTbl.Dispose();
if (myAdp != null) myAdp.Dispose();
if (myBldr != null) myBldr.Dispose();
}
}
}
else
{
System.Windows.MessageBox.Show("There was a problem connecting to the SDR database");
}
}
finally
{
if (cmd != null) cmd.Dispose();
if (tbl != null) tbl.Dispose();
if (cn != null) cn.Close(); cn.Dispose();
}
}; //do work
worker.RunWorkerCompleted += delegate(object s, RunWorkerCompletedEventArgs args)
{
SetStatusBarMsg(teamsImported.ToString() + " schools successfully imported at " + DateTime.Now.ToLongTimeString() + ".", "Green");
GlobalCollections.Instance.LoadSchools();
};
worker.ProgressChanged += delegate(object s, ProgressChangedEventArgs args)
{
SetStatusBarMsg(teamsImported.ToString() + " of " + teamsToImport.ToString() + " schools imported.", "Yellow");
};
worker.RunWorkerAsync();
}
private void SaveConditions()
{
if (!bInputConditionOK) { return; }
if (ConditionTable.Rows.Count > 0)
{
// DataRow dr_Con = ConditionTable.Rows[c1FlexGrid_Conditions.Row - 1];
try
{
MySqlCommandBuilder myCommand = new MySqlCommandBuilder(da_Condition);
da_Condition.Update(ConditionTable);
myCommand.Dispose();
}
catch (DBConcurrencyException DBCe)
{
mySave.InsertRow((int)Definition.Message.D_DEBUG, "button_Condition_Save_Click: \r\n" + DBCe.ToString());
}
catch (Exception ex) { mySave.InsertRow((int)Definition.Message.D_ALARM, ex.ToString()); }
c1FlexGrid_Conditions.Update();
bConditionTableChanged = false;
bInputConditionOK = true;
SaveTimeWarningAlarm();
}
}
private void SaveCommands()
{
if (!bInputCommandOK) { return; }
try
{
MySqlCommandBuilder myCommand = new MySqlCommandBuilder(da_Commands);
da_Commands.Update(CommandTable);
bCommandTableChanged = false;
bInputCommandOK = true;
myCommand.Dispose();
}
catch (DBConcurrencyException DBCe)
{
mySave.InsertRow((int)Definition.Message.D_DEBUG, "button_Command_Save_Click: \r\n" + DBCe.ToString());
}
catch (Exception ex) { mySave.InsertRow((int)Definition.Message.D_ALARM, ex.ToString()); /* MessageBox.Show(ex.ToString());*/ }
}
public Boolean recordSave(String primaryId)
{
Boolean newRecord = m_state.Equals(State.Add);
if (newRecord)
{
DataTable dataTable = m_dataSet.Tables[m_tableName];
DataRow newDataRow = dataTable.NewRow();
newDataRow[ColumnData.GetName(m_idField)] = primaryId;
updateFields(newDataRow);
Object[] columns = newDataRow.ItemArray;
dataTable.Rows.Add(newDataRow);
MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(m_sqlDataAdapter);
m_sqlDataAdapter.Update(m_dataSet, m_tableName);
m_dataSet.AcceptChanges();
mySqlCommandBuilder.Dispose();
m_dataRow = newDataRow;
}
else // Edit
{
try
{
// http://www.codersource.net/csharp_adonet_tutorial_ed.html
m_dataRow.BeginEdit();
updateFields(m_dataRow);
m_dataRow.EndEdit();
if (m_dataRow.HasErrors)
{
DataColumn[] errorColumns = m_dataRow.GetColumnsInError();
for (int error = 0; error < errorColumns.Length; error++)
{
// TODO: Handle column validation errors
MessageBox.Show("Validation error with column " + errorColumns[error].ColumnName);
}
if (errorColumns.Length != 0)
{
return false;
}
}
else
{
/*DataRow[] dataRows = new DataRow[]{m_dataRow};
m_dataSet.Merge(dataRows);
DataSet dsChanges = m_dataSet.GetChanges(DataRowState.Modified);
if (dsChanges.HasErrors)
{
m_dataSet.RejectChanges();
}
else
{
MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(m_sqlDataAdapter);
m_sqlDataAdapter.Update(dsChanges, m_tableName);
m_dataSet.AcceptChanges();
}*/
MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(m_sqlDataAdapter); // Last one wins
DataRow[] dataRows = new DataRow[]{m_dataRow};
m_sqlDataAdapter.Update(dataRows);
mySqlCommandBuilder.Dispose();
}
}
catch (System.Data.DBConcurrencyException e)
{
//DB.printDataSetContents(m_dataSet, "TableIO.recordSave()");
// http://msdn.microsoft.com/en-us/library/tf579hcz(VS.80).aspx
MessageBox.Show("Another user has modified the current record.\n"
+ "To perform your modifications:\n"
+ "\t1. Jot down your changes\n"
+ "\t2. Cancel your changes\n"
+ "\t3. Modify the record again"
+ "\n\n" + e.Message
+ "\n\n" + e.StackTrace,
"Changes Cannot be Saved");
return false;
}
}
return true;
}
public String recordDelete()
{
String id = m_gui.getCurrentEditor().getPrimaryId();
DataRow[] dataRows = m_dataSet.Tables[m_tableName].Select(ColumnData.GetName(m_idField) + " = " + id);
if (dataRows.Length != 0)
{
try
{
DataRow m_dataRow = dataRows[0];
m_dataRow.Delete();
MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(m_sqlDataAdapter);
m_sqlDataAdapter.Update(m_dataSet, m_tableName);
m_dataSet.AcceptChanges();
mySqlCommandBuilder.Dispose();
m_dataRow = null;
}
catch(MySqlException e)
{
switch (e.Number)
{
case 1451:
return "This record cannot be deleted because it is used in other tables\n\n"
+ e.GetBaseException().Message;
default:
return "MySqlException: " + e.Number.ToString();
}
}
}
return "";
/*
String query = "DELETE FROM "
+ m_tableName
+ " WHERE "
+ ColumnData.GetName(m_idField)
+ " = "
+ m_dataRow[0].ToString();
m_dataRow.Delete();
m_sqlDataAdapter.Update(m_dataset);
m_dataSet.AcceptChanges();
m_dataRow = null;
setState(TableIO.State.View);
viewRecord();
*/
}
public void UnsignedTypes()
{
execSQL("CREATE TABLE Test (b TINYINT UNSIGNED PRIMARY KEY)");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataView dv = new DataView(dt);
DataRowView row;
row = dv.AddNew();
row["b"] = 120;
row.EndEdit();
da.Update(dv.Table);
row = dv.AddNew();
row["b"] = 135;
row.EndEdit();
da.Update(dv.Table);
cb.Dispose();
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (b MEDIUMINT UNSIGNED PRIMARY KEY)");
execSQL("INSERT INTO Test VALUES(20)");
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test WHERE (b > ?id)", conn);
cmd.Parameters.Add("?id", MySqlDbType.UInt16).Value = 10;
using (MySqlDataReader dr = cmd.ExecuteReader())
{
dr.Read();
Assert.AreEqual(20, dr.GetUInt16(0));
}
}
public static bool SaveCategory(Category category)
{
MySqlConnection cn = null;
MySqlCommand cmd = null;
MySqlDataAdapter adp = null;
MySqlCommandBuilder bldr = null;
DataTable tbl = null;
DataRow row = null;
bool saved = false;
try
{
cn = createConnectionMySql();
String sql = "select * from categories where categoryid = " + category.ID;
cmd = new MySqlCommand(sql, cn);
adp = new MySqlDataAdapter(cmd);
bldr = new MySqlCommandBuilder(adp);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count == 0)
{
row = tbl.Rows.Add();
}
else
{
row = tbl.Rows[0];
}
row["categoryname"] = category.FullName;
row["tricode"] = category.Tricode;
foreach (Tidbit tidbit in category.Tidbits)
{
updateTidbitMySql(tidbit.ReferenceType, category.ID, tidbit.TidbitOrder, tidbit.TidbitText, tidbit.Timecode, tidbit.Enabled);
}
adp.Update(tbl.GetChanges());
tbl.AcceptChanges();
saved = true;
}
finally
{
if (cmd != null) cmd.Dispose();
if (adp != null) adp.Dispose();
if (bldr != null) bldr.Dispose();
if (tbl != null) tbl.Dispose();
if (cn != null) cn.Close(); cn.Dispose();
}
return saved;
}
public void DateTimeInDataTable()
{
execSQL("INSERT INTO Test VALUES(1, Now(), '0000-00-00', NULL, NULL)");
using (MySqlConnection c = new MySqlConnection(
conn.ConnectionString + ";pooling=false;AllowZeroDatetime=true"))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row["id"] = 2;
row["dt"] = new MySqlDateTime(DateTime.Now);
row["d"] = new MySqlDateTime(DateTime.Now);
row["t"] = new TimeSpan(1, 1, 1);
row["ts"] = DBNull.Value;
dt.Rows.Add(row);
da.Update(dt);
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(2, dt.Rows.Count);
cb.Dispose();
}
}
public static bool SavePoll(List<string> pollLines)
{
MySqlConnection cn = null;
MySqlCommand cmd = null;
MySqlDataAdapter adp = null;
MySqlCommandBuilder bldr = null;
DataTable tbl = null;
DataRow row;
bool saved = false;
List<string[]> lines = new List<string[]>();
foreach (string line in pollLines)
{
lines.Add(line.Split('|'));
}
try
{
cn = createConnectionMySql();
String sql = "select * from poll where pollid = 1";
cmd = new MySqlCommand(sql, cn);
adp = new MySqlDataAdapter(cmd);
bldr = new MySqlCommandBuilder(adp);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count == 0)
{
row = tbl.Rows.Add();
}
else
{
row = tbl.Rows[0];
}
for (int i = 1; i <= 5; i++)
{
row["answer" + i.ToString()] = "";
row["answer" + i.ToString() + "pct"] = "";
}
if (lines.Count > 0)
{
foreach (string[] line in lines)
{
switch (line[0].ToString().ToUpper())
{
case "Q":
row["question"] = line[1].ToString();
break;
case "P":
row["pollname"] = line[1].ToString();
break;
case "T":
row["totalvotes"] = line[1].ToString();
break;
default:
row["answer" + line[0].ToString()] = line[1].ToString();
row["answer" + line[0].ToString() + "pct"] = line[3].ToString();
break;
}
}
adp.Update(tbl.GetChanges());
tbl.AcceptChanges();
}
saved = true;
}
catch (Exception ex)
{
}
finally
{
if (cmd != null) cmd.Dispose();
if (adp != null) adp.Dispose();
if (bldr != null) bldr.Dispose();
if (tbl != null) tbl.Dispose();
if (cn != null) cn.Close(); cn.Dispose();
}
return saved;
}
public void TestAllowZeroDateTime()
{
execSQL("TRUNCATE TABLE Test");
execSQL("INSERT INTO Test (id, d, dt) VALUES (1, '0000-00-00', '0000-00-00 00:00:00')");
using (MySqlConnection c = new MySqlConnection(
conn.ConnectionString + ";pooling=false;AllowZeroDatetime=true"))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", c);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Assert.IsTrue(reader.GetValue(1) is MySqlDateTime);
Assert.IsTrue(reader.GetValue(2) is MySqlDateTime);
Assert.IsFalse(reader.GetMySqlDateTime(1).IsValidDateTime);
Assert.IsFalse(reader.GetMySqlDateTime(2).IsValidDateTime);
try
{
reader.GetDateTime(1);
Assert.Fail("This should not succeed");
}
catch (MySqlConversionException)
{
}
}
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
da.Fill(dt);
dt.Rows[0]["id"] = 2;
DataRow row = dt.NewRow();
row["id"] = 3;
row["d"] = new MySqlDateTime("2003-9-24");
row["dt"] = new MySqlDateTime("0000/0/00 00:00:00");
dt.Rows.Add(row);
da.Update(dt);
dt.Clear();
da.Fill(dt);
Assert.AreEqual(2, dt.Rows.Count);
MySqlDateTime date = (MySqlDateTime)dt.Rows[1]["d"];
Assert.AreEqual(2003, date.Year);
Assert.AreEqual(9, date.Month);
Assert.AreEqual(24, date.Day);
cb.Dispose();
}
}
public static bool SaveTeam(Team team)
{
MySqlConnection cn = null;
MySqlCommand cmd = null;
MySqlDataAdapter adp = null;
MySqlCommandBuilder bldr = null;
DataTable tbl = null;
DataRow row = null;
bool saved = false;
try
{
cn = createConnectionMySql();
String sql = "select * from teams where id = " + team.ID;
cmd = new MySqlCommand(sql, cn);
adp = new MySqlDataAdapter(cmd);
bldr = new MySqlCommandBuilder(adp);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count == 0)
{
row = tbl.Rows.Add();
}
else
{
row = tbl.Rows[0];
}
row["name"] = team.Name;
row["tricode"] = team.Tricode;
row["overallrecord"] = team.OverallRecord;
row["conferencerecord"] = team.ConferenceRecord;
row["lotterypctrank"] = team.LotteryPctRank;
row["lotteryorder"] = team.LotteryOrder;
OracleConnection cnO = createConnectionSDR();
try
{
foreach (Tidbit tidbit in team.Tidbits)
{
if (ConfigurationManager.AppSettings["TeamTidbitsDatabase"].ToString().ToUpper() == "MYSQL")
{
updateTidbitMySql(tidbit.ReferenceType, tidbit.ReferenceID, tidbit.TidbitOrder, tidbit.TidbitText, null, tidbit.Enabled);
}
else
{
UpdateTidbitSDR(tidbit.ReferenceType, tidbit.ReferenceID, tidbit.TidbitOrder, tidbit.TidbitText, null, tidbit.Enabled);
}
}
}
finally
{
if (cnO != null) cnO.Close(); cnO.Dispose();
}
adp.Update(tbl.GetChanges());
tbl.AcceptChanges();
saved = true;
}
finally
{
if (cmd != null) cmd.Dispose();
if (adp != null) adp.Dispose();
if (bldr != null) bldr.Dispose();
if (tbl != null) tbl.Dispose();
if (cn != null) cn.Close(); cn.Dispose();
}
return saved;
}
public void FillWithNulls()
{
execSQL(@"CREATE TABLE Test (id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100), PRIMARY KEY(id))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Columns[0].AutoIncrement = true;
dt.Columns[0].AutoIncrementSeed = -1;
dt.Columns[0].AutoIncrementStep = -1;
DataRow row = dt.NewRow();
row["name"] = "Test1";
dt.Rows.Add(row);
da.Update(dt);
dt.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual("Test1", dt.Rows[0]["name"]);
row = dt.NewRow();
row["name"] = System.DBNull.Value;
dt.Rows.Add(row);
da.Update(dt);
dt.Clear();
da.Fill(dt);
Assert.AreEqual(2, dt.Rows.Count);
Assert.AreEqual(2, dt.Rows[1]["id"]);
Assert.AreEqual(DBNull.Value, dt.Rows[1]["name"]);
row = dt.NewRow();
row["name"] = "Test3";
dt.Rows.Add(row);
da.Update(dt);
dt.Clear();
da.Fill(dt);
Assert.AreEqual(3, dt.Rows.Count);
Assert.AreEqual(3, dt.Rows[2]["id"]);
Assert.AreEqual("Test3", dt.Rows[2]["name"]);
cb.Dispose();
}
private static void updateTidbitMySql(int tidbitTypeId, Int32 refId, int tidbitOrder, string tidbitText = "", string timecode = "", bool enabled = false)
{
MySqlConnection cn = null;
MySqlCommand cmd = null;
MySqlDataAdapter adp = null;
MySqlCommandBuilder bldr = null;
DataTable tbl = null;
DataRow row = null;
try
{
cn = createConnectionMySql();
string sql = "select * from tidbits where referencetype = " + tidbitTypeId + " and referenceid = " + refId + " and tidbitorder = " + tidbitOrder;
cmd = new MySqlCommand(sql, cn);
adp = new MySqlDataAdapter(cmd);
bldr = new MySqlCommandBuilder(adp);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count == 0)
{
row = tbl.Rows.Add();
}
else
{
row = tbl.Rows[0];
}
row["referencetype"] = tidbitTypeId;
row["referenceid"] = refId;
row["tidbitorder"] = tidbitOrder;
row["text"] = tidbitText;
row["enabled"] = enabled;
//row["timecode"] = timecode;
adp.Update(tbl.GetChanges());
tbl.AcceptChanges();
}
finally
{
if (cmd != null) cmd.Dispose();
if (adp != null) adp.Dispose();
if (bldr != null) bldr.Dispose();
if (tbl != null) tbl.Dispose();
}
}
public void TestUpdate()
{
CreateDefaultTable();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow dr = dt.NewRow();
dr["id2"] = 2;
dr["name"] = "TestName1";
dt.Rows.Add(dr);
int count = da.Update(dt);
// make sure our refresh of auto increment values worked
Assert.AreEqual(1, count, "checking insert count");
Assert.IsNotNull(dt.Rows[dt.Rows.Count - 1]["id"],
"Checking auto increment column");
dt.Rows.Clear();
da.Fill(dt);
dt.Rows[0]["id2"] = 3;
dt.Rows[0]["name"] = "TestName2";
dt.Rows[0]["ts"] = DBNull.Value;
DateTime day1 = new DateTime(2003, 1, 16, 12, 24, 0);
dt.Rows[0]["dt"] = day1;
dt.Rows[0]["tm"] = day1.TimeOfDay;
count = da.Update(dt);
Assert.IsNotNull(dt.Rows[0]["ts"], "checking refresh of record");
Assert.AreEqual(3, dt.Rows[0]["id2"], "checking refresh of primary column");
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(1, count, "checking update count");
DateTime dateTime = (DateTime)dt.Rows[0]["dt"];
Assert.AreEqual(day1.Date, dateTime.Date, "checking date");
Assert.AreEqual(day1.TimeOfDay, dt.Rows[0]["tm"], "checking time");
dt.Rows[0].Delete();
count = da.Update(dt);
Assert.AreEqual(1, count, "checking insert count");
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(0, dt.Rows.Count, "checking row count");
cb.Dispose();
}
/// <summary>
/// Commits any changes to segment data set to the database
/// </summary>
public void CommitChanges()
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString;
MySqlConnection connection = new MySqlConnection(connString);
MySqlDataAdapter dsAdapter = new MySqlDataAdapter();
dsAdapter.SelectCommand = new MySqlCommand("SELECT * FROM Segments WHERE Segments.Trip_ID = " + this.ID, connection);
MySqlCommandBuilder dsBuilder = new MySqlCommandBuilder(dsAdapter);
dsBuilder.GetUpdateCommand();
dsBuilder.GetInsertCommand();
dsBuilder.GetDeleteCommand();
try
{
dsAdapter.Update(this.SegmentDataSet, "Segments");
}
catch (Exception e)
{
System.Diagnostics.Debug.Write(e.ToString());
}
finally
{
dsBuilder.Dispose();
dsAdapter.Dispose();
connection.Dispose();
}
}
