/// <summary>
/// //call it as below
/// <para></para>
/// <![CDATA[UpdateClosure(query, param, (oConn, oCmd, oAdapter, oCmdBuilder) => ]]>
/// <para></para>
/// <![CDATA[ {]]>
/// <para>//...</para>
/// <![CDATA[ nRes = oAdapter.Update(mDt);]]>
/// <para></para>
/// <![CDATA[ mDt.AcceptChanges();]]>
/// <para></para>
/// <![CDATA[ });]]>
/// <para></para>
/// </summary>
/// <param name="query"></param>
/// <param name="param"></param>
/// <param name="action"></param>
public override void UpdateClosure(String query, Pair param,
Action <DbConnection, DbCommand, DbDataAdapter, DbCommandBuilder> action)
{
var sw = new Stopwatch();
sw.Start();
Settings.FireQueryExecutingEvent(query, CommandType.Text);
try
{
using (var oConn = new NpgsqlConnection(ConnectionString))
{
using (var oCmd = new NpgsqlCommand(query, oConn))
{
if (param != null)
{
AddParam(oCmd, param);
}
using (var oAdapter = new NpgsqlDataAdapter(oCmd))
{
oAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
using (var oCmdBuilder = new NpgsqlCommandBuilder(oAdapter))
{
oCmdBuilder.SetAllValues = true;
oAdapter.InsertCommand = oCmdBuilder.GetInsertCommand();
if (null != oAdapter.InsertCommand)
{
oAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
}
oAdapter.UpdateCommand = oCmdBuilder.GetUpdateCommand();
oAdapter.InsertCommand = oCmdBuilder.GetInsertCommand();
oAdapter.DeleteCommand = oCmdBuilder.GetDeleteCommand();
oCmdBuilder.ConflictOption = ConflictOption.OverwriteChanges;
action(oConn, oCmd, oAdapter, oCmdBuilder);
}
}
}
}
}
catch (NpgsqlException ex)
{
throw new DataAccessException(ex.Message, ex);
}
finally
{
sw.Stop();
Settings.FireQueryExecutedEvent(String.Format("UpdateClosure:End. {0} (s), query:{1}", sw.ElapsedMilliseconds / 1000, query));
}
}
public static NpgsqlDataAdapter CreatePhonesAdapter(
NpgsqlConnection connection, int?page)
{
string query;
if (page != null)
{
query = @"SELECT id, name, os, ram, cpu_cores, storage, camera, gps,
battery, screen_resolution, price, screen_size ,image_url
FROM phones order by id limit 15 offset " + (page - 1) * 15;
}
else
{
query = @"SELECT id, name, os, ram, cpu_cores, storage, camera, gps,
battery, screen_resolution, price, screen_size,image_url
FROM phones order by id";
}
// Create the SelectCommand.
NpgsqlDataAdapter adapter =
new NpgsqlDataAdapter(query, connection);
NpgsqlCommandBuilder command = new NpgsqlCommandBuilder(adapter);
adapter.InsertCommand = command.GetInsertCommand();
adapter.UpdateCommand = command.GetUpdateCommand();
adapter.DeleteCommand = command.GetDeleteCommand();
return(adapter);
}
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 defineAndReadTable()
{
// ＤＢよりデータを読み込み、ＤＴＡＡＴＡＢＬＥへ格納
string sql = "select no, model, process, inspect, description, upper, lower, instrument, clm_set, row_set " +
"from tbl_measure_item where model='" + model + "' order by no, process, inspect";
connection = new NpgsqlConnection(conStringBoxidDb);
connection.Open();
command = new NpgsqlCommand(sql, connection);
adapter = new NpgsqlDataAdapter(command);
cmdbuilder = new NpgsqlCommandBuilder(adapter);
adapter.InsertCommand = cmdbuilder.GetInsertCommand();
adapter.UpdateCommand = cmdbuilder.GetUpdateCommand();
adapter.DeleteCommand = cmdbuilder.GetDeleteCommand();
ds = new DataSet();
adapter.Fill(ds, "buff");
dt = ds.Tables["buff"];
// データグリットビューへＤＴＡＡＴＡＢＬＥを格納
dgvTester.DataSource = dt;
dgvTester.ReadOnly = true;
btnSave.Enabled = false;
dgvTester.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dgvTester.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
}
public PostgresUpdater(Subject <TicksResponse> tickResponses, Subject <IntervalsResponse> intervalResponses)
{
this.tickResponses = tickResponses;
this.intervalResponses = intervalResponses;
this.tickResponses.Subscribe(
(response) => TicksReponseHandler(response),
() => Done()
);
this.intervalResponses.Subscribe(
(response) => IntervalsResponseHandler(response),
() => Done()
);
string connect = $"Server=127.0.0.1;Port={port};User Id={user};Password={pass};Database={dbname}";
postgres = new NpgsqlConnection(connect);
postgres.Open();
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter();
adapter.SelectCommand = new NpgsqlCommand("SELECT * FROM \"historical_ticks\"", postgres);
NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(adapter);
insertTicks = builder.GetInsertCommand(true);
}
/// <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 static void getData(out NpgsqlDataAdapter dataAdapter, string table)
{
//string[] tables = new string[] { "book", "booking", "department", "login", "reader", "issuing_books" };
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
string sql = String.Format("select * from {0};", table);
dataAdapter = new NpgsqlDataAdapter(sql, connection);
//libraryDS.Tables.Add(table);
NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(dataAdapter);
dataAdapter.InsertCommand = builder.GetInsertCommand();
dataAdapter.DeleteCommand = builder.GetDeleteCommand();
dataAdapter.UpdateCommand = builder.GetUpdateCommand();
dataAdapter.Fill(libraryDS, table);
//foreach (var table in tables)
//{
// string sql = String.Format("select * from {0};", table);
// NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(sql, connection);
// adapter.Fill(libraryDS, table);
//}
}
}
/// <summary>Setups the data adapter.</summary>
protected void SetupAdapter()
{
dataAdapter = new NpgsqlDataAdapter("Select * FROM " + tableName + " ORDER BY " + idField, npgsqlConnection);
NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(dataAdapter);
var insertCmd = builder.GetInsertCommand(true);
insertCmd.Connection = npgsqlConnection;
//insertCmd.CommandText = insertCommand;
dataAdapter.InsertCommand = insertCmd;
var updateCmd = builder.GetUpdateCommand(true);
updateCmd.Connection = npgsqlConnection;
//updateCmd.CommandText = updateCommand;
dataAdapter.UpdateCommand = updateCmd;
var deleteCmd = builder.GetDeleteCommand(true);
deleteCmd.Connection = npgsqlConnection;
//deleteCmd.CommandText = "DELETE FROM " + tableName + " WHERE " + idField + " = @pk";
//deleteCmd.Parameters.Add("pk", NpgsqlTypes.NpgsqlDbType.Varchar, 255, sourceColumn: idField);
dataAdapter.DeleteCommand = deleteCmd;
}
/// <summary>
/// 表批量写入（须手动处理自增列SQL）
/// 根据行数据 RowState 状态新增、修改
/// </summary>
/// <param name="dt">数据表（Namespace=SchemaName，TableName=TableName）</param>
/// <param name="sqlEmpty">查询空表脚本，默认*，可选列，会影响数据更新的列</param>
/// <param name="dataAdapter">执行前修改（命令行脚本、超时等信息）</param>
/// <param name="openTransaction">开启事务，默认开启</param>
/// <returns></returns>
public int BulkBatchPostgreSQL(DataTable dt, string sqlEmpty = null, Action <NpgsqlDataAdapter> dataAdapter = null, bool openTransaction = true)
{
return(SafeConn(() =>
{
var connection = (NpgsqlConnection)Connection;
NpgsqlTransaction transaction = openTransaction ? (NpgsqlTransaction)(Transaction = connection.BeginTransaction()) : null;
var cb = new NpgsqlCommandBuilder();
if (string.IsNullOrWhiteSpace(sqlEmpty))
{
var sntn = SqlSNTN(dt.TableName, dt.Namespace, SharedEnum.TypeDB.PostgreSQL);
sqlEmpty = SqlEmpty(sntn);
}
cb.DataAdapter = new NpgsqlDataAdapter
{
SelectCommand = new NpgsqlCommand(sqlEmpty, connection, transaction)
};
cb.ConflictOption = ConflictOption.OverwriteChanges;
var da = new NpgsqlDataAdapter
{
InsertCommand = cb.GetInsertCommand(true),
UpdateCommand = cb.GetUpdateCommand(true)
};
da.InsertCommand.CommandTimeout = 300;
da.UpdateCommand.CommandTimeout = 300;
//处理：无效的 "UTF8" 编码字节顺序: 0x00
var listColName = dt.Columns.Cast <DataColumn>().Where(x => x.DataType == typeof(string)).Select(x => x.ColumnName).ToList();
foreach (DataRow dr in dt.Rows)
{
listColName.ForEach(colName =>
{
var val = dr[colName];
if (val is not DBNull)
{
dr[colName] = val.ToString().Replace("\0", "");
}
});
}
//执行前修改
dataAdapter?.Invoke(da);
var num = da.Update(dt);
transaction?.Commit();
return num;
}));
}
public override DbDataAdapter CreateAdapter(Table table, TableFilter filter)
{
var adapter = new NpgsqlDataAdapter(table.GetBaseSelectCommandText(filter), Connection);
var builder = new NpgsqlCommandBuilder(adapter);
try {
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
} catch {
}
return(adapter);
}
public int SubmitDataSetChanges(NpgsqlDataAdapter m_DataAdapter, DataSet m_DataSet)
{
conn.Open();
NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(m_DataAdapter as NpgsqlDataAdapter);
m_DataAdapter.InsertCommand = cb.GetInsertCommand();
m_DataAdapter.DeleteCommand = cb.GetDeleteCommand();
m_DataAdapter.UpdateCommand = cb.GetUpdateCommand();
DataTable table = m_DataSet.Tables[0];
int response = m_DataAdapter.Update(table);
conn.Close();
return(response);
}
/// <summary>
/// для всех кнопок по которым допускается изменение даннх
/// </summary>
/// <param name="select"></param>
private void all_update_button(string select)
{
allowEdit();
Program.mainForm.da = new NpgsqlDataAdapter(select, Program.mainForm.con);
NpgsqlCommandBuilder npgsqlCommandBuilder = new NpgsqlCommandBuilder(Program.mainForm.da);
Program.mainForm.da.InsertCommand = npgsqlCommandBuilder.GetInsertCommand();
Program.mainForm.da.UpdateCommand = npgsqlCommandBuilder.GetUpdateCommand();
Program.mainForm.da.DeleteCommand = npgsqlCommandBuilder.GetDeleteCommand();
Program.mainForm.ds.Reset();
Program.mainForm.da.Fill(Program.mainForm.ds);
Program.mainForm.dt = Program.mainForm.ds.Tables[0];
dataGridView1.DataSource = Program.mainForm.dt;
}
private bool UpdateRecDym(NpgsqlTransaction sqlTrans, DataTable dt)
{
NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(da);
da.DeleteCommand = builder.GetDeleteCommand();
da.InsertCommand = builder.GetInsertCommand();
da.UpdateCommand = builder.GetUpdateCommand(true);
da.UpdateCommand.CommandText = @"UPDATE prcdtl SET unitprice = @p4, lastuser = @p6 WHERE uid = @p7";
// var aa =da.InsertCommand.Parameters["hdruid"];
if (!UpdateTable(ref dt, sqlTrans))
{
return(false);
}
return(true);
}
private void FillTableAfterAdd()
{
var sqlQuery = "SELECT * FROM users;";
using (NpgsqlCommand command = new NpgsqlCommand(sqlQuery, connection))
{
adapter = new NpgsqlDataAdapter(command);
usersTable.Clear();
adapter.Fill(usersTable);
comandbuilder = new NpgsqlCommandBuilder(adapter);
adapter.InsertCommand = comandbuilder.GetInsertCommand();
adapter.UpdateCommand = comandbuilder.GetUpdateCommand();
adapter.DeleteCommand = comandbuilder.GetDeleteCommand();
usersGrid.ItemsSource = usersTable.DefaultView;
}
}
/// <summary>
/// DataSet ---NpgsqlDataAdapter ---Database
/// </summary>
public NpgsqlDataAdapter GetDataAdapter(string strSQL)
{// Use NpgsqlCommandBuilder and SelectCommand--->Auto generate InsertCommand UpdateCommand DeleteCommand
try
{
m_gCommnd.CommandText = strSQL;
NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter(m_gCommnd);//associate
//InsertCommand UpdateCommand DeleteCommand SelectCommand = m_sqlCommand;
// Initialize the InsertCommand UpdateCommand DeleteCommand of NpgsqlDataAdapter by NpgsqlCommandBuilder.
NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(dataAdapter);
dataAdapter.InsertCommand = cb.GetInsertCommand();
dataAdapter.UpdateCommand = cb.GetUpdateCommand();
dataAdapter.DeleteCommand = cb.GetDeleteCommand();
return(dataAdapter);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
return(null);
}
}
// サブプロシージャ：テーブルを定義し、ＤＢよりデータを読み込む
private void defineAndReadTable(string sql)
{
// ＤＢよりデータを読み込み、ＤＴＡＡＴＡＢＬＥへ格納
connection = new NpgsqlConnection(conStringTrayGuardDb);
connection.Open();
command = new NpgsqlCommand(sql, connection);
adapter = new NpgsqlDataAdapter(command);
cmdbuilder = new NpgsqlCommandBuilder(adapter);
adapter.InsertCommand = cmdbuilder.GetInsertCommand();
adapter.UpdateCommand = cmdbuilder.GetUpdateCommand();
adapter.DeleteCommand = cmdbuilder.GetDeleteCommand();
ds = new DataSet();
adapter.Fill(ds, "buff");
dt = ds.Tables["buff"];
// データグリットビューへＤＴＡＡＴＡＢＬＥを格納
dgvTester.DataSource = dt;
dgvTester.ReadOnly = true;
btnSave.Enabled = false;
dgvTester.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dgvTester.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
}
public override int SaveTable(DataTable dataTable, string sql)
{
//Logger.WriteLine("Save Table with transaction");
//Performance perf = new Performance();
Logger.WriteLine("Saving " + dataTable.TableName);
//Logger.WriteLine(sql);
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
NpgsqlConnection conn = new NpgsqlConnection(ConnectionString);
NpgsqlCommand myAccessCommand = new NpgsqlCommand(sql, conn);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(myAccessCommand);
NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
cb.SetAllValues = SetAllValuesInCommandBuilder;
cb.ConflictOption = ConflictOption.OverwriteChanges; // this fixes System.InvalidCastException : Specified cast is not valid.
// when reserved word (group) was a column name
if (MapToLowerCase)
{
var map = da.TableMappings.Add(dataTable.TableName.ToLower(), dataTable.TableName);
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var cn = dataTable.Columns[i].ColumnName;
map.ColumnMappings.Add(cn.ToLower(), cn);
}
//PrintMapping(da);
}
Logger.WriteLine(sql);
int recordCount = 0;
//da.RowUpdating += myDataAdapter_RowUpdating;
try
{
conn.Open();
var dbTrans = conn.BeginTransaction();
da.Fill(myDataSet, dataTable.TableName);
recordCount = da.Update(dataTable);
dbTrans.Commit();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
string msg = "[" + dataTable.TableName + "] " + recordCount;
Logger.WriteLine(msg, "ui");
Console.WriteLine(msg);
return(recordCount);
}
/// <summary>
/// 表批量写入（排除自增列）
/// https://www.npgsql.org/doc/copy.html
/// </summary>
/// <param name="dt">数据表（Namespace=SchemaName，TableName=TableName）</param>
/// <returns></returns>
public int BulkCopyPostgreSQL(DataTable dt)
{
return(SafeConn(() =>
{
var connection = (NpgsqlConnection)Connection;
//提取表列类型与数据库类型
var cb = new NpgsqlCommandBuilder();
var sntn = SqlSNTN(dt.TableName, dt.Namespace, SharedEnum.TypeDB.PostgreSQL);
cb.DataAdapter = new NpgsqlDataAdapter
{
SelectCommand = new NpgsqlCommand($"select * from {sntn} where 0=1", connection)
};
//获取列类型
var pars = cb.GetInsertCommand(true).Parameters;
var colDbType = new Dictionary <string, NpgsqlDbType>();
foreach (NpgsqlParameter par in pars)
{
colDbType.Add(par.SourceColumn, par.NpgsqlDbType);
}
//获取自增
var dtSchema = new DataTable();
cb.DataAdapter.FillSchema(dtSchema, SchemaType.Source);
var autoIncrCol = dtSchema.Columns.Cast <DataColumn>().Where(x => x.AutoIncrement == true).Select(x => x.ColumnName).ToList();
//自增列是主键时，清空主键属性
var pkCol = dt.PrimaryKey.Select(x => x.ColumnName);
if (autoIncrCol.Any(x => pkCol.Contains(x)))
{
dt.PrimaryKey = null;
}
//排除自增
var columns = dt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToList();
columns = columns.Except(autoIncrCol).ToList();
string copyString = $"COPY {sntn}(\"" + string.Join("\",\"", columns) + "\") FROM STDIN (FORMAT BINARY)";
autoIncrCol.ForEach(x => dt.Columns.Remove(x));
var num = 0;
using (var writer = connection.BeginBinaryImport(copyString))
{
var now = DateTime.Now;
writer.Timeout = now.AddSeconds(3600) - now;
foreach (DataRow dr in dt.Rows)
{
writer.StartRow();
foreach (DataColumn dc in dt.Columns)
{
var val = dr[dc.ColumnName];
if (val is not DBNull)
{
//列对应数据库类型
var dbType = colDbType[dc.ColumnName];
if (dc.DataType.FullName == "System.String")
{
writer.Write(val.ToString().Replace("\0", ""), dbType);
}
else
{
writer.Write(val, dbType);
}
}
else
{
writer.WriteNull();
}
}
}
num = (int)writer.Complete();
}
return num;
}));
}
public void GetUpdateCommandInfersParametersWithNpgsqDbType()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery(@"
CREATE TABLE pg_temp.test (
Cod varchar(5) NOT NULL,
Descr varchar(40),
Data date,
DataOra timestamp,
Intero smallInt NOT NULL,
Decimale money,
Singolo float,
Booleano bit,
Nota varchar(255),
CONSTRAINT PK_test_Cod PRIMARY KEY (Cod)
);
INSERT INTO test VALUES('key1', 'description', '2018-07-03', '2018-07-03 07:02:00', 123, 123.4, 1234.5, B'1', 'note');
");
var daDataAdapter =
new NpgsqlDataAdapter(
"SELECT Cod, Descr, Data, DataOra, Intero, Decimale, Singolo, Booleano, Nota FROM test", conn);
var cbCommandBuilder = new NpgsqlCommandBuilder(daDataAdapter);
var dtTable = new DataTable();
daDataAdapter.InsertCommand = cbCommandBuilder.GetInsertCommand();
daDataAdapter.UpdateCommand = cbCommandBuilder.GetUpdateCommand();
daDataAdapter.DeleteCommand = cbCommandBuilder.GetDeleteCommand();
Assert.That(daDataAdapter.UpdateCommand.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[2].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date));
Assert.That(daDataAdapter.UpdateCommand.Parameters[3].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(daDataAdapter.UpdateCommand.Parameters[4].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[5].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money));
Assert.That(daDataAdapter.UpdateCommand.Parameters[6].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double));
Assert.That(daDataAdapter.UpdateCommand.Parameters[7].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit));
Assert.That(daDataAdapter.UpdateCommand.Parameters[8].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[9].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[11].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[13].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date));
Assert.That(daDataAdapter.UpdateCommand.Parameters[15].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(daDataAdapter.UpdateCommand.Parameters[16].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[18].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money));
Assert.That(daDataAdapter.UpdateCommand.Parameters[20].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double));
Assert.That(daDataAdapter.UpdateCommand.Parameters[22].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit));
Assert.That(daDataAdapter.UpdateCommand.Parameters[24].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
daDataAdapter.Fill(dtTable);
var row = dtTable.Rows[0];
Assert.That(row[0], Is.EqualTo("key1"));
Assert.That(row[1], Is.EqualTo("description"));
Assert.That(row[2], Is.EqualTo(new DateTime(2018, 7, 3)));
Assert.That(row[3], Is.EqualTo(new DateTime(2018, 7, 3, 7, 2, 0)));
Assert.That(row[4], Is.EqualTo(123));
Assert.That(row[5], Is.EqualTo(123.4));
Assert.That(row[6], Is.EqualTo(1234.5));
Assert.That(row[7], Is.EqualTo(true));
Assert.That(row[8], Is.EqualTo("note"));
dtTable.Rows[0]["Singolo"] = 1.1D;
Assert.That(daDataAdapter.Update(dtTable), Is.EqualTo(1));
}
}
//public override int SaveTable(DataTable dataTable, string sql)
//{
// return SaveTable(dataTable, sql, false);
//}
/// <summary>
/// SaveTable1 is SLOW -- not using a transaction.
/// </summary>
/// <param name="dataTable"></param>
/// <param name="sql"></param>
/// <param name="insert"></param>
/// <returns></returns>
private int SaveTable1(DataTable dataTable, string sql, bool insert = false)
{
base.SqlCommands.Add(sql);
//Logger.WriteLine("Saving " + dataTable.TableName + "\n ");
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
NpgsqlConnection myAccessConn = new NpgsqlConnection(ConnectionString);
NpgsqlCommand myAccessCommand = new NpgsqlCommand(sql, myAccessConn);
var da = new NpgsqlDataAdapter(myAccessCommand);
// myDataAdapter.TableMappings.Add(dataTable.TableName.ToLower(), dataTable.TableName);
var cb = new NpgsqlCommandBuilder(da);
cb.ConflictOption = ConflictOption.OverwriteChanges;
da.InsertCommand = (NpgsqlCommand)cb.GetInsertCommand();
//da.RowUpdated += da_RowUpdated;
Logger.WriteLine(da.InsertCommand.CommandText);
if (!insert)
{
try
{
da.UpdateCommand = (NpgsqlCommand)cb.GetUpdateCommand();
}
catch (InvalidOperationException ioe)
{
Console.WriteLine(ioe.Message);
}
}
if (!insert)
{
try
{
da.DeleteCommand = (NpgsqlCommand)cb.GetDeleteCommand();
}
catch (InvalidOperationException ioe)
{
Console.WriteLine(ioe.Message);
}
}
this.lastSqlCeCommand = sql;
SqlCommands.Add(sql);
myAccessConn.Open();
int recordCount = 0;
try
{ // call Fill method only to make things work. (we ignore myDataSet)
da.Fill(myDataSet, dataTable.TableName);
recordCount = da.Update(dataTable);
}
catch (DBConcurrencyException e)
{
throw e;
}
finally
{
myAccessConn.Close();
}
return(recordCount);
}
public async Task GetUpdateCommandInfersParametersWithNpgsqDbType()
{
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,
Descr varchar(40),
Data date,
DataOra timestamp,
Intero smallInt NOT NULL,
Decimale money,
Singolo float,
Booleano bit,
Nota varchar(255),
BigIntArr bigint[],
VarCharArr character varying(20)[],
PRIMARY KEY (Cod)
);
INSERT INTO {table} VALUES('key1', 'description', '2018-07-03', '2018-07-03 07:02:00', 123, 123.4, 1234.5, B'1', 'note');
");
var daDataAdapter =
new NpgsqlDataAdapter(
$"SELECT Cod, Descr, Data, DataOra, Intero, Decimale, Singolo, Booleano, Nota, BigIntArr, VarCharArr FROM {table}", conn);
var cbCommandBuilder = new NpgsqlCommandBuilder(daDataAdapter);
var dtTable = new DataTable();
daDataAdapter.InsertCommand = cbCommandBuilder.GetInsertCommand();
daDataAdapter.UpdateCommand = cbCommandBuilder.GetUpdateCommand();
daDataAdapter.DeleteCommand = cbCommandBuilder.GetDeleteCommand();
Assert.That(daDataAdapter.UpdateCommand.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[2].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date));
Assert.That(daDataAdapter.UpdateCommand.Parameters[3].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(daDataAdapter.UpdateCommand.Parameters[4].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[5].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money));
Assert.That(daDataAdapter.UpdateCommand.Parameters[6].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double));
Assert.That(daDataAdapter.UpdateCommand.Parameters[7].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit));
Assert.That(daDataAdapter.UpdateCommand.Parameters[8].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[9].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Bigint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[10].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[11].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[13].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[15].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date));
Assert.That(daDataAdapter.UpdateCommand.Parameters[17].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(daDataAdapter.UpdateCommand.Parameters[18].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[20].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money));
Assert.That(daDataAdapter.UpdateCommand.Parameters[22].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double));
Assert.That(daDataAdapter.UpdateCommand.Parameters[24].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit));
Assert.That(daDataAdapter.UpdateCommand.Parameters[26].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[28].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Bigint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[30].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Varchar));
daDataAdapter.Fill(dtTable);
var row = dtTable.Rows[0];
Assert.That(row[0], Is.EqualTo("key1"));
Assert.That(row[1], Is.EqualTo("description"));
Assert.That(row[2], Is.EqualTo(new DateTime(2018, 7, 3)));
Assert.That(row[3], Is.EqualTo(new DateTime(2018, 7, 3, 7, 2, 0)));
Assert.That(row[4], Is.EqualTo(123));
Assert.That(row[5], Is.EqualTo(123.4));
Assert.That(row[6], Is.EqualTo(1234.5));
Assert.That(row[7], Is.EqualTo(true));
Assert.That(row[8], Is.EqualTo("note"));
dtTable.Rows[0]["Singolo"] = 1.1D;
Assert.That(daDataAdapter.Update(dtTable), Is.EqualTo(1));
}
}
private DataSet VeriKaydet(DataTable dt, bool LoglansinMi, decimal UKullaniciKey)
{
if (dt.PrimaryKey.Length == 0)
{
throw new Exception(dt.TableName + " tablosuna ait bir \"PrimaryKey\" bulunmamaktadır.");
}
// Eğer Değişiklik yapılan kayıtlar var ise kayıt işlemi yapılır.
if (dt.GetChanges() != null)
{
gLoglansinMi = LoglansinMi;
arrLogSQL = new List <NpgsqlParameter[]>();
Stopwatch oStopwatch = new Stopwatch();
oStopwatch.Start();
try
{
OpenConnection();
NpgsqlDataAdapter daORC = new NpgsqlDataAdapter("SELECT * FROM " + dt.TableName, cnNpgsql);
NpgsqlCommandBuilder cbNpgsql = new NpgsqlCommandBuilder(daORC);
daORC.InsertCommand = cbNpgsql.GetInsertCommand();
// daORC.InsertCommand.CommandText += " returning \"" + dt.PrimaryKey[0].ColumnName + "\" into :TabloID";
daORC.InsertCommand.CommandText += " returning \"" + dt.PrimaryKey[0].ColumnName + "\" ";
// daORC.InsertCommand.Parameters.Add(new NpgsqlParameter(":TabloID", NpgsqlDbType.Integer, ParameterDirection.Output));
daORC.InsertCommand.Parameters[0].Direction = ParameterDirection.Output;
daORC.InsertCommand.Parameters[0].SourceColumn = dt.PrimaryKey[0].ColumnName;
daORC.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
daORC.UpdateCommand = cbNpgsql.GetUpdateCommand();
daORC.DeleteCommand = cbNpgsql.GetDeleteCommand();
daORC.RowUpdated += new NpgsqlRowUpdatedEventHandler(RowUpdated);
daORC.Update(dt);
oStopwatch.Stop();
gSure = oStopwatch.ElapsedMilliseconds;
if (dt.TableName == "LOG_TABLO")
{
LoglanackTablolariTazele();
}
if (LoglansinMi)
{
VeriKaydetLogYaz(UKullaniciKey);
}
}
catch (System.Data.DBConcurrencyException ex)
{
oStopwatch.Stop();
if (dt.Columns.Contains("UKullaniciKey"))
{
VeriKaydetHataYaz(ex, "Tablo:" + dt.TableName + " TK:" + dt.Rows[0][dt.PrimaryKey[0].ColumnName].ToString() + " KK:" + dt.Rows[0]["UKullaniciKey"].ToString());
}
else
{
VeriKaydetHataYaz(ex, "Tablo:" + dt.TableName + " Key:" + dt.Rows[0][dt.PrimaryKey[0].ColumnName].ToString());
}
throw ex;
}
catch (Exception ex)
{
oStopwatch.Stop();
VeriKaydetHataYaz(ex, "Tablo:" + dt.TableName);
throw ex;
}
finally
{
CloseConnection();
}
}
return(dt.DataSet);
}
public void VeriKaydetBatch(DataTable dt, bool LoglansinMi, int BatchSize, decimal UKullaniciKey)
{
if (dt.PrimaryKey.Length == 0)
{
throw new Exception(dt.TableName + " tablosuna ait bir \"PrimaryKey\" bulunmamaktadır.");
}
// Eğer Değişiklik yapılan kayıtlar var ise kayıt işlemi yapılır.
if (dt.GetChanges() != null)
{
Stopwatch oStopwatch = new Stopwatch();
oStopwatch.Start();
gLoglansinMi = LoglansinMi;
arrLogSQL = new List <NpgsqlParameter[]>();
using (NpgsqlConnection cnMSNpgsql = new NpgsqlConnection(sConn))
{
cnMSNpgsql.Open();
using (NpgsqlDataAdapter daORC = new NpgsqlDataAdapter("SELECT * FROM " + dt.TableName, cnMSNpgsql))
{
using (NpgsqlCommandBuilder cbMSNpgsql = new NpgsqlCommandBuilder(daORC))
{
try
{
daORC.InsertCommand = cbMSNpgsql.GetInsertCommand();
daORC.InsertCommand.UpdatedRowSource = System.Data.UpdateRowSource.None;
string strKolonAdlari = "";
string strParametreler = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
strParametreler += ",:p" + (i + 1);
strKolonAdlari += ",\"" + dt.Columns[i].ColumnName + "\"";
}
strParametreler = strParametreler.Remove(0, 1);
strKolonAdlari = strKolonAdlari.Remove(0, 1);
gsbMSInsertCommandText.Append("INSERT INTO " + dt.TableName + "(" + strKolonAdlari + ") VALUES (" + strParametreler + ")");
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
gsbMSKolonAdlari.Append(dt.Columns[i].ColumnName + ",");
}
else
{
gsbMSKolonAdlari.Append(dt.Columns[i].ColumnName);
}
}
daORC.DeleteCommand = cbMSNpgsql.GetDeleteCommand();
daORC.DeleteCommand.UpdatedRowSource = System.Data.UpdateRowSource.None;
gsbMSDeleteCommandText.Append("DELETE FROM " + dt.TableName + " WHERE (");
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i == 0)
{
gsbMSDeleteCommandText.Append("(\"" + dt.Columns[i].ColumnName + "\"=:p" + (i + 1) + ")");
}
else
{
gsbMSDeleteCommandText.Append(" AND (\"" + dt.Columns[i].ColumnName + "\"=:p" + (i + 1) + ")");
}
}
gsbMSDeleteCommandText.Append(")");
daORC.UpdateBatchSize = BatchSize;
daORC.RowUpdating += new NpgsqlRowUpdatingEventHandler(RowUpdating);
daORC.Update(dt);
oStopwatch.Stop();
gSure = oStopwatch.ElapsedMilliseconds;
if (dt.TableName == "LOG_TABLO")
{
LoglanackTablolariTazele();
}
if (LoglansinMi)
{
VeriKaydetLogYaz(UKullaniciKey);
}
}
catch (Exception ex)
{
oStopwatch.Stop();
VeriKaydetHataYaz(ex, "Tablo:" + dt.TableName);
throw ex;
}
}
}
}
}
}
