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

   1 //
   2 // SqlCommandTest.cs - NUnit Test Cases for testing the
   3 //                          SqlCommand class
   4 // Author:
   5 //      Umadevi S (sumadevi@novell.com)
   6 //      Sureshkumar T (tsureshkumar@novell.com)
   7 //      Senganal T (tsenganal@novell.com)
   8 //
   9 // Copyright (c) 2004 Novell Inc., and the individuals listed
  10 // on the ChangeLog entries.
  11 //
  12 // Permission is hereby granted, free of charge, to any person obtaining
  13 // a copy of this software and associated documentation files (the
  14 // "Software"), to deal in the Software without restriction, including
  15 // without limitation the rights to use, copy, modify, merge, publish,
  16 // distribute, sublicense, and/or sell copies of the Software, and to
  17 // permit persons to whom the Software is furnished to do so, subject to
  18 // the following conditions:
  19 //
  20 // The above copyright notice and this permission notice shall be
  21 // included in all copies or substantial portions of the Software.
  22 //
  23 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  24 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  25 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  26 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  27 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  28 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  29 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  30 //
  31
  32 using System;
  33 using System.Data;
  34 using System.Data.Common;
  35 using System.Data.SqlClient;
  36 #if NET_2_0
  37 using System.Data.Sql;
  38 #endif
  39 using System.Globalization;
  40 #if NET_2_0
  41 using System.Xml;
  42 #endif
  43
  44 using NUnit.Framework;
  45
  46 namespace MonoTests.System.Data.SqlClient
  47 {
  48         [TestFixture]
  49         [Category ("sqlserver")]
  50         public class SqlCommandTest
  51         {
  52                 SqlConnection conn;
  53                 SqlCommand cmd;
  54                 string connectionString = ConnectionManager.Singleton.ConnectionString;
  55
  56                 static readonly decimal SMALLMONEY_MAX = 214748.3647m;
  57                 static readonly decimal SMALLMONEY_MIN = -214748.3648m;
  58
  59                 [TearDown]
  60                 public void TearDown ()
  61                 {
  62                         if (cmd != null) {
  63                                 cmd.Dispose ();
  64                                 cmd = null;
  65                         }
  66
  67                         if (conn != null) {
  68                                 conn.Close ();
  69                                 conn = null;
  70                         }
  71                 }
  72
  73                 [Test] // ctor (String, SqlConnection, SqlTransaction)
  74                 public void Constructor4 ()
  75                 {
  76                         string cmdText = "select @@version";
  77
  78                         SqlTransaction trans = null;
  79                         SqlConnection connA = null;
  80                         SqlConnection connB = null;
  81
  82                         // transaction from same connection
  83                         try {
  84                                 connA = new SqlConnection (connectionString);
  85                                 connA.Open ();
  86
  87                                 trans = connA.BeginTransaction ();
  88                                 cmd = new SqlCommand (cmdText, connA, trans);
  89
  90                                 Assert.AreEqual (cmdText, cmd.CommandText, "#A1");
  91                                 Assert.AreEqual (30, cmd.CommandTimeout, "#A2");
  92                                 Assert.AreEqual (CommandType.Text, cmd.CommandType, "#A3");
  93                                 Assert.AreSame (connA, cmd.Connection, "#A4");
  94                                 Assert.IsNull (cmd.Container, "#A5");
  95                                 Assert.IsTrue (cmd.DesignTimeVisible, "#A6");
  96 #if NET_2_0
  97                                 Assert.IsNull (cmd.Notification, "#A7");
  98                                 Assert.IsTrue (cmd.NotificationAutoEnlist, "#A8");
  99 #endif
 100                                 Assert.IsNotNull (cmd.Parameters, "#A9");
 101                                 Assert.AreEqual (0, cmd.Parameters.Count, "#A10");
 102                                 Assert.IsNull (cmd.Site, "#A11");
 103                                 Assert.AreSame (trans, cmd.Transaction, "#A12");
 104                                 Assert.AreEqual (UpdateRowSource.Both, cmd.UpdatedRowSource, "#A13");
 105                         } finally {
 106                                 if (trans != null)
 107                                         trans.Dispose ();
 108                                 if (connA != null)
 109                                         connA.Close ();
 110                         }
 111
 112                         // transaction from other connection
 113                         try {
 114                                 connA = new SqlConnection (connectionString);
 115                                 connA.Open ();
 116                                 connB = new SqlConnection (connectionString);
 117                                 connB.Open ();
 118
 119                                 trans = connB.BeginTransaction ();
 120                                 cmd = new SqlCommand (cmdText, connA, trans);
 121
 122                                 Assert.AreEqual (cmdText, cmd.CommandText, "#B1");
 123                                 Assert.AreEqual (30, cmd.CommandTimeout, "#B2");
 124                                 Assert.AreEqual (CommandType.Text, cmd.CommandType, "#B3");
 125                                 Assert.AreSame (connA, cmd.Connection, "#B4");
 126                                 Assert.IsNull (cmd.Container, "#B5");
 127                                 Assert.IsTrue (cmd.DesignTimeVisible, "#B6");
 128 #if NET_2_0
 129                                 Assert.IsNull (cmd.Notification, "#B7");
 130                                 Assert.IsTrue (cmd.NotificationAutoEnlist, "#B8");
 131 #endif
 132                                 Assert.IsNotNull (cmd.Parameters, "#B9");
 133                                 Assert.AreEqual (0, cmd.Parameters.Count, "#B10");
 134                                 Assert.IsNull (cmd.Site, "#B11");
 135                                 Assert.AreSame (trans, cmd.Transaction, "#B12");
 136                                 Assert.AreEqual (UpdateRowSource.Both, cmd.UpdatedRowSource, "#B13");
 137                         } finally {
 138                                 if (trans != null)
 139                                         trans.Dispose ();
 140                                 if (connA != null)
 141                                         connA.Close ();
 142                         }
 143                 }
 144
 145                 [Test]
 146                 public void ExecuteScalar ()
 147                 {
 148                         conn = new SqlConnection (connectionString);
 149                         cmd = new SqlCommand ("" , conn);
 150                         cmd.CommandText = "Select count(*) from numeric_family where id<=4";
 151
 152                         // Check the Return value for a Correct Query
 153                         object result = 0;
 154                         conn.Open ();
 155                         result = cmd.ExecuteScalar ();
 156                         Assert.AreEqual (4, (int)result, "#A1 Query Result returned is incorrect");
 157
 158                         cmd.CommandText = "select id , type_bit from numeric_family order by id asc";
 159                         result = Convert.ToInt32 (cmd.ExecuteScalar ());
 160                         Assert.AreEqual (1, result,
 161                                 "#A2 ExecuteScalar Should return (1,1) the result set" );
 162
 163                         cmd.CommandText = "select id from numeric_family where id=-1";
 164                         result = cmd.ExecuteScalar ();
 165                         Assert.IsNull (result, "#A3 Null should be returned if result set is empty");
 166
 167                         // Check SqlException is thrown for Invalid Query
 168                         cmd.CommandText = "select count* from numeric_family";
 169                         try {
 170                                 result = cmd.ExecuteScalar ();
 171                                 Assert.Fail ("#B1");
 172                         } catch (SqlException ex) {
 173                                 // Incorrect syntax near the keyword 'from'
 174                                 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#B2");
 175                                 Assert.AreEqual ((byte) 15, ex.Class, "#B3");
 176                                 Assert.IsNull (ex.InnerException, "#B4");
 177                                 Assert.IsNotNull (ex.Message, "#B5");
 178                                 Assert.IsTrue (ex.Message.IndexOf ("'from'") != -1, "#B6");
 179                                 Assert.AreEqual (156, ex.Number, "#B7");
 180                                 Assert.AreEqual ((byte) 1, ex.State, "#B8");
 181                         }
 182
 183                         // Parameterized stored procedure calls
 184
 185                         int int_value = 20;
 186                         string string_value = "output value changed";
 187                         string return_value = "first column of first rowset";
 188
 189                         cmd.CommandText =
 190                                 "create procedure #tmp_executescalar_outparams "+
 191                                 " (@p1 int, @p2 int out, @p3 varchar(200) out) "+
 192                                 "as " +
 193                                 "select '" + return_value + "' as 'col1', @p1 as 'col2' "+
 194                                 "set @p2 = @p2 * 2 "+
 195                                 "set @p3 = N'" + string_value + "' "+
 196                                 "select 'second rowset' as 'col1', 2 as 'col2' "+
 197                                 "return 1";
 198
 199                         cmd.CommandType = CommandType.Text;
 200                         cmd.ExecuteNonQuery ();
 201
 202                         cmd.CommandText = "#tmp_executescalar_outparams";
 203                         cmd.CommandType = CommandType.StoredProcedure;
 204
 205                         SqlParameter p1 = new SqlParameter ();
 206                         p1.ParameterName = "@p1";
 207                         p1.Direction = ParameterDirection.Input;
 208                         p1.DbType = DbType.Int32;
 209                         p1.Value = int_value;
 210                         cmd.Parameters.Add (p1);
 211
 212                         SqlParameter p2 = new SqlParameter ();
 213                         p2.ParameterName = "@p2";
 214                         p2.Direction = ParameterDirection.InputOutput;
 215                         p2.DbType = DbType.Int32;
 216                         p2.Value = int_value;
 217                         cmd.Parameters.Add (p2);
 218
 219                         SqlParameter p3 = new SqlParameter ();
 220                         p3.ParameterName = "@p3";
 221                         p3.Direction = ParameterDirection.Output;
 222                         p3.DbType = DbType.String;
 223                         p3.Size = 200;
 224                         cmd.Parameters.Add (p3);
 225
 226                         result = cmd.ExecuteScalar ();
 227                         Assert.AreEqual (return_value, result, "#C1 ExecuteScalar Should return 'first column of first rowset'");
 228                         Assert.AreEqual (int_value * 2, p2.Value, "#C2 ExecuteScalar should fill the parameter collection with the outputted values");
 229                         Assert.AreEqual (string_value, p3.Value, "#C3 ExecuteScalar should fill the parameter collection with the outputted values");
 230
 231                         p3.Size = 0;
 232                         p3.Value = null;
 233                         try {
 234                                 cmd.ExecuteScalar ();
 235                                 Assert.Fail ("#D1 Query should throw System.InvalidOperationException due to size = 0 and value = null");
 236                         } catch (InvalidOperationException ex) {
 237                                 // String[2]: the Size property has an invalid
 238                                 // size of 0
 239                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#D2");
 240                                 Assert.IsNull (ex.InnerException, "#D3");
 241                                 Assert.IsNotNull (ex.Message, "#D4");
 242                         } finally {
 243                                 conn.Close ();
 244                         }
 245                 }
 246
 247                 [Test]
 248                 public void ExecuteScalar_Connection_PendingTransaction ()
 249                 {
 250                         conn = new SqlConnection (connectionString);
 251                         conn.Open ();
 252
 253                         using (SqlTransaction trans = conn.BeginTransaction ()) {
 254                                 cmd = new SqlCommand ("select @@version", conn);
 255
 256                                 try {
 257                                         cmd.ExecuteScalar ();
 258                                         Assert.Fail ("#1");
 259                                 } catch (InvalidOperationException ex) {
 260                                         // ExecuteScalar requires the command
 261                                         // to have a transaction object when the
 262                                         // connection assigned to the command is
 263                                         // in a pending local transaction.  The
 264                                         // Transaction property of the command
 265                                         // has not been initialized
 266                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
 267                                         Assert.IsNull (ex.InnerException, "#3");
 268                                         Assert.IsNotNull (ex.Message, "#4");
 269 #if NET_2_0
 270                                         Assert.IsTrue (ex.Message.IndexOf ("ExecuteScalar") != -1, "#5:" + ex.Message);
 271 #else
 272                                         Assert.IsTrue (ex.Message.IndexOf ("Execute") != -1, "#5:" + ex.Message);
 273 #endif
 274                                 }
 275                         }
 276                 }
 277
 278                 [Test]
 279                 public void ExecuteScalar_Query_Invalid ()
 280                 {
 281                         conn = new SqlConnection (connectionString);
 282                         conn.Open ();
 283
 284                         cmd = new SqlCommand ("InvalidQuery", conn);
 285                         try {
 286                                 cmd.ExecuteScalar ();
 287                                 Assert.Fail ("#1");
 288                         } catch (SqlException ex) {
 289                                 // Could not find stored procedure 'InvalidQuery'
 290                                 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
 291                                 Assert.AreEqual ((byte) 16, ex.Class, "#3");
 292                                 Assert.IsNull (ex.InnerException, "#4");
 293                                 Assert.IsNotNull (ex.Message, "#5");
 294                                 Assert.IsTrue (ex.Message.IndexOf ("'InvalidQuery'") != -1, "#6");
 295                                 Assert.AreEqual (2812, ex.Number, "#7");
 296                                 Assert.AreEqual ((byte) 62, ex.State, "#8");
 297                         }
 298                 }
 299
 300                 [Test]
 301                 public void ExecuteScalar_Transaction_NotAssociated ()
 302                 {
 303                         SqlTransaction trans = null;
 304                         SqlConnection connA = null;
 305                         SqlConnection connB = null;
 306
 307                         try {
 308                                 connA = new SqlConnection (connectionString);
 309                                 connA.Open ();
 310
 311                                 connB = new SqlConnection (connectionString);
 312                                 connB.Open ();
 313
 314                                 trans = connA.BeginTransaction ();
 315
 316                                 cmd = new SqlCommand ("select @@version", connB, trans);
 317
 318                                 try {
 319                                         cmd.ExecuteScalar ();
 320                                         Assert.Fail ("#A1");
 321                                 } catch (InvalidOperationException ex) {
 322                                         // The transaction object is not associated
 323                                         // with the connection object
 324                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
 325                                         Assert.IsNull (ex.InnerException, "#A3");
 326                                         Assert.IsNotNull (ex.Message, "#A4");
 327                                 } finally {
 328                                         cmd.Dispose ();
 329                                 }
 330
 331                                 cmd = new SqlCommand ("select @@version", connB);
 332                                 cmd.Transaction = trans;
 333
 334                                 try {
 335                                         cmd.ExecuteScalar ();
 336                                         Assert.Fail ("#B1");
 337                                 } catch (InvalidOperationException ex) {
 338                                         // The transaction object is not associated
 339                                         // with the connection object
 340                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
 341                                         Assert.IsNull (ex.InnerException, "#B3");
 342                                         Assert.IsNotNull (ex.Message, "#B4");
 343                                 } finally {
 344                                         cmd.Dispose ();
 345                                 }
 346                         } finally {
 347                                 if (trans != null)
 348                                         trans.Dispose ();
 349                                 if (connA != null)
 350                                         connA.Close ();
 351                                 if (connB != null)
 352                                         connB.Close ();
 353                         }
 354                 }
 355
 356                 [Test]
 357                 public void ExecuteScalar_Transaction_Only ()
 358                 {
 359                         SqlTransaction trans = null;
 360
 361                         conn = new SqlConnection (connectionString);
 362                         conn.Open ();
 363                         trans = conn.BeginTransaction ();
 364
 365                         cmd = new SqlCommand ("select @@version");
 366                         cmd.Transaction = trans;
 367
 368                         try {
 369                                 cmd.ExecuteScalar ();
 370                                 Assert.Fail ("#1");
 371                         } catch (InvalidOperationException ex) {
 372                                 // ExecuteScalar: Connection property has not
 373                                 // been initialized
 374                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
 375                                 Assert.IsNull (ex.InnerException, "#3");
 376                                 Assert.IsNotNull (ex.Message, "#4");
 377 #if NET_2_0
 378                                 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar:"), "#5");
 379 #else
 380                                 Assert.IsTrue (ex.Message.StartsWith ("ExecuteReader:"), "#5");
 381 #endif
 382                         } finally {
 383                                 trans.Dispose ();
 384                         }
 385                 }
 386
 387                 [Test]
 388                 public void ExecuteNonQuery ()
 389                 {
 390                         conn = new SqlConnection (connectionString);
 391                         conn.Open ();
 392
 393                         SqlTransaction trans = conn.BeginTransaction ();
 394
 395                         cmd = conn.CreateCommand ();
 396                         cmd.Transaction = trans;
 397
 398                         int result = 0;
 399
 400                         try {
 401                                 cmd.CommandText = "Select id from numeric_family where id=1";
 402                                 result = cmd.ExecuteNonQuery ();
 403                                 Assert.AreEqual (-1, result, "#A1");
 404
 405                                 cmd.CommandText = "Insert into numeric_family (id,type_int) values (100,200)";
 406                                 result = cmd.ExecuteNonQuery ();
 407                                 Assert.AreEqual (1, result, "#A2 One row shud be inserted");
 408
 409                                 cmd.CommandText = "Update numeric_family set type_int=300 where id=100";
 410                                 result = cmd.ExecuteNonQuery ();
 411                                 Assert.AreEqual (1, result, "#A3 One row shud be updated");
 412
 413                                 // Test Batch Commands
 414                                 cmd.CommandText = "Select id from numeric_family where id=1;";
 415                                 cmd.CommandText += "update numeric_family set type_int=10 where id=1000";
 416                                 cmd.CommandText += "update numeric_family set type_int=10 where id=100";
 417                                 result = cmd.ExecuteNonQuery ();
 418                                 Assert.AreEqual (1, result, "#A4 One row shud be updated");
 419
 420                                 cmd.CommandText = "Delete from numeric_family where id=100";
 421                                 result = cmd.ExecuteNonQuery ();
 422                                 Assert.AreEqual (1, result, "#A5 One row shud be deleted");
 423                         }finally {
 424                                 trans.Dispose ();
 425                         }
 426
 427                         // Parameterized stored procedure calls
 428
 429                         int int_value = 20;
 430                         string string_value = "output value changed";
 431
 432                         cmd.CommandText =
 433                                 "create procedure #tmp_executescalar_outparams " +
 434                                 " (@p1 int, @p2 int out, @p3 varchar(200) out) " +
 435                                 "as " +
 436                                 "select 'test' as 'col1', @p1 as 'col2' " +
 437                                 "set @p2 = @p2 * 2 " +
 438                                 "set @p3 = N'" + string_value + "' " +
 439                                 "select 'second rowset' as 'col1', 2 as 'col2' " +
 440                                 "return 1";
 441
 442                         cmd.CommandType = CommandType.Text;
 443                         cmd.ExecuteNonQuery ();
 444
 445                         cmd.CommandText = "#tmp_executescalar_outparams";
 446                         cmd.CommandType = CommandType.StoredProcedure;
 447
 448                         SqlParameter p1 = new SqlParameter ();
 449                         p1.ParameterName = "@p1";
 450                         p1.Direction = ParameterDirection.Input;
 451                         p1.DbType = DbType.Int32;
 452                         p1.Value = int_value;
 453                         cmd.Parameters.Add (p1);
 454
 455                         SqlParameter p2 = new SqlParameter ();
 456                         p2.ParameterName = "@p2";
 457                         p2.Direction = ParameterDirection.InputOutput;
 458                         p2.DbType = DbType.Int32;
 459                         p2.Value = int_value;
 460                         cmd.Parameters.Add (p2);
 461
 462                         SqlParameter p3 = new SqlParameter ();
 463                         p3.ParameterName = "@p3";
 464                         p3.Direction = ParameterDirection.Output;
 465                         p3.DbType = DbType.String;
 466                         p3.Size = 200;
 467                         cmd.Parameters.Add (p3);
 468
 469                         cmd.ExecuteNonQuery ();
 470                         Assert.AreEqual (int_value * 2, p2.Value, "#B1 ExecuteNonQuery should fill the parameter collection with the outputted values");
 471                         Assert.AreEqual (string_value, p3.Value, "#B2 ExecuteNonQuery should fill the parameter collection with the outputted values");
 472                 }
 473
 474                 [Test]
 475                 public void ExecuteNonQuery_Connection_PendingTransaction ()
 476                 {
 477                         conn = new SqlConnection (connectionString);
 478                         conn.Open ();
 479
 480                         using (SqlTransaction trans = conn.BeginTransaction ()) {
 481                                 cmd = new SqlCommand ("select @@version", conn);
 482
 483                                 try {
 484                                         cmd.ExecuteNonQuery ();
 485                                         Assert.Fail ("#1");
 486                                 } catch (InvalidOperationException ex) {
 487                                         // ExecuteNonQuery requires the command
 488                                         // to have a transaction object when the
 489                                         // connection assigned to the command is
 490                                         // in a pending local transaction.  The
 491                                         // Transaction property of the command
 492                                         // has not been initialized
 493                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
 494                                         Assert.IsNull (ex.InnerException, "#3");
 495                                         Assert.IsNotNull (ex.Message, "#4");
 496 #if NET_2_0
 497                                         Assert.IsTrue (ex.Message.IndexOf ("ExecuteNonQuery") != -1, "#5:" + ex.Message);
 498 #else
 499                                         Assert.IsTrue (ex.Message.IndexOf ("Execute") != -1, "#5:" + ex.Message);
 500 #endif
 501                                 }
 502                         }
 503                 }
 504
 505                 [Test]
 506                 public void ExecuteNonQuery_Query_Invalid ()
 507                 {
 508                         conn = new SqlConnection (connectionString);
 509                         conn.Open ();
 510                         cmd = new SqlCommand ("select id1 from numeric_family", conn);
 511
 512                         try {
 513                                 cmd.ExecuteNonQuery ();
 514                                 Assert.Fail ("#1");
 515                         } catch (SqlException ex) {
 516                                 // Invalid column name 'id1'
 517                                 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
 518                                 Assert.AreEqual ((byte) 16, ex.Class, "#3");
 519                                 Assert.IsNull (ex.InnerException, "#4");
 520                                 Assert.IsNotNull (ex.Message, "#5");
 521                                 Assert.IsTrue (ex.Message.IndexOf ("'id1'") != -1, "#6");
 522                                 Assert.AreEqual (207, ex.Number, "#7");
 523                                 Assert.AreEqual ((byte) 1, ex.State, "#8");
 524                         } finally {
 525                                 conn.Close ();
 526                         }
 527                 }
 528
 529                 [Test]
 530                 public void ExecuteNonQuery_Transaction_NotAssociated ()
 531                 {
 532                         SqlTransaction trans = null;
 533                         SqlConnection connA = null;
 534                         SqlConnection connB = null;
 535
 536                         try {
 537                                 connA = new SqlConnection (connectionString);
 538                                 connA.Open ();
 539
 540                                 connB = new SqlConnection (connectionString);
 541                                 connB.Open ();
 542
 543                                 trans = connA.BeginTransaction ();
 544
 545                                 cmd = new SqlCommand ("select @@version", connB, trans);
 546
 547                                 try {
 548                                         cmd.ExecuteNonQuery ();
 549                                         Assert.Fail ("#A1");
 550                                 } catch (InvalidOperationException ex) {
 551                                         // The transaction object is not associated
 552                                         // with the connection object
 553                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
 554                                         Assert.IsNull (ex.InnerException, "#A3");
 555                                         Assert.IsNotNull (ex.Message, "#A4");
 556                                 } finally {
 557                                         cmd.Dispose ();
 558                                 }
 559
 560                                 cmd = new SqlCommand ("select @@version", connB);
 561                                 cmd.Transaction = trans;
 562
 563                                 try {
 564                                         cmd.ExecuteNonQuery ();
 565                                         Assert.Fail ("#B1");
 566                                 } catch (InvalidOperationException ex) {
 567                                         // The transaction object is not associated
 568                                         // with the connection object
 569                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
 570                                         Assert.IsNull (ex.InnerException, "#B3");
 571                                         Assert.IsNotNull (ex.Message, "#B4");
 572                                 } finally {
 573                                         cmd.Dispose ();
 574                                 }
 575                         } finally {
 576                                 if (trans != null)
 577                                         trans.Dispose ();
 578                                 if (connA != null)
 579                                         connA.Close ();
 580                                 if (connB != null)
 581                                         connB.Close ();
 582                         }
 583                 }
 584
 585                 [Test]
 586                 public void ExecuteNonQuery_Transaction_Only ()
 587                 {
 588                         conn = new SqlConnection (connectionString);
 589                         conn.Open ();
 590
 591                         SqlTransaction trans = conn.BeginTransaction ();
 592
 593                         cmd = new SqlCommand ("select @@version");
 594                         cmd.Transaction = trans;
 595
 596                         try {
 597                                 cmd.ExecuteNonQuery ();
 598                                 Assert.Fail ("#1");
 599                         } catch (InvalidOperationException ex) {
 600                                 // ExecuteNonQuery: Connection property has not
 601                                 // been initialized
 602                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
 603                                 Assert.IsNull (ex.InnerException, "#3");
 604                                 Assert.IsNotNull (ex.Message, "#4");
 605                                 Assert.IsTrue (ex.Message.StartsWith ("ExecuteNonQuery:"), "#5");
 606                         } finally {
 607                                 trans.Dispose ();
 608                         }
 609                 }
 610
 611                 [Test] // bug #412569
 612                 public void ExecuteReader ()
 613                 {
 614                         // Test for command behaviors
 615                         DataTable schemaTable = null;
 616                         SqlDataReader reader = null;
 617
 618                         conn = new SqlConnection (connectionString);
 619                         conn.Open ();
 620                         cmd = new SqlCommand ("", conn);
 621                         cmd.CommandText = "Select id from numeric_family where id <=4 order by id asc;";
 622                         cmd.CommandText += "Select type_bit from numeric_family where id <=4 order by id asc";
 623
 624                         // Test for default command behavior
 625                         reader = cmd.ExecuteReader ();
 626                         int rows = 0;
 627                         int results = 0;
 628                         do {
 629                                 while (reader.Read ())
 630                                         rows++;
 631                                 Assert.AreEqual (4, rows, "#1 Multiple rows shud be returned");
 632                                 results++;
 633                                 rows = 0;
 634                         } while (reader.NextResult ());
 635                         Assert.AreEqual (2, results, "#2 Multiple result sets shud be returned");
 636                         reader.Close ();
 637
 638                         // Test if closing reader, closes the connection
 639                         reader = cmd.ExecuteReader (CommandBehavior.CloseConnection);
 640                         reader.Close ();
 641                         Assert.AreEqual (ConnectionState.Closed, conn.State,
 642                                 "#3 Command Behavior is not followed");
 643                         conn.Open ();
 644
 645                         // Test if row info and primary Key info is returned
 646                         reader = cmd.ExecuteReader (CommandBehavior.KeyInfo);
 647                         schemaTable = reader.GetSchemaTable ();
 648                         Assert.IsTrue (reader.HasRows, "#4 Data Rows shud also be returned");
 649                         Assert.IsTrue ((bool) schemaTable.Rows [0] ["IsKey"],
 650                                 "#5 Primary Key info shud be returned");
 651                         reader.Close ();
 652
 653                         // Test only column information is returned
 654                         reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly);
 655                         schemaTable = reader.GetSchemaTable ();
 656                         Assert.IsFalse (reader.HasRows, "#6 row data shud not be returned");
 657                         Assert.AreEqual (DBNull.Value, schemaTable.Rows [0] ["IsKey"],
 658                                 "#7 Primary Key info shud not be returned");
 659                         Assert.AreEqual ("id", schemaTable.Rows [0] ["ColumnName"],
 660                                 "#8 Schema Data is Incorrect");
 661                         reader.Close ();
 662
 663                         // Test only one result set (first) is returned
 664                         reader = cmd.ExecuteReader (CommandBehavior.SingleResult);
 665                         schemaTable = reader.GetSchemaTable ();
 666                         Assert.IsFalse (reader.NextResult (),
 667                                 "#9 Only one result set shud be returned");
 668                         Assert.AreEqual ("id", schemaTable.Rows [0] ["ColumnName"],
 669                                 "#10 The result set returned shud be the first result set");
 670                         reader.Close ();
 671
 672                         // Test only one row is returned for all result sets
 673                         // msdotnet doesnt work correctly.. returns only one result set
 674                         reader = cmd.ExecuteReader (CommandBehavior.SingleRow);
 675                         rows = 0;
 676                         results = 0;
 677                         do {
 678                                 while (reader.Read ())
 679                                         rows++;
 680                                 Assert.AreEqual (1, rows, "#11 Only one row shud be returned");
 681                                 results++;
 682                                 rows = 0;
 683                         } while (reader.NextResult ());
 684
 685                         // LAMESPEC:
 686                         // https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=357085
 687                         Assert.AreEqual (1, results, "#12 Multiple result sets shud be returned");
 688                         reader.Close ();
 689                 }
 690
 691                 [Test]
 692                 public void ExecuteReader_Connection_PendingTransaction ()
 693                 {
 694                         conn = new SqlConnection (connectionString);
 695                         conn.Open ();
 696
 697                         using (SqlTransaction trans = conn.BeginTransaction ()) {
 698                                 cmd = new SqlCommand ("select @@version", conn);
 699
 700                                 try {
 701                                         cmd.ExecuteReader ();
 702                                         Assert.Fail ("#1");
 703                                 } catch (InvalidOperationException ex) {
 704                                         // ExecuteReader requires the command
 705                                         // to have a transaction object when the
 706                                         // connection assigned to the command is
 707                                         // in a pending local transaction.  The
 708                                         // Transaction property of the command
 709                                         // has not been initialized
 710                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
 711                                         Assert.IsNull (ex.InnerException, "#3");
 712                                         Assert.IsNotNull (ex.Message, "#4");
 713 #if NET_2_0
 714                                         Assert.IsTrue (ex.Message.IndexOf ("ExecuteReader") != -1, "#5:" + ex.Message);
 715 #else
 716                                         Assert.IsTrue (ex.Message.IndexOf ("Execute") != -1, "#5:" + ex.Message);
 717 #endif
 718                                 }
 719                         }
 720                 }
 721
 722                 [Test]
 723                 public void ExecuteReader_Query_Invalid ()
 724                 {
 725                         conn = new SqlConnection (connectionString);
 726                         conn.Open ();
 727
 728                         cmd = new SqlCommand ("InvalidQuery", conn);
 729                         try {
 730                                 cmd.ExecuteReader ();
 731                                 Assert.Fail ("#1");
 732                         } catch (SqlException ex) {
 733                                 // Could not find stored procedure 'InvalidQuery'
 734                                 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
 735                                 Assert.AreEqual ((byte) 16, ex.Class, "#3");
 736                                 Assert.IsNull (ex.InnerException, "#4");
 737                                 Assert.IsNotNull (ex.Message, "#5");
 738                                 Assert.IsTrue (ex.Message.IndexOf ("'InvalidQuery'") != -1, "#6");
 739                                 Assert.AreEqual (2812, ex.Number, "#7");
 740                                 Assert.AreEqual ((byte) 62, ex.State, "#8");
 741                         }
 742                 }
 743
 744                 [Test]
 745                 public void ExecuteReader_Transaction_NotAssociated ()
 746                 {
 747                         SqlTransaction trans = null;
 748                         SqlConnection connA = null;
 749                         SqlConnection connB = null;
 750
 751                         try {
 752                                 connA = new SqlConnection (connectionString);
 753                                 connA.Open ();
 754
 755                                 connB = new SqlConnection (connectionString);
 756                                 connB.Open ();
 757
 758                                 trans = connA.BeginTransaction ();
 759
 760                                 cmd = new SqlCommand ("select @@version", connB, trans);
 761
 762                                 try {
 763                                         cmd.ExecuteReader ();
 764                                         Assert.Fail ("#A1");
 765                                 } catch (InvalidOperationException ex) {
 766                                         // The transaction object is not associated
 767                                         // with the connection object
 768                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
 769                                         Assert.IsNull (ex.InnerException, "#A3");
 770                                         Assert.IsNotNull (ex.Message, "#A4");
 771                                 } finally {
 772                                         cmd.Dispose ();
 773                                 }
 774
 775                                 cmd = new SqlCommand ("select @@version", connB);
 776                                 cmd.Transaction = trans;
 777
 778                                 try {
 779                                         cmd.ExecuteReader ();
 780                                         Assert.Fail ("#B1");
 781                                 } catch (InvalidOperationException ex) {
 782                                         // The transaction object is not associated
 783                                         // with the connection object
 784                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
 785                                         Assert.IsNull (ex.InnerException, "#B3");
 786                                         Assert.IsNotNull (ex.Message, "#B4");
 787                                 } finally {
 788                                         cmd.Dispose ();
 789                                 }
 790                         } finally {
 791                                 if (trans != null)
 792                                         trans.Dispose ();
 793                                 if (connA != null)
 794                                         connA.Close ();
 795                                 if (connB != null)
 796                                         connB.Close ();
 797                         }
 798                 }
 799
 800                 [Test]
 801                 public void ExecuteReader_Transaction_Only ()
 802                 {
 803                         SqlTransaction trans = null;
 804
 805                         conn = new SqlConnection (connectionString);
 806                         conn.Open ();
 807                         trans = conn.BeginTransaction ();
 808
 809                         cmd = new SqlCommand ("select @@version");
 810                         cmd.Transaction = trans;
 811
 812                         try {
 813                                 cmd.ExecuteReader ();
 814                                 Assert.Fail ("#1");
 815                         } catch (InvalidOperationException ex) {
 816                                 // ExecuteReader: Connection property has not
 817                                 // been initialized
 818                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
 819                                 Assert.IsNull (ex.InnerException, "#3");
 820                                 Assert.IsNotNull (ex.Message, "#4");
 821                                 Assert.IsTrue (ex.Message.StartsWith ("ExecuteReader:"), "#5");
 822                         } finally {
 823                                 trans.Dispose ();
 824                         }
 825                 }
 826
 827
 828                 [Test]
 829                 public void PrepareTest_CheckValidStatement ()
 830                 {
 831                         cmd = new SqlCommand ();
 832                         conn = new SqlConnection (connectionString);
 833                         conn.Open ();
 834
 835                         cmd.CommandText = "Select id from numeric_family where id=@ID";
 836                         cmd.Connection = conn;
 837
 838                         // Test if Parameters are correctly populated
 839                         cmd.Parameters.Clear ();
 840                         cmd.Parameters.Add ("@ID", SqlDbType.TinyInt);
 841                         cmd.Parameters["@ID"].Value = 2;
 842                         cmd.Prepare ();
 843                         Assert.AreEqual (2, cmd.ExecuteScalar (), "#3 Prepared Stmt not working");
 844
 845                         cmd.Parameters[0].Value = 3;
 846                         Assert.AreEqual (3, cmd.ExecuteScalar (), "#4 Prep Stmt not working");
 847                         conn.Close ();
 848                 }
 849
 850                 [Test]
 851                 public void Prepare ()
 852                 {
 853                         cmd = new SqlCommand ();
 854                         conn = new SqlConnection (connectionString);
 855                         conn.Open ();
 856
 857                         cmd.CommandText = "Select id from numeric_family where id=@ID";
 858                         cmd.Connection = conn;
 859
 860                         // Test InvalidOperation Exception is thrown if Parameter Type
 861                         // is not explicitly set
 862 #if NET_2_0
 863                         cmd.Parameters.AddWithValue ("@ID", 2);
 864 #else
 865                         cmd.Parameters.Add ("@ID", 2);
 866 #endif
 867                         try {
 868                                 cmd.Prepare ();
 869                                 Assert.Fail ("#A1");
 870                         } catch (InvalidOperationException ex) {
 871                                 // SqlCommand.Prepare method requires all parameters
 872                                 // to have an explicitly set type
 873                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
 874                                 Assert.IsNull (ex.InnerException, "#A3");
 875                                 Assert.IsNotNull (ex.Message, "#A4");
 876                         }
 877
 878                         // Test Exception is thrown for variable size data  if precision/scale
 879                         // is not set
 880                         cmd.CommandText = "select type_varchar from string_family where type_varchar=@p1";
 881                         cmd.Parameters.Clear ();
 882                         cmd.Parameters.Add ("@p1", SqlDbType.VarChar);
 883                         cmd.Parameters["@p1"].Value = "afasasadadada";
 884                         try {
 885                                 cmd.Prepare ();
 886                                 Assert.Fail ("#B1");
 887                         } catch (InvalidOperationException ex) {
 888                                 // SqlCommand.Prepare method requires all variable
 889                                 // length parameters to have an explicitly set
 890                                 // non-zero Size
 891                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
 892                                 Assert.IsNull (ex.InnerException, "#B3");
 893                                 Assert.IsNotNull (ex.Message, "#B4");
 894                         }
 895
 896                         // Test Exception is not thrown for Stored Procs
 897                         cmd.CommandType = CommandType.StoredProcedure;
 898                         cmd.CommandText = "ABFSDSFSF";
 899                         cmd.Prepare ();
 900
 901                         cmd.CommandType = CommandType.Text;
 902                         conn.Close ();
 903                 }
 904
 905                 [Test]
 906                 public void Prepare_Connection_PendingTransaction ()
 907                 {
 908                         conn = new SqlConnection (connectionString);
 909                         conn.Open ();
 910
 911                         using (SqlTransaction trans = conn.BeginTransaction ()) {
 912                                 // Text, without parameters
 913                                 cmd = new SqlCommand ("select * from whatever where name=?", conn);
 914                                 cmd.Prepare ();
 915
 916                                 // Text, with parameters
 917                                 cmd = new SqlCommand ("select * from whatever where name=?", conn);
 918                                 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
 919                                 try {
 920                                         cmd.Prepare ();
 921                                         Assert.Fail ("#1");
 922                                 } catch (InvalidOperationException ex) {
 923                                         // Prepare requires the command to have a
 924                                         // transaction object when the connection
 925                                         // assigned to the command is in a pending
 926                                         // local transaction.  The Transaction
 927                                         // property of the command has not been
 928                                         // initialized
 929                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
 930                                         Assert.IsNull (ex.InnerException, "#3");
 931                                         Assert.IsNotNull (ex.Message, "#4");
 932 #if NET_2_0
 933                                         Assert.IsTrue (ex.Message.IndexOf ("Prepare") != -1, "#5:" + ex.Message);
 934 #else
 935                                         Assert.IsTrue (ex.Message.IndexOf ("Execute") != -1, "#5:" + ex.Message);
 936 #endif
 937                                 }
 938
 939                                 // Text, parameters cleared
 940                                 cmd = new SqlCommand ("select * from whatever where name=?", conn);
 941                                 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
 942                                 cmd.Parameters.Clear ();
 943                                 cmd.Prepare ();
 944
 945                                 // StoredProcedure, without parameters
 946                                 cmd = new SqlCommand ("FindCustomer", conn);
 947                                 cmd.CommandType = CommandType.StoredProcedure;
 948                                 cmd.Prepare ();
 949
 950                                 // StoredProcedure, with parameters
 951                                 cmd = new SqlCommand ("FindCustomer", conn);
 952                                 cmd.CommandType = CommandType.StoredProcedure;
 953                                 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
 954                                 cmd.Prepare ();
 955                         }
 956                 }
 957
 958                 [Test]
 959                 public void Prepare_Transaction_NotAssociated ()
 960                 {
 961                         SqlTransaction trans = null;
 962                         SqlConnection connA = null;
 963                         SqlConnection connB = null;
 964
 965                         try {
 966                                 connA = new SqlConnection (connectionString);
 967                                 connA.Open ();
 968
 969                                 connB = new SqlConnection (connectionString);
 970                                 connB.Open ();
 971
 972                                 trans = connA.BeginTransaction ();
 973
 974                                 // Text, without parameters
 975                                 cmd = new SqlCommand ("select @@version", connB, trans);
 976                                 cmd.Transaction = trans;
 977                                 cmd.Prepare ();
 978
 979                                 // Text, with parameters
 980                                 cmd = new SqlCommand ("select @@version", connB, trans);
 981                                 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
 982                                 try {
 983                                         cmd.Prepare ();
 984                                         Assert.Fail ("#1");
 985                                 } catch (InvalidOperationException ex) {
 986                                         // The transaction is either not associated
 987                                         // with the current connection or has been
 988                                         // completed
 989                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
 990                                         Assert.IsNull (ex.InnerException, "#3");
 991                                         Assert.IsNotNull (ex.Message, "#4");
 992                                 }
 993
 994                                 // Text, parameters cleared
 995                                 cmd = new SqlCommand ("select @@version", connB, trans);
 996                                 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
 997                                 cmd.Parameters.Clear ();
 998                                 cmd.Prepare ();
 999
1000                                 // StoredProcedure, without parameters
1001                                 cmd = new SqlCommand ("FindCustomer", connB, trans);
1002                                 cmd.CommandType = CommandType.StoredProcedure;
1003                                 cmd.Prepare ();
1004
1005                                 // StoredProcedure, with parameters
1006                                 cmd = new SqlCommand ("FindCustomer", connB, trans);
1007                                 cmd.CommandType = CommandType.StoredProcedure;
1008                                 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1009                                 cmd.Prepare ();
1010                         } finally {
1011                                 if (trans != null)
1012                                         trans.Dispose ();
1013                                 if (connA != null)
1014                                         connA.Close ();
1015                                 if (connB != null)
1016                                         connB.Close ();
1017                         }
1018                 }
1019
1020                 [Test]
1021                 public void Prepare_Transaction_Only ()
1022                 {
1023                         SqlTransaction trans = null;
1024
1025                         conn = new SqlConnection (connectionString);
1026                         conn.Open ();
1027                         trans = conn.BeginTransaction ();
1028
1029                         // Text, without parameters
1030                         cmd = new SqlCommand ("select count(*) from whatever");
1031                         cmd.Transaction = trans;
1032 #if NET_2_0
1033                         try {
1034                                 cmd.Prepare ();
1035                                 Assert.Fail ("#A1");
1036                         } catch (NullReferenceException) {
1037                         }
1038 #else
1039                         cmd.Prepare ();
1040 #endif
1041
1042                         // Text, with parameters
1043                         cmd = new SqlCommand ("select count(*) from whatever");
1044                         cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1045                         cmd.Transaction = trans;
1046                         try {
1047                                 cmd.Prepare ();
1048                                 Assert.Fail ("#B1");
1049 #if NET_2_0
1050                         } catch (NullReferenceException) {
1051                         }
1052 #else
1053                         } catch (InvalidOperationException ex) {
1054                                 // Prepare: Connection property has not been
1055                                 // initialized
1056                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
1057                                 Assert.IsNull (ex.InnerException, "#B3");
1058                                 Assert.IsNotNull (ex.Message, "#B4");
1059                                 Assert.IsTrue (ex.Message.StartsWith ("Prepare:"), "#B5");
1060                         }
1061 #endif
1062
1063                         // Text, parameters cleared
1064                         cmd = new SqlCommand ("select count(*) from whatever");
1065                         cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1066                         cmd.Parameters.Clear ();
1067                         cmd.Transaction = trans;
1068 #if NET_2_0
1069                         try {
1070                                 cmd.Prepare ();
1071                                 Assert.Fail ("#C1");
1072                         } catch (NullReferenceException) {
1073                         }
1074 #else
1075                         cmd.Prepare ();
1076 #endif
1077
1078                         // StoredProcedure, without parameters
1079                         cmd = new SqlCommand ("FindCustomer");
1080                         cmd.CommandType = CommandType.StoredProcedure;
1081                         cmd.Transaction = trans;
1082 #if NET_2_0
1083                         try {
1084                                 cmd.Prepare ();
1085                                 Assert.Fail ("#D1");
1086                         } catch (NullReferenceException) {
1087                         }
1088 #else
1089                         cmd.Prepare ();
1090 #endif
1091
1092                         // StoredProcedure, with parameters
1093                         cmd = new SqlCommand ("FindCustomer");
1094                         cmd.CommandType = CommandType.StoredProcedure;
1095                         cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1096                         cmd.Transaction = trans;
1097 #if NET_2_0
1098                         try {
1099                                 cmd.Prepare ();
1100                                 Assert.Fail ("#E1");
1101                         } catch (NullReferenceException) {
1102                         }
1103 #else
1104                         cmd.Prepare ();
1105 #endif
1106                 }
1107
1108                 [Test] // bug #412576
1109                 public void Connection ()
1110                 {
1111                         SqlConnection connA = null;
1112                         SqlConnection connB = null;
1113                         SqlTransaction trans = null;
1114
1115                         try {
1116                                 connA = new SqlConnection (connectionString);
1117                                 connA.Open ();
1118
1119                                 connB = new SqlConnection (connectionString);
1120                                 connB.Open ();
1121
1122                                 cmd = connA.CreateCommand ();
1123                                 cmd.Connection = connB;
1124                                 Assert.AreSame (connB, cmd.Connection, "#A1");
1125                                 Assert.IsNull (cmd.Transaction, "#A2");
1126                                 cmd.Dispose ();
1127
1128                                 trans = connA.BeginTransaction ();
1129                                 cmd = new SqlCommand ("select @@version", connA, trans);
1130                                 cmd.Connection = connB;
1131                                 Assert.AreSame (connB, cmd.Connection, "#B1");
1132                                 Assert.AreSame (trans, cmd.Transaction, "#B2");
1133                                 trans.Dispose ();
1134
1135                                 trans = connA.BeginTransaction ();
1136                                 cmd = new SqlCommand ("select @@version", connA, trans);
1137                                 trans.Rollback ();
1138                                 Assert.AreSame (connA, cmd.Connection, "#C1");
1139                                 Assert.IsNull (cmd.Transaction, "#C2");
1140                                 cmd.Connection = connB;
1141                                 Assert.AreSame (connB, cmd.Connection, "#C3");
1142                                 Assert.IsNull (cmd.Transaction, "#C4");
1143
1144                                 trans = connA.BeginTransaction ();
1145                                 cmd = new SqlCommand ("select @@version", connA, trans);
1146                                 cmd.Connection = null;
1147                                 Assert.IsNull (cmd.Connection, "#D1");
1148                                 Assert.AreSame (trans, cmd.Transaction, "#D2");
1149                         }finally {
1150                                 if (trans != null)
1151                                         trans.Dispose ();
1152                                 if (connA != null)
1153                                         connA.Close ();
1154                                 if (connB != null)
1155                                         connB.Close ();
1156                         }
1157                 }
1158
1159                 [Test]
1160                 public void Connection_Reader_Open ()
1161                 {
1162                         SqlConnection connA = null;
1163                         SqlConnection connB = null;
1164                         SqlTransaction trans = null;
1165
1166                         try {
1167                                 connA = new SqlConnection (connectionString);
1168                                 connA.Open ();
1169
1170                                 connB = new SqlConnection (connectionString);
1171                                 connB.Open ();
1172
1173                                 trans = connA.BeginTransaction ();
1174                                 SqlCommand cmdA = new SqlCommand ("select @@version", connA, trans);
1175
1176                                 SqlCommand cmdB = new SqlCommand ("select @@version", connA, trans);
1177                                 using (SqlDataReader reader = cmdB.ExecuteReader ()) {
1178 #if NET_2_0
1179                                         cmdA.Connection = connA;
1180                                         Assert.AreSame (connA, cmdA.Connection, "#A1");
1181                                         Assert.AreSame (trans, cmdA.Transaction, "#A2");
1182 #else
1183                                         try {
1184                                                 cmdA.Connection = connA;
1185                                                 Assert.Fail ("#A1");
1186                                         } catch (InvalidOperationException ex) {
1187                                                 // The SqlCommand is currently busy
1188                                                 // Open, Fetching
1189                                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
1190                                                 Assert.IsNull (ex.InnerException, "#A3");
1191                                                 Assert.IsNotNull (ex.Message, "#A4");
1192
1193                                                 Assert.AreSame (connA, cmdA.Connection, "#A5");
1194                                                 Assert.AreSame (trans, cmdA.Transaction, "#A6");
1195                                         }
1196 #endif
1197
1198 #if NET_2_0
1199                                         cmdA.Connection = connB;
1200                                         Assert.AreSame (connB, cmdA.Connection, "#B1");
1201                                         Assert.AreSame (trans, cmdA.Transaction, "#B2");
1202 #else
1203                                         try {
1204                                                 cmdA.Connection = connB;
1205                                                 Assert.Fail ("#B1");
1206                                         } catch (InvalidOperationException ex) {
1207                                                 // The SqlCommand is currently busy
1208                                                 // Open, Fetching
1209                                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
1210                                                 Assert.IsNull (ex.InnerException, "#B3");
1211                                                 Assert.IsNotNull (ex.Message, "#B4");
1212
1213                                                 Assert.AreSame (connA, cmdA.Connection, "#B5");
1214                                                 Assert.AreSame (trans, cmdA.Transaction, "#B6");
1215                                         }
1216 #endif
1217
1218 #if NET_2_0
1219                                         cmdA.Connection = null;
1220                                         Assert.IsNull (cmdA.Connection, "#C1");
1221                                         Assert.AreSame (trans, cmdA.Transaction, "#C2");
1222 #else
1223                                         try {
1224                                                 cmdA.Connection = null;
1225                                                 Assert.Fail ("#C1");
1226                                         } catch (InvalidOperationException ex) {
1227                                                 // The SqlCommand is currently busy
1228                                                 // Open, Fetching
1229                                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#C2");
1230                                                 Assert.IsNull (ex.InnerException, "#C3");
1231                                                 Assert.IsNotNull (ex.Message, "#C4");
1232
1233                                                 Assert.AreSame (connA, cmdA.Connection, "#C5");
1234                                                 Assert.AreSame (trans, cmdA.Transaction, "#C6");
1235                                         }
1236 #endif
1237                                 }
1238                         } finally {
1239                                 if (trans != null)
1240                                         trans.Dispose ();
1241                                 if (connA != null)
1242                                         connA.Close ();
1243                                 if (connB != null)
1244                                         connB.Close ();
1245                         }
1246                 }
1247
1248                 [Test]
1249                 public void Transaction ()
1250                 {
1251                         SqlConnection connA = null;
1252                         SqlConnection connB = null;
1253
1254                         SqlTransaction transA = null;
1255                         SqlTransaction transB = null;
1256
1257                         try {
1258                                 connA = new SqlConnection (connectionString);
1259                                 connA.Open ();
1260
1261                                 connB = new SqlConnection (connectionString);
1262                                 connB.Open ();
1263
1264                                 transA = connA.BeginTransaction ();
1265                                 transB = connB.BeginTransaction ();
1266
1267                                 SqlCommand cmd = new SqlCommand ("select @@version", connA, transA);
1268                                 cmd.Transaction = transA;
1269                                 Assert.AreSame (connA, cmd.Connection, "#A1");
1270                                 Assert.AreSame (transA, cmd.Transaction, "#A2");
1271                                 cmd.Transaction = transB;
1272                                 Assert.AreSame (connA, cmd.Connection, "#B1");
1273                                 Assert.AreSame (transB, cmd.Transaction, "#B2");
1274                                 cmd.Transaction = null;
1275                                 Assert.AreSame (connA, cmd.Connection, "#C1");
1276                                 Assert.IsNull (cmd.Transaction, "#C2");
1277                         } finally {
1278                                 if (transA != null)
1279                                         transA.Dispose ();
1280                                 if (transB != null)
1281                                         transA.Dispose ();
1282                                 if (connA != null)
1283                                         connA.Close ();
1284                                 if (connB != null)
1285                                         connB.Close ();
1286                         }
1287                 }
1288
1289                 [Test] // bug #412579
1290                 public void Transaction_Reader_Open ()
1291                 {
1292                         SqlConnection connA = null;
1293                         SqlConnection connB = null;
1294
1295                         SqlTransaction transA = null;
1296                         SqlTransaction transB = null;
1297
1298                         try {
1299                                 connA = new SqlConnection (connectionString);
1300                                 connA.Open ();
1301
1302                                 connB = new SqlConnection (connectionString);
1303                                 connB.Open ();
1304
1305                                 transA = connA.BeginTransaction ();
1306                                 transB = connB.BeginTransaction ();
1307
1308                                 SqlCommand cmdA = new SqlCommand ("select * from employee", connA, transA);
1309
1310                                 SqlCommand cmdB = new SqlCommand ("select * from employee", connA, transA);
1311                                 using (SqlDataReader reader = cmdB.ExecuteReader ()) {
1312 #if NET_2_0
1313                                         cmdA.Transaction = transA;
1314                                         Assert.AreSame (transA, cmdA.Transaction, "#A1");
1315 #else
1316                                         try {
1317                                                 cmdA.Transaction = transA;
1318                                                 Assert.Fail ("#A1");
1319                                         } catch (InvalidOperationException ex) {
1320                                                 // The SqlCommand is currently busy
1321                                                 // Open, Fetching
1322                                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
1323                                                 Assert.IsNull (ex.InnerException, "#A3");
1324                                                 Assert.IsNotNull (ex.Message, "#A4");
1325
1326                                                 Assert.AreSame (transA, cmdA.Transaction, "#A5");
1327                                         }
1328 #endif
1329
1330 #if NET_2_0
1331                                         cmdA.Transaction = transB;
1332                                         Assert.AreSame (transB, cmdA.Transaction, "#B1");
1333 #else
1334                                         try {
1335                                                 cmdA.Transaction = transB;
1336                                                 Assert.Fail ("#B1");
1337                                         } catch (InvalidOperationException ex) {
1338                                                 // The SqlCommand is currently busy
1339                                                 // Open, Fetching
1340                                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
1341                                                 Assert.IsNull (ex.InnerException, "#B3");
1342                                                 Assert.IsNotNull (ex.Message, "#B4");
1343
1344                                                 Assert.AreSame (transA, cmdA.Transaction, "#B5");
1345                                         }
1346 #endif
1347
1348 #if NET_2_0
1349                                         cmdA.Transaction = null;
1350                                         Assert.IsNull (cmdA.Transaction, "#C1");
1351 #else
1352                                         try {
1353                                                 cmdA.Transaction = null;
1354                                                 Assert.Fail ("#C1");
1355                                         } catch (InvalidOperationException ex) {
1356                                                 // The SqlCommand is currently busy
1357                                                 // Open, Fetching
1358                                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#C2");
1359                                                 Assert.IsNull (ex.InnerException, "#C3");
1360                                                 Assert.IsNotNull (ex.Message, "#C4");
1361
1362                                                 Assert.AreSame (transA, cmdA.Transaction, "#C5");
1363                                         }
1364 #endif
1365                                 }
1366
1367                                 cmdA.Transaction = transA;
1368                                 Assert.AreSame (transA, cmdA.Transaction, "#D1");
1369                                 cmdA.Transaction = transB;
1370                                 Assert.AreSame (transB, cmdA.Transaction, "#D2");
1371                         } finally {
1372                                 if (transA != null)
1373                                         transA.Dispose ();
1374                                 if (transB != null)
1375                                         transA.Dispose ();
1376                                 if (connA != null)
1377                                         connA.Close ();
1378                                 if (connB != null)
1379                                         connB.Close ();
1380                         }
1381                 }
1382
1383                 [Test]
1384                 public void ExecuteNonQueryTempProcedureTest ()
1385                 {
1386                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
1387                         try {
1388                                 ConnectionManager.Singleton.OpenConnection ();
1389                                 // create temp sp here, should normally be created in Setup of test
1390                                 // case, but cannot be done right now because of ug #68978
1391                                 DBHelper.ExecuteNonQuery (conn, CREATE_TMP_SP_TEMP_INSERT_PERSON);
1392                                 SqlCommand cmd = new SqlCommand();
1393                                 cmd.Connection = conn;
1394                                 cmd.CommandText = "#sp_temp_insert_employee";
1395                                 cmd.CommandType = CommandType.StoredProcedure;
1396                                 Object TestPar = "test";
1397                                 cmd.Parameters.Add("@fname", SqlDbType.VarChar);
1398                                 cmd.Parameters ["@fname"].Value = TestPar;
1399                                 Assert.AreEqual(1,cmd.ExecuteNonQuery());
1400                         } finally {
1401                                 DBHelper.ExecuteNonQuery (conn, string.Format (
1402                                         CultureInfo.InvariantCulture,
1403                                         DROP_STORED_PROCEDURE, "#sp_temp_insert_employee"));
1404                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_person_table");
1405                                 ConnectionManager.Singleton.CloseConnection ();
1406                         }
1407                 }
1408
1409                 [Test] // bug #319598
1410                 public void LongQueryTest ()
1411                 {
1412                         SqlConnection conn = new SqlConnection (
1413                                                         connectionString + ";Pooling=false");
1414                         using (conn) {
1415                                 conn.Open ();
1416                                 SqlCommand cmd = conn.CreateCommand ();
1417                                 String value =  new String ('a', 10000);
1418                                 cmd.CommandText = String.Format ("Select '{0}'", value);
1419                                 cmd.ExecuteNonQuery ();
1420                         }
1421                 }
1422
1423                 [Test] // bug #319598
1424                 public void LongStoredProcTest()
1425                 {
1426                         SqlConnection conn = new SqlConnection (
1427                                                         connectionString + ";Pooling=false");
1428                         using (conn) {
1429                                 conn.Open ();
1430                                 /*int size = conn.PacketSize;*/
1431                                 SqlCommand cmd = conn.CreateCommand ();
1432                                 // create a temp stored proc
1433                                 cmd.CommandText  = "Create Procedure #sp_tmp_long_params ";
1434                                 cmd.CommandText += "@p1 nvarchar (4000), ";
1435                                 cmd.CommandText += "@p2 nvarchar (4000), ";
1436                                 cmd.CommandText += "@p3 nvarchar (4000), ";
1437                                 cmd.CommandText += "@p4 nvarchar (4000) out ";
1438                                 cmd.CommandText += "As ";
1439                                 cmd.CommandText += "Begin ";
1440                                 cmd.CommandText += "Set @p4 = N'Hello' ";
1441                                 cmd.CommandText += "Return 2 ";
1442                                 cmd.CommandText += "End";
1443                                 cmd.ExecuteNonQuery ();
1444
1445                                 //execute the proc
1446                                 cmd.CommandType = CommandType.StoredProcedure;
1447                                 cmd.CommandText = "#sp_tmp_long_params";
1448
1449                                 String value =  new String ('a', 4000);
1450                                 SqlParameter p1 = new SqlParameter ("@p1",
1451                                                         SqlDbType.NVarChar,4000);
1452                                 p1.Value = value;
1453
1454                                 SqlParameter p2 = new SqlParameter ("@p2",
1455                                                         SqlDbType.NVarChar,4000);
1456                                 p2.Value = value;
1457
1458                                 SqlParameter p3 = new SqlParameter ("@p3",
1459                                                         SqlDbType.NVarChar,4000);
1460                                 p3.Value = value;
1461
1462                                 SqlParameter p4 = new SqlParameter ("@p4",
1463                                                         SqlDbType.NVarChar,4000);
1464                                 p4.Direction = ParameterDirection.Output;
1465
1466                                 // for now, name shud be @RETURN_VALUE
1467                                 // can be changed once RPC is implemented
1468                                 SqlParameter p5 = new SqlParameter ("@RETURN_VALUE", SqlDbType.Int);
1469                                 p5.Direction = ParameterDirection.ReturnValue;
1470
1471                                 cmd.Parameters.Add (p1);
1472                                 cmd.Parameters.Add (p2);
1473                                 cmd.Parameters.Add (p3);
1474                                 cmd.Parameters.Add (p4);
1475                                 cmd.Parameters.Add (p5);
1476
1477                                 cmd.ExecuteNonQuery ();
1478                                 Assert.AreEqual ("Hello", p4.Value, "#1");
1479                                 Assert.AreEqual (2, p5.Value, "#2");
1480                         }
1481                 }
1482
1483                 [Test] // bug #319694
1484                 public void DateTimeParameterTest ()
1485                 {
1486                         SqlConnection conn = new SqlConnection (connectionString);
1487                         using (conn) {
1488                                 conn.Open ();
1489                                 SqlCommand cmd = conn.CreateCommand ();
1490                                 cmd.CommandText = "select * from datetime_family where type_datetime=@p1";
1491                                 cmd.Parameters.Add ("@p1", SqlDbType.DateTime).Value= "10-10-2005";
1492                                 // shudnt cause and exception
1493                                 SqlDataReader rdr = cmd.ExecuteReader ();
1494                                 rdr.Close ();
1495                         }
1496                 }
1497
1498                 /**
1499                  * Verifies whether an enum value is converted to a numeric value when
1500                  * used as value for a numeric parameter (bug #66630)
1501                  */
1502                 [Test]
1503                 public void EnumParameterTest ()
1504                 {
1505                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
1506                         try {
1507                                 ConnectionManager.Singleton.OpenConnection ();
1508                                 // create temp sp here, should normally be created in Setup of test
1509                                 // case, but cannot be done right now because of ug #68978
1510                                 DBHelper.ExecuteNonQuery (conn, "CREATE PROCEDURE #Bug66630 ("
1511                                                           + "@Status smallint = 7"
1512                                                           + ")"
1513                                                           + "AS" + Environment.NewLine
1514                                                           + "BEGIN" + Environment.NewLine
1515                                                           + "SELECT CAST(5 AS int), @Status" + Environment.NewLine
1516                                                           + "END");
1517
1518                                 SqlCommand cmd = new SqlCommand("#Bug66630", conn);
1519                                 cmd.CommandType = CommandType.StoredProcedure;
1520                                 cmd.Parameters.Add("@Status", SqlDbType.Int).Value = Status.Error;
1521
1522                                 using (SqlDataReader dr = cmd.ExecuteReader()) {
1523                                         // one record should be returned
1524                                         Assert.IsTrue(dr.Read(), "EnumParameterTest#1");
1525                                         // we should get two field in the result
1526                                         Assert.AreEqual(2, dr.FieldCount, "EnumParameterTest#2");
1527                                         // field 1
1528                                         Assert.AreEqual("int", dr.GetDataTypeName(0), "EnumParameterTest#3");
1529                                         Assert.AreEqual(5, dr.GetInt32(0), "EnumParameterTest#4");
1530                                         // field 2
1531                                         Assert.AreEqual("smallint", dr.GetDataTypeName(1), "EnumParameterTest#5");
1532                                         Assert.AreEqual((short) Status.Error, dr.GetInt16(1), "EnumParameterTest#6");
1533                                         // only one record should be returned
1534                                         Assert.IsFalse(dr.Read(), "EnumParameterTest#7");
1535                                 }
1536                         } finally {
1537                                 DBHelper.ExecuteNonQuery (conn, "if exists (select name from sysobjects " +
1538                                                           " where name like '#temp_Bug66630' and type like 'P') " +
1539                                                           " drop procedure #temp_Bug66630; ");
1540                                 ConnectionManager.Singleton.CloseConnection ();
1541                         }
1542                 }
1543
1544                 [Test]
1545                 public void CloneTest ()
1546                 {
1547                         conn = new SqlConnection (connectionString);
1548                         conn.Open ();
1549
1550                         SqlTransaction trans = conn.BeginTransaction ();
1551
1552                         cmd = new SqlCommand ();
1553                         cmd.Connection = conn;
1554                         cmd.Transaction = trans;
1555
1556                         SqlCommand clone = (((ICloneable) (cmd)).Clone ()) as SqlCommand;
1557                         Assert.AreSame (conn, clone.Connection);
1558                         Assert.AreSame (trans, clone.Transaction);
1559                 }
1560
1561                 [Test]
1562                 public void StoredProc_NoParameterTest ()
1563                 {
1564                         string query = "create procedure #tmp_sp_proc as begin";
1565                         query += " select 'data' end";
1566                         SqlConnection conn = new SqlConnection (connectionString);
1567                         SqlCommand cmd = conn.CreateCommand ();
1568                         cmd.CommandText = query;
1569                         conn.Open ();
1570                         cmd.ExecuteNonQuery ();
1571
1572                         cmd.CommandType = CommandType.StoredProcedure;
1573                         cmd.CommandText = "#tmp_sp_proc";
1574                         using (SqlDataReader reader = cmd.ExecuteReader()) {
1575                                 if (reader.Read ())
1576                                         Assert.AreEqual ("data", reader.GetString(0),"#1");
1577                                 else
1578                                         Assert.Fail ("#2 Select shud return data");
1579                         }
1580                         conn.Close ();
1581                 }
1582
1583                 [Test]
1584                 public void StoredProc_ParameterTest ()
1585                 {
1586                         string create_query  = CREATE_TMP_SP_PARAM_TEST;
1587                         string drop_query = string.Format (CultureInfo.InvariantCulture,
1588                                 DROP_STORED_PROCEDURE, "#tmp_sp_param_test");
1589
1590                         SqlConnection conn = new SqlConnection (connectionString);
1591
1592                         conn.Open ();
1593                         SqlCommand cmd = conn.CreateCommand ();
1594                         int label = 0;
1595                         string error = string.Empty;
1596                         while (label != -1) {
1597                                 try {
1598                                         switch (label) {
1599                                                 case 0 :
1600                                                         // Test BigInt Param
1601                                                         DBHelper.ExecuteNonQuery (conn,
1602                                                                 String.Format (create_query, "bigint"));
1603                                                         rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1604                                                                 Int64.MaxValue, Int64.MaxValue,
1605                                                                 Int64.MaxValue);
1606                                                         rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1607                                                                 Int64.MinValue, Int64.MinValue,
1608                                                                 Int64.MinValue);
1609                                                         rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1610                                                                 DBNull.Value, DBNull.Value,
1611                                                                 DBNull.Value);
1612                                                         break;
1613                                                 case 1 :
1614                                                         // Test Binary Param
1615                                                         DBHelper.ExecuteNonQuery (conn,
1616                                                                 String.Format (create_query, "binary(5)"));
1617                                                         rpc_helper_function (cmd, SqlDbType.Binary, 5,
1618                                                                 new byte [] { 1, 2, 3, 4, 5 },
1619                                                                 new byte [] { 1, 2, 3, 4, 5 },
1620                                                                 new byte [] { 1, 2, 3, 4, 5 });
1621                                                         /*
1622                                                         rpc_helper_function (cmd, SqlDbType.Binary, 5,
1623                                                                 DBNull.Value, DBNull.Value,
1624                                                                 DBNull.Value);
1625                                                         */
1626                                                         rpc_helper_function (cmd, SqlDbType.Binary, 2,
1627                                                                 new byte [0],
1628                                                                 new byte [] { 0, 0, 0, 0, 0 },
1629                                                                 new byte [] { 0, 0 });
1630                                                         break;
1631                                                 case 2 :
1632                                                         // Test Bit Param
1633                                                         DBHelper.ExecuteNonQuery (conn,
1634                                                                 String.Format (create_query, "bit"));
1635                                                         rpc_helper_function (cmd, SqlDbType.Bit, 0,
1636                                                                 true, true, true);
1637                                                         rpc_helper_function (cmd, SqlDbType.Bit, 0,
1638                                                                 false, false, false);
1639                                                         rpc_helper_function (cmd, SqlDbType.Bit, 0,
1640                                                                 DBNull.Value, DBNull.Value,
1641                                                                 DBNull.Value);
1642                                                         break;
1643                                                 case 3 :
1644                                                         // Testing Char
1645                                                         DBHelper.ExecuteNonQuery (conn,
1646                                                                 String.Format (create_query, "char(10)"));
1647                                                         rpc_helper_function (cmd, SqlDbType.Char, 10,
1648                                                                 "characters", "characters",
1649                                                                 "characters");
1650                                                         /*
1651                                                         rpc_helper_function (cmd, SqlDbType.Char, 3,
1652                                                                 "characters", "cha       ",
1653                                                                 "cha");
1654                                                         rpc_helper_function (cmd, SqlDbType.Char, 3,
1655                                                                 string.Empty, "          ",
1656                                                                 "   ");
1657                                                         */
1658                                                         rpc_helper_function (cmd, SqlDbType.Char, 5,
1659                                                                 DBNull.Value, DBNull.Value,
1660                                                                 DBNull.Value);
1661                                                         break;
1662                                                 case 4 :
1663                                                         // Testing DateTime
1664                                                         DBHelper.ExecuteNonQuery (conn,
1665                                                                 String.Format (create_query, "datetime"));
1666                                                         rpc_helper_function (cmd, SqlDbType.DateTime, 0, "2079-06-06 23:59:00",
1667                                                                 new DateTime (2079, 6, 6, 23, 59, 0),
1668                                                                 new DateTime (2079, 6, 6, 23, 59, 0));
1669                                                         rpc_helper_function (cmd, SqlDbType.DateTime, 0, "2009-04-12 10:39:45",
1670                                                                 new DateTime (2009, 4, 12, 10, 39, 45),
1671                                                                 new DateTime (2009, 4, 12, 10, 39, 45));
1672                                                         rpc_helper_function (cmd, SqlDbType.DateTime, 0,
1673                                                                 DBNull.Value, DBNull.Value,
1674                                                                 DBNull.Value);
1675                                                         break;
1676                                                 case 5 :
1677                                                         // Test Decimal Param
1678                                                         DBHelper.ExecuteNonQuery (conn,
1679                                                                 String.Format (create_query, "decimal(10,2)"));
1680                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1681                                                                 10.665, 10.67m, 11m);
1682                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1683                                                                 0m, 0m, 0m);
1684                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1685                                                                 -5.657, -5.66m, -6m);
1686                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1687                                                                 DBNull.Value, DBNull.Value,
1688                                                                 DBNull.Value);
1689                                                         break;
1690                                                 case 6 :
1691                                                         // Test Float Param
1692                                                         DBHelper.ExecuteNonQuery (conn,
1693                                                                 String.Format (create_query, "float"));
1694                                                         rpc_helper_function (cmd, SqlDbType.Float, 0,
1695                                                                 10.0, 10.0, 10.0);
1696                                                         rpc_helper_function (cmd, SqlDbType.Float, 0,
1697                                                                 10.54, 10.54, 10.54);
1698                                                         rpc_helper_function (cmd, SqlDbType.Float, 0,
1699                                                                 0, 0d, 0d);
1700                                                         rpc_helper_function (cmd, SqlDbType.Float, 0,
1701                                                                 -5.34, -5.34, -5.34);
1702                                                         rpc_helper_function (cmd, SqlDbType.Float, 0,
1703                                                                 DBNull.Value, DBNull.Value,
1704                                                                 DBNull.Value);
1705                                                         break;
1706                                                 case 7 :
1707                                                         // Testing Image
1708                                                         /* NOT WORKING
1709                                                            DBHelper.ExecuteNonQuery (conn,
1710                                                            String.Format(create_query, "image"));
1711                                                            rpc_helper_function (cmd, SqlDbType.Image, 0, );
1712                                                            rpc_helper_function (cmd, SqlDbType.Image, 0, );
1713                                                            rpc_helper_function (cmd, SqlDbType.Image, 0, );
1714                                                            /* NOT WORKING*/
1715                                                         break;
1716                                                 case 8 :
1717                                                         // Test Integer Param
1718                                                         DBHelper.ExecuteNonQuery (conn,
1719                                                                 String.Format (create_query, "int"));
1720                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1721                                                                 10, 10, 10);
1722                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1723                                                                 0, 0, 0);
1724                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1725                                                                 -5, -5, -5);
1726                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1727                                                                 int.MaxValue, int.MaxValue,
1728                                                                 int.MaxValue);
1729                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1730                                                                 int.MinValue, int.MinValue,
1731                                                                 int.MinValue);
1732                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1733                                                                 DBNull.Value, DBNull.Value,
1734                                                                 DBNull.Value);
1735                                                         break;
1736                                                 case 9 :
1737                                                         // Test Money Param
1738                                                         DBHelper.ExecuteNonQuery (conn,
1739                                                                 String.Format (create_query, "money"));
1740                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1741                                                                 10m, 10m, 10m);
1742                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1743                                                                 10.54, 10.54m, 10.54m);
1744                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1745                                                                 0, 0m, 0m);
1746                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1747                                                                 -5.34, -5.34m, -5.34m);
1748                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1749                                                                 5.34, 5.34m, 5.34m);
1750                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1751                                                                 -10.1234m, -10.1234m, -10.1234m);
1752                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1753                                                                 10.1234m, 10.1234m, 10.1234m);
1754                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1755                                                                 -2000000000m, -2000000000m, -2000000000m);
1756                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1757                                                                 2000000000m, 2000000000m, 2000000000m);
1758                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1759                                                                 -200000000.2345m, -200000000.2345m, -200000000.2345m);
1760                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1761                                                                 200000000.2345m, 200000000.2345m, 200000000.2345m);
1762                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1763                                                                 DBNull.Value, DBNull.Value,
1764                                                                 DBNull.Value);
1765
1766                                                         // rounding tests
1767                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1768                                                                 -200000000.234561m, -200000000.2346m, -200000000.2346m);
1769                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1770                                                                 -200000000.234551m, -200000000.2346m, -200000000.2346m);
1771                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1772                                                                 -200000000.234541m, -200000000.2345m, -200000000.2345m);
1773                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1774                                                                 200000000.234561m, 200000000.2346m, 200000000.2346m);
1775                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1776                                                                 200000000.234551m, 200000000.2346m, 200000000.2346m);
1777                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1778                                                                 200000000.234541m, 200000000.2345m, 200000000.2345m);
1779                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1780                                                                 -200000000.234461m, -200000000.2345m, -200000000.2345m);
1781                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1782                                                                 -200000000.234451m, -200000000.2345m, -200000000.2345m);
1783                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1784                                                                 -200000000.234441m, -200000000.2344m, -200000000.2344m);
1785                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1786                                                                 200000000.234461m, 200000000.2345m, 200000000.2345m);
1787                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1788                                                                 200000000.234451m, 200000000.2345m, 200000000.2345m);
1789                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1790                                                                 200000000.234441m, 200000000.2344m, 200000000.2344m);
1791                                                         // FIXME: we round toward even in SqlParameter.ConvertToFrameworkType
1792                                                         /*
1793                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1794                                                                 -200000000.234550m, -200000000.2346m, -200000000.2346m);
1795                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1796                                                                 200000000.234550m, 200000000.2346m, 200000000.2346m);
1797                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1798                                                                 -200000000.234450m, -200000000.2345m, -200000000.2345m);
1799                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1800                                                                 200000000.234450m, 200000000.2345m, 200000000.2345m);
1801                                                         */
1802                                                         break;
1803                                                 case 23 :
1804                                                         // Test NChar Param
1805                                                         DBHelper.ExecuteNonQuery (conn,
1806                                                                 String.Format (create_query, "nchar(10)"));
1807                                                         rpc_helper_function (cmd, SqlDbType.NChar, 10,
1808                                                                 "characters", "characters",
1809                                                                 "characters");
1810                                                         rpc_helper_function (cmd, SqlDbType.NChar, 3,
1811                                                                 "characters", "cha       ",
1812                                                                 "cha");
1813                                                         rpc_helper_function (cmd, SqlDbType.NChar, 3,
1814                                                                 string.Empty, "          ",
1815                                                                 "   ");
1816                                                         /*
1817                                                         rpc_helper_function (cmd, SqlDbType.NChar, 5,
1818                                                                 DBNull.Value, DBNull.Value,
1819                                                                 DBNull.Value);
1820                                                         */
1821                                                         break;
1822                                                 case 10 :
1823                                                         // Test NText Param
1824                                                         DBHelper.ExecuteNonQuery (conn,
1825                                                                 String.Format (create_query, "ntext"));
1826                                                         /*
1827                                                         rpc_helper_function (cmd, SqlDbType.NText, 0, "ntext");
1828                                                         rpc_helper_function (cmd, SqlDbType.NText, 0, "");
1829                                                         rpc_helper_function (cmd, SqlDbType.NText, 0, null);
1830                                                         */
1831                                                         break;
1832                                                 case 11 :
1833                                                         // Test NVarChar Param
1834                                                         DBHelper.ExecuteNonQuery (conn,
1835                                                                 String.Format (create_query, "nvarchar(10)"));
1836                                                         rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1837                                                                 "nvarchar", "nvarchar", "nvarchar");
1838                                                         rpc_helper_function (cmd, SqlDbType.NVarChar, 3,
1839                                                                 "nvarchar", "nva", "nva");
1840                                                         /*
1841                                                         rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1842                                                                 string.Empty, string.Empty, string.Empty);
1843                                                         rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1844                                                                 DBNull.Value, DBNull.Value, DBNull.Value);
1845                                                         */
1846                                                         break;
1847                                                 case 12 :
1848                                                         // Test Real Param
1849                                                         DBHelper.ExecuteNonQuery (conn,
1850                                                                 String.Format (create_query, "real"));
1851                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1852                                                                 10m, 10f, 10f);
1853                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1854                                                                 10d, 10f, 10f);
1855                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1856                                                                 0, 0f, 0f);
1857                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1858                                                                 3.54d, 3.54f, 3.54f);
1859                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1860                                                                 10, 10f, 10f);
1861                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1862                                                                 10.5f, 10.5f, 10.5f);
1863                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1864                                                                 3.5d, 3.5f, 3.5f);
1865                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1866                                                                 4.54m, 4.54f, 4.54f);
1867                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1868                                                                 -4.54m, -4.54f, -4.54f);
1869                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1870                                                                 DBNull.Value, DBNull.Value, DBNull.Value);
1871                                                         break;
1872                                                 case 13 :
1873                                                         // Test SmallDateTime Param
1874                                                         DBHelper.ExecuteNonQuery (conn,
1875                                                                 String.Format (create_query, "smalldatetime"));
1876                                                         rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0,
1877                                                                 "6/6/2079 11:59:00 PM",
1878                                                                 new DateTime (2079, 6, 6, 23, 59, 0),
1879                                                                 new DateTime (2079, 6, 6, 23, 59, 0));
1880                                                         rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0,
1881                                                                 DBNull.Value, DBNull.Value,
1882                                                                 DBNull.Value);
1883                                                         break;
1884                                                 case 14 :
1885                                                         // Test SmallInt Param
1886                                                         DBHelper.ExecuteNonQuery (conn,
1887                                                                 String.Format (create_query, "smallint"));
1888                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1889                                                                 10, (short) 10, (short) 10);
1890                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1891                                                                 -10, (short) -10, (short) -10);
1892                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1893                                                                 short.MaxValue, short.MaxValue,
1894                                                                 short.MaxValue);
1895                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1896                                                                 short.MinValue, short.MinValue,
1897                                                                 short.MinValue);
1898                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1899                                                                 DBNull.Value, DBNull.Value,
1900                                                                 DBNull.Value);
1901                                                         break;
1902                                                 case 15 :
1903                                                         // Test SmallMoney Param
1904                                                         DBHelper.ExecuteNonQuery (conn,
1905                                                                         String.Format (create_query, "smallmoney"));
1906                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1907                                                                 10.0d, 10m, 10m);
1908                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1909                                                                 0, 0m, 0m);
1910                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1911                                                                 3.54d, 3.54m, 3.54m);
1912                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1913                                                                 10, 10m, 10m);
1914                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1915                                                                 10.5f, 10.5m, 10.5m);
1916                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1917                                                                 3.5d, 3.5m, 3.5m);
1918                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1919                                                                 4.54m, 4.54m, 4.54m);
1920                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1921                                                                 -4.54m, -4.54m, -4.54m);
1922                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1923                                                                 -214748.3648m, -214748.3648m, -214748.3648m);
1924                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1925                                                                 214748.3647m, 214748.3647m, 214748.3647m);
1926                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1927                                                                 DBNull.Value, DBNull.Value, DBNull.Value);
1928
1929                                                         // rounding tests
1930                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1931                                                                 -4.543361m, -4.5434m, -4.5434m);
1932                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1933                                                                 -4.543351m, -4.5434m, -4.5434m);
1934                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1935                                                                 -4.543341m, -4.5433m, -4.5433m);
1936                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1937                                                                 4.543361m, 4.5434m, 4.5434m);
1938                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1939                                                                 4.543351m, 4.5434m, 4.5434m);
1940                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1941                                                                 4.543341m, 4.5433m, 4.5433m);
1942                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1943                                                                 -4.543261m, -4.5433m, -4.5433m);
1944                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1945                                                                 -4.543251m, -4.5433m, -4.5433m);
1946                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1947                                                                 -4.543241m, -4.5432m, -4.5432m);
1948                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1949                                                                 4.543261m, 4.5433m, 4.5433m);
1950                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1951                                                                 4.543251m, 4.5433m, 4.5433m);
1952                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1953                                                                 4.543241m, 4.5432m, 4.5432m);
1954                                                         // FIXME: we round toward even in SqlParameter.ConvertToFrameworkType
1955                                                         /*
1956                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1957                                                                 -4.543350m, -4.5434m, -4.5434m);
1958                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1959                                                                 4.543350m, 4.5434m, 4.5434m);
1960                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1961                                                                 -4.543250m, -4.5433m, -4.5433m);
1962                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1963                                                                 4.543250m, 4.5433m, 4.5433m);
1964                                                         */
1965                                                         break;
1966                                                 case 16 :
1967                                                         // Test Text Param
1968                                                         DBHelper.ExecuteNonQuery (conn,
1969                                                                         String.Format (create_query, "text"));
1970                                                         /*
1971                                                         rpc_helper_function (cmd, SqlDbType.Text, 0, "text");
1972                                                         rpc_helper_function (cmd, SqlDbType.Text, 0, "");
1973                                                         rpc_helper_function (cmd, SqlDbType.Text, 0, null);
1974                                                         */
1975                                                         break;
1976                                                 case 17 :
1977                                                         // Test TimeStamp Param
1978                                                         /* NOT WORKING
1979                                                            DBHelper.ExecuteNonQuery (conn,
1980                                                            String.Format(create_query,"timestamp"));
1981                                                            rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, "");
1982                                                            rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, "");
1983                                                            rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, null);
1984                                                          */
1985                                                         break;
1986                                                 case 18 :
1987                                                         // Test TinyInt Param
1988                                                         DBHelper.ExecuteNonQuery (conn,
1989                                                                         String.Format(create_query,"tinyint"));
1990                                                         rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
1991                                                                 10.0d, (byte) 10, (byte) 10);
1992                                                         rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
1993                                                                 0, (byte) 0, (byte) 0);
1994                                                         rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
1995                                                                 byte.MaxValue, byte.MaxValue, byte.MaxValue);
1996                                                         rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
1997                                                                 byte.MinValue, byte.MinValue, byte.MinValue);
1998                                                         break;
1999                                                 case 19 :
2000                                                         // Test UniqueIdentifier Param
2001                                                         /*
2002                                                         DBHelper.ExecuteNonQuery (conn,
2003                                                                         String.Format(create_query,"uniqueidentifier"));
2004                                                         rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, "0f159bf395b1d04f8c2ef5c02c3add96");
2005                                                         rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, null);
2006                                                         */
2007                                                         break;
2008                                                 case 20 :
2009                                                         // Test VarBinary Param
2010                                                         /* NOT WORKING
2011                                                            DBHelper.ExecuteNonQuery (conn,
2012                                                            String.Format(create_query,"varbinary (10)"));
2013                                                            rpc_helper_function (cmd, SqlDbType.VarBinary, 0,);
2014                                                            rpc_helper_function (cmd, SqlDbType.VarBinary, 0,);
2015                                                            rpc_helper_function (cmd, SqlDbType.VarBinary, 0, null);
2016                                                          */
2017                                                         break;
2018                                                 case 21 :
2019                                                         // Test Varchar Param
2020                                                         DBHelper.ExecuteNonQuery (conn,
2021                                                                         String.Format(create_query,"varchar(10)"));
2022                                                         rpc_helper_function (cmd, SqlDbType.VarChar, 7,
2023                                                                 "VarChar", "VarChar", "VarChar");
2024                                                         rpc_helper_function (cmd, SqlDbType.VarChar, 5,
2025                                                                 "Var", "Var", "Var");
2026                                                         /*
2027                                                         rpc_helper_function (cmd, SqlDbType.VarChar, 3,
2028                                                                 "Varchar", "Var", "Var");
2029                                                         rpc_helper_function (cmd, SqlDbType.VarChar, 10,
2030                                                                 string.Empty, string.Empty, string.Empty);
2031                                                         rpc_helper_function (cmd, SqlDbType.VarChar, 10,
2032                                                                 DBNull.Value, DBNull.Value,
2033                                                                 DBNull.Value);
2034                                                         */
2035                                                         break;
2036                                                 case 22 :
2037                                                         // Test Variant Param
2038                                                         /* NOT WORKING
2039                                                            DBHelper.ExecuteNonQuery (conn,
2040                                                            String.Format(create_query,"variant"));
2041                                                            rpc_helper_function (cmd, SqlDbType.Variant, 0, );
2042                                                            rpc_helper_function (cmd, SqlDbType.Variant, 0, );
2043                                                            rpc_helper_function (cmd, SqlDbType.Variant, 0, null);
2044                                                          */
2045                                                         break;
2046                                                 default :
2047                                                         label = -2;
2048                                                         break;
2049                                         }
2050                                 } catch (AssertionException ex) {
2051                                         error += String.Format (" Case {0} INCORRECT VALUE : {1}\n", label, ex.ToString ());
2052                                 } catch (Exception ex) {
2053                                         error += String.Format (" Case {0} NOT WORKING : {1}\n", label, ex.ToString ());
2054                                 }
2055
2056                                 label++;
2057                                 if (label != -1)
2058                                         DBHelper.ExecuteNonQuery (conn, drop_query);
2059                         }
2060
2061                         if (error.Length != 0)
2062                                 Assert.Fail (error);
2063                 }
2064
2065                 private void rpc_helper_function (SqlCommand cmd, SqlDbType type, int size, object input, object expectedRead, object expectedOut)
2066                 {
2067                         cmd.Parameters.Clear ();
2068                         SqlParameter param1;
2069                         SqlParameter param2;
2070                         if (size != 0) {
2071                                 param1 = new SqlParameter ("@param1", type, size);
2072                                 param2 = new SqlParameter ("@param2", type, size);
2073                         } else {
2074                                 param1 = new SqlParameter ("@param1", type);
2075                                 param2 = new SqlParameter ("@param2", type);
2076                         }
2077
2078                         SqlParameter retval = new SqlParameter ("retval", SqlDbType.Int);
2079                         param1.Value = input;
2080                         param1.Direction = ParameterDirection.Input;
2081                         param2.Direction = ParameterDirection.Output;
2082                         retval.Direction = ParameterDirection.ReturnValue;
2083                         cmd.Parameters.Add (param1);
2084                         cmd.Parameters.Add (param2);
2085                         cmd.Parameters.Add (retval);
2086                         cmd.CommandText = "#tmp_sp_param_test";
2087                         cmd.CommandType = CommandType.StoredProcedure;
2088                         using (SqlDataReader reader = cmd.ExecuteReader ()) {
2089                                 Assert.IsTrue (reader.Read (), "#1");
2090                                 AreEqual (expectedRead, reader.GetValue (0), "#2");
2091                                 Assert.IsFalse (reader.Read (), "#3");
2092                         }
2093                         AreEqual (expectedOut, param2.Value, "#4");
2094                         AreEqual (5, retval.Value, "#5");
2095                 }
2096
2097                 [Test]
2098                 public void OutputParamSizeTest1 ()
2099                 {
2100                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2101                         ConnectionManager.Singleton.OpenConnection ();
2102                         cmd = new SqlCommand ();
2103                         cmd.Connection = conn;
2104
2105                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2106                         cmd.CommandType = CommandType.Text;
2107                         cmd.ExecuteNonQuery ();
2108
2109                         cmd.CommandText = "#testsize";
2110                         cmd.CommandType = CommandType.StoredProcedure;
2111
2112                         SqlParameter p1 = new SqlParameter ();
2113                         p1.ParameterName = "@p1";
2114                         p1.Direction = ParameterDirection.InputOutput;
2115                         p1.DbType = DbType.String;
2116                         p1.IsNullable = false;
2117                         cmd.Parameters.Add (p1);
2118
2119                         try {
2120                                 cmd.ExecuteNonQuery ();
2121                                 Assert.Fail ("#1");
2122                         } catch (InvalidOperationException ex) {
2123                                 // String[0]: the Size property has an invalid
2124                                 // size of 0
2125                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2126                                 Assert.IsNull (ex.InnerException, "#3");
2127                                 Assert.IsNotNull (ex.Message, "#4");
2128                         }
2129                 }
2130
2131                 [Test]
2132                 public void OutputParamSizeTest2 ()
2133                 {
2134                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2135                         ConnectionManager.Singleton.OpenConnection ();
2136                         cmd = new SqlCommand ();
2137                         cmd.Connection = conn;
2138
2139                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2140                         cmd.CommandType = CommandType.Text;
2141                         cmd.ExecuteNonQuery ();
2142
2143                         cmd.CommandText = "#testsize";
2144                         cmd.CommandType = CommandType.StoredProcedure;
2145
2146                         SqlParameter p1 = new SqlParameter ();
2147                         p1.ParameterName = "@p1";
2148                         p1.Direction = ParameterDirection.Output;
2149                         p1.DbType = DbType.String;
2150                         p1.IsNullable = false;
2151                         cmd.Parameters.Add (p1);
2152
2153                         try {
2154                                 cmd.ExecuteNonQuery ();
2155                                 Assert.Fail ("#1");
2156                         } catch (InvalidOperationException ex) {
2157                                 // String[0]: the Size property has an invalid
2158                                 // size of 0
2159                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2160                                 Assert.IsNull (ex.InnerException, "#3");
2161                                 Assert.IsNotNull (ex.Message, "#4");
2162                         }
2163                 }
2164
2165                 [Test]
2166                 public void OutputParamSizeTest3 ()
2167                 {
2168                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2169                         ConnectionManager.Singleton.OpenConnection ();
2170                         cmd = new SqlCommand ();
2171                         cmd.Connection = conn;
2172
2173                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2174                         cmd.CommandType = CommandType.Text;
2175                         cmd.ExecuteNonQuery ();
2176
2177                         cmd.CommandText = "#testsize";
2178                         cmd.CommandType = CommandType.StoredProcedure;
2179
2180                         SqlParameter p1 = new SqlParameter ();
2181                         p1.ParameterName = "@p1";
2182                         p1.Direction = ParameterDirection.InputOutput;
2183                         p1.DbType = DbType.String;
2184                         p1.IsNullable = true;
2185                         cmd.Parameters.Add (p1);
2186
2187                         try {
2188                                 cmd.ExecuteNonQuery ();
2189                                 Assert.Fail ("#1");
2190                         } catch (InvalidOperationException ex) {
2191                                 // String[0]: the Size property has an invalid
2192                                 // size of 0
2193                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2194                                 Assert.IsNull (ex.InnerException, "#3");
2195                                 Assert.IsNotNull (ex.Message, "#4");
2196                         }
2197                 }
2198
2199                 [Test]
2200                 public void OutputParamSizeTest4 ()
2201                 {
2202                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2203                         ConnectionManager.Singleton.OpenConnection ();
2204                         cmd = new SqlCommand ();
2205                         cmd.Connection = conn;
2206
2207                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2208                         cmd.CommandType = CommandType.Text;
2209                         cmd.ExecuteNonQuery ();
2210
2211                         cmd.CommandText = "#testsize";
2212                         cmd.CommandType = CommandType.StoredProcedure;
2213
2214                         SqlParameter p1 = new SqlParameter ();
2215                         p1.ParameterName = "@p1";
2216                         p1.Direction = ParameterDirection.Output;
2217                         p1.DbType = DbType.String;
2218                         p1.IsNullable = true;
2219                         cmd.Parameters.Add (p1);
2220
2221                         try {
2222                                 cmd.ExecuteNonQuery ();
2223                                 Assert.Fail ("#1");
2224                         } catch (InvalidOperationException ex) {
2225                                 // String[0]: the Size property has an invalid
2226                                 // size of 0
2227                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2228                                 Assert.IsNull (ex.InnerException, "#3");
2229                                 Assert.IsNotNull (ex.Message, "#4");
2230                         }
2231                 }
2232
2233                 [Test]
2234                 public void SmallMoney_Overflow_Max ()
2235                 {
2236                         conn = new SqlConnection (connectionString);
2237                         conn.Open ();
2238
2239                         DBHelper.ExecuteNonQuery (conn, string.Format (
2240                                 CultureInfo.InvariantCulture, CREATE_TMP_SP_TYPE_TEST,
2241                                 "SMALLMONEY"));
2242                         //decimal overflow = 214748.36471m;
2243                         decimal overflow = 214748.3648m;
2244
2245                         cmd = conn.CreateCommand ();
2246                         cmd.CommandText = "#tmp_sp_type_test";
2247                         cmd.CommandType = CommandType.StoredProcedure;
2248
2249                         SqlParameter param = cmd.Parameters.Add ("@param",
2250                                 SqlDbType.SmallMoney);
2251                         param.Value = overflow;
2252
2253                         try {
2254                                 cmd.ExecuteScalar ();
2255                                 Assert.Fail ("#1");
2256                         } catch (OverflowException ex) {
2257                                 // SqlDbType.SmallMoney overflow.  Value '214748.36471'
2258                                 // is out of range.  Must be between -214,748.3648 and 214,748.3647
2259                                 Assert.AreEqual (typeof (OverflowException), ex.GetType (), "#2");
2260                                 Assert.IsNull (ex.InnerException, "#3");
2261                                 Assert.IsNotNull (ex.Message, "#4");
2262 #if NET_2_0
2263                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2264                                         CultureInfo.InvariantCulture, "'{0}'",
2265                                         overflow)) != -1, "#5:" + ex.Message);
2266 #else
2267                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2268                                         CultureInfo.CurrentCulture, "'{0}'",
2269                                         overflow)) != -1, "#5:" + ex.Message);
2270 #endif
2271                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2272                                         CultureInfo.InvariantCulture, "{0:N4}",
2273                                         SMALLMONEY_MIN)) != -1, "#6:" + ex.Message);
2274                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2275                                         CultureInfo.InvariantCulture, "{0:N4}",
2276                                         SMALLMONEY_MAX)) != -1, "#7:" + ex.Message);
2277                         } finally {
2278                                 DBHelper.ExecuteNonQuery (conn, string.Format (
2279                                         CultureInfo.InvariantCulture,
2280                                         DROP_STORED_PROCEDURE, "#tmp_sp_type_test"));
2281                         }
2282                 }
2283
2284                 [Test]
2285                 public void SmallMoney_Overflow_Min ()
2286                 {
2287                         conn = new SqlConnection (connectionString);
2288                         conn.Open ();
2289
2290                         DBHelper.ExecuteNonQuery (conn, string.Format (
2291                                 CultureInfo.InvariantCulture, CREATE_TMP_SP_TYPE_TEST,
2292                                 "SMALLMONEY"));
2293                         //decimal overflow = -214748.36481m;
2294                         decimal overflow = -214748.3649m;
2295
2296                         cmd = conn.CreateCommand ();
2297                         cmd.CommandText = "#tmp_sp_type_test";
2298                         cmd.CommandType = CommandType.StoredProcedure;
2299
2300                         SqlParameter param = cmd.Parameters.Add ("@param",
2301                                 SqlDbType.SmallMoney);
2302                         param.Value = overflow;
2303
2304                         try {
2305                                 cmd.ExecuteScalar ();
2306                                 Assert.Fail ("#1");
2307                         } catch (OverflowException ex) {
2308                                 // SqlDbType.SmallMoney overflow.  Value '-214748,36481'
2309                                 // is out of range.  Must be between -214,748.3648 and 214,748.3647
2310                                 Assert.AreEqual (typeof (OverflowException), ex.GetType (), "#2");
2311                                 Assert.IsNull (ex.InnerException, "#3");
2312                                 Assert.IsNotNull (ex.Message, "#4");
2313 #if NET_2_0
2314                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2315                                         CultureInfo.InvariantCulture, "'{0}'",
2316                                         overflow)) != -1, "#5:" + ex.Message);
2317 #else
2318                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2319                                         CultureInfo.CurrentCulture, "'{0}'",
2320                                         overflow)) != -1, "#5:" + ex.Message);
2321 #endif
2322                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2323                                         CultureInfo.InvariantCulture, "{0:N4}",
2324                                         SMALLMONEY_MIN)) != -1, "#6:" + ex.Message);
2325                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2326                                         CultureInfo.InvariantCulture, "{0:N4}",
2327                                         SMALLMONEY_MAX)) != -1, "#7:" + ex.Message);
2328                         } finally {
2329                                 DBHelper.ExecuteNonQuery (conn, string.Format (
2330                                         CultureInfo.InvariantCulture,
2331                                         DROP_STORED_PROCEDURE, "#tmp_sp_type_test"));
2332                         }
2333                 }
2334
2335 #if NET_2_0
2336                 [Test]
2337                 public void NotificationTest ()
2338                 {
2339                         cmd = new SqlCommand ();
2340                         SqlNotificationRequest notification = new SqlNotificationRequest("MyNotification","MyService",15);
2341                         Assert.AreEqual (null, cmd.Notification, "#1 The default value for this property should be null");
2342                         cmd.Notification = notification;
2343                         Assert.AreEqual ("MyService", cmd.Notification.Options, "#2 The value should be MyService as the constructor is initiated with this value");
2344                         Assert.AreEqual (15, cmd.Notification.Timeout, "#2 The value should be 15 as the constructor is initiated with this value");
2345                 }
2346
2347                 [Test]
2348                 public void NotificationAutoEnlistTest ()
2349                 {
2350                         cmd = new SqlCommand ();
2351                         Assert.AreEqual (true, cmd.NotificationAutoEnlist, "#1 Default value of the property should be true");
2352                         cmd.NotificationAutoEnlist = false;
2353                         Assert.AreEqual (false, cmd.NotificationAutoEnlist, "#2 The value of the property should be false after setting it to false");
2354                 }
2355
2356                 [Test]
2357                 public void BeginExecuteXmlReaderTest ()
2358                 {
2359                         cmd = new SqlCommand ();
2360                         string connectionString1 = null;
2361                         connectionString1 = ConnectionManager.Singleton.ConnectionString + "Asynchronous Processing=true";
2362                         try {
2363                                 SqlConnection conn1 = new SqlConnection (connectionString1);
2364                                 conn1.Open ();
2365                                 cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA";
2366                                 cmd.Connection = conn1;
2367
2368                                 IAsyncResult result = cmd.BeginExecuteXmlReader ();
2369                                 XmlReader reader = cmd.EndExecuteXmlReader (result);
2370                                 while (reader.Read ()) {
2371                                         if (reader.LocalName.ToString () == "employee")
2372                                                 Assert.AreEqual ("kumar", reader["lname"], "#1 ");
2373                                 }
2374                         } finally {
2375                                 ConnectionManager.Singleton.CloseConnection ();
2376                         }
2377                 }
2378
2379                 [Test]
2380                 public void BeginExecuteXmlReaderExceptionTest ()
2381                 {
2382                         cmd = new SqlCommand ();
2383                         try {
2384                                 SqlConnection conn = new SqlConnection (connectionString);
2385                                 conn.Open ();
2386                                 cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA";
2387                                 cmd.Connection = conn;
2388
2389                                 try {
2390                                         /*IAsyncResult result = */cmd.BeginExecuteXmlReader ();
2391                                 } catch (InvalidOperationException) {
2392                                         Assert.AreEqual (ConnectionManager.Singleton.ConnectionString, connectionString, "#1 Connection string has changed");
2393                                         return;
2394                                 }
2395                                 Assert.Fail ("Expected Exception InvalidOperationException not thrown");
2396                         } finally {
2397                                 ConnectionManager.Singleton.CloseConnection ();
2398                         }
2399                 }
2400 #endif
2401
2402                 [Test]
2403                 public void SqlCommandDisposeTest ()
2404                 {
2405                         IDataReader reader = null;
2406                         try {
2407                                 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2408                                 ConnectionManager.Singleton.OpenConnection ();
2409
2410                                 IDbCommand command = conn.CreateCommand ();
2411                                 try {
2412                                         string sql = "SELECT * FROM employee";
2413                                         command.CommandText = sql;
2414                                         reader = command.ExecuteReader();
2415                                 } finally {
2416                                         command.Dispose();
2417                                 }
2418                                 while (reader.Read());
2419                         } finally {
2420                                 reader.Dispose();
2421                                 ConnectionManager.Singleton.CloseConnection ();
2422                         }
2423                 }
2424
2425                 private void bug326182_OutputParamMixupTestCommon (int paramOrder,
2426                                                                    out int param0Val,
2427                                                                    out int param1Val,
2428                                                                    out int param2Val,
2429                                                                    out int param3Val,
2430                                                                    out int rvalVal)
2431                 {
2432                         try {
2433                                 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2434                                 ConnectionManager.Singleton.OpenConnection ();
2435                                 string create_proc = "CREATE procedure #sp_326182 ( " + Environment.NewLine +
2436                                                 "@param0 int out," + Environment.NewLine +
2437                                                 "@param1 int out," + Environment.NewLine +
2438                                                 "@param2 int out," + Environment.NewLine +
2439                                                 "@param3 int out" + Environment.NewLine +
2440                                                 ")" + Environment.NewLine +
2441                                                 "as" + Environment.NewLine +
2442                                                 "set @param0 = 100" + Environment.NewLine +
2443                                                 "set @param1 = 101" + Environment.NewLine +
2444                                                 "set @param2 = 102" + Environment.NewLine +
2445                                                 "set @param3 = 103" + Environment.NewLine +
2446                                                 "return 2";
2447
2448                                 try {
2449                                         SqlParameter param0 = new SqlParameter ("@param0", SqlDbType.Int);
2450                                         SqlParameter param1 = new SqlParameter ("@param1", SqlDbType.Int);
2451                                         SqlParameter param2 = new SqlParameter ("@param2", SqlDbType.Int);
2452                                         SqlParameter param3 = new SqlParameter ("@param3", SqlDbType.Int);
2453                                         SqlParameter rval = new SqlParameter ("@RETURN_VALUE", SqlDbType.Int);
2454
2455                                         cmd = new SqlCommand();
2456                                         cmd.CommandText = create_proc;
2457                                         cmd.CommandType = CommandType.Text;
2458                                         cmd.Connection = conn;
2459                                         cmd.CommandTimeout = 90;
2460                                         cmd.ExecuteNonQuery ();
2461
2462                                         cmd.CommandText = "dbo.[#sp_326182]";
2463                                         cmd.CommandType = CommandType.StoredProcedure;
2464
2465                                         param0.Direction = ParameterDirection.Output;
2466                                         param1.Direction = ParameterDirection.Output;
2467                                         param2.Direction = ParameterDirection.Output;
2468                                         param3.Direction = ParameterDirection.Output;
2469                                         rval.Direction = ParameterDirection.ReturnValue;
2470
2471                                         switch (paramOrder) {
2472                                         case 1: cmd.Parameters.Add (param0);
2473                                                 cmd.Parameters.Add (param1);
2474                                                 cmd.Parameters.Add (rval);
2475                                                 cmd.Parameters.Add (param2);
2476                                                 cmd.Parameters.Add (param3);
2477                                                 break;
2478                                         case 2: cmd.Parameters.Add (rval);
2479                                                 cmd.Parameters.Add (param1);
2480                                                 cmd.Parameters.Add (param0);
2481                                                 cmd.Parameters.Add (param2);
2482                                                 cmd.Parameters.Add (param3);
2483                                                 break;
2484                                         default: cmd.Parameters.Add (param0);
2485                                                 cmd.Parameters.Add (param1);
2486                                                 cmd.Parameters.Add (param2);
2487                                                 cmd.Parameters.Add (param3);
2488                                                 cmd.Parameters.Add (rval);
2489                                                 break;
2490                                         }
2491
2492                                         cmd.ExecuteNonQuery ();
2493
2494                                         /* Copy the param values to variables, just in case if
2495                                          * tests fail, we don't want the created sp to exist */
2496                                         param3Val = (int)cmd.Parameters["@param3"].Value;
2497                                         param1Val = (int)cmd.Parameters["@param1"].Value;
2498                                         rvalVal = (int)cmd.Parameters["@RETURN_VALUE"].Value;
2499                                         param2Val = (int)cmd.Parameters["@param2"].Value;
2500                                         param0Val = (int)cmd.Parameters["@param0"].Value;
2501                                 } finally {
2502                                         /* Delete the created stored procedure */
2503                                         cmd = conn.CreateCommand ();
2504                                         cmd.CommandText = "drop procedure #sp_326182";
2505                                         cmd.ExecuteNonQuery ();
2506
2507                                         cmd.Dispose();
2508                                         cmd = null;
2509                                 }
2510                         } finally {
2511                                 ConnectionManager.Singleton.CloseConnection ();
2512                                 conn = null;
2513                         }
2514                 }
2515
2516                 [Test]
2517                 public void bug326182_OutputParamMixupTest_Normal ()
2518                 {
2519                         int param0Val, param1Val, param2Val, param3Val, rvalVal;
2520
2521                         //param0Val = param1Val = param2Val = param3Val = rvalVal = 0;
2522
2523                         bug326182_OutputParamMixupTestCommon (0, out param0Val, out param1Val,
2524                                                               out param2Val, out param3Val, out rvalVal);
2525                         Assert.AreEqual (103, param3Val);
2526                         Assert.AreEqual (101, param1Val);
2527                         Assert.AreEqual (2, rvalVal);
2528                         Assert.AreEqual (102, param2Val);
2529                         Assert.AreEqual (100, param0Val);
2530                 }
2531
2532                 [Test]
2533                 public void bug326182_OutputParamMixupTest_RValInBetween ()
2534                 {
2535                         int param0Val, param1Val, param2Val, param3Val, rvalVal;
2536
2537                         bug326182_OutputParamMixupTestCommon (1, out param0Val, out param1Val,
2538                                                               out param2Val, out param3Val, out rvalVal);
2539                         Assert.AreEqual (103, param3Val);
2540                         Assert.AreEqual (101, param1Val);
2541                         Assert.AreEqual (2, rvalVal);
2542                         Assert.AreEqual (102, param2Val);
2543                         Assert.AreEqual (100, param0Val);
2544                 }
2545
2546                 [Test]
2547                 public void bug326182_OutputParamMixupTest_RValFirst ()
2548                 {
2549                         int param0Val, param1Val, param2Val, param3Val, rvalVal;
2550
2551                         bug326182_OutputParamMixupTestCommon (2, out param0Val, out param1Val,
2552                                                               out param2Val, out param3Val, out rvalVal);
2553                         Assert.AreEqual (103, param3Val);
2554                         Assert.AreEqual (101, param1Val);
2555                         Assert.AreEqual (2, rvalVal);
2556                         Assert.AreEqual (102, param2Val);
2557                         Assert.AreEqual (100, param0Val);
2558                 }
2559
2560                 // used as workaround for bugs in NUnit 2.2.0
2561                 static void AreEqual (object x, object y, string msg)
2562                 {
2563                         if (x == null && y == null)
2564                                 return;
2565                         if ((x == null || y == null))
2566                                 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2567                                         "Expected: {0}, but was: {1}. {2}",
2568                                         x == null ? "<null>" : x, y == null ? "<null>" : y, msg));
2569
2570                         bool isArrayX = x.GetType ().IsArray;
2571                         bool isArrayY = y.GetType ().IsArray;
2572
2573                         if (isArrayX && isArrayY) {
2574                                 Array arrayX = (Array) x;
2575                                 Array arrayY = (Array) y;
2576
2577                                 if (arrayX.Length != arrayY.Length)
2578                                         throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2579                                                 "Length of arrays differs. Expected: {0}, but was: {1}. {2}",
2580                                                 arrayX.Length, arrayY.Length, msg));
2581
2582                                 for (int i = 0; i < arrayX.Length; i++) {
2583                                         object itemX = arrayX.GetValue (i);
2584                                         object itemY = arrayY.GetValue (i);
2585                                         if (!itemX.Equals (itemY))
2586                                                 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2587                                                         "Arrays differ at position {0}. Expected: {1}, but was: {2}. {3}",
2588                                                         i, itemX, itemY, msg));
2589                                 }
2590                         } else if (!x.Equals (y)) {
2591                                 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2592                                         "Expected: {0} ({1}), but was: {2} ({3}). {4}",
2593                                         x, x.GetType (), y, y.GetType (), msg));
2594                         }
2595                 }
2596
2597                 private enum Status
2598                 {
2599                         OK = 0,
2600                         Error = 3
2601                 }
2602
2603                 private static readonly string CREATE_TMP_SP_PARAM_TEST = "create procedure #tmp_sp_param_test (@param1 {0}, @param2 {0} output) as begin select @param1 set @param2=@param1 return 5 end";
2604                 private readonly string CREATE_TMP_SP_TEMP_INSERT_PERSON = ("create procedure #sp_temp_insert_employee ( " + Environment.NewLine +
2605                                                                             "@fname varchar (20)) " + Environment.NewLine +
2606                                                                             "as " + Environment.NewLine +
2607                                                                             "begin" + Environment.NewLine +
2608                                                                             "declare @id int;" + Environment.NewLine +
2609                                                                             "select @id = max (id) from employee;" + Environment.NewLine +
2610                                                                             "set @id = @id + 6000 + 1;" + Environment.NewLine +
2611                                                                             "insert into employee (id, fname, dob, doj) values (@id, @fname, '1980-02-11', getdate ());" + Environment.NewLine +
2612                                                                             "return @id;" + Environment.NewLine +
2613                                                                             "end");
2614                 private static readonly string CREATE_TMP_SP_TYPE_TEST =
2615                         "CREATE PROCEDURE #tmp_sp_type_test " +
2616                         "(" +
2617                         "       @param {0}" +
2618                         ") AS SELECT @param";
2619                 private static readonly string DROP_STORED_PROCEDURE =
2620                         "DROP PROCEDURE {0}";
2621         }
2622 }