public async Task DataAdapter_update_return_value2() { using var conn = await OpenConnectionAsync(); await using var _ = await SetupTempTable(conn, out var table); var cmd = conn.CreateCommand(); var da = new NpgsqlDataAdapter($"select * from {table}", conn); var cb = new NpgsqlCommandBuilder(da); var ds = new DataSet(); da.Fill(ds); //## Insert a new row with id = 1 ds.Tables[0].Rows.Add(0.4, 0.5); da.Update(ds); //## change id from 1 to 2 cmd.CommandText = $"update {table} set field_float4 = 0.8"; cmd.ExecuteNonQuery(); //## change value to newvalue ds.Tables[0].Rows[0][1] = 0.7; //## update should fail, and make a DBConcurrencyException var count = da.Update(ds); //## count is 1, even if the isn't updated in the database Assert.AreEqual(0, count); }
public void GetUpdateCommand() { using (var conn = OpenConnection()) { Setup(conn); using (var da = new NpgsqlDataAdapter("SELECT field_pk, field_int4 FROM data", conn)) { using (var cb = new NpgsqlCommandBuilder(da)) { var updateCommand = cb.GetUpdateCommand(true); da.UpdateCommand = updateCommand; var ds = new DataSet(); da.Fill(ds); var table = ds.Tables[0]; var row = table.Rows.Add(); row["field_pk"] = 1; row["field_int4"] = 1; da.Update(ds); row["field_int4"] = 2; da.Update(ds); row.Delete(); da.Update(ds); } } } }
public async Task Auto_populate_adapter_commands() { using var conn = await OpenConnectionAsync(); await using var _ = await SetupTempTable(conn, out var table); var da = new NpgsqlDataAdapter($"SELECT field_pk,field_int4 FROM {table}", conn); var builder = new NpgsqlCommandBuilder(da); var ds = new DataSet(); da.Fill(ds); var t = ds.Tables[0]; var row = t.NewRow(); row["field_pk"] = 1; row["field_int4"] = 8; t.Rows.Add(row); da.Update(ds); Assert.That(await conn.ExecuteScalarAsync($"SELECT field_int4 FROM {table}"), Is.EqualTo(8)); row["field_int4"] = 9; da.Update(ds); Assert.That(await conn.ExecuteScalarAsync($"SELECT field_int4 FROM {table}"), Is.EqualTo(9)); row.Delete(); da.Update(ds); Assert.That(await conn.ExecuteScalarAsync($"SELECT COUNT(*) FROM {table}"), Is.EqualTo(0)); }
///// <summary> ///// ///// </summary> ///// <param name="connection_string"></param> ///// <param name="command_text"></param> ///// <returns></returns> //public DataSet SelectDeltaSet(string connection_string, string command_text) //{ // var _result = new DataSet(); // var _dbc = new NpglDatCommand(command_text); // _result = SelectDataSet(connection_string, CommandType.Text, command_text, _dbc.Name); // return _result; //} //----------------------------------------------------------------------------------------------------------------------------- // Update or Insert Delta DataSet //----------------------------------------------------------------------------------------------------------------------------- /// <summary> /// /// </summary> /// <param name="npg_transaction"></param> /// <param name="delta_table"></param> /// <remarks> /// DataSet 내의 Table간의 Foreign Key 관계로 인하여 /// 삭제는 detail -> master순서대로, 추가는 master -> detail 관계로 처리되어야 한다. /// 따라서 삭제부분은 DataTable_Delete()으로 따로 분리한다. /// </remarks> /// <returns></returns> private int DataTable_Update(NpgsqlTransaction npg_transaction, DataTable delta_table) { var _result = 0; using (var _deltacmd = new NpgsqlDataAdapter(_bldDeltaSelSQL(delta_table), npg_transaction.Connection)) { _deltacmd.InsertCommand = new NpgsqlCommand("", npg_transaction.Connection); { _deltacmd.InsertCommand.Transaction = npg_transaction; _bld_DeltaInsParm(delta_table.Columns, _deltacmd.InsertCommand); } _deltacmd.UpdateCommand = new NpgsqlCommand("", npg_transaction.Connection); { _deltacmd.UpdateCommand.Transaction = npg_transaction; _bld_DeltaUpdParm(delta_table.Columns, _deltacmd.UpdateCommand); } _deltacmd.RowUpdating += _RowUpdating; _result += _deltacmd.Update(delta_table.Select(null, null, DataViewRowState.ModifiedCurrent)); _result += _deltacmd.Update(delta_table.Select(null, null, DataViewRowState.Added)); } return(_result); }
public async Task Get_UpdateCommand() { using var conn = await OpenConnectionAsync(); await using var _ = await SetupTempTable(conn, out var table); using var da = new NpgsqlDataAdapter($"SELECT field_pk, field_int4 FROM {table}", conn); using var cb = new NpgsqlCommandBuilder(da); var updateCommand = cb.GetUpdateCommand(true); da.UpdateCommand = updateCommand; var ds = new DataSet(); da.Fill(ds); var t = ds.Tables[0]; var row = t.Rows.Add(); row["field_pk"] = 1; row["field_int4"] = 1; da.Update(ds); row["field_int4"] = 2; da.Update(ds); row.Delete(); da.Update(ds); }
public void DataAdapterUpdateReturnValue2() { using (var conn = OpenConnection()) { var cmd = conn.CreateCommand(); var da = new NpgsqlDataAdapter("select * from tabled", conn); var cb = new NpgsqlCommandBuilder(da); var ds = new DataSet(); da.Fill(ds); //## Insert a new row with id = 1 ds.Tables[0].Rows.Add(new Object[] { 0.4, 0.5 }); da.Update(ds); //## change id from 1 to 2 cmd.CommandText = "update tabled set field_float4 = 0.8"; cmd.ExecuteNonQuery(); //## change value to newvalue ds.Tables[0].Rows[0][1] = 0.7; //## update should fail, and make a DBConcurrencyException var count = da.Update(ds); //## count is 1, even if the isn't updated in the database Assert.AreEqual(0, count); } }
private void updateInfo() { ad_apdrosinasana.Update((DataTable)dataGridViewUserForm.DataSource); ad_auto.Update((DataTable)dataGridViewUserForm.DataSource); ad_firma.Update((DataTable)dataGridViewUserForm.DataSource); ad_godalga.Update((DataTable)dataGridViewUserForm.DataSource); ad_klubs.Update((DataTable)dataGridViewUserForm.DataSource); ad_krasa.Update((DataTable)dataGridViewUserForm.DataSource); ad_modelis.Update((DataTable)dataGridViewUserForm.DataSource); ad_persona.Update((DataTable)dataGridViewUserForm.DataSource); ad_pilseta.Update((DataTable)dataGridViewUserForm.DataSource); ad_tehniska_apskate.Update((DataTable)dataGridViewUserForm.DataSource); NpgsqlTransaction trans = ncon.BeginTransaction(); try { ad_apdrosinasana.Update(dt_apdrosinasana); ad_auto.Update(dt_auto); ad_firma.Update(dt_firma); ad_godalga.Update(dt_godalga); ad_klubs.Update(dt_klubs); ad_krasa.Update(dt_krasa); ad_modelis.Update(dt_modelis); ad_persona.Update(dt_persona); ad_pilseta.Update(dt_pilseta); ad_tehniska_apskate.Update(dt_tehniska_apskate); trans.Commit(); } catch (Exception err) { MessageBox.Show(err.Message); trans.Rollback(); } }
public void AutoPopulateAdapterCommands() { using (var conn = OpenConnection()) { Setup(conn); var da = new NpgsqlDataAdapter("SELECT field_pk,field_int4 FROM data", conn); var builder = new NpgsqlCommandBuilder(da); var ds = new DataSet(); da.Fill(ds); var table = ds.Tables[0]; var row = table.NewRow(); row["field_pk"] = 1; row["field_int4"] = 8; table.Rows.Add(row); da.Update(ds); Assert.That(conn.ExecuteScalar(@"SELECT field_int4 FROM data"), Is.EqualTo(8)); row["field_int4"] = 9; da.Update(ds); Assert.That(conn.ExecuteScalar(@"SELECT field_int4 FROM data"), Is.EqualTo(9)); row.Delete(); da.Update(ds); Assert.That(conn.ExecuteScalar(@"SELECT COUNT(*) FROM data"), Is.EqualTo(0)); } }
public bool PerformDelete(Dictionary <string, string> values) { DataRow rowToDelete; if (ds == null) { PerformSelect(); } else { cn.Open(); } rowToDelete = entity.GetRowDefinedByKey(values, ds.Tables[0]); ds.Tables[0].Rows[ds.Tables[0].Rows.IndexOf(rowToDelete)].Delete(); try { adapter.Update(ds.Tables[0]); } catch (Exception) { return(false); } finally { cn.Close(); } return(true); }
private void btnSalvar_Click(object sender, EventArgs e) { // sem o "SqlCommandBuilder" essa linha irá gerar um erro //passa os dados da tabela para o adapter que se conecta com o banco //e o atualiza data_adapter.Update(data_table); MessageBox.Show("Dados Salvos!"); }
private void btt_del_Click_1(object sender, EventArgs e) { if (MessageBox.Show("Do you want to delete this row ?", "Delete", MessageBoxButtons.YesNo) == DialogResult.Yes) { dgv_lf.Rows.RemoveAt(dgv_lf.SelectedRows[0].Index); adaptador.Update(tabla_det); } }
//Добавить товар public void AddProduct(string name, string description, DateTime releaseDate, int price, long providerId) { try { // Перевод времени в timestamp getDataSet().Tables[tableTwo].Rows.Add(0, name, description, releaseDate, price, providerId); productDataAdapter.Update(getDataSet(), tableTwo); } catch (Exception error) { Console.WriteLine(error); } }
public void GetUpdateCommandWithArrayColumType() { using var conn = OpenConnection(); try { conn.ExecuteNonQuery(@" DROP TABLE IF EXISTS Test; CREATE TABLE Test ( Cod varchar(5) NOT NULL, Vettore character varying(20)[], CONSTRAINT PK_test_Cod PRIMARY KEY (Cod) ) "); using var daDataAdapter = new NpgsqlDataAdapter("SELECT cod, vettore FROM test ORDER By cod", conn); using var cbCommandBuilder = new NpgsqlCommandBuilder(daDataAdapter); var dtTable = new DataTable(); cbCommandBuilder.SetAllValues = true; daDataAdapter.UpdateCommand = cbCommandBuilder.GetUpdateCommand(); daDataAdapter.Fill(dtTable); dtTable.Rows.Add(); dtTable.Rows[0]["cod"] = '0'; dtTable.Rows[0]["vettore"] = new string[] { "aaa", "bbb" }; daDataAdapter.Update(dtTable); } finally { conn.ExecuteNonQuery("DROP TABLE IF EXISTS Test"); } }
public override int BlockCommand(DataTable dt) { int result = 0; lock (lockObject) { try { CheckConn(); using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format("select * from {0}", dt.TableName), conn)) { using (NpgsqlDataAdapter ada = new NpgsqlDataAdapter(cmd)) { using (NpgsqlCommandBuilder scb = new NpgsqlCommandBuilder(ada)) { ada.InsertCommand = scb.GetInsertCommand(); ada.DeleteCommand = scb.GetDeleteCommand(); ada.UpdateCommand = scb.GetUpdateCommand(); result = ada.Update(dt); } } } } catch (Exception e) { All.Class.Error.Add(e);//数据库中一定要有主键,不然当前方法会出错。即没有办法生成删除命令 } } return(result); }
private void button5_Click(object sender, EventArgs e) { DtServer = pg_Connect.connect_database(); string connstring = DtServer.dt_connection; bool conn_True = DtServer.fileExist; if (conn_True) { try { connection = new NpgsqlConnection(connstring); command = new NpgsqlCommand("SELECT * from public.student", connection); NpgsqlDataAdapter NpgsqlDA = new NpgsqlDataAdapter(); NpgsqlDA.SelectCommand = command; DataTable dbdataset = new DataTable(); NpgsqlDA.Fill(dbdataset); BindingSource bsource = new BindingSource(); bsource.DataSource = dbdataset; dataGridView1.DataSource = bsource; NpgsqlDA.Update(dbdataset); dataGridView1.AllowUserToAddRows = false; data_load_from_excel_file = false; } catch (Exception msg) { MessageBox.Show("You can't connect with database!Please chek data connections saved in the file and try again! " + "Server=127.0.0.1; Port=5432; User Id=postgres; Password=b2b4cc1b2; Database=DataStudent;"); // MessageBox.Show(msg.Message); } } else { MessageBox.Show("Connection to dataBase has Failed Because File with data connections not Exist or name of the file has changed!"); } }
public async Task Get_update_command_with_array_column_type() { using var conn = await OpenConnectionAsync(); await using var _ = await GetTempTableName(conn, out var table); await conn.ExecuteNonQueryAsync($@" CREATE TABLE {table} ( Cod varchar(5) NOT NULL, Vettore character varying(20)[], CONSTRAINT PK_test_Cod PRIMARY KEY (Cod) )"); using var daDataAdapter = new NpgsqlDataAdapter($"SELECT cod, vettore FROM {table} ORDER By cod", conn); using var cbCommandBuilder = new NpgsqlCommandBuilder(daDataAdapter); var dtTable = new DataTable(); cbCommandBuilder.SetAllValues = true; daDataAdapter.UpdateCommand = cbCommandBuilder.GetUpdateCommand(); daDataAdapter.Fill(dtTable); dtTable.Rows.Add(); dtTable.Rows[0]["cod"] = '0'; dtTable.Rows[0]["vettore"] = new[] { "aaa", "bbb" }; daDataAdapter.Update(dtTable); }
public void Update(Goal goal) { using var connection = new NpgsqlConnection(connStr); connection.Open(); using var dataAdapter = new NpgsqlDataAdapter(); dataAdapter.SelectCommand = new NpgsqlCommand("SELECT * FROM smartsaver.goal;", connection); var commandBuilder = new NpgsqlCommandBuilder(dataAdapter); var dataTable = new DataTable(); dataAdapter.Fill(dataTable); foreach (DataRow row in dataTable.Rows) { if ((long)row["Id"] == goal.Id) { row["Title"] = goal.Title; row["Amount"] = goal.Amount; row["Description"] = goal.Description; row["Deadlinedate"] = goal.Deadlinedate; } } dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(); dataAdapter.Update(dataTable); }
private bool UpdateTable(ref DataTable tablename, NpgsqlTransaction sqlTrans) { da.UpdateCommand.Connection = con; da.InsertCommand.Connection = con; da.DeleteCommand.Connection = con; da.InsertCommand.Transaction = sqlTrans; da.UpdateCommand.Transaction = sqlTrans; da.DeleteCommand.Transaction = sqlTrans; try { da.Update(tablename); da.UpdateCommand.Parameters.Clear(); } catch (Exception ex) { //CCommon.CreateMessageAlert(this, "Error saving data, please contact your system admistrator! " + CCommon.ConvertToHTMLString(ex.Message), "CONNECTION_EXCEPTION"); // ItemGridEntry.GetGridView.JSProperties["cpErrMsg"] = "Error saving data, please contact your system admistrator! " + ex.Message; _err = ex.Message; Console.WriteLine(ex); return(false); } return(true); }
private void removeButton_Click(object sender, EventArgs e) { DialogResult result = MessageBox.Show("削除しますか?", "確認", MessageBoxButtons.YesNo); if (result == DialogResult.Yes) { NpgsqlConnection conn = new NpgsqlConnection(); NpgsqlDataAdapter adapter; NpgsqlCommandBuilder builder; conn.ConnectionString = @"Server = 192.168.152.43; Port = 5432; User Id = postgres; Password = postgres; Database = master;"; //変更予定 string sql_str = "delete from main_category_m where main_category_code = " + mainCode + ""; conn.Open(); adapter = new NpgsqlDataAdapter(sql_str, conn); builder = new NpgsqlCommandBuilder(adapter); adapter.Fill(dt); adapter.Update(dt); MessageBox.Show("削除完了"); conn.Close(); MainCategoryMaster mainCategory = new MainCategoryMaster(master); this.Close(); master.Show(); } else { } }
private void updateButton_Click(object sender, EventArgs e) { DialogResult result = MessageBox.Show("更新をしますか?", "確認", MessageBoxButtons.YesNo); if (result == DialogResult.Yes) { NpgsqlConnection conn = new NpgsqlConnection(); NpgsqlDataAdapter adapter; NpgsqlCommandBuilder builder; DataTable dt = new DataTable(); string mainName = mainCategoryNameTextBox.Text; conn.ConnectionString = @"Server = 192.168.152.43; Port = 5432; User Id = postgres; Password = postgres; Database = master;"; //変更予定 conn.Open(); string sql_str = "update main_category_m set main_category_name = '" + mainName + "' where main_category_code = " + mainCode + ";"; adapter = new NpgsqlDataAdapter(sql_str, conn); builder = new NpgsqlCommandBuilder(adapter); adapter.Fill(dt); adapter.Update(dt); MessageBox.Show("更新完了"); conn.Close(); MainCategoryMaster mainCategory = new MainCategoryMaster(master); this.Close(); mainCategory.Show(); } else { } }
public static void Main(String[] args) { NpgsqlConnection conn = new NpgsqlConnection("Server=192.168.0.3;Port=5432;User Id=rick;Password=password;Database=bpfinal;"); try { conn.Open(); DataSet ds = new DataSet(); NpgsqlDataAdapter da = new NpgsqlDataAdapter("SELECT description, cost_price, sell_price FROM item", conn); da.InsertCommand = new NpgsqlCommand("INSERT INTO item(description, cost_price, sell_price) VALUES(:de, :cp, :sp)", conn); da.InsertCommand.Parameters.Add(new NpgsqlParameter(":de", DbType.String)); da.InsertCommand.Parameters.Add(new NpgsqlParameter(":cp", DbType.Double)); da.InsertCommand.Parameters.Add(new NpgsqlParameter(":sp", DbType.Double)); da.InsertCommand.Parameters[0].SourceColumn = "description"; da.InsertCommand.Parameters[1].SourceColumn = "cost_price"; da.InsertCommand.Parameters[2].SourceColumn = "sell_price"; da.Fill(ds); DataTable dt = ds.Tables[0]; DataRow dr = dt.NewRow(); dr["description"] = "Large Penguin"; dr["cost_price"] = 7.23; dr["sell_price"] = 9.99; dt.Rows.Add(dr); da.Update(ds); } finally { conn.Close(); } }
public DataTable UpdateDataTable(DataTable table, string target, string search) { lock (objLock) { using (NpgsqlConnection _connectionInternal = new NpgsqlConnection(ConnectString)) { if (_connectionInternal.State != ConnectionState.Open) { _connectionInternal.Open(); } string SelectString = @"SELECT " + (target == null ? "*" : target) + " FROM " + table.TableName + (search == null ? "" : " WHERE " + search) + " LIMIT 1"; NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(); adapter.SelectCommand = new NpgsqlCommand(SelectString, _connectionInternal); adapter.SelectCommand.CommandTimeout = 0 * 60 * 15; adapter.UpdateBatchSize = 0; adapter.Update(table.DataSet, table.TableName); table.AcceptChanges(); _connectionInternal.Close(); return(table); } } }
public async Task DoUpdateWithDataSet() { using (var conn = await OpenConnectionAsync()) { await using var _ = await SetupTempTable(conn, out var table); var command = new NpgsqlCommand($"insert into {table} (field_int2) values (2)", conn); command.ExecuteNonQuery(); var ds = new DataSet(); var da = new NpgsqlDataAdapter($"select * from {table}", conn); var cb = new NpgsqlCommandBuilder(da); Assert.IsNotNull(cb); 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 {table}", conn).ExecuteReader()) { dr2.Read(); Assert.AreEqual(4, dr2["field_int2"]); } } }
public virtual void DoUpdateWithDataSet() { using (var conn = OpenConnection()) { var command = new NpgsqlCommand("insert into tableb(field_int2) values (2)", conn); command.ExecuteNonQuery(); var ds = new DataSet(); var da = new NpgsqlDataAdapter("select * from tableb", conn); var cb = new NpgsqlCommandBuilder(da); Assert.IsNotNull(cb); 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", conn).ExecuteReader()) { dr2.Read(); Assert.AreEqual(4, dr2["field_int2"]); } } }
public void WriteData(AbstractTransaction aTransaction, AbstractConnection aConnection, TouristDataSet dataSet) { NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter { UpdateCommand = new NpgsqlCommand("update sight set sight_name=:name, sight_descr=:descr where id=:id"), InsertCommand = new NpgsqlCommand("insert into sight (sight_name, sight_descr) values (:name, :descr)"), DeleteCommand = new NpgsqlCommand("delete from sight where id=:id") }; dataAdapter.UpdateCommand.Connection = aConnection.connection; dataAdapter.UpdateCommand.Transaction = aTransaction.transaction; dataAdapter.InsertCommand.Connection = aConnection.connection; dataAdapter.InsertCommand.Transaction = aTransaction.transaction; dataAdapter.DeleteCommand.Connection = aConnection.connection; dataAdapter.DeleteCommand.Transaction = aTransaction.transaction; NpgsqlParameter paramIdU = new NpgsqlParameter { SourceColumn = "id", ParameterName = ":id" }; NpgsqlParameter paramIdD = new NpgsqlParameter { SourceColumn = "id", ParameterName = ":id" }; dataAdapter.UpdateCommand.Parameters.Add(paramIdU); dataAdapter.DeleteCommand.Parameters.Add(paramIdD); NpgsqlParameter paramNameU = new NpgsqlParameter { SourceColumn = "sight_name", ParameterName = ":name" }; NpgsqlParameter paramNameI = new NpgsqlParameter { SourceColumn = "sight_name", ParameterName = ":name" }; dataAdapter.UpdateCommand.Parameters.Add(paramNameU); dataAdapter.InsertCommand.Parameters.Add(paramNameI); NpgsqlParameter paramDescrU = new NpgsqlParameter { SourceColumn = "sight_descr", ParameterName = ":descr" }; NpgsqlParameter paramDescrI = new NpgsqlParameter { SourceColumn = "sight_descr", ParameterName = ":descr" }; dataAdapter.UpdateCommand.Parameters.Add(paramDescrU); dataAdapter.InsertCommand.Parameters.Add(paramDescrI); dataAdapter.Update(dataSet, "sight"); }
public virtual void DoInsertWithCommandBuilderCaseSensitive() { using (var conn = OpenConnection()) { var ds = new DataSet(); var da = new NpgsqlDataAdapter("select * from tablei", conn); var builder = new NpgsqlCommandBuilder(da); Assert.IsNotNull(builder); da.Fill(ds); var dt = ds.Tables[0]; var dr = dt.NewRow(); dr["Field_Case_Sensitive"] = 4; dt.Rows.Add(dr); var ds2 = ds.GetChanges(); da.Update(ds2); ds.Merge(ds2); ds.AcceptChanges(); using (var dr2 = new NpgsqlCommand("select * from tablei", conn).ExecuteReader()) { dr2.Read(); Assert.AreEqual(4, dr2[1]); } } }
private void addButton_Click(object sender, EventArgs e) { DialogResult result = MessageBox.Show("登録をしますか?", "確認", MessageBoxButtons.YesNo); if (result == DialogResult.Yes) { NpgsqlCommandBuilder builder; conn.ConnectionString = @"Server = localhost; Port = 5432; User Id = postgres; Password = postgres; Database = master;"; //変更予定 int mainCategoryCode = (int)this.meinCategoryNameComboBox.SelectedValue; string subName = this.subCategoryNameTextBox.Text; int subCode = int.Parse(this.subCategoryCodeTextBox.Text); string sql_str = "insert into sub_category_m values(" + mainCategoryCode + ", " + subCode + ",'" + subName + "');"; conn.Open(); adapter = new NpgsqlDataAdapter(sql_str, conn); builder = new NpgsqlCommandBuilder(adapter); adapter.Fill(dt); adapter.Update(dt); conn.Close(); MessageBox.Show("登録完了"); SubCategoryMaster subCategory = new SubCategoryMaster(master); this.Close(); subCategory.Show(); } else { } }
/// <summary> /// 批量插入数据。 /// </summary> /// <param name="dataTable">数据表</param> /// <param name="tableName">要插入的数据表名称</param> /// <param name="connKey">连接配置名称</param> public override void BatchInsert(DataTable dataTable, string tableName, string connKey = null) { var sql = string.Empty; try { this.Open(connKey); this.Command.CommandType = CommandType.Text; this.Command.CommandText = "SELECT * FROM " + tableName; var dt = dataTable.Copy(); foreach (DataRow dr in dt.Rows) { if (dr.RowState == DataRowState.Unchanged) { dr.SetAdded(); } } this.Command.Transaction = this.Connection.BeginTransaction(); var da = new NpgsqlDataAdapter((NpgsqlCommand)this.Command); var cb = new NpgsqlCommandBuilder(da); da.InsertCommand = cb.GetInsertCommand(); da.Update(dt); this.Command.Transaction.Commit(); } catch (Exception ex) { throw new DataObjectException("批量插入数据时出现错误:" + ex.Message + "\r\n" + sql, ex); } }
public async Task Insert_with_CommandBuilder_case_sensitive() { 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); var builder = new NpgsqlCommandBuilder(da); Assert.IsNotNull(builder); da.Fill(ds); var dt = ds.Tables[0]; var dr = dt.NewRow(); dr["Field_Case_Sensitive"] = 4; dt.Rows.Add(dr); var ds2 = ds.GetChanges() !; da.Update(ds2); ds.Merge(ds2); ds.AcceptChanges(); using var dr2 = new NpgsqlCommand($"select * from {table}", conn).ExecuteReader(); dr2.Read(); Assert.AreEqual(4, dr2[1]); }