Example #1
0
        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]);
        }
Example #2
0
        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();
        }
Example #3
0
        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]);
        }
Example #4
0
 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));
     }
 }
Example #5
0
        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]);
            }
        }
Example #6
0
        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"]);
            }
        }
Example #7
0
        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();
			
			
						
		}
Example #9
0
        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();
        }
Example #10
0
        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]);
        }
Example #11
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");
            }
        }
Example #12
0
        /// <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);
                }
            }
        }
Example #13
0
        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;
        }