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
  37 using NUnit.Framework;
  38
  39 namespace MonoTests.System.Data.SqlClient
  40 {
  41         [TestFixture]
  42         [Category ("sqlserver")]
  43         public class SqlCommandTest
  44         {
  45
  46                 public SqlConnection conn = null ;
  47                 SqlCommand cmd = null;
  48                 string connectionString = ConnectionManager.Singleton.ConnectionString;
  49
  50                 [SetUp]
  51                 public void Setup ()
  52                 {
  53                 }
  54
  55                 [TearDown]
  56                 public void TearDown ()
  57                 {
  58                         if (conn != null)
  59                                 conn.Close ();
  60                 }
  61
  62                 [Test]
  63                 public void ConstructorTest ()
  64                 {
  65                         // Test Default Constructor
  66                         cmd = new SqlCommand ();
  67                         Assert.AreEqual (String.Empty, cmd.CommandText,
  68                                  "#1 Command Test should be empty");
  69                         Assert.AreEqual (30, cmd.CommandTimeout,
  70                                 "#2 CommandTimeout should be 30");
  71                         Assert.AreEqual (CommandType.Text, cmd.CommandType,
  72                                 "#3 CommandType should be text");
  73                         Assert.IsNull (cmd.Connection, "#4 Connection Should be null");
  74                         Assert.AreEqual (0, cmd.Parameters.Count,
  75                                 "#5 Parameter shud be empty");
  76
  77                         // Test Overloaded Constructor
  78                         String cmdText = "select * from tbl1" ;
  79                         cmd = new SqlCommand (cmdText);
  80                         Assert.AreEqual (cmdText, cmd.CommandText,
  81                                 "#5 CommandText should be the same as passed");
  82                         Assert.AreEqual (30, cmd.CommandTimeout,
  83                                 "#6 CommandTimeout should be 30");
  84                         Assert.AreEqual (CommandType.Text, cmd.CommandType,
  85                                 "#7 CommandType should be text");
  86                         Assert.IsNull (cmd.Connection , "#8 Connection Should be null");
  87
  88                         // Test Overloaded Constructor
  89                         SqlConnection conn = new SqlConnection ();
  90                         cmd = new SqlCommand (cmdText , conn);
  91                         Assert.AreEqual (cmdText, cmd.CommandText,
  92                                 "#9 CommandText should be the same as passed");
  93                         Assert.AreEqual (30, cmd.CommandTimeout,
  94                                 "#10 CommandTimeout should be 30");
  95                         Assert.AreEqual (CommandType.Text, cmd.CommandType,
  96                                 "#11 CommandType should be text");
  97                         Assert.AreSame (cmd.Connection, conn, "#12 Connection Should be same");
  98
  99                         // Test Overloaded Constructor
 100                         SqlTransaction trans = null ;
 101                         try {
 102                                 conn = new SqlConnection (connectionString);
 103                                 conn.Open ();
 104                                 trans = conn.BeginTransaction ();
 105                                 cmd = new SqlCommand (cmdText, conn, trans);
 106                                 Assert.AreEqual (cmdText, cmd.CommandText,
 107                                         "#9 CommandText should be the same as passed");
 108                                 Assert.AreEqual (30, cmd.CommandTimeout,
 109                                         "#10 CommandTimeout should be 30");
 110                                 Assert.AreEqual (CommandType.Text, cmd.CommandType,
 111                                         "#11 CommandType should be text");
 112                                 Assert.AreEqual (cmd.Connection, conn,
 113                                         "#12 Connection Should be null");
 114                                 Assert.AreEqual (cmd.Transaction, trans,
 115                                          "#13 Transaction Property should be set");
 116
 117                                 // Test if parameters are reset to Default Values
 118                                 cmd = new SqlCommand ();
 119                                 Assert.AreEqual (String.Empty, cmd.CommandText,
 120                                         "#1 Command Test should be empty");
 121                                 Assert.AreEqual (30, cmd.CommandTimeout,
 122                                         "#2 CommandTimeout should be 30");
 123                                 Assert.AreEqual (CommandType.Text, cmd.CommandType,
 124                                         "#3 CommandType should be text");
 125                                 Assert.IsNull (cmd.Connection, "#4 Connection Should be null");
 126                         }finally {
 127                                 trans.Rollback ();
 128                         }
 129                 }
 130
 131                 [Test]
 132                 public void ExecuteScalarTest ()
 133                 {
 134                         conn = new SqlConnection (connectionString);
 135                         cmd = new SqlCommand ("" , conn);
 136                         cmd.CommandText = "Select count(*) from numeric_family where id<=4";
 137
 138                         //Check Exception is thrown when executed on a closed connection
 139                         try {
 140                                 cmd.ExecuteScalar ();
 141                                 Assert.Fail ("#1 InvalidOperation Exception must be thrown");
 142                         }catch (AssertionException e) {
 143                                 throw e;
 144                         }catch (Exception e) {
 145 #if NET_2_0
 146                                 Assert.AreEqual (typeof (NullReferenceException), e.GetType (),
 147                                         "#2 Incorrect Exception : " + e.StackTrace);
 148 #else
 149                                 Assert.AreEqual (typeof (InvalidOperationException), e.GetType (),
 150                                         "#2 Incorrect Exception : " + e.StackTrace);
 151 #endif
 152                         }
 153
 154                         // Check the Return value for a Correct Query
 155                         object result = 0;
 156                         conn.Open ();
 157                         result = cmd.ExecuteScalar ();
 158                         Assert.AreEqual (4, (int)result, "#3 Query Result returned is incorrect");
 159
 160                         cmd.CommandText = "select id , type_bit from numeric_family order by id asc" ;
 161                         result = Convert.ToInt32 (cmd.ExecuteScalar ());
 162                         Assert.AreEqual (1, result,
 163                                 "#4 ExecuteScalar Should return (1,1) the result set" );
 164
 165                         cmd.CommandText = "select id from numeric_family where id=-1";
 166                         result = cmd.ExecuteScalar ();
 167                         Assert.IsNull (result, "#5 Null should be returned if result set is empty");
 168
 169                         // Check SqlException is thrown for Invalid Query
 170                         cmd.CommandText = "select count* from numeric_family";
 171                         try {
 172                                 result = cmd.ExecuteScalar ();
 173                                 Assert.Fail ("#6 InCorrect Query should cause an SqlException");
 174                         }catch (AssertionException e) {
 175                                 throw e;
 176                         }catch (Exception e) {
 177                                 Assert.AreEqual (typeof(SqlException), e.GetType(),
 178                                         "#7 Incorrect Exception : " + e.StackTrace);
 179                         }
 180
 181
 182                         // Parameterized stored procedure calls
 183
 184                         int int_value = 20;
 185                         string string_value = "output value changed";
 186                         string return_value = "first column of first rowset";
 187
 188                         cmd.CommandText =
 189                                 "create procedure #tmp_executescalar_outparams "+
 190                                 " (@p1 int, @p2 int out, @p3 varchar(200) out) "+
 191                                 "as " +
 192                                 "select '" + return_value + "' as 'col1', @p1 as 'col2' "+
 193                                 "set @p2 = @p2 * 2 "+
 194                                 "set @p3 = N'" + string_value + "' "+
 195                                 "select 'second rowset' as 'col1', 2 as 'col2' "+
 196                                 "return 1";
 197
 198                         cmd.CommandType = CommandType.Text;
 199                         cmd.ExecuteNonQuery ();
 200
 201                         cmd.CommandText = "#tmp_executescalar_outparams";
 202                         cmd.CommandType = CommandType.StoredProcedure;
 203
 204                         SqlParameter p1 = new SqlParameter ();
 205                         p1.ParameterName = "@p1";
 206                         p1.Direction = ParameterDirection.Input;
 207                         p1.DbType = DbType.Int32;
 208                         p1.Value = int_value;
 209                         cmd.Parameters.Add (p1);
 210
 211                         SqlParameter p2 = new SqlParameter ();
 212                         p2.ParameterName = "@p2";
 213                         p2.Direction = ParameterDirection.InputOutput;
 214                         p2.DbType = DbType.Int32;
 215                         p2.Value = int_value;
 216                         cmd.Parameters.Add (p2);
 217
 218                         SqlParameter p3 = new SqlParameter ();
 219                         p3.ParameterName = "@p3";
 220                         p3.Direction = ParameterDirection.Output;
 221                         p3.DbType = DbType.String;
 222                         p3.Size = 200;
 223                         cmd.Parameters.Add (p3);
 224
 225                         result = cmd.ExecuteScalar ();
 226                         Assert.AreEqual (return_value, result, "#8 ExecuteScalar Should return 'first column of first rowset'");
 227                         Assert.AreEqual (int_value * 2, p2.Value, "#9 ExecuteScalar should fill the parameter collection with the outputted values");
 228                         Assert.AreEqual (string_value, p3.Value, "#10 ExecuteScalar should fill the parameter collection with the outputted values");
 229
 230                         p3.Size = 0;
 231                         p3.Value = null;
 232                         try {
 233                                 cmd.ExecuteScalar ();
 234                                 Assert.Fail ("#11 Query should throw System.InvalidOperationException due to size = 0 and value = null");
 235                         }
 236                         catch (AssertionException e) {
 237                                 throw e;
 238                         }
 239                         catch (Exception e) {
 240                                 Assert.AreEqual (typeof (InvalidOperationException), e.GetType (),
 241                                         "#12 Incorrect Exception : " + e.StackTrace);
 242                         }
 243
 244                         conn.Close ();
 245
 246                 }
 247
 248                 [Test]
 249                 public void ExecuteNonQuery ()
 250                 {
 251                         conn = new SqlConnection (connectionString);
 252                         cmd = new SqlCommand ("", conn);
 253                         int result = 0;
 254
 255                         // Test for exceptions
 256                         // Test exception is thrown if connection is closed
 257                         cmd.CommandText = "Select id from numeric_family where id=1";
 258                         try {
 259                                 cmd.ExecuteNonQuery ();
 260                                 Assert.Fail ("#1 Connextion shud be open");
 261                         }catch (AssertionException e) {
 262                                 throw e;
 263                         }catch (Exception e) {
 264 #if NET_2_0
 265                                 Assert.AreEqual (typeof(NullReferenceException), e.GetType(),
 266                                         "#2 Incorrect Exception : " + e);
 267 #else
 268                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 269                                         "#2 Incorrect Exception : " + e);
 270 #endif
 271                         }
 272
 273                         // Test Exception is thrown if Query is incorrect
 274                         conn.Open ();
 275                         cmd.CommandText = "Select id1 from numeric_family";
 276                         try {
 277                                 cmd.ExecuteNonQuery ();
 278                                 Assert.Fail ("#1 invalid Query");
 279                         }catch (AssertionException e) {
 280                                 throw e;
 281                         }catch (Exception e) {
 282                                 Assert.AreEqual (typeof(SqlException), e.GetType(),
 283                                         "#2 Incorrect Exception : " + e);
 284                         }
 285
 286                         // Test Select/Insert/Update/Delete Statements
 287                         SqlTransaction trans = conn.BeginTransaction ();
 288                         cmd.Transaction = trans;
 289
 290                         try {
 291                                 cmd.CommandText = "Select id from numeric_family where id=1";
 292                                 result = cmd.ExecuteNonQuery ();
 293                                 Assert.AreEqual (-1, result, "#1");
 294
 295                                 cmd.CommandText = "Insert into numeric_family (id,type_int) values (100,200)";
 296                                 result = cmd.ExecuteNonQuery ();
 297                                 Assert.AreEqual (1, result, "#2 One row shud be inserted");
 298
 299                                 cmd.CommandText = "Update numeric_family set type_int=300 where id=100";
 300                                 result = cmd.ExecuteNonQuery ();
 301                                 Assert.AreEqual (1, result, "#3 One row shud be updated");
 302
 303                                 // Test Batch Commands
 304                                 cmd.CommandText = "Select id from numeric_family where id=1;";
 305                                 cmd.CommandText += "update numeric_family set type_int=10 where id=1000";
 306                                 cmd.CommandText += "update numeric_family set type_int=10 where id=100";
 307                                 result = cmd.ExecuteNonQuery ();
 308                                 Assert.AreEqual (1, result, "#4 One row shud be updated");
 309
 310                                 cmd.CommandText = "Delete from numeric_family where id=100";
 311                                 result = cmd.ExecuteNonQuery ();
 312                                 Assert.AreEqual (1, result, "#5 One row shud be deleted");
 313
 314                         }finally {
 315                                 trans.Rollback ();
 316                         }
 317
 318
 319                         // Parameterized stored procedure calls
 320
 321                         int int_value = 20;
 322                         string string_value = "output value changed";
 323
 324                         cmd.CommandText =
 325                                 "create procedure #tmp_executescalar_outparams " +
 326                                 " (@p1 int, @p2 int out, @p3 varchar(200) out) " +
 327                                 "as " +
 328                                 "select 'test' as 'col1', @p1 as 'col2' " +
 329                                 "set @p2 = @p2 * 2 " +
 330                                 "set @p3 = N'" + string_value + "' " +
 331                                 "select 'second rowset' as 'col1', 2 as 'col2' " +
 332                                 "return 1";
 333
 334                         cmd.CommandType = CommandType.Text;
 335                         cmd.ExecuteNonQuery ();
 336
 337                         cmd.CommandText = "#tmp_executescalar_outparams";
 338                         cmd.CommandType = CommandType.StoredProcedure;
 339
 340                         SqlParameter p1 = new SqlParameter ();
 341                         p1.ParameterName = "@p1";
 342                         p1.Direction = ParameterDirection.Input;
 343                         p1.DbType = DbType.Int32;
 344                         p1.Value = int_value;
 345                         cmd.Parameters.Add (p1);
 346
 347                         SqlParameter p2 = new SqlParameter ();
 348                         p2.ParameterName = "@p2";
 349                         p2.Direction = ParameterDirection.InputOutput;
 350                         p2.DbType = DbType.Int32;
 351                         p2.Value = int_value;
 352                         cmd.Parameters.Add (p2);
 353
 354                         SqlParameter p3 = new SqlParameter ();
 355                         p3.ParameterName = "@p3";
 356                         p3.Direction = ParameterDirection.Output;
 357                         p3.DbType = DbType.String;
 358                         p3.Size = 200;
 359                         cmd.Parameters.Add (p3);
 360
 361                         cmd.ExecuteNonQuery ();
 362                         Assert.AreEqual (int_value * 2, p2.Value, "#6 ExecuteNonQuery should fill the parameter collection with the outputted values");
 363                         Assert.AreEqual (string_value, p3.Value, "#7 ExecuteNonQuery should fill the parameter collection with the outputted values");
 364                 }
 365
 366                 [Test]
 367                 public void ExecuteReaderTest ()
 368                 {
 369                         SqlDataReader reader = null;
 370                         conn = new SqlConnection (connectionString);
 371
 372                         // Test exception is thrown if conn is closed
 373                         cmd = new SqlCommand ("Select count(*) from numeric_family");
 374                         try {
 375                                 reader = cmd.ExecuteReader ();
 376                         }catch (AssertionException e) {
 377                                 throw e;
 378                         }catch (Exception e) {
 379 #if NET_2_0
 380                                 Assert.AreEqual (typeof(NullReferenceException), e.GetType(),
 381                                         "#1 Incorrect Exception");
 382 #else
 383                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 384                                         "#1 Incorrect Exception");
 385 #endif
 386                         }
 387
 388                         conn.Open ();
 389                         // Test exception is thrown for Invalid Query
 390                         cmd = new SqlCommand ("InvalidQuery", conn);
 391                         try {
 392                                 reader = cmd.ExecuteReader ();
 393                                 Assert.Fail ("#1 Exception shud be thrown");
 394                         }catch (AssertionException e) {
 395                                 throw e;
 396                         }catch (Exception e) {
 397                                 Assert.AreEqual (typeof(SqlException), e.GetType (),
 398                                         "#2 Incorrect Exception : " + e);
 399                         }
 400
 401                         // NOTE
 402                         // Test SqlException is thrown if a row is locked
 403                         // should lock a particular row and then modify it
 404                         /*
 405                         */
 406
 407                         // Test Connection  cannot be modified when reader is in use
 408                         // NOTE : msdotnet contradicts documented behavior
 409                         cmd.CommandText = "select * from numeric_family where id=1";
 410                         reader = cmd.ExecuteReader ();
 411                         reader.Read ();
 412                         conn.Close (); // valid operation
 413                         conn = new SqlConnection (connectionString);
 414
 415                         /*
 416                         // NOTE msdotnet contradcits documented behavior
 417                         // If the above testcase fails, then this shud be tested
 418                         // Test connection can be modified once reader is closed
 419                         conn.Close ();
 420                         reader.Close ();
 421                         conn = new SqlConnection (connectionString); // valid operation
 422                         */
 423                 }
 424
 425                 [Test]
 426                 public void ExecuteReaderCommandBehaviorTest ()
 427                 {
 428                         // Test for command behaviors
 429                         DataTable schemaTable = null;
 430                         SqlDataReader reader = null;
 431
 432                         conn = new SqlConnection (connectionString);
 433                         conn.Open ();
 434                         cmd = new SqlCommand ("", conn);
 435                         cmd.CommandText = "Select id from numeric_family where id <=4 order by id asc;";
 436                         cmd.CommandText += "Select type_bit from numeric_family where id <=4 order by id asc";
 437
 438                         // Test for default command behavior
 439                         reader = cmd.ExecuteReader ();
 440                         int rows = 0;
 441                         int results = 0;
 442                         do {
 443                                 while (reader.Read ())
 444                                         rows++ ;
 445                                 Assert.AreEqual (4, rows, "#1 Multiple rows shud be returned");
 446                                 results++;
 447                                 rows = 0;
 448                         }while (reader.NextResult());
 449                         Assert.AreEqual (2, results, "#2 Multiple result sets shud be returned");
 450                         reader.Close ();
 451
 452                         // Test if closing reader, closes the connection
 453                         reader = cmd.ExecuteReader (CommandBehavior.CloseConnection);
 454                         reader.Close ();
 455                         Assert.AreEqual (ConnectionState.Closed, conn.State,
 456                                 "#3 Command Behavior is not followed");
 457                         conn.Open();
 458
 459                         // Test if row info and primary Key info is returned
 460                         reader = cmd.ExecuteReader (CommandBehavior.KeyInfo);
 461                         schemaTable = reader.GetSchemaTable ();
 462                         Assert.IsTrue(reader.HasRows, "#4 Data Rows shud also be returned");
 463                         Assert.IsTrue ((bool)schemaTable.Rows[0]["IsKey"],
 464                                 "#5 Primary Key info shud be returned");
 465                         reader.Close ();
 466
 467                         // Test only column information is returned
 468                         reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly);
 469                         schemaTable = reader.GetSchemaTable ();
 470                         Assert.IsFalse (reader.HasRows, "#6 row data shud not be returned");
 471                         Assert.AreEqual(DBNull.Value, schemaTable.Rows[0]["IsKey"],
 472                                 "#7 Primary Key info shud not be returned");
 473                         Assert.AreEqual ("id", schemaTable.Rows[0]["ColumnName"],
 474                                 "#8 Schema Data is Incorrect");
 475                         reader.Close ();
 476
 477                         // Test only one result set (first) is returned
 478                         reader = cmd.ExecuteReader (CommandBehavior.SingleResult);
 479                         schemaTable = reader.GetSchemaTable ();
 480                         Assert.IsFalse (reader.NextResult(),
 481                                 "#9 Only one result set shud be returned");
 482                         Assert.AreEqual ("id", schemaTable.Rows[0]["ColumnName"],
 483                                 "#10 The result set returned shud be the first result set");
 484                         reader.Close ();
 485
 486                         // Test only one row is returned for all result sets
 487                         // msdotnet doesnt work correctly.. returns only one result set
 488                         reader = cmd.ExecuteReader (CommandBehavior.SingleRow);
 489                         rows=0;
 490                         results=0;
 491                         do {
 492                                 while (reader.Read ())
 493                                         rows++ ;
 494                                 Assert.AreEqual (1, rows, "#11 Only one row shud be returned");
 495                                 results++;
 496                                 rows = 0;
 497                         }while (reader.NextResult());
 498                         // NOTE msdotnet contradicts documented behavior.
 499                         // Multiple result sets shud be returned , and in this case : 2
 500                         //Assert.AreEqual (2, results, "# Multiple result sets shud be returned");
 501                         Assert.AreEqual (2, results, "#12 Multiple result sets shud be returned");
 502                         reader.Close ();
 503                 }
 504
 505                 [Test]
 506                 public void PrepareTest_CheckValidStatement ()
 507                 {
 508                         cmd = new SqlCommand ();
 509                         conn = new SqlConnection (connectionString);
 510                         conn.Open ();
 511
 512                         cmd.CommandText = "Select id from numeric_family where id=@ID" ;
 513                         cmd.Connection = conn ;
 514
 515                         // Test if Parameters are correctly populated
 516                         cmd.Parameters.Clear ();
 517                         cmd.Parameters.Add ("@ID", SqlDbType.TinyInt);
 518                         cmd.Parameters["@ID"].Value = 2 ;
 519                         cmd.Prepare ();
 520                         Assert.AreEqual (2, cmd.ExecuteScalar (), "#3 Prepared Stmt not working");
 521
 522                         cmd.Parameters[0].Value = 3;
 523                         Assert.AreEqual (3, cmd.ExecuteScalar (), "#4 Prep Stmt not working");
 524                         conn.Close ();
 525                 }
 526
 527                 [Test]
 528                 public void PrepareTest ()
 529                 {
 530                         cmd = new SqlCommand ();
 531                         conn = new SqlConnection (connectionString);
 532                         conn.Open ();
 533
 534                         cmd.CommandText = "Select id from numeric_family where id=@ID" ;
 535                         cmd.Connection = conn ;
 536
 537                         // Test InvalidOperation Exception is thrown if Parameter Type
 538                         // is not explicitly set
 539                         cmd.Parameters.Add ("@ID", 2);
 540                         try {
 541                                 cmd.Prepare ();
 542                                 Assert.Fail ("#1 Parameter Type shud be explicitly Set");
 543                         }catch (AssertionException e) {
 544                                 throw e;
 545                         }catch (Exception e) {
 546                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType (),
 547                                         "#2 Incorrect Exception : " + e.StackTrace);
 548                         }
 549
 550                         // Test Exception is thrown for variable size data  if precision/scale
 551                         // is not set
 552                         cmd.CommandText = "select type_varchar from string_family where type_varchar=@p1";
 553                         cmd.Parameters.Clear ();
 554                         cmd.Parameters.Add ("@p1", SqlDbType.VarChar);
 555                         cmd.Parameters["@p1"].Value = "afasasadadada";
 556                         try {
 557                                 cmd.Prepare ();
 558                                 Assert.Fail ("#5 Exception shud be thrown");
 559                         }catch (AssertionException e) {
 560                                 throw e;
 561                         }catch (Exception e) {
 562                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 563                                         "#6 Incorrect Exception " + e.StackTrace);
 564                         }
 565
 566                         // Test Exception is not thrown for Stored Procs
 567                         try {
 568                                 cmd.CommandType = CommandType.StoredProcedure;
 569                                 cmd.CommandText = "ABFSDSFSF" ;
 570                                 cmd.Prepare ();
 571                         }catch (Exception e) {
 572                                 Assert.Fail ("#7 Exception shud not be thrown for Stored Procs");
 573                         }
 574                         cmd.CommandType = CommandType.Text;
 575                         conn.Close ();
 576
 577                         //Test InvalidOperation Exception is thrown if connection is not set
 578                         cmd.Connection = null;
 579                         try {
 580                                 cmd.Prepare ();
 581 #if NET_2_0
 582                                 Assert.Fail ("#8 NullReferenceException should be thrown");
 583 #else
 584                                 Assert.Fail ("#8 InvalidOperation Exception should be thrown");
 585 #endif
 586                         }
 587                         catch (AssertionException e) {
 588                                 throw e;
 589                         }catch (Exception e) {
 590 #if NET_2_0
 591                                 Assert.AreEqual (typeof (NullReferenceException), e.GetType (),
 592                                         "#9 Incorrect Exception : " + e.StackTrace);
 593 #else
 594                                 Assert.AreEqual (typeof (InvalidOperationException), e.GetType (),
 595                                         "#9 Incorrect Exception : " + e.StackTrace);
 596 #endif
 597                         }
 598
 599                         //Test InvalidOperation Exception is thrown if connection is closed
 600                         cmd.Connection = conn ;
 601                         try{
 602                                 cmd.Prepare ();
 603                                 Assert.Fail ("#4 InvalidOperation Exception shud be thrown");
 604                         }catch (AssertionException e) {
 605                                 throw e;
 606                         }catch (Exception e) {
 607 #if NET_2_0
 608                                 Assert.AreEqual (typeof(NullReferenceException), e.GetType(),
 609                                         "Incorrect Exception : " + e.StackTrace);
 610 #else
 611                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 612                                         "Incorrect Exception : " + e.StackTrace);
 613 #endif
 614                         }
 615                 }
 616
 617                 [Test]
 618                 public void ResetTimeOut ()
 619                 {
 620                         SqlCommand cmd = new SqlCommand ();
 621                         cmd.CommandTimeout = 50 ;
 622                         Assert.AreEqual ( cmd.CommandTimeout, 50,
 623                                 "#1 CommandTimeout should be modfiable");
 624                         cmd.ResetCommandTimeout ();
 625                         Assert.AreEqual (cmd.CommandTimeout, 30,
 626                                 "#2 Reset Should set the Timeout to default value");
 627                 }
 628
 629                 [Test]
 630                 [ExpectedException (typeof(ArgumentException))]
 631                 public void CommandTimeout ()
 632                 {
 633                         cmd = new SqlCommand ();
 634                         cmd.CommandTimeout = 10;
 635                         Assert.AreEqual (10, cmd.CommandTimeout, "#1");
 636                         cmd.CommandTimeout = -1;
 637                 }
 638
 639                 [Test]
 640 #if NET_2_0
 641                 [ExpectedException (typeof(ArgumentOutOfRangeException))]
 642 #else
 643                 [ExpectedException (typeof(ArgumentException))]
 644 #endif
 645                 public void CommandTypeTest ()
 646                 {
 647                         cmd = new SqlCommand ();
 648                         Assert.AreEqual (CommandType.Text ,cmd.CommandType,
 649                                 "Default CommandType is text");
 650                         cmd.CommandType = (CommandType)(-1);
 651                 }
 652
 653                 [Test]
 654                 [Ignore ("msdotnet contradicts documented behavior")]
 655                 [ExpectedException (typeof(InvalidOperationException))]
 656                 public void ConnectionTest ()
 657                 {
 658                         SqlTransaction trans = null;
 659                         try {
 660                                 conn = new SqlConnection (connectionString);
 661                                 conn.Open ();
 662                                 trans = conn.BeginTransaction ();
 663                                 cmd = new SqlCommand ("", conn,trans);
 664                                 cmd.CommandText = "Select id from numeric_family where id=1";
 665                                 cmd.Connection = new SqlConnection ();
 666                         }finally {
 667                                 trans.Rollback();
 668                                 conn.Close ();
 669                         }
 670                 }
 671
 672                 [Test]
 673                 public void TransactionTest ()
 674                 {
 675                         conn = new SqlConnection (connectionString);
 676                         cmd = new SqlCommand ("", conn);
 677                         Assert.IsNull (cmd.Transaction, "#1 Default value is null");
 678
 679                         SqlConnection conn1 = new SqlConnection (connectionString);
 680                         conn1.Open ();
 681                         SqlTransaction trans1 = conn1.BeginTransaction ();
 682                         cmd.Transaction = trans1 ;
 683                         try {
 684                                 cmd.ExecuteNonQuery ();
 685                                 Assert.Fail ("#2 Connection cannot be different");
 686                         }catch (Exception e) {
 687 #if NET_2_0
 688                                 Assert.AreEqual (typeof(NullReferenceException), e.GetType(),
 689                                         "#3 Incorrect Exception : " + e);
 690 #else
 691                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 692                                         "#3 Incorrect Exception : " + e);
 693 #endif
 694                         }finally {
 695                                 conn1.Close ();
 696                                 conn.Close ();
 697                         }
 698                 }
 699
 700                 // Need to add more tests
 701                 [Test]
 702 #if NET_2_0
 703                 [ExpectedException (typeof(ArgumentOutOfRangeException))]
 704 #else
 705                 [ExpectedException (typeof(ArgumentException))]
 706 #endif
 707                 public void UpdatedRowSourceTest ()
 708                 {
 709                         cmd = new SqlCommand ();
 710                         Assert.AreEqual (UpdateRowSource.Both, cmd.UpdatedRowSource,
 711                                 "#1 Default value is both");
 712                         cmd.UpdatedRowSource = UpdateRowSource.None;
 713                         Assert.AreEqual (UpdateRowSource.None, cmd.UpdatedRowSource,
 714                                 "#2");
 715
 716                         cmd.UpdatedRowSource = (UpdateRowSource) (-1);
 717                 }
 718
 719                 [Test]
 720                 public void ExecuteNonQueryTempProcedureTest () {
 721                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
 722                         try {
 723                                 ConnectionManager.Singleton.OpenConnection ();
 724                                 // create temp sp here, should normally be created in Setup of test
 725                                 // case, but cannot be done right now because of ug #68978
 726                                 DBHelper.ExecuteNonQuery (conn, CREATE_TMP_SP_TEMP_INSERT_PERSON);
 727                                 SqlCommand cmd = new SqlCommand();
 728                                 cmd.Connection = conn;
 729                                 cmd.CommandText = "#sp_temp_insert_employee";
 730                                 cmd.CommandType = CommandType.StoredProcedure;
 731                                 Object TestPar = "test";
 732                                 cmd.Parameters.Add("@fname", SqlDbType.VarChar);
 733                                 cmd.Parameters ["@fname"].Value = TestPar;
 734                                 Assert.AreEqual(1,cmd.ExecuteNonQuery());
 735                         } finally {
 736                                 DBHelper.ExecuteNonQuery (conn, DROP_TMP_SP_TEMP_INSERT_PERSON);
 737                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_person_table");
 738                                 ConnectionManager.Singleton.CloseConnection ();
 739                         }
 740                 }
 741
 742                 // Test for bug #76778
 743                 // Test for a case, when query size is greater than the block size
 744                 [Test]
 745                 public void LongQueryTest ()
 746                 {
 747                         SqlConnection conn = new SqlConnection (
 748                                                         connectionString + ";Pooling=false");
 749                         using (conn) {
 750                                 conn.Open ();
 751                                 SqlCommand cmd = conn.CreateCommand ();
 752                                 String value =  new String ('a', 10000);
 753                                 cmd.CommandText = String.Format ("Select '{0}'", value);
 754                                 cmd.ExecuteNonQuery ();
 755                         }
 756                 }
 757
 758                 // Test for bug #76778
 759                 // To make sure RPC (when implemented) works ok..
 760                 [Test]
 761                 public void LongStoredProcTest()
 762                 {
 763                         SqlConnection conn = new SqlConnection (
 764                                                         connectionString + ";Pooling=false");
 765                         using (conn) {
 766                                 conn.Open ();
 767                                 int size = conn.PacketSize ;
 768                                 SqlCommand cmd = conn.CreateCommand ();
 769                                 // create a temp stored proc ..
 770                                 cmd.CommandText  = "Create Procedure #sp_tmp_long_params ";
 771                                 cmd.CommandText += "@p1 nvarchar (4000), ";
 772                                 cmd.CommandText += "@p2 nvarchar (4000), ";
 773                                 cmd.CommandText += "@p3 nvarchar (4000), ";
 774                                 cmd.CommandText += "@p4 nvarchar (4000) out ";
 775                                 cmd.CommandText += "As ";
 776                                 cmd.CommandText += "Begin ";
 777                                 cmd.CommandText += "Set @p4 = N'Hello' ";
 778                                 cmd.CommandText += "Return 2 ";
 779                                 cmd.CommandText += "End";
 780                                 cmd.ExecuteNonQuery ();
 781
 782                                 //execute the proc
 783                                 cmd.CommandType = CommandType.StoredProcedure;
 784                                 cmd.CommandText = "#sp_tmp_long_params";
 785
 786                                 String value =  new String ('a', 4000);
 787                                 SqlParameter p1 = new SqlParameter ("@p1",
 788                                                         SqlDbType.NVarChar,4000);
 789                                 p1.Value = value;
 790
 791                                 SqlParameter p2 = new SqlParameter ("@p2",
 792                                                         SqlDbType.NVarChar,4000);
 793                                 p2.Value = value;
 794
 795                                 SqlParameter p3 = new SqlParameter ("@p3",
 796                                                         SqlDbType.NVarChar,4000);
 797                                 p3.Value = value;
 798
 799                                 SqlParameter p4 = new SqlParameter ("@p4",
 800                                                         SqlDbType.NVarChar,4000);
 801                                 p4.Direction = ParameterDirection.Output;
 802
 803                                 // for now, name shud be @RETURN_VALUE
 804                                 // can be changed once RPC is implemented
 805                                 SqlParameter p5 = new SqlParameter ("@RETURN_VALUE", SqlDbType.Int);
 806                                 p5.Direction = ParameterDirection.ReturnValue ;
 807
 808                                 cmd.Parameters.Add (p1);
 809                                 cmd.Parameters.Add (p2);
 810                                 cmd.Parameters.Add (p3);
 811                                 cmd.Parameters.Add (p4);
 812                                 cmd.Parameters.Add (p5);
 813
 814                                 cmd.ExecuteNonQuery ();
 815                                 Assert.AreEqual ("Hello", p4.Value, "#1");
 816                                 Assert.AreEqual (2, p5.Value, "#2");
 817                         }
 818                 }
 819
 820                 // Test for bug #76880
 821                 [Test]
 822                 public void DateTimeParameterTest ()
 823                 {
 824                         SqlConnection conn = new SqlConnection (connectionString);
 825                         using (conn) {
 826                                 conn.Open ();
 827                                 SqlCommand cmd = conn.CreateCommand ();
 828                                 cmd.CommandText = "select * from datetime_family where type_datetime=@p1";
 829                                 cmd.Parameters.Add ("@p1", SqlDbType.DateTime).Value= "10-10-2005";
 830                                 // shudnt cause and exception
 831                                 SqlDataReader rdr = cmd.ExecuteReader ();
 832                                 rdr.Close ();
 833                         }
 834                 }
 835
 836                 /**
 837                  * Verifies whether an enum value is converted to a numeric value when
 838                  * used as value for a numeric parameter (bug #66630)
 839                  */
 840                 [Test]
 841                 public void EnumParameterTest() {
 842                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
 843                         try {
 844                                 ConnectionManager.Singleton.OpenConnection ();
 845                                 // create temp sp here, should normally be created in Setup of test
 846                                 // case, but cannot be done right now because of ug #68978
 847                                 DBHelper.ExecuteNonQuery (conn, "CREATE PROCEDURE #Bug66630 ("
 848                                                           + "@Status smallint = 7"
 849                                                           + ")"
 850                                                           + "AS" + Environment.NewLine
 851                                                           + "BEGIN" + Environment.NewLine
 852                                                           + "SELECT CAST(5 AS int), @Status" + Environment.NewLine
 853                                                           + "END");
 854
 855                                 SqlCommand cmd = new SqlCommand("#Bug66630", conn);
 856                                 cmd.CommandType = CommandType.StoredProcedure;
 857                                 cmd.Parameters.Add("@Status", SqlDbType.Int).Value = Status.Error;
 858
 859                                 using (SqlDataReader dr = cmd.ExecuteReader()) {
 860                                         // one record should be returned
 861                                         Assert.IsTrue(dr.Read(), "EnumParameterTest#1");
 862                                         // we should get two field in the result
 863                                         Assert.AreEqual(2, dr.FieldCount, "EnumParameterTest#2");
 864                                         // field 1
 865                                         Assert.AreEqual("int", dr.GetDataTypeName(0), "EnumParameterTest#3");
 866                                         Assert.AreEqual(5, dr.GetInt32(0), "EnumParameterTest#4");
 867                                         // field 2
 868                                         Assert.AreEqual("smallint", dr.GetDataTypeName(1), "EnumParameterTest#5");
 869                                         Assert.AreEqual((short) Status.Error, dr.GetInt16(1), "EnumParameterTest#6");
 870                                         // only one record should be returned
 871                                         Assert.IsFalse(dr.Read(), "EnumParameterTest#7");
 872                                 }
 873                         } finally {
 874                                 DBHelper.ExecuteNonQuery (conn, "if exists (select name from sysobjects " +
 875                                                           " where name like '#temp_Bug66630' and type like 'P') " +
 876                                                           " drop procedure #temp_Bug66630; ");
 877                                 ConnectionManager.Singleton.CloseConnection ();
 878                         }
 879                 }
 880
 881                 /**
 882                  * The below test does not need a connection but since the setup opens
 883                  * the connection i will need to close it
 884                  */
 885                 [Test]
 886                 public void CloneTest() {
 887                         ConnectionManager.Singleton.OpenConnection ();
 888                         SqlCommand cmd = new SqlCommand();
 889                         cmd.Connection = null;
 890                         cmd.CommandText = "sp_insert";
 891                         cmd.CommandType = CommandType.StoredProcedure;
 892                         Object TestPar = DBNull.Value;
 893                         cmd.Parameters.Add("@TestPar1", SqlDbType.Int);
 894                         cmd.Parameters["@TestPar1"].Value = TestPar;
 895                         cmd.Parameters.Add("@BirthDate", DateTime.Now);
 896                         cmd.DesignTimeVisible = true;
 897                         cmd.CommandTimeout = 100;
 898                         Object clone1 = ((ICloneable)(cmd)).Clone();
 899                         SqlCommand cmd1 = (SqlCommand) clone1;
 900                         Assert.AreEqual(2, cmd1.Parameters.Count);
 901                         Assert.AreEqual(100, cmd1.CommandTimeout);
 902                         cmd1.Parameters.Add("@test", DateTime.Now);
 903                         // to check that it is deep copy and not a shallow copy of the
 904                         // parameter collection
 905                         Assert.AreEqual(3, cmd1.Parameters.Count);
 906                         Assert.AreEqual(2, cmd.Parameters.Count);
 907                 }
 908
 909                 private enum Status {
 910                         OK = 0,
 911                         Error = 3
 912                 }
 913
 914                 private readonly string CREATE_TMP_SP_TEMP_INSERT_PERSON = ("create procedure #sp_temp_insert_employee ( " + Environment.NewLine +
 915                                                                             "@fname varchar (20)) " + Environment.NewLine +
 916                                                                             "as " + Environment.NewLine +
 917                                                                             "begin" + Environment.NewLine +
 918                                                                             "declare @id int;" + Environment.NewLine +
 919                                                                             "select @id = max (id) from employee;" + Environment.NewLine +
 920                                                                             "set @id = @id + 6000 + 1;" + Environment.NewLine +
 921                                                                             "insert into employee (id, fname, dob, doj) values (@id, @fname, '1980-02-11', getdate ());" + Environment.NewLine +
 922                                                                             "return @id;" + Environment.NewLine +
 923                                                                             "end");
 924
 925                 private readonly string DROP_TMP_SP_TEMP_INSERT_PERSON = ("if exists (select name from sysobjects where " + Environment.NewLine +
 926                                                                           "name = '#sp_temp_insert_employee' and type = 'P') " + Environment.NewLine +
 927                                                                           "drop procedure #sp_temp_insert_employee; ");
 928         }
 929 }
 930