public void TestXmlTypes()
{
try
{
_connection.ExecuteSql(@"
CREATE OR REPLACE PROCEDURE OracleXmlTableProc (id int, testdata xmltype, r out sys_refcursor)
IS
BEGIN
OPEN r FOR SELECT id as id, testdata as testdata FROM dual;
END;" );
var testData = new TestData()
{
X = 9, Z = 13
};
var parentTestData = new ParentTestData()
{
ID = 1, TestData = testData
};
Assert.Throws <OracleException>(() => _connection.Query <ParentTestData, TestData>("OracleXmlTableProc", parentTestData));
}
finally
{
try { _connection.ExecuteSql("DROP PROCEDURE OracleXmlTableProc"); } catch {}
}
}
public SearchChildrenForStringTests()
{
_dataOne = new TestData {
Name = "chris", Description = "child data", Number = 1, Age = 20
};
_dataTwo = new TestData {
Name = "fred", Description = "nested positionly", Number = 6, Age = 30
};
_dataThree = new TestData {
Name = "teddy", Description = "children description", Number = 2, Age = 40
};
_dataFour = new TestData {
Name = "josh", Description = "nested data", Number = 20, Age = 50
};
_parent = new ParentTestData
{
Children = new List <TestData> {
_dataOne, _dataTwo
},
};
_otherParent = new ParentTestData
{
Children = new List <TestData> {
_dataThree, _dataFour
},
};
_testData = new List <ParentTestData> {
_parent, _otherParent
};
}
public void TestXmlTypes()
{
try
{
_connection.ExecuteSql(@"
CREATE OR REPLACE PROCEDURE DB2XmlTableProc (id int, testdata xml)
RESULT SET 1
BEGIN
DECLARE c CURSOR WITH RETURN FOR
SELECT id as id, testdata as testdata FROM SYSIBM.dual;
OPEN c;
END" );
var testData = new TestData()
{
X = 9, Z = 13
};
var parentTestData = new ParentTestData()
{
ID = 1, TestData = testData
};
var results = _connection.Query <ParentTestData, TestData>("DB2XmlTableProc", parentTestData);
var resultParent = results[0];
Assert.AreEqual(parentTestData.ID, resultParent.ID);
Assert.IsNotNull(resultParent.TestData);
Assert.AreEqual(parentTestData.TestData.X, resultParent.TestData.X);
Assert.AreEqual(parentTestData.TestData.Z, resultParent.TestData.Z);
}
finally
{
try { _connection.ExecuteSql("DROP PROCEDURE DB2XmlTableProc"); }
catch { }
}
}
public void TestXmlTypes()
{
try
{
_connection.ExecuteSql(@"
CREATE OR REPLACE PROCEDURE OracleXmlTableProc (id int, testdata xmltype, r out sys_refcursor)
IS
BEGIN
OPEN r FOR SELECT id as id, testdata as testdata FROM dual;
END;" );
var testData = new TestData()
{
X = 9, Z = 13
};
var parentTestData = new ParentTestData()
{
ID = 1, TestData = testData
};
var results = _connection.Query <ParentTestData, TestData>("OracleXmlTableProc", parentTestData);
var resultParent = results[0];
Assert.AreEqual(parentTestData.ID, resultParent.ID);
Assert.IsNotNull(resultParent.TestData);
Assert.AreEqual(parentTestData.TestData.X, resultParent.TestData.X);
Assert.AreEqual(parentTestData.TestData.Z, resultParent.TestData.Z);
}
finally
{
try { _connection.ExecuteSql("DROP PROCEDURE OracleXmlTableProc"); } catch {}
}
}
public void SetUp()
{
this._dataOne = new TestData {
Name = "chris", Description = "child data", Number = 1, Age = 60
};
this._dataTwo = new TestData {
Name = "fred", Description = "child", Number = 20, Age = 30
};
this._dataThree = new TestData {
Name = "teddy", Description = "data", Number = 2, Age = 40
};
this._dataFour = new TestData {
Name = "josh", Description = "child data", Number = 20, Age = 50
};
this._parent = new ParentTestData
{
Children = new List <TestData> {
this._dataOne, this._dataTwo
},
};
this._otherParent = new ParentTestData
{
Children = new List <TestData> {
this._dataThree, this._dataFour
},
};
this._testData = new List <ParentTestData> {
this._parent, this._otherParent
};
}
public void TestBulkLoad()
{
try
{
// NOTE: I have not been able to get the xmltype to bulk copy. It throws "unsupported column datatype".
// Insight will store the value of the object as a string in the bulk copy
//_connection.ExecuteSql("CREATE TABLE InsightTestData (ID int, Dec Decimal, TestData xmltype)");
_connection.ExecuteSql("CREATE TABLE InsightTestData (ID int, Dec Decimal)");
for (int i = 0; i < 3; i++)
{
// build test data
ParentTestData[] array = new ParentTestData[i];
for (int j = 0; j < i; j++)
{
array[j] = new ParentTestData()
{
ID = j, Dec = j, TestData = new TestData()
}
}
;
// bulk load the data
_connection.BulkCopy("InsightTestData", array, configure: bulkCopy => bulkCopy.BatchSize = 10);
// run the query
var items = _connection.QuerySql <ParentTestData>("SELECT * FROM InsightTestData");
Assert.IsNotNull(items);
Assert.AreEqual(i, items.Count);
for (int j = 0; j < i; j++)
{
Assert.AreEqual(j, items[j].ID);
Assert.AreEqual(j, items[j].Dec);
}
_connection.ExecuteSql("DELETE FROM InsightTestData");
}
}
finally
{
try { _connection.ExecuteSql("DROP TABLE InsightTestData"); } catch { }
}
}
public void TestBulkLoad()
{
try
{
_connection.ExecuteSql("CREATE TABLE InsightTestData (ID int, Dec Decimal, TestData xml)");
for (int i = 0; i < 3; i++)
{
// build test data
ParentTestData[] array = new ParentTestData[i];
for (int j = 0; j < i; j++)
{
array[j] = new ParentTestData()
{
ID = j, Dec = j, TestData = new TestData()
}
}
;
// bulk load the data
_connection.BulkCopy("InsightTestData", array, configure: bulkCopy => bulkCopy.BatchSize = 10);
// run the query
var items = _connection.QuerySql <ParentTestData>("SELECT * FROM InsightTestData");
Assert.IsNotNull(items);
Assert.AreEqual(i, items.Count);
for (int j = 0; j < i; j++)
{
Assert.AreEqual(j, items[j].ID);
Assert.AreEqual(j, items[j].Dec);
}
_connection.ExecuteSql("DELETE FROM InsightTestData");
}
}
finally
{
try { _connection.ExecuteSql("DROP TABLE InsightTestData"); }
catch { }
}
}
private void BuildTestData()
{
_testData.Add(new TestData {
Name = "aaa"
});
_testData.Add(new TestData {
Name = "bbb"
});
_testData.Add(new TestData {
Name = "ccc"
});
_testData.Add(new TestData {
Name = "ddd"
});
var parent1 = new ParentTestData {
Children = new List <TestData>()
};
var parent2 = new ParentTestData {
Children = new List <TestData>()
};
parent1.Children.Add(new TestData {
Name = "aaa"
});
parent1.Children.Add(new TestData {
Name = "bbb"
});
parent2.Children.Add(new TestData {
Name = "ccc"
});
parent2.Children.Add(new TestData {
Name = "ddd"
});
_parentData.Add(parent1);
_parentData.Add(parent2);
}
public void SetUp()
{
_dataOne = new TestData {
Name = "chris", Description = "child data", Number = 1, Age = 20
};
_dataTwo = new TestData {
Name = "fred", Description = "child data", Number = 6, Age = 30
};
_dataThree = new TestData {
Name = "teddy", Description = "child data", Number = 2, Age = 40
};
_dataFour = new TestData {
Name = "josh", Description = "child data", Number = 20, Age = 50
};
_parent = new ParentTestData
{
Children = new List <TestData> {
_dataOne, _dataTwo
},
OtherChildren = new List <TestData> {
_dataThree, _dataFour
}
};
_otherParent = new ParentTestData
{
Children = new List <TestData> {
_dataThree, _dataFour
},
OtherChildren = new List <TestData> {
_dataOne, _dataTwo
}
};
_testData = new List <ParentTestData> {
_parent, _otherParent
};
}
public void TestXmlTypes()
{
try
{
_connection.ExecuteSql("CREATE TYPE PostgreSQLTestXmlType AS (id int, testdata xml)");
_connection.ExecuteSql(@"
CREATE OR REPLACE FUNCTION XmlTableProc (id int, testdata xml)
RETURNS SETOF PostgreSQLTestXmlType
AS $$
BEGIN
RETURN QUERY SELECT id as id, testdata as testdata;
END;
$$ LANGUAGE plpgsql;" );
var testData = new TestData()
{
X = 9, Z = 13
};
var parentTestData = new ParentTestData()
{
ID = 1, TestData = testData
};
var results = _connection.Query <ParentTestData, TestData>("XmlTableProc", parentTestData);
var resultParent = results[0];
Assert.AreEqual(parentTestData.ID, resultParent.ID);
Assert.IsNotNull(resultParent.TestData);
Assert.AreEqual(parentTestData.TestData.X, resultParent.TestData.X);
Assert.AreEqual(parentTestData.TestData.Z, resultParent.TestData.Z);
}
finally
{
try { _connection.ExecuteSql("DROP FUNCTION XmlTableProc (id int, testdata xml)"); } catch { }
try { _connection.ExecuteSql("DROP TYPE PostgreSQLTestXmlType"); } catch { }
}
}
public void TestBulkLoad()
{
try
{
_connection.ExecuteSql("CREATE TABLE InsightTestData (ID int, Dec Decimal)");
for (int i = 0; i < 3; i++)
{
// build test data
ParentTestData[] array = new ParentTestData[i];
for (int j = 0; j < i; j++)
array[j] = new ParentTestData() { ID = j, Dec = j, TestData = new TestData() };
// bulk load the data
_connection.BulkCopy("InsightTestData", array, configure: bulkCopy => bulkCopy.BatchSize = 10);
// run the query
var items = _connection.QuerySql<ParentTestData>("SELECT * FROM InsightTestData");
Assert.IsNotNull(items);
Assert.AreEqual(i, items.Count);
for (int j = 0; j < i; j++)
{
Assert.AreEqual(j, items[j].ID);
Assert.AreEqual(j, items[j].Dec);
}
_connection.ExecuteSql("DELETE FROM InsightTestData");
}
}
finally
{
try { _connection.ExecuteSql("DROP TABLE InsightTestData"); }
catch { }
}
}
public void TestXmlTypes()
{
try
{
_connection.ExecuteSql(@"
CREATE OR REPLACE PROCEDURE DB2XmlTableProc (id int, testdata xml)
RESULT SET 1
BEGIN
DECLARE c CURSOR WITH RETURN FOR
SELECT id as id, testdata as testdata FROM SYSIBM.dual;
OPEN c;
END");
var testData = new TestData() { X = 9, Z = 13 };
var parentTestData = new ParentTestData() { ID = 1, TestData = testData };
var results = _connection.Query<ParentTestData, TestData>("DB2XmlTableProc", parentTestData);
var resultParent = results[0];
Assert.AreEqual(parentTestData.ID, resultParent.ID);
Assert.IsNotNull(resultParent.TestData);
Assert.AreEqual(parentTestData.TestData.X, resultParent.TestData.X);
Assert.AreEqual(parentTestData.TestData.Z, resultParent.TestData.Z);
}
finally
{
try { _connection.ExecuteSql("DROP PROCEDURE DB2XmlTableProc"); }
catch { }
}
}
public void SetUp()
{
this._dataOne = new TestData {Name = "chris", Description = "child data", Number = 1, Age = 20};
this._dataTwo = new TestData {Name = "fred", Description = "nested positionly", Number = 6, Age = 30};
this._dataThree = new TestData {Name = "teddy", Description = "children description", Number = 2, Age = 40};
this._dataFour = new TestData {Name = "josh", Description = "nested data", Number = 20, Age = 50};
this._parent = new ParentTestData
{
Children = new List<TestData> {this._dataOne, this._dataTwo},
};
this._otherParent = new ParentTestData
{
Children = new List<TestData> {this._dataThree, this._dataFour},
};
this._testData = new List<ParentTestData> {this._parent, this._otherParent};
}
public void SetUp()
{
_dataOne = new TestData { Name = "chris", Description = "child data", Number = 1, Age = 20};
_dataTwo = new TestData { Name = "fred", Description = "child data", Number = 6, Age = 30 };
_dataThree = new TestData { Name = "teddy", Description = "child data", Number = 2, Age = 40 };
_dataFour = new TestData { Name = "josh", Description = "child data", Number = 20, Age = 50 };
_parent = new ParentTestData
{
Children = new List<TestData> {_dataOne, _dataTwo},
OtherChildren = new List<TestData> { _dataThree, _dataFour }
};
_otherParent = new ParentTestData
{
Children = new List<TestData> {_dataThree, _dataFour},
OtherChildren = new List<TestData> { _dataOne, _dataTwo }
};
_testData = new List<ParentTestData> { _parent, _otherParent };
}
public void TestXmlTypes()
{
try
{
_connection.ExecuteSql("CREATE TYPE PostgreSQLTestXmlType AS (id int, testdata xml)");
_connection.ExecuteSql(@"
CREATE OR REPLACE FUNCTION XmlTableProc (id int, testdata xml)
RETURNS SETOF PostgreSQLTestXmlType
AS $$
BEGIN
RETURN QUERY SELECT id as id, testdata as testdata;
END;
$$ LANGUAGE plpgsql;");
var testData = new TestData() { X = 9, Z = 13 };
var parentTestData = new ParentTestData() { ID = 1, TestData = testData };
var results = _connection.Query<ParentTestData, TestData>("XmlTableProc", parentTestData);
var resultParent = results[0];
Assert.AreEqual(parentTestData.ID, resultParent.ID);
Assert.IsNotNull(resultParent.TestData);
Assert.AreEqual(parentTestData.TestData.X, resultParent.TestData.X);
Assert.AreEqual(parentTestData.TestData.Z, resultParent.TestData.Z);
}
finally
{
try { _connection.ExecuteSql("DROP FUNCTION XmlTableProc (id int, testdata xml)"); } catch { }
try { _connection.ExecuteSql("DROP TYPE PostgreSQLTestXmlType"); } catch { }
}
}
public void MappingsAreAppliedToBulkCopy()
{
ColumnMapping.Tables.RemovePrefixes("int");
for (int i = 0; i < 3; i++)
{
// build test data
ParentTestData[] array = new ParentTestData[i];
for (int j = 0; j < i; j++)
array[j] = new ParentTestData() { ParentX = j };
// bulk load the data
_sqlConnection.BulkCopy("InsightTestDataTable2", array);
// run the query
var items = _connection.QuerySql<ParentTestData>("SELECT * FROM InsightTestDataTable2");
Assert.IsNotNull(items);
Assert.AreEqual(i, items.Count);
for (int j = 0; j < i; j++)
Assert.AreEqual(j, items[j].ParentX);
_connection.ExecuteSql("DELETE FROM InsightTestDataTable2");
}
}
public void MappingsAreAppliedToParameters()
{
ColumnMapping.Parameters.RemovePrefixes("int");
var parentTestData = new ParentTestData() { ParentX = 5 };
var results = _connection.Query<int>("TestProc2", parentTestData);
int data = results.First();
Assert.AreEqual(parentTestData.ParentX, data);
}
public void TestXmlTypes()
{
try
{
_connection.ExecuteSql(@"
CREATE OR REPLACE PROCEDURE OracleXmlTableProc (id int, testdata xmltype, r out sys_refcursor)
IS
BEGIN
OPEN r FOR SELECT id as id, testdata as testdata FROM dual;
END;");
var testData = new TestData() { X = 9, Z = 13 };
var parentTestData = new ParentTestData() { ID = 1, TestData = testData };
Assert.Throws<OracleException>(() => _connection.Query<ParentTestData, TestData>("OracleXmlTableProc", parentTestData));
}
finally
{
try { _connection.ExecuteSql("DROP PROCEDURE OracleXmlTableProc"); } catch {}
}
}
public void TestXmlTypes()
{
try
{
_connection.ExecuteSql(@"
CREATE OR REPLACE PROCEDURE OracleXmlTableProc (id int, testdata xmltype, r out sys_refcursor)
IS
BEGIN
OPEN r FOR SELECT id as id, testdata as testdata FROM dual;
END;");
var testData = new TestData() { X = 9, Z = 13 };
var parentTestData = new ParentTestData() { ID = 1, TestData = testData };
var results = _connection.Query<ParentTestData, TestData>("OracleXmlTableProc", parentTestData);
var resultParent = results[0];
Assert.AreEqual(parentTestData.ID, resultParent.ID);
Assert.IsNotNull(resultParent.TestData);
Assert.AreEqual(parentTestData.TestData.X, resultParent.TestData.X);
Assert.AreEqual(parentTestData.TestData.Z, resultParent.TestData.Z);
}
finally
{
try { _connection.ExecuteSql("DROP PROCEDURE OracleXmlTableProc"); } catch {}
}
}
