public void InvariantCultureNpgsqlCopySerializer() { // Test for https://github.com/npgsql/Npgsql/pull/92 // SetCulture is used to set a culture where a comma is used to separate decimal values (0,5) which will cause problems if Npgsql // doesn't convert correctly to use a point. (0.5) var cmd = new NpgsqlCommand("COPY data (field_int4, field_int8, field_float4) FROM STDIN", Conn); var npgsqlCopySerializer = new NpgsqlCopySerializer(Conn); var npgsqlCopyIn = new NpgsqlCopyIn(cmd, Conn, npgsqlCopySerializer.ToStream); npgsqlCopyIn.Start(); npgsqlCopySerializer.AddInt32(300000); npgsqlCopySerializer.AddInt64(1000000); npgsqlCopySerializer.AddNumber(0.5); npgsqlCopySerializer.EndRow(); npgsqlCopySerializer.Flush(); npgsqlCopyIn.End(); NpgsqlDataReader dr = new NpgsqlCommand("select field_int4, field_int8, field_float4 from data", Conn).ExecuteReader(); dr.Read(); Assert.AreEqual(300000, dr[0]); Assert.AreEqual(1000000, dr[1]); Assert.AreEqual(0.5, dr[2]); }
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 Bug188BufferNpgsqlCopySerializer() { var cmd = new NpgsqlCommand("COPY data (field_int4, field_text) FROM STDIN", Conn); var npgsqlCopySerializer = new NpgsqlCopySerializer(Conn); var npgsqlCopyIn = new NpgsqlCopyIn(cmd, Conn, npgsqlCopySerializer.ToStream); string str = "Very long string".PadRight(NpgsqlCopySerializer.DEFAULT_BUFFER_SIZE, 'z'); npgsqlCopyIn.Start(); npgsqlCopySerializer.AddInt32(12345678); npgsqlCopySerializer.AddString(str); npgsqlCopySerializer.EndRow(); npgsqlCopySerializer.Flush(); npgsqlCopyIn.End(); NpgsqlDataReader dr = new NpgsqlCommand("select field_int4, field_text from data", Conn).ExecuteReader(); dr.Read(); Assert.AreEqual(12345678, dr[0]); Assert.AreEqual(str, dr[1]); }
public void GetSchemaDuringRead() { ExecuteNonQuery(@"INSERT INTO data (field_text) VALUES ('foo')"); using (var dr = new NpgsqlCommand(@"SELECT field_text FROM data", Conn).ExecuteReader()) { dr.Read(); var metadata = Conn.GetSchema("Tables", new string[] { null, null, "data" }); Assert.That(metadata.Rows.Count, Is.EqualTo(1)); } }
public virtual void DoInsertWithCommandBuilderCaseSensitive() { var ds = new DataSet(); var da = new NpgsqlDataAdapter("select * from tablei", Conn); var builder = new NpgsqlCommandBuilder(da); Assert.IsNotNull(builder); da.Fill(ds); var dt = ds.Tables[0]; var dr = dt.NewRow(); dr["Field_Case_Sensitive"] = 4; dt.Rows.Add(dr); var ds2 = ds.GetChanges(); da.Update(ds2); ds.Merge(ds2); ds.AcceptChanges(); using (var dr2 = new NpgsqlCommand("select * from tablei", Conn).ExecuteReader()) { dr2.Read(); Assert.AreEqual(4, dr2[1]); } }
public virtual void DoUpdateWithDataSet() { var command = new NpgsqlCommand("insert into tableb(field_int2) values (2)", Conn); command.ExecuteNonQuery(); var ds = new DataSet(); var da = new NpgsqlDataAdapter("select * from tableb where field_serial = (select max(field_serial) from tableb)", Conn); var cb = new NpgsqlCommandBuilder(da); Assert.IsNotNull(cb); da.Fill(ds); var dt = ds.Tables[0]; Assert.IsNotNull(dt); var dr = ds.Tables[0].Rows[ds.Tables[0].Rows.Count - 1]; dr["field_int2"] = 4; var ds2 = ds.GetChanges(); da.Update(ds2); ds.Merge(ds2); ds.AcceptChanges(); using (var 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"]); } }
public void UpdateLettingNullFieldValue() { var command = new NpgsqlCommand(@"INSERT INTO data (field_int2) VALUES (2)", Conn); command.ExecuteNonQuery(); var ds = new DataSet(); var da = new NpgsqlDataAdapter("SELECT * FROM data", Conn); da.InsertCommand = new NpgsqlCommand(";", Conn); da.UpdateCommand = new NpgsqlCommand("UPDATE data SET field_int2 = :a, field_timestamp = :b, field_numeric = :c WHERE field_serial = :d", Conn); 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); var dt = ds.Tables[0]; Assert.IsNotNull(dt); var dr = ds.Tables[0].Rows[ds.Tables[0].Rows.Count - 1]; dr["field_int2"] = 4; var ds2 = ds.GetChanges(); da.Update(ds2); ds.Merge(ds2); ds.AcceptChanges(); using (var dr2 = new NpgsqlCommand(@"SELECT field_int2 FROM data", Conn).ExecuteReader()) { dr2.Read(); Assert.AreEqual(4, dr2["field_int2"]); } }
public void InsertWithDataSet() { _conn.Open(); DataSet ds = new DataSet(); NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tableb", _conn); da.InsertCommand = new NpgsqlCommand("insert into tableb(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); DataTable dt = ds.Tables[0]; DataRow dr = dt.NewRow(); dr["field_int2"] = 4; dr["field_timestamp"] = new DateTime(2003, 03, 03, 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", _conn).ExecuteReader(); dr2.Read(); Assert.AreEqual(4, dr2[1]); Assert.AreEqual(7.3000000M, dr2[3]); new NpgsqlCommand("delete from tableb where field_serial > 4", _conn).ExecuteNonQuery(); }
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 void Bug221MillisecondsFieldNotCopied() { // Test for https://github.com/npgsql/Npgsql/issues/221 // The milliseconds field is not properly copied in NpgsqlCopySerializer.cs in method AddDateTime var cmd = new NpgsqlCommand("COPY data (field_timestamp) FROM STDIN", Conn); var npgsqlCopySerializer = new NpgsqlCopySerializer(Conn); var npgsqlCopyIn = new NpgsqlCopyIn(cmd, Conn, npgsqlCopySerializer.ToStream); var testDate = DateTime.Parse("2002-02-02 09:00:23.005"); npgsqlCopyIn.Start(); npgsqlCopySerializer.AddDateTime(testDate); npgsqlCopySerializer.EndRow(); npgsqlCopySerializer.Flush(); npgsqlCopyIn.End(); NpgsqlDataReader dr = new NpgsqlCommand("select field_timestamp from data", Conn).ExecuteReader(); dr.Read(); Assert.AreEqual(testDate, dr[0]); }
/// <summary> /// Queries the data t /// </summary> /// <returns></returns> private PostGisSpatialObjectType GetSpatialObjectType() { var sql = string.Format("SELECT \"udt_name\" from \"information_schema\".\"columns\" " + "WHERE \"table_schema\"='{0}' AND \"table_name\"='{1}' AND \"column_name\"='{2}';", _schema, _table, _geometryColumn); using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); using (var rdr = new NpgsqlCommand(sql, conn).ExecuteReader()) { if (rdr.HasRows) { rdr.Read(); switch (rdr.GetString(0)) { case "geometry": return PostGisSpatialObjectType.Geometry; case "geography": _geometryCast = "::geometry"; return PostGisSpatialObjectType.Geography; default: throw new ArgumentException( "Provided geometry/geography column name does not yield geometry/geography data"); } } } throw new NotSupportedException("Could not find geometry column within tables, need to check view definition"); } }
/// <summary> /// Get the non-spatial columns /// </summary> private void GetNonSpatialColumns() { if (!string.IsNullOrEmpty(_columns)) return; if (string.IsNullOrEmpty(ConnectionID)) return; using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); using (var dr = new NpgsqlCommand(string.Format( "SELECT \"column_name\" FROM \"information_schema\".\"columns\" "+ "WHERE \"table_schema\"='{0}' AND \"table_name\"='{1}';", Schema, Table), conn).ExecuteReader()) { if (!dr.HasRows) throw new InvalidOperationException("Provider configuration incomplete or wrong!"); var columns = new List<string>{ "\"" + ObjectIdColumn + "\"" }; while (dr.Read()) { var column = dr.GetString(0); if (string.Equals(column, ObjectIdColumn)) continue; if (string.Equals(column, GeometryColumn)) continue; columns.Add(string.Format("\"{0}\"", column)); } _columns = string.Join(", ", columns); } } }
public List<string> GetExistingLayerInformation() { //SQL command: select * from geometry_columns using (var conn = new NpgsqlConnection(ConnectionString)) { var sql = string.Format("SELECT * FROM \"geometry_columns\" "); conn.Open(); using (var rdr = new NpgsqlCommand(sql, conn).ExecuteReader()) { if (rdr.HasRows) { tableList = new List<string>(); while (rdr.Read()) { for (int i = 0; i < rdr.FieldCount; i++) { tableList.Add(rdr[i].ToString()); } } conn.Close(); } } } return tableList; }