public void LateMapping()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TYPE pg_temp.composite3 AS (x int, some_text text)");
conn.ReloadTypes();
conn.MapComposite <SomeComposite>("composite3");
var expected = new SomeComposite {
x = 8, SomeText = "foo"
};
using (var cmd = new NpgsqlCommand("SELECT @p1::composite3, @p2::composite3", conn))
{
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlDbType.Composite)
{
Value = expected,
SpecificType = typeof(SomeComposite)
});
cmd.Parameters.AddWithValue("p2", expected);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
var actual = reader.GetFieldValue <SomeComposite>(i);
Assert.That(actual.x, Is.EqualTo(8));
Assert.That(actual.SomeText, Is.EqualTo("foo"));
}
}
}
}
}
public void DeriveTextCommandParameters_UnmappedComposite()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TYPE pg_temp.deriveparameterscomposite2 AS (x int, some_text text)");
conn.ReloadTypes();
var expected1 = new SomeComposite {
X = 8, SomeText = "foo"
};
using (var cmd = new NpgsqlCommand("SELECT @p1::deriveparameterscomposite2", conn))
{
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(1));
Assert.That(cmd.Parameters[0].ParameterName, Is.EqualTo("p1"));
Assert.That(cmd.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Unknown));
Assert.That(cmd.Parameters[0].PostgresType, Is.InstanceOf <PostgresCompositeType>());
Assert.That(cmd.Parameters[0].DataTypeName, Does.EndWith("deriveparameterscomposite2"));
var p1Fields = ((PostgresCompositeType)cmd.Parameters[0].PostgresType).Fields;
Assert.That(p1Fields[0].Name, Is.EqualTo("x"));
Assert.That(p1Fields[1].Name, Is.EqualTo("some_text"));
cmd.Parameters[0].Value = expected1;
using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SingleRow))
{
Assert.That(reader.Read(), Is.True);
Assert.That(reader.GetFieldValue <SomeComposite>(0).SomeText, Is.EqualTo(expected1.SomeText));
Assert.That(reader.GetFieldValue <SomeComposite>(0).X, Is.EqualTo(expected1.X));
}
}
}
}
public void LateMapping()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(LateMapping),
Pooling = false
};
using var conn = OpenConnection(csb);
conn.ExecuteNonQuery("CREATE TYPE pg_temp.composite3 AS (x int, some_text text)");
conn.ReloadTypes();
conn.TypeMapper.MapComposite <SomeComposite>("composite3");
var expected = new SomeComposite {
X = 8, SomeText = "foo"
};
using var cmd = new NpgsqlCommand("SELECT @p1::composite3, @p2::composite3", conn);
cmd.Parameters.Add(new NpgsqlParameter
{
ParameterName = "p1",
DataTypeName = "composite3",
Value = expected
});
cmd.Parameters.AddWithValue("p2", expected);
using var reader = cmd.ExecuteReader();
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
var actual = reader.GetFieldValue <SomeComposite>(i);
Assert.That(actual.X, Is.EqualTo(8));
Assert.That(actual.SomeText, Is.EqualTo("foo"));
}
}
public async Task DeriveParameters_text_mapped_composite()
{
using var conn = await OpenConnectionAsync();
await using var _ = await GetTempTypeName(conn, out var type);
await conn.ExecuteNonQueryAsync($"CREATE TYPE {type} AS (x int, some_text text)");
conn.ReloadTypes();
conn.TypeMapper.MapComposite <SomeComposite>(type);
var expected1 = new SomeComposite {
X = 8, SomeText = "foo"
};
var expected2 = new[] {
expected1,
new SomeComposite {
X = 9, SomeText = "bar"
}
};
using var cmd = new NpgsqlCommand($"SELECT @p1::{type}, @p2::{type}[]", conn);
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(2));
Assert.That(cmd.Parameters[0].ParameterName, Is.EqualTo("p1"));
Assert.That(cmd.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Unknown));
Assert.That(cmd.Parameters[0].PostgresType, Is.InstanceOf <PostgresCompositeType>());
Assert.That(cmd.Parameters[0].DataTypeName, Does.EndWith(type));
var p1Fields = ((PostgresCompositeType)cmd.Parameters[0].PostgresType !).Fields;
Assert.That(p1Fields[0].Name, Is.EqualTo("x"));
Assert.That(p1Fields[1].Name, Is.EqualTo("some_text"));
Assert.That(cmd.Parameters[1].ParameterName, Is.EqualTo("p2"));
Assert.That(cmd.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Unknown));
Assert.That(cmd.Parameters[1].PostgresType, Is.InstanceOf <PostgresArrayType>());
Assert.That(cmd.Parameters[1].DataTypeName, Does.EndWith(type + "[]"));
var p2Element = ((PostgresArrayType)cmd.Parameters[1].PostgresType !).Element;
Assert.That(p2Element, Is.InstanceOf <PostgresCompositeType>());
Assert.That(p2Element.Name, Is.EqualTo(type));
var p2Fields = ((PostgresCompositeType)p2Element).Fields;
Assert.That(p2Fields[0].Name, Is.EqualTo("x"));
Assert.That(p2Fields[1].Name, Is.EqualTo("some_text"));
cmd.Parameters[0].Value = expected1;
cmd.Parameters[1].Value = expected2;
using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult | CommandBehavior.SingleRow);
Assert.That(reader.Read(), Is.True);
Assert.That(reader.GetFieldValue <SomeComposite>(0).SomeText, Is.EqualTo(expected1.SomeText));
Assert.That(reader.GetFieldValue <SomeComposite>(0).X, Is.EqualTo(expected1.X));
for (var i = 0; i < 2; i++)
{
Assert.That(reader.GetFieldValue <SomeComposite[]>(1)[i].SomeText, Is.EqualTo(expected2[i].SomeText));
Assert.That(reader.GetFieldValue <SomeComposite[]>(1)[i].X, Is.EqualTo(expected2[i].X));
}
}
public void DeriveTextCommandParameters_MappedComposite()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TYPE pg_temp.deriveparameterscomposite1 AS (x int, some_text text)");
conn.ReloadTypes();
conn.TypeMapper.MapComposite <SomeComposite>("deriveparameterscomposite1");
var expected1 = new SomeComposite {
X = 8, SomeText = "foo"
};
var expected2 = new[] {
expected1,
new SomeComposite {
X = 9, SomeText = "bar"
}
};
using (var cmd = new NpgsqlCommand("SELECT @p1::deriveparameterscomposite1, @p2::deriveparameterscomposite1[]", conn))
{
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(2));
Assert.That(cmd.Parameters[0].ParameterName, Is.EqualTo("p1"));
Assert.That(cmd.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Unknown));
Assert.That(cmd.Parameters[0].PostgresType, Is.InstanceOf <PostgresCompositeType>());
Assert.That(cmd.Parameters[0].DataTypeName, Does.EndWith("deriveparameterscomposite1"));
var p1Fields = ((PostgresCompositeType)cmd.Parameters[0].PostgresType).Fields;
Assert.That(p1Fields[0].Name, Is.EqualTo("x"));
Assert.That(p1Fields[1].Name, Is.EqualTo("some_text"));
Assert.That(cmd.Parameters[1].ParameterName, Is.EqualTo("p2"));
Assert.That(cmd.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Unknown));
Assert.That(cmd.Parameters[1].PostgresType, Is.InstanceOf <PostgresArrayType>());
Assert.That(cmd.Parameters[1].DataTypeName, Does.EndWith("deriveparameterscomposite1[]"));
var p2Element = ((PostgresArrayType)cmd.Parameters[1].PostgresType).Element;
Assert.That(p2Element, Is.InstanceOf <PostgresCompositeType>());
Assert.That(p2Element.Name, Is.EqualTo("deriveparameterscomposite1"));
var p2Fields = ((PostgresCompositeType)p2Element).Fields;
Assert.That(p2Fields[0].Name, Is.EqualTo("x"));
Assert.That(p2Fields[1].Name, Is.EqualTo("some_text"));
cmd.Parameters[0].Value = expected1;
cmd.Parameters[1].Value = expected2;
using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SingleRow))
{
Assert.That(reader.Read(), Is.True);
Assert.That(reader.GetFieldValue <SomeComposite>(0).SomeText, Is.EqualTo(expected1.SomeText));
Assert.That(reader.GetFieldValue <SomeComposite>(0).X, Is.EqualTo(expected1.X));
for (var i = 0; i < 2; i++)
{
Assert.That(reader.GetFieldValue <SomeComposite[]>(1)[i].SomeText, Is.EqualTo(expected2[i].SomeText));
Assert.That(reader.GetFieldValue <SomeComposite[]>(1)[i].X, Is.EqualTo(expected2[i].X));
}
}
}
}
}
public void GlobalMapping()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(LateMapping),
Pooling = false
};
try
{
using (var conn = OpenConnection(csb))
{
conn.ExecuteNonQuery("DROP TYPE IF EXISTS composite4");
conn.ExecuteNonQuery("CREATE TYPE composite4 AS (x int, some_text text)");
NpgsqlConnection.GlobalTypeMapper.MapComposite <SomeComposite>("composite4");
conn.ReloadTypes();
var expected = new SomeComposite {
X = 8, SomeText = "foo"
};
using (var cmd = new NpgsqlCommand($"SELECT @p::composite4", conn))
{
cmd.Parameters.AddWithValue("p", expected);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
var actual = reader.GetFieldValue <SomeComposite>(0);
Assert.That(actual.X, Is.EqualTo(8));
Assert.That(actual.SomeText, Is.EqualTo("foo"));
}
}
}
// Unmap
NpgsqlConnection.GlobalTypeMapper.UnmapComposite <SomeComposite>("composite4");
using (var conn = OpenConnection(csb))
{
// Composite should have been unmapped and so will return as dynamic
Assert.That(conn.ExecuteScalar("SELECT '(8, \"foo\")'::composite4"), Is.TypeOf <ExpandoObject>());
}
}
finally
{
using (var conn = OpenConnection(csb))
conn.ExecuteNonQuery("DROP TYPE IF EXISTS composite4");
}
}
public void GlobalMapping()
{
try
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("DROP TYPE IF EXISTS composite4");
conn.ExecuteNonQuery("CREATE TYPE composite4 AS (x int, some_text text)");
conn.ReloadTypes();
}
NpgsqlConnection.MapCompositeGlobally <SomeComposite>("composite4");
using (var conn = OpenConnection())
{
var expected = new SomeComposite {
x = 8, SomeText = "foo"
};
using (var cmd = new NpgsqlCommand($"SELECT @p::composite4", conn))
{
cmd.Parameters.AddWithValue("p", expected);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
var actual = reader.GetFieldValue <SomeComposite>(0);
Assert.That(actual.x, Is.EqualTo(8));
Assert.That(actual.SomeText, Is.EqualTo("foo"));
}
}
}
}
finally
{
NpgsqlConnection.UnmapCompositeGlobally <SomeComposite>("composite4");
using (var conn = OpenConnection())
conn.ExecuteNonQuery("DROP TYPE IF EXISTS composite4");
}
}
public void GlobalMapping()
{
try
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("DROP TYPE IF EXISTS composite4");
conn.ExecuteNonQuery("CREATE TYPE composite4 AS (x int, some_text text)");
conn.ReloadTypes();
}
NpgsqlConnection.MapCompositeGlobally<SomeComposite>("composite4");
using (var conn = OpenConnection())
{
var expected = new SomeComposite { x = 8, SomeText = "foo" };
using (var cmd = new NpgsqlCommand($"SELECT @p::composite4", conn))
{
cmd.Parameters.AddWithValue("p", expected);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
var actual = reader.GetFieldValue<SomeComposite>(0);
Assert.That(actual.x, Is.EqualTo(8));
Assert.That(actual.SomeText, Is.EqualTo("foo"));
}
}
}
}
finally
{
NpgsqlConnection.UnmapCompositeGlobally<SomeComposite>("composite4");
using (var conn = OpenConnection())
conn.ExecuteNonQuery("DROP TYPE IF EXISTS composite4");
}
}
public void LateMapping()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TYPE pg_temp.composite3 AS (x int, some_text text)");
conn.ReloadTypes();
conn.MapComposite<SomeComposite>("composite3");
var expected = new SomeComposite {x = 8, SomeText = "foo"};
using (var cmd = new NpgsqlCommand("SELECT @p1::composite3, @p2::composite3", conn))
{
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlDbType.Composite) {
Value = expected,
SpecificType = typeof (SomeComposite)
});
cmd.Parameters.AddWithValue("p2", expected);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
var actual = reader.GetFieldValue<SomeComposite>(i);
Assert.That(actual.x, Is.EqualTo(8));
Assert.That(actual.SomeText, Is.EqualTo("foo"));
}
}
}
}
}
public void UnmappedComposite()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(UnmappedComposite),
Pooling = false
};
using (var conn = OpenConnection(csb))
{
conn.ExecuteNonQuery("CREATE TYPE pg_temp.unmapped_comp AS (x int, some_text text)");
conn.ReloadTypes();
var tempSchema = conn.ExecuteScalar("SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema()");
var composite = new SomeComposite {
X = 8, SomeText = "foo"
};
var expando = (dynamic) new ExpandoObject();
expando.X = 8;
expando.SomeText = "foo";
var compositeArray = new[]
{
new SomeComposite {
X = 9, SomeText = "bar"
},
new SomeComposite {
X = 10, SomeText = "baz"
}
};
var expandoArray = new dynamic[2];
expandoArray[0] = new ExpandoObject();
expandoArray[0].x = 9;
expandoArray[0].some_text = "bar";
expandoArray[1] = new ExpandoObject();
expandoArray[1].x = 10;
expandoArray[1].some_text = "baz";
using (var cmd = new NpgsqlCommand("SELECT @scalar1, @scalar2, @scalar3, @scalar4, @array1, @array2", conn))
{
cmd.Parameters.Add(new NpgsqlParameter
{
ParameterName = "scalar1",
Value = composite,
DataTypeName = $"{tempSchema}.unmapped_comp"
});
cmd.Parameters.Add(new NpgsqlParameter
{
ParameterName = "scalar2",
Value = expando,
DataTypeName = $"{tempSchema}.unmapped_comp"
});
cmd.Parameters.Add(new NpgsqlParameter <SomeComposite>
{
ParameterName = "scalar3",
TypedValue = composite,
DataTypeName = $"{tempSchema}.unmapped_comp"
});
cmd.Parameters.Add(new NpgsqlParameter <dynamic>
{
ParameterName = "scalar4",
TypedValue = expando,
DataTypeName = $"{tempSchema}.unmapped_comp"
});
cmd.Parameters.Add(new NpgsqlParameter
{
ParameterName = "array1",
Value = compositeArray,
DataTypeName = $"{tempSchema}.unmapped_comp[]"
});
cmd.Parameters.Add(new NpgsqlParameter
{
ParameterName = "array2",
Value = expandoArray,
DataTypeName = $"{tempSchema}.unmapped_comp[]"
});
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < 4; i++)
{
Assert.That(reader.GetDataTypeName(i),
Does.StartWith("pg_temp") & Does.EndWith(".unmapped_comp"));
var asTyped = reader.GetFieldValue <SomeComposite>(i);
Assert.That(asTyped.X, Is.EqualTo(8));
Assert.That(asTyped.SomeText, Is.EqualTo("foo"));
// TODO: Need name translation...
var asDynamic = (dynamic)reader.GetValue(i);
Assert.That(asDynamic.x, Is.EqualTo(8));
Assert.That(asDynamic.some_text, Is.EqualTo("foo"));
}
for (var i = 4; i < 6; i++)
{
Assert.That(reader.GetDataTypeName(i),
Does.StartWith("pg_temp") & Does.EndWith(".unmapped_comp[]"));
// TODO: The following doesn't work because of limitations in ArrayHandler.
// You currently have to map the composite in order to read an array.
/*
* var asTyped = reader.GetFieldValue<SomeComposite[]>(i);
* Assert.That(asTyped[0].X, Is.EqualTo(9));
* Assert.That(asTyped[0].SomeText, Is.EqualTo("bar"));
* Assert.That(asTyped[1].X, Is.EqualTo(10));
* Assert.That(asTyped[1].SomeText, Is.EqualTo("baz"));
*
* // TODO: Need name translation...
* var asDynamic = (dynamic[])reader.GetValue(i);
* Assert.That(asDynamic[0].x, Is.EqualTo(9));
* Assert.That(asDynamic[0].some_text, Is.EqualTo("bar"));
* Assert.That(asDynamic[1].x, Is.EqualTo(10));
* Assert.That(asDynamic[1].some_text, Is.EqualTo("baz"));
*/
}
}
}
}
}
