public void AutoIncrementColumnsOnInsert()
{
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);
da.InsertCommand = cb.GetInsertCommand();
da.InsertCommand.CommandText += "; SELECT last_insert_id()";
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Columns[0].AutoIncrement = true;
Assert.IsTrue(dt.Columns[0].AutoIncrement);
dt.Columns[0].AutoIncrementSeed = -1;
dt.Columns[0].AutoIncrementStep = -1;
DataRow row = dt.NewRow();
row["name"] = "Test";
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("Test", dt.Rows[0]["name"]);
cb.Dispose();
}
public void AutoIncrementColumnsOnInsert()
{
execSQL("DROP TABLE IF EXISTS Test");
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;
Assert.IsTrue(dt.Columns[0].AutoIncrement);
dt.Columns[0].AutoIncrementSeed = -1;
dt.Columns[0].AutoIncrementStep = -1;
DataRow row = dt.NewRow();
row["name"] = "Test";
try
{
dt.Rows.Add(row);
da.Update(dt);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
dt.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual("Test", dt.Rows[0]["name"]);
cb.Dispose();
}
public void MultiWord()
{
try
{
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"] = 1;
row["name"] = "Name";
row["dt"] = DBNull.Value;
row["tm"] = DBNull.Value;
row["multi word"] = 2;
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Rows[0]["multi word"]);
dt.Rows[0]["multi word"] = 3;
da.Update(dt);
cb.Dispose();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(3, dt.Rows[0]["multi word"]);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
}
public void MultiWord()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, `multi word` int, PRIMARY KEY(id))");
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"] = 1;
row["name"] = "Name";
row["dt"] = DBNull.Value;
row["tm"] = DBNull.Value;
row["multi word"] = 2;
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Rows[0]["multi word"]);
dt.Rows[0]["multi word"] = 3;
da.Update(dt);
cb.Dispose();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(3, dt.Rows[0]["multi word"]);
}
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 void TestUpdate()
{
CreateDefaultTable();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", _fixture.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.True(count == 1, "checking insert count");
Assert.True(dt.Rows[dt.Rows.Count - 1]["id"] != null, "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.True(dt.Rows[0]["ts"] != null, "checking refresh of record");
Assert.True(dt.Rows[0]["id2"] != null, "checking refresh of primary column");
dt.Rows.Clear();
da.Fill(dt);
Assert.True(count == 1, "checking update count");
DateTime dateTime = (DateTime)dt.Rows[0]["dt"];
Assert.True(day1.Date == dateTime.Date, "checking date");
Assert.True(day1.TimeOfDay == (TimeSpan)dt.Rows[0]["tm"], "checking time");
dt.Rows[0].Delete();
count = da.Update(dt);
Assert.True(count == 1, "checking insert count");
dt.Rows.Clear();
da.Fill(dt);
Assert.True(dt.Rows.Count == 0, "checking row count");
cb.Dispose();
}
public void UnsignedTypes()
{
execSQL("DROP TABLE IF EXISTS Test");
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;
MySqlDataReader dr = null;
try
{
dr = cmd.ExecuteReader();
dr.Read();
Assert.AreEqual(20, dr.GetUInt16(0));
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (dr != null)
{
dr.Close();
}
}
}
public void FillWithNulls()
{
st.execSQL("DROP TABLE IF EXISTS Test");
st.execSQL(@"CREATE TABLE Test (id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100), PRIMARY KEY(id))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", st.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.Equal(1, dt.Rows.Count);
Assert.Equal(1, dt.Rows[0]["id"]);
Assert.Equal("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.Equal(2, dt.Rows.Count);
Assert.Equal(2, dt.Rows[1]["id"]);
Assert.Equal(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.Equal(3, dt.Rows.Count);
Assert.Equal(3, dt.Rows[2]["id"]);
Assert.Equal("Test3", dt.Rows[2]["name"]);
cb.Dispose();
}
public async Task UpdateAsync()
{
st.execSQL("CREATE TABLE UpdateAsyncTest (id INT NOT NULL AUTO_INCREMENT, id2 INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, ts TIMESTAMP, OriginalId INT, PRIMARY KEY(id, id2))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM UpdateAsyncTest", st.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 = await da.UpdateAsync(dt);
Assert.True(count == 1, "checking insert count");
Assert.True(dt.Rows[dt.Rows.Count - 1]["id"] != null, "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 = await da.UpdateAsync(dt);
Assert.True(dt.Rows[0]["ts"] != null, "checking refresh of record");
Assert.True(dt.Rows[0]["id2"] != null, "checking refresh of primary column");
dt.Rows.Clear();
da.Fill(dt);
Assert.True(count == 1, "checking update count");
DateTime dateTime = (DateTime)dt.Rows[0]["dt"];
Assert.True(day1.Date == dateTime.Date, "checking date");
Assert.True(day1.TimeOfDay == (TimeSpan)dt.Rows[0]["tm"], "checking time");
dt.Rows[0].Delete();
count = await da.UpdateAsync(dt);
Assert.True(count == 1, "checking insert count");
dt.Rows.Clear();
da.Fill(dt);
Assert.True(dt.Rows.Count == 0, "checking row count");
cb.Dispose();
}
public bool UpdateTable(DataTable table, string tableName, bool setInsertOn = true, bool setModfyOn = true, string additionalMessage = "")
{
try
{
TableHelper.SetDefaultColumnValues(table, setInsertOn, setModfyOn);
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 (DBConcurrencyException cex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable DBConcurrencyError!",
AdditionalMessage = $"Table: {tableName}{Environment.NewLine}AdditionalMessage: {additionalMessage}",
Ex = cex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateTable Error!", cex);
}
return(false);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable Error!",
AdditionalMessage = $"Table: {tableName}{Environment.NewLine}AdditionalMessage: {additionalMessage}",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateTable Error!", ex);
}
return(false);
}
}
public int UpdateData(DataSet newDataSet)
{
if (null == newDataSet)
{
throw new ArgumentNullException("用于更新的数据集不能为空");
}
if (newDataSet.Tables.Count.Equals(0))
{
throw new Exception("用于更新的数据集中没有数据表");
}
int ret = 0;
foreach (DataTable dt in newDataSet.Tables)
{
if (dt.TableName.Length > 0)
{
string str_cols = "";
foreach (DataColumn dc in dt.Columns)
{
str_cols += dc.ColumnName + ",";
}
str_cols = StringUtil.StrLeftBack(str_cols, ",", true);
_DataAdapter = new MySqlDataAdapter();
//_DataAdapter.RowUpdated += new MySqlRowUpdatedEventHandler(_oraDataAdp_RowUpdated);
_DataAdapter.SelectCommand = new MySqlCommand();
_DataAdapter.SelectCommand.Connection = _Connection;
_DataAdapter.SelectCommand.CommandText = string.Format("select {1} from {0} limit 1", dt.TableName, str_cols);
}
MySqlCommandBuilder CmdBuilder = new MySqlCommandBuilder(_DataAdapter);
try
{
ret += _DataAdapter.Update(newDataSet, dt.TableName);
}
catch (Exception e)
{
throw e;
}
finally
{
_Connection.Close();
CmdBuilder.RefreshSchema();
CmdBuilder.Dispose();
_DataAdapter.Dispose();
_DataAdapter = null;
}
}
return(ret);
}
void Tabla_cnvRowValidated(object sender, DataGridViewCellEventArgs e)
{
DataTable changes = ((DataTable)tabla_cnv.DataSource).GetChanges();
if (changes != null)
{
MySqlCommandBuilder mcb = new MySqlCommandBuilder(aux);
aux.UpdateCommand = mcb.GetUpdateCommand();
aux.Update(changes);
((DataTable)tabla_cnv.DataSource).AcceptChanges();
aux.Dispose();
mcb.Dispose();
updateTarifas();
}
}
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 TestAllowZeroDateTime()
{
ExecuteSQL("CREATE TABLE Test (id INT NOT NULL, dt DATETIME, d DATE, " +
"t TIME, ts TIMESTAMP, PRIMARY KEY(id))");
ExecuteSQL("INSERT INTO Test (id, d, dt) VALUES (1, '0000-00-00', '0000-00-00 00:00:00')");
using (MySqlConnection c = new MySqlConnection(
Connection.ConnectionString + ";pooling=false;AllowZeroDatetime=true"))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", c);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Assert.True(reader.GetValue(1) is MySqlDateTime);
Assert.True(reader.GetValue(2) is MySqlDateTime);
Assert.False(reader.GetMySqlDateTime(1).IsValidDateTime);
Assert.False(reader.GetMySqlDateTime(2).IsValidDateTime);
Exception ex = Assert.Throws <MySqlConversionException>(() => reader.GetDateTime(1));
Assert.AreEqual("Unable to convert MySQL date/time value to System.DateTime", ex.Message);
}
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();
}
}
private void FormSimpleSetting_FormClosing(object sender, FormClosingEventArgs e)
{
if (SCB != null)
{
SCB.Dispose();
}
if (sda != null)
{
sda.Dispose();
}
if (scom != null)
{
scom.Dispose();
}
if (ds != null)
{
ds.Clear();
}
//ds.AcceptChanges();
}
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);
}
}
private void btnModify_Click(object sender, EventArgs e)
{
if (this.txtName.Text != "" && this.txtYear.Text != "" &&
this.txtPrice.Text != "" && this.txtDoor.Text != "")
{
try
{
var Conn = new MySqlConnection(StrSQL);
Conn.Open();
var MySqlAdapter = new MySqlDataAdapter("select * from carinfo", Conn);
var ds = new DataSet();
MySqlAdapter.Fill(ds, "dsTable");
var dt = ds.Tables["dsTable"].Select("id =" + Convert.ToInt32(this.Data_Num),
null, DataViewRowState.CurrentRows);
DataRow drTemp;
drTemp = dt[0];
drTemp["c_name"] = this.txtName.Text;
drTemp["c_year"] = this.txtYear.Text;
drTemp["c_price"] = this.txtPrice.Text;
drTemp["c_door"] = this.txtDoor.Text;
var cmdBuild = new MySqlCommandBuilder(MySqlAdapter);
MySqlAdapter.UpdateCommand = cmdBuild.GetUpdateCommand();
MySqlAdapter.Update(ds, "dsTable");
cmdBuild.Dispose();
MessageBox.Show("정상적으로 데이터가 수정되었습니다.", "알림",
MessageBoxButtons.OK, MessageBoxIcon.Information);
lvList_MySqlClient_View();
Control_Clear();
}
catch
{
MessageBox.Show("정상적으로 데이터가 수정되지 않았습니다.", "에러",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public static int MultiUpateData(DataTable dtInfor, string sqlCon)
{
if (dtInfor.Rows.Count == 0)
{
return(-1);
}
string sqlStr = "SELECT * FROM books";
using (MySqlConnection con = new MySqlConnection(sqlCon))
{
using (MySqlCommand cmd = new MySqlCommand(sqlStr, con))
{
con.Open();
MySqlTransaction transction = con.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
int count = 0;
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
dataAdapter.SelectCommand = new MySqlCommand(sqlStr, con);
MySqlCommandBuilder builder = new MySqlCommandBuilder(dataAdapter);
builder.ConflictOption = ConflictOption.OverwriteChanges;
builder.SetAllValues = true;
count = dataAdapter.Update(dtInfor);
transction.Commit();
dtInfor.AcceptChanges();
dataAdapter.Dispose();
builder.Dispose();
MessageBox.Show("批量更新成功");
return(count);
}
catch (Exception)
{
transction.Rollback();
throw;
}
}
}
}
public void UnsignedTypes()
{
st.execSQL("DROP TABLE IF EXISTS Test");
st.execSQL("CREATE TABLE Test (b TINYINT UNSIGNED PRIMARY KEY)");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", st.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();
st.execSQL("DROP TABLE IF EXISTS Test");
st.execSQL("CREATE TABLE Test (b MEDIUMINT UNSIGNED PRIMARY KEY)");
st.execSQL("INSERT INTO Test VALUES(20)");
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test WHERE (b > ?id)", st.conn);
cmd.Parameters.Add("?id", MySqlDbType.UInt16).Value = 10;
using (MySqlDataReader dr = cmd.ExecuteReader())
{
dr.Read();
Assert.Equal(20, dr.GetUInt16(0));
}
}
public void MultiUpdate()
{
try
{
execSQL("INSERT INTO Test (id, name) VALUES (1, 'test1')");
execSQL("INSERT INTO Test (id, name) VALUES (2, 'test2')");
execSQL("INSERT INTO Test (id, name) VALUES (3, 'test3')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 4;
dt.Rows[0]["name"] = "test4";
dt.Rows[1]["id"] = 5;
dt.Rows[1]["name"] = "test5";
dt.Rows[2]["id"] = 6;
dt.Rows[2]["name"] = "test6";
DataTable changes = dt.GetChanges();
da.Update(changes);
dt.AcceptChanges();
dt.Rows[0]["id"] = 7;
dt.Rows[0]["name"] = "test7";
dt.Rows[1]["id"] = 8;
dt.Rows[1]["name"] = "test8";
dt.Rows[2]["id"] = 9;
dt.Rows[2]["name"] = "test9";
changes = dt.GetChanges();
da.Update(changes);
dt.AcceptChanges();
cb.Dispose();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
}
public void SemicolonAtEndOfSQL()
{
st.execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), PRIMARY KEY(id))");
st.execSQL("INSERT INTO Test VALUES(1, 'Data')");
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM `Test`;", st.conn);
da.FillSchema(ds, SchemaType.Source, "Test");
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 2;
da.Update(dt);
dt.Clear();
da.Fill(dt);
cb.Dispose();
Assert.Equal(1, dt.Rows.Count);
Assert.Equal(2, dt.Rows[0]["id"]);
}
public void AutoIncrementColumns()
{
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);
da.Update(ds);
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();
}
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 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();
}
public void SemicolonAtEndOfSQL()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), PRIMARY KEY(id))");
execSQL("INSERT INTO Test VALUES(1, 'Data')");
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM `Test`;", conn);
da.FillSchema(ds, SchemaType.Source, "Test");
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 2;
da.Update(dt);
dt.Clear();
da.Fill(dt);
cb.Dispose();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Rows[0]["id"]);
}
public void MultiUpdate()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, `multi word` int, PRIMARY KEY(id))");
execSQL("INSERT INTO Test (id, name) VALUES (1, 'test1')");
execSQL("INSERT INTO Test (id, name) VALUES (2, 'test2')");
execSQL("INSERT INTO Test (id, name) VALUES (3, 'test3')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 4;
dt.Rows[0]["name"] = "test4";
dt.Rows[1]["id"] = 5;
dt.Rows[1]["name"] = "test5";
dt.Rows[2]["id"] = 6;
dt.Rows[2]["name"] = "test6";
DataTable changes = dt.GetChanges();
da.Update(changes);
dt.AcceptChanges();
dt.Rows[0]["id"] = 7;
dt.Rows[0]["name"] = "test7";
dt.Rows[1]["id"] = 8;
dt.Rows[1]["name"] = "test8";
dt.Rows[2]["id"] = 9;
dt.Rows[2]["name"] = "test9";
changes = dt.GetChanges();
da.Update(changes);
dt.AcceptChanges();
cb.Dispose();
}
public void DifferentDatabase()
{
if (Version < new Version(4, 1)) return;
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, `multi word` int, PRIMARY KEY(id))");
execSQL("INSERT INTO Test (id, name) VALUES (1,'test1')");
execSQL("INSERT INTO Test (id, name) VALUES (2,'test2')");
execSQL("INSERT INTO Test (id, name) VALUES (3,'test3')");
conn.ChangeDatabase(database1);
MySqlDataAdapter da = new MySqlDataAdapter(
String.Format("SELECT id, name FROM `{0}`.Test", database0), conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
ds.Tables[0].Rows[0]["id"] = 4;
DataSet changes = ds.GetChanges();
da.Update(changes);
ds.Merge(changes);
ds.AcceptChanges();
cb.Dispose();
conn.ChangeDatabase(database0);
}
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 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();
}
public void UpdateDataSet()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, blob1 LONGBLOB, text1 LONGTEXT, PRIMARY KEY(id))");
execSQL("INSERT INTO Test VALUES( 1, NULL, 'Text field' )");
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");
}
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 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 void AutoIncrementColumns()
{
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);
da.Update(ds);
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();
}
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 bool GetData(string pSqlcmd, ref DataTable pDt, ref string err, int timeout = -1)
{
err = "";
bool flag = false;
Monitor.TryEnter(m_op_lock, timeout, ref flag);
if (!flag)
{
err = "timeout"; return(false);
}
MySqlConnection cn = null;
MySqlDataAdapter da = null;
DataSet ds = null;
DataTable dt = null;
MySqlCommandBuilder cb = null;
try
{
cn = new MySqlConnection();
da = new MySqlDataAdapter();
ds = new DataSet();
cn.ConnectionString = m_CnString;
da.SelectCommand = new MySqlCommand(pSqlcmd, cn);
cb = new MySqlCommandBuilder(da);
cn.Open();
da.Fill(ds);
dt = ds.Tables[0];
pDt = dt;
return(true);
}
catch (MySqlException ex)
{
pDt = null;
err = ex.Message;
return(false);
}
catch (Exception ex)
{
pDt = null;
err = ex.Message;
return(false);
}
finally
{
Monitor.Exit(m_op_lock);
if (cn != null)
{
cn.Close();
cn.Dispose();
cn = null;
}
if (da != null)
{
da.Dispose();
}
if (ds != null)
{
ds.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
if (cb != null)
{
cb.Dispose();
}
}
}
public void MultiUpdate()
{
try
{
execSQL("INSERT INTO Test (id, name) VALUES (1, 'test1')");
execSQL("INSERT INTO Test (id, name) VALUES (2, 'test2')");
execSQL("INSERT INTO Test (id, name) VALUES (3, 'test3')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 4;
dt.Rows[0]["name"] = "test4";
dt.Rows[1]["id"] = 5;
dt.Rows[1]["name"] = "test5";
dt.Rows[2]["id"] = 6;
dt.Rows[2]["name"] = "test6";
DataTable changes = dt.GetChanges();
da.Update(changes);
dt.AcceptChanges();
dt.Rows[0]["id"] = 7;
dt.Rows[0]["name"] = "test7";
dt.Rows[1]["id"] = 8;
dt.Rows[1]["name"] = "test8";
dt.Rows[2]["id"] = 9;
dt.Rows[2]["name"] = "test9";
changes = dt.GetChanges();
da.Update(changes);
dt.AcceptChanges();
cb.Dispose();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
}
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 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 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();
}