コード例 #1
0
        /// <summary>
        /// Tests if killing the connection using the InternalConnectionWrapper is working
        /// </summary>
        /// <param name="connectionString"></param>
        private static void KillConnectionTest(string connectionString)
        {
            InternalConnectionWrapper wrapper = null;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                wrapper = new InternalConnectionWrapper(connection);

                using (SqlCommand command = new SqlCommand("SELECT 5;", connection))
                {
                    DataTestClass.AssertEqualsWithDescription(5, command.ExecuteScalar(), "Incorrect scalar result.");
                }

                wrapper.KillConnection();
            }

            using (SqlConnection connection2 = new SqlConnection(connectionString))
            {
                connection2.Open();
                Assert.False(wrapper.IsInternalConnectionOf(connection2), "New connection has internal connection that was just killed");
                using (SqlCommand command = new SqlCommand("SELECT 5;", connection2))
                {
                    DataTestClass.AssertEqualsWithDescription(5, command.ExecuteScalar(), "Incorrect scalar result.");
                }
            }
        }
コード例 #2
0
        public void TestDbConnection()
        {
            MockConnection          connection = new MockConnection();
            CancellationTokenSource source     = new CancellationTokenSource();

            // ensure OpenAsync() calls OpenAsync(CancellationToken.None)
            DataTestClass.AssertEqualsWithDescription(ConnectionState.Closed, connection.State, "Connection state should have been marked as Closed");
            connection.OpenAsync().Wait();
            Assert.False(connection.CancellationToken.CanBeCanceled, "Default cancellation token should not be cancellable");
            DataTestClass.AssertEqualsWithDescription(ConnectionState.Open, connection.State, "Connection state should have been marked as Open");
            connection.Close();

            // Verify cancellationToken over-ride
            DataTestClass.AssertEqualsWithDescription(ConnectionState.Closed, connection.State, "Connection state should have been marked as Closed");
            connection.OpenAsync(source.Token).Wait();
            DataTestClass.AssertEqualsWithDescription(ConnectionState.Open, connection.State, "Connection state should have been marked as Open");
            connection.Close();

            // Verify exceptions are routed through task
            MockConnection connectionFail = new MockConnection()
            {
                Fail = true
            };

            connectionFail.OpenAsync().ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            connectionFail.OpenAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();

            // Verify base implementation does not call Open when passed an already cancelled cancellation token
            source.Cancel();
            DataTestClass.AssertEqualsWithDescription(ConnectionState.Closed, connection.State, "Connection state should have been marked as Closed");
            connection.OpenAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnCanceled).Wait();
            DataTestClass.AssertEqualsWithDescription(ConnectionState.Closed, connection.State, "Connection state should have been marked as Closed");
        }
コード例 #3
0
ファイル: DDMARSTest.cs プロジェクト: karthikrajkumar/demo
        public static void TestMain()
        {
            string connstr  = DataTestClass.SQL2005_Northwind + ";multipleactiveresultsets=true;";
            string cmdText1 = "select * from Orders; select count(*) from Customers";
            string cmdText2 = "select * from Customers; select count(*) from Orders";

            using (var conn = new SqlConnection(connstr))
            {
                conn.Open();

                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    SqlCommand cmd1 = conn.CreateCommand();
                    cmd1.Transaction = tran;
                    cmd1.CommandText = cmdText1;

                    using (SqlDataReader reader1 = cmd1.ExecuteReader())
                    {
                        SqlCommand cmd2 = conn.CreateCommand();
                        cmd2.Transaction = tran;
                        cmd2.CommandText = cmdText2;

                        using (SqlDataReader reader2 = cmd2.ExecuteReader())
                        {
                            int actualOrderCount = 0;
                            while (reader1.Read())
                            {
                                Assert.True(actualOrderCount <= expectedOrders.Length, "FAILED: Received more results than expected");
                                DataTestClass.AssertEqualsWithDescription(expectedOrders[actualOrderCount], reader1.GetValue(0), "FAILED: Received wrong value in order query at row " + actualOrderCount);
                                actualOrderCount++;
                            }
                            reader1.NextResult();
                            reader1.Read();
                            int customerCount = (int)reader1.GetValue(0);

                            int actualCustomerCount = 0;
                            while (reader2.Read())
                            {
                                Assert.True(actualCustomerCount <= expectedCustomers.Length, "FAILED: Received more results than expected");
                                DataTestClass.AssertEqualsWithDescription(expectedCustomers[actualCustomerCount], reader2.GetValue(0), "FAILED: Received wrong value in customer query at row " + actualCustomerCount);
                                actualCustomerCount++;
                            }
                            reader2.NextResult();
                            reader2.Read();
                            int orderCount = (int)reader2.GetValue(0);

                            DataTestClass.AssertEqualsWithDescription(expectedCustomers.Length, customerCount, "FAILED: Count query returned incorrect Customer count");
                            DataTestClass.AssertEqualsWithDescription(expectedOrders.Length, orderCount, "FAILED: Count query returned incorrect Order count");
                        }
                    }

                    cmd1.CommandText = "select @@trancount";
                    int tranCount = (int)cmd1.ExecuteScalar();
                    Assert.True(tranCount == 1, "FAILED: Expected a transaction count of 1, but actually received " + tranCount);

                    tran.Commit();
                }
            }
        }
コード例 #4
0
 private static void OpenGoodConnection(string connectionString)
 {
     using (SqlConnection conn = new SqlConnection(connectionString))
     {
         conn.Open();
         DataTestClass.AssertEqualsWithDescription(ConnectionState.Open, conn.State, "FAILED: Connection should be in open state");
     }
 }
コード例 #5
0
        private void GetFieldValueAsync <T>(MockDataReader reader, CancellationToken cancellationToken, int ordinal, T expected)
        {
            Task <T> result = reader.GetFieldValueAsync <T>(ordinal, cancellationToken);

            result.Wait();
            DataTestClass.AssertEqualsWithDescription("GetValue", reader.LastCommand, "Last command was not as expected");
            DataTestClass.AssertEqualsWithDescription(expected, result.Result, "GetFieldValueAsync did not return expected value");
        }
コード例 #6
0
        private static void TestTransactionSingleCase(string caseName, string connectionString, TransactionTestType testType)
        {
            WeakReference weak = null;

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                SqlTransaction gch = null;
                if ((testType != TransactionTestType.TransactionGC) && (testType != TransactionTestType.TransactionGCConnectionClose))
                {
                    gch = con.BeginTransaction();
                }

                switch (testType)
                {
                case TransactionTestType.TransactionRollback:
                    gch.Rollback();
                    break;

                case TransactionTestType.TransactionDispose:
                    gch.Dispose();
                    break;

                case TransactionTestType.TransactionGC:
                    weak = OpenNullifyTransaction(con);
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    Assert.False(weak.IsAlive, "Transaction is still alive on TestTransactionSingle: TransactionGC");
                    break;

                case TransactionTestType.ConnectionClose:
                    GC.SuppressFinalize(gch);
                    con.Close();
                    con.Open();
                    break;

                case TransactionTestType.TransactionGCConnectionClose:
                    weak = OpenNullifyTransaction(con);
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    Assert.False(weak.IsAlive, "Transaction is still alive on TestTransactionSingle: TransactionGCConnectionClose");
                    con.Close();
                    con.Open();
                    break;
                }

                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "select @@trancount";
                    int tranCount = (int)cmd.ExecuteScalar();
                    DataTestClass.AssertEqualsWithDescription(0, tranCount, "TransactionSingle Case " + caseName + " should return expected trans count");
                }
            }
        }
コード例 #7
0
        /// <summary>
        /// Tests if clearing all of the pools does actually remove the pools
        /// </summary>
        /// <param name="connectionString"></param>
        private static void ClearAllPoolsTest(string connectionString)
        {
            SqlConnection.ClearAllPools();
            Assert.True(0 == ConnectionPoolWrapper.AllConnectionPools().Length, "Pools exist after clearing all pools");

            SqlConnection connection = new SqlConnection(connectionString);

            connection.Open();
            ConnectionPoolWrapper pool = new ConnectionPoolWrapper(connection);

            connection.Close();
            ConnectionPoolWrapper[] allPools = ConnectionPoolWrapper.AllConnectionPools();
            DataTestClass.AssertEqualsWithDescription(1, allPools.Length, "Incorrect number of pools exist.");
            Assert.True(allPools[0].Equals(pool), "Saved pool is not in the list of all pools");
            DataTestClass.AssertEqualsWithDescription(1, pool.ConnectionCount, "Saved pool has incorrect number of connections");

            SqlConnection.ClearAllPools();
            Assert.True(0 == ConnectionPoolWrapper.AllConnectionPools().Length, "Pools exist after clearing all pools");
            DataTestClass.AssertEqualsWithDescription(0, pool.ConnectionCount, "Saved pool has incorrect number of connections.");
        }
コード例 #8
0
        /// <summary>
        /// Checks if an 'emancipated' internal connection is reclaimed when a new connection is opened AND we hit max pool size
        /// NOTE: 'emancipated' means that the internal connection's SqlConnection has fallen out of scope and has no references, but was not explicitly disposed\closed
        /// </summary>
        /// <param name="connectionString"></param>
        private static void ReclaimEmancipatedOnOpenTest(string connectionString)
        {
            string newConnectionString = connectionString + ";Max Pool Size=1";

            SqlConnection.ClearAllPools();

            InternalConnectionWrapper internalConnection = CreateEmancipatedConnection(newConnectionString);
            ConnectionPoolWrapper     connectionPool     = internalConnection.ConnectionPool;

            GC.Collect();
            GC.WaitForPendingFinalizers();

            DataTestClass.AssertEqualsWithDescription(1, connectionPool.ConnectionCount, "Wrong number of connections in the pool.");
            DataTestClass.AssertEqualsWithDescription(0, connectionPool.FreeConnectionCount, "Wrong number of free connections in the pool.");

            using (SqlConnection connection = new SqlConnection(newConnectionString))
            {
                connection.Open();
                Assert.True(internalConnection.IsInternalConnectionOf(connection), "Connection has wrong internal connection");
                Assert.True(connectionPool.ContainsConnection(connection), "Connection is in wrong connection pool");
            }
        }
コード例 #9
0
        private static void TestReaderMarsCase(string caseName, string connectionString, ReaderTestType testType, ReaderVerificationType verificationType)
        {
            WeakReference weak = null;

            SqlCommand[]    cmd = new SqlCommand[CONCURRENT_COMMANDS];
            SqlDataReader[] gch = new SqlDataReader[CONCURRENT_COMMANDS];

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                for (int i = 0; i < CONCURRENT_COMMANDS; i++)
                {
                    cmd[i]             = con.CreateCommand();
                    cmd[i].CommandText = COMMAND_TEXT_1;
                    if ((testType != ReaderTestType.ReaderGC) && (testType != ReaderTestType.ReaderGCConnectionClose))
                    {
                        gch[i] = cmd[i].ExecuteReader();
                    }
                    else
                    {
                        gch[i] = null;
                    }
                }

                for (int i = 0; i < CONCURRENT_COMMANDS; i++)
                {
                    switch (testType)
                    {
                    case ReaderTestType.ReaderClose:
                        gch[i].Dispose();
                        break;

                    case ReaderTestType.ReaderDispose:
                        gch[i].Dispose();
                        break;

                    case ReaderTestType.ReaderGC:
                        weak = OpenNullifyReader(cmd[i]);
                        GC.Collect();
                        GC.WaitForPendingFinalizers();
                        Assert.False(weak.IsAlive, "Transaction is still alive on TestReaderMars: ReaderGC");
                        break;

                    case ReaderTestType.ConnectionClose:
                        GC.SuppressFinalize(gch[i]);
                        con.Close();
                        con.Open();
                        break;

                    case ReaderTestType.ReaderGCConnectionClose:
                        weak = OpenNullifyReader(cmd[i]);
                        GC.Collect();
                        GC.WaitForPendingFinalizers();
                        Assert.False(weak.IsAlive, "Transaction is still alive on TestReaderMars: ReaderGCConnectionClose");
                        con.Close();
                        con.Open();
                        break;
                    }

                    cmd[i].Dispose();
                }

                SqlCommand verificationCmd = con.CreateCommand();

                switch (verificationType)
                {
                case ReaderVerificationType.ExecuteReader:
                    verificationCmd.CommandText = COMMAND_TEXT_2;
                    using (SqlDataReader rdr = verificationCmd.ExecuteReader())
                    {
                        rdr.Read();
                        DataTestClass.AssertEqualsWithDescription(1, rdr.FieldCount, "Execute Reader should return expected Field count");
                        DataTestClass.AssertEqualsWithDescription(COLUMN_NAME_2, rdr.GetName(0), "Execute Reader should return expected Field name");
                    }
                    break;

                case ReaderVerificationType.ChangeDatabase:
                    con.ChangeDatabase(DATABASE_NAME);
                    DataTestClass.AssertEqualsWithDescription(DATABASE_NAME, con.Database, "Change Database should return expected Database Name");
                    break;

                case ReaderVerificationType.BeginTransaction:
                    verificationCmd.Transaction = con.BeginTransaction();
                    verificationCmd.CommandText = "select @@trancount";
                    int tranCount = (int)verificationCmd.ExecuteScalar();
                    DataTestClass.AssertEqualsWithDescription(1, tranCount, "Begin Transaction should return expected Transaction count");
                    break;
                }

                verificationCmd.Dispose();
            }
        }
コード例 #10
0
        public void TestDbCommand()
        {
            MockCommand command = new MockCommand()
            {
                ScalarResult = 1,
                Results      = Enumerable.Range(1, 5).Select((x) => new object[] { x, x.ToString() })
            };
            CancellationTokenSource source = new CancellationTokenSource();

            // Verify parameter routing and correct synchronous implementation is called
            command.ExecuteNonQueryAsync().Wait();
            Assert.False(command.CancellationToken.CanBeCanceled, "Default cancellation token should not be cancellable");
            DataTestClass.AssertEqualsWithDescription("ExecuteNonQuery", command.LastCommand, "Last command was not as expected");
            command.ExecuteReaderAsync().Wait();
            DataTestClass.AssertEqualsWithDescription(CommandBehavior.Default, command.CommandBehavior, "Command behavior should have been marked as Default");
            Assert.False(command.CancellationToken.CanBeCanceled, "Default cancellation token should not be cancellable");
            DataTestClass.AssertEqualsWithDescription("ExecuteReader", command.LastCommand, "Last command was not as expected");
            command.ExecuteScalarAsync().Wait();
            Assert.False(command.CancellationToken.CanBeCanceled, "Default cancellation token should not be cancellable");
            DataTestClass.AssertEqualsWithDescription("ExecuteScalar", command.LastCommand, "Last command was not as expected");

            command.ExecuteNonQueryAsync(source.Token).Wait();
            DataTestClass.AssertEqualsWithDescription("ExecuteNonQuery", command.LastCommand, "Last command was not as expected");
            command.ExecuteReaderAsync(source.Token).Wait();
            DataTestClass.AssertEqualsWithDescription("ExecuteReader", command.LastCommand, "Last command was not as expected");
            command.ExecuteScalarAsync(source.Token).Wait();
            DataTestClass.AssertEqualsWithDescription("ExecuteScalar", command.LastCommand, "Last command was not as expected");

            command.ExecuteReaderAsync(CommandBehavior.SequentialAccess).Wait();
            DataTestClass.AssertEqualsWithDescription(CommandBehavior.SequentialAccess, command.CommandBehavior, "Command behavior should have been marked as SequentialAccess");
            Assert.False(command.CancellationToken.CanBeCanceled, "Default cancellation token should not be cancellable");
            DataTestClass.AssertEqualsWithDescription("ExecuteReader", command.LastCommand, "Last command was not as expected");

            command.ExecuteReaderAsync(CommandBehavior.SingleRow, source.Token).Wait();
            DataTestClass.AssertEqualsWithDescription(CommandBehavior.SingleRow, command.CommandBehavior, "Command behavior should have been marked as SingleRow");
            DataTestClass.AssertEqualsWithDescription("ExecuteReader", command.LastCommand, "Last command was not as expected");

            // Verify exceptions are routed through task
            MockCommand commandFail = new MockCommand
            {
                Fail = true
            };

            commandFail.ExecuteNonQueryAsync().ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            commandFail.ExecuteNonQueryAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            commandFail.ExecuteReaderAsync().ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            commandFail.ExecuteReaderAsync(CommandBehavior.SequentialAccess).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            commandFail.ExecuteReaderAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            commandFail.ExecuteReaderAsync(CommandBehavior.SequentialAccess, source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            commandFail.ExecuteScalarAsync().ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            commandFail.ExecuteScalarAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();

            // Verify base implementation does not call Open when passed an already cancelled cancellation token
            source.Cancel();
            command.LastCommand = "Nothing";
            command.ExecuteNonQueryAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnCanceled).Wait();
            DataTestClass.AssertEqualsWithDescription("Nothing", command.LastCommand, "Expected last command to be 'Nothing'");
            command.ExecuteReaderAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnCanceled).Wait();
            DataTestClass.AssertEqualsWithDescription("Nothing", command.LastCommand, "Expected last command to be 'Nothing'");
            command.ExecuteReaderAsync(CommandBehavior.SequentialAccess, source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnCanceled).Wait();
            DataTestClass.AssertEqualsWithDescription("Nothing", command.LastCommand, "Expected last command to be 'Nothing'");
            command.ExecuteScalarAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnCanceled).Wait();
            DataTestClass.AssertEqualsWithDescription("Nothing", command.LastCommand, "Expected last command to be 'Nothing'");

            // Verify cancellation
            command.WaitForCancel = true;
            source = new CancellationTokenSource();
            Task.Factory.StartNew(() => { command.WaitForWaitingForCancel(); source.Cancel(); });
            Task result = command.ExecuteNonQueryAsync(source.Token);

            Assert.True(result.IsFaulted, "Task result should be faulted");

            source = new CancellationTokenSource();
            Task.Factory.StartNew(() => { command.WaitForWaitingForCancel(); source.Cancel(); });
            result = command.ExecuteReaderAsync(source.Token);
            Assert.True(result.IsFaulted, "Task result should be faulted");

            source = new CancellationTokenSource();
            Task.Factory.StartNew(() => { command.WaitForWaitingForCancel(); source.Cancel(); });
            result = command.ExecuteScalarAsync(source.Token);
            Assert.True(result.IsFaulted, "Task result should be faulted");
        }
コード例 #11
0
        public void TestDbDataReader()
        {
            var            query  = Enumerable.Range(1, 2).Select((x) => new object[] { x, x.ToString(), DBNull.Value });
            MockDataReader reader = new MockDataReader {
                Results = query.GetEnumerator()
            };
            CancellationTokenSource source = new CancellationTokenSource();

            Task <bool> result;

            result = reader.ReadAsync(); result.Wait();
            DataTestClass.AssertEqualsWithDescription("Read", reader.LastCommand, "Last command was not as expected");
            Assert.True(result.Result, "Should have received a Result from the ReadAsync");
            Assert.False(reader.CancellationToken.CanBeCanceled, "Default cancellation token should not be cancellable");

            GetFieldValueAsync(reader, 0, 1);
            Assert.False(reader.CancellationToken.CanBeCanceled, "Default cancellation token should not be cancellable");
            GetFieldValueAsync(reader, source.Token, 1, "1");

            result = reader.ReadAsync(source.Token); result.Wait();
            DataTestClass.AssertEqualsWithDescription("Read", reader.LastCommand, "Last command was not as expected");
            Assert.True(result.Result, "Should have received a Result from the ReadAsync");

            GetFieldValueAsync <object>(reader, 2, DBNull.Value);
            GetFieldValueAsync <DBNull>(reader, 2, DBNull.Value);
            reader.GetFieldValueAsync <int?>(2).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            reader.GetFieldValueAsync <string>(2).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            reader.GetFieldValueAsync <bool>(2).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            DataTestClass.AssertEqualsWithDescription("GetValue", reader.LastCommand, "Last command was not as expected");

            result = reader.ReadAsync(); result.Wait();
            DataTestClass.AssertEqualsWithDescription("Read", reader.LastCommand, "Last command was not as expected");
            Assert.False(result.Result, "Should NOT have received a Result from the ReadAsync");

            result = reader.NextResultAsync();
            DataTestClass.AssertEqualsWithDescription("NextResult", reader.LastCommand, "Last command was not as expected");
            Assert.False(result.Result, "Should NOT have received a Result from NextResultAsync");
            Assert.False(reader.CancellationToken.CanBeCanceled, "Default cancellation token should not be cancellable");
            result = reader.NextResultAsync(source.Token);
            DataTestClass.AssertEqualsWithDescription("NextResult", reader.LastCommand, "Last command was not as expected");
            Assert.False(result.Result, "Should NOT have received a Result from NextResultAsync");

            MockDataReader readerFail = new MockDataReader {
                Results = query.GetEnumerator(), Fail = true
            };

            readerFail.ReadAsync().ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            readerFail.ReadAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            readerFail.NextResultAsync().ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            readerFail.NextResultAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            readerFail.GetFieldValueAsync <object>(0).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();
            readerFail.GetFieldValueAsync <object>(0, source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait();

            source.Cancel();
            reader.LastCommand = "Nothing";
            reader.ReadAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnCanceled).Wait();
            DataTestClass.AssertEqualsWithDescription("Nothing", reader.LastCommand, "Expected last command to be 'Nothing'");
            reader.NextResultAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnCanceled).Wait();
            DataTestClass.AssertEqualsWithDescription("Nothing", reader.LastCommand, "Expected last command to be 'Nothing'");
            reader.GetFieldValueAsync <object>(0, source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnCanceled).Wait();
            DataTestClass.AssertEqualsWithDescription("Nothing", reader.LastCommand, "Expected last command to be 'Nothing'");
        }
コード例 #12
0
        public static void TestMain()
        {
            string connectionString = DataTestClass.SQL2005_Pubs;

            string tempTable = DataTestClass.GetUniqueName("T", "[", "]");
            string tempKey   = DataTestClass.GetUniqueName("K", "[", "]");

            DbProviderFactory provider = SqlClientFactory.Instance;

            try
            {
                using (DbConnection con = provider.CreateConnection())
                {
                    con.ConnectionString = connectionString;
                    con.Open();

                    using (DbCommand cmd = provider.CreateCommand())
                    {
                        cmd.Connection = con;
                        DbTransaction tx;

                        #region <<Create temp table>>
                        cmd.CommandText = "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract into " + tempTable + " from authors where au_id='UNKNOWN-ID'";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "alter table " + tempTable + " add constraint " + tempKey + " primary key (au_id)";
                        cmd.ExecuteNonQuery();

                        #endregion

                        tx = con.BeginTransaction();
                        cmd.Transaction = tx;

                        cmd.CommandText = "insert into " + tempTable + "(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values ('876-54-3210', 'Doe', 'Jane' , '882-8080', 'One Microsoft Way', 'Redmond', 'WA', '98052', 0)";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "insert into " + tempTable + "(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values ('876-54-3211', 'Doe', 'John' , '882-8181', NULL, NULL, NULL, NULL, 0)";
                        cmd.ExecuteNonQuery();

                        tx.Commit();

                        cmd.Transaction = null;
                        string      parameterName = "@p1";
                        DbParameter p1            = cmd.CreateParameter();
                        p1.ParameterName = parameterName;
                        p1.Value         = "876-54-3210";
                        cmd.Parameters.Add(p1);

                        cmd.CommandText = "select * from " + tempTable + " where au_id >= " + parameterName;

                        // Test GetValue + IsDBNull
                        using (DbDataReader rdr = cmd.ExecuteReader())
                        {
                            StringBuilder actualResult   = new StringBuilder();
                            int           currentValue   = 0;
                            string[]      expectedValues =
                            {
                                "876-54-3210,Doe,Jane,882-8080    ,One Microsoft Way,Redmond,WA,98052,False",
                                "876-54-3211,Doe,John,882-8181    ,(NULL),(NULL),(NULL),(NULL),False"
                            };

                            while (rdr.Read())
                            {
                                Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");

                                for (int i = 0; i < rdr.FieldCount; i++)
                                {
                                    if (i > 0)
                                    {
                                        actualResult.Append(",");
                                    }
                                    if (rdr.IsDBNull(i))
                                    {
                                        actualResult.Append("(NULL)");
                                    }
                                    else
                                    {
                                        actualResult.Append(rdr.GetValue(i));
                                    }
                                }

                                DataTestClass.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
                                actualResult.Clear();
                            }
                        }

                        // Test GetFieldValue<T> + IsDBNull
                        using (DbDataReader rdr = cmd.ExecuteReader())
                        {
                            StringBuilder actualResult   = new StringBuilder();
                            int           currentValue   = 0;
                            string[]      expectedValues =
                            {
                                "876-54-3210,Doe,Jane,882-8080    ,One Microsoft Way,Redmond,WA,98052,False",
                                "876-54-3211,Doe,John,882-8181    ,(NULL),(NULL),(NULL),(NULL),False"
                            };

                            while (rdr.Read())
                            {
                                Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");

                                for (int i = 0; i < rdr.FieldCount; i++)
                                {
                                    if (i > 0)
                                    {
                                        actualResult.Append(",");
                                    }
                                    if (rdr.IsDBNull(i))
                                    {
                                        actualResult.Append("(NULL)");
                                    }
                                    else
                                    {
                                        if (rdr.GetFieldType(i) == typeof(bool))
                                        {
                                            actualResult.Append(rdr.GetFieldValue <bool>(i));
                                        }
                                        else if (rdr.GetFieldType(i) == typeof(decimal))
                                        {
                                            actualResult.Append(rdr.GetFieldValue <decimal>(i));
                                        }
                                        else
                                        {
                                            actualResult.Append(rdr.GetFieldValue <string>(i));
                                        }
                                    }
                                }

                                DataTestClass.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
                                actualResult.Clear();
                            }
                        }

                        // Test GetFieldValueAsync<T> + IsDBNullAsync
                        using (DbDataReader rdr = cmd.ExecuteReaderAsync().Result)
                        {
                            StringBuilder actualResult   = new StringBuilder();
                            int           currentValue   = 0;
                            string[]      expectedValues =
                            {
                                "876-54-3210,Doe,Jane,882-8080    ,One Microsoft Way,Redmond,WA,98052,False",
                                "876-54-3211,Doe,John,882-8181    ,(NULL),(NULL),(NULL),(NULL),False"
                            };

                            while (rdr.ReadAsync().Result)
                            {
                                Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");

                                for (int i = 0; i < rdr.FieldCount; i++)
                                {
                                    if (i > 0)
                                    {
                                        actualResult.Append(",");
                                    }
                                    if (rdr.IsDBNullAsync(i).Result)
                                    {
                                        actualResult.Append("(NULL)");
                                    }
                                    else
                                    {
                                        if (rdr.GetFieldType(i) == typeof(bool))
                                        {
                                            actualResult.Append(rdr.GetFieldValueAsync <bool>(i).Result);
                                        }
                                        else if (rdr.GetFieldType(i) == typeof(decimal))
                                        {
                                            actualResult.Append(rdr.GetFieldValueAsync <decimal>(i).Result);
                                        }
                                        else
                                        {
                                            actualResult.Append(rdr.GetFieldValueAsync <string>(i).Result);
                                        }
                                    }
                                }

                                DataTestClass.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
                                actualResult.Clear();
                            }
                        }
                    }

                    // GetStream
                    byte[] correctBytes = { 0x12, 0x34, 0x56, 0x78 };
                    string queryString;
                    string correctBytesAsString = "0x12345678";
                    queryString = string.Format("SELECT CAST({0} AS BINARY(20)), CAST({0} AS IMAGE), CAST({0} AS VARBINARY(20))", correctBytesAsString);
                    using (var command = provider.CreateCommand())
                    {
                        command.CommandText = queryString;
                        command.Connection  = con;
                        using (var reader = command.ExecuteReader())
                        {
                            reader.Read();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                byte[] buffer    = new byte[256];
                                Stream stream    = reader.GetStream(i);
                                int    bytesRead = stream.Read(buffer, 0, buffer.Length);
                                for (int j = 0; j < correctBytes.Length; j++)
                                {
                                    Assert.True(correctBytes[j] == buffer[j], "ERROR: Bytes do not match");
                                }
                            }
                        }
                    }

                    // GetTextReader
                    string[] correctStrings = { "Hello World", "\uFF8A\uFF9B\uFF70\uFF9C\uFF70\uFF99\uFF84\uFF9E" };
                    string[] collations     = { "Latin1_General_CI_AS", "Japanese_CI_AS" };

                    for (int j = 0; j < collations.Length; j++)
                    {
                        string substring = string.Format("(N'{0}' COLLATE {1})", correctStrings[j], collations[j]);
                        queryString = string.Format("SELECT CAST({0} AS CHAR(20)), CAST({0} AS NCHAR(20)), CAST({0} AS NTEXT), CAST({0} AS NVARCHAR(20)), CAST({0} AS TEXT), CAST({0} AS VARCHAR(20))", substring);
                        using (var command = provider.CreateCommand())
                        {
                            command.CommandText = queryString;
                            command.Connection  = con;
                            using (var reader = command.ExecuteReader())
                            {
                                reader.Read();
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    char[]     buffer     = new char[256];
                                    TextReader textReader = reader.GetTextReader(i);
                                    int        charsRead  = textReader.Read(buffer, 0, buffer.Length);
                                    string     stringRead = new string(buffer, 0, charsRead);

                                    Assert.True(stringRead == (string)reader.GetValue(i), "ERROR: Strings to not match");
                                }
                            }
                        }
                    }
                }
            }
            finally
            {
                using (DbConnection con = provider.CreateConnection())
                {
                    con.ConnectionString = connectionString;
                    con.Open();

                    using (DbCommand cmd = provider.CreateCommand())
                    {
                        cmd.Connection  = con;
                        cmd.CommandText = "drop table " + tempTable;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }
コード例 #13
0
ファイル: ParametersTest.cs プロジェクト: stevenlius/corefx
        public static void CodeCoverageSqlClient()
        {
            SqlParameterCollection opc = new SqlCommand().Parameters;

            Assert.True(opc.Count == 0, string.Format("FAILED: Expected count: {0}. Actual count: {1}.", 0, opc.Count));
            Assert.False(((IList)opc).IsReadOnly, "FAILED: Expected collection to NOT be read only.");
            Assert.False(((IList)opc).IsFixedSize, "FAILED: Expected collection to NOT be fixed size.");
            Assert.False(((IList)opc).IsSynchronized, "FAILED: Expected collection to NOT be synchronized.");
            DataTestClass.AssertEqualsWithDescription("Object", ((IList)opc).SyncRoot.GetType().Name, "FAILED: Incorrect SyncRoot Name");

            {
                string failValue;
                DataTestClass.AssertThrowsWrapper <IndexOutOfRangeException>(() => failValue = opc[0].ParameterName, "Invalid index 0 for this SqlParameterCollection with Count=0.");

                DataTestClass.AssertThrowsWrapper <IndexOutOfRangeException>(() => failValue = opc["@p1"].ParameterName, "An SqlParameter with ParameterName '@p1' is not contained by this SqlParameterCollection.");
            }

            DataTestClass.AssertThrowsWrapper <ArgumentNullException>(() => opc.Add(null), "The SqlParameterCollection only accepts non-null SqlParameter type objects.");

            opc.Add((object)new SqlParameter());
            IEnumerator enm = opc.GetEnumerator();

            Assert.True(enm.MoveNext(), "FAILED: Expected MoveNext to be true");
            DataTestClass.AssertEqualsWithDescription("Parameter1", ((SqlParameter)enm.Current).ParameterName, "FAILED: Incorrect ParameterName");

            opc.Add(new SqlParameter());
            DataTestClass.AssertEqualsWithDescription("Parameter2", opc[1].ParameterName, "FAILED: Incorrect ParameterName");

            opc.Add(new SqlParameter(null, null));
            opc.Add(new SqlParameter(null, SqlDbType.Int));
            DataTestClass.AssertEqualsWithDescription("Parameter4", opc["Parameter4"].ParameterName, "FAILED: Incorrect ParameterName");

            opc.Add(new SqlParameter("Parameter5", SqlDbType.NVarChar, 20));
            opc.Add(new SqlParameter(null, SqlDbType.NVarChar, 20, "a"));
            opc.RemoveAt(opc[3].ParameterName);
            DataTestClass.AssertEqualsWithDescription(-1, opc.IndexOf(null), "FAILED: Incorrect index for null value");

            SqlParameter p = opc[0];

            DataTestClass.AssertThrowsWrapper <ArgumentException>(() => opc.Add((object)p), "The SqlParameter is already contained by another SqlParameterCollection.");

            DataTestClass.AssertThrowsWrapper <ArgumentException>(() => new SqlCommand().Parameters.Add(p), "The SqlParameter is already contained by another SqlParameterCollection.");

            DataTestClass.AssertThrowsWrapper <ArgumentNullException>(() => opc.Remove(null), "The SqlParameterCollection only accepts non-null SqlParameter type objects.");

            string pname = p.ParameterName;

            p.ParameterName = pname;
            p.ParameterName = pname.ToUpper();
            p.ParameterName = pname.ToLower();
            p.ParameterName = "@p1";
            p.ParameterName = pname;

            opc.Clear();
            opc.Add(p);

            opc.Clear();
            opc.AddWithValue("@p1", null);

            DataTestClass.AssertEqualsWithDescription(-1, opc.IndexOf(p.ParameterName), "FAILED: Incorrect index for parameter name");

            opc[0] = p;
            DataTestClass.AssertEqualsWithDescription(0, opc.IndexOf(p.ParameterName), "FAILED: Incorrect index for parameter name");

            Assert.True(opc.Contains(p.ParameterName), "FAILED: Expected collection to contain provided parameter.");
            Assert.True(opc.Contains(opc[0]), "FAILED: Expected collection to contain provided parameter.");

            opc[0] = p;
            opc[p.ParameterName] = new SqlParameter(p.ParameterName, null);
            opc[p.ParameterName] = new SqlParameter();
            opc.RemoveAt(0);

            new SqlCommand().Parameters.Clear();
            new SqlCommand().Parameters.CopyTo(new object[0], 0);
            Assert.False(new SqlCommand().Parameters.GetEnumerator().MoveNext(), "FAILED: Expected MoveNext to be false");

            DataTestClass.AssertThrowsWrapper <InvalidCastException>(() => new SqlCommand().Parameters.Add(0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");

            DataTestClass.AssertThrowsWrapper <InvalidCastException>(() => new SqlCommand().Parameters.Insert(0, 0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");

            DataTestClass.AssertThrowsWrapper <InvalidCastException>(() => new SqlCommand().Parameters.Remove(0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");

            DataTestClass.AssertThrowsWrapper <ArgumentException>(() => new SqlCommand().Parameters.Remove(new SqlParameter()), "Attempted to remove an SqlParameter that is not contained by this SqlParameterCollection.");
        }
コード例 #14
0
ファイル: DataTypes.cs プロジェクト: karthikrajkumar/demo
        public static void MaxTypesTest()
        {
            string connStr = DataTestClass.SQL2005_Master;

            string tempTable = "max_" + Guid.NewGuid().ToString().Replace('-', '_');
            string initStr   = "create table " + tempTable + " (col1 varchar(max), col2 nvarchar(max), col3 varbinary(max))";

            string insertNormStr = "INSERT " + tempTable + " VALUES('ASCIASCIASCIASCIASCIASCIThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first row', ";

            insertNormStr += "N'This is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first row', ";
            insertNormStr += "0x010100110011000111000111000011110000111100001111000001111100000111110000011111000001111100000111110000011111000001111100000111110000011111)";

            string insertParamStr = "INSERT " + tempTable + " VALUES(@x, @y, @z)";
            string queryStr       = "select * from " + tempTable;

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = initStr;
                cmd.ExecuteNonQuery();

                try
                {
                    cmd.CommandText = insertNormStr;
                    cmd.ExecuteNonQuery();

                    SqlCommand cmd2 = new SqlCommand(insertParamStr, conn);

                    cmd2.Parameters.Add("@x", SqlDbType.VarChar);
                    cmd2.Parameters.Add("@y", SqlDbType.NVarChar);
                    cmd2.Parameters.Add("@z", SqlDbType.VarBinary);
                    cmd2.Parameters[1].Value  = "second line, Insert big, Insert Big, This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
                    cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";

                    byte[] bytes = new byte[2];

                    for (int i = 0; i < bytes.Length; ++i)
                    {
                        bytes[i] = 0xad;
                    }
                    cmd2.Parameters[2].Value = bytes;
                    cmd2.Parameters[0].Value = "This is second row ANSI value";
                    cmd2.ExecuteNonQuery();

                    cmd.CommandText = queryStr;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int        currentValue   = 0;
                        string[][] expectedValues =
                        {
                            new string[]
                            {
                                "ASCIASCIASCIASCIASCIASCIThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first row",
                                "This is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first row",
                                "010100110011000111000111000011110000111100001111000001111100000111110000011111000001111100000111110000011111000001111100000111110000011111"
                            },
                            new string[]
                            {
                                "This is second row ANSI value",
                                "second line, Insert big, Insert Big, This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row",
                                "ADAD"
                            }
                        };

                        while (reader.Read())
                        {
                            Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");

                            char[] stringResult = reader.GetSqlChars(0).Value;
                            DataTestClass.AssertEqualsWithDescription(expectedValues[currentValue][0], new string(stringResult, 0, stringResult.Length), "FAILED: Did not receive expected data");
                            stringResult = reader.GetSqlChars(1).Value;
                            DataTestClass.AssertEqualsWithDescription(expectedValues[currentValue][1], new string(stringResult, 0, stringResult.Length), "FAILED: Did not receive expected data");

                            byte[] bb = reader.GetSqlBytes(2).Value;
                            char[] cc = new char[bb.Length * 2];
                            ConvertBinaryToChar(bb, cc);

                            DataTestClass.AssertEqualsWithDescription(expectedValues[currentValue][2], new string(cc, 0, cc.Length), "FAILED: Did not receive expected data");
                            currentValue++;
                        }
                    }
                }
                finally
                {
                    cmd.CommandText = "drop table " + tempTable;
                    cmd.ExecuteNonQuery();
                }
            }
        }
コード例 #15
0
ファイル: DataTypes.cs プロジェクト: karthikrajkumar/demo
        public static void XmlTest()
        {
            string connStr = DataTestClass.SQL2005_Master;

            string tempTable      = "xml_" + Guid.NewGuid().ToString().Replace('-', '_');
            string initStr        = "create table " + tempTable + " (xml_col XML)";
            string insertNormStr  = "INSERT " + tempTable + " VALUES('<doc>Hello World</doc>')";
            string insertParamStr = "INSERT " + tempTable + " VALUES(@x)";
            string queryStr       = "select * from " + tempTable;

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = initStr;
                cmd.ExecuteNonQuery();

                try
                {
                    cmd.CommandText = insertNormStr;
                    cmd.ExecuteNonQuery();

                    SqlCommand cmd2 = new SqlCommand(insertParamStr, conn);

                    cmd2.Parameters.Add("@x", SqlDbType.Xml);
                    XmlReader xr = XmlReader.Create("data.xml");
                    cmd2.Parameters[0].Value = new SqlXml(xr);
                    cmd2.ExecuteNonQuery();

                    cmd.CommandText = queryStr;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int      currentValue   = 0;
                        string[] expectedValues =
                        {
                            "<doc>Hello World</doc>",
                            "<NewDataSet><builtinCLRtypes><colsbyte>1</colsbyte><colbyte>2</colbyte><colint16>-20</colint16><coluint16>40</coluint16><colint32>-300</colint32><coluint32>300</coluint32><colint64>-4000</colint64><coluint64>4000</coluint64><coldecimal>50000.01</coldecimal><coldouble>600000.987</coldouble><colsingle>70000.9</colsingle><colstring>string variable</colstring><colboolean>true</colboolean><coltimespan>P10675199DT2H48M5.4775807S</coltimespan><coldatetime>9999-12-30T23:59:59.9999999-08:00</coldatetime><colGuid>00000001-0002-0003-0405-060708010101</colGuid><colbyteArray>AQIDBAUGBwgJCgsMDQ4PEA==</colbyteArray><colUri>http://www.abc.com/</colUri><colobjectsbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"byte\">1</colobjectsbyte><colobjectbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedByte\">2</colobjectbyte><colobjectint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"short\">-20</colobjectint16><colobjectuint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedShort\">40</colobjectuint16><colobjectint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"int\">-300</colobjectint32><colobjectuint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedInt\">300</colobjectuint32><colobjectint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"long\">-4000</colobjectint64><colobjectuint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedLong\">4000</colobjectuint64><colobjectdecimal xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"decimal\">50000.01</colobjectdecimal><colobjectdouble xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"double\">600000.987</colobjectdouble><colobjectsingle xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"float\">70000.9</colobjectsingle><colobjectstring xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"string\">string variable</colobjectstring><colobjectboolean xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"boolean\">true</colobjectboolean><colobjecttimespan xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"duration\">P10675199DT2H48M5.4775807S</colobjecttimespan><colobjectdatetime xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"dateTime\">9999-12-30T23:59:59.9999999-08:00</colobjectdatetime><colobjectguid xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\" msdata:InstanceType=\"System.Guid\">00000001-0002-0003-0405-060708010101</colobjectguid><colobjectbytearray xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"base64Binary\">AQIDBAUGBwgJCgsMDQ4PEA==</colobjectbytearray><colobjectUri xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"anyURI\">http://www.abc.com/</colobjectUri></builtinCLRtypes><builtinCLRtypes><colbyte>2</colbyte><colint16>-20</colint16><colint32>-300</colint32><coluint32>300</coluint32><coluint64>4000</coluint64><coldecimal>50000.01</coldecimal><coldouble>600000.987</coldouble><colsingle>70000.9</colsingle><colboolean>true</colboolean><colobjectsbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"byte\">11</colobjectsbyte><colobjectbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedByte\">22</colobjectbyte><colobjectint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"short\">-200</colobjectint16><colobjectuint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedShort\">400</colobjectuint16><colobjectint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"int\">-3000</colobjectint32><colobjectuint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedInt\">3000</colobjectuint32><colobjectint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"long\">-40000</colobjectint64><colobjectuint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedLong\">40000</colobjectuint64><colobjectdecimal xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"decimal\">500000.01</colobjectdecimal><colobjectdouble xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"double\">6000000.987</colobjectdouble><colobjectsingle xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"float\">700000.9</colobjectsingle><colobjectstring xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"string\">string variable 2</colobjectstring><colobjectboolean xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"boolean\">false</colobjectboolean><colobjecttimespan xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"duration\">-P10675199DT2H48M5.4775808S</colobjecttimespan><colobjectdatetime xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"dateTime\">0001-01-01T00:00:00.0000000-08:00</colobjectdatetime><colobjectguid xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\" msdata:InstanceType=\"System.Guid\">00000002-0001-0001-0807-060504030201</colobjectguid><colobjectbytearray xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"base64Binary\">EA8ODQwLCgkIBwYFBAMCAQ==</colobjectbytearray></builtinCLRtypes></NewDataSet>"
                        };

                        while (reader.Read())
                        {
                            Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");

                            SqlXml sx = reader.GetSqlXml(0);
                            xr = sx.CreateReader();
                            xr.Read();

                            DataTestClass.AssertEqualsWithDescription(expectedValues[currentValue++], xr.ReadOuterXml(), "FAILED: Did not receive expected data");
                        }
                    }
                }
                finally
                {
                    cmd.CommandText = "drop table " + tempTable;
                    cmd.ExecuteNonQuery();
                }
            }
        }