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."); } }