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