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;
}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
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";
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]
}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
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;
// 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
// 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");
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;
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
}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
}
}
}
[Test]
+#if NET_2_0
+ [ExpectedException (typeof(ArgumentOutOfRangeException))]
+#else
[ExpectedException (typeof(ArgumentException))]
+#endif
public void CommandTypeTest ()
{
cmd = new SqlCommand ();
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 ();
// Need to add more tests
[Test]
+#if NET_2_0
+ [ExpectedException (typeof(ArgumentOutOfRangeException))]
+#else
[ExpectedException (typeof(ArgumentException))]
+#endif
public void UpdatedRowSourceTest ()
{
cmd = new SqlCommand ();
Object TestPar = "test";
cmd.Parameters.Add("@fname", SqlDbType.VarChar);
cmd.Parameters ["@fname"].Value = TestPar;
- Assert.AreEqual(-1,cmd.ExecuteNonQuery());
+ Assert.AreEqual(1,cmd.ExecuteNonQuery());
} finally {
DBHelper.ExecuteNonQuery (conn, DROP_TMP_SP_TEMP_INSERT_PERSON);
DBHelper.ExecuteSimpleSP (conn, "sp_clean_person_table");
}
}
+ // 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)
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 +
projects / mono.git / blobdiff