/// <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); }
// サブプロシージャ:テーブルを定義し、DBよりデータを読み込む private void defineAndReadTable() { // DBよりデータを読み込み、DTAATABLEへ格納 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"]; // データグリットビューへDTAATABLEを格納 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); } }
// サブプロシージャ:テーブルを定義し、DBよりデータを読み込む private void defineAndReadTable(string sql) { // DBよりデータを読み込み、DTAATABLEへ格納 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"]; // データグリットビューへDTAATABLEを格納 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; } } } } } }