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