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();
}
}
}
/// <summary>
/// Round trip sql_variant value using TVP.
/// </summary>
private static void SendVariantTvp(object paramValue, string expectedTypeName, string expectedBaseTypeName)
{
string tvpTypeName = DataTestUtility.GetUniqueNameForSqlServer("tvpVariant");
using (SqlConnection connTvp = new SqlConnection(s_connStr))
{
connTvp.Open();
ExecuteSQL(connTvp, "create type dbo.{0} as table (f1 sql_variant)", tvpTypeName);
try
{
// Send TVP using SqlMetaData.
SqlMetaData[] metadata = new SqlMetaData[1];
metadata[0] = new SqlMetaData("f1", SqlDbType.Variant);
SqlDataRecord[] record = new SqlDataRecord[1];
record[0] = new SqlDataRecord(metadata);
record[0].SetValue(0, paramValue);
using (SqlCommand cmd = connTvp.CreateCommand())
{
cmd.CommandText = "select f1, sql_variant_property(f1,'BaseType') as BaseType from @tvpParam";
SqlParameter p = cmd.Parameters.AddWithValue("@tvpParam", record);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = string.Format("dbo.{0}", tvpTypeName);
using (SqlDataReader dr = cmd.ExecuteReader())
{
VerifyReader("SendVariantTvp[SqlMetaData]", dr, expectedTypeName, expectedBaseTypeName);
}
}
// Send TVP using SqlDataReader.
using (SqlDataReader dr = GetReaderForVariant(paramValue, false))
{
using (SqlCommand cmd = connTvp.CreateCommand())
{
cmd.CommandText = "select f1, sql_variant_property(f1,'BaseType') as BaseType from @tvpParam";
SqlParameter p = cmd.Parameters.AddWithValue("@tvpParam", dr);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = string.Format("dbo.{0}", tvpTypeName);
using (SqlDataReader dr2 = cmd.ExecuteReader())
{
VerifyReader("SendVariantTvp[SqlDataReader]", dr2, expectedTypeName, expectedBaseTypeName);
}
}
}
}
finally
{
// Cleanup tvp type.
ExecuteSQL(connTvp, "drop type {0}", tvpTypeName);
}
}
}
public void UDTParams_NullInput()
{
string spInsertCustomer = DataTestUtility.GetUniqueNameForSqlServer("spUdtTest2_InsertCustomer");
string tableName = DataTestUtility.GetUniqueNameForSqlServer("UdtTest2_Customer");
using (SqlConnection conn = new SqlConnection(_connStr))
using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.Transaction = conn.BeginTransaction();
cmd.CommandText = "create table " + tableName + " (name nvarchar(30), address Address)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create proc " + spInsertCustomer + "(@name nvarchar(30), @addr Address OUTPUT)" + " AS insert into " + tableName + " values (@name, @addr)";
cmd.ExecuteNonQuery();
try
{
cmd.CommandText = spInsertCustomer;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pName = cmd.Parameters.Add("@name", SqlDbType.NVarChar, 20);
SqlParameter p = cmd.Parameters.Add("@addr", SqlDbType.Udt);
p.UdtTypeName = "UdtTestDb.dbo.Address";
p.Value = null;
pName.Value = "john";
string spInsertCustomerNoBrackets = spInsertCustomer;
if (spInsertCustomer.StartsWith("[") && spInsertCustomer.EndsWith("]"))
{
spInsertCustomerNoBrackets = spInsertCustomer.Substring(1, spInsertCustomer.Length - 2);
}
string errorMsg = "Procedure or function '" + spInsertCustomerNoBrackets + "' expects parameter '@addr', which was not supplied.";
DataTestUtility.AssertThrowsWrapper <SqlException>(
() => cmd.ExecuteNonQuery(),
errorMsg);
}
finally
{
cmd.Transaction.Rollback();
}
}
}
public void UDTParams_TypedNull()
{
string spInsertCustomer = DataTestUtility.GetUniqueNameForSqlServer("spUdtTest2_InsertCustomer");
string tableName = DataTestUtility.GetUniqueNameForSqlServer("UdtTest2_Customer");
using (SqlConnection conn = new SqlConnection(_connStr))
using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.Transaction = conn.BeginTransaction();
cmd.CommandText = "create table " + tableName + " (name nvarchar(30), address Address)";
cmd.ExecuteNonQuery();
// create proc sp_insert_customer(@name nvarchar(30), @addr Address OUTPUT)
// AS
// insert into customers values (@name, @addr)
cmd.CommandText = "create proc " + spInsertCustomer + " (@name nvarchar(30), @addr Address OUTPUT)" + " AS insert into " + tableName + " values (@name, @addr)";
cmd.ExecuteNonQuery();
try
{
cmd.CommandText = spInsertCustomer;
cmd.CommandType = CommandType.StoredProcedure;
Address addr = Address.Parse("123 baker st || Redmond");
SqlParameter pName = cmd.Parameters.Add("@name", SqlDbType.NVarChar, 20);
SqlParameter p = cmd.Parameters.Add("@addr", SqlDbType.Udt);
p.UdtTypeName = "UdtTestDb.dbo.Address";
p.Value = Address.Null;
pName.Value = "john";
cmd.ExecuteNonQuery();
DataTestUtility.AssertEqualsWithDescription(
Address.Null.ToString(), p.Value.ToString(),
"Unexpected parameter value.");
}
finally
{
cmd.Transaction.Rollback();
}
}
}
public void UDTParams_Invalid2()
{
string spInsertCustomer = DataTestUtility.GetUniqueNameForSqlServer("spUdtTest2_InsertCustomer");
string tableName = DataTestUtility.GetUniqueNameForSqlServer("UdtTest2");
using (SqlConnection conn = new SqlConnection(_connStr))
using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.Transaction = conn.BeginTransaction();
cmd.CommandText = "create table " + tableName + " (name nvarchar(30), address Address)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create proc " + spInsertCustomer + "(@name nvarchar(30), @addr Address OUTPUT)" + " AS insert into " + tableName + " values (@name, @addr)";
cmd.ExecuteNonQuery();
try
{
cmd.CommandText = spInsertCustomer;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pName = cmd.Parameters.Add("@fname", SqlDbType.NVarChar, 20);
SqlParameter p = cmd.Parameters.Add("@addr", SqlDbType.Udt);
Address addr = Address.Parse("customer whose name is address");
p.UdtTypeName = "UdtTestDb.dbo.Address";
p.Value = addr;
pName.Value = addr;
DataTestUtility.AssertThrowsWrapper <InvalidCastException>(
() => cmd.ExecuteReader(),
"Failed to convert parameter value from a Address to a String.");
}
finally
{
cmd.Transaction.Rollback();
}
}
}
/// <summary>
/// Round trip sql_variant value using SqlBulkCopy.
/// </summary>
private static void SendVariantBulkCopy(object paramValue, string expectedTypeName, string expectedBaseTypeName)
{
string bulkCopyTableName = DataTestUtility.GetUniqueNameForSqlServer("bulkDest");
// Fetch reader using type.
using (SqlDataReader dr = GetReaderForVariant(paramValue, false))
{
using (SqlConnection connBulk = new SqlConnection(s_connStr))
{
connBulk.Open();
ExecuteSQL(connBulk, "create table dbo.{0} (f1 sql_variant)", bulkCopyTableName);
try
{
// Perform bulk copy to target.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connBulk))
{
bulkCopy.BulkCopyTimeout = 60;
bulkCopy.BatchSize = 1;
bulkCopy.DestinationTableName = bulkCopyTableName;
bulkCopy.WriteToServer(dr);
}
// Verify target.
using (SqlCommand cmd = connBulk.CreateCommand())
{
cmd.CommandText = string.Format("select f1, sql_variant_property(f1,'BaseType') as BaseType from {0}", bulkCopyTableName);
using (SqlDataReader drVerify = cmd.ExecuteReader())
{
VerifyReader("SendVariantBulkCopy[SqlDataReader]", drVerify, expectedTypeName, expectedBaseTypeName);
}
}
// Truncate target table for next pass.
ExecuteSQL(connBulk, "truncate table {0}", bulkCopyTableName);
// Send using DataTable as source.
DataTable t = new DataTable();
t.Columns.Add("f1", typeof(object));
t.Rows.Add(new object[] { paramValue });
// Perform bulk copy to target.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connBulk))
{
bulkCopy.BulkCopyTimeout = 60;
bulkCopy.BatchSize = 1;
bulkCopy.DestinationTableName = bulkCopyTableName;
bulkCopy.WriteToServer(t, DataRowState.Added);
}
// Verify target.
using (SqlCommand cmd = connBulk.CreateCommand())
{
cmd.CommandText = string.Format("select f1, sql_variant_property(f1,'BaseType') as BaseType from {0}", bulkCopyTableName);
using (SqlDataReader drVerify = cmd.ExecuteReader())
{
VerifyReader("SendVariantBulkCopy[DataTable]", drVerify, expectedTypeName, expectedBaseTypeName);
}
}
// Truncate target table for next pass.
ExecuteSQL(connBulk, "truncate table {0}", bulkCopyTableName);
// Send using DataRow as source.
DataRow[] rowToSend = t.Select();
// Perform bulk copy to target.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connBulk))
{
bulkCopy.BulkCopyTimeout = 60;
bulkCopy.BatchSize = 1;
bulkCopy.DestinationTableName = bulkCopyTableName;
bulkCopy.WriteToServer(rowToSend);
}
// Verify target.
using (SqlCommand cmd = connBulk.CreateCommand())
{
cmd.CommandText = string.Format("select f1, sql_variant_property(f1,'BaseType') as BaseType from {0}", bulkCopyTableName);
using (SqlDataReader drVerify = cmd.ExecuteReader())
{
VerifyReader("SendVariantBulkCopy[DataRow]", drVerify, expectedTypeName, expectedBaseTypeName);
}
}
}
finally
{
// Cleanup target table.
ExecuteSQL(connBulk, "drop table {0}", bulkCopyTableName);
}
}
}
}
public void RunCopyTest()
{
_connStr = (new SqlConnectionStringBuilder(DataTestUtility.TcpConnStr)
{
InitialCatalog = DataTestUtility.UdtTestDbName
}).ConnectionString;
SqlConnection conn = new SqlConnection(_connStr);
string cities = DataTestUtility.GetUniqueNameForSqlServer("UdtBulkCopy_cities");
string customers = DataTestUtility.GetUniqueNameForSqlServer("UdtBulkCopy_customers");
string circles = DataTestUtility.GetUniqueNameForSqlServer("UdtBulkCopy_circles");
conn.Open();
try
{
ExecuteNonQueryCommand($"create table {cities} (name sysname, location Point)", _connStr);
ExecuteNonQueryCommand($"create table {customers} (name nvarchar(30), address Address)", _connStr);
ExecuteNonQueryCommand($"create table {circles} (num int, def Circle)", _connStr);
string expectedResults =
"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.866068747318506" + 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.866068747318506" + 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.01562118716424" + Environment.NewLine;
CopyTableTest(_connStr, "cities", cities, expectedResults);
expectedResults =
"ColumnName[0] = name" + Environment.NewLine +
"DataType[0] = nvarchar" + Environment.NewLine +
"FieldType[0] = System.String" + Environment.NewLine +
"ColumnName[1] = address" + Environment.NewLine +
"DataType[1] = UdtTestDb.dbo.Address" + Environment.NewLine +
"FieldType[1] = Address" + Environment.NewLine +
" first, Address 1 Address 2" + Environment.NewLine +
" second, 123 Park Lane New York" + Environment.NewLine +
" third, 21 Forest grove Portland" + Environment.NewLine +
" fourth, 34 Lake Blvd Seattle" + Environment.NewLine +
" fifth, A2 Meadows Bellevue" + Environment.NewLine;
CopyTableTest(_connStr, "customers", customers, expectedResults);
expectedResults =
"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;
CopyTableTest(_connStr, "circles", circles, expectedResults);
}
finally
{
ExecuteNonQueryCommand($"drop table {cities}", _connStr);
ExecuteNonQueryCommand($"drop table {customers}", _connStr);
ExecuteNonQueryCommand($"drop table {circles}", _connStr);
}
}
/// <summary>
/// Round trip sql_variant value using TVP.
/// </summary>
private static void SendVariantTvp(object paramValue, string expectedTypeName, string expectedBaseTypeName)
{
string tvpTypeName = DataTestUtility.GetUniqueNameForSqlServer("tvpVariant");
using (SqlConnection connTvp = new SqlConnection(s_connStr))
{
connTvp.Open();
// Drop and re-create tvp type.
xsql(connTvp, "if exists(select 1 from sys.types where name='{0}') begin drop type {0} end", tvpTypeName);
xsql(connTvp, "create type {0} as table (f1 sql_variant)", tvpTypeName);
// Send TVP using SqlMetaData.
SqlMetaData[] metadata = new SqlMetaData[1];
metadata[0] = new SqlMetaData("f1", SqlDbType.Variant);
SqlDataRecord[] record = new SqlDataRecord[1];
record[0] = new SqlDataRecord(metadata);
record[0].SetValue(0, paramValue);
using (SqlCommand cmd = connTvp.CreateCommand())
{
cmd.CommandText = "select f1, sql_variant_property(f1,'BaseType') as BaseType from @tvpParam";
SqlParameter p = cmd.Parameters.AddWithValue("@tvpParam", record);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = string.Format("dbo.{0}", tvpTypeName);
using (SqlDataReader dr = cmd.ExecuteReader())
{
VerifyReader("SendVariantTvp[SqlMetaData]", dr, expectedTypeName, expectedBaseTypeName);
}
}
// Send TVP using DataSet.
// DEVNOTE: TVPs do not support sql_variant inside DataTable, by design according to tvp spec:
// 4.3 DbDataReader
// The only difference or special case for DataTable is that we will not support:
// 1) UDT?s
// 2) Variant
/*
* DataTable t = new DataTable();
* t.Columns.Add("f1", typeof(object));
* t.Rows.Add(new object[] { paramValue });
* t.AcceptChanges();
*
* using (SqlCommand cmd = connTvp.CreateCommand())
* {
* cmd.CommandText = "select f1, sql_variant_property(@p1,'BaseType') as BaseType from @tvpParam";
* SqlParameter p = cmd.Parameters.AddWithValue("@tvpParam", t);
* p.SqlDbType = SqlDbType.Structured;
* p.TypeName = @"dbo.tvpVariant";
* using (SqlDataReader dr = cmd.ExecuteReader())
* {
* VerifyReader("SendVariantTvp[DataTable]", dr, expectedTypeName, expectedBaseTypeName);
* }
* }
*/
// Send TVP using SqlDataReader.
using (SqlDataReader dr = GetReaderForVariant(paramValue, false))
{
using (SqlCommand cmd = connTvp.CreateCommand())
{
cmd.CommandText = "select f1, sql_variant_property(f1,'BaseType') as BaseType from @tvpParam";
SqlParameter p = cmd.Parameters.AddWithValue("@tvpParam", dr);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = string.Format("dbo.{0}", tvpTypeName);
using (SqlDataReader dr2 = cmd.ExecuteReader())
{
VerifyReader("SendVariantTvp[SqlDataReader]", dr2, expectedTypeName, expectedBaseTypeName);
}
}
}
// Cleanup tvp type.
xsql(connTvp, "if exists(select 1 from sys.types where name='{0}') begin drop type {0} end", tvpTypeName);
}
}