private void BtnSaveChanges_Click(object sender, EventArgs e) { if (dataGridView1.Rows.Count == 0) { MessageBox.Show("Нечего сохранять!", "0", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } try { NpgsqlCommandBuilder commandBuilder = new NpgsqlCommandBuilder(da); da.UpdateCommand = commandBuilder.GetUpdateCommand(); //da.Update(ds); da.Update(dt); MessageBox.Show("Успешно.", "Хорошо", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("Ошибка: " + ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public void DeriveFunctionParameters_QuoteCharactersInFunctionName() { using (var conn = OpenConnection()) { conn.ExecuteNonQuery( @"CREATE OR REPLACE FUNCTION pg_temp.""""""FunctionQuote""""CharactersInName""""""(int4, text) returns int4 as $BODY$ begin return 0; end $BODY$ language 'plpgsql';"); var command = new NpgsqlCommand("pg_temp.\"\"\"FunctionQuote\"\"CharactersInName\"\"\"", conn) { CommandType = CommandType.StoredProcedure }; NpgsqlCommandBuilder.DeriveParameters(command); Assert.AreEqual(NpgsqlDbType.Integer, command.Parameters[0].NpgsqlDbType); Assert.AreEqual(NpgsqlDbType.Text, command.Parameters[1].NpgsqlDbType); } }
public void DeriveParametersInOnly() { // This function returns record because of the two Out (InOut & Out) parameters ExecuteNonQuery(@"CREATE OR REPLACE FUNCTION ""func""(IN param1 INT, IN param2 INT) RETURNS int AS ' BEGIN RETURN param1 + param2; END; ' LANGUAGE 'plpgsql';"); var cmd = new NpgsqlCommand("func", Conn); cmd.CommandType = CommandType.StoredProcedure; NpgsqlCommandBuilder.DeriveParameters(cmd); Assert.That(cmd.Parameters, Has.Count.EqualTo(2)); Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input)); Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Input)); cmd.Parameters[0].Value = 5; cmd.Parameters[1].Value = 4; Assert.That(cmd.ExecuteScalar(), Is.EqualTo(9)); }
public async Task GetUpdateCommandWithColumnAliases() { using var conn = await OpenConnectionAsync(); await using var _ = await GetTempTableName(conn, out var table); await conn.ExecuteNonQueryAsync($@" CREATE TEMP TABLE {table} ( Cod varchar(5) NOT NULL, Descr varchar(40), Data date, CONSTRAINT PK_test_Cod PRIMARY KEY (Cod) )"); using var cmd = new NpgsqlCommand($"SELECT Cod as CodAlias, Descr as DescrAlias, Data as DataAlias FROM {table}", conn); using var daDataAdapter = new NpgsqlDataAdapter(cmd); using var cbCommandBuilder = new NpgsqlCommandBuilder(daDataAdapter); daDataAdapter.UpdateCommand = cbCommandBuilder.GetUpdateCommand(); Assert.True(daDataAdapter.UpdateCommand.CommandText.Contains("SET \"cod\" = @p1, \"descr\" = @p2, \"data\" = @p3 WHERE ((\"cod\" = @p4) AND ((@p5 = 1 AND \"descr\" IS NULL) OR (\"descr\" = @p6)) AND ((@p7 = 1 AND \"data\" IS NULL) OR (\"data\" = @p8)))")); }
/// <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 btnCommit_Click(object sender, EventArgs e) { // 어댑터를 사용한 DB 업데이트 // CommandBuilder를 생성 // 커맨드 빌더가 데이터셋 안의 테이블 상태를 체크하여 자동으로 쿼리문 생성(Update,Delete, Insert) ////// 중요!! /// 데이터셋을 db에 반영하는 것 : CommandBuilder NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(adapter); // Commit changes back to the data source try { adapter.Update(table); // = adapter.Update(dataset, "Bicycle"); } catch (NpgsqlException ex) { MessageBox.Show(ex.Message, "Error(s) Commiting Changes"); } DisplayData(); }
public async Task DeriveTextCommandParameters_Domain() { using var conn = await OpenConnectionAsync(); MinimumPgVersion(conn, "11.0", "Arrays of domains and domains over arrays were introduced in PostgreSQL 11"); await conn.ExecuteNonQueryAsync("CREATE DOMAIN posint AS integer CHECK (VALUE > 0);" + "CREATE DOMAIN int_array AS int[] CHECK(array_length(VALUE, 1) = 2);"); conn.ReloadTypes(); await using var _ = DeferAsync(async() => { await conn.ExecuteNonQueryAsync("DROP DOMAIN int_array; DROP DOMAIN posint"); conn.ReloadTypes(); }); var cmd = new NpgsqlCommand("SELECT :a::posint, :b::posint[], :c::int_array", conn); var val = 23; var arrayVal = new[] { 7, 42 }; NpgsqlCommandBuilder.DeriveParameters(cmd); Assert.That(cmd.Parameters, Has.Count.EqualTo(3)); Assert.That(cmd.Parameters[0].ParameterName, Is.EqualTo("a")); Assert.That(cmd.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer)); Assert.That(cmd.Parameters[0].DataTypeName, Does.EndWith("posint")); Assert.That(cmd.Parameters[1].ParameterName, Is.EqualTo("b")); Assert.That(cmd.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer | NpgsqlDbType.Array)); Assert.That(cmd.Parameters[1].DataTypeName, Does.EndWith("posint[]")); Assert.That(cmd.Parameters[2].ParameterName, Is.EqualTo("c")); Assert.That(cmd.Parameters[2].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer | NpgsqlDbType.Array)); Assert.That(cmd.Parameters[2].DataTypeName, Does.EndWith("int_array")); cmd.Parameters[0].Value = val; cmd.Parameters[1].Value = arrayVal; cmd.Parameters[2].Value = arrayVal; using var reader = await cmd.ExecuteReaderAsync(); reader.Read(); Assert.That(reader.GetFieldValue <int>(0), Is.EqualTo(val)); Assert.That(reader.GetFieldValue <int[]>(1), Is.EqualTo(arrayVal)); Assert.That(reader.GetFieldValue <int[]>(2), Is.EqualTo(arrayVal)); }
public async Task DeriveParameters_text_domain() { using var conn = await OpenConnectionAsync(); MinimumPgVersion(conn, "11.0", "Arrays of domains and domains over arrays were introduced in PostgreSQL 11"); await using var _ = await GetTempTypeName(conn, out var domainType); await using var __ = await GetTempTypeName(conn, out var domainArrayType); await conn.ExecuteNonQueryAsync($@" CREATE DOMAIN {domainType} AS integer CHECK (VALUE > 0); CREATE DOMAIN {domainArrayType} AS int[] CHECK(array_length(VALUE, 1) = 2);"); conn.ReloadTypes(); var cmd = new NpgsqlCommand($"SELECT :a::{domainType}, :b::{domainType}[], :c::{domainArrayType}", conn); var val = 23; var arrayVal = new[] { 7, 42 }; NpgsqlCommandBuilder.DeriveParameters(cmd); Assert.That(cmd.Parameters, Has.Count.EqualTo(3)); Assert.That(cmd.Parameters[0].ParameterName, Is.EqualTo("a")); Assert.That(cmd.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer)); Assert.That(cmd.Parameters[0].DataTypeName, Does.EndWith(domainType)); Assert.That(cmd.Parameters[1].ParameterName, Is.EqualTo("b")); Assert.That(cmd.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer | NpgsqlDbType.Array)); Assert.That(cmd.Parameters[1].DataTypeName, Does.EndWith(domainType + "[]")); Assert.That(cmd.Parameters[2].ParameterName, Is.EqualTo("c")); Assert.That(cmd.Parameters[2].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer | NpgsqlDbType.Array)); Assert.That(cmd.Parameters[2].DataTypeName, Does.EndWith(domainArrayType)); cmd.Parameters[0].Value = val; cmd.Parameters[1].Value = arrayVal; cmd.Parameters[2].Value = arrayVal; using var reader = await cmd.ExecuteReaderAsync(); reader.Read(); Assert.That(reader.GetFieldValue <int>(0), Is.EqualTo(val)); Assert.That(reader.GetFieldValue <int[]>(1), Is.EqualTo(arrayVal)); Assert.That(reader.GetFieldValue <int[]>(2), Is.EqualTo(arrayVal)); }
public void Update(AbstractConnection connection, AbstractTransaction transaction, UPrintDataSet dataSet) { adapter = new NpgsqlDataAdapter(); adapter.SelectCommand = new NpgsqlCommand(selection, connection.GetConnection(), transaction.GetTransaction()); adapter.Fill(dataSet, "model"); adapter.InsertCommand = new NpgsqlCommand(insertion, connection.GetConnection(), transaction.GetTransaction()); adapter.InsertCommand.Parameters.Add(new NpgsqlParameter() { SourceColumn = "id", ParameterName = "@id" }); adapter.InsertCommand.Parameters.Add(new NpgsqlParameter() { SourceColumn = "name", ParameterName = "@name" }); adapter.InsertCommand.Parameters.Add(new NpgsqlParameter() { SourceColumn = "path", ParameterName = "@path" }); adapter.UpdateCommand = new NpgsqlCommand(updation, connection.GetConnection(), transaction.GetTransaction()); adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter() { SourceColumn = "id", ParameterName = "@id" }); adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter() { SourceColumn = "name", ParameterName = "@name" }); adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter() { SourceColumn = "path", ParameterName = "@path" }); adapter.DeleteCommand = new NpgsqlCommand(deletion, connection.GetConnection(), transaction.GetTransaction()); adapter.DeleteCommand.Parameters.Add(new NpgsqlParameter() { SourceColumn = "id", ParameterName = "@id" }); NpgsqlCommandBuilder commandBuilder = new NpgsqlCommandBuilder(adapter); adapter.Update(dataSet, "model"); }
private void removeButton_Click(object sender, EventArgs e)//削除ボタン { 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 staff_m where staff_code = " + staffCode + ""; conn.Open(); rePassword = this.passwordReText.Text; //変更予定 if (password == rePassword) { adapter = new NpgsqlDataAdapter(sql_str, conn); builder = new NpgsqlCommandBuilder(adapter); adapter.Fill(dt); adapter.Update(dt); MessageBox.Show("削除完了"); } else { MessageBox.Show("削除失敗"); } conn.Close(); StaffMaster staffMaster = new StaffMaster(master); this.Close(); staffMaster.Show(); }
public static DataSet UpdateDB(string sql, IDataParameter[] aParam, DataSet inputDs) { DataSet outputDs = new DataSet(); int i = 1; int numFilas; NpgsqlConnection pgCn = new NpgsqlConnection(GetPGSQLCnString()); try { pgCn.Open(); NpgsqlCommand pgCmd = new NpgsqlCommand(sql, pgCn); if (aParam != null) { foreach (IDataParameter param in aParam) { pgCmd.Parameters.Add(new NpgsqlParameter(string.Format("param{0}", i.ToString()), param)); i += 1; } } NpgsqlDataAdapter pgAdaptador = new NpgsqlDataAdapter(pgCmd); NpgsqlCommandBuilder pgCB = new NpgsqlCommandBuilder(pgAdaptador); numFilas = pgAdaptador.Update(inputDs); FillInfoDataSet(outputDs, string.Format("El comando se completó con {0} fila/s afectada/s", numFilas.ToString())); } catch (NpgsqlException e) { FillExceptionDataSet(outputDs, e.ToString()); } finally { if (pgCn.State != 0) { pgCn.Close(); } } return(outputDs); }
public void UpdateWithDataSet() { NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_int2) values (2)", _conn); command.ExecuteNonQuery(); DataSet ds = new DataSet(); NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tableb where field_serial = (select max(field_serial) from tableb)", _conn); NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(da); Assert.IsNotNull(cb); da.Fill(ds); DataTable dt = ds.Tables[0]; Assert.IsNotNull(dt); DataRow dr = ds.Tables[0].Rows[ds.Tables[0].Rows.Count - 1]; dr["field_int2"] = 4; DataSet ds2 = ds.GetChanges(); da.Update(ds2); ds.Merge(ds2); ds.AcceptChanges(); NpgsqlDataReader dr2 = new NpgsqlCommand("select * from tableb where field_serial = (select max(field_serial) from tableb)", _conn).ExecuteReader(); dr2.Read(); Assert.AreEqual(4, dr2["field_int2"]); }
private void FillTable() { try { connection = new NpgsqlConnection(connectionString); var sqlQuery = "SELECT * FROM volunteers;"; using (NpgsqlCommand command = new NpgsqlCommand(sqlQuery, connection)) { adapter = new NpgsqlDataAdapter(command); usersTable = new DataTable(); connection.Open(); adapter.Fill(usersTable); comandbuilder = new NpgsqlCommandBuilder(adapter); usersGrid.ItemsSource = usersTable.DefaultView; } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void DeriveParametersNoParams() { using (var conn = OpenConnection()) { // This function returns record because of the two Out (InOut & Out) parameters conn.ExecuteNonQuery(@" CREATE OR REPLACE FUNCTION pg_temp.func() RETURNS int AS ' BEGIN RETURN 4; END; ' LANGUAGE 'plpgsql'; "); var cmd = new NpgsqlCommand("pg_temp.func", conn) { CommandType = CommandType.StoredProcedure }; NpgsqlCommandBuilder.DeriveParameters(cmd); Assert.That(cmd.Parameters, Is.Empty); } }
public void Update(DataTable table) { if (table.Rows.Count == 0) { return; } var builder = new CommandBuilder("SELECT {} FROM {}"); foreach (var column_names in updated_columns.Values) { builder.Insert(0, column_names.Name); } builder.Insert(1, Name); using (var Connection = new NpgsqlConnection(Database.ConnectionStr)) { using (var adapter = new NpgsqlDataAdapter(builder.GetCommand(), Connection)) using (var cmd_builder = new NpgsqlCommandBuilder(adapter)) { adapter.Update(table); } } }
public async Task DeriveFunctionParameters_FunctionReturningSetofRecord() { using (var conn = await OpenConnectionAsync()) { MinimumPgVersion(conn, "9.2.0"); await using var _ = await GetTempTableName(conn, out var table); await using var __ = GetTempFunctionName(conn, out var function); // This function returns record because of the two Out (InOut & Out) parameters await conn.ExecuteNonQueryAsync($@" CREATE TABLE {table} (fooid int, foosubid int, fooname text); INSERT INTO {table} VALUES (1, 1, 'Joe'), (1, 2, 'Ed'), (2, 1, 'Mary'); CREATE FUNCTION {function}(int, OUT fooid int, OUT foosubid int, OUT fooname text) RETURNS SETOF record AS $$ SELECT * FROM {table} WHERE {table}.fooid = $1 ORDER BY {table}.foosubid; $$ LANGUAGE SQL; "); var cmd = new NpgsqlCommand(function, conn) { CommandType = CommandType.StoredProcedure }; NpgsqlCommandBuilder.DeriveParameters(cmd); Assert.That(cmd.Parameters, Has.Count.EqualTo(4)); Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input)); Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Output)); Assert.That(cmd.Parameters[2].Direction, Is.EqualTo(ParameterDirection.Output)); Assert.That(cmd.Parameters[3].Direction, Is.EqualTo(ParameterDirection.Output)); cmd.Parameters[0].Value = 1; cmd.ExecuteNonQuery(); Assert.That(cmd.Parameters[0].Value, Is.EqualTo(1)); } }
// サブプロシージャ:テーブルを定義し、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 async Task DeriveTextCommandParameters_OneParameterWithSameType() { using (var conn = await OpenConnectionAsync()) { await using var _ = await CreateTempTable(conn, "id int, val text", out var table); var cmd = new NpgsqlCommand( $@"INSERT INTO {table} VALUES(:x, 'some value'); UPDATE {table} SET val = 'changed value' WHERE id = :x; SELECT val FROM {table} WHERE id = :x;", conn); NpgsqlCommandBuilder.DeriveParameters(cmd); Assert.That(cmd.Parameters, Has.Count.EqualTo(1)); Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input)); Assert.That(cmd.Parameters[0].ParameterName, Is.EqualTo("x")); Assert.That(cmd.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer)); cmd.Parameters[0].Value = 42; var retVal = await cmd.ExecuteScalarAsync(); Assert.That(retVal, Is.EqualTo("changed value")); } }
/// <summary> /// Calls the CommandBuilder.DeriveParameters method for the specified provider, doing any setup and cleanup necessary /// </summary> /// <param name="cmd">The IDbCommand referencing the stored procedure from which the parameter information is to be derived. The derived parameters are added to the Parameters collection of the IDbCommand. </param> public override void DeriveParameters(IDbCommand cmd) { bool mustCloseConnection = false; if (!(cmd is NpgsqlCommand)) { throw new ArgumentException("The command provided is not an NpgsqlCommand instance.", "cmd"); } if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); mustCloseConnection = true; } NpgsqlCommandBuilder.DeriveParameters((NpgsqlCommand)cmd); if (mustCloseConnection) { cmd.Connection.Close(); } }
public async Task DeriveTextCommandParameters_MappedEnum() { using (var conn = await OpenConnectionAsync()) { await conn.ExecuteNonQueryAsync("CREATE TYPE fruit AS ENUM ('apple', 'cherry', 'plum')"); conn.ReloadTypes(); await using var _ = DeferAsync(async() => { await conn.ExecuteNonQueryAsync("DROP TYPE fruit"); conn.ReloadTypes(); }); conn.TypeMapper.MapEnum <Fruit>("fruit"); var cmd = new NpgsqlCommand("SELECT :x::fruit, :y::fruit[]", conn); const Fruit val1 = Fruit.Apple; var val2 = new Fruit[] { Fruit.Cherry, Fruit.Plum }; NpgsqlCommandBuilder.DeriveParameters(cmd); Assert.That(cmd.Parameters, Has.Count.EqualTo(2)); Assert.That(cmd.Parameters[0].ParameterName, Is.EqualTo("x")); Assert.That(cmd.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Unknown)); Assert.That(cmd.Parameters[0].PostgresType, Is.InstanceOf <PostgresEnumType>()); Assert.That(cmd.Parameters[0].DataTypeName, Does.EndWith("fruit")); Assert.That(cmd.Parameters[1].ParameterName, Is.EqualTo("y")); Assert.That(cmd.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Unknown)); Assert.That(cmd.Parameters[1].PostgresType, Is.InstanceOf <PostgresArrayType>()); Assert.That(cmd.Parameters[1].DataTypeName, Does.EndWith("fruit[]")); cmd.Parameters[0].Value = val1; cmd.Parameters[1].Value = val2; using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult | CommandBehavior.SingleRow)) { Assert.That(reader.Read(), Is.True); Assert.That(reader.GetFieldValue <Fruit>(0), Is.EqualTo(val1)); Assert.That(reader.GetFieldValue <Fruit[]>(1), Is.EqualTo(val2)); } } }
public virtual int UpdateTable(DataTable rdatTable, NpgsqlCommand SelectCommand) { NpgsqlDataAdapter adapter = null; NpgsqlCommandBuilder builder = null; try { if ((rdatTable == null) || (SelectCommand == null)) { return(0); } if (this._sqlTran != null) { SelectCommand.Transaction = this._sqlTran; } adapter = new NpgsqlDataAdapter(SelectCommand); builder = new NpgsqlCommandBuilder(adapter); return(adapter.Update(rdatTable)); } catch (Exception ex) { this._InnerException = ex; throw ex; } finally { if (builder != null) { builder.Dispose(); } if (adapter != null) { adapter.Dispose(); } builder = null; adapter = null; } }
private void addButton_Click(object sender, EventArgs e) { NpgsqlConnection conn = new NpgsqlConnection(); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(); NpgsqlCommandBuilder builder; int productCode = int.Parse(productCodeTextBox.Text); string productName = productNameTextBox.Text; string sql_str = "insert into productlist values(" + productCode + ", '" + productName + "')"; conn.ConnectionString = @"Server = localhost; Port = 5432; User Id = postgres; Password = postgres; Database = test_login;"; //変更予定 conn.Open(); adapter = new NpgsqlDataAdapter(sql_str, conn); builder = new NpgsqlCommandBuilder(adapter); adapter.Fill(dt); adapter.Update(dt); MessageBox.Show("登録完了"); conn.Close(); }
public void showTable() { if (nameTable == "flights") { try { string commSellect = "select * from flights"; //string commSellect = "select flights.id as " + '"' + "id" + '"' + ", whence, whither, departure_time,"; //commSellect += "arrival_time, dir_company.name as " + '"' + "company" + '"' + ", dir_plane.name"; //commSellect += " as " + '"' + "airplane" + '"' + ", platform from flights join dir_company on "; //commSellect += "flights.id_company = dir_company.id join dir_plane on flights.id_airplane = dir_plane.id"; npgsqlConnection.Open(); dataAdapter = new NpgsqlDataAdapter(commSellect, npgsqlConnection); NpgsqlCommandBuilder commandBuilder = new NpgsqlCommandBuilder(dataAdapter); dataSet = new DataSet(); dataAdapter.Fill(dataSet, nameTable); dataGridView1.DataSource = dataSet.Tables[0]; npgsqlConnection.Close(); } catch (Exception ex) { label4.Text = "Log: Show fails"; MessageBox.Show(ex.ToString()); } } else { try { npgsqlConnection.Open(); dataAdapter = new NpgsqlDataAdapter("select * from " + nameTable, npgsqlConnection); NpgsqlCommandBuilder commandBuilder = new NpgsqlCommandBuilder(dataAdapter); dataSet = new DataSet(); dataAdapter.Fill(dataSet, nameTable); dataGridView1.DataSource = dataSet.Tables[0]; npgsqlConnection.Close(); label4.Text = "Log: Таблица отображена"; } catch (Exception ex) { label4.Text = "Log: Show fails"; MessageBox.Show(ex.ToString()); } } }
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); } catch (Exception ex) { throw ex; } finally { conn.ExecuteNonQuery("DROP TABLE IF EXISTS Test"); } }
public virtual void DoInsertWithCommandBuilderCaseSensitive() { DataSet ds = new DataSet(); NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tablei", TheConnection); NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(da); Assert.IsNotNull(builder); da.Fill(ds); DataTable dt = ds.Tables[0]; DataRow dr = dt.NewRow(); dr["Field_Case_Sensitive"] = 4; dt.Rows.Add(dr); DataSet ds2 = ds.GetChanges(); da.Update(ds2); ds.Merge(ds2); ds.AcceptChanges(); NpgsqlDataReader dr2 = new NpgsqlCommand("select * from tablei", TheConnection).ExecuteReader(); dr2.Read(); Assert.AreEqual(4, dr2[1]); dr2.Close(); }
/// <summary> /// Name:GenerateCommand /// Author:Suhel Sagar / 3 JAN 2019 /// Purpose:This method accepts a String array and then using these parameters forms a command object. /// and returns this command object. /// </summary> /// <param name="param"></param> /// <remarks></remarks> /// <modifications> /// </modifications> private void GenerateCommand(params object[] param) { int i = 0; npgsqlcomCommand = new NpgsqlCommand("", npgsqlconDBConnection); npgsqlcomCommand.CommandText = strProcName; npgsqlcomCommand.CommandType = CommandType.StoredProcedure; npgsqlcomCommand.CommandTimeout = intCommandTimeout; if ((npgsqltrnTransaction != null)) { npgsqlcomCommand.Transaction = npgsqltrnTransaction; } NpgsqlCommandBuilder.DeriveParameters(npgsqlcomCommand); for (i = 1; i <= param.GetUpperBound(0) + 1; i += 1) { if (Convert.ToInt32(npgsqlcomCommand.Parameters[i].Value) > 0) { npgsqlcomCommand.Parameters[i].Value = Convert.ToInt32(param[i - 1]); } else { //{ //if (npgsqlcomCommand.Parameters[i].TypeName.Contains("dbo")) //{ // npgsqlcomCommand.Parameters[i].Value = (DataTable)(param[i - 1]); //} //else npgsqlcomCommand.Parameters[i].Value = Convert.ToString(param[i - 1]); } // } } }
public async Task Bug1010788UpdateRowSource() { if (IsMultiplexing) { return; } using (var conn = await OpenConnectionAsync()) { await using var _ = await CreateTempTable(conn, "id SERIAL PRIMARY KEY, name TEXT", out var table); var command = new NpgsqlCommand($"SELECT * FROM {table}", conn); Assert.AreEqual(UpdateRowSource.Both, command.UpdatedRowSource); var cmdBuilder = new NpgsqlCommandBuilder(); var da = new NpgsqlDataAdapter(command); cmdBuilder.DataAdapter = da; Assert.IsNotNull(da.SelectCommand); Assert.IsNotNull(cmdBuilder.DataAdapter); var updateCommand = cmdBuilder.GetUpdateCommand(); Assert.AreEqual(UpdateRowSource.None, updateCommand.UpdatedRowSource); } }
public async Task DeriveFunctionParameters_NoParams() { using (var conn = await OpenConnectionAsync()) { await using var _ = GetTempFunctionName(conn, out var function); // This function returns record because of the two Out (InOut & Out) parameters await conn.ExecuteNonQueryAsync($@" CREATE OR REPLACE FUNCTION {function}() RETURNS int AS ' BEGIN RETURN 4; END; ' LANGUAGE 'plpgsql'; "); var cmd = new NpgsqlCommand(function, conn) { CommandType = CommandType.StoredProcedure }; NpgsqlCommandBuilder.DeriveParameters(cmd); Assert.That(cmd.Parameters, Is.Empty); } }
public async Task DeriveFunctionParameters_DotCharacterInFunctionName() { using (var conn = await OpenConnectionAsync()) { await conn.ExecuteNonQueryAsync( @"CREATE OR REPLACE FUNCTION ""My.Dotted.Function""(int4, text) returns int4 as $BODY$ begin return 0; end $BODY$ language 'plpgsql';"); await using var _ = DeferAsync(() => conn.ExecuteNonQueryAsync(@"DROP FUNCTION ""My.Dotted.Function""")); var command = new NpgsqlCommand(@"""My.Dotted.Function""", conn) { CommandType = CommandType.StoredProcedure }; NpgsqlCommandBuilder.DeriveParameters(command); Assert.AreEqual(NpgsqlDbType.Integer, command.Parameters[0].NpgsqlDbType); Assert.AreEqual(NpgsqlDbType.Text, command.Parameters[1].NpgsqlDbType); } }
private void dataGridView1_CellContentClick_1(object sender, DataGridViewCellEventArgs e) { string asd = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString(); string str = dataGridView1.Columns[e.ColumnIndex].Name.ToString(); if (str == "group_name" || str == "abbr_name") { asd = "\'" + asd + "\'"; } String connectionString = "Server=spsu.ru;Port=5433;User=shtyrba;Password=gBTnz9;Database=goshchina;"; NpgsqlConnection npgSqlConnection = new NpgsqlConnection(connectionString); npgSqlConnection.Open(); b = String.Format("select students.id,students.students_name,period,group_id,abbr_name from students,studentsgroup,groups where students.id=studentsgroup.students_id and groups.id=studentsgroup.group_id and groups.{1} ={0}", asd, str); NpgsqlDataAdapter da = new NpgsqlDataAdapter(b, npgSqlConnection); NpgsqlCommandBuilder cd = new NpgsqlCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; // textBox1.Text = b; listBox1.DataSource = ds.Tables[0].Columns[0]; npgSqlConnection.Close(); }