Exemplo n.º 1
0
        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.");
                    }
                }
        }
Exemplo n.º 2
0
        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();
                }
            }
        }
Exemplo n.º 3
0
        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.");
                    }
                }
        }
Exemplo n.º 4
0
        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.");
            }
        }