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

test/Npgsql.Tests/DataAdapterTests.cs
541 строка19 KB
Nino Floris
Re-enable many NonParallelizable and Ignore tests (#6339)
21 ноя 2025, 02:34
21 ноя 2025, 02:34eaa88b8
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 DataAdapter_SelectCommand()
    {
        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 DataAdapter_NpgsqlCommand_in_constructor()
    {
        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 DataAdapter_string_command_in_constructor()
    {
        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 DataAdapter_connection_string_in_constructor()
    {
        var da = new NpgsqlDataAdapter("SELECT 1", ConnectionString);
        var ds = new DataSet();
        da.Fill(ds);
        //ds.WriteXml("TestUseDataAdapterStringStringConstructor.xml");
    }

    [Test]
    public async Task Insert_with_DataSet()
    {
        using var conn = await OpenConnectionAsync();
        var table = await SetupTempTable(conn);
        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.DateTime2));
        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.That(dr2[0], Is.EqualTo(4));
        Assert.That(dr2[1], Is.EqualTo(7.3000000M));
        dr2.Close();
    }

    [Test]
    public async Task DataAdapter_update_return_value()
    {
        using var conn = await OpenConnectionAsync();
        var table = await SetupTempTable(conn);
        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.DateTime2));
        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.That(daupdate, Is.EqualTo(2));
    }

    [Test]
    public async Task DataAdapter_update_return_value2()
    {
        using var conn = await OpenConnectionAsync();
        var table = await SetupTempTable(conn);

        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_numeric = 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 row isn't updated in the database
        Assert.That(count, Is.EqualTo(1));
    }

    [Test]
    public async Task Fill_with_empty_resultset()
    {
        using var conn = await OpenConnectionAsync();
        var table = await SetupTempTable(conn);

        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.That(ds.Tables.Count, Is.EqualTo(1));
        Assert.That(ds.Tables[0].Columns.Count, Is.EqualTo(4));
        Assert.That(ds.Tables[0].Columns[0].ColumnName, Is.EqualTo("field_serial"));
        Assert.That(ds.Tables[0].Columns[1].ColumnName, Is.EqualTo("field_int2"));
        Assert.That(ds.Tables[0].Columns[2].ColumnName, Is.EqualTo("field_timestamp"));
        Assert.That(ds.Tables[0].Columns[3].ColumnName, Is.EqualTo("field_numeric"));
    }

    [Test]
    public async Task Fill_add_with_key()
    {
        using var conn = await OpenConnectionAsync();
        var table = await SetupTempTable(conn);

        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.That(field_serial.AllowDBNull, Is.False);
        Assert.That(field_serial.AutoIncrement);
        Assert.That(field_serial.ColumnName, Is.EqualTo("field_serial"));
        Assert.That(field_serial.DataType, Is.EqualTo(typeof(int)));
        Assert.That(field_serial.Ordinal, Is.EqualTo(0));
        Assert.That(field_serial.Unique, Is.False);

        Assert.That(field_int2.AllowDBNull);
        Assert.That(field_int2.AutoIncrement, Is.False);
        Assert.That(field_int2.ColumnName, Is.EqualTo("field_int2"));
        Assert.That(field_int2.DataType, Is.EqualTo(typeof(short)));
        Assert.That(field_int2.Ordinal, Is.EqualTo(1));
        Assert.That(field_int2.Unique, Is.False);

        Assert.That(field_timestamp.AllowDBNull);
        Assert.That(field_timestamp.AutoIncrement, Is.False);
        Assert.That(field_timestamp.ColumnName, Is.EqualTo("field_timestamp"));
        Assert.That(field_timestamp.DataType, Is.EqualTo(typeof(DateTime)));
        Assert.That(field_timestamp.Ordinal, Is.EqualTo(2));
        Assert.That(field_timestamp.Unique, Is.False);

        Assert.That(field_numeric.AllowDBNull);
        Assert.That(field_numeric.AutoIncrement, Is.False);
        Assert.That(field_numeric.ColumnName, Is.EqualTo("field_numeric"));
        Assert.That(field_numeric.DataType, Is.EqualTo(typeof(decimal)));
        Assert.That(field_numeric.Ordinal, Is.EqualTo(3));
        Assert.That(field_numeric.Unique, Is.False);
    }

    [Test]
    public async Task Fill_add_columns()
    {
        using var conn = await OpenConnectionAsync();
        var table = await SetupTempTable(conn);

        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.That(field_serial.ColumnName, Is.EqualTo("field_serial"));
        Assert.That(field_serial.DataType, Is.EqualTo(typeof(int)));
        Assert.That(field_serial.Ordinal, Is.EqualTo(0));

        Assert.That(field_int2.ColumnName, Is.EqualTo("field_int2"));
        Assert.That(field_int2.DataType, Is.EqualTo(typeof(short)));
        Assert.That(field_int2.Ordinal, Is.EqualTo(1));

        Assert.That(field_timestamp.ColumnName, Is.EqualTo("field_timestamp"));
        Assert.That(field_timestamp.DataType, Is.EqualTo(typeof(DateTime)));
        Assert.That(field_timestamp.Ordinal, Is.EqualTo(2));

        Assert.That(field_numeric.ColumnName, Is.EqualTo("field_numeric"));
        Assert.That(field_numeric.DataType, Is.EqualTo(typeof(decimal)));
        Assert.That(field_numeric.Ordinal, Is.EqualTo(3));
    }

    [Test]
    public async Task Update_letting_null_field_falue()
    {
        using var conn = await OpenConnectionAsync();
        var table = await SetupTempTable(conn);

        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.That(dt, Is.Not.Null);

        var dr = ds.Tables[0].Rows[^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.That(dr2["field_int2"], Is.EqualTo(4));
    }

    [Test]
    public async Task Fill_with_duplicate_column_name()
    {
        using var conn = await OpenConnectionAsync();
        var table = await SetupTempTable(conn);

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

    [Test]
    public Task Update_with_DataSet() => DoUpdateWithDataSet();

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

        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.That(cb, Is.Not.Null);

        da.Fill(ds);

        var dt = ds.Tables[0];
        Assert.That(dt, Is.Not.Null);

        var dr = ds.Tables[0].Rows[^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.That(dr2["field_int2"], Is.EqualTo(4));
    }

    [Test]
    public async Task Insert_with_CommandBuilder_case_sensitive()
    {
        using var conn = await OpenConnectionAsync();
        var table = await SetupTempTable(conn);

        var ds = new DataSet();
        var da = new NpgsqlDataAdapter($"select * from {table}", conn);
        var builder = new NpgsqlCommandBuilder(da);
        Assert.That(builder, Is.Not.Null);

        da.Fill(ds);

        var dt = ds.Tables[0];
        var dr = dt.NewRow();
        dr["Field_int4"] = 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.That(dr2["field_int4"], Is.EqualTo(4));
    }

    [Test]
    public async Task Interval_as_TimeSpan()
    {
        using var conn = await OpenConnectionAsync();
        var table = await GetTempTableName(conn);
        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 Interval_as_TimeSpan2()
    {
        using var conn = await OpenConnectionAsync();
        var table = await GetTempTableName(conn);
        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 DataAdapter_command_access()
    {
        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.That(common.SelectCommand, Is.Not.Null);
    }

    [Test, Description("Makes sure that the INSERT/UPDATE/DELETE commands are auto-populated on NpgsqlDataAdapter")]
    [IssueLink("https://github.com/npgsql/npgsql/issues/179")]
    public async Task Auto_populate_adapter_commands()
    {
        using var conn = await OpenConnectionAsync();
        var table = await SetupTempTable(conn);

        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 Command_builder_quoting()
    {
        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")]
    public async Task Get_UpdateCommand()
    {
        using var conn = await OpenConnectionAsync();
        var table = await SetupTempTable(conn);

        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 Load_DataTable()
    {
        using var conn = await OpenConnectionAsync();
        var table = await CreateTempTable(conn, "char5 CHAR(5), varchar5 VARCHAR(5)");
        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.That(dt.Columns[0].MaxLength, Is.EqualTo(5));
        Assert.That(dt.Columns[1].MaxLength, Is.EqualTo(5));
    }

    public Task<string> SetupTempTable(NpgsqlConnection conn)
        => CreateTempTable(conn, @"
field_pk INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
field_serial SERIAL,
field_int2 SMALLINT,
field_int4 INTEGER,
field_numeric NUMERIC,
field_timestamp TIMESTAMP");
}