public void PqsqlCommandBuilderTest3() { using (PqsqlConnection connection = new PqsqlConnection(connectionString)) using (PqsqlCommand command = connection.CreateCommand()) { PqsqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = "create temp table temptab (c0 int4 primary key, c1 float8)"; command.CommandType = CommandType.Text; command.ExecuteNonQuery(); transaction.Commit(); // temp table must be visible in the next transaction transaction = connection.BeginTransaction(); PqsqlDataAdapter adapter = new PqsqlDataAdapter("select * from temptab", connection) { SelectCommand = { Transaction = transaction } }; PqsqlCommandBuilder builder = new PqsqlCommandBuilder(adapter); // INSERT INTO "postgres"."pg_temp_2"."temptab" ("c0", "c1") VALUES (:p1, :p2) PqsqlCommand inserter = builder.GetInsertCommand(); inserter.Parameters["p1"].Value = 1; inserter.Parameters["p2"].Value = 2.1; int inserted = inserter.ExecuteNonQuery(); Assert.AreEqual(1, inserted); // UPDATE "postgres"."pg_temp_2"."temptab" // SET "c0" = :p1, "c1" = :p2 // WHERE (("c0" = :p3) AND ((:p4 = 1 AND "c1" IS NULL) OR ("c1" = :p5))) PqsqlCommand updater = builder.GetUpdateCommand(); updater.Parameters["p1"].Value = 2; updater.Parameters["p2"].Value = 2.2; updater.Parameters["p3"].Value = 1; updater.Parameters["p4"].Value = 0; updater.Parameters["p5"].Value = 2.1; int updated = updater.ExecuteNonQuery(); Assert.AreEqual(1, updated); // DELETE FROM "postgres"."pg_temp_2"."temptab" // WHERE (("c0" = :p1) AND ((:p2 = 1 AND "c1" IS NULL) OR ("c1" = :p3))) PqsqlCommand deleter = builder.GetDeleteCommand(); deleter.Parameters["p1"].Value = 2; deleter.Parameters["p2"].Value = 0; deleter.Parameters["p3"].Value = 2.2; int deleted = deleter.ExecuteNonQuery(); Assert.AreEqual(1, deleted); transaction.Rollback(); } }
public void PqsqlCommandBuilderTest2() { using (PqsqlConnection connection = new PqsqlConnection(connectionString)) using (PqsqlCommand command = connection.CreateCommand()) { PqsqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = "create temp table temptab (c0 int4 primary key, c1 float8)"; command.CommandType = CommandType.Text; command.ExecuteNonQuery(); transaction.Commit(); // temp table must be visible in the next transaction transaction = connection.BeginTransaction(); PqsqlDataAdapter adapter = new PqsqlDataAdapter("select * from temptab", connection) { SelectCommand = { Transaction = transaction }, }; adapter.RowUpdated += Adapter_RowUpdated; PqsqlCommandBuilder builder = new PqsqlCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.FillSchema(ds, SchemaType.Source); adapter.Fill(ds, "temptab"); DataTable temptab = ds.Tables["temptab"]; DataRow row = temptab.NewRow(); row["c0"] = 123; row["c1"] = 1.23; temptab.Rows.Add(row); adapter.Update(ds, "temptab"); command.CommandText = "select * from temptab"; command.CommandType = CommandType.Text; using (PqsqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Assert.AreEqual(123, reader.GetInt32(0)); Assert.AreEqual(1.23, reader.GetDouble(1)); } } transaction.Rollback(); } }
public void PqsqlCommandBuilderTest4() { using (PqsqlConnection connection = new PqsqlConnection(connectionString)) using (PqsqlCommand command = connection.CreateCommand()) { PqsqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = "create temp table temptab (c0 int4 primary key, c1 float8, c2 timestamp);"; command.CommandType = CommandType.Text; command.ExecuteNonQuery(); transaction.Commit(); // temp table must be visible in the next transaction transaction = connection.BeginTransaction(); PqsqlDataAdapter adapter = new PqsqlDataAdapter("select * from temptab", connection) { SelectCommand = { Transaction = transaction }, }; adapter.RowUpdated += Adapter_RowUpdated; PqsqlCommandBuilder builder = new PqsqlCommandBuilder(adapter); DataTableMapping mapping = adapter.TableMappings.Add("Table", "temptab"); mapping.ColumnMappings.Add("c0", "id"); mapping.ColumnMappings.Add("c2", "time"); DataSet ds = new DataSet(); adapter.FillSchema(ds, SchemaType.Mapped); adapter.Fill(ds); DataTable tab = ds.Tables[0]; Assert.AreEqual("id", tab.Columns[0].ColumnName); Assert.AreEqual("c1", tab.Columns[1].ColumnName); Assert.AreEqual("time", tab.Columns[2].ColumnName); transaction.Rollback(); } }
public void PqsqlDataAdapterTest1() { // get current_database() name mCmd.CommandText = "current_database"; mCmd.CommandType = CommandType.StoredProcedure; object curdb = mCmd.ExecuteScalar(); // get dbname from connection object dbname = mConnection.Database; // fetch pg_database tuple mCmd.CommandText = "select datname,pg_encoding_to_char(encoding),datcollate,datctype,datallowconn from pg_database where datname=:dat"; mCmd.CommandType = CommandType.Text; PqsqlParameter datpar = mCmd.CreateParameter(); datpar.ParameterName = "dat"; datpar.DbType = DbType.String; datpar.Value = curdb; mCmd.Parameters.Add(datpar); DataSet ds = new DataSet(); using (PqsqlDataAdapter adapter = new PqsqlDataAdapter(mCmd)) { adapter.Fill(ds); } Assert.AreEqual(1, ds.Tables.Count, "wrong table count"); int tables = 0; int rows = 0; int columns = 0; foreach (DataTable table in ds.Tables) { foreach (DataRow row in table.Rows) { foreach (object item in row.ItemArray) { // read item switch (columns) { case 0: // datname Assert.AreEqual(dbname, item, "wrong database name"); break; case 1: // encoding Assert.AreEqual("UTF8", item, "wrong encoding id"); break; case 2: // datcollate case 3: // datctype Assert.IsNotNull(item); break; case 4: // datallowconn Assert.AreEqual(true, item, "we must be allowed to connect"); break; } columns++; } rows++; } tables++; } Assert.AreEqual(1, tables, "wrong table count"); Assert.AreEqual(1, rows, "wrong row count"); Assert.AreEqual(5, columns, "wrong column count"); }