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                                 cmd.CommandText = "Delete from numeric_family where id=100";
 228                                 result = cmd.ExecuteNonQuery ();
 229                                 Assert.AreEqual (1, result, "#4 One row shud be deleted");
 230
 231                         }finally {
 232                                 trans.Rollback ();
 233                         }
 234                 }
 235
 236                 [Test]
 237                 public void ExecuteReaderTest ()
 238                 {
 239                         SqlDataReader reader = null;
 240                         conn = new SqlConnection (connectionString);
 241
 242                         // Test exception is thrown if conn is closed
 243                         cmd = new SqlCommand ("Select count(*) from numeric_family");
 244                         try {
 245                                 reader = cmd.ExecuteReader ();
 246                         }catch (AssertionException e) {
 247                                 throw e;
 248                         }catch (Exception e) {
 249                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 250                                         "#1 Incorrect Exception");
 251                         }
 252
 253                         conn.Open ();
 254                         // Test exception is thrown for Invalid Query
 255                         cmd = new SqlCommand ("InvalidQuery", conn);
 256                         try {
 257                                 reader = cmd.ExecuteReader ();
 258                                 Assert.Fail ("#1 Exception shud be thrown");
 259                         }catch (AssertionException e) {
 260                                 throw e;
 261                         }catch (Exception e) {
 262                                 Assert.AreEqual (typeof(SqlException), e.GetType (),
 263                                         "#2 Incorrect Exception : " + e);
 264                         }
 265
 266                         // NOTE
 267                         // Test SqlException is thrown if a row is locked
 268                         // should lock a particular row and then modify it
 269                         /*
 270                         */
 271
 272                         // Test Connection  cannot be modified when reader is in use
 273                         // NOTE : msdotnet contradicts documented behavior
 274                         cmd.CommandText = "select * from numeric_family where id=1";
 275                         reader = cmd.ExecuteReader ();
 276                         reader.Read ();
 277                         conn.Close (); // valid operation
 278                         conn = new SqlConnection (connectionString);
 279
 280                         /*
 281                         // NOTE msdotnet contradcits documented behavior
 282                         // If the above testcase fails, then this shud be tested
 283                         // Test connection can be modified once reader is closed
 284                         conn.Close ();
 285                         reader.Close ();
 286                         conn = new SqlConnection (connectionString); // valid operation
 287                         */
 288                 }
 289
 290                 [Test]
 291                 public void ExecuteReaderCommandBehaviorTest ()
 292                 {
 293                         // Test for command behaviors
 294                         DataTable schemaTable = null;
 295                         SqlDataReader reader = null;
 296
 297                         conn = new SqlConnection (connectionString);
 298                         conn.Open ();
 299                         cmd = new SqlCommand ("", conn);
 300                         cmd.CommandText = "Select id from numeric_family where id <=4 order by id asc;";
 301                         cmd.CommandText += "Select type_bit from numeric_family where id <=4 order by id asc";
 302
 303                         // Test for default command behavior
 304                         reader = cmd.ExecuteReader ();
 305                         int rows = 0;
 306                         int results = 0;
 307                         do {
 308                                 while (reader.Read ())
 309                                         rows++ ;
 310                                 Assert.AreEqual (4, rows, "#1 Multiple rows shud be returned");
 311                                 results++;
 312                                 rows = 0;
 313                         }while (reader.NextResult());
 314                         Assert.AreEqual (2, results, "#2 Multiple result sets shud be returned");
 315                         reader.Close ();
 316
 317                         // Test if closing reader, closes the connection
 318                         reader = cmd.ExecuteReader (CommandBehavior.CloseConnection);
 319                         reader.Close ();
 320                         Assert.AreEqual (ConnectionState.Closed, conn.State,
 321                                 "#3 Command Behavior is not followed");
 322                         conn.Open();
 323
 324                         // Test if row info and primary Key info is returned
 325                         reader = cmd.ExecuteReader (CommandBehavior.KeyInfo);
 326                         schemaTable = reader.GetSchemaTable ();
 327                         Assert.IsTrue(reader.HasRows, "#4 Data Rows shud also be returned");
 328                         Assert.IsTrue ((bool)schemaTable.Rows[0]["IsKey"],
 329                                 "#5 Primary Key info shud be returned");
 330                         reader.Close ();
 331
 332                         // Test only column information is returned
 333                         reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly);
 334                         schemaTable = reader.GetSchemaTable ();
 335                         Assert.IsFalse (reader.HasRows, "#6 row data shud not be returned");
 336                         Assert.AreEqual(DBNull.Value, schemaTable.Rows[0]["IsKey"],
 337                                 "#7 Primary Key info shud not be returned");
 338                         Assert.AreEqual ("id", schemaTable.Rows[0]["ColumnName"],
 339                                 "#8 Schema Data is Incorrect");
 340                         reader.Close ();
 341
 342                         // Test only one result set (first) is returned
 343                         reader = cmd.ExecuteReader (CommandBehavior.SingleResult);
 344                         schemaTable = reader.GetSchemaTable ();
 345                         Assert.IsFalse (reader.NextResult(),
 346                                 "#9 Only one result set shud be returned");
 347                         Assert.AreEqual ("id", schemaTable.Rows[0]["ColumnName"],
 348                                 "#10 The result set returned shud be the first result set");
 349                         reader.Close ();
 350
 351                         // Test only one row is returned for all result sets
 352                         // msdotnet doesnt work correctly.. returns only one result set
 353                         reader = cmd.ExecuteReader (CommandBehavior.SingleRow);
 354                         rows=0;
 355                         results=0;
 356                         do {
 357                                 while (reader.Read ())
 358                                         rows++ ;
 359                                 Assert.AreEqual (1, rows, "#11 Only one row shud be returned");
 360                                 results++;
 361                                 rows = 0;
 362                         }while (reader.NextResult());
 363                         // NOTE msdotnet contradicts documented behavior.
 364                         // Multiple result sets shud be returned , and in this case : 2
 365                         //Assert.AreEqual (2, results, "# Multiple result sets shud be returned");
 366                         Assert.AreEqual (2, results, "#12 Multiple result sets shud be returned");
 367                         reader.Close ();
 368                 }
 369
 370                 [Test]
 371                 public void PrepareTest_CheckValidStatement ()
 372                 {
 373                         cmd = new SqlCommand ();
 374                         conn = new SqlConnection (connectionString);
 375                         conn.Open ();
 376
 377                         cmd.CommandText = "Select id from numeric_family where id=@ID" ;
 378                         cmd.Connection = conn ;
 379
 380                         // Test if Parameters are correctly populated
 381                         cmd.Parameters.Clear ();
 382                         cmd.Parameters.Add ("@ID", SqlDbType.TinyInt);
 383                         cmd.Parameters["@ID"].Value = 2 ;
 384                         cmd.Prepare ();
 385                         Assert.AreEqual (2, cmd.ExecuteScalar (), "#3 Prepared Stmt not working");
 386
 387                         cmd.Parameters[0].Value = 3;
 388                         Assert.AreEqual (3, cmd.ExecuteScalar (), "#4 Prep Stmt not working");
 389                         conn.Close ();
 390                 }
 391
 392                 [Test]
 393                 public void PrepareTest ()
 394                 {
 395                         cmd = new SqlCommand ();
 396                         conn = new SqlConnection (connectionString);
 397                         conn.Open ();
 398
 399                         cmd.CommandText = "Select id from numeric_family where id=@ID" ;
 400                         cmd.Connection = conn ;
 401
 402                         // Test InvalidOperation Exception is thrown if Parameter Type
 403                         // is not explicitly set
 404                         cmd.Parameters.Add ("@ID", 2);
 405                         try {
 406                                 cmd.Prepare ();
 407                                 Assert.Fail ("#1 Parameter Type shud be explicitly Set");
 408                         }catch (AssertionException e) {
 409                                 throw e;
 410                         }catch (Exception e) {
 411                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType (),
 412                                         "#2 Incorrect Exception : " + e.StackTrace);
 413                         }
 414
 415                         // Test Exception is thrown for variable size data  if precision/scale
 416                         // is not set
 417                         cmd.CommandText = "select type_varchar from string_family where type_varchar=@p1";
 418                         cmd.Parameters.Clear ();
 419                         cmd.Parameters.Add ("@p1", SqlDbType.VarChar);
 420                         cmd.Parameters["@p1"].Value = "afasasadadada";
 421                         try {
 422                                 cmd.Prepare ();
 423                                 Assert.Fail ("#5 Exception shud be thrown");
 424                         }catch (AssertionException e) {
 425                                 throw e;
 426                         }catch (Exception e) {
 427                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 428                                         "#6 Incorrect Exception " + e.StackTrace);
 429                         }
 430
 431                         // Test Exception is not thrown for Stored Procs
 432                         try {
 433                                 cmd.CommandType = CommandType.StoredProcedure;
 434                                 cmd.CommandText = "ABFSDSFSF" ;
 435                                 cmd.Prepare ();
 436                         }catch (Exception e) {
 437                                 Assert.Fail ("#7 Exception shud not be thrown for Stored Procs");
 438                         }
 439                         cmd.CommandType = CommandType.Text;
 440                         conn.Close ();
 441
 442                         //Test InvalidOperation Exception is thrown if connection is not set
 443                         cmd.Connection = null;
 444                         try {
 445                                 cmd.Prepare ();
 446                                 Assert.Fail ("#8 InvalidOperation Exception shud be thrown");
 447                         }catch (AssertionException e) {
 448                                 throw e;
 449                         }catch (Exception e) {
 450                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 451                                         "#9 Incorrect Exception : " + e.StackTrace);
 452                         }
 453
 454                         //Test InvalidOperation Exception is thrown if connection is closed
 455                         cmd.Connection = conn ;
 456                         try{
 457                                 cmd.Prepare ();
 458                                 Assert.Fail ("#4 InvalidOperation Exception shud be thrown");
 459                         }catch (AssertionException e) {
 460                                 throw e;
 461                         }catch (Exception e) {
 462                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 463                                         "Incorrect Exception : " + e.StackTrace);
 464                         }
 465                 }
 466
 467                 [Test]
 468                 public void ResetTimeOut ()
 469                 {
 470                         SqlCommand cmd = new SqlCommand ();
 471                         cmd.CommandTimeout = 50 ;
 472                         Assert.AreEqual ( cmd.CommandTimeout, 50,
 473                                 "#1 CommandTimeout should be modfiable");
 474                         cmd.ResetCommandTimeout ();
 475                         Assert.AreEqual (cmd.CommandTimeout, 30,
 476                                 "#2 Reset Should set the Timeout to default value");
 477                 }
 478
 479                 [Test]
 480                 [ExpectedException (typeof(ArgumentException))]
 481                 public void CommandTimeout ()
 482                 {
 483                         cmd = new SqlCommand ();
 484                         cmd.CommandTimeout = 10;
 485                         Assert.AreEqual (10, cmd.CommandTimeout, "#1");
 486                         cmd.CommandTimeout = -1;
 487                 }
 488
 489                 [Test]
 490                 [ExpectedException (typeof(ArgumentException))]
 491                 public void CommandTypeTest ()
 492                 {
 493                         cmd = new SqlCommand ();
 494                         Assert.AreEqual (CommandType.Text ,cmd.CommandType,
 495                                 "Default CommandType is text");
 496                         cmd.CommandType = (CommandType)(-1);
 497                 }
 498
 499                 [Test]
 500                 [Ignore ("msdotnet contradicts documented behavior")]
 501                 [ExpectedException (typeof(InvalidOperationException))]
 502                 public void ConnectionTest ()
 503                 {
 504                         SqlTransaction trans = null;
 505                         try {
 506                                 conn = new SqlConnection (connectionString);
 507                                 conn.Open ();
 508                                 trans = conn.BeginTransaction ();
 509                                 cmd = new SqlCommand ("", conn,trans);
 510                                 cmd.CommandText = "Select id from numeric_family where id=1";
 511                                 cmd.Connection = new SqlConnection ();
 512                         }finally {
 513                                 trans.Rollback();
 514                                 conn.Close ();
 515                         }
 516                 }
 517
 518                 [Test]
 519                 public void TransactionTest ()
 520                 {
 521                         conn = new SqlConnection (connectionString);
 522                         cmd = new SqlCommand ("", conn);
 523                         Assert.IsNull (cmd.Transaction, "#1 Default value is null");
 524
 525                         SqlConnection conn1 = new SqlConnection (connectionString);
 526                         conn1.Open ();
 527                         SqlTransaction trans1 = conn1.BeginTransaction ();
 528                         cmd.Transaction = trans1 ;
 529                         try {
 530                                 cmd.ExecuteNonQuery ();
 531                                 Assert.Fail ("#2 Connection cannot be different");
 532                         }catch (Exception e) {
 533                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 534                                         "#3 Incorrect Exception : " + e);
 535                         }finally {
 536                                 conn1.Close ();
 537                                 conn.Close ();
 538                         }
 539                 }
 540
 541                 // Need to add more tests
 542                 [Test]
 543                 [ExpectedException (typeof(ArgumentException))]
 544                 public void UpdatedRowSourceTest ()
 545                 {
 546                         cmd = new SqlCommand ();
 547                         Assert.AreEqual (UpdateRowSource.Both, cmd.UpdatedRowSource,
 548                                 "#1 Default value is both");
 549                         cmd.UpdatedRowSource = UpdateRowSource.None;
 550                         Assert.AreEqual (UpdateRowSource.None, cmd.UpdatedRowSource,
 551                                 "#2");
 552
 553                         cmd.UpdatedRowSource = (UpdateRowSource) (-1);
 554                 }
 555
 556                 [Test]
 557                 public void ExecuteNonQueryTempProcedureTest () {
 558                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
 559                         try {
 560                                 ConnectionManager.Singleton.OpenConnection ();
 561                                 // create temp sp here, should normally be created in Setup of test
 562                                 // case, but cannot be done right now because of ug #68978
 563                                 DBHelper.ExecuteNonQuery (conn, CREATE_TMP_SP_TEMP_INSERT_PERSON);
 564                                 SqlCommand cmd = new SqlCommand();
 565                                 cmd.Connection = conn;
 566                                 cmd.CommandText = "#sp_temp_insert_employee";
 567                                 cmd.CommandType = CommandType.StoredProcedure;
 568                                 Object TestPar = "test";
 569                                 cmd.Parameters.Add("@fname", SqlDbType.VarChar);
 570                                 cmd.Parameters ["@fname"].Value = TestPar;
 571                                 Assert.AreEqual(-1,cmd.ExecuteNonQuery());
 572                         } finally {
 573                                 DBHelper.ExecuteNonQuery (conn, DROP_TMP_SP_TEMP_INSERT_PERSON);
 574                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_person_table");
 575                                 ConnectionManager.Singleton.CloseConnection ();
 576                         }
 577                 }
 578
 579                 /**
 580                  * Verifies whether an enum value is converted to a numeric value when
 581                  * used as value for a numeric parameter (bug #66630)
 582                  */
 583                 [Test]
 584                 public void EnumParameterTest() {
 585                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
 586                         try {
 587                                 ConnectionManager.Singleton.OpenConnection ();
 588                                 // create temp sp here, should normally be created in Setup of test
 589                                 // case, but cannot be done right now because of ug #68978
 590                                 DBHelper.ExecuteNonQuery (conn, "CREATE PROCEDURE #Bug66630 ("
 591                                                           + "@Status smallint = 7"
 592                                                           + ")"
 593                                                           + "AS" + Environment.NewLine
 594                                                           + "BEGIN" + Environment.NewLine
 595                                                           + "SELECT CAST(5 AS int), @Status" + Environment.NewLine
 596                                                           + "END");
 597
 598                                 SqlCommand cmd = new SqlCommand("#Bug66630", conn);
 599                                 cmd.CommandType = CommandType.StoredProcedure;
 600                                 cmd.Parameters.Add("@Status", SqlDbType.Int).Value = Status.Error;
 601
 602                                 using (SqlDataReader dr = cmd.ExecuteReader()) {
 603                                         // one record should be returned
 604                                         Assert.IsTrue(dr.Read(), "EnumParameterTest#1");
 605                                         // we should get two field in the result
 606                                         Assert.AreEqual(2, dr.FieldCount, "EnumParameterTest#2");
 607                                         // field 1
 608                                         Assert.AreEqual("int", dr.GetDataTypeName(0), "EnumParameterTest#3");
 609                                         Assert.AreEqual(5, dr.GetInt32(0), "EnumParameterTest#4");
 610                                         // field 2
 611                                         Assert.AreEqual("smallint", dr.GetDataTypeName(1), "EnumParameterTest#5");
 612                                         Assert.AreEqual((short) Status.Error, dr.GetInt16(1), "EnumParameterTest#6");
 613                                         // only one record should be returned
 614                                         Assert.IsFalse(dr.Read(), "EnumParameterTest#7");
 615                                 }
 616                         } finally {
 617                                 DBHelper.ExecuteNonQuery (conn, "if exists (select name from sysobjects " +
 618                                                           " where name like '#temp_Bug66630' and type like 'P') " +
 619                                                           " drop procedure #temp_Bug66630; ");
 620                                 ConnectionManager.Singleton.CloseConnection ();
 621                         }
 622                 }
 623
 624                 /**
 625                  * The below test does not need a connection but since the setup opens
 626                  * the connection i will need to close it
 627                  */
 628                 [Test]
 629                 public void CloneTest() {
 630                         ConnectionManager.Singleton.OpenConnection ();
 631                         SqlCommand cmd = new SqlCommand();
 632                         cmd.Connection = null;
 633                         cmd.CommandText = "sp_insert";
 634                         cmd.CommandType = CommandType.StoredProcedure;
 635                         Object TestPar = DBNull.Value;
 636                         cmd.Parameters.Add("@TestPar1", SqlDbType.Int);
 637                         cmd.Parameters["@TestPar1"].Value = TestPar;
 638                         cmd.Parameters.Add("@BirthDate", DateTime.Now);
 639                         cmd.DesignTimeVisible = true;
 640                         cmd.CommandTimeout = 100;
 641                         Object clone1 = ((ICloneable)(cmd)).Clone();
 642                         SqlCommand cmd1 = (SqlCommand) clone1;
 643                         Assert.AreEqual(2, cmd1.Parameters.Count);
 644                         Assert.AreEqual(100, cmd1.CommandTimeout);
 645                         cmd1.Parameters.Add("@test", DateTime.Now);
 646                         // to check that it is deep copy and not a shallow copy of the
 647                         // parameter collection
 648                         Assert.AreEqual(3, cmd1.Parameters.Count);
 649                         Assert.AreEqual(2, cmd.Parameters.Count);
 650                 }
 651
 652                 private enum Status {
 653                         OK = 0,
 654                         Error = 3
 655                 }
 656
 657                 private readonly string CREATE_TMP_SP_TEMP_INSERT_PERSON = ("create procedure #sp_temp_insert_employee ( " + Environment.NewLine +
 658                                                                             "@fname varchar (20)) " + Environment.NewLine +
 659                                                                             "as " + Environment.NewLine +
 660                                                                             "begin" + Environment.NewLine +
 661                                                                             "declare @id int;" + Environment.NewLine +
 662                                                                             "select @id = max (id) from employee;" + Environment.NewLine +
 663                                                                             "set @id = @id + 6000 + 1;" + Environment.NewLine +
 664                                                                             "insert into employee (id, fname, dob, doj) values (@id, @fname, '1980-02-11', getdate ());" + Environment.NewLine +
 665                                                                             "return @id;" + Environment.NewLine +
 666                                                                             "end");
 667
 668                 private readonly string DROP_TMP_SP_TEMP_INSERT_PERSON = ("if exists (select name from sysobjects where " + Environment.NewLine +
 669                                                                           "name = '#sp_temp_insert_employee' and type = 'P') " + Environment.NewLine +
 670                                                                           "drop procedure #sp_temp_insert_employee; ");
 671         }
 672 }
 673