dev-npgsql/npgsql: Npgsql is the .NET data provider for PostgreSQL.

test/Npgsql.Tests/DataAdapterTests.cs
636 строк24 KB
Shay Rojansky
Remove some old ifdefs
28 фев 2018, 20:08
28 фев 2018, 20:08f7ba55b
#region License
// The PostgreSQL License
//
// Copyright (C) 2018 The Npgsql Development Team
//
// Permission to use, copy, modify, and distribute this software and its
// documentation for any purpose, without fee, and without a written
// agreement is hereby granted, provided that the above copyright notice
// and this paragraph and the following two paragraphs appear in all copies.
//
// IN NO EVENT SHALL THE NPGSQL DEVELOPMENT TEAM BE LIABLE TO ANY PARTY
// FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES,
// INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
// DOCUMENTATION, EVEN IF THE NPGSQL DEVELOPMENT TEAM HAS BEEN ADVISED OF
// THE POSSIBILITY OF SUCH DAMAGE.
//
// THE NPGSQL DEVELOPMENT TEAM SPECIFICALLY DISCLAIMS ANY WARRANTIES,
// INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
// AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
// ON AN "AS IS" BASIS, AND THE NPGSQL DEVELOPMENT TEAM HAS NO OBLIGATIONS
// TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
#endregion

using System;
using System.Data;
using Npgsql;

using NpgsqlTypes;

using NUnit.Framework;

namespace Npgsql.Tests
{
    public class DataAdapterTests : TestBase
    {
        [Test]
        public void UseDataAdapter()
        {
            using (var conn = OpenConnection())
            using (var command = new NpgsqlCommand("SELECT 1", conn))
            {
                var da = new NpgsqlDataAdapter();
                da.SelectCommand = command;
                var ds = new DataSet();
                da.Fill(ds);
                //ds.WriteXml("TestUseDataAdapter.xml");
            }
        }

        [Test]
        public void UseDataAdapterNpgsqlConnectionConstructor()
        {
            using (var conn = OpenConnection())
            using (var command = new NpgsqlCommand("SELECT 1", conn))
            {
                command.Connection = conn;
                var da = new NpgsqlDataAdapter(command);
                var ds = new DataSet();
                da.Fill(ds);
                //ds.WriteXml("TestUseDataAdapterNpgsqlConnectionConstructor.xml");
            }
        }

        [Test]
        public void UseDataAdapterStringNpgsqlConnectionConstructor()
        {
            using (var conn = OpenConnection())
            {
                var da = new NpgsqlDataAdapter("SELECT 1", conn);
                var ds = new DataSet();
                da.Fill(ds);
                //ds.WriteXml("TestUseDataAdapterStringNpgsqlConnectionConstructor.xml");
            }
        }

        [Test]
        public void UseDataAdapterStringStringConstructor()
        {
            var da = new NpgsqlDataAdapter("SELECT 1", ConnectionString);
            var ds = new DataSet();
            da.Fill(ds);
            //ds.WriteXml("TestUseDataAdapterStringStringConstructor.xml");
        }

        [Test]
        public void UseDataAdapterStringStringConstructor2()
        {
            var da = new NpgsqlDataAdapter("SELECT 1", ConnectionString);
            var ds = new DataSet();
            da.Fill(ds);
            //ds.WriteXml("TestUseDataAdapterStringStringConstructor2.xml");
        }

        [Test]
        [MonoIgnore("Bug in mono, submitted pull request: https://github.com/mono/mono/pull/1172")]
        public void InsertWithDataSet()
        {
            using (var conn = OpenConnection())
            {
                Setup(conn);
                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();
            }
        }

        [Test]
        public void DataAdapterUpdateReturnValue()
        {
            using (var conn = OpenConnection())
            {
                Setup(conn);
                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);

                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();
                var daupdate = da.Update(ds2);

                Assert.AreEqual(2, daupdate);
            }
        }

        [Test]
        [Ignore("")]
        public void DataAdapterUpdateReturnValue2()
        {
            using (var conn = OpenConnection())
            {
                var cmd = conn.CreateCommand();
                var da = new NpgsqlDataAdapter("select * from tabled", conn);
                var cb = new NpgsqlCommandBuilder(da);
                var ds = new DataSet();
                da.Fill(ds);

                //## Insert a new row with id = 1
                ds.Tables[0].Rows.Add(0.4, 0.5);
                da.Update(ds);

                //## change id from 1 to 2
                cmd.CommandText = "update tabled set field_float4 = 0.8";
                cmd.ExecuteNonQuery();

                //## change value to newvalue
                ds.Tables[0].Rows[0][1] = 0.7;
                //## update should fail, and make a DBConcurrencyException
                var count = da.Update(ds);
                //## count is 1, even if the isn't updated in the database
                Assert.AreEqual(0, count);
            }
        }

        [Test]
        public void FillWithEmptyResultset()
        {
            using (var conn = OpenConnection())
            {
                Setup(conn);
                var ds = new DataSet();
                var da = new NpgsqlDataAdapter("SELECT field_serial, field_int2, field_timestamp, field_numeric FROM data WHERE field_serial = -1", conn);

                da.Fill(ds);

                Assert.AreEqual(1, ds.Tables.Count);
                Assert.AreEqual(4, ds.Tables[0].Columns.Count);
                Assert.AreEqual("field_serial", ds.Tables[0].Columns[0].ColumnName);
                Assert.AreEqual("field_int2", ds.Tables[0].Columns[1].ColumnName);
                Assert.AreEqual("field_timestamp", ds.Tables[0].Columns[2].ColumnName);
                Assert.AreEqual("field_numeric", ds.Tables[0].Columns[3].ColumnName);
            }
        }

        [Test]
        [Ignore("")]
        public void FillAddWithKey()
        {
            using (var conn = OpenConnection())
            {
                Setup(conn);
                var ds = new DataSet();
                var da = new NpgsqlDataAdapter("select field_serial, field_int2, field_timestamp, field_numeric from tableb", conn);

                da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                da.Fill(ds);

                var field_serial = ds.Tables[0].Columns[0];
                var field_int2 = ds.Tables[0].Columns[1];
                var field_timestamp = ds.Tables[0].Columns[2];
                var field_numeric = ds.Tables[0].Columns[3];

                Assert.IsFalse(field_serial.AllowDBNull);
                Assert.IsTrue(field_serial.AutoIncrement);
                Assert.AreEqual("field_serial", field_serial.ColumnName);
                Assert.AreEqual(typeof(int), field_serial.DataType);
                Assert.AreEqual(0, field_serial.Ordinal);
                Assert.IsTrue(field_serial.Unique);

                Assert.IsTrue(field_int2.AllowDBNull);
                Assert.IsFalse(field_int2.AutoIncrement);
                Assert.AreEqual("field_int2", field_int2.ColumnName);
                Assert.AreEqual(typeof(short), field_int2.DataType);
                Assert.AreEqual(1, field_int2.Ordinal);
                Assert.IsFalse(field_int2.Unique);

                Assert.IsTrue(field_timestamp.AllowDBNull);
                Assert.IsFalse(field_timestamp.AutoIncrement);
                Assert.AreEqual("field_timestamp", field_timestamp.ColumnName);
                Assert.AreEqual(typeof(DateTime), field_timestamp.DataType);
                Assert.AreEqual(2, field_timestamp.Ordinal);
                Assert.IsFalse(field_timestamp.Unique);

                Assert.IsTrue(field_numeric.AllowDBNull);
                Assert.IsFalse(field_numeric.AutoIncrement);
                Assert.AreEqual("field_numeric", field_numeric.ColumnName);
                Assert.AreEqual(typeof(decimal), field_numeric.DataType);
                Assert.AreEqual(3, field_numeric.Ordinal);
                Assert.IsFalse(field_numeric.Unique);
            }
        }

        [Test]
        public void FillAddColumns()
        {
            using (var conn = OpenConnection())
            {
                Setup(conn);
                var ds = new DataSet();
                var da = new NpgsqlDataAdapter(@"SELECT field_serial, field_int2, field_timestamp, field_numeric FROM data", conn);

                da.MissingSchemaAction = MissingSchemaAction.Add;
                da.Fill(ds);

                var field_serial = ds.Tables[0].Columns[0];
                var field_int2 = ds.Tables[0].Columns[1];
                var field_timestamp = ds.Tables[0].Columns[2];
                var field_numeric = ds.Tables[0].Columns[3];

                Assert.AreEqual("field_serial", field_serial.ColumnName);
                Assert.AreEqual(typeof(int), field_serial.DataType);
                Assert.AreEqual(0, field_serial.Ordinal);

                Assert.AreEqual("field_int2", field_int2.ColumnName);
                Assert.AreEqual(typeof(short), field_int2.DataType);
                Assert.AreEqual(1, field_int2.Ordinal);

                Assert.AreEqual("field_timestamp", field_timestamp.ColumnName);
                Assert.AreEqual(typeof(DateTime), field_timestamp.DataType);
                Assert.AreEqual(2, field_timestamp.Ordinal);

                Assert.AreEqual("field_numeric", field_numeric.ColumnName);
                Assert.AreEqual(typeof(decimal), field_numeric.DataType);
                Assert.AreEqual(3, field_numeric.Ordinal);
            }
        }

        [Test]
        [MonoIgnore("Bug in mono, submitted pull request: https://github.com/mono/mono/pull/1172")]
        public void UpdateLettingNullFieldValue()
        {
            using (var conn = OpenConnection())
            {
                Setup(conn);
                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"]);
                }
            }
        }

        [Test]
        public void FillWithDuplicateColumnName()
        {
            using (var conn = OpenConnection())
            {
                Setup(conn);
                var ds = new DataSet();
                var da = new NpgsqlDataAdapter("SELECT field_serial, field_serial FROM data", conn);
                da.Fill(ds);
            }
        }

        [Test]
        [Ignore("")]
        public void UpdateWithDataSet()
        {
            DoUpdateWithDataSet();
        }

        public virtual void DoUpdateWithDataSet()
        {
            using (var conn = OpenConnection())
            {
                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", 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", conn).ExecuteReader())
                {
                    dr2.Read();
                    Assert.AreEqual(4, dr2["field_int2"]);
                }
            }
        }

        [Test]
        [Ignore("")]
        public void InsertWithCommandBuilderCaseSensitive()
        {
            DoInsertWithCommandBuilderCaseSensitive();
        }

        public virtual void DoInsertWithCommandBuilderCaseSensitive()
        {
            using (var conn = OpenConnection())
            {
                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]);
                }
            }
        }

        [Test]
        public void IntervalAsTimeSpan()
        {
            using (var conn = OpenConnection())
            {
                conn.ExecuteNonQuery(@"CREATE TEMP TABLE data (" +
                                      "  pk SERIAL PRIMARY KEY, " +
                                      "  interval INTERVAL" +
                                      ")");
                conn.ExecuteNonQuery(@"INSERT INTO data (interval) VALUES ('1 hour'::INTERVAL)");

                var dt = new DataTable("data");
                var command = new NpgsqlCommand
                {
                    CommandType = CommandType.Text,
                    CommandText = "SELECT interval FROM data",
                    Connection = conn
                };
                var da = new NpgsqlDataAdapter {SelectCommand = command};
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    //Console.Out.WriteLine(dr["interval"]);
                }
            }
        }

        [Test]
        public void IntervalAsTimeSpan2()
        {
            using (var conn = OpenConnection())
            {
                conn.ExecuteNonQuery(@"CREATE TEMP TABLE data (" +
                                      "  pk SERIAL PRIMARY KEY, " +
                                      "  interval INTERVAL" +
                                      ")");
                conn.ExecuteNonQuery(@"INSERT INTO data (interval) VALUES ('1 hour'::INTERVAL)");

                var dt = new DataTable("data");
                //DataColumn c = dt.Columns.Add("dauer", typeof(TimeSpan));
                // DataColumn c = dt.Columns.Add("dauer", typeof(NpgsqlInterval));
                //c.AllowDBNull = true;
                var command = new NpgsqlCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = "SELECT interval FROM data";
                command.Connection = conn;
                var da = new NpgsqlDataAdapter();
                da.SelectCommand = command;
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    //Console.Out.WriteLine(dr["interval"]);
                }
            }
        }

        [Test]
        public void DbDataAdapterCommandAccess()
        {
            using (var conn = OpenConnection())
            using (var command = new NpgsqlCommand("SELECT CAST('1 hour' AS interval) AS dauer", conn))
            {
                Setup(conn);
                var da = new NpgsqlDataAdapter();
                da.SelectCommand = command;
                System.Data.Common.DbDataAdapter common = da;
                Assert.IsNotNull(common.SelectCommand);
            }
        }

        [Test, Description("Makes sure that the INSERT/UPDATE/DELETE commands are auto-populated on NpgsqlDataAdapter")]
        [IssueLink("https://github.com/npgsql/npgsql/issues/179")]
        [Ignore("Somehow related to us using a temporary table???")]
        public void AutoPopulateAdapterCommands()
        {
            using (var conn = OpenConnection())
            {
                Setup(conn);
                var da = new NpgsqlDataAdapter("SELECT field_pk,field_int4 FROM data", conn);
                var builder = new NpgsqlCommandBuilder(da);
                var ds = new DataSet();
                da.Fill(ds);

                var table = ds.Tables[0];
                var row = table.NewRow();
                row["field_pk"] = 1;
                row["field_int4"] = 8;
                table.Rows.Add(row);
                da.Update(ds);
                Assert.That(conn.ExecuteScalar(@"SELECT field_int4 FROM data"), Is.EqualTo(8));

                row["field_int4"] = 9;
                da.Update(ds);
                Assert.That(conn.ExecuteScalar(@"SELECT field_int4 FROM data"), Is.EqualTo(9));

                row.Delete();
                da.Update(ds);
                Assert.That(conn.ExecuteScalar(@"SELECT COUNT(*) FROM data"), Is.EqualTo(0));
            }
        }

        [Test]
        public void CommandBuilderQuoting()
        {
            var cb = new NpgsqlCommandBuilder();
            const string orig = "some\"column";
            var quoted = cb.QuoteIdentifier(orig);
            Assert.That(quoted, Is.EqualTo("\"some\"\"column\""));
            Assert.That(cb.UnquoteIdentifier(quoted), Is.EqualTo(orig));
        }

        [Test, Description("Makes sure a correct SQL string is built with GetUpdateCommand(true) using correct parameter names and placeholders")]
        [IssueLink("https://github.com/npgsql/npgsql/issues/397")]
        [Ignore("Somehow related to us using a temporary table???")]
        public void GetUpdateCommand()
        {
            using (var conn = OpenConnection())
            {
                Setup(conn);
                using (var da = new NpgsqlDataAdapter("SELECT field_pk, field_int4 FROM data", conn))
                {
                    using (var cb = new NpgsqlCommandBuilder(da))
                    {
                        var updateCommand = cb.GetUpdateCommand(true);
                        da.UpdateCommand = updateCommand;

                        var ds = new DataSet();
                        da.Fill(ds);

                        var table = ds.Tables[0];
                        var row = table.Rows.Add();
                        row["field_pk"] = 1;
                        row["field_int4"] = 1;
                        da.Update(ds);

                        row["field_int4"] = 2;
                        da.Update(ds);

                        row.Delete();
                        da.Update(ds);
                    }
                }
            }
        }

        [Test]
        public void LoadDataTable()
        {
            using (var conn = OpenConnection())
            {
                conn.ExecuteNonQuery("CREATE TEMP TABLE data (char5 CHAR(5), varchar5 VARCHAR(5))");
                using (var command = new NpgsqlCommand("SELECT char5, varchar5 FROM data", conn))
                using (var dr = command.ExecuteReader())
                {
                    var dt = new DataTable();
                    dt.Load(dr);
                    dr.Close();

                    Assert.AreEqual(5, dt.Columns[0].MaxLength);
                    Assert.AreEqual(5, dt.Columns[1].MaxLength);
                }
            }
        }

        public void Setup(NpgsqlConnection conn)
        {
            conn.ExecuteNonQuery("CREATE TEMP TABLE data (" +
                                 "field_pk SERIAL PRIMARY KEY," +
                                 "field_serial SERIAL," +
                                 "field_int2 SMALLINT," +
                                 "field_int4 INTEGER," +
                                 "field_numeric NUMERIC," +
                                 "field_timestamp TIMESTAMP" +
                                 ")");
        }
    }
}