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