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));
}
}
// サブプロシージャ：テーブルを定義し、ＤＢよりデータを読み込む
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 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();
}
