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