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

test/Npgsql.Tests/DataAdapterTests.cs
612 строк24 KB
Shay Rojansky
Switch to using .NET SDK 5.0.0-preview8
01 сен 2020, 22:55
01 сен 2020, 22:5581a6c33
using System;
using System.Data;
using System.Threading.Tasks;
using NpgsqlTypes;
using NUnit.Framework;
using static Npgsql.Tests.TestUtil;

namespace Npgsql.Tests
{
    public class DataAdapterTests : TestBase
    {
        [Test]
        public async Task UseDataAdapter()
        {
            using (var conn = await OpenConnectionAsync())
            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 async Task UseDataAdapterNpgsqlConnectionConstructor()
        {
            using (var conn = await OpenConnectionAsync())
            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 async Task UseDataAdapterStringNpgsqlConnectionConstructor()
        {
            using (var conn = await OpenConnectionAsync())
            {
                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 async Task InsertWithDataSet()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);
                var ds = new DataSet();
                var da = new NpgsqlDataAdapter($"SELECT * FROM {table}", conn);

                da.InsertCommand = new NpgsqlCommand($"INSERT INTO {table} (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 {table}", conn).ExecuteReader();
                dr2.Read();

                Assert.AreEqual(4, dr2[0]);
                Assert.AreEqual(7.3000000M, dr2[1]);
                dr2.Close();
            }
        }

        [Test]
        public async Task DataAdapterUpdateReturnValue()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);
                var ds = new DataSet();
                var da = new NpgsqlDataAdapter($"SELECT * FROM {table}", conn);

                da.InsertCommand = new NpgsqlCommand($@"INSERT INTO {table} (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 async Task DataAdapterUpdateReturnValue2()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);

                var cmd = conn.CreateCommand();
                var da = new NpgsqlDataAdapter($"select * from {table}", 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 {table} 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 async Task FillWithEmptyResultset()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);

                var ds = new DataSet();
                var da = new NpgsqlDataAdapter($"SELECT field_serial, field_int2, field_timestamp, field_numeric FROM {table} 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 async Task FillAddWithKey()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);

                var ds = new DataSet();
                var da = new NpgsqlDataAdapter($"select field_serial, field_int2, field_timestamp, field_numeric from {table}", 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 async Task FillAddColumns()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);

                var ds = new DataSet();
                var da = new NpgsqlDataAdapter($"SELECT field_serial, field_int2, field_timestamp, field_numeric FROM {table}", 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 async Task UpdateLettingNullFieldValue()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);

                var command = new NpgsqlCommand($"INSERT INTO {table} (field_int2) VALUES (2)", conn);
                command.ExecuteNonQuery();

                var ds = new DataSet();

                var da = new NpgsqlDataAdapter($"SELECT * FROM {table}", conn);
                da.InsertCommand = new NpgsqlCommand(";", conn);
                da.UpdateCommand = new NpgsqlCommand($"UPDATE {table} 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 {table}", conn).ExecuteReader())
                {
                    dr2.Read();
                    Assert.AreEqual(4, dr2["field_int2"]);
                }
            }
        }

        [Test]
        public async Task FillWithDuplicateColumnName()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);

                var ds = new DataSet();
                var da = new NpgsqlDataAdapter($"SELECT field_serial, field_serial FROM {table}", conn);
                da.Fill(ds);
            }
        }

        [Test]
        [Ignore("")]
        public Task UpdateWithDataSet() => DoUpdateWithDataSet();

        public async Task DoUpdateWithDataSet()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);

                var command = new NpgsqlCommand($"insert into {table} (field_int2) values (2)", conn);
                command.ExecuteNonQuery();

                var ds = new DataSet();
                var da = new NpgsqlDataAdapter($"select * from {table}", 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 {table}", conn).ExecuteReader())
                {
                    dr2.Read();
                    Assert.AreEqual(4, dr2["field_int2"]);
                }
            }
        }

        [Test]
        [Ignore("")]
        public Task InsertWithCommandBuilderCaseSensitive()
            => DoInsertWithCommandBuilderCaseSensitive();

        public async Task DoInsertWithCommandBuilderCaseSensitive()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);

                var ds = new DataSet();
                var da = new NpgsqlDataAdapter($"select * from {table}", 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 {table}", conn).ExecuteReader())
                {
                    dr2.Read();
                    Assert.AreEqual(4, dr2[1]);
                }
            }
        }

        [Test]
        public async Task IntervalAsTimeSpan()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await GetTempTableName(conn, out var table);
                await conn.ExecuteNonQueryAsync($@"
CREATE TABLE {table} (
    pk SERIAL PRIMARY KEY,
    interval INTERVAL
);
INSERT INTO {table} (interval) VALUES ('1 hour'::INTERVAL);");

                var dt = new DataTable("data");
                var command = new NpgsqlCommand
                {
                    CommandType = CommandType.Text,
                    CommandText = $"SELECT interval FROM {table}",
                    Connection = conn
                };
                var da = new NpgsqlDataAdapter { SelectCommand = command };
                da.Fill(dt);
            }
        }

        [Test]
        public async Task IntervalAsTimeSpan2()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await GetTempTableName(conn, out var table);
                await conn.ExecuteNonQueryAsync($@"
CREATE TABLE {table} (
    pk SERIAL PRIMARY KEY,
    interval INTERVAL
);
INSERT INTO {table} (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 {table}";
                command.Connection = conn;
                var da = new NpgsqlDataAdapter();
                da.SelectCommand = command;
                da.Fill(dt);
            }
        }

        [Test]
        public async Task DbDataAdapterCommandAccess()
        {
            using (var conn = await OpenConnectionAsync())
            using (var command = new NpgsqlCommand("SELECT CAST('1 hour' AS interval) AS dauer", 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 async Task AutoPopulateAdapterCommands()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);

                var da = new NpgsqlDataAdapter($"SELECT field_pk,field_int4 FROM {table}", conn);
                var builder = new NpgsqlCommandBuilder(da);
                var ds = new DataSet();
                da.Fill(ds);

                var t = ds.Tables[0];
                var row = t.NewRow();
                row["field_pk"] = 1;
                row["field_int4"] = 8;
                t.Rows.Add(row);
                da.Update(ds);
                Assert.That(await conn.ExecuteScalarAsync($"SELECT field_int4 FROM {table}"), Is.EqualTo(8));

                row["field_int4"] = 9;
                da.Update(ds);
                Assert.That(await conn.ExecuteScalarAsync($"SELECT field_int4 FROM {table}"), Is.EqualTo(9));

                row.Delete();
                da.Update(ds);
                Assert.That(await conn.ExecuteScalarAsync($"SELECT COUNT(*) FROM {table}"), 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 async Task GetUpdateCommand()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await SetupTempTable(conn, out var table);

                using (var da = new NpgsqlDataAdapter($"SELECT field_pk, field_int4 FROM {table}", conn))
                {
                    using (var cb = new NpgsqlCommandBuilder(da))
                    {
                        var updateCommand = cb.GetUpdateCommand(true);
                        da.UpdateCommand = updateCommand;

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

                        var t = ds.Tables[0];
                        var row = t.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 async Task LoadDataTable()
        {
            using (var conn = await OpenConnectionAsync())
            {
                await using var _ = await CreateTempTable(conn, "char5 CHAR(5), varchar5 VARCHAR(5)", out var table);
                using (var command = new NpgsqlCommand($"SELECT char5, varchar5 FROM {table}", 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 Task<IAsyncDisposable> SetupTempTable(NpgsqlConnection conn, out string table)
            => CreateTempTable(conn, @"
field_pk SERIAL PRIMARY KEY,
field_serial SERIAL,
field_int2 SMALLINT,
field_int4 INTEGER,
field_numeric NUMERIC,
field_timestamp TIMESTAMP", out table);
    }
}