public void InsertWithDataSet()
{
using (var ds = new DataSet())
using (var da = new MySqlDataAdapter("SELECT * FROM data_adapter", m_connection))
{
da.Fill(ds);
da.InsertCommand = new MySqlCommand("INSERT INTO data_adapter (int_value, text_value) VALUES (@int, @text)", m_connection);
da.InsertCommand.Parameters.Add(new MySqlParameter("@int", DbType.Int32));
da.InsertCommand.Parameters.Add(new MySqlParameter("@text", DbType.String));
da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[0].SourceColumn = "int_value";
da.InsertCommand.Parameters[1].SourceColumn = "text_value";
var dt = ds.Tables[0];
var dr = dt.NewRow();
dr["int_value"] = 4;
dr["text_value"] = "four";
dt.Rows.Add(dr);
using (var ds2 = ds.GetChanges())
{
da.Update(ds2);
ds.Merge(ds2);
ds.AcceptChanges();
}
}
using (var cmd2 = new MySqlCommand("SELECT id, int_value, text_value FROM data_adapter", m_connection))
using (var dr2 = cmd2.ExecuteReader())
{
Assert.True(dr2.Read());
Assert.Equal(1L, dr2[0]);
Assert.True(dr2.Read());
Assert.Equal(2L, dr2[0]);
Assert.True(dr2.Read());
Assert.Equal(3L, dr2[0]);
Assert.True(dr2.Read());
Assert.Equal(4L, dr2[0]);
Assert.Equal(4, dr2[1]);
Assert.Equal("four", dr2[2]);
}
}
/// <summary>
/// 將 System.Data.DataTable 上傳至資料庫表格 upload_r_item
/// </summary>
/// <param name="_dt"></param>
public static void Fn_Upload_R_Item(DataTable _dt)
{
using (NpgsqlConnection conn = new NpgsqlConnection(GetConnString()))
{
try
{
conn.Open();
DataSet ds = new DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from upload_r_item", conn);
da.DeleteCommand = new NpgsqlCommand("delete from upload_r_item", conn);
da.InsertCommand = new NpgsqlCommand("insert into upload_r_item values (:c1,:c2)", conn);
string[] paraArray = new string[] { "c1", "c2" };
foreach (var para in paraArray)
{
da.InsertCommand.Parameters.Add(para, NpgsqlDbType.Char);
}
string[] colnames = new string[] { "r_name", "unit" };
for (int i = 0; i < da.InsertCommand.Parameters.Count; i++)
{
da.InsertCommand.Parameters[i].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[i].SourceColumn = colnames[i];
}
//Delete
da.DeleteCommand.ExecuteNonQuery();
da.Fill(ds);
//Insert
DataTable dt = ds.Tables[0];
//dt.Merge(_dt);
foreach (DataRow dr in _dt.Rows)
{
DataRow row = dt.NewRow();
row["r_name"] = dr["r_name"];
row["unit"] = dr["unit"];
dt.Rows.Add(row);
}
DataSet d2 = ds.GetChanges();
if (d2 != null)
{
da.Update(d2);
ds.Merge(d2);
}
ds.AcceptChanges();
}
catch (Exception)
{
throw;
}
}
}
//Activate This Construntor to log All To Standard output
//public TestClass():base(true){}
//Activate this constructor to log Failures to a log file
//public TestClass(System.IO.TextWriter tw):base(tw, false){}
//Activate this constructor to log All to a log file
//public TestClass(System.IO.TextWriter tw):base(tw, true){}
//BY DEFAULT LOGGING IS DONE TO THE STANDARD OUTPUT ONLY FOR FAILURES
public void run()
{
Exception exp = null;
DataSet ds = new DataSet();
ds.Tables.Add(GHTUtils.DataProvider.CreateParentDataTable());
try
{
BeginCase("GetChanges 1");
Compare(ds.GetChanges(), null);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
DataRow dr = ds.Tables[0].NewRow();
dr[0] = 9;
ds.Tables[0].Rows.Add(dr);
try
{
BeginCase("GetChanges 2");
Compare(ds.GetChanges() != null, true);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("GetChanges 3");
Compare(ds.GetChanges().Tables[0].Rows[0].ItemArray, dr.ItemArray);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
}
public void UpdateLettingNullFieldValue()
{
var command = new NpgsqlCommand("insert into tableb(field_int2) values (2)", TheConnection);
command.ExecuteNonQuery();
var ds = new DataSet();
var da = new NpgsqlDataAdapter("select * from tableb where field_serial = (select max(field_serial) from tableb)", TheConnection);
da.InsertCommand = new NpgsqlCommand(";", TheConnection);
da.UpdateCommand = new NpgsqlCommand("update tableb set field_int2 = :a, field_timestamp = :b, field_numeric = :c where field_serial = :d", TheConnection);
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("d", NpgsqlDbType.Bigint));
da.UpdateCommand.Parameters[0].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[1].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[2].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[3].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[0].SourceColumn = "field_int2";
da.UpdateCommand.Parameters[1].SourceColumn = "field_timestamp";
da.UpdateCommand.Parameters[2].SourceColumn = "field_numeric";
da.UpdateCommand.Parameters[3].SourceColumn = "field_serial";
da.Fill(ds);
var dt = ds.Tables[0];
Assert.IsNotNull(dt);
var dr = ds.Tables[0].Rows[ds.Tables[0].Rows.Count - 1];
dr["field_int2"] = 4;
var ds2 = ds.GetChanges();
da.Update(ds2);
ds.Merge(ds2);
ds.AcceptChanges();
using (var dr2 = new NpgsqlCommand("select * from tableb where field_serial = (select max(field_serial) from tableb)", TheConnection).ExecuteReader())
{
dr2.Read();
Assert.AreEqual(4, dr2["field_int2"]);
}
}
static void PrintChanges(DataSet ds)
{
DataSet changes = ds.GetChanges();
if (changes != null)
{
Console.WriteLine("--- changes ---");
Print(changes);
}
else
{
Console.WriteLine("--- no changes ---");
}
}
public override void DeleteCmd()
{
if (MessageBox.Show("您确定要删除吗？", "确定", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
DataRow delRow = customTaskDS.Tables[0].Rows[customTaskDGV.CurrentRow.Index];
delRow.Delete();
int i = OMWorkBench.DataAgent.UpdateCustomTask(customTaskDS.GetChanges());
customTaskDS.AcceptChanges();
if (i > 0)
{
MessageBox.Show("删除成功！");
}
}
}
public void Add(PerformerLocalDto performer)
{
AccessRetriever retriever = new AccessRetriever(DatabasePath);
DataSet dataSet = retriever.GetPerformers();
DataTable performersTable = dataSet.Tables[0];
DataRow newRow = dataSet.Tables[0].NewRow();
newRow["Performer"] = performer.Name;
// newRow[2] = 1; // this is supposed to be Performer Type. Can use the column number instead
performersTable.Rows.Add(newRow);
var changes = dataSet.GetChanges();
retriever.UpdateAccessTableFromDataSet(dataSet, "tblPerformers", "ID, Performer");
}
/// <summary>
/// Проверяет наличие ошибок в записях
/// </summary>
/// <param name="dataSet">проверяемый DataSet</param>
/// <returns></returns>
private static bool HaveDataError(DataSet dataSet)
{
DataSet ds = dataSet.GetChanges();
if (ds == null)
{
return(false);
}
if (!ds.HasErrors)
{
return(false);
}
return(true);
}
public void UpdateLettingNullFieldValue()
{
var command = new NpgsqlCommand(@"INSERT INTO data (field_int2) VALUES (2)", Conn);
command.ExecuteNonQuery();
var ds = new DataSet();
var da = new NpgsqlDataAdapter("SELECT * FROM data", Conn);
da.InsertCommand = new NpgsqlCommand(";", Conn);
da.UpdateCommand = new NpgsqlCommand("UPDATE data SET field_int2 = :a, field_timestamp = :b, field_numeric = :c WHERE field_serial = :d", Conn);
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("d", NpgsqlDbType.Bigint));
da.UpdateCommand.Parameters[0].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[1].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[2].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[3].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[0].SourceColumn = "field_int2";
da.UpdateCommand.Parameters[1].SourceColumn = "field_timestamp";
da.UpdateCommand.Parameters[2].SourceColumn = "field_numeric";
da.UpdateCommand.Parameters[3].SourceColumn = "field_serial";
da.Fill(ds);
var dt = ds.Tables[0];
Assert.IsNotNull(dt);
var dr = ds.Tables[0].Rows[ds.Tables[0].Rows.Count - 1];
dr["field_int2"] = 4;
var ds2 = ds.GetChanges();
da.Update(ds2);
ds.Merge(ds2);
ds.AcceptChanges();
using (var dr2 = new NpgsqlCommand(@"SELECT field_int2 FROM data", Conn).ExecuteReader())
{
dr2.Read();
Assert.AreEqual(4, dr2["field_int2"]);
}
}
public bool eliminarDatos(String nombreTabla, ref DataSet ds)
{
DataSet dsEliminar = new DataSet();
dsEliminar = ds.GetChanges(DataRowState.Deleted);
if (ad.EliminarEnBD(nombreTabla, dsEliminar))
{
return(true);
}
else
{
return(false);
}
}
public void UpdateDataSet(DataSet dataset, int?commandTimeout = null)
{
dataset = dataset.GetChanges();
if (dataset == null)
{
return;
}
var changeCount = 0;
using (var con = CreateConnection())
{
con.Open();
using (var trans = con.BeginTransaction())
{
foreach (DataTable table in dataset.Tables)
{
using (var cmd = CreateDbCommand(commandTimeout))
{
cmd.CommandText = string.Format("SELECT {0} FROM {1}",
string.Join(",", table.Columns.Cast <DataColumn>().Select(x => QuoteIdentifier(x.ColumnName))),
QuoteIdentifier(table.TableName));
cmd.Connection = con;
using (var adapter = CreateDbDataAdapter())
{
using (var cmdBuilder = CreateDbCommandBuilder())
{
adapter.SelectCommand = cmd;
cmdBuilder.DataAdapter = adapter;
changeCount += adapter.Update(table);
}
}
}
}
trans.Commit();
}
}
if (changeCount > 0)
{
var dataChange = new DataChange
{
ChangeInfo = dataset.Copy(),
ChangeGetter = arg => (DataSet)arg,
};
RequestFireDataChanged(dataChange);
}
}
// <Snippet1>
private void CheckForErrors(DataSet dataSet)
{
// Invoke GetChanges on the DataSet to create a reduced set.
DataSet thisDataSet = dataSet.GetChanges();
// Check each table's HasErrors property.
foreach (DataTable table in thisDataSet.Tables)
{
// If HasErrors is true, reconcile errors.
if (table.HasErrors)
{
// Insert code to reconcile errors.
}
}
}
public bool ActualizarDatos(String nombreTabla, ref DataSet ds)
{
DataSet dsActualizar = new DataSet();
dsActualizar = ds.GetChanges(DataRowState.Modified);
if (ad.ActualizarEnBD(nombreTabla, ds))
{
return(true);
}
else
{
return(false);
}
}
public static void SaveData(DataSet changes)
{
if (changes == null)
{
return;
}
DataSet ds_upd = changes.GetChanges(DataRowState.Deleted | DataRowState.Added);
if (ds_upd != null)
{
_da.Update(ds_upd.Tables[0]);
changes.Merge(ds_upd);
}
}
private void button3_Click_1(object sender, EventArgs e)
{
changes = ds.GetChanges();
if (changes != null)
{
SqlCommandBuilder builder = new SqlCommandBuilder(sda);
builder.GetInsertCommand();
int updatesRows = sda.Update(changes, "Tkani");
ds.AcceptChanges();
}
LoadList();
MessageBox.Show("Успешно!", "Заголовок", MessageBoxButtons.OKCancel);
}
public async Task Insert_with_DataSet()
{
using var conn = await OpenConnectionAsync();
await using var _ = await SetupTempTable(conn, out var table);
var ds = new DataSet();
var da = new NpgsqlDataAdapter($"SELECT * FROM {table}", conn);
da.InsertCommand = new NpgsqlCommand($"INSERT INTO {table} (field_int2, field_timestamp, field_numeric) VALUES (:a, :b, :c)", conn);
da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime2));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));
da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[0].SourceColumn = "field_int2";
da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp";
da.InsertCommand.Parameters[2].SourceColumn = "field_numeric";
da.Fill(ds);
var dt = ds.Tables[0];
var dr = dt.NewRow();
dr["field_int2"] = 4;
dr["field_timestamp"] = new DateTime(2003, 01, 30, 14, 0, 0);
dr["field_numeric"] = 7.3M;
dt.Rows.Add(dr);
var ds2 = ds.GetChanges() !;
da.Update(ds2);
ds.Merge(ds2);
ds.AcceptChanges();
var dr2 = new NpgsqlCommand($"SELECT field_int2, field_numeric, field_timestamp FROM {table}", conn).ExecuteReader();
dr2.Read();
Assert.AreEqual(4, dr2[0]);
Assert.AreEqual(7.3000000M, dr2[1]);
dr2.Close();
}
public bool insertarDatos(String nombreTabla, ref DataSet ds)
{
DataSet dsInsertar = new DataSet();
dsInsertar = ds.GetChanges(DataRowState.Added);
if (ad.InsertarEnBD(nombreTabla, dsInsertar) == true)
{
return(true);
}
else
{
return(false);
}
}
private void barButtonItemSave_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
Model.Role r = this.bindingSourceRole.Current as Model.Role;
if (r != null)
{
r.IsXOQuantity = this.checkEditIsXOQuantity.Checked;
r.IsXOPrice = this.checkEditIsXOPrice.Checked;
r.IsCOPrice = this.checkEditIsCOPrice.Checked;
r.IsCOCount = this.checkEditIsCOCount.Checked;
r.IsStockCount = this.checkEditIsStockCount.Checked;
r.IsProductCost = this.checkEditIsProductCost.Checked;
r.IsEmployeeBasicInfo = this.checkEditIsEmployeeBasicInfo.Checked;
r.IsSalaryViewCalc = this.checkEditIsSalaryViewCalc.Checked;
r.IsCOJiaoYiMingXi = this.checkEditIsCOJiaoYiMingXi.Checked;
r.IsCOFaPiaoZiLiao = this.checkEditIsCOFaPiaoZiLiao.Checked;
r.IsCOZhangKuanZiLiao = this.checkEditIsCOZhangKuanZiLiao.Checked;
r.IsCOXiangGuanZiLiao = this.checkEditIsCOXiangGuanZiLiao.Checked;
r.IsCOJinHuoJinE = this.checkEditIsCOJinHuoJinE.Checked;
r.IsXOJiaoYiMingXi = this.checkEditIsXOJiaoYiMingXi.Checked;
r.IsXOFaPiaoZiLiao = this.checkEditIsXOFaPiaoZiLiao.Checked;
r.IsXOZhangKuanZiLiao = this.checkEditIsXOZhangKuanZiLiao.Checked;
r.IsXOXiangGuanZiLiao = this.checkEditIsXOXiangGuanZiLiao.Checked;
r.IsXOJinHuoJinE = this.checkEditIsXOJinHuoJinE.Checked;
r.IsPOCPrice = this.checkEditWWPrice.Checked;
r.IsPODPrice = this.checkEditOtherIndepotPrice.Checked;
r.IsPIDPrice = this.checkEditPIDPrice.Checked;
roleManager.Update(r);
BL.V.RoleList = roleManager.Select(BL.V.ActiveOperator.OperatorsId);
}
if (!this.gridView2.PostEditor() || !this.gridView2.UpdateCurrentRow())
{
return;
}
if (ds.HasChanges())
{
//int a = ds.GetChanges().Tables[0].Rows.Count;
this.roleOperationManager.UpdateTable(ds.GetChanges(), _roleid);
ds.AcceptChanges();
}
MessageBox.Show(Properties.Resources.SuccessfullySaved, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void btnSua_Click(object sender, EventArgs e)
{
DataSet changes = new DataSet();
MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
changes = ds.GetChanges();
if (changes == null)
{
MessageBox.Show("Chưa có bất kỳ thay đổi nào");
}
else
{
adapter.Update(ds.Tables["bd"]);
ds.AcceptChanges();
MessageBox.Show("Cập nhật thành công");
}
}
private void button2_Click(object sender, EventArgs e)
{
try
{
cmdBuilder = new SqlCommandBuilder(adapter);
changes = ds.GetChanges();
if (changes != null)
{
adapter.Update(changes);
}
MessageBox.Show("Changes Done");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void button1_Click(object sender, EventArgs e)
{
String query = "Select * from Заказы_сотрудников";
sda = new SqlDataAdapter(query, ConnectionString);
ds = new DataSet();
DataSet changes = ds.GetChanges();
if (changes != null)
{
SqlCommandBuilder builder = new SqlCommandBuilder(sda);
builder.GetInsertCommand();
int updateRows = sda.Update(changes, "Заказы_сотрудников");
ds.AcceptChanges();
}
}
private void btnFiltersCommit_Click(object sender, EventArgs e)
{
//// get changes
//DataSet dsTemp = dsFilters.GetChanges();
//// update the database
//daFilters.Update(dsTemp);
//// merge changes back into dsFilters data set
//dsFilters.Merge(dsTemp);
//dsFilters.AcceptChanges();
// try to do it all at once
daFilters.Update(dsFilters.GetChanges());
LoadFilters();
}
public void InsertWithDataSet()
{
using (var conn = OpenConnection())
{
Setup(conn);
var ds = new DataSet();
var da = new NpgsqlDataAdapter("SELECT * FROM data", conn);
da.InsertCommand = new NpgsqlCommand("INSERT INTO data (field_int2, field_timestamp, field_numeric) VALUES (:a, :b, :c)", conn);
da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));
da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[0].SourceColumn = "field_int2";
da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp";
da.InsertCommand.Parameters[2].SourceColumn = "field_numeric";
da.Fill(ds);
var dt = ds.Tables[0];
var dr = dt.NewRow();
dr["field_int2"] = 4;
dr["field_timestamp"] = new DateTime(2003, 01, 30, 14, 0, 0);
dr["field_numeric"] = 7.3M;
dt.Rows.Add(dr);
var ds2 = ds.GetChanges();
da.Update(ds2);
ds.Merge(ds2);
ds.AcceptChanges();
var dr2 = new NpgsqlCommand("SELECT field_int2, field_numeric, field_timestamp FROM data", conn).ExecuteReader();
dr2.Read();
Assert.AreEqual(4, dr2[0]);
Assert.AreEqual(7.3000000M, dr2[1]);
dr2.Close();
}
}
private void listView2_SelectedIndexChanged(object sender, EventArgs e)
{
if (listView2.SelectedIndices.Count == 0)
{
dataGridView8.DataSource = null;
richTextBox8.Clear();
richTextBox7.Clear();
richTextBox13.Clear();
}
else
{
try
{
dataSet21 = dataSet11.GetChanges();
if (dataSet21 != null)
{
adapter.Update(dataSet21);
}
}
catch (Exception)
{
dataSet21 = null;
}
UseWaitCursor = true;
BindingSource bindingSource1 = new BindingSource();
DataSet dataSet1 = dbw1.ReadDataBaseToDataSet("QUIM", "select progName, CreationD_rep, percofend from Report where progName LIKE '" + listView2.Items[listView2.SelectedIndices[0]].Text + "'");
richTextBox8.Text = dataSet1.Tables[0].Rows[0].ItemArray[0].ToString();
richTextBox7.Text = dataSet1.Tables[0].Rows[0].ItemArray[1].ToString();
richTextBox13.Text = dataSet1.Tables[0].Rows[0].ItemArray[2].ToString();
dataSet21 = new DataSet();
adapter = new SqlDataAdapter();
dataSet11 = dbw1.ReadDataBaseToDataSet("QUIM", "select m.name_met, mr.Curvalue, mr.Type, mr.id_metrInrep from metric m, MetrInRep mr where m.id_met = mr.id_met and mr.id_rep in (select r.id_rep from report r where r.progName like '" + listView2.Items[listView2.SelectedIndices[0]].Text + "')");
dataGridView8.DataSource = dataSet11.Tables[0];
dataGridView8.Columns[0].HeaderText = "NAME METRIC:";
dataGridView8.Columns[0].ReadOnly = true;
dataGridView8.Columns[1].HeaderText = "VALUE:";
dataGridView8.Columns[2].HeaderText = "TYPE:";
dataGridView8.Columns[3].Visible = false;
dataGridView8.Columns[3].ReadOnly = true;
adapter = dbw1.fillDataAdapter(SqlConnectionParametrs.DataBaseName, "select mr.Curvalue, mr.Type, mr.id_metrInrep from MetrInRep mr where mr.id_rep in (select r.id_rep from report r where r.progName like '" + listView2.Items[listView2.SelectedIndices[0]].Text + "')");
cmdBuilder = new SqlCommandBuilder(adapter);
dataGridView8_Resize(dataGridView8, null);
UseWaitCursor = false;
}
}
/// <summary>
/// Writes the updates to the Publisher table to the database on disk for a new or updated publisher records.
/// </summary>
private void savePublisher()
{
try
{
DataSet dataSetPublisherUpdate = dataSetPublisher.GetChanges();
if (dataSetPublisherUpdate != null)
{
dataAdapterPublisher.Update(dataSetPublisherUpdate);
dataSetPublisherUpdate.AcceptChanges();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "Update Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
//Function to update the database
public void UpdateDatabase()
{
if (myData.Tables[0].Rows.Count != 0) // check if the dataset has any data into it
{
try
{
conn.Open();
DataSet newDataSet = myData.GetChanges(); // create new dataset that takes any changes occured into the primary dataset
adapter.Update(newDataSet); //adapter takes the new dataset with the new changes and updates the data into the database.
myData.Tables[0].AcceptChanges();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString() + " " + ex.Source.ToString());
}
conn.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
try
{
oledbCmdBuilder = new OleDbCommandBuilder(oledbAdapter);
changes = ds.GetChanges();
if (changes != null)
{
oledbAdapter.Update(ds.Tables[0]);
}
ds.AcceptChanges();
MessageBox.Show("Save changes");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void InsertWithDataSet()
{
var ds = new DataSet();
var da = new NpgsqlDataAdapter("select * from tableb", TheConnection);
da.InsertCommand = new NpgsqlCommand("insert into tableb(field_int2, field_timestamp, field_numeric) values (:a, :b, :c)", TheConnection);
da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));
da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[0].SourceColumn = "field_int2";
da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp";
da.InsertCommand.Parameters[2].SourceColumn = "field_numeric";
da.Fill(ds);
var dt = ds.Tables[0];
var dr = dt.NewRow();
dr["field_int2"] = 4;
dr["field_timestamp"] = new DateTime(2003, 01, 30, 14, 0, 0);
dr["field_numeric"] = 7.3M;
dt.Rows.Add(dr);
var ds2 = ds.GetChanges();
da.Update(ds2);
ds.Merge(ds2);
ds.AcceptChanges();
var dr2 = new NpgsqlCommand("select * from tableb where field_serial > 4", TheConnection).ExecuteReader();
dr2.Read();
Assert.AreEqual(4, dr2[1]);
Assert.AreEqual(7.3000000M, dr2[3]);
dr2.Close();
}
public async Task DataAdapterUpdateReturnValue()
{
using (var conn = await OpenConnectionAsync())
{
await using var _ = await SetupTempTable(conn, out var table);
var ds = new DataSet();
var da = new NpgsqlDataAdapter($"SELECT * FROM {table}", conn);
da.InsertCommand = new NpgsqlCommand($@"INSERT INTO {table} (field_int2, field_timestamp, field_numeric) VALUES (:a, :b, :c)", conn);
da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));
da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[0].SourceColumn = "field_int2";
da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp";
da.InsertCommand.Parameters[2].SourceColumn = "field_numeric";
da.Fill(ds);
var dt = ds.Tables[0];
var dr = dt.NewRow();
dr["field_int2"] = 4;
dr["field_timestamp"] = new DateTime(2003, 01, 30, 14, 0, 0);
dr["field_numeric"] = 7.3M;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["field_int2"] = 4;
dr["field_timestamp"] = new DateTime(2003, 01, 30, 14, 0, 0);
dr["field_numeric"] = 7.3M;
dt.Rows.Add(dr);
var ds2 = ds.GetChanges() !;
var daupdate = da.Update(ds2);
Assert.AreEqual(2, daupdate);
}
}
/// <summary>
/// Upload the specific datatable to table upload_exp_init
/// </summary>
/// <param name="_dt">datatable include field c.t. upload_exp_init</param>
public static void Fn_Upload_Exp_Init(DataTable _dt)
{
using (NpgsqlConnection conn = new NpgsqlConnection(GetConnString()))
{
try
{
conn.Open();
DataSet ds = new DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from upload_exp_init", conn);
da.DeleteCommand = new NpgsqlCommand("delete from upload_exp_init", conn);
da.InsertCommand = new NpgsqlCommand("insert into upload_exp_init values (:c1,:c2,:c3,:c4,:c5,:c6,:c7,:c8,:c9,:c10,:c11,:c12,:c13,:c14)", conn);
string[] paraArray = new string[] { "c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8", "c9", "c10", "c11", "c12", "c13", "c14" };
foreach (var para in paraArray)
{
da.InsertCommand.Parameters.Add(para, NpgsqlDbType.Char);
}
string[] colnames = new string[] { "exp_date", "c_tool_id", "i_tool_id", "purpose", "customer_id", "grade", "type", "emp_id", "remark", "item_id", "item_value", "item_flag", "item_name", "unit" };
for (int i = 0; i < da.InsertCommand.Parameters.Count; i++)
{
da.InsertCommand.Parameters[i].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[i].SourceColumn = colnames[i];
}
//Delete
da.DeleteCommand.ExecuteNonQuery();
da.Fill(ds);
//Insert
DataTable dt = ds.Tables[0];
dt.Merge(_dt);
DataSet d2 = ds.GetChanges();
if (d2 != null)
{
da.Update(d2);
ds.Merge(d2);
}
ds.AcceptChanges();
}
catch (Exception)
{
throw;
}
}
}
public void GetChanges()
{
var ds = new DataSet();
ds.Tables.Add(DataProvider.CreateParentDataTable());
// GetChanges 1
Assert.Equal(null, ds.GetChanges());
DataRow dr = ds.Tables[0].NewRow();
dr[0] = 9;
ds.Tables[0].Rows.Add(dr);
// GetChanges 2
Assert.Equal(true, ds.GetChanges() != null);
// GetChanges 3
Assert.Equal(dr.ItemArray, ds.GetChanges().Tables[0].Rows[0].ItemArray);
}
public void GetChanges_ByDataRowState()
{
var ds = new DataSet();
object[] arrAdded, arrDeleted, arrModified, arrUnchanged;
//object[] arrDetached;
DataRow dr;
ds.Tables.Add(DataProvider.CreateParentDataTable());
// GetChanges 1
Assert.Equal(null, ds.GetChanges());
//make some changes
// can't check detached
// dr = ds.Tables[0].Rows[0];
// arrDetached = dr.ItemArray;
// dr.Delete();
// ds.Tables[0].AcceptChanges();
dr = ds.Tables[0].Rows[1];
arrDeleted = dr.ItemArray;
dr.Delete();
dr = ds.Tables[0].Rows[2];
dr[1] = "NewValue";
arrModified = dr.ItemArray;
dr = ds.Tables[0].Select("", "", DataViewRowState.Unchanged)[0];
arrUnchanged = dr.ItemArray;
dr = ds.Tables[0].NewRow();
dr[0] = 1;
ds.Tables[0].Rows.Add(dr);
arrAdded = dr.ItemArray;
// GetChanges Added
Assert.Equal(arrAdded, ds.GetChanges(DataRowState.Added).Tables[0].Rows[0].ItemArray);
// GetChanges Deleted
dr = ds.GetChanges(DataRowState.Deleted).Tables[0].Rows[0];
object[] tmp = new object[] { dr[0, DataRowVersion.Original], dr[1, DataRowVersion.Original], dr[2, DataRowVersion.Original], dr[3, DataRowVersion.Original], dr[4, DataRowVersion.Original], dr[5, DataRowVersion.Original] };
Assert.Equal(arrDeleted, tmp);
// can't check it
// // GetChanges Detached
// dr = ds.GetChanges(DataRowState.Detached).Tables[0].Rows[0];
// object[] tmp = new object[] {dr[0,DataRowVersion.Original],dr[1,DataRowVersion.Original],dr[2,DataRowVersion.Original]};
// Assert.Equal(arrDetached, tmp);
// GetChanges Modified
Assert.Equal(arrModified, ds.GetChanges(DataRowState.Modified).Tables[0].Rows[0].ItemArray);
// GetChanges Unchanged
Assert.Equal(arrUnchanged, ds.GetChanges(DataRowState.Unchanged).Tables[0].Rows[0].ItemArray);
}
public void GetChanges_Relations_DifferentRowStatesTest()
{
DataSet ds = new DataSet("ds");
DataTable parent = ds.Tables.Add("parent");
DataTable child = ds.Tables.Add("child");
parent.Columns.Add("id", typeof(int));
parent.Columns.Add("name", typeof(string));
child.Columns.Add("id", typeof(int));
child.Columns.Add("parent", typeof(int));
child.Columns.Add("name", typeof(string));
parent.Rows.Add(new object[] { 1, "mono parent 1" });
parent.Rows.Add(new object[] { 2, "mono parent 2" });
parent.Rows.Add(new object[] { 3, "mono parent 3" });
parent.Rows.Add(new object[] { 4, "mono parent 4" });
parent.AcceptChanges();
child.Rows.Add(new object[] { 1, 1, "mono child 1" });
child.Rows.Add(new object[] { 2, 2, "mono child 2" });
child.Rows.Add(new object[] { 3, 3, "mono child 3" });
child.AcceptChanges();
DataRelation relation = ds.Relations.Add("parent_child",
parent.Columns["id"],
child.Columns["parent"]);
// modify the parent and get changes
child.Rows[1]["parent"] = 4;
DataSet changes = ds.GetChanges();
DataRow row = changes.Tables["parent"].Rows[0];
Assert.Equal((int)parent.Rows[3][0], (int)row[0]);
Assert.Equal(1, changes.Tables["parent"].Rows.Count);
ds.RejectChanges();
// delete a child row and get changes.
child.Rows[0].Delete();
changes = ds.GetChanges();
Assert.Equal(changes.Tables.Count, 2);
Assert.Equal(1, changes.Tables["parent"].Rows.Count);
Assert.Equal(1, (int)changes.Tables["parent"].Rows[0][0]);
}