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