mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlCommandTest.cs

   1 //
   2 // SqlCommandTest.cs - NUnit Test Cases for testing the
   3 //                          SqlCommand class
   4 // Author:
   5 //      Umadevi S (sumadevi@novell.com)
   6 //      Sureshkumar T (tsureshkumar@novell.com)
   7 //      Senganal T (tsenganal@novell.com)
   8 //
   9 // Copyright (c) 2004 Novell Inc., and the individuals listed
  10 // on the ChangeLog entries.
  11 //
  12 // Permission is hereby granted, free of charge, to any person obtaining
  13 // a copy of this software and associated documentation files (the
  14 // "Software"), to deal in the Software without restriction, including
  15 // without limitation the rights to use, copy, modify, merge, publish,
  16 // distribute, sublicense, and/or sell copies of the Software, and to
  17 // permit persons to whom the Software is furnished to do so, subject to
  18 // the following conditions:
  19 //
  20 // The above copyright notice and this permission notice shall be
  21 // included in all copies or substantial portions of the Software.
  22 //
  23 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  24 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  25 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  26 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  27 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  28 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  29 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  30 //
  31
  32 using System;
  33 using System.Data;
  34 using System.Data.Common;
  35 using System.Data.SqlClient;
  36 #if NET_2_0
  37 using System.Data.Sql;
  38 using System.Xml;
  39 #endif
  40
  41 using NUnit.Framework;
  42
  43 namespace MonoTests.System.Data.SqlClient
  44 {
  45         [TestFixture]
  46         [Category ("sqlserver")]
  47         public class SqlCommandTest
  48         {
  49
  50                 public SqlConnection conn = null ;
  51                 SqlCommand cmd = null;
  52                 string connectionString = ConnectionManager.Singleton.ConnectionString;
  53
  54                 [SetUp]
  55                 public void Setup ()
  56                 {
  57                 }
  58
  59                 [TearDown]
  60                 public void TearDown ()
  61                 {
  62                         if (conn != null)
  63                                 conn.Close ();
  64                 }
  65
  66                 [Test]
  67                 public void ConstructorTest ()
  68                 {
  69                         // Test Default Constructor
  70                         cmd = new SqlCommand ();
  71                         Assert.AreEqual (String.Empty, cmd.CommandText,
  72                                  "#1 Command Test should be empty");
  73                         Assert.AreEqual (30, cmd.CommandTimeout,
  74                                 "#2 CommandTimeout should be 30");
  75                         Assert.AreEqual (CommandType.Text, cmd.CommandType,
  76                                 "#3 CommandType should be text");
  77                         Assert.IsNull (cmd.Connection, "#4 Connection Should be null");
  78                         Assert.AreEqual (0, cmd.Parameters.Count,
  79                                 "#5 Parameter shud be empty");
  80
  81                         // Test Overloaded Constructor
  82                         String cmdText = "select * from tbl1" ;
  83                         cmd = new SqlCommand (cmdText);
  84                         Assert.AreEqual (cmdText, cmd.CommandText,
  85                                 "#5 CommandText should be the same as passed");
  86                         Assert.AreEqual (30, cmd.CommandTimeout,
  87                                 "#6 CommandTimeout should be 30");
  88                         Assert.AreEqual (CommandType.Text, cmd.CommandType,
  89                                 "#7 CommandType should be text");
  90                         Assert.IsNull (cmd.Connection , "#8 Connection Should be null");
  91
  92                         // Test Overloaded Constructor
  93                         SqlConnection conn = new SqlConnection ();
  94                         cmd = new SqlCommand (cmdText , conn);
  95                         Assert.AreEqual (cmdText, cmd.CommandText,
  96                                 "#9 CommandText should be the same as passed");
  97                         Assert.AreEqual (30, cmd.CommandTimeout,
  98                                 "#10 CommandTimeout should be 30");
  99                         Assert.AreEqual (CommandType.Text, cmd.CommandType,
 100                                 "#11 CommandType should be text");
 101                         Assert.AreSame (cmd.Connection, conn, "#12 Connection Should be same");
 102
 103                         // Test Overloaded Constructor
 104                         SqlTransaction trans = null ;
 105                         try {
 106                                 conn = new SqlConnection (connectionString);
 107                                 conn.Open ();
 108                                 trans = conn.BeginTransaction ();
 109                                 cmd = new SqlCommand (cmdText, conn, trans);
 110                                 Assert.AreEqual (cmdText, cmd.CommandText,
 111                                         "#9 CommandText should be the same as passed");
 112                                 Assert.AreEqual (30, cmd.CommandTimeout,
 113                                         "#10 CommandTimeout should be 30");
 114                                 Assert.AreEqual (CommandType.Text, cmd.CommandType,
 115                                         "#11 CommandType should be text");
 116                                 Assert.AreEqual (cmd.Connection, conn,
 117                                         "#12 Connection Should be null");
 118                                 Assert.AreEqual (cmd.Transaction, trans,
 119                                          "#13 Transaction Property should be set");
 120
 121                                 // Test if parameters are reset to Default Values
 122                                 cmd = new SqlCommand ();
 123                                 Assert.AreEqual (String.Empty, cmd.CommandText,
 124                                         "#1 Command Test should be empty");
 125                                 Assert.AreEqual (30, cmd.CommandTimeout,
 126                                         "#2 CommandTimeout should be 30");
 127                                 Assert.AreEqual (CommandType.Text, cmd.CommandType,
 128                                         "#3 CommandType should be text");
 129                                 Assert.IsNull (cmd.Connection, "#4 Connection Should be null");
 130                         }finally {
 131                                 trans.Rollback ();
 132                         }
 133                 }
 134
 135                 [Test]
 136                 public void ExecuteScalarTest ()
 137                 {
 138                         conn = new SqlConnection (connectionString);
 139                         cmd = new SqlCommand ("" , conn);
 140                         cmd.CommandText = "Select count(*) from numeric_family where id<=4";
 141
 142                         //Check Exception is thrown when executed on a closed connection
 143                         try {
 144                                 cmd.ExecuteScalar ();
 145                                 Assert.Fail ("#1 InvalidOperation Exception must be thrown");
 146                         }catch (AssertionException e) {
 147                                 throw e;
 148                         }catch (Exception e) {
 149 #if NET_2_0
 150                                 Assert.AreEqual (typeof (NullReferenceException), e.GetType (),
 151                                         "#2 Incorrect Exception : " + e.StackTrace);
 152 #else
 153                                 Assert.AreEqual (typeof (InvalidOperationException), e.GetType (),
 154                                         "#2 Incorrect Exception : " + e.StackTrace);
 155 #endif
 156                         }
 157
 158                         // Check the Return value for a Correct Query
 159                         object result = 0;
 160                         conn.Open ();
 161                         result = cmd.ExecuteScalar ();
 162                         Assert.AreEqual (4, (int)result, "#3 Query Result returned is incorrect");
 163
 164                         cmd.CommandText = "select id , type_bit from numeric_family order by id asc" ;
 165                         result = Convert.ToInt32 (cmd.ExecuteScalar ());
 166                         Assert.AreEqual (1, result,
 167                                 "#4 ExecuteScalar Should return (1,1) the result set" );
 168
 169                         cmd.CommandText = "select id from numeric_family where id=-1";
 170                         result = cmd.ExecuteScalar ();
 171                         Assert.IsNull (result, "#5 Null should be returned if result set is empty");
 172
 173                         // Check SqlException is thrown for Invalid Query
 174                         cmd.CommandText = "select count* from numeric_family";
 175                         try {
 176                                 result = cmd.ExecuteScalar ();
 177                                 Assert.Fail ("#6 InCorrect Query should cause an SqlException");
 178                         }catch (AssertionException e) {
 179                                 throw e;
 180                         }catch (Exception e) {
 181                                 Assert.AreEqual (typeof(SqlException), e.GetType(),
 182                                         "#7 Incorrect Exception : " + e.StackTrace);
 183                         }
 184
 185
 186                         // Parameterized stored procedure calls
 187
 188                         int int_value = 20;
 189                         string string_value = "output value changed";
 190                         string return_value = "first column of first rowset";
 191
 192                         cmd.CommandText =
 193                                 "create procedure #tmp_executescalar_outparams "+
 194                                 " (@p1 int, @p2 int out, @p3 varchar(200) out) "+
 195                                 "as " +
 196                                 "select '" + return_value + "' as 'col1', @p1 as 'col2' "+
 197                                 "set @p2 = @p2 * 2 "+
 198                                 "set @p3 = N'" + string_value + "' "+
 199                                 "select 'second rowset' as 'col1', 2 as 'col2' "+
 200                                 "return 1";
 201
 202                         cmd.CommandType = CommandType.Text;
 203                         cmd.ExecuteNonQuery ();
 204
 205                         cmd.CommandText = "#tmp_executescalar_outparams";
 206                         cmd.CommandType = CommandType.StoredProcedure;
 207
 208                         SqlParameter p1 = new SqlParameter ();
 209                         p1.ParameterName = "@p1";
 210                         p1.Direction = ParameterDirection.Input;
 211                         p1.DbType = DbType.Int32;
 212                         p1.Value = int_value;
 213                         cmd.Parameters.Add (p1);
 214
 215                         SqlParameter p2 = new SqlParameter ();
 216                         p2.ParameterName = "@p2";
 217                         p2.Direction = ParameterDirection.InputOutput;
 218                         p2.DbType = DbType.Int32;
 219                         p2.Value = int_value;
 220                         cmd.Parameters.Add (p2);
 221
 222                         SqlParameter p3 = new SqlParameter ();
 223                         p3.ParameterName = "@p3";
 224                         p3.Direction = ParameterDirection.Output;
 225                         p3.DbType = DbType.String;
 226                         p3.Size = 200;
 227                         cmd.Parameters.Add (p3);
 228
 229                         result = cmd.ExecuteScalar ();
 230                         Assert.AreEqual (return_value, result, "#8 ExecuteScalar Should return 'first column of first rowset'");
 231                         Assert.AreEqual (int_value * 2, p2.Value, "#9 ExecuteScalar should fill the parameter collection with the outputted values");
 232                         Assert.AreEqual (string_value, p3.Value, "#10 ExecuteScalar should fill the parameter collection with the outputted values");
 233
 234                         p3.Size = 0;
 235                         p3.Value = null;
 236                         try {
 237                                 cmd.ExecuteScalar ();
 238                                 Assert.Fail ("#11 Query should throw System.InvalidOperationException due to size = 0 and value = null");
 239                         }
 240                         catch (AssertionException e) {
 241                                 throw e;
 242                         }
 243                         catch (Exception e) {
 244                                 Assert.AreEqual (typeof (InvalidOperationException), e.GetType (),
 245                                         "#12 Incorrect Exception : " + e.StackTrace);
 246                         }
 247
 248                         conn.Close ();
 249
 250                 }
 251
 252                 [Test]
 253                 public void ExecuteNonQuery ()
 254                 {
 255                         conn = new SqlConnection (connectionString);
 256                         cmd = new SqlCommand ("", conn);
 257                         int result = 0;
 258
 259                         // Test for exceptions
 260                         // Test exception is thrown if connection is closed
 261                         cmd.CommandText = "Select id from numeric_family where id=1";
 262                         try {
 263                                 cmd.ExecuteNonQuery ();
 264                                 Assert.Fail ("#1 Connextion shud be open");
 265                         }catch (AssertionException e) {
 266                                 throw e;
 267                         }catch (Exception e) {
 268 #if NET_2_0
 269                                 Assert.AreEqual (typeof(NullReferenceException), e.GetType(),
 270                                         "#2 Incorrect Exception : " + e);
 271 #else
 272                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 273                                         "#2 Incorrect Exception : " + e);
 274 #endif
 275                         }
 276
 277                         // Test Exception is thrown if Query is incorrect
 278                         conn.Open ();
 279                         cmd.CommandText = "Select id1 from numeric_family";
 280                         try {
 281                                 cmd.ExecuteNonQuery ();
 282                                 Assert.Fail ("#1 invalid Query");
 283                         }catch (AssertionException e) {
 284                                 throw e;
 285                         }catch (Exception e) {
 286                                 Assert.AreEqual (typeof(SqlException), e.GetType(),
 287                                         "#2 Incorrect Exception : " + e);
 288                         }
 289
 290                         // Test Select/Insert/Update/Delete Statements
 291                         SqlTransaction trans = conn.BeginTransaction ();
 292                         cmd.Transaction = trans;
 293
 294                         try {
 295                                 cmd.CommandText = "Select id from numeric_family where id=1";
 296                                 result = cmd.ExecuteNonQuery ();
 297                                 Assert.AreEqual (-1, result, "#1");
 298
 299                                 cmd.CommandText = "Insert into numeric_family (id,type_int) values (100,200)";
 300                                 result = cmd.ExecuteNonQuery ();
 301                                 Assert.AreEqual (1, result, "#2 One row shud be inserted");
 302
 303                                 cmd.CommandText = "Update numeric_family set type_int=300 where id=100";
 304                                 result = cmd.ExecuteNonQuery ();
 305                                 Assert.AreEqual (1, result, "#3 One row shud be updated");
 306
 307                                 // Test Batch Commands
 308                                 cmd.CommandText = "Select id from numeric_family where id=1;";
 309                                 cmd.CommandText += "update numeric_family set type_int=10 where id=1000";
 310                                 cmd.CommandText += "update numeric_family set type_int=10 where id=100";
 311                                 result = cmd.ExecuteNonQuery ();
 312                                 Assert.AreEqual (1, result, "#4 One row shud be updated");
 313
 314                                 cmd.CommandText = "Delete from numeric_family where id=100";
 315                                 result = cmd.ExecuteNonQuery ();
 316                                 Assert.AreEqual (1, result, "#5 One row shud be deleted");
 317
 318                         }finally {
 319                                 trans.Rollback ();
 320                         }
 321
 322
 323                         // Parameterized stored procedure calls
 324
 325                         int int_value = 20;
 326                         string string_value = "output value changed";
 327
 328                         cmd.CommandText =
 329                                 "create procedure #tmp_executescalar_outparams " +
 330                                 " (@p1 int, @p2 int out, @p3 varchar(200) out) " +
 331                                 "as " +
 332                                 "select 'test' as 'col1', @p1 as 'col2' " +
 333                                 "set @p2 = @p2 * 2 " +
 334                                 "set @p3 = N'" + string_value + "' " +
 335                                 "select 'second rowset' as 'col1', 2 as 'col2' " +
 336                                 "return 1";
 337
 338                         cmd.CommandType = CommandType.Text;
 339                         cmd.ExecuteNonQuery ();
 340
 341                         cmd.CommandText = "#tmp_executescalar_outparams";
 342                         cmd.CommandType = CommandType.StoredProcedure;
 343
 344                         SqlParameter p1 = new SqlParameter ();
 345                         p1.ParameterName = "@p1";
 346                         p1.Direction = ParameterDirection.Input;
 347                         p1.DbType = DbType.Int32;
 348                         p1.Value = int_value;
 349                         cmd.Parameters.Add (p1);
 350
 351                         SqlParameter p2 = new SqlParameter ();
 352                         p2.ParameterName = "@p2";
 353                         p2.Direction = ParameterDirection.InputOutput;
 354                         p2.DbType = DbType.Int32;
 355                         p2.Value = int_value;
 356                         cmd.Parameters.Add (p2);
 357
 358                         SqlParameter p3 = new SqlParameter ();
 359                         p3.ParameterName = "@p3";
 360                         p3.Direction = ParameterDirection.Output;
 361                         p3.DbType = DbType.String;
 362                         p3.Size = 200;
 363                         cmd.Parameters.Add (p3);
 364
 365                         cmd.ExecuteNonQuery ();
 366                         Assert.AreEqual (int_value * 2, p2.Value, "#6 ExecuteNonQuery should fill the parameter collection with the outputted values");
 367                         Assert.AreEqual (string_value, p3.Value, "#7 ExecuteNonQuery should fill the parameter collection with the outputted values");
 368                 }
 369
 370                 [Test]
 371                 public void ExecuteReaderTest ()
 372                 {
 373                         //SqlDataReader reader = null;
 374                         conn = new SqlConnection (connectionString);
 375
 376                         // Test exception is thrown if conn is closed
 377                         cmd = new SqlCommand ("Select count(*) from numeric_family");
 378                         try {
 379                                 /*reader = */cmd.ExecuteReader ();
 380                         }catch (AssertionException e) {
 381                                 throw e;
 382                         }catch (Exception e) {
 383 #if NET_2_0
 384                                 Assert.AreEqual (typeof(NullReferenceException), e.GetType(),
 385                                         "#1 Incorrect Exception");
 386 #else
 387                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 388                                         "#1 Incorrect Exception");
 389 #endif
 390                         }
 391
 392                         conn.Open ();
 393                         // Test exception is thrown for Invalid Query
 394                         cmd = new SqlCommand ("InvalidQuery", conn);
 395                         try {
 396                                 /*reader = */cmd.ExecuteReader ();
 397                                 Assert.Fail ("#1 Exception shud be thrown");
 398                         }catch (AssertionException e) {
 399                                 throw e;
 400                         }catch (Exception e) {
 401                                 Assert.AreEqual (typeof(SqlException), e.GetType (),
 402                                         "#2 Incorrect Exception : " + e);
 403                         }
 404
 405                         // NOTE
 406                         // Test SqlException is thrown if a row is locked
 407                         // should lock a particular row and then modify it
 408                         /*
 409                         */
 410
 411                         // Test Connection  cannot be modified when reader is in use
 412                         // NOTE : msdotnet contradicts documented behavior
 413                         /*
 414                         cmd.CommandText = "select * from numeric_family where id=1";
 415                         reader = cmd.ExecuteReader ();
 416                         reader.Read ();
 417                         conn.Close (); // valid operation
 418                         conn = new SqlConnection (connectionString);
 419                         */
 420                         /*
 421                         // NOTE msdotnet contradcits documented behavior
 422                         // If the above testcase fails, then this shud be tested
 423                         // Test connection can be modified once reader is closed
 424                         conn.Close ();
 425                         reader.Close ();
 426                         conn = new SqlConnection (connectionString); // valid operation
 427                         */
 428                 }
 429
 430                 [Test]
 431                 public void ExecuteReaderCommandBehaviorTest ()
 432                 {
 433                         // Test for command behaviors
 434                         DataTable schemaTable = null;
 435                         SqlDataReader reader = null;
 436
 437                         conn = new SqlConnection (connectionString);
 438                         conn.Open ();
 439                         cmd = new SqlCommand ("", conn);
 440                         cmd.CommandText = "Select id from numeric_family where id <=4 order by id asc;";
 441                         cmd.CommandText += "Select type_bit from numeric_family where id <=4 order by id asc";
 442
 443                         // Test for default command behavior
 444                         reader = cmd.ExecuteReader ();
 445                         int rows = 0;
 446                         int results = 0;
 447                         do {
 448                                 while (reader.Read ())
 449                                         rows++ ;
 450                                 Assert.AreEqual (4, rows, "#1 Multiple rows shud be returned");
 451                                 results++;
 452                                 rows = 0;
 453                         }while (reader.NextResult());
 454                         Assert.AreEqual (2, results, "#2 Multiple result sets shud be returned");
 455                         reader.Close ();
 456
 457                         // Test if closing reader, closes the connection
 458                         reader = cmd.ExecuteReader (CommandBehavior.CloseConnection);
 459                         reader.Close ();
 460                         Assert.AreEqual (ConnectionState.Closed, conn.State,
 461                                 "#3 Command Behavior is not followed");
 462                         conn.Open();
 463
 464                         // Test if row info and primary Key info is returned
 465                         reader = cmd.ExecuteReader (CommandBehavior.KeyInfo);
 466                         schemaTable = reader.GetSchemaTable ();
 467                         Assert.IsTrue(reader.HasRows, "#4 Data Rows shud also be returned");
 468                         Assert.IsTrue ((bool)schemaTable.Rows[0]["IsKey"],
 469                                 "#5 Primary Key info shud be returned");
 470                         reader.Close ();
 471
 472                         // Test only column information is returned
 473                         reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly);
 474                         schemaTable = reader.GetSchemaTable ();
 475                         Assert.IsFalse (reader.HasRows, "#6 row data shud not be returned");
 476                         Assert.AreEqual(DBNull.Value, schemaTable.Rows[0]["IsKey"],
 477                                 "#7 Primary Key info shud not be returned");
 478                         Assert.AreEqual ("id", schemaTable.Rows[0]["ColumnName"],
 479                                 "#8 Schema Data is Incorrect");
 480                         reader.Close ();
 481
 482                         // Test only one result set (first) is returned
 483                         reader = cmd.ExecuteReader (CommandBehavior.SingleResult);
 484                         schemaTable = reader.GetSchemaTable ();
 485                         Assert.IsFalse (reader.NextResult(),
 486                                 "#9 Only one result set shud be returned");
 487                         Assert.AreEqual ("id", schemaTable.Rows[0]["ColumnName"],
 488                                 "#10 The result set returned shud be the first result set");
 489                         reader.Close ();
 490
 491                         // Test only one row is returned for all result sets
 492                         // msdotnet doesnt work correctly.. returns only one result set
 493                         reader = cmd.ExecuteReader (CommandBehavior.SingleRow);
 494                         rows=0;
 495                         results=0;
 496                         do {
 497                                 while (reader.Read ())
 498                                         rows++ ;
 499                                 Assert.AreEqual (1, rows, "#11 Only one row shud be returned");
 500                                 results++;
 501                                 rows = 0;
 502                         }while (reader.NextResult());
 503                         // NOTE msdotnet contradicts documented behavior.
 504                         // Multiple result sets shud be returned , and in this case : 2
 505                         //Assert.AreEqual (2, results, "# Multiple result sets shud be returned");
 506                         Assert.AreEqual (2, results, "#12 Multiple result sets shud be returned");
 507                         reader.Close ();
 508                 }
 509
 510                 [Test]
 511                 public void PrepareTest_CheckValidStatement ()
 512                 {
 513                         cmd = new SqlCommand ();
 514                         conn = new SqlConnection (connectionString);
 515                         conn.Open ();
 516
 517                         cmd.CommandText = "Select id from numeric_family where id=@ID" ;
 518                         cmd.Connection = conn ;
 519
 520                         // Test if Parameters are correctly populated
 521                         cmd.Parameters.Clear ();
 522                         cmd.Parameters.Add ("@ID", SqlDbType.TinyInt);
 523                         cmd.Parameters["@ID"].Value = 2 ;
 524                         cmd.Prepare ();
 525                         Assert.AreEqual (2, cmd.ExecuteScalar (), "#3 Prepared Stmt not working");
 526
 527                         cmd.Parameters[0].Value = 3;
 528                         Assert.AreEqual (3, cmd.ExecuteScalar (), "#4 Prep Stmt not working");
 529                         conn.Close ();
 530                 }
 531
 532                 [Test]
 533                 public void PrepareTest ()
 534                 {
 535                         cmd = new SqlCommand ();
 536                         conn = new SqlConnection (connectionString);
 537                         conn.Open ();
 538
 539                         cmd.CommandText = "Select id from numeric_family where id=@ID" ;
 540                         cmd.Connection = conn ;
 541
 542                         // Test InvalidOperation Exception is thrown if Parameter Type
 543                         // is not explicitly set
 544 #if NET_2_0
 545                         cmd.Parameters.AddWithValue ("@ID", 2);
 546 #else
 547                         cmd.Parameters.Add ("@ID", 2);
 548 #endif
 549                         try {
 550                                 cmd.Prepare ();
 551                                 Assert.Fail ("#1 Parameter Type shud be explicitly Set");
 552                         }catch (AssertionException e) {
 553                                 throw e;
 554                         }catch (Exception e) {
 555                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType (),
 556                                         "#2 Incorrect Exception : " + e.StackTrace);
 557                         }
 558
 559                         // Test Exception is thrown for variable size data  if precision/scale
 560                         // is not set
 561                         cmd.CommandText = "select type_varchar from string_family where type_varchar=@p1";
 562                         cmd.Parameters.Clear ();
 563                         cmd.Parameters.Add ("@p1", SqlDbType.VarChar);
 564                         cmd.Parameters["@p1"].Value = "afasasadadada";
 565                         try {
 566                                 cmd.Prepare ();
 567                                 Assert.Fail ("#5 Exception shud be thrown");
 568                         }catch (AssertionException e) {
 569                                 throw e;
 570                         }catch (Exception e) {
 571                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 572                                         "#6 Incorrect Exception " + e.StackTrace);
 573                         }
 574
 575                         // Test Exception is not thrown for Stored Procs
 576                         try {
 577                                 cmd.CommandType = CommandType.StoredProcedure;
 578                                 cmd.CommandText = "ABFSDSFSF" ;
 579                                 cmd.Prepare ();
 580                         }catch {
 581                                 Assert.Fail ("#7 Exception shud not be thrown for Stored Procs");
 582                         }
 583                         cmd.CommandType = CommandType.Text;
 584                         conn.Close ();
 585
 586                         //Test InvalidOperation Exception is thrown if connection is not set
 587                         cmd.Connection = null;
 588                         try {
 589                                 cmd.Prepare ();
 590 #if NET_2_0
 591                                 Assert.Fail ("#8 NullReferenceException should be thrown");
 592 #else
 593                                 Assert.Fail ("#8 InvalidOperation Exception should be thrown");
 594 #endif
 595                         }
 596                         catch (AssertionException e) {
 597                                 throw e;
 598                         }catch (Exception e) {
 599 #if NET_2_0
 600                                 Assert.AreEqual (typeof (NullReferenceException), e.GetType (),
 601                                         "#9 Incorrect Exception : " + e.StackTrace);
 602 #else
 603                                 Assert.AreEqual (typeof (InvalidOperationException), e.GetType (),
 604                                         "#9 Incorrect Exception : " + e.StackTrace);
 605 #endif
 606                         }
 607
 608                         //Test InvalidOperation Exception is thrown if connection is closed
 609                         cmd.Connection = conn ;
 610                         try{
 611                                 cmd.Prepare ();
 612                                 Assert.Fail ("#4 InvalidOperation Exception shud be thrown");
 613                         }catch (AssertionException e) {
 614                                 throw e;
 615                         }catch (Exception e) {
 616 #if NET_2_0
 617                                 Assert.AreEqual (typeof(NullReferenceException), e.GetType(),
 618                                         "Incorrect Exception : " + e.StackTrace);
 619 #else
 620                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 621                                         "Incorrect Exception : " + e.StackTrace);
 622 #endif
 623                         }
 624                 }
 625
 626                 [Test]
 627                 public void ResetTimeOut ()
 628                 {
 629                         SqlCommand cmd = new SqlCommand ();
 630                         cmd.CommandTimeout = 50 ;
 631                         Assert.AreEqual ( cmd.CommandTimeout, 50,
 632                                 "#1 CommandTimeout should be modfiable");
 633                         cmd.ResetCommandTimeout ();
 634                         Assert.AreEqual (cmd.CommandTimeout, 30,
 635                                 "#2 Reset Should set the Timeout to default value");
 636                 }
 637
 638                 [Test]
 639                 [ExpectedException (typeof(ArgumentException))]
 640                 public void CommandTimeout ()
 641                 {
 642                         cmd = new SqlCommand ();
 643                         cmd.CommandTimeout = 10;
 644                         Assert.AreEqual (10, cmd.CommandTimeout, "#1");
 645                         cmd.CommandTimeout = -1;
 646                 }
 647
 648                 [Test]
 649 #if NET_2_0
 650                 [ExpectedException (typeof(ArgumentOutOfRangeException))]
 651 #else
 652                 [ExpectedException (typeof(ArgumentException))]
 653 #endif
 654                 public void CommandTypeTest ()
 655                 {
 656                         cmd = new SqlCommand ();
 657                         Assert.AreEqual (CommandType.Text ,cmd.CommandType,
 658                                 "Default CommandType is text");
 659                         cmd.CommandType = (CommandType)(-1);
 660                 }
 661
 662                 [Test]
 663                 [Ignore ("msdotnet contradicts documented behavior")]
 664                 [ExpectedException (typeof(InvalidOperationException))]
 665                 public void ConnectionTest ()
 666                 {
 667                         SqlTransaction trans = null;
 668                         try {
 669                                 conn = new SqlConnection (connectionString);
 670                                 conn.Open ();
 671                                 trans = conn.BeginTransaction ();
 672                                 cmd = new SqlCommand ("", conn,trans);
 673                                 cmd.CommandText = "Select id from numeric_family where id=1";
 674                                 cmd.Connection = new SqlConnection ();
 675                         }finally {
 676                                 trans.Rollback();
 677                                 conn.Close ();
 678                         }
 679                 }
 680
 681                 [Test]
 682                 public void TransactionTest ()
 683                 {
 684                         conn = new SqlConnection (connectionString);
 685                         cmd = new SqlCommand ("", conn);
 686                         Assert.IsNull (cmd.Transaction, "#1 Default value is null");
 687
 688                         SqlConnection conn1 = new SqlConnection (connectionString);
 689                         conn1.Open ();
 690                         SqlTransaction trans1 = conn1.BeginTransaction ();
 691                         cmd.Transaction = trans1 ;
 692                         try {
 693                                 cmd.ExecuteNonQuery ();
 694                                 Assert.Fail ("#2 Connection cannot be different");
 695                         }catch (Exception e) {
 696 #if NET_2_0
 697                                 Assert.AreEqual (typeof(NullReferenceException), e.GetType(),
 698                                         "#3 Incorrect Exception : " + e);
 699 #else
 700                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 701                                         "#3 Incorrect Exception : " + e);
 702 #endif
 703                         }finally {
 704                                 conn1.Close ();
 705                                 conn.Close ();
 706                         }
 707                 }
 708
 709                 // Need to add more tests
 710                 [Test]
 711 #if NET_2_0
 712                 [ExpectedException (typeof(ArgumentOutOfRangeException))]
 713 #else
 714                 [ExpectedException (typeof(ArgumentException))]
 715 #endif
 716                 public void UpdatedRowSourceTest ()
 717                 {
 718                         cmd = new SqlCommand ();
 719                         Assert.AreEqual (UpdateRowSource.Both, cmd.UpdatedRowSource,
 720                                 "#1 Default value is both");
 721                         cmd.UpdatedRowSource = UpdateRowSource.None;
 722                         Assert.AreEqual (UpdateRowSource.None, cmd.UpdatedRowSource,
 723                                 "#2");
 724
 725                         cmd.UpdatedRowSource = (UpdateRowSource) (-1);
 726                 }
 727
 728                 [Test]
 729                 public void ExecuteNonQueryTempProcedureTest () {
 730                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
 731                         try {
 732                                 ConnectionManager.Singleton.OpenConnection ();
 733                                 // create temp sp here, should normally be created in Setup of test
 734                                 // case, but cannot be done right now because of ug #68978
 735                                 DBHelper.ExecuteNonQuery (conn, CREATE_TMP_SP_TEMP_INSERT_PERSON);
 736                                 SqlCommand cmd = new SqlCommand();
 737                                 cmd.Connection = conn;
 738                                 cmd.CommandText = "#sp_temp_insert_employee";
 739                                 cmd.CommandType = CommandType.StoredProcedure;
 740                                 Object TestPar = "test";
 741                                 cmd.Parameters.Add("@fname", SqlDbType.VarChar);
 742                                 cmd.Parameters ["@fname"].Value = TestPar;
 743                                 Assert.AreEqual(1,cmd.ExecuteNonQuery());
 744                         } finally {
 745                                 DBHelper.ExecuteNonQuery (conn, DROP_TMP_SP_TEMP_INSERT_PERSON);
 746                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_person_table");
 747                                 ConnectionManager.Singleton.CloseConnection ();
 748                         }
 749                 }
 750
 751                 // Test for bug #76778
 752                 // Test for a case, when query size is greater than the block size
 753                 [Test]
 754                 public void LongQueryTest ()
 755                 {
 756                         SqlConnection conn = new SqlConnection (
 757                                                         connectionString + ";Pooling=false");
 758                         using (conn) {
 759                                 conn.Open ();
 760                                 SqlCommand cmd = conn.CreateCommand ();
 761                                 String value =  new String ('a', 10000);
 762                                 cmd.CommandText = String.Format ("Select '{0}'", value);
 763                                 cmd.ExecuteNonQuery ();
 764                         }
 765                 }
 766
 767                 // Test for bug #76778
 768                 // To make sure RPC (when implemented) works ok..
 769                 [Test]
 770                 public void LongStoredProcTest()
 771                 {
 772                         SqlConnection conn = new SqlConnection (
 773                                                         connectionString + ";Pooling=false");
 774                         using (conn) {
 775                                 conn.Open ();
 776                                 /*int size = conn.PacketSize ; */
 777                                 SqlCommand cmd = conn.CreateCommand ();
 778                                 // create a temp stored proc ..
 779                                 cmd.CommandText  = "Create Procedure #sp_tmp_long_params ";
 780                                 cmd.CommandText += "@p1 nvarchar (4000), ";
 781                                 cmd.CommandText += "@p2 nvarchar (4000), ";
 782                                 cmd.CommandText += "@p3 nvarchar (4000), ";
 783                                 cmd.CommandText += "@p4 nvarchar (4000) out ";
 784                                 cmd.CommandText += "As ";
 785                                 cmd.CommandText += "Begin ";
 786                                 cmd.CommandText += "Set @p4 = N'Hello' ";
 787                                 cmd.CommandText += "Return 2 ";
 788                                 cmd.CommandText += "End";
 789                                 cmd.ExecuteNonQuery ();
 790
 791                                 //execute the proc
 792                                 cmd.CommandType = CommandType.StoredProcedure;
 793                                 cmd.CommandText = "#sp_tmp_long_params";
 794
 795                                 String value =  new String ('a', 4000);
 796                                 SqlParameter p1 = new SqlParameter ("@p1",
 797                                                         SqlDbType.NVarChar,4000);
 798                                 p1.Value = value;
 799
 800                                 SqlParameter p2 = new SqlParameter ("@p2",
 801                                                         SqlDbType.NVarChar,4000);
 802                                 p2.Value = value;
 803
 804                                 SqlParameter p3 = new SqlParameter ("@p3",
 805                                                         SqlDbType.NVarChar,4000);
 806                                 p3.Value = value;
 807
 808                                 SqlParameter p4 = new SqlParameter ("@p4",
 809                                                         SqlDbType.NVarChar,4000);
 810                                 p4.Direction = ParameterDirection.Output;
 811
 812                                 // for now, name shud be @RETURN_VALUE
 813                                 // can be changed once RPC is implemented
 814                                 SqlParameter p5 = new SqlParameter ("@RETURN_VALUE", SqlDbType.Int);
 815                                 p5.Direction = ParameterDirection.ReturnValue ;
 816
 817                                 cmd.Parameters.Add (p1);
 818                                 cmd.Parameters.Add (p2);
 819                                 cmd.Parameters.Add (p3);
 820                                 cmd.Parameters.Add (p4);
 821                                 cmd.Parameters.Add (p5);
 822
 823                                 cmd.ExecuteNonQuery ();
 824                                 Assert.AreEqual ("Hello", p4.Value, "#1");
 825                                 Assert.AreEqual (2, p5.Value, "#2");
 826                         }
 827                 }
 828
 829                 // Test for bug #76880
 830                 [Test]
 831                 public void DateTimeParameterTest ()
 832                 {
 833                         SqlConnection conn = new SqlConnection (connectionString);
 834                         using (conn) {
 835                                 conn.Open ();
 836                                 SqlCommand cmd = conn.CreateCommand ();
 837                                 cmd.CommandText = "select * from datetime_family where type_datetime=@p1";
 838                                 cmd.Parameters.Add ("@p1", SqlDbType.DateTime).Value= "10-10-2005";
 839                                 // shudnt cause and exception
 840                                 SqlDataReader rdr = cmd.ExecuteReader ();
 841                                 rdr.Close ();
 842                         }
 843                 }
 844
 845                 /**
 846                  * Verifies whether an enum value is converted to a numeric value when
 847                  * used as value for a numeric parameter (bug #66630)
 848                  */
 849                 [Test]
 850                 public void EnumParameterTest() {
 851                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
 852                         try {
 853                                 ConnectionManager.Singleton.OpenConnection ();
 854                                 // create temp sp here, should normally be created in Setup of test
 855                                 // case, but cannot be done right now because of ug #68978
 856                                 DBHelper.ExecuteNonQuery (conn, "CREATE PROCEDURE #Bug66630 ("
 857                                                           + "@Status smallint = 7"
 858                                                           + ")"
 859                                                           + "AS" + Environment.NewLine
 860                                                           + "BEGIN" + Environment.NewLine
 861                                                           + "SELECT CAST(5 AS int), @Status" + Environment.NewLine
 862                                                           + "END");
 863
 864                                 SqlCommand cmd = new SqlCommand("#Bug66630", conn);
 865                                 cmd.CommandType = CommandType.StoredProcedure;
 866                                 cmd.Parameters.Add("@Status", SqlDbType.Int).Value = Status.Error;
 867
 868                                 using (SqlDataReader dr = cmd.ExecuteReader()) {
 869                                         // one record should be returned
 870                                         Assert.IsTrue(dr.Read(), "EnumParameterTest#1");
 871                                         // we should get two field in the result
 872                                         Assert.AreEqual(2, dr.FieldCount, "EnumParameterTest#2");
 873                                         // field 1
 874                                         Assert.AreEqual("int", dr.GetDataTypeName(0), "EnumParameterTest#3");
 875                                         Assert.AreEqual(5, dr.GetInt32(0), "EnumParameterTest#4");
 876                                         // field 2
 877                                         Assert.AreEqual("smallint", dr.GetDataTypeName(1), "EnumParameterTest#5");
 878                                         Assert.AreEqual((short) Status.Error, dr.GetInt16(1), "EnumParameterTest#6");
 879                                         // only one record should be returned
 880                                         Assert.IsFalse(dr.Read(), "EnumParameterTest#7");
 881                                 }
 882                         } finally {
 883                                 DBHelper.ExecuteNonQuery (conn, "if exists (select name from sysobjects " +
 884                                                           " where name like '#temp_Bug66630' and type like 'P') " +
 885                                                           " drop procedure #temp_Bug66630; ");
 886                                 ConnectionManager.Singleton.CloseConnection ();
 887                         }
 888                 }
 889
 890                 /**
 891                  * The below test does not need a connection but since the setup opens
 892                  * the connection i will need to close it
 893                  */
 894                 [Test]
 895                 public void CloneTest() {
 896                         ConnectionManager.Singleton.OpenConnection ();
 897                         SqlCommand cmd = new SqlCommand();
 898                         cmd.Connection = null;
 899                         cmd.CommandText = "sp_insert";
 900                         cmd.CommandType = CommandType.StoredProcedure;
 901                         Object TestPar = DBNull.Value;
 902                         cmd.Parameters.Add("@TestPar1", SqlDbType.Int);
 903                         cmd.Parameters["@TestPar1"].Value = TestPar;
 904 #if NET_2_0
 905                         cmd.Parameters.AddWithValue ("@BirthDate", DateTime.Now);
 906 #else
 907                         cmd.Parameters.Add ("@BirthDate", DateTime.Now);
 908 #endif
 909                         cmd.DesignTimeVisible = true;
 910                         cmd.CommandTimeout = 100;
 911                         Object clone1 = ((ICloneable)(cmd)).Clone();
 912                         SqlCommand cmd1 = (SqlCommand) clone1;
 913                         Assert.AreEqual(2, cmd1.Parameters.Count);
 914                         Assert.AreEqual(100, cmd1.CommandTimeout);
 915 #if NET_2_0
 916                         cmd1.Parameters.AddWithValue ("@test", DateTime.Now);
 917 #else
 918                         cmd1.Parameters.Add ("@test", DateTime.Now);
 919 #endif
 920                         // to check that it is deep copy and not a shallow copy of the
 921                         // parameter collection
 922                         Assert.AreEqual(3, cmd1.Parameters.Count);
 923                         Assert.AreEqual(2, cmd.Parameters.Count);
 924                 }
 925
 926                 [Test]
 927                 public void StoredProc_NoParameterTest ()
 928                 {
 929                         string query = "create procedure #tmp_sp_proc as begin";
 930                         query += " select 'data' end";
 931                         SqlConnection conn = new SqlConnection (connectionString);
 932                         SqlCommand cmd = conn.CreateCommand ();
 933                         cmd.CommandText = query ;
 934                         conn.Open ();
 935                         cmd.ExecuteNonQuery ();
 936
 937                         cmd.CommandType = CommandType.StoredProcedure;
 938                         cmd.CommandText = "#tmp_sp_proc";
 939                         using (SqlDataReader reader = cmd.ExecuteReader()) {
 940                                 if (reader.Read ())
 941                                         Assert.AreEqual ("data", reader.GetString(0),"#1");
 942                                 else
 943                                         Assert.Fail ("#2 Select shud return data");
 944                         }
 945                         conn.Close ();
 946                 }
 947
 948                 [Test]
 949                 public void StoredProc_ParameterTest ()
 950                 {
 951                         string create_query  = CREATE_TMP_SP_PARAM_TEST;
 952                         string drop_query = DROP_TMP_SP_PARAM_TEST;
 953
 954                         SqlConnection conn = new SqlConnection (connectionString);
 955
 956                         conn.Open ();
 957                         SqlCommand cmd = conn.CreateCommand ();
 958                         int label = 0 ;
 959                         string error = "";
 960                         while (label != -1) {
 961                                 try {
 962                                         switch (label) {
 963                                                 case 0 :
 964                                                         // Test BigInt Param
 965                                                         DBHelper.ExecuteNonQuery (conn,
 966                                                                         String.Format(create_query, "bigint"));
 967                                                         rpc_helper_function (cmd, SqlDbType.BigInt, 0, Int64.MaxValue);
 968                                                         rpc_helper_function (cmd, SqlDbType.BigInt, 0, Int64.MinValue);
 969                                                         break;
 970                                                 case 1 :
 971                                                         // Test Binary Param
 972                                                         DBHelper.ExecuteNonQuery (conn,
 973                                                                         String.Format(create_query, "binary(5)"));
 974                                                         //rpc_helper_function (cmd, SqlDbType.Binary, 0, new byte[] {});
 975                                                         rpc_helper_function (cmd, SqlDbType.Binary, 5, new byte[] {1,2,3,4,5});
 976                                                         break;
 977                                                 case 2 :
 978                                                         // Test Bit Param
 979                                                         DBHelper.ExecuteNonQuery (conn,
 980                                                                         String.Format(create_query, "bit"));
 981                                                         rpc_helper_function (cmd, SqlDbType.Bit, 0, true);
 982                                                         rpc_helper_function (cmd, SqlDbType.Bit, 0, false);
 983                                                         break;
 984                                                 case 3 :
 985                                                         // Testing Char
 986                                                         DBHelper.ExecuteNonQuery (conn,
 987                                                                         String.Format(create_query, "char(10)"));
 988                                                         rpc_helper_function (cmd, SqlDbType.Char, 10, "characters");
 989                                                         /*
 990                                                         rpc_helper_function (cmd, SqlDbType.Char, 10, "");
 991                                                         rpc_helper_function (cmd, SqlDbType.Char, 10, null);
 992                                                         */
 993                                                         break;
 994                                                 case 4 :
 995                                                         // Testing DateTime
 996                                                         DBHelper.ExecuteNonQuery (conn,
 997                                                                         String.Format(create_query, "datetime"));
 998                                                         rpc_helper_function (cmd, SqlDbType.DateTime, 0, "2079-06-06 23:59:00");
 999                                                         /*
1000                                                         rpc_helper_function (cmd, SqlDbType.DateTime, 10, "");
1001                                                         rpc_helper_function (cmd, SqlDbType.DateTime, 10, null);
1002                                                         */
1003                                                         break;
1004                                                 case 5 :
1005                                                         // Test Decimal Param
1006                                                         DBHelper.ExecuteNonQuery (conn,
1007                                                                         String.Format(create_query,"decimal(10,2)"));
1008                                                         /*
1009                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0, 10.01);
1010                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0, -10.01);
1011                                                         */
1012                                                         break;
1013                                                 case 6 :
1014                                                         // Test Float Param
1015                                                         DBHelper.ExecuteNonQuery (conn,
1016                                                                         String.Format(create_query,"float"));
1017                                                         rpc_helper_function (cmd, SqlDbType.Float, 0, 10.0);
1018                                                         rpc_helper_function (cmd, SqlDbType.Float, 0, 0);
1019                                                         /*
1020                                                         rpc_helper_function (cmd, SqlDbType.Float, 0, null);
1021                                                         */
1022                                                         break;
1023                                                 case 7 :
1024                                                         // Testing Image
1025                                                         /* NOT WORKING
1026                                                            DBHelper.ExecuteNonQuery (conn,
1027                                                            String.Format(create_query, "image"));
1028                                                            rpc_helper_function (cmd, SqlDbType.Image, 0, );
1029                                                            rpc_helper_function (cmd, SqlDbType.Image, 0, );
1030                                                            rpc_helper_function (cmd, SqlDbType.Image, 0, );
1031                                                            /* NOT WORKING*/
1032                                                         break;
1033                                                 case 8 :
1034                                                         // Test Integer Param
1035                                                         DBHelper.ExecuteNonQuery (conn,
1036                                                         String.Format(create_query,"int"));
1037                                                         rpc_helper_function (cmd, SqlDbType.Int, 0, 10);
1038                                                         /*
1039                                                         rpc_helper_function (cmd, SqlDbType.Int, 0, null);
1040                                                         */
1041                                                         break;
1042                                                 case 9 :
1043                                                         // Test Money Param
1044                                                         DBHelper.ExecuteNonQuery (conn,
1045                                                                         String.Format(create_query,"money"));
1046                                                         /*
1047                                                         rpc_helper_function (cmd, SqlDbType.Money, 0, 10.0);
1048                                                         rpc_helper_function (cmd, SqlDbType.Money, 0, null);
1049                                                         */
1050                                                         break;
1051                                                 case 23 :
1052                                                         // Test NChar Param
1053                                                         DBHelper.ExecuteNonQuery (conn,
1054                                                                         String.Format(create_query,"nchar(10)"));
1055                                                         /*
1056                                                         rpc_helper_function (cmd, SqlDbType.NChar, 10, "nchar");
1057                                                         rpc_helper_function (cmd, SqlDbType.NChar, 10, "");
1058                                                         rpc_helper_function (cmd, SqlDbType.NChar, 10, null);
1059                                                         */
1060                                                         break;
1061                                                 case 10 :
1062                                                         // Test NText Param
1063                                                         DBHelper.ExecuteNonQuery (conn,
1064                                                                         String.Format(create_query,"ntext"));
1065                                                         /*
1066                                                         rpc_helper_function (cmd, SqlDbType.NText, 0, "ntext");
1067                                                         rpc_helper_function (cmd, SqlDbType.NText, 0, "");
1068                                                         rpc_helper_function (cmd, SqlDbType.NText, 0, null);
1069                                                         */
1070                                                         break;
1071                                                 case 11 :
1072                                                         // Test NVarChar Param
1073                                                         DBHelper.ExecuteNonQuery (conn,
1074                                                                         String.Format(create_query,"nvarchar(10)"));
1075                                                         rpc_helper_function (cmd, SqlDbType.NVarChar, 10, "nvarchar");
1076                                                         rpc_helper_function (cmd, SqlDbType.NVarChar, 10, "");
1077                                                         //rpc_helper_function (cmd, SqlDbType.NVarChar, 10, null);
1078                                                         break;
1079                                                 case 12 :
1080                                                         // Test Real Param
1081                                                         DBHelper.ExecuteNonQuery (conn,
1082                                                         String.Format(create_query,"real"));
1083                                                         rpc_helper_function (cmd, SqlDbType.Real, 0, 10.0);
1084                                                         //rpc_helper_function (cmd, SqlDbType.Real, 0, null);
1085                                                         break;
1086                                                 case 13 :
1087                                                         // Test SmallDateTime Param
1088                                                         DBHelper.ExecuteNonQuery (conn,
1089                                                                         String.Format(create_query,"smalldatetime"));
1090                                                         rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0, "6/6/2079 11:59:00 PM");
1091                                                         /*
1092                                                         rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0, "");
1093                                                         rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0, null);
1094                                                         */
1095                                                         break;
1096                                                 case 14 :
1097                                                         // Test SmallInt Param
1098                                                         DBHelper.ExecuteNonQuery (conn,
1099                                                         String.Format(create_query,"smallint"));
1100                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0, 10);
1101                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0, -10);
1102                                                         //rpc_helper_function (cmd, SqlDbType.SmallInt, 0, null);
1103                                                         break;
1104                                                 case 15 :
1105                                                         // Test SmallMoney Param
1106                                                         DBHelper.ExecuteNonQuery (conn,
1107                                                                         String.Format(create_query,"smallmoney"));
1108                                                         /*
1109                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0, 10.0);
1110                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0, -10.0);
1111                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0, null);
1112                                                         */
1113                                                         break;
1114                                                 case 16 :
1115                                                         // Test Text Param
1116                                                         DBHelper.ExecuteNonQuery (conn,
1117                                                                         String.Format(create_query,"text"));
1118                                                         /*
1119                                                         rpc_helper_function (cmd, SqlDbType.Text, 0, "text");
1120                                                         rpc_helper_function (cmd, SqlDbType.Text, 0, "");
1121                                                         rpc_helper_function (cmd, SqlDbType.Text, 0, null);
1122                                                         */
1123                                                         break;
1124                                                 case 17 :
1125                                                         // Test TimeStamp Param
1126                                                         /* NOT WORKING
1127                                                            DBHelper.ExecuteNonQuery (conn,
1128                                                            String.Format(create_query,"timestamp"));
1129                                                            rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, "");
1130                                                            rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, "");
1131                                                            rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, null);
1132                                                          */
1133                                                         break;
1134                                                 case 18 :
1135                                                         // Test TinyInt Param
1136                                                         DBHelper.ExecuteNonQuery (conn,
1137                                                                         String.Format(create_query,"tinyint"));
1138                                                         /*
1139                                                         rpc_helper_function (cmd, SqlDbType.TinyInt, 0, 10);
1140                                                         rpc_helper_function (cmd, SqlDbType.TinyInt, 0, -10);
1141                                                         rpc_helper_function (cmd, SqlDbType.TinyInt, 0, null);
1142                                                         */
1143                                                         break;
1144                                                 case 19 :
1145                                                         // Test UniqueIdentifier Param
1146                                                         /*
1147                                                         DBHelper.ExecuteNonQuery (conn,
1148                                                                         String.Format(create_query,"uniqueidentifier"));
1149                                                         rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, "0f159bf395b1d04f8c2ef5c02c3add96");
1150                                                         rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, null);
1151                                                         */
1152                                                         break;
1153                                                 case 20 :
1154                                                         // Test VarBinary Param
1155                                                         /* NOT WORKING
1156                                                            DBHelper.ExecuteNonQuery (conn,
1157                                                            String.Format(create_query,"varbinary (10)"));
1158                                                            rpc_helper_function (cmd, SqlDbType.VarBinary, 0,);
1159                                                            rpc_helper_function (cmd, SqlDbType.VarBinary, 0,);
1160                                                            rpc_helper_function (cmd, SqlDbType.VarBinary, 0, null);
1161                                                          */
1162                                                         break;
1163                                                 case 21 :
1164                                                         // Test Varchar Param
1165                                                         DBHelper.ExecuteNonQuery (conn,
1166                                                                         String.Format(create_query,"varchar(10)"));
1167                                                         rpc_helper_function (cmd, SqlDbType.VarChar, 10, "VarChar");
1168                                                         break;
1169                                                 case 22 :
1170                                                         // Test Variant Param
1171                                                         /* NOT WORKING
1172                                                            DBHelper.ExecuteNonQuery (conn,
1173                                                            String.Format(create_query,"variant"));
1174                                                            rpc_helper_function (cmd, SqlDbType.Variant, 0, );
1175                                                            rpc_helper_function (cmd, SqlDbType.Variant, 0, );
1176                                                            rpc_helper_function (cmd, SqlDbType.Variant, 0, null);
1177                                                          */
1178                                                         break;
1179                                                 default :
1180                                                         label = -2;
1181                                                         break;
1182                                         }
1183                                 }catch (AssertionException e) {
1184                                         error += String.Format (" Case {0} INCORRECT VALUE : {1}\n",label, e.Message);
1185                                 }catch (Exception e) {
1186                                         error += String.Format (" Case {0} NOT WORKING : {1}\n",label, e.Message);
1187                                 }
1188
1189                                 label++;
1190                                 if (label != -1)
1191                                         DBHelper.ExecuteNonQuery (conn, drop_query);
1192                         }
1193                         if (error != String.Empty)
1194                                 Assert.Fail (error);
1195                 }
1196
1197                 private void rpc_helper_function (SqlCommand cmd, SqlDbType type, int size, object val)
1198                 {
1199                                 cmd.Parameters.Clear ();
1200                                 SqlParameter param1 ;
1201                                 SqlParameter param2 ;
1202                                 if (size != 0) {
1203                                         param1 = new SqlParameter ("@param1", type, size);
1204                                         param2 = new SqlParameter ("@param2", type, size);
1205                                 }
1206                                 else {
1207                                         param1 = new SqlParameter ("@param1", type);
1208                                         param2 = new SqlParameter ("@param2", type);
1209                                 }
1210
1211                                 SqlParameter retval = new SqlParameter ("retval", SqlDbType.Int);
1212                                 param1.Value = val;
1213                                 param1.Direction = ParameterDirection.Input;
1214                                 param2.Direction = ParameterDirection.Output;
1215                                 retval.Direction = ParameterDirection.ReturnValue;
1216                                 cmd.Parameters.Add (param1);
1217                                 cmd.Parameters.Add (param2);
1218                                 cmd.Parameters.Add (retval);
1219                                 cmd.CommandText = "#tmp_sp_param_test";
1220                                 cmd.CommandType = CommandType.StoredProcedure;
1221                                 using (SqlDataReader reader = cmd.ExecuteReader ()) {
1222                                         while (reader.Read ()) {
1223                                                 if (param1.Value != null && param1.Value.GetType () == typeof (string))
1224                                                         Assert.AreEqual (param1.Value,
1225                                                                          reader.GetValue(0).ToString (),"#1");
1226                                                 else
1227                                                         Assert.AreEqual (param1.Value,
1228                                                                          reader.GetValue(0),"#1");
1229                                         }
1230                                 }
1231                                 if (param1.Value != null && param1.Value.GetType () == typeof (string) && param2.Value != null)
1232                                         Assert.AreEqual (param1.Value.ToString (), param2.Value.ToString (), "#2");
1233                                 else
1234                                         Assert.AreEqual (param1.Value, param2.Value, "#2");
1235                                 Assert.AreEqual (5, retval.Value, "#3");
1236                 }
1237
1238                 [Test]
1239                 [ExpectedException (typeof (InvalidOperationException))]
1240                 public void OutputParamSizeTest1 ()
1241                 {
1242                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
1243                         ConnectionManager.Singleton.OpenConnection ();
1244                         cmd = new SqlCommand ();
1245                         cmd.Connection = conn;
1246
1247                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
1248                         cmd.CommandType = CommandType.Text;
1249                         cmd.ExecuteNonQuery ();
1250
1251                         cmd.CommandText = "#testsize";
1252                         cmd.CommandType = CommandType.StoredProcedure;
1253
1254                         SqlParameter p1 = new SqlParameter ();
1255                         p1.ParameterName = "@p1";
1256                         p1.Direction = ParameterDirection.InputOutput;
1257                         p1.DbType = DbType.String;
1258                         p1.IsNullable = false;
1259                         cmd.Parameters.Add (p1);
1260                         cmd.ExecuteNonQuery ();
1261                 }
1262
1263                 [Test]
1264                 [ExpectedException (typeof (InvalidOperationException))]
1265                 public void OutputParamSizeTest2 ()
1266                 {
1267                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
1268                         ConnectionManager.Singleton.OpenConnection ();
1269                         cmd = new SqlCommand ();
1270                         cmd.Connection = conn;
1271
1272                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
1273                         cmd.CommandType = CommandType.Text;
1274                         cmd.ExecuteNonQuery ();
1275
1276                         cmd.CommandText = "#testsize";
1277                         cmd.CommandType = CommandType.StoredProcedure;
1278
1279                         SqlParameter p1 = new SqlParameter ();
1280                         p1.ParameterName = "@p1";
1281                         p1.Direction = ParameterDirection.Output;
1282                         p1.DbType = DbType.String;
1283                         p1.IsNullable = false;
1284                         cmd.Parameters.Add (p1);
1285                         cmd.ExecuteNonQuery ();
1286                 }
1287
1288                 [Test]
1289                 [ExpectedException (typeof (InvalidOperationException))]
1290                 public void OutputParamSizeTest3 ()
1291                 {
1292                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
1293                         ConnectionManager.Singleton.OpenConnection ();
1294                         cmd = new SqlCommand ();
1295                         cmd.Connection = conn;
1296
1297                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
1298                         cmd.CommandType = CommandType.Text;
1299                         cmd.ExecuteNonQuery ();
1300
1301                         cmd.CommandText = "#testsize";
1302                         cmd.CommandType = CommandType.StoredProcedure;
1303
1304                         SqlParameter p1 = new SqlParameter ();
1305                         p1.ParameterName = "@p1";
1306                         p1.Direction = ParameterDirection.InputOutput;
1307                         p1.DbType = DbType.String;
1308                         p1.IsNullable = true;
1309                         cmd.Parameters.Add (p1);
1310                         cmd.ExecuteNonQuery ();
1311                 }
1312
1313                 [Test]
1314                 [ExpectedException (typeof (InvalidOperationException))]
1315                 public void OutputParamSizeTest4 ()
1316                 {
1317                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
1318                         ConnectionManager.Singleton.OpenConnection ();
1319                         cmd = new SqlCommand ();
1320                         cmd.Connection = conn;
1321
1322                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
1323                         cmd.CommandType = CommandType.Text;
1324                         cmd.ExecuteNonQuery ();
1325
1326                         cmd.CommandText = "#testsize";
1327                         cmd.CommandType = CommandType.StoredProcedure;
1328
1329                         SqlParameter p1 = new SqlParameter ();
1330                         p1.ParameterName = "@p1";
1331                         p1.Direction = ParameterDirection.Output;
1332                         p1.DbType = DbType.String;
1333                         p1.IsNullable = true;
1334                         cmd.Parameters.Add (p1);
1335                         cmd.ExecuteNonQuery ();
1336                 }
1337
1338 #if NET_2_0
1339                 [Test]
1340                 public void NotificationTest ()
1341                 {
1342                         cmd = new SqlCommand ();
1343                         SqlNotificationRequest notification = new SqlNotificationRequest("MyNotification","MyService",15);
1344                         Assert.AreEqual (null, cmd.Notification, "#1 The default value for this property should be null");
1345                         cmd.Notification = notification;
1346                         Assert.AreEqual ("MyService", cmd.Notification.Options, "#2 The value should be MyService as the constructor is initiated with this value");
1347                         Assert.AreEqual (15, cmd.Notification.Timeout, "#2 The value should be 15 as the constructor is initiated with this value");
1348                 }
1349
1350                 [Test]
1351                 public void NotificationAutoEnlistTest ()
1352                 {
1353                         cmd = new SqlCommand ();
1354                         Assert.AreEqual (true, cmd.NotificationAutoEnlist, "#1 Default value of the property should be true");
1355                         cmd.NotificationAutoEnlist = false;
1356                         Assert.AreEqual (false, cmd.NotificationAutoEnlist, "#2 The value of the property should be false after setting it to false");
1357                 }
1358
1359                 [Test]
1360                 public void BeginExecuteXmlReaderTest ()
1361                 {
1362                         cmd = new SqlCommand ();
1363                         string connectionString1 = null;
1364                         connectionString1 = ConnectionManager.Singleton.ConnectionString + "Asynchronous Processing=true";
1365                         try {
1366                                 SqlConnection conn1 = new SqlConnection (connectionString1);
1367                                 conn1.Open ();
1368                                 cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA" ;
1369                                 cmd.Connection = conn1;
1370
1371                                 IAsyncResult result = cmd.BeginExecuteXmlReader ();
1372                                 XmlReader reader = cmd.EndExecuteXmlReader (result);
1373                                 while (reader.Read ())
1374                                 {
1375                                         if (reader.LocalName.ToString () == "employee")
1376                                         {
1377                                                 Assert.AreEqual ("kumar", reader["lname"], "#1 ");
1378                                         }
1379                                 }
1380                         } finally {
1381                                 ConnectionManager.Singleton.CloseConnection ();
1382                         }
1383                 }
1384
1385                 [Test]
1386                 public void BeginExecuteXmlReaderExceptionTest ()
1387                 {
1388                         cmd = new SqlCommand ();
1389                         try {
1390                                 SqlConnection conn = new SqlConnection (connectionString);
1391                                 conn.Open ();
1392                                 cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA" ;
1393                                 cmd.Connection = conn;
1394
1395                                 try {
1396                                         /*IAsyncResult result = */cmd.BeginExecuteXmlReader ();
1397                                 } catch (InvalidOperationException) {
1398                                         Assert.AreEqual (ConnectionManager.Singleton.ConnectionString, connectionString, "#1 Connection string has changed");
1399                                         return;
1400                                 }
1401                                 Assert.Fail ("Expected Exception InvalidOperationException not thrown");
1402                         } finally {
1403                                 ConnectionManager.Singleton.CloseConnection ();
1404                         }
1405                 }
1406
1407                 [Test]
1408                 public void SqlCommandDisposeTest ()
1409                 {
1410                         IDataReader reader = null;
1411                         try {
1412                                 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
1413                                 ConnectionManager.Singleton.OpenConnection ();
1414
1415                                 IDbCommand command = conn.CreateCommand ();
1416                                 try {
1417                                         string sql = "SELECT * FROM employee";
1418                                         command.CommandText = sql;
1419                                         reader = command.ExecuteReader();
1420                                 } finally {
1421                                         command.Dispose();
1422                                 }
1423                                 while (reader.Read());
1424                         } finally {
1425                                 reader.Dispose();
1426                                 ConnectionManager.Singleton.CloseConnection ();
1427                         }
1428                 }
1429
1430                 [Test]
1431                 public void CloneObjTest ()
1432                 {
1433                         SqlCommand cmd = new SqlCommand();
1434                         cmd.CommandText = "sp_insert";
1435                         cmd.CommandType = CommandType.StoredProcedure;
1436                         Object TestPar = DBNull.Value;
1437                         cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1438                         cmd.Parameters ["@TestPar1"].Value = TestPar;
1439 #if NET_2_0
1440                         cmd.Parameters.AddWithValue ("@BirthDate", DateTime.Now);
1441 #else
1442                         cmd.Parameters.Add ("@BirthDate", DateTime.Now);
1443 #endif
1444                         cmd.DesignTimeVisible = true;
1445                         cmd.CommandTimeout = 100;
1446                         SqlCommand cmd1 = cmd.Clone ();
1447                         Assert.AreEqual (2, cmd1.Parameters.Count);
1448                         Assert.AreEqual (100, cmd1.CommandTimeout);
1449 #if NET_2_0
1450                         cmd1.Parameters.AddWithValue ("@test", DateTime.Now);
1451 #else
1452                         cmd1.Parameters.Add ("@test", DateTime.Now);
1453 #endif
1454                         Assert.AreEqual (3, cmd1.Parameters.Count);
1455                         Assert.AreEqual (2, cmd.Parameters.Count);
1456                 }
1457 #endif
1458
1459                 private enum Status {
1460                         OK = 0,
1461                         Error = 3
1462                 }
1463
1464                 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";
1465                 private readonly string DROP_TMP_SP_PARAM_TEST = "drop procedure #tmp_sp_param_test";
1466
1467                 private readonly string CREATE_TMP_SP_TEMP_INSERT_PERSON = ("create procedure #sp_temp_insert_employee ( " + Environment.NewLine +
1468                                                                             "@fname varchar (20)) " + Environment.NewLine +
1469                                                                             "as " + Environment.NewLine +
1470                                                                             "begin" + Environment.NewLine +
1471                                                                             "declare @id int;" + Environment.NewLine +
1472                                                                             "select @id = max (id) from employee;" + Environment.NewLine +
1473                                                                             "set @id = @id + 6000 + 1;" + Environment.NewLine +
1474                                                                             "insert into employee (id, fname, dob, doj) values (@id, @fname, '1980-02-11', getdate ());" + Environment.NewLine +
1475                                                                             "return @id;" + Environment.NewLine +
1476                                                                             "end");
1477
1478                 private readonly string DROP_TMP_SP_TEMP_INSERT_PERSON = ("if exists (select name from sysobjects where " + Environment.NewLine +
1479                                                                           "name = '#sp_temp_insert_employee' and type = 'P') " + Environment.NewLine +
1480                                                                           "drop procedure #sp_temp_insert_employee; ");
1481         }
1482 }
1483