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