public void Reader_CircleLate()
{
using (SqlConnection conn = new SqlConnection(_connStr))
using (SqlCommand cmd = new SqlCommand("select * from circles", conn))
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
string expectedReaderValues =
"ColumnName[0] = num" + Environment.NewLine +
"DataType[0] = int" + Environment.NewLine +
"FieldType[0] = System.Int32" + Environment.NewLine +
"ColumnName[1] = def" + Environment.NewLine +
"DataType[1] = UdtTestDb.dbo.Circle" + Environment.NewLine +
"FieldType[1] = Circle" + Environment.NewLine +
" 1, Center = 1,2" + Environment.NewLine +
" 2, Center = 3,4" + Environment.NewLine +
" 3, Center = 11,23" + Environment.NewLine +
" 4, Center = 444,555" + Environment.NewLine +
" 5, Center = 1,2" + Environment.NewLine +
" 6, Center = 3,4" + Environment.NewLine +
" 7, Center = 11,23" + Environment.NewLine +
" 8, Center = 444,245" + Environment.NewLine;
DataTestUtility.AssertEqualsWithDescription(
expectedReaderValues, UdtTestHelpers.DumpReaderString(reader),
"Unexpected reader values.");
}
}
}
public void UDTParams_InputOutput()
{
string spInsertCity = DataTestUtility.GetUniqueNameForSqlServer("spUdtTest2_InsertCity");
string tableName = DataTestUtility.GetUniqueNameForSqlServer("UdtTest2");
using (SqlConnection conn = new SqlConnection(_connStr))
{
conn.Open();
SqlTransaction tx = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = tx;
// create the table
cmd.CommandText = "create table " + tableName + " (name sysname,location Point)";
cmd.ExecuteNonQuery();
// create sp
cmd.CommandText = "create proc " + spInsertCity + "(@name sysname, @location Point OUTPUT)" + " AS insert into " + tableName + " values (@name, @location)";
cmd.ExecuteNonQuery();
try
{
cmd.CommandText = spInsertCity;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pName = cmd.Parameters.Add("@name", SqlDbType.NVarChar, 20);
SqlParameter p = cmd.Parameters.Add("@location", SqlDbType.Udt);
Point pt = new Point(100, 100);
p.UdtTypeName = "Point";
p.Direction = ParameterDirection.InputOutput;
p.Value = pt;
pName.Value = "newcity";
cmd.ExecuteNonQuery();
DataTestUtility.AssertEqualsWithDescription(
"141.42135623731", ((Point)(p.Value)).Distance().ToString(),
"Unexpected distance value.");
DataTestUtility.AssertEqualsWithDescription(
"141.42135623731", ((Point)(p.Value)).Distance().ToString(),
"Unexpected distance value after reading out param again.");
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from " + tableName;
using (SqlDataReader reader = cmd.ExecuteReader())
{
string expectedValue = " newcity, p.X = 100, p.Y = 100, p.Distance() = 141.42135623731" + Environment.NewLine;
DataTestUtility.AssertEqualsWithDescription(
expectedValue, UdtTestHelpers.DumpReaderString(reader, false),
"Unexpected reader dump string.");
}
}
finally
{
tx.Rollback();
}
}
}
public void Reader_PointLate()
{
using (SqlConnection conn = new SqlConnection(_connStr))
using (SqlCommand cmd = new SqlCommand("select name, location from cities", conn))
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
string expectedReaderValues =
"ColumnName[0] = name" + Environment.NewLine +
"DataType[0] = nvarchar" + Environment.NewLine +
"FieldType[0] = System.String" + Environment.NewLine +
"ColumnName[1] = location" + Environment.NewLine +
"DataType[1] = UdtTestDb.dbo.Point" + Environment.NewLine +
"FieldType[1] = Point" + Environment.NewLine +
" redmond, p.X = 3, p.Y = 3, p.Distance() = 5" + Environment.NewLine +
" bellevue, p.X = 6, p.Y = 6, p.Distance() = 10" + Environment.NewLine +
" seattle, p.X = 10, p.Y = 10, p.Distance() = 14.8660687473185" + Environment.NewLine +
" portland, p.X = 20, p.Y = 20, p.Distance() = 25" + Environment.NewLine +
" LA, p.X = 3, p.Y = 3, p.Distance() = 5" + Environment.NewLine +
" SFO, p.X = 6, p.Y = 6, p.Distance() = 10" + Environment.NewLine +
" beaverton, p.X = 10, p.Y = 10, p.Distance() = 14.8660687473185" + Environment.NewLine +
" new york, p.X = 20, p.Y = 20, p.Distance() = 25" + Environment.NewLine +
" yukon, p.X = 20, p.Y = 20, p.Distance() = 32.0156211871642" + Environment.NewLine;
DataTestUtility.AssertEqualsWithDescription(
expectedReaderValues, UdtTestHelpers.DumpReaderString(reader),
"Unexpected reader values.");
}
}
}
private void CopyTableTest(string connStr, string sourceTable, string targetTable, string expectedResults)
{
using (SqlConnection srcConn = new SqlConnection(connStr))
{
srcConn.Open();
SqlCommand cmd = srcConn.CreateCommand();
cmd.CommandText = "select * from " + sourceTable;
using (SqlDataReader reader = cmd.ExecuteReader())
using (SqlBulkCopy bc = new SqlBulkCopy(connStr))
{
bc.DestinationTableName = targetTable;
bc.WriteToServer(reader);
}
cmd.CommandText = "select * from " + targetTable;
DataTestUtility.AssertEqualsWithDescription(
expectedResults, UdtTestHelpers.DumpReaderString(cmd.ExecuteReader()),
"Unexpected bulk copy results.");
}
}