X-Git-Url: http://wien.tomnetworks.com/gitweb/?a=blobdiff_plain;f=mcs%2Fclass%2FSystem.Data%2FTest%2FProviderTests%2FSystem.Data.SqlClient%2FSqlCommandTest.cs;h=161cdb8f5dfb4910ccde05b6f9e6de3a9b5a6ed2;hb=f90d4f76a9929225953ada2d84ce37171409f4ac;hp=a4f239f6ecef8f4e0f9d4337092abb460f8919ec;hpb=3f1e1a17dda22cdf48bb2450f941c8b6035e20ee;p=mono.git diff --git a/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlCommandTest.cs b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlCommandTest.cs index a4f239f6ece..161cdb8f5df 100644 --- a/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlCommandTest.cs +++ b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlCommandTest.cs @@ -33,6 +33,10 @@ using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; +#if NET_2_0 +using System.Data.Sql; +using System.Xml; +#endif using NUnit.Framework; @@ -142,8 +146,13 @@ namespace MonoTests.System.Data.SqlClient }catch (AssertionException e) { throw e; }catch (Exception e) { - Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), +#if NET_2_0 + Assert.AreEqual (typeof (NullReferenceException), e.GetType (), + "#2 Incorrect Exception : " + e.StackTrace); +#else + Assert.AreEqual (typeof (InvalidOperationException), e.GetType (), "#2 Incorrect Exception : " + e.StackTrace); +#endif } // Check the Return value for a Correct Query @@ -159,7 +168,7 @@ namespace MonoTests.System.Data.SqlClient cmd.CommandText = "select id from numeric_family where id=-1"; result = cmd.ExecuteScalar (); - Assert.IsNull (result, "#5 Null shud be returned if result set is empty"); + Assert.IsNull (result, "#5 Null should be returned if result set is empty"); // Check SqlException is thrown for Invalid Query cmd.CommandText = "select count* from numeric_family"; @@ -172,6 +181,72 @@ namespace MonoTests.System.Data.SqlClient Assert.AreEqual (typeof(SqlException), e.GetType(), "#7 Incorrect Exception : " + e.StackTrace); } + + + // Parameterized stored procedure calls + + int int_value = 20; + string string_value = "output value changed"; + string return_value = "first column of first rowset"; + + cmd.CommandText = + "create procedure #tmp_executescalar_outparams "+ + " (@p1 int, @p2 int out, @p3 varchar(200) out) "+ + "as " + + "select '" + return_value + "' as 'col1', @p1 as 'col2' "+ + "set @p2 = @p2 * 2 "+ + "set @p3 = N'" + string_value + "' "+ + "select 'second rowset' as 'col1', 2 as 'col2' "+ + "return 1"; + + cmd.CommandType = CommandType.Text; + cmd.ExecuteNonQuery (); + + cmd.CommandText = "#tmp_executescalar_outparams"; + cmd.CommandType = CommandType.StoredProcedure; + + SqlParameter p1 = new SqlParameter (); + p1.ParameterName = "@p1"; + p1.Direction = ParameterDirection.Input; + p1.DbType = DbType.Int32; + p1.Value = int_value; + cmd.Parameters.Add (p1); + + SqlParameter p2 = new SqlParameter (); + p2.ParameterName = "@p2"; + p2.Direction = ParameterDirection.InputOutput; + p2.DbType = DbType.Int32; + p2.Value = int_value; + cmd.Parameters.Add (p2); + + SqlParameter p3 = new SqlParameter (); + p3.ParameterName = "@p3"; + p3.Direction = ParameterDirection.Output; + p3.DbType = DbType.String; + p3.Size = 200; + cmd.Parameters.Add (p3); + + result = cmd.ExecuteScalar (); + Assert.AreEqual (return_value, result, "#8 ExecuteScalar Should return 'first column of first rowset'"); + Assert.AreEqual (int_value * 2, p2.Value, "#9 ExecuteScalar should fill the parameter collection with the outputted values"); + Assert.AreEqual (string_value, p3.Value, "#10 ExecuteScalar should fill the parameter collection with the outputted values"); + + p3.Size = 0; + p3.Value = null; + try { + cmd.ExecuteScalar (); + Assert.Fail ("#11 Query should throw System.InvalidOperationException due to size = 0 and value = null"); + } + catch (AssertionException e) { + throw e; + } + catch (Exception e) { + Assert.AreEqual (typeof (InvalidOperationException), e.GetType (), + "#12 Incorrect Exception : " + e.StackTrace); + } + + conn.Close (); + } [Test] @@ -190,8 +265,13 @@ namespace MonoTests.System.Data.SqlClient }catch (AssertionException e) { throw e; }catch (Exception e) { +#if NET_2_0 + Assert.AreEqual (typeof(NullReferenceException), e.GetType(), + "#2 Incorrect Exception : " + e); +#else Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), "#2 Incorrect Exception : " + e); +#endif } // Test Exception is thrown if Query is incorrect @@ -224,37 +304,96 @@ namespace MonoTests.System.Data.SqlClient result = cmd.ExecuteNonQuery (); Assert.AreEqual (1, result, "#3 One row shud be updated"); + // Test Batch Commands + cmd.CommandText = "Select id from numeric_family where id=1;"; + cmd.CommandText += "update numeric_family set type_int=10 where id=1000"; + cmd.CommandText += "update numeric_family set type_int=10 where id=100"; + result = cmd.ExecuteNonQuery (); + Assert.AreEqual (1, result, "#4 One row shud be updated"); + cmd.CommandText = "Delete from numeric_family where id=100"; result = cmd.ExecuteNonQuery (); - Assert.AreEqual (1, result, "#4 One row shud be deleted"); + Assert.AreEqual (1, result, "#5 One row shud be deleted"); }finally { trans.Rollback (); } + + + // Parameterized stored procedure calls + + int int_value = 20; + string string_value = "output value changed"; + + cmd.CommandText = + "create procedure #tmp_executescalar_outparams " + + " (@p1 int, @p2 int out, @p3 varchar(200) out) " + + "as " + + "select 'test' as 'col1', @p1 as 'col2' " + + "set @p2 = @p2 * 2 " + + "set @p3 = N'" + string_value + "' " + + "select 'second rowset' as 'col1', 2 as 'col2' " + + "return 1"; + + cmd.CommandType = CommandType.Text; + cmd.ExecuteNonQuery (); + + cmd.CommandText = "#tmp_executescalar_outparams"; + cmd.CommandType = CommandType.StoredProcedure; + + SqlParameter p1 = new SqlParameter (); + p1.ParameterName = "@p1"; + p1.Direction = ParameterDirection.Input; + p1.DbType = DbType.Int32; + p1.Value = int_value; + cmd.Parameters.Add (p1); + + SqlParameter p2 = new SqlParameter (); + p2.ParameterName = "@p2"; + p2.Direction = ParameterDirection.InputOutput; + p2.DbType = DbType.Int32; + p2.Value = int_value; + cmd.Parameters.Add (p2); + + SqlParameter p3 = new SqlParameter (); + p3.ParameterName = "@p3"; + p3.Direction = ParameterDirection.Output; + p3.DbType = DbType.String; + p3.Size = 200; + cmd.Parameters.Add (p3); + + cmd.ExecuteNonQuery (); + Assert.AreEqual (int_value * 2, p2.Value, "#6 ExecuteNonQuery should fill the parameter collection with the outputted values"); + Assert.AreEqual (string_value, p3.Value, "#7 ExecuteNonQuery should fill the parameter collection with the outputted values"); } [Test] public void ExecuteReaderTest () { - SqlDataReader reader = null; + //SqlDataReader reader = null; conn = new SqlConnection (connectionString); // Test exception is thrown if conn is closed cmd = new SqlCommand ("Select count(*) from numeric_family"); try { - reader = cmd.ExecuteReader (); + /*reader = */cmd.ExecuteReader (); }catch (AssertionException e) { throw e; }catch (Exception e) { +#if NET_2_0 + Assert.AreEqual (typeof(NullReferenceException), e.GetType(), + "#1 Incorrect Exception"); +#else Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), "#1 Incorrect Exception"); +#endif } conn.Open (); // Test exception is thrown for Invalid Query cmd = new SqlCommand ("InvalidQuery", conn); try { - reader = cmd.ExecuteReader (); + /*reader = */cmd.ExecuteReader (); Assert.Fail ("#1 Exception shud be thrown"); }catch (AssertionException e) { throw e; @@ -271,12 +410,13 @@ namespace MonoTests.System.Data.SqlClient // Test Connection cannot be modified when reader is in use // NOTE : msdotnet contradicts documented behavior + /* cmd.CommandText = "select * from numeric_family where id=1"; reader = cmd.ExecuteReader (); reader.Read (); conn.Close (); // valid operation conn = new SqlConnection (connectionString); - + */ /* // NOTE msdotnet contradcits documented behavior // If the above testcase fails, then this shud be tested @@ -401,7 +541,11 @@ namespace MonoTests.System.Data.SqlClient // Test InvalidOperation Exception is thrown if Parameter Type // is not explicitly set +#if NET_2_0 + cmd.Parameters.AddWithValue ("@ID", 2); +#else cmd.Parameters.Add ("@ID", 2); +#endif try { cmd.Prepare (); Assert.Fail ("#1 Parameter Type shud be explicitly Set"); @@ -433,7 +577,7 @@ namespace MonoTests.System.Data.SqlClient cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "ABFSDSFSF" ; cmd.Prepare (); - }catch (Exception e) { + }catch { Assert.Fail ("#7 Exception shud not be thrown for Stored Procs"); } cmd.CommandType = CommandType.Text; @@ -443,12 +587,22 @@ namespace MonoTests.System.Data.SqlClient cmd.Connection = null; try { cmd.Prepare (); - Assert.Fail ("#8 InvalidOperation Exception shud be thrown"); - }catch (AssertionException e) { +#if NET_2_0 + Assert.Fail ("#8 NullReferenceException should be thrown"); +#else + Assert.Fail ("#8 InvalidOperation Exception should be thrown"); +#endif + } + catch (AssertionException e) { throw e; }catch (Exception e) { - Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), +#if NET_2_0 + Assert.AreEqual (typeof (NullReferenceException), e.GetType (), + "#9 Incorrect Exception : " + e.StackTrace); +#else + Assert.AreEqual (typeof (InvalidOperationException), e.GetType (), "#9 Incorrect Exception : " + e.StackTrace); +#endif } //Test InvalidOperation Exception is thrown if connection is closed @@ -459,8 +613,13 @@ namespace MonoTests.System.Data.SqlClient }catch (AssertionException e) { throw e; }catch (Exception e) { +#if NET_2_0 + Assert.AreEqual (typeof(NullReferenceException), e.GetType(), + "Incorrect Exception : " + e.StackTrace); +#else Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), "Incorrect Exception : " + e.StackTrace); +#endif } } @@ -487,7 +646,11 @@ namespace MonoTests.System.Data.SqlClient } [Test] +#if NET_2_0 + [ExpectedException (typeof(ArgumentOutOfRangeException))] +#else [ExpectedException (typeof(ArgumentException))] +#endif public void CommandTypeTest () { cmd = new SqlCommand (); @@ -530,8 +693,13 @@ namespace MonoTests.System.Data.SqlClient cmd.ExecuteNonQuery (); Assert.Fail ("#2 Connection cannot be different"); }catch (Exception e) { +#if NET_2_0 + Assert.AreEqual (typeof(NullReferenceException), e.GetType(), + "#3 Incorrect Exception : " + e); +#else Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), "#3 Incorrect Exception : " + e); +#endif }finally { conn1.Close (); conn.Close (); @@ -540,7 +708,11 @@ namespace MonoTests.System.Data.SqlClient // Need to add more tests [Test] +#if NET_2_0 + [ExpectedException (typeof(ArgumentOutOfRangeException))] +#else [ExpectedException (typeof(ArgumentException))] +#endif public void UpdatedRowSourceTest () { cmd = new SqlCommand (); @@ -576,6 +748,100 @@ namespace MonoTests.System.Data.SqlClient } } + // Test for bug #76778 + // Test for a case, when query size is greater than the block size + [Test] + public void LongQueryTest () + { + SqlConnection conn = new SqlConnection ( + connectionString + ";Pooling=false"); + using (conn) { + conn.Open (); + SqlCommand cmd = conn.CreateCommand (); + String value = new String ('a', 10000); + cmd.CommandText = String.Format ("Select '{0}'", value); + cmd.ExecuteNonQuery (); + } + } + + // Test for bug #76778 + // To make sure RPC (when implemented) works ok.. + [Test] + public void LongStoredProcTest() + { + SqlConnection conn = new SqlConnection ( + connectionString + ";Pooling=false"); + using (conn) { + conn.Open (); + /*int size = conn.PacketSize ; */ + SqlCommand cmd = conn.CreateCommand (); + // create a temp stored proc .. + cmd.CommandText = "Create Procedure #sp_tmp_long_params "; + cmd.CommandText += "@p1 nvarchar (4000), "; + cmd.CommandText += "@p2 nvarchar (4000), "; + cmd.CommandText += "@p3 nvarchar (4000), "; + cmd.CommandText += "@p4 nvarchar (4000) out "; + cmd.CommandText += "As "; + cmd.CommandText += "Begin "; + cmd.CommandText += "Set @p4 = N'Hello' "; + cmd.CommandText += "Return 2 "; + cmd.CommandText += "End"; + cmd.ExecuteNonQuery (); + + //execute the proc + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "#sp_tmp_long_params"; + + String value = new String ('a', 4000); + SqlParameter p1 = new SqlParameter ("@p1", + SqlDbType.NVarChar,4000); + p1.Value = value; + + SqlParameter p2 = new SqlParameter ("@p2", + SqlDbType.NVarChar,4000); + p2.Value = value; + + SqlParameter p3 = new SqlParameter ("@p3", + SqlDbType.NVarChar,4000); + p3.Value = value; + + SqlParameter p4 = new SqlParameter ("@p4", + SqlDbType.NVarChar,4000); + p4.Direction = ParameterDirection.Output; + + // for now, name shud be @RETURN_VALUE + // can be changed once RPC is implemented + SqlParameter p5 = new SqlParameter ("@RETURN_VALUE", SqlDbType.Int); + p5.Direction = ParameterDirection.ReturnValue ; + + cmd.Parameters.Add (p1); + cmd.Parameters.Add (p2); + cmd.Parameters.Add (p3); + cmd.Parameters.Add (p4); + cmd.Parameters.Add (p5); + + cmd.ExecuteNonQuery (); + Assert.AreEqual ("Hello", p4.Value, "#1"); + Assert.AreEqual (2, p5.Value, "#2"); + } + } + + // Test for bug #76880 + [Test] + public void DateTimeParameterTest () + { + SqlConnection conn = new SqlConnection (connectionString); + using (conn) { + conn.Open (); + SqlCommand cmd = conn.CreateCommand (); + cmd.CommandText = "select * from datetime_family where type_datetime=@p1"; + cmd.Parameters.Add ("@p1", SqlDbType.DateTime).Value= "10-10-2005"; + // shudnt cause and exception + SqlDataReader rdr = cmd.ExecuteReader (); + rdr.Close (); + } + } + /** * Verifies whether an enum value is converted to a numeric value when * used as value for a numeric parameter (bug #66630) @@ -635,25 +901,569 @@ namespace MonoTests.System.Data.SqlClient Object TestPar = DBNull.Value; cmd.Parameters.Add("@TestPar1", SqlDbType.Int); cmd.Parameters["@TestPar1"].Value = TestPar; - cmd.Parameters.Add("@BirthDate", DateTime.Now); +#if NET_2_0 + cmd.Parameters.AddWithValue ("@BirthDate", DateTime.Now); +#else + cmd.Parameters.Add ("@BirthDate", DateTime.Now); +#endif cmd.DesignTimeVisible = true; cmd.CommandTimeout = 100; Object clone1 = ((ICloneable)(cmd)).Clone(); SqlCommand cmd1 = (SqlCommand) clone1; Assert.AreEqual(2, cmd1.Parameters.Count); Assert.AreEqual(100, cmd1.CommandTimeout); - cmd1.Parameters.Add("@test", DateTime.Now); +#if NET_2_0 + cmd1.Parameters.AddWithValue ("@test", DateTime.Now); +#else + cmd1.Parameters.Add ("@test", DateTime.Now); +#endif // to check that it is deep copy and not a shallow copy of the // parameter collection Assert.AreEqual(3, cmd1.Parameters.Count); Assert.AreEqual(2, cmd.Parameters.Count); } + [Test] + public void StoredProc_NoParameterTest () + { + string query = "create procedure #tmp_sp_proc as begin"; + query += " select 'data' end"; + SqlConnection conn = new SqlConnection (connectionString); + SqlCommand cmd = conn.CreateCommand (); + cmd.CommandText = query ; + conn.Open (); + cmd.ExecuteNonQuery (); + + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "#tmp_sp_proc"; + using (SqlDataReader reader = cmd.ExecuteReader()) { + if (reader.Read ()) + Assert.AreEqual ("data", reader.GetString(0),"#1"); + else + Assert.Fail ("#2 Select shud return data"); + } + conn.Close (); + } + + [Test] + public void StoredProc_ParameterTest () + { + string create_query = CREATE_TMP_SP_PARAM_TEST; + string drop_query = DROP_TMP_SP_PARAM_TEST; + + SqlConnection conn = new SqlConnection (connectionString); + + conn.Open (); + SqlCommand cmd = conn.CreateCommand (); + int label = 0 ; + string error = ""; + while (label != -1) { + try { + switch (label) { + case 0 : + // Test BigInt Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query, "bigint")); + rpc_helper_function (cmd, SqlDbType.BigInt, 0, Int64.MaxValue); + rpc_helper_function (cmd, SqlDbType.BigInt, 0, Int64.MinValue); + break; + case 1 : + // Test Binary Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query, "binary(5)")); + //rpc_helper_function (cmd, SqlDbType.Binary, 0, new byte[] {}); + rpc_helper_function (cmd, SqlDbType.Binary, 5, new byte[] {1,2,3,4,5}); + break; + case 2 : + // Test Bit Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query, "bit")); + rpc_helper_function (cmd, SqlDbType.Bit, 0, true); + rpc_helper_function (cmd, SqlDbType.Bit, 0, false); + break; + case 3 : + // Testing Char + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query, "char(10)")); + rpc_helper_function (cmd, SqlDbType.Char, 10, "characters"); + /* + rpc_helper_function (cmd, SqlDbType.Char, 10, ""); + rpc_helper_function (cmd, SqlDbType.Char, 10, null); + */ + break; + case 4 : + // Testing DateTime + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query, "datetime")); + rpc_helper_function (cmd, SqlDbType.DateTime, 0, "2079-06-06 23:59:00"); + /* + rpc_helper_function (cmd, SqlDbType.DateTime, 10, ""); + rpc_helper_function (cmd, SqlDbType.DateTime, 10, null); + */ + break; + case 5 : + // Test Decimal Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"decimal(10,2)")); + /* + rpc_helper_function (cmd, SqlDbType.Decimal, 0, 10.01); + rpc_helper_function (cmd, SqlDbType.Decimal, 0, -10.01); + */ + break; + case 6 : + // Test Float Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"float")); + rpc_helper_function (cmd, SqlDbType.Float, 0, 10.0); + rpc_helper_function (cmd, SqlDbType.Float, 0, 0); + /* + rpc_helper_function (cmd, SqlDbType.Float, 0, null); + */ + break; + case 7 : + // Testing Image + /* NOT WORKING + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query, "image")); + rpc_helper_function (cmd, SqlDbType.Image, 0, ); + rpc_helper_function (cmd, SqlDbType.Image, 0, ); + rpc_helper_function (cmd, SqlDbType.Image, 0, ); + /* NOT WORKING*/ + break; + case 8 : + // Test Integer Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"int")); + rpc_helper_function (cmd, SqlDbType.Int, 0, 10); + /* + rpc_helper_function (cmd, SqlDbType.Int, 0, null); + */ + break; + case 9 : + // Test Money Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"money")); + /* + rpc_helper_function (cmd, SqlDbType.Money, 0, 10.0); + rpc_helper_function (cmd, SqlDbType.Money, 0, null); + */ + break; + case 23 : + // Test NChar Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"nchar(10)")); + /* + rpc_helper_function (cmd, SqlDbType.NChar, 10, "nchar"); + rpc_helper_function (cmd, SqlDbType.NChar, 10, ""); + rpc_helper_function (cmd, SqlDbType.NChar, 10, null); + */ + break; + case 10 : + // Test NText Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"ntext")); + /* + rpc_helper_function (cmd, SqlDbType.NText, 0, "ntext"); + rpc_helper_function (cmd, SqlDbType.NText, 0, ""); + rpc_helper_function (cmd, SqlDbType.NText, 0, null); + */ + break; + case 11 : + // Test NVarChar Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"nvarchar(10)")); + rpc_helper_function (cmd, SqlDbType.NVarChar, 10, "nvarchar"); + rpc_helper_function (cmd, SqlDbType.NVarChar, 10, ""); + //rpc_helper_function (cmd, SqlDbType.NVarChar, 10, null); + break; + case 12 : + // Test Real Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"real")); + rpc_helper_function (cmd, SqlDbType.Real, 0, 10.0); + //rpc_helper_function (cmd, SqlDbType.Real, 0, null); + break; + case 13 : + // Test SmallDateTime Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"smalldatetime")); + rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0, "6/6/2079 11:59:00 PM"); + /* + rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0, ""); + rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0, null); + */ + break; + case 14 : + // Test SmallInt Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"smallint")); + rpc_helper_function (cmd, SqlDbType.SmallInt, 0, 10); + rpc_helper_function (cmd, SqlDbType.SmallInt, 0, -10); + //rpc_helper_function (cmd, SqlDbType.SmallInt, 0, null); + break; + case 15 : + // Test SmallMoney Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"smallmoney")); + /* + rpc_helper_function (cmd, SqlDbType.SmallMoney, 0, 10.0); + rpc_helper_function (cmd, SqlDbType.SmallMoney, 0, -10.0); + rpc_helper_function (cmd, SqlDbType.SmallMoney, 0, null); + */ + break; + case 16 : + // Test Text Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"text")); + /* + rpc_helper_function (cmd, SqlDbType.Text, 0, "text"); + rpc_helper_function (cmd, SqlDbType.Text, 0, ""); + rpc_helper_function (cmd, SqlDbType.Text, 0, null); + */ + break; + case 17 : + // Test TimeStamp Param + /* NOT WORKING + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"timestamp")); + rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, ""); + rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, ""); + rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, null); + */ + break; + case 18 : + // Test TinyInt Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"tinyint")); + /* + rpc_helper_function (cmd, SqlDbType.TinyInt, 0, 10); + rpc_helper_function (cmd, SqlDbType.TinyInt, 0, -10); + rpc_helper_function (cmd, SqlDbType.TinyInt, 0, null); + */ + break; + case 19 : + // Test UniqueIdentifier Param + /* + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"uniqueidentifier")); + rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, "0f159bf395b1d04f8c2ef5c02c3add96"); + rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, null); + */ + break; + case 20 : + // Test VarBinary Param + /* NOT WORKING + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"varbinary (10)")); + rpc_helper_function (cmd, SqlDbType.VarBinary, 0,); + rpc_helper_function (cmd, SqlDbType.VarBinary, 0,); + rpc_helper_function (cmd, SqlDbType.VarBinary, 0, null); + */ + break; + case 21 : + // Test Varchar Param + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"varchar(10)")); + rpc_helper_function (cmd, SqlDbType.VarChar, 10, "VarChar"); + break; + case 22 : + // Test Variant Param + /* NOT WORKING + DBHelper.ExecuteNonQuery (conn, + String.Format(create_query,"variant")); + rpc_helper_function (cmd, SqlDbType.Variant, 0, ); + rpc_helper_function (cmd, SqlDbType.Variant, 0, ); + rpc_helper_function (cmd, SqlDbType.Variant, 0, null); + */ + break; + default : + label = -2; + break; + } + }catch (AssertionException e) { + error += String.Format (" Case {0} INCORRECT VALUE : {1}\n",label, e.Message); + }catch (Exception e) { + error += String.Format (" Case {0} NOT WORKING : {1}\n",label, e.Message); + } + + label++; + if (label != -1) + DBHelper.ExecuteNonQuery (conn, drop_query); + } + if (error != String.Empty) + Assert.Fail (error); + } + + private void rpc_helper_function (SqlCommand cmd, SqlDbType type, int size, object val) + { + cmd.Parameters.Clear (); + SqlParameter param1 ; + SqlParameter param2 ; + if (size != 0) { + param1 = new SqlParameter ("@param1", type, size); + param2 = new SqlParameter ("@param2", type, size); + } + else { + param1 = new SqlParameter ("@param1", type); + param2 = new SqlParameter ("@param2", type); + } + + SqlParameter retval = new SqlParameter ("retval", SqlDbType.Int); + param1.Value = val; + param1.Direction = ParameterDirection.Input; + param2.Direction = ParameterDirection.Output; + retval.Direction = ParameterDirection.ReturnValue; + cmd.Parameters.Add (param1); + cmd.Parameters.Add (param2); + cmd.Parameters.Add (retval); + cmd.CommandText = "#tmp_sp_param_test"; + cmd.CommandType = CommandType.StoredProcedure; + using (SqlDataReader reader = cmd.ExecuteReader ()) { + while (reader.Read ()) { + if (param1.Value != null && param1.Value.GetType () == typeof (string)) + Assert.AreEqual (param1.Value, + reader.GetValue(0).ToString (),"#1"); + else + Assert.AreEqual (param1.Value, + reader.GetValue(0),"#1"); + } + } + if (param1.Value != null && param1.Value.GetType () == typeof (string) && param2.Value != null) + Assert.AreEqual (param1.Value.ToString (), param2.Value.ToString (), "#2"); + else + Assert.AreEqual (param1.Value, param2.Value, "#2"); + Assert.AreEqual (5, retval.Value, "#3"); + } + + [Test] + [ExpectedException (typeof (InvalidOperationException))] + public void OutputParamSizeTest1 () + { + conn = (SqlConnection) ConnectionManager.Singleton.Connection; + ConnectionManager.Singleton.OpenConnection (); + cmd = new SqlCommand (); + cmd.Connection = conn; + + cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return"; + cmd.CommandType = CommandType.Text; + cmd.ExecuteNonQuery (); + + cmd.CommandText = "#testsize"; + cmd.CommandType = CommandType.StoredProcedure; + + SqlParameter p1 = new SqlParameter (); + p1.ParameterName = "@p1"; + p1.Direction = ParameterDirection.InputOutput; + p1.DbType = DbType.String; + p1.IsNullable = false; + cmd.Parameters.Add (p1); + cmd.ExecuteNonQuery (); + } + + [Test] + [ExpectedException (typeof (InvalidOperationException))] + public void OutputParamSizeTest2 () + { + conn = (SqlConnection) ConnectionManager.Singleton.Connection; + ConnectionManager.Singleton.OpenConnection (); + cmd = new SqlCommand (); + cmd.Connection = conn; + + cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return"; + cmd.CommandType = CommandType.Text; + cmd.ExecuteNonQuery (); + + cmd.CommandText = "#testsize"; + cmd.CommandType = CommandType.StoredProcedure; + + SqlParameter p1 = new SqlParameter (); + p1.ParameterName = "@p1"; + p1.Direction = ParameterDirection.Output; + p1.DbType = DbType.String; + p1.IsNullable = false; + cmd.Parameters.Add (p1); + cmd.ExecuteNonQuery (); + } + + [Test] + [ExpectedException (typeof (InvalidOperationException))] + public void OutputParamSizeTest3 () + { + conn = (SqlConnection) ConnectionManager.Singleton.Connection; + ConnectionManager.Singleton.OpenConnection (); + cmd = new SqlCommand (); + cmd.Connection = conn; + + cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return"; + cmd.CommandType = CommandType.Text; + cmd.ExecuteNonQuery (); + + cmd.CommandText = "#testsize"; + cmd.CommandType = CommandType.StoredProcedure; + + SqlParameter p1 = new SqlParameter (); + p1.ParameterName = "@p1"; + p1.Direction = ParameterDirection.InputOutput; + p1.DbType = DbType.String; + p1.IsNullable = true; + cmd.Parameters.Add (p1); + cmd.ExecuteNonQuery (); + } + + [Test] + [ExpectedException (typeof (InvalidOperationException))] + public void OutputParamSizeTest4 () + { + conn = (SqlConnection) ConnectionManager.Singleton.Connection; + ConnectionManager.Singleton.OpenConnection (); + cmd = new SqlCommand (); + cmd.Connection = conn; + + cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return"; + cmd.CommandType = CommandType.Text; + cmd.ExecuteNonQuery (); + + cmd.CommandText = "#testsize"; + cmd.CommandType = CommandType.StoredProcedure; + + SqlParameter p1 = new SqlParameter (); + p1.ParameterName = "@p1"; + p1.Direction = ParameterDirection.Output; + p1.DbType = DbType.String; + p1.IsNullable = true; + cmd.Parameters.Add (p1); + cmd.ExecuteNonQuery (); + } + +#if NET_2_0 + [Test] + public void NotificationTest () + { + cmd = new SqlCommand (); + SqlNotificationRequest notification = new SqlNotificationRequest("MyNotification","MyService",15); + Assert.AreEqual (null, cmd.Notification, "#1 The default value for this property should be null"); + cmd.Notification = notification; + Assert.AreEqual ("MyService", cmd.Notification.Options, "#2 The value should be MyService as the constructor is initiated with this value"); + Assert.AreEqual (15, cmd.Notification.Timeout, "#2 The value should be 15 as the constructor is initiated with this value"); + } + + [Test] + public void NotificationAutoEnlistTest () + { + cmd = new SqlCommand (); + Assert.AreEqual (true, cmd.NotificationAutoEnlist, "#1 Default value of the property should be true"); + cmd.NotificationAutoEnlist = false; + Assert.AreEqual (false, cmd.NotificationAutoEnlist, "#2 The value of the property should be false after setting it to false"); + } + + [Test] + public void BeginExecuteXmlReaderTest () + { + cmd = new SqlCommand (); + string connectionString1 = null; + connectionString1 = ConnectionManager.Singleton.ConnectionString + "Asynchronous Processing=true"; + try { + SqlConnection conn1 = new SqlConnection (connectionString1); + conn1.Open (); + cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA" ; + cmd.Connection = conn1; + + IAsyncResult result = cmd.BeginExecuteXmlReader (); + XmlReader reader = cmd.EndExecuteXmlReader (result); + while (reader.Read ()) + { + if (reader.LocalName.ToString () == "employee") + { + Assert.AreEqual ("kumar", reader["lname"], "#1 "); + } + } + } finally { + ConnectionManager.Singleton.CloseConnection (); + } + } + + [Test] + public void BeginExecuteXmlReaderExceptionTest () + { + cmd = new SqlCommand (); + try { + SqlConnection conn = new SqlConnection (connectionString); + conn.Open (); + cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA" ; + cmd.Connection = conn; + + try { + /*IAsyncResult result = */cmd.BeginExecuteXmlReader (); + } catch (InvalidOperationException) { + Assert.AreEqual (ConnectionManager.Singleton.ConnectionString, connectionString, "#1 Connection string has changed"); + return; + } + Assert.Fail ("Expected Exception InvalidOperationException not thrown"); + } finally { + ConnectionManager.Singleton.CloseConnection (); + } + } + + [Test] + public void SqlCommandDisposeTest () + { + IDataReader reader = null; + try { + conn = (SqlConnection) ConnectionManager.Singleton.Connection; + ConnectionManager.Singleton.OpenConnection (); + + IDbCommand command = conn.CreateCommand (); + try { + string sql = "SELECT * FROM employee"; + command.CommandText = sql; + reader = command.ExecuteReader(); + } finally { + command.Dispose(); + } + while (reader.Read()); + } finally { + reader.Dispose(); + ConnectionManager.Singleton.CloseConnection (); + } + } + + [Test] + public void CloneObjTest () + { + SqlCommand cmd = new SqlCommand(); + cmd.CommandText = "sp_insert"; + cmd.CommandType = CommandType.StoredProcedure; + Object TestPar = DBNull.Value; + cmd.Parameters.Add ("@TestPar1", SqlDbType.Int); + cmd.Parameters ["@TestPar1"].Value = TestPar; +#if NET_2_0 + cmd.Parameters.AddWithValue ("@BirthDate", DateTime.Now); +#else + cmd.Parameters.Add ("@BirthDate", DateTime.Now); +#endif + cmd.DesignTimeVisible = true; + cmd.CommandTimeout = 100; + SqlCommand cmd1 = cmd.Clone (); + Assert.AreEqual (2, cmd1.Parameters.Count); + Assert.AreEqual (100, cmd1.CommandTimeout); +#if NET_2_0 + cmd1.Parameters.AddWithValue ("@test", DateTime.Now); +#else + cmd1.Parameters.Add ("@test", DateTime.Now); +#endif + Assert.AreEqual (3, cmd1.Parameters.Count); + Assert.AreEqual (2, cmd.Parameters.Count); + } +#endif + private enum Status { OK = 0, Error = 3 } + private readonly string CREATE_TMP_SP_PARAM_TEST = "create procedure #tmp_sp_param_test (@param1 {0}, @param2 {0} output) as begin select @param1 set @param2=@param1 return 5 end"; + private readonly string DROP_TMP_SP_PARAM_TEST = "drop procedure #tmp_sp_param_test"; + private readonly string CREATE_TMP_SP_TEMP_INSERT_PERSON = ("create procedure #sp_temp_insert_employee ( " + Environment.NewLine + "@fname varchar (20)) " + Environment.NewLine + "as " + Environment.NewLine +