public void TestObjectToJsonbSqlParameter()
{
try
{
var input = new TestData()
{
X = 1, Z = 2
};
var users = new UsersWithTestData()
{
Id = 1, JsonData = input
};
_connection.ExecuteSql(@"CREATE TABLE Users (Id integer NOT NULL, JsonData jsonb)");
_connection.ExecuteSql("INSERT INTO Users (Id, JsonData) VALUES (@Id, @JsonData)", users);
var result = _connection.QuerySql <UsersWithTestData>(@"SELECT Users.* FROM Users").First();
Assert.AreEqual(input.X, result.JsonData.X);
Assert.AreEqual(input.Z, result.JsonData.Z);
}
finally
{
Cleanup("DROP TABLE Users");
}
}
public void TestObjectToJsonbProcParameters()
{
try
{
var input = new TestData()
{
X = 1, Z = 2
};
var users = new UsersWithTestData()
{
Id = 1, JsonData = input
};
_connection.ExecuteSql(@"CREATE TABLE Users (Id integer NOT NULL, JsonData jsonb)");
_connection.ExecuteSql("CREATE TYPE PostgreSQLTestType AS (Id integer, JsonData jsonb)");
_connection.ExecuteSql(@"
CREATE OR REPLACE FUNCTION TestObjectToJsonbProcParameters (Id integer, JsonData jsonb)
RETURNS SETOF PostgreSQLTestType
AS $$
BEGIN
RETURN QUERY SELECT id as id, JsonData as JsonData;
END;
$$ LANGUAGE plpgsql;" );
var result = _connection.Query <UsersWithTestData>("TestObjectToJsonbProcParameters", users).First();
Assert.AreEqual(input.X, result.JsonData.X);
Assert.AreEqual(input.Z, result.JsonData.Z);
}
finally
{
Cleanup("DROP FUNCTION TestObjectToJsonbProcParameters (Id integer, JsonData jsonb) ");
Cleanup("DROP TYPE PostgreSQLTestType");
Cleanup("DROP TABLE Users");
}
}
public void TestJsonQueryParameter()
{
try
{
var input = new TestData()
{
X = 1, Z = 2, Text = "MyText"
};
var users = new UsersWithTestData()
{
Id = 1, JsonData = input
};
_connection.ExecuteSql(@"CREATE TABLE Users (Id integer NOT NULL, JsonData jsonb)");
_connection.ExecuteSql("INSERT INTO Users (Id, JsonData) VALUES (@Id, @JsonData)", users);
var result = _connection.QuerySql <UsersWithTestData>(
// "SELECT Users.* FROM Users WHERE JsonData @> '{ \"Text\": \"MyText\" }'",
"SELECT Users.* FROM Users WHERE JsonData @> ('{ \"Text\": \"' || @Text || '\" }')::jsonb",
new { Text = "MyText" }).FirstOrDefault();
Assert.AreEqual(input.X, result.JsonData.X);
Assert.AreEqual(input.Z, result.JsonData.Z);
}
finally
{
Cleanup("DROP TABLE Users");
}
}
public void TestObjectToJsonbSqlParameter()
{
try
{
var input = new TestData() { X = 1, Z = 2 };
var users = new UsersWithTestData() { Id = 1, JsonData = input };
_connection.ExecuteSql(@"CREATE TABLE Users (Id integer NOT NULL, JsonData jsonb)");
_connection.ExecuteSql("INSERT INTO Users (Id, JsonData) VALUES (@Id, @JsonData)", users);
var result = _connection.QuerySql<UsersWithTestData>(@"SELECT Users.* FROM Users").First();
Assert.AreEqual(input.X, result.JsonData.X);
Assert.AreEqual(input.Z, result.JsonData.Z);
}
finally
{
Cleanup("DROP TABLE Users");
}
}
public void TestObjectToJsonbProcParameters()
{
try
{
var input = new TestData() { X = 1, Z = 2 };
var users = new UsersWithTestData() { Id = 1, JsonData = input };
_connection.ExecuteSql(@"CREATE TABLE Users (Id integer NOT NULL, JsonData jsonb)");
_connection.ExecuteSql("CREATE TYPE PostgreSQLTestType AS (Id integer, JsonData jsonb)");
_connection.ExecuteSql(@"
CREATE OR REPLACE FUNCTION PostgreSQLTestExecute (Id integer, JsonData jsonb)
RETURNS SETOF PostgreSQLTestType
AS $$
BEGIN
RETURN QUERY SELECT id as id, JsonData as JsonData;
END;
$$ LANGUAGE plpgsql;");
var result = _connection.Query<UsersWithTestData>("PostgreSQLTestExecute", users).First();
Assert.AreEqual(input.X, result.JsonData.X);
Assert.AreEqual(input.Z, result.JsonData.Z);
}
finally
{
Cleanup("DROP FUNCTION PostgreSQLTestExecute (Id integer, JsonData jsonb) ");
Cleanup("DROP TYPE PostgreSQLTestType");
Cleanup("DROP TABLE Users");
}
}
