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");
}
