public void InsertWithDataSet()
{
var ds = new DataSet();
var da = new NpgsqlDataAdapter("SELECT * FROM data", Conn);
da.InsertCommand = new NpgsqlCommand("INSERT INTO data (field_int2, field_timestamp, field_numeric) VALUES (:a, :b, :c)", Conn);
da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));
da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[0].SourceColumn = "field_int2";
da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp";
da.InsertCommand.Parameters[2].SourceColumn = "field_numeric";
da.Fill(ds);
var dt = ds.Tables[0];
var dr = dt.NewRow();
dr["field_int2"] = 4;
dr["field_timestamp"] = new DateTime(2003, 01, 30, 14, 0, 0);
dr["field_numeric"] = 7.3M;
dt.Rows.Add(dr);
var ds2 = ds.GetChanges();
da.Update(ds2);
ds.Merge(ds2);
ds.AcceptChanges();
var dr2 = new NpgsqlCommand("SELECT field_int2, field_numeric, field_timestamp FROM data", Conn).ExecuteReader();
dr2.Read();
Assert.AreEqual(4, dr2[0]);
Assert.AreEqual(7.3000000M, dr2[1]);
dr2.Close();
}
public void UpdateLettingNullFieldValue()
{
NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_int2) values (2)", TheConnection);
command.ExecuteNonQuery();
DataSet ds = new DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tableb where field_serial = (select max(field_serial) from tableb)", TheConnection);
da.InsertCommand = new NpgsqlCommand(";", TheConnection);
da.UpdateCommand = new NpgsqlCommand("update tableb set field_int2 = :a, field_timestamp = :b, field_numeric = :c where field_serial = :d", TheConnection);
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("d", NpgsqlDbType.Bigint));
da.UpdateCommand.Parameters[0].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[1].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[2].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[3].Direction = ParameterDirection.Input;
da.UpdateCommand.Parameters[0].SourceColumn = "field_int2";
da.UpdateCommand.Parameters[1].SourceColumn = "field_timestamp";
da.UpdateCommand.Parameters[2].SourceColumn = "field_numeric";
da.UpdateCommand.Parameters[3].SourceColumn = "field_serial";
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)", TheConnection).ExecuteReader();
dr2.Read();
Assert.AreEqual(4, dr2["field_int2"]);
dr2.Close();
}
public virtual void DoUpdateWithDataSet()
{
NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_int2) values (2)", TheConnection);
command.ExecuteNonQuery();
DataSet ds = new DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tableb where field_serial = (select max(field_serial) from tableb)", TheConnection);
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[dt.Rows.Count - 1];
Assert.IsNotNull(dr["field_serial"]);
dr["field_int2"] = 4;
DataSet ds2 = ds.GetChanges();
DataRow datarow2 = ds.Tables[0].Rows[0];
Assert.IsNotNull(datarow2["field_serial"]);
da.Update(ds2);
ds.Merge(ds2);
ds.AcceptChanges();
NpgsqlDataReader dr2 = new NpgsqlCommand("select * from tableb where field_serial = (select max(field_serial) from tableb)", TheConnection).ExecuteReader();
dr2.Read();
Assert.AreEqual(4, dr2["field_int2"]);
dr2.Close();
}
public void InsertWithDataSet()
{
DataSet ds = new DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tableb", TheConnection);
da.InsertCommand = new NpgsqlCommand("insert into tableb(field_int2, field_timestamp, field_numeric) values (:a, :b, :c)", TheConnection);
da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));
da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[0].SourceColumn = "field_int2";
da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp";
da.InsertCommand.Parameters[2].SourceColumn = "field_numeric";
da.Fill(ds);
DataTable dt = ds.Tables[0];
DataRow dr = dt.NewRow();
dr["field_int2"] = 4;
dr["field_timestamp"] = new DateTime(2003, 01, 30, 14, 0, 0);
dr["field_numeric"] = 7.3M;
dt.Rows.Add(dr);
DataSet ds2 = ds.GetChanges();
da.Update(ds2);
ds.Merge(ds2);
ds.AcceptChanges();
NpgsqlDataReader dr2 = new NpgsqlCommand("select * from tableb where field_serial > 4", TheConnection).ExecuteReader();
dr2.Read();
Assert.AreEqual(4, dr2[1]);
Assert.AreEqual(7.3000000M, dr2[3]);
dr2.Close();
}
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();
}
