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.Connected.SqlClient
  43 {
  44         [TestFixture]
  45         [Category ("sqlserver")]
  46         public class SqlCommandTest
  47         {
  48                 SqlConnection conn;
  49                 SqlCommand cmd;
  50                 string connectionString = ConnectionManager.Instance.Sql.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.Instance.Sql.EngineConfig;
  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 = ConnectionManager.Instance.Sql.Connection;
 148
 149                         cmd = conn.CreateCommand ();
 150                         cmd.CommandText = "SELECT 'a'";
 151                         cmd.ExecuteNonQuery ();
 152
 153                         conn.Dispose ();
 154
 155                         Assert.AreSame (conn, cmd.Connection, "#1");
 156                         cmd.Dispose ();
 157                         Assert.AreSame (conn, cmd.Connection, "#2");
 158                 }
 159
 160                 [Test]
 161                 [Category("NotWorking")]
 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 = ConnectionManager.Instance.Sql.Connection;
 273
 274                         cmd = conn.CreateCommand ();
 275
 276                         try {
 277                                 cmd.ExecuteScalar ();
 278                                 Assert.Fail ("#A1");
 279                         } catch (InvalidOperationException ex) {
 280                                 // ExecuteScalar: CommandText property
 281                                 // has not been initialized
 282                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
 283                                 Assert.IsNull (ex.InnerException, "#A3");
 284                                 Assert.IsNotNull (ex.Message, "#A4");
 285                                 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar"), "#A5:" + ex.Message);
 286                         }
 287
 288                         cmd.CommandText = string.Empty;
 289
 290                         try {
 291                                 cmd.ExecuteScalar ();
 292                                 Assert.Fail ("#B1");
 293                         } catch (InvalidOperationException ex) {
 294                                 // ExecuteScalar: CommandText property
 295                                 // has not been initialized
 296                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
 297                                 Assert.IsNull (ex.InnerException, "#B3");
 298                                 Assert.IsNotNull (ex.Message, "#B4");
 299                                 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar"), "#B5:" + ex.Message);
 300                         }
 301
 302                         cmd.CommandText = null;
 303
 304                         try {
 305                                 cmd.ExecuteScalar ();
 306                                 Assert.Fail ("#C1");
 307                         } catch (InvalidOperationException ex) {
 308                                 // ExecuteScalar: CommandText property
 309                                 // has not been initialized
 310                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#C2");
 311                                 Assert.IsNull (ex.InnerException, "#C3");
 312                                 Assert.IsNotNull (ex.Message, "#C4");
 313                                 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar"), "#C5:" + ex.Message);
 314                         }
 315                 }
 316
 317                 [Test]
 318                 public void ExecuteScalar_Connection_PendingTransaction ()
 319                 {
 320                         conn = new SqlConnection (connectionString);
 321                         conn.Open ();
 322
 323                         using (SqlTransaction trans = conn.BeginTransaction ()) {
 324                                 cmd = new SqlCommand ("select @@version", conn);
 325
 326                                 try {
 327                                         cmd.ExecuteScalar ();
 328                                         Assert.Fail ("#1");
 329                                 } catch (InvalidOperationException ex) {
 330                                         // ExecuteScalar requires the command
 331                                         // to have a transaction object when the
 332                                         // connection assigned to the command is
 333                                         // in a pending local transaction.  The
 334                                         // Transaction property of the command
 335                                         // has not been initialized
 336                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
 337                                         Assert.IsNull (ex.InnerException, "#3");
 338                                         Assert.IsNotNull (ex.Message, "#4");
 339                                         Assert.IsTrue (ex.Message.IndexOf ("ExecuteScalar") != -1, "#5:" + ex.Message);
 340                                 }
 341                         }
 342                 }
 343
 344                 [Test]
 345                 public void ExecuteScalar_Query_Invalid ()
 346                 {
 347                         conn = new SqlConnection (connectionString);
 348                         conn.Open ();
 349
 350                         cmd = new SqlCommand ("InvalidQuery", conn);
 351                         try {
 352                                 cmd.ExecuteScalar ();
 353                                 Assert.Fail ("#1");
 354                         } catch (SqlException ex) {
 355                                 // Could not find stored procedure 'InvalidQuery'
 356                                 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
 357                                 Assert.AreEqual ((byte) 16, ex.Class, "#3");
 358                                 Assert.IsNull (ex.InnerException, "#4");
 359                                 Assert.IsNotNull (ex.Message, "#5");
 360                                 Assert.IsTrue (ex.Message.IndexOf ("'InvalidQuery'") != -1, "#6:" + ex.Message);
 361                                 Assert.AreEqual (2812, ex.Number, "#7");
 362                                 Assert.AreEqual ((byte) 62, ex.State, "#8");
 363                         }
 364                 }
 365
 366                 [Test]
 367                 public void ExecuteScalar_Transaction_NotAssociated ()
 368                 {
 369                         SqlTransaction trans = null;
 370                         SqlConnection connA = null;
 371                         SqlConnection connB = null;
 372
 373                         try {
 374                                 connA = new SqlConnection (connectionString);
 375                                 connA.Open ();
 376
 377                                 connB = new SqlConnection (connectionString);
 378                                 connB.Open ();
 379
 380                                 trans = connA.BeginTransaction ();
 381
 382                                 cmd = new SqlCommand ("select @@version", connB, trans);
 383
 384                                 try {
 385                                         cmd.ExecuteScalar ();
 386                                         Assert.Fail ("#A1");
 387                                 } catch (InvalidOperationException ex) {
 388                                         // The transaction object is not associated
 389                                         // with the connection object
 390                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
 391                                         Assert.IsNull (ex.InnerException, "#A3");
 392                                         Assert.IsNotNull (ex.Message, "#A4");
 393                                 } finally {
 394                                         cmd.Dispose ();
 395                                 }
 396
 397                                 cmd = new SqlCommand ("select @@version", connB);
 398                                 cmd.Transaction = trans;
 399
 400                                 try {
 401                                         cmd.ExecuteScalar ();
 402                                         Assert.Fail ("#B1");
 403                                 } catch (InvalidOperationException ex) {
 404                                         // The transaction object is not associated
 405                                         // with the connection object
 406                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
 407                                         Assert.IsNull (ex.InnerException, "#B3");
 408                                         Assert.IsNotNull (ex.Message, "#B4");
 409                                 } finally {
 410                                         cmd.Dispose ();
 411                                 }
 412                         } finally {
 413                                 if (trans != null)
 414                                         trans.Dispose ();
 415                                 if (connA != null)
 416                                         connA.Close ();
 417                                 if (connB != null)
 418                                         connB.Close ();
 419                         }
 420                 }
 421
 422                 [Test]
 423                 public void ExecuteScalar_Transaction_Only ()
 424                 {
 425                         SqlTransaction trans = null;
 426
 427                         conn = new SqlConnection (connectionString);
 428                         conn.Open ();
 429                         trans = conn.BeginTransaction ();
 430
 431                         cmd = new SqlCommand ("select @@version");
 432                         cmd.Transaction = trans;
 433
 434                         try {
 435                                 cmd.ExecuteScalar ();
 436                                 Assert.Fail ("#1");
 437                         } catch (InvalidOperationException ex) {
 438                                 // ExecuteScalar: Connection property has not
 439                                 // been initialized
 440                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
 441                                 Assert.IsNull (ex.InnerException, "#3");
 442                                 Assert.IsNotNull (ex.Message, "#4");
 443                                 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar:"), "#5");
 444                         } finally {
 445                                 trans.Dispose ();
 446                         }
 447                 }
 448
 449                 [Test]
 450                 [Category("NotWorking")]
 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                 [Category("NotWorking")]
1110                 public void Prepare_Transaction_Only ()
1111                 {
1112                         SqlTransaction trans = null;
1113
1114                         conn = new SqlConnection (connectionString);
1115                         conn.Open ();
1116                         trans = conn.BeginTransaction ();
1117
1118                         // Text, without parameters
1119                         cmd = new SqlCommand ("select count(*) from whatever");
1120                         cmd.Transaction = trans;
1121                         cmd.Prepare();
1122
1123                         // Text, with parameters
1124                         cmd = new SqlCommand ("select count(*) from whatever");
1125                         cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1126                         cmd.Transaction = trans;
1127                         Assert.Throws<InvalidOperationException>(() => cmd.Prepare());
1128
1129                         // Text, parameters cleared
1130                         cmd = new SqlCommand ("select count(*) from whatever");
1131                         cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1132                         cmd.Parameters.Clear ();
1133                         cmd.Transaction = trans;
1134                         cmd.Prepare();
1135
1136                         // StoredProcedure, without parameters
1137                         cmd = new SqlCommand ("FindCustomer");
1138                         cmd.CommandType = CommandType.StoredProcedure;
1139                         cmd.Transaction = trans;
1140                         cmd.Prepare();
1141
1142                         // StoredProcedure, with parameters
1143                         cmd = new SqlCommand ("FindCustomer");
1144                         cmd.CommandType = CommandType.StoredProcedure;
1145                         cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1146                         cmd.Transaction = trans;
1147                         cmd.Prepare();
1148                 }
1149
1150                 [Test] // bug #412576
1151                 public void Connection ()
1152                 {
1153                         SqlConnection connA = null;
1154                         SqlConnection connB = null;
1155                         SqlTransaction trans = null;
1156
1157                         try {
1158                                 connA = new SqlConnection (connectionString);
1159                                 connA.Open ();
1160
1161                                 connB = new SqlConnection (connectionString);
1162                                 connB.Open ();
1163
1164                                 cmd = connA.CreateCommand ();
1165                                 cmd.Connection = connB;
1166                                 Assert.AreSame (connB, cmd.Connection, "#A1");
1167                                 Assert.IsNull (cmd.Transaction, "#A2");
1168                                 cmd.Dispose ();
1169
1170                                 trans = connA.BeginTransaction ();
1171                                 cmd = new SqlCommand ("select @@version", connA, trans);
1172                                 cmd.Connection = connB;
1173                                 Assert.AreSame (connB, cmd.Connection, "#B1");
1174                                 Assert.AreSame (trans, cmd.Transaction, "#B2");
1175                                 trans.Dispose ();
1176
1177                                 trans = connA.BeginTransaction ();
1178                                 cmd = new SqlCommand ("select @@version", connA, trans);
1179                                 trans.Rollback ();
1180                                 Assert.AreSame (connA, cmd.Connection, "#C1");
1181                                 Assert.IsNull (cmd.Transaction, "#C2");
1182                                 cmd.Connection = connB;
1183                                 Assert.AreSame (connB, cmd.Connection, "#C3");
1184                                 Assert.IsNull (cmd.Transaction, "#C4");
1185
1186                                 trans = connA.BeginTransaction ();
1187                                 cmd = new SqlCommand ("select @@version", connA, trans);
1188                                 cmd.Connection = null;
1189                                 Assert.IsNull (cmd.Connection, "#D1");
1190                                 Assert.AreSame (trans, cmd.Transaction, "#D2");
1191                         } finally {
1192                                 if (trans != null)
1193                                         trans.Dispose ();
1194                                 if (connA != null)
1195                                         connA.Close ();
1196                                 if (connB != null)
1197                                         connB.Close ();
1198                         }
1199                 }
1200
1201                 [Test]
1202                 public void Connection_Reader_Open ()
1203                 {
1204                         SqlConnection connA = null;
1205                         SqlConnection connB = null;
1206                         SqlTransaction trans = null;
1207
1208                         try {
1209                                 connA = new SqlConnection (connectionString);
1210                                 connA.Open ();
1211
1212                                 connB = new SqlConnection (connectionString);
1213                                 connB.Open ();
1214
1215                                 trans = connA.BeginTransaction ();
1216                                 SqlCommand cmdA = new SqlCommand ("select @@version", connA, trans);
1217
1218                                 SqlCommand cmdB = new SqlCommand ("select @@version", connA, trans);
1219                                 using (SqlDataReader reader = cmdB.ExecuteReader ()) {
1220                                         cmdA.Connection = connA;
1221                                         Assert.AreSame (connA, cmdA.Connection, "#A1");
1222                                         Assert.AreSame (trans, cmdA.Transaction, "#A2");
1223
1224                                         cmdA.Connection = connB;
1225                                         Assert.AreSame (connB, cmdA.Connection, "#B1");
1226                                         Assert.AreSame (trans, cmdA.Transaction, "#B2");
1227
1228                                         cmdA.Connection = null;
1229                                         Assert.IsNull (cmdA.Connection, "#C1");
1230                                         Assert.AreSame (trans, cmdA.Transaction, "#C2");
1231                                 }
1232                         } finally {
1233                                 if (trans != null)
1234                                         trans.Dispose ();
1235                                 if (connA != null)
1236                                         connA.Close ();
1237                                 if (connB != null)
1238                                         connB.Close ();
1239                         }
1240                 }
1241
1242                 [Test]
1243                 public void Transaction ()
1244                 {
1245                         SqlConnection connA = null;
1246                         SqlConnection connB = null;
1247
1248                         SqlTransaction transA = null;
1249                         SqlTransaction transB = null;
1250
1251                         try {
1252                                 connA = new SqlConnection (connectionString);
1253                                 connA.Open ();
1254
1255                                 connB = new SqlConnection (connectionString);
1256                                 connB.Open ();
1257
1258                                 transA = connA.BeginTransaction ();
1259                                 transB = connB.BeginTransaction ();
1260
1261                                 SqlCommand cmd = new SqlCommand ("select @@version", connA, transA);
1262                                 cmd.Transaction = transA;
1263                                 Assert.AreSame (connA, cmd.Connection, "#A1");
1264                                 Assert.AreSame (transA, cmd.Transaction, "#A2");
1265                                 cmd.Transaction = transB;
1266                                 Assert.AreSame (connA, cmd.Connection, "#B1");
1267                                 Assert.AreSame (transB, cmd.Transaction, "#B2");
1268                                 cmd.Transaction = null;
1269                                 Assert.AreSame (connA, cmd.Connection, "#C1");
1270                                 Assert.IsNull (cmd.Transaction, "#C2");
1271                         } finally {
1272                                 if (transA != null)
1273                                         transA.Dispose ();
1274                                 if (transB != null)
1275                                         transA.Dispose ();
1276                                 if (connA != null)
1277                                         connA.Close ();
1278                                 if (connB != null)
1279                                         connB.Close ();
1280                         }
1281                 }
1282
1283                 [Test] // bug #412579
1284                 public void Transaction_Reader_Open ()
1285                 {
1286                         SqlConnection connA = null;
1287                         SqlConnection connB = null;
1288
1289                         SqlTransaction transA = null;
1290                         SqlTransaction transB = null;
1291
1292                         try {
1293                                 connA = new SqlConnection (connectionString);
1294                                 connA.Open ();
1295
1296                                 connB = new SqlConnection (connectionString);
1297                                 connB.Open ();
1298
1299                                 transA = connA.BeginTransaction ();
1300                                 transB = connB.BeginTransaction ();
1301
1302                                 SqlCommand cmdA = new SqlCommand ("select * from employee", connA, transA);
1303
1304                                 SqlCommand cmdB = new SqlCommand ("select * from employee", connA, transA);
1305                                 using (SqlDataReader reader = cmdB.ExecuteReader ()) {
1306                                         cmdA.Transaction = transA;
1307                                         Assert.AreSame (transA, cmdA.Transaction, "#A1");
1308
1309                                         cmdA.Transaction = transB;
1310                                         Assert.AreSame (transB, cmdA.Transaction, "#B1");
1311
1312                                         cmdA.Transaction = null;
1313                                         Assert.IsNull (cmdA.Transaction, "#C1");
1314                                 }
1315
1316                                 cmdA.Transaction = transA;
1317                                 Assert.AreSame (transA, cmdA.Transaction, "#D1");
1318                                 cmdA.Transaction = transB;
1319                                 Assert.AreSame (transB, cmdA.Transaction, "#D2");
1320                         } finally {
1321                                 if (transA != null)
1322                                         transA.Dispose ();
1323                                 if (transB != null)
1324                                         transA.Dispose ();
1325                                 if (connA != null)
1326                                         connA.Close ();
1327                                 if (connB != null)
1328                                         connB.Close ();
1329                         }
1330                 }
1331
1332                 [Test]
1333                 public void ExecuteNonQuery_StoredProcedure ()
1334                 {
1335                         SqlParameter param;
1336                         SqlCommand cmd = null;
1337                         SqlDataReader dr = null;
1338                         SqlParameter idParam;
1339                         SqlParameter dojParam;
1340
1341                         conn = ConnectionManager.Instance.Sql.Connection;
1342
1343                         // parameters with leading '@'
1344                         try {
1345                                 // create temp sp here, should normally be created in Setup of test
1346                                 // case, but cannot be done right now because of bug #68978
1347                                 DBHelper.ExecuteNonQuery (conn, CREATE_TMP_SP_TEMP_INSERT_PERSON);
1348
1349                                 cmd = conn.CreateCommand ();
1350                                 cmd.CommandText = "#sp_temp_insert_employee";
1351                                 cmd.CommandType = CommandType.StoredProcedure;
1352                                 param = cmd.Parameters.Add ("@fname", SqlDbType.VarChar);
1353                                 param.Value = "testA";
1354                                 dojParam = cmd.Parameters.Add ("@doj", SqlDbType.DateTime);
1355                                 dojParam.Direction = ParameterDirection.Output;
1356                                 param = cmd.Parameters.Add ("@dob", SqlDbType.DateTime);
1357                                 param.Value = new DateTime (2004, 8, 20);
1358                                 idParam = cmd.Parameters.Add ("@id", SqlDbType.Int);
1359                                 idParam.Direction = ParameterDirection.ReturnValue;
1360
1361                                 Assert.AreEqual (1, cmd.ExecuteNonQuery (), "#A1");
1362                                 cmd.Dispose ();
1363
1364                                 cmd = conn.CreateCommand ();
1365                                 cmd.CommandText = "select fname, dob, doj from employee where id = @id";
1366                                 param = cmd.Parameters.Add ("@id", SqlDbType.Int);
1367                                 param.Value = idParam.Value;
1368
1369                                 dr = cmd.ExecuteReader ();
1370                                 Assert.IsTrue (dr.Read (), "#A2");
1371                                 Assert.AreEqual (typeof (string), dr.GetFieldType (0), "#A3");
1372                                 Assert.AreEqual ("testA", dr.GetValue (0), "#A4");
1373                                 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (1), "#A5");
1374                                 Assert.AreEqual (new DateTime (2004, 8, 20), dr.GetValue (1), "#A6");
1375                                 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (2), "#A7");
1376                                 Assert.AreEqual (dojParam.Value, dr.GetValue (2), "#A8");
1377                                 Assert.IsFalse (dr.Read (), "#A9");
1378                                 cmd.Dispose ();
1379                                 dr.Close ();
1380                         } finally {
1381                                 if (cmd != null)
1382                                         cmd.Dispose ();
1383                                 if (dr != null)
1384                                         dr.Close ();
1385                                 DBHelper.ExecuteNonQuery (conn, DROP_TMP_SP_TEMP_INSERT_PERSON);
1386                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
1387                                 conn.Close ();
1388                         }
1389
1390                         conn.Open ();
1391
1392                         // parameters without leading '@'
1393                         try {
1394                                 // create temp sp here, should normally be created in Setup of test
1395                                 // case, but cannot be done right now because of bug #68978
1396                                 DBHelper.ExecuteNonQuery (conn, CREATE_TMP_SP_TEMP_INSERT_PERSON);
1397
1398                                 cmd = conn.CreateCommand ();
1399                                 cmd.CommandText = "#sp_temp_insert_employee";
1400                                 cmd.CommandType = CommandType.StoredProcedure;
1401                                 param = cmd.Parameters.Add ("fname", SqlDbType.VarChar);
1402                                 param.Value = "testB";
1403                                 dojParam = cmd.Parameters.Add ("doj", SqlDbType.DateTime);
1404                                 dojParam.Direction = ParameterDirection.Output;
1405                                 param = cmd.Parameters.Add ("dob", SqlDbType.DateTime);
1406                                 param.Value = new DateTime (2004, 8, 20);
1407                                 idParam = cmd.Parameters.Add ("id", SqlDbType.Int);
1408                                 idParam.Direction = ParameterDirection.ReturnValue;
1409
1410                                 Assert.AreEqual (1, cmd.ExecuteNonQuery (), "#B1");
1411                                 cmd.Dispose ();
1412
1413                                 cmd = conn.CreateCommand ();
1414                                 cmd.CommandText = "select fname, dob, doj from employee where id = @id";
1415                                 param = cmd.Parameters.Add ("id", SqlDbType.Int);
1416                                 param.Value = idParam.Value;
1417
1418                                 dr = cmd.ExecuteReader ();
1419                                 Assert.IsTrue (dr.Read (), "#B2");
1420                                 Assert.AreEqual (typeof (string), dr.GetFieldType (0), "#B3");
1421                                 Assert.AreEqual ("testB", dr.GetValue (0), "#B4");
1422                                 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (1), "#B5");
1423                                 Assert.AreEqual (new DateTime (2004, 8, 20), dr.GetValue (1), "#B6");
1424                                 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (2), "#B7");
1425                                 Assert.AreEqual (dojParam.Value, dr.GetValue (2), "#B8");
1426                                 Assert.IsFalse (dr.Read (), "#B9");
1427                                 cmd.Dispose ();
1428                                 dr.Close ();
1429                         } finally {
1430                                 if (cmd != null)
1431                                         cmd.Dispose ();
1432                                 if (dr != null)
1433                                         dr.Close ();
1434                                 DBHelper.ExecuteNonQuery (conn, DROP_TMP_SP_TEMP_INSERT_PERSON);
1435                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
1436                                 conn.Close ();
1437                         }
1438                 }
1439
1440                 [Test] // bug #319598
1441                 public void LongQueryTest ()
1442                 {
1443                         if (ClientVersion == 7)
1444                                 Assert.Ignore ("Hangs on SQL Server 7.0");
1445
1446                         SqlConnection conn = new SqlConnection (
1447                                                         connectionString + ";Pooling=false");
1448                         using (conn) {
1449                                 conn.Open ();
1450                                 SqlCommand cmd = conn.CreateCommand ();
1451                                 String value = new String ('a', 10000);
1452                                 cmd.CommandText = String.Format ("Select '{0}'", value);
1453                                 cmd.ExecuteNonQuery ();
1454                         }
1455                 }
1456
1457                 [Test] // bug #319598
1458                 [Category("NotWorking")]
1459                 public void LongStoredProcTest ()
1460                 {
1461                         if (ClientVersion == 7)
1462                                 Assert.Ignore ("Hangs on SQL Server 7.0");
1463
1464                         SqlConnection conn = new SqlConnection (
1465                                                         connectionString + ";Pooling=false");
1466                         using (conn) {
1467                                 conn.Open ();
1468                                 /*int size = conn.PacketSize;*/
1469                                 SqlCommand cmd = conn.CreateCommand ();
1470                                 // create a temp stored proc
1471                                 cmd.CommandText = "Create Procedure #sp_tmp_long_params ";
1472                                 cmd.CommandText += "@p1 nvarchar (4000), ";
1473                                 cmd.CommandText += "@p2 nvarchar (4000), ";
1474                                 cmd.CommandText += "@p3 nvarchar (4000), ";
1475                                 cmd.CommandText += "@p4 nvarchar (4000) out ";
1476                                 cmd.CommandText += "As ";
1477                                 cmd.CommandText += "Begin ";
1478                                 cmd.CommandText += "Set @p4 = N'Hello' ";
1479                                 cmd.CommandText += "Return 2 ";
1480                                 cmd.CommandText += "End";
1481                                 cmd.ExecuteNonQuery ();
1482
1483                                 //execute the proc
1484                                 cmd.CommandType = CommandType.StoredProcedure;
1485                                 cmd.CommandText = "#sp_tmp_long_params";
1486
1487                                 String value = new String ('a', 4000);
1488                                 SqlParameter p1 = new SqlParameter ("@p1",
1489                                                         SqlDbType.NVarChar, 4000);
1490                                 p1.Value = value;
1491
1492                                 SqlParameter p2 = new SqlParameter ("@p2",
1493                                                         SqlDbType.NVarChar, 4000);
1494                                 p2.Value = value;
1495
1496                                 SqlParameter p3 = new SqlParameter ("@p3",
1497                                                         SqlDbType.NVarChar, 4000);
1498                                 p3.Value = value;
1499
1500                                 SqlParameter p4 = new SqlParameter ("@p4",
1501                                                         SqlDbType.NVarChar, 4000);
1502                                 p4.Direction = ParameterDirection.Output;
1503
1504                                 // for now, name shud be @RETURN_VALUE
1505                                 // can be changed once RPC is implemented
1506                                 SqlParameter p5 = new SqlParameter ("@RETURN_VALUE", SqlDbType.Int);
1507                                 p5.Direction = ParameterDirection.ReturnValue;
1508
1509                                 cmd.Parameters.Add (p1);
1510                                 cmd.Parameters.Add (p2);
1511                                 cmd.Parameters.Add (p3);
1512                                 cmd.Parameters.Add (p4);
1513                                 cmd.Parameters.Add (p5);
1514
1515                                 cmd.ExecuteNonQuery ();
1516                                 Assert.AreEqual ("Hello", p4.Value, "#1");
1517                                 Assert.AreEqual (2, p5.Value, "#2");
1518                         }
1519                 }
1520
1521                 [Test] // bug #319694
1522                 public void DateTimeParameterTest ()
1523                 {
1524                         SqlConnection conn = new SqlConnection (connectionString);
1525                         using (conn) {
1526                                 conn.Open ();
1527                                 SqlCommand cmd = conn.CreateCommand ();
1528                                 cmd.CommandText = "select * from datetime_family where type_datetime=@p1";
1529                                 cmd.Parameters.Add ("@p1", SqlDbType.DateTime).Value = "10-10-2005";
1530                                 // shudnt cause and exception
1531                                 SqlDataReader rdr = cmd.ExecuteReader ();
1532                                 rdr.Close ();
1533                         }
1534                 }
1535
1536                 /**
1537                  * Verifies whether an enum value is converted to a numeric value when
1538                  * used as value for a numeric parameter (bug #66630)
1539                  */
1540                 [Test]
1541                 public void EnumParameterTest ()
1542                 {
1543                         conn = ConnectionManager.Instance.Sql.Connection;
1544                         try {
1545                                 // create temp sp here, should normally be created in Setup of test
1546                                 // case, but cannot be done right now because of ug #68978
1547                                 DBHelper.ExecuteNonQuery (conn, "CREATE PROCEDURE #Bug66630 ("
1548                                                           + "@Status smallint = 7"
1549                                                           + ")"
1550                                                           + "AS" + Environment.NewLine
1551                                                           + "BEGIN" + Environment.NewLine
1552                                                           + "SELECT CAST(5 AS int), @Status" + Environment.NewLine
1553                                                           + "END");
1554
1555                                 SqlCommand cmd = new SqlCommand ("#Bug66630", conn);
1556                                 cmd.CommandType = CommandType.StoredProcedure;
1557                                 cmd.Parameters.Add ("@Status", SqlDbType.Int).Value = Status.Error;
1558
1559                                 using (SqlDataReader dr = cmd.ExecuteReader ()) {
1560                                         // one record should be returned
1561                                         Assert.IsTrue (dr.Read (), "EnumParameterTest#1");
1562                                         // we should get two field in the result
1563                                         Assert.AreEqual (2, dr.FieldCount, "EnumParameterTest#2");
1564                                         // field 1
1565                                         Assert.AreEqual ("int", dr.GetDataTypeName (0), "EnumParameterTest#3");
1566                                         Assert.AreEqual (5, dr.GetInt32 (0), "EnumParameterTest#4");
1567                                         // field 2
1568                                         Assert.AreEqual ("smallint", dr.GetDataTypeName (1), "EnumParameterTest#5");
1569                                         Assert.AreEqual ((short) Status.Error, dr.GetInt16 (1), "EnumParameterTest#6");
1570                                         // only one record should be returned
1571                                         Assert.IsFalse (dr.Read (), "EnumParameterTest#7");
1572                                 }
1573                         } finally {
1574                                 DBHelper.ExecuteNonQuery (conn, "if exists (select name from sysobjects " +
1575                                                           " where name like '#temp_Bug66630' and type like 'P') " +
1576                                                           " drop procedure #temp_Bug66630; ");
1577                                 ConnectionManager.Instance.Sql.CloseConnection ();
1578                         }
1579                 }
1580
1581                 [Test]
1582                 public void CloneTest ()
1583                 {
1584                         conn = new SqlConnection (connectionString);
1585                         conn.Open ();
1586
1587                         SqlTransaction trans = conn.BeginTransaction ();
1588
1589                         cmd = new SqlCommand ();
1590                         cmd.Connection = conn;
1591                         cmd.Transaction = trans;
1592
1593                         SqlCommand clone = (((ICloneable) (cmd)).Clone ()) as SqlCommand;
1594                         Assert.AreSame (conn, clone.Connection);
1595                         Assert.AreSame (trans, clone.Transaction);
1596                 }
1597
1598                 [Test]
1599                 public void StoredProc_NoParameterTest ()
1600                 {
1601                         string query = "create procedure #tmp_sp_proc as begin";
1602                         query += " select 'data' end";
1603                         SqlConnection conn = new SqlConnection (connectionString);
1604                         SqlCommand cmd = conn.CreateCommand ();
1605                         cmd.CommandText = query;
1606                         conn.Open ();
1607                         cmd.ExecuteNonQuery ();
1608
1609                         cmd.CommandType = CommandType.StoredProcedure;
1610                         cmd.CommandText = "#tmp_sp_proc";
1611                         using (SqlDataReader reader = cmd.ExecuteReader ()) {
1612                                 if (reader.Read ())
1613                                         Assert.AreEqual ("data", reader.GetString (0), "#1");
1614                                 else
1615                                         Assert.Fail ("#2 Select shud return data");
1616                         }
1617                         conn.Close ();
1618                 }
1619
1620                 [Test]
1621                 [Category("NotWorking")]
1622                 public void StoredProc_ParameterTest ()
1623                 {
1624                         string create_query = CREATE_TMP_SP_PARAM_TEST;
1625
1626                         SqlConnection conn = new SqlConnection (connectionString);
1627                         conn.Open ();
1628
1629                         SqlCommand cmd = conn.CreateCommand ();
1630                         int label = 0;
1631                         string error = string.Empty;
1632                         while (label != -1) {
1633                                 try {
1634                                         switch (label) {
1635                                                 case 0:
1636                                                         // Test BigInt Param
1637                                                         DBHelper.ExecuteNonQuery (conn,
1638                                                                 String.Format (create_query, "bigint"));
1639                                                         rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1640                                                                 Int64.MaxValue, Int64.MaxValue,
1641                                                                 Int64.MaxValue, Int64.MaxValue);
1642                                                         rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1643                                                                 Int64.MinValue, Int64.MinValue,
1644                                                                 Int64.MinValue, Int64.MinValue);
1645                                                         rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1646                                                                 DBNull.Value, DBNull.Value,
1647                                                                 DBNull.Value, DBNull.Value);
1648                                                         break;
1649                                                 case 1:
1650                                                         // Test Binary Param
1651                                                         DBHelper.ExecuteNonQuery (conn,
1652                                                                 String.Format (create_query, "binary(5)"));
1653                                                         rpc_helper_function (cmd, SqlDbType.Binary, 5,
1654                                                                 new byte [] { 1, 2, 3, 4, 5 },
1655                                                                 new byte [] { 1, 2, 3, 4, 5 },
1656                                                                 new byte [] { 1, 2, 3, 4, 5 },
1657                                                                 new byte [] { 1, 2, 3, 4, 5 });
1658                                                         /*
1659                                                         rpc_helper_function (cmd, SqlDbType.Binary, 5,
1660                                                                 DBNull.Value, DBNull.Value,
1661                                                                 DBNull.Value);
1662                                                         */
1663                                                         rpc_helper_function (cmd, SqlDbType.Binary, 2,
1664                                                                 new byte [0],
1665                                                                 new byte [] { 0, 0, 0, 0, 0 },
1666                                                                 new byte [] { 0, 0 },
1667                                                                 new byte [] { 0, 0 });
1668                                                         break;
1669                                                 case 2:
1670                                                         // Test Bit Param
1671                                                         DBHelper.ExecuteNonQuery (conn,
1672                                                                 String.Format (create_query, "bit"));
1673                                                         rpc_helper_function (cmd, SqlDbType.Bit, 0,
1674                                                                 true, true, true, true);
1675                                                         rpc_helper_function (cmd, SqlDbType.Bit, 0,
1676                                                                 false, false, false, false);
1677                                                         rpc_helper_function (cmd, SqlDbType.Bit, 0,
1678                                                                 DBNull.Value, DBNull.Value,
1679                                                                 DBNull.Value, DBNull.Value);
1680                                                         break;
1681                                                 case 3:
1682                                                         // Testing Char
1683                                                         DBHelper.ExecuteNonQuery (conn,
1684                                                                 String.Format (create_query, "char(10)"));
1685                                                         rpc_helper_function (cmd, SqlDbType.Char, 10,
1686                                                                 "characters", "characters",
1687                                                                 "characters", "characters");
1688                                                         /*
1689                                                         rpc_helper_function (cmd, SqlDbType.Char, 3,
1690                                                                 "characters", "cha       ",
1691                                                                 "cha");
1692                                                         rpc_helper_function (cmd, SqlDbType.Char, 3,
1693                                                                 string.Empty, "          ",
1694                                                                 "   ");
1695                                                         */
1696                                                         rpc_helper_function (cmd, SqlDbType.Char, 5,
1697                                                                 DBNull.Value, DBNull.Value,
1698                                                                 DBNull.Value, DBNull.Value);
1699                                                         break;
1700                                                 case 4:
1701                                                         // Testing DateTime
1702                                                         DBHelper.ExecuteNonQuery (conn,
1703                                                                 String.Format (create_query, "datetime"));
1704                                                         rpc_helper_function (cmd, SqlDbType.DateTime, 0, "2079-06-06 23:59:00",
1705                                                                 new DateTime (2079, 6, 6, 23, 59, 0),
1706                                                                 new DateTime (2079, 6, 6, 23, 59, 0),
1707                                                                 new DateTime (2079, 6, 6, 23, 59, 0));
1708                                                         rpc_helper_function (cmd, SqlDbType.DateTime, 0, "2009-04-12 10:39:45",
1709                                                                 new DateTime (2009, 4, 12, 10, 39, 45),
1710                                                                 new DateTime (2009, 4, 12, 10, 39, 45),
1711                                                                 new DateTime (2009, 4, 12, 10, 39, 45));
1712                                                         rpc_helper_function (cmd, SqlDbType.DateTime, 0,
1713                                                                 DBNull.Value, DBNull.Value,
1714                                                                 DBNull.Value, DBNull.Value);
1715                                                         break;
1716                                                 case 5:
1717                                                         // Test Decimal Param
1718                                                         DBHelper.ExecuteNonQuery (conn,
1719                                                                 String.Format (create_query, "decimal(10,2)"));
1720                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1721                                                                 10.665m, 10.67m, 11m, 10.67m);
1722                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1723                                                                 0m, 0m, 0m, 0m);
1724                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1725                                                                 -5.657m, -5.66m, -6m, -5.66m);
1726                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1727                                                                 DBNull.Value, DBNull.Value,
1728                                                                 DBNull.Value, DBNull.Value);
1729
1730                                                         // conversion
1731                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1732                                                                 AttributeTargets.Constructor,
1733                                                                 32.0m, 32m, 32m);
1734                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1735                                                                 4.325f, 4.33m, 4m, 4.33m);
1736                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1737                                                                 10.0d, 10.00m, 10m, 10m);
1738                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1739                                                                 10.665d, 10.67m, 11m, 10.67m);
1740                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1741                                                                 -5.657d, -5.66m, -6m, -5.66m);
1742                                                         rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1743                                                                 4, 4m, 4m, 4m);
1744                                                         break;
1745                                                 case 6:
1746                                                         // Test Float Param
1747                                                         DBHelper.ExecuteNonQuery (conn,
1748                                                                 String.Format (create_query, "float"));
1749                                                         rpc_helper_function (cmd, SqlDbType.Float, 0,
1750                                                                 10.0, 10.0, 10.0, 10.0);
1751                                                         rpc_helper_function (cmd, SqlDbType.Float, 0,
1752                                                                 10.54, 10.54, 10.54, 10.54);
1753                                                         rpc_helper_function (cmd, SqlDbType.Float, 0,
1754                                                                 0, 0d, 0d, 0d);
1755                                                         rpc_helper_function (cmd, SqlDbType.Float, 0,
1756                                                                 -5.34, -5.34, -5.34, -5.34);
1757                                                         rpc_helper_function (cmd, SqlDbType.Float, 0,
1758                                                                 DBNull.Value, DBNull.Value,
1759                                                                 DBNull.Value, DBNull.Value);
1760                                                         break;
1761                                                 case 7:
1762                                                         // Testing Image
1763                                                         /* NOT WORKING
1764                                                            DBHelper.ExecuteNonQuery (conn,
1765                                                            String.Format(create_query, "image"));
1766                                                            rpc_helper_function (cmd, SqlDbType.Image, 0, );
1767                                                            rpc_helper_function (cmd, SqlDbType.Image, 0, );
1768                                                            rpc_helper_function (cmd, SqlDbType.Image, 0, );
1769                                                            /* NOT WORKING*/
1770                                                         break;
1771                                                 case 8:
1772                                                         // Test Integer Param
1773                                                         DBHelper.ExecuteNonQuery (conn,
1774                                                                 String.Format (create_query, "int"));
1775                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1776                                                                 10, 10, 10, 10);
1777                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1778                                                                 0, 0, 0, 0);
1779                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1780                                                                 -5, -5, -5, -5);
1781                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1782                                                                 int.MaxValue, int.MaxValue,
1783                                                                 int.MaxValue, int.MaxValue);
1784                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1785                                                                 int.MinValue, int.MinValue,
1786                                                                 int.MinValue, int.MinValue);
1787                                                         rpc_helper_function (cmd, SqlDbType.Int, 0,
1788                                                                 DBNull.Value, DBNull.Value,
1789                                                                 DBNull.Value, DBNull.Value);
1790                                                         break;
1791                                                 case 9:
1792                                                         // Test Money Param
1793                                                         DBHelper.ExecuteNonQuery (conn,
1794                                                                 String.Format (create_query, "money"));
1795                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1796                                                                 10m, 10m, 10m, 10m);
1797                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1798                                                                 10.54, 10.54m, 10.54m, 10.54m);
1799                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1800                                                                 0, 0m, 0m, 0m);
1801                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1802                                                                 -5.34, -5.34m, -5.34m, -5.34m);
1803                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1804                                                                 5.34, 5.34m, 5.34m, 5.34m);
1805                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1806                                                                 -10.1234m, -10.1234m, -10.1234m,
1807                                                                 -10.1234m);
1808                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1809                                                                 10.1234m, 10.1234m, 10.1234m,
1810                                                                 10.1234m);
1811                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1812                                                                 -2000000000m, -2000000000m,
1813                                                                 -2000000000m, -2000000000m);
1814                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1815                                                                 2000000000m, 2000000000m,
1816                                                                 2000000000m, 2000000000m);
1817                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1818                                                                 -200000000.2345m, -200000000.2345m,
1819                                                                 -200000000.2345m, -200000000.2345m);
1820                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1821                                                                 200000000.2345m, 200000000.2345m,
1822                                                                 200000000.2345m, 200000000.2345m);
1823                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1824                                                                 DBNull.Value, DBNull.Value,
1825                                                                 DBNull.Value, DBNull.Value);
1826
1827                                                         // rounding tests
1828                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1829                                                                 -200000000.234561m, -200000000.2346m,
1830                                                                 -200000000.2346m, -200000000.2346m);
1831                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1832                                                                 -200000000.234551m, -200000000.2346m,
1833                                                                 -200000000.2346m, -200000000.2346m);
1834                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1835                                                                 -200000000.234541m, -200000000.2345m,
1836                                                                 -200000000.2345m, -200000000.2345m);
1837                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1838                                                                 200000000.234561m, 200000000.2346m,
1839                                                                 200000000.2346m, 200000000.2346m);
1840                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1841                                                                 200000000.234551m, 200000000.2346m,
1842                                                                 200000000.2346m, 200000000.2346m);
1843                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1844                                                                 200000000.234541m, 200000000.2345m,
1845                                                                 200000000.2345m, 200000000.2345m);
1846                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1847                                                                 -200000000.234461m, -200000000.2345m,
1848                                                                 -200000000.2345m, -200000000.2345m);
1849                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1850                                                                 -200000000.234451m, -200000000.2345m,
1851                                                                 -200000000.2345m, -200000000.2345m);
1852                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1853                                                                 -200000000.234441m, -200000000.2344m,
1854                                                                 -200000000.2344m, -200000000.2344m);
1855                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1856                                                                 200000000.234461m, 200000000.2345m,
1857                                                                 200000000.2345m, 200000000.2345m);
1858                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1859                                                                 200000000.234451m, 200000000.2345m,
1860                                                                 200000000.2345m, 200000000.2345m);
1861                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1862                                                                 200000000.234441m, 200000000.2344m,
1863                                                                 200000000.2344m, 200000000.2344m);
1864                                                         // FIXME: we round toward even in SqlParameter.ConvertToFrameworkType
1865                                                         /*
1866                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1867                                                                 -200000000.234550m, -200000000.2346m, -200000000.2346m);
1868                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1869                                                                 200000000.234550m, 200000000.2346m, 200000000.2346m);
1870                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1871                                                                 -200000000.234450m, -200000000.2345m, -200000000.2345m);
1872                                                         rpc_helper_function (cmd, SqlDbType.Money, 0,
1873                                                                 200000000.234450m, 200000000.2345m, 200000000.2345m);
1874                                                         */
1875                                                         break;
1876                                                 case 23:
1877                                                         // Test NChar Param
1878                                                         DBHelper.ExecuteNonQuery (conn,
1879                                                                 String.Format (create_query, "nchar(10)"));
1880                                                         rpc_helper_function (cmd, SqlDbType.NChar, 10,
1881                                                                 "characters", "characters",
1882                                                                 "characters", "characters");
1883                                                         rpc_helper_function (cmd, SqlDbType.NChar, 3,
1884                                                                 "characters", "cha       ",
1885                                                                 "cha", "cha");
1886                                                         rpc_helper_function (cmd, SqlDbType.NChar, 3,
1887                                                                 string.Empty, "          ",
1888                                                                 "   ", "   ");
1889                                                         /*
1890                                                         rpc_helper_function (cmd, SqlDbType.NChar, 5,
1891                                                                 DBNull.Value, DBNull.Value,
1892                                                                 DBNull.Value);
1893                                                         */
1894                                                         break;
1895                                                 case 10:
1896                                                         // Test NText Param
1897                                                         DBHelper.ExecuteNonQuery (conn,
1898                                                                 String.Format (create_query, "ntext"));
1899                                                         /*
1900                                                         rpc_helper_function (cmd, SqlDbType.NText, 0, "ntext");
1901                                                         rpc_helper_function (cmd, SqlDbType.NText, 0, "");
1902                                                         rpc_helper_function (cmd, SqlDbType.NText, 0, null);
1903                                                         */
1904                                                         break;
1905                                                 case 11:
1906                                                         // Test NVarChar Param
1907                                                         DBHelper.ExecuteNonQuery (conn,
1908                                                                 String.Format (create_query, "nvarchar(10)"));
1909                                                         rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1910                                                                 "nvarchar", "nvarchar", "nvarchar",
1911                                                                 "nvarchar");
1912                                                         rpc_helper_function (cmd, SqlDbType.NVarChar, 3,
1913                                                                 "nvarchar", "nva", "nva", "nva");
1914                                                         /*
1915                                                         rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1916                                                                 string.Empty, string.Empty, string.Empty);
1917                                                         rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1918                                                                 DBNull.Value, DBNull.Value, DBNull.Value);
1919                                                         */
1920                                                         break;
1921                                                 case 12:
1922                                                         // Test Real Param
1923                                                         DBHelper.ExecuteNonQuery (conn,
1924                                                                 String.Format (create_query, "real"));
1925                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1926                                                                 10m, 10f, 10f, 10f);
1927                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1928                                                                 10d, 10f, 10f, 10f);
1929                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1930                                                                 0, 0f, 0f, 0f);
1931                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1932                                                                 3.54d, 3.54f, 3.54f, 3.54f);
1933                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1934                                                                 10, 10f, 10f, 10f);
1935                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1936                                                                 10.5f, 10.5f, 10.5f, 10.5f);
1937                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1938                                                                 3.5d, 3.5f, 3.5f, 3.5f);
1939                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1940                                                                 4.54m, 4.54f, 4.54f, 4.54f);
1941                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1942                                                                 -4.54m, -4.54f, -4.54f, -4.54f);
1943                                                         rpc_helper_function (cmd, SqlDbType.Real, 0,
1944                                                                 DBNull.Value, DBNull.Value,
1945                                                                 DBNull.Value, DBNull.Value);
1946                                                         break;
1947                                                 case 13:
1948                                                         // Test SmallDateTime Param
1949                                                         DBHelper.ExecuteNonQuery (conn,
1950                                                                 String.Format (create_query, "smalldatetime"));
1951                                                         rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0,
1952                                                                 "6/6/2079 11:59:00 PM",
1953                                                                 new DateTime (2079, 6, 6, 23, 59, 0),
1954                                                                 new DateTime (2079, 6, 6, 23, 59, 0),
1955                                                                 new DateTime (2079, 6, 6, 23, 59, 0));
1956                                                         rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0,
1957                                                                 DBNull.Value, DBNull.Value,
1958                                                                 DBNull.Value, DBNull.Value);
1959                                                         break;
1960                                                 case 14:
1961                                                         // Test SmallInt Param
1962                                                         DBHelper.ExecuteNonQuery (conn,
1963                                                                 String.Format (create_query, "smallint"));
1964                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1965                                                                 10, (short) 10, (short) 10, (short) 10);
1966                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1967                                                                 -10, (short) -10, (short) -10,
1968                                                                 (short) -10);
1969                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1970                                                                 short.MaxValue, short.MaxValue,
1971                                                                 short.MaxValue, short.MaxValue);
1972                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1973                                                                 short.MinValue, short.MinValue,
1974                                                                 short.MinValue, short.MinValue);
1975                                                         rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1976                                                                 DBNull.Value, DBNull.Value,
1977                                                                 DBNull.Value, DBNull.Value);
1978                                                         break;
1979                                                 case 15:
1980                                                         // Test SmallMoney Param
1981                                                         DBHelper.ExecuteNonQuery (conn,
1982                                                                         String.Format (create_query, "smallmoney"));
1983                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1984                                                                 10.0d, 10m, 10m, 10m);
1985                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1986                                                                 0, 0m, 0m, 0m);
1987                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1988                                                                 3.54d, 3.54m, 3.54m, 3.54m);
1989                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1990                                                                 10, 10m, 10m, 10m);
1991                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1992                                                                 10.5f, 10.5m, 10.5m, 10.5m);
1993                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1994                                                                 3.5d, 3.5m, 3.5m, 3.5m);
1995                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1996                                                                 4.54m, 4.54m, 4.54m, 4.54m);
1997                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1998                                                                 -4.54m, -4.54m, -4.54m, -4.54m);
1999                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2000                                                                 -214748.3648m, -214748.3648m,
2001                                                                 -214748.3648m, -214748.3648m);
2002                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2003                                                                 214748.3647m, 214748.3647m, 214748.3647m,
2004                                                                 214748.3647m);
2005                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2006                                                                 DBNull.Value, DBNull.Value, DBNull.Value,
2007                                                                 DBNull.Value);
2008
2009                                                         // rounding tests
2010                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2011                                                                 -4.543361m, -4.5434m, -4.5434m, -4.5434m);
2012                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2013                                                                 -4.543351m, -4.5434m, -4.5434m, -4.5434m);
2014                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2015                                                                 -4.543341m, -4.5433m, -4.5433m, -4.5433m);
2016                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2017                                                                 4.543361m, 4.5434m, 4.5434m, 4.5434m);
2018                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2019                                                                 4.543351m, 4.5434m, 4.5434m, 4.5434m);
2020                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2021                                                                 4.543341m, 4.5433m, 4.5433m, 4.5433m);
2022                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2023                                                                 -4.543261m, -4.5433m, -4.5433m, -4.5433m);
2024                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2025                                                                 -4.543251m, -4.5433m, -4.5433m, -4.5433m);
2026                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2027                                                                 -4.543241m, -4.5432m, -4.5432m, -4.5432m);
2028                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2029                                                                 4.543261m, 4.5433m, 4.5433m, 4.5433m);
2030                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2031                                                                 4.543251m, 4.5433m, 4.5433m, 4.5433m);
2032                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2033                                                                 4.543241m, 4.5432m, 4.5432m, 4.5432m);
2034                                                         // FIXME: we round toward even in SqlParameter.ConvertToFrameworkType
2035                                                         /*
2036                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2037                                                                 -4.543350m, -4.5434m, -4.5434m);
2038                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2039                                                                 4.543350m, 4.5434m, 4.5434m);
2040                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2041                                                                 -4.543250m, -4.5433m, -4.5433m);
2042                                                         rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2043                                                                 4.543250m, 4.5433m, 4.5433m);
2044                                                         */
2045                                                         break;
2046                                                 case 16:
2047                                                         // Test Text Param
2048                                                         DBHelper.ExecuteNonQuery (conn,
2049                                                                         String.Format (create_query, "text"));
2050                                                         /*
2051                                                         rpc_helper_function (cmd, SqlDbType.Text, 0, "text");
2052                                                         rpc_helper_function (cmd, SqlDbType.Text, 0, "");
2053                                                         rpc_helper_function (cmd, SqlDbType.Text, 0, null);
2054                                                         */
2055                                                         break;
2056                                                 case 17:
2057                                                         // Test TimeStamp Param
2058                                                         /* NOT WORKING
2059                                                            DBHelper.ExecuteNonQuery (conn,
2060                                                            String.Format(create_query,"timestamp"));
2061                                                            rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, "");
2062                                                            rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, "");
2063                                                            rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, null);
2064                                                          */
2065                                                         break;
2066                                                 case 18:
2067                                                         // Test TinyInt Param
2068                                                         DBHelper.ExecuteNonQuery (conn,
2069                                                                         String.Format (create_query, "tinyint"));
2070                                                         rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2071                                                                 10.0d, (byte) 10, (byte) 10,
2072                                                                 (byte) 10);
2073                                                         rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2074                                                                 0, (byte) 0, (byte) 0, (byte) 0);
2075                                                         rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2076                                                                 byte.MaxValue, byte.MaxValue,
2077                                                                 byte.MaxValue, byte.MaxValue);
2078                                                         rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2079                                                                 byte.MinValue, byte.MinValue,
2080                                                                 byte.MinValue, byte.MinValue);
2081                                                         break;
2082                                                 case 19:
2083                                                         // Test UniqueIdentifier Param
2084                                                         /*
2085                                                         DBHelper.ExecuteNonQuery (conn,
2086                                                                         String.Format(create_query,"uniqueidentifier"));
2087                                                         rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, "0f159bf395b1d04f8c2ef5c02c3add96");
2088                                                         rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, null);
2089                                                         */
2090                                                         break;
2091                                                 case 20:
2092                                                         // Test VarBinary Param
2093                                                         /* NOT WORKING
2094                                                            DBHelper.ExecuteNonQuery (conn,
2095                                                            String.Format(create_query,"varbinary (10)"));
2096                                                            rpc_helper_function (cmd, SqlDbType.VarBinary, 0,);
2097                                                            rpc_helper_function (cmd, SqlDbType.VarBinary, 0,);
2098                                                            rpc_helper_function (cmd, SqlDbType.VarBinary, 0, null);
2099                                                          */
2100                                                         break;
2101                                                 case 21:
2102                                                         // Test Varchar Param
2103                                                         DBHelper.ExecuteNonQuery (conn,
2104                                                                         String.Format (create_query, "varchar(10)"));
2105                                                         rpc_helper_function (cmd, SqlDbType.VarChar, 7,
2106                                                                 "VarChar", "VarChar", "VarChar",
2107                                                                 "VarChar");
2108                                                         rpc_helper_function (cmd, SqlDbType.VarChar, 5,
2109                                                                 "Var", "Var", "Var", "Var");
2110                                                         /*
2111                                                         rpc_helper_function (cmd, SqlDbType.VarChar, 3,
2112                                                                 "Varchar", "Var", "Var");
2113                                                         rpc_helper_function (cmd, SqlDbType.VarChar, 10,
2114                                                                 string.Empty, string.Empty, string.Empty);
2115                                                         rpc_helper_function (cmd, SqlDbType.VarChar, 10,
2116                                                                 DBNull.Value, DBNull.Value,
2117                                                                 DBNull.Value);
2118                                                         */
2119                                                         break;
2120                                                 case 22:
2121                                                         // Test Variant Param
2122                                                         /* NOT WORKING
2123                                                            DBHelper.ExecuteNonQuery (conn,
2124                                                            String.Format(create_query,"variant"));
2125                                                            rpc_helper_function (cmd, SqlDbType.Variant, 0, );
2126                                                            rpc_helper_function (cmd, SqlDbType.Variant, 0, );
2127                                                            rpc_helper_function (cmd, SqlDbType.Variant, 0, null);
2128                                                          */
2129                                                         break;
2130                                                 default:
2131                                                         label = -2;
2132                                                         break;
2133                                         }
2134                                 } catch (AssertionException ex) {
2135                                         error += String.Format (" Case {0} INCORRECT VALUE : {1}\n", label, ex.ToString ());
2136                                 } catch (Exception ex) {
2137                                         error += String.Format (" Case {0} NOT WORKING : {1}\n", label, ex.ToString ());
2138                                 }
2139
2140                                 label++;
2141                                 if (label != -1)
2142                                         DBHelper.ExecuteNonQuery (conn, string.Format (
2143                                                 CultureInfo.InvariantCulture,
2144                                                 DROP_STORED_PROCEDURE, "#tmp_sp_param_test"));
2145                         }
2146
2147                         if (error.Length != 0)
2148                                 Assert.Fail (error);
2149                 }
2150
2151                 private void rpc_helper_function (SqlCommand cmd, SqlDbType type, int size, object input, object expectedRead, object expectedOut, object expectedInOut)
2152                 {
2153                         cmd.Parameters.Clear ();
2154                         SqlParameter param1, param2, param3;
2155                         if (size != 0) {
2156                                 param1 = new SqlParameter ("@param1", type, size);
2157                                 param2 = new SqlParameter ("@param2", type, size);
2158                                 param3 = new SqlParameter ("@param3", type, size);
2159                         } else {
2160                                 param1 = new SqlParameter ("@param1", type);
2161                                 param2 = new SqlParameter ("@param2", type);
2162                                 param3 = new SqlParameter ("@param3", type);
2163                         }
2164
2165                         SqlParameter retval = new SqlParameter ("retval", SqlDbType.Int);
2166                         param1.Value = input;
2167                         param1.Direction = ParameterDirection.Input;
2168                         param2.Direction = ParameterDirection.Output;
2169                         param3.Direction = ParameterDirection.InputOutput;
2170                         param3.Value = input;
2171                         retval.Direction = ParameterDirection.ReturnValue;
2172                         cmd.Parameters.Add (param1);
2173                         cmd.Parameters.Add (param2);
2174                         cmd.Parameters.Add (param3);
2175                         cmd.Parameters.Add (retval);
2176                         cmd.CommandText = "#tmp_sp_param_test";
2177                         cmd.CommandType = CommandType.StoredProcedure;
2178                         using (SqlDataReader reader = cmd.ExecuteReader ()) {
2179                                 Assert.IsTrue (reader.Read (), "#1");
2180                                 AreEqual (expectedRead, reader.GetValue (0), "#2");
2181                                 Assert.IsFalse (reader.Read (), "#3");
2182                         }
2183
2184                         AreEqual (expectedOut, param2.Value, "#4");
2185                         AreEqual (expectedInOut, param3.Value, "#5");
2186                         Assert.AreEqual (5, retval.Value, "#6");
2187                 }
2188
2189                 [Test]
2190                 public void OutputParamSizeTest1 ()
2191                 {
2192                         conn = ConnectionManager.Instance.Sql.Connection;
2193                         cmd = new SqlCommand ();
2194                         cmd.Connection = conn;
2195
2196                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2197                         cmd.CommandType = CommandType.Text;
2198                         cmd.ExecuteNonQuery ();
2199
2200                         cmd.CommandText = "#testsize";
2201                         cmd.CommandType = CommandType.StoredProcedure;
2202
2203                         SqlParameter p1 = new SqlParameter ();
2204                         p1.ParameterName = "@p1";
2205                         p1.Direction = ParameterDirection.InputOutput;
2206                         p1.DbType = DbType.String;
2207                         p1.IsNullable = false;
2208                         cmd.Parameters.Add (p1);
2209
2210                         try {
2211                                 cmd.ExecuteNonQuery ();
2212                                 Assert.Fail ("#1");
2213                         } catch (InvalidOperationException ex) {
2214                                 // String[0]: the Size property has an invalid
2215                                 // size of 0
2216                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2217                                 Assert.IsNull (ex.InnerException, "#3");
2218                                 Assert.IsNotNull (ex.Message, "#4");
2219                         }
2220                 }
2221
2222                 [Test]
2223                 public void OutputParamSizeTest2 ()
2224                 {
2225                         conn = ConnectionManager.Instance.Sql.Connection;
2226                         cmd = new SqlCommand ();
2227                         cmd.Connection = conn;
2228
2229                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2230                         cmd.CommandType = CommandType.Text;
2231                         cmd.ExecuteNonQuery ();
2232
2233                         cmd.CommandText = "#testsize";
2234                         cmd.CommandType = CommandType.StoredProcedure;
2235
2236                         SqlParameter p1 = new SqlParameter ();
2237                         p1.ParameterName = "@p1";
2238                         p1.Direction = ParameterDirection.Output;
2239                         p1.DbType = DbType.String;
2240                         p1.IsNullable = false;
2241                         cmd.Parameters.Add (p1);
2242
2243                         try {
2244                                 cmd.ExecuteNonQuery ();
2245                                 Assert.Fail ("#1");
2246                         } catch (InvalidOperationException ex) {
2247                                 // String[0]: the Size property has an invalid
2248                                 // size of 0
2249                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2250                                 Assert.IsNull (ex.InnerException, "#3");
2251                                 Assert.IsNotNull (ex.Message, "#4");
2252                         }
2253                 }
2254
2255                 [Test]
2256                 public void OutputParamSizeTest3 ()
2257                 {
2258                         conn = ConnectionManager.Instance.Sql.Connection;
2259                         cmd = new SqlCommand ();
2260                         cmd.Connection = conn;
2261
2262                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2263                         cmd.CommandType = CommandType.Text;
2264                         cmd.ExecuteNonQuery ();
2265
2266                         cmd.CommandText = "#testsize";
2267                         cmd.CommandType = CommandType.StoredProcedure;
2268
2269                         SqlParameter p1 = new SqlParameter ();
2270                         p1.ParameterName = "@p1";
2271                         p1.Direction = ParameterDirection.InputOutput;
2272                         p1.DbType = DbType.String;
2273                         p1.IsNullable = true;
2274                         cmd.Parameters.Add (p1);
2275
2276                         try {
2277                                 cmd.ExecuteNonQuery ();
2278                                 Assert.Fail ("#1");
2279                         } catch (InvalidOperationException ex) {
2280                                 // String[0]: the Size property has an invalid
2281                                 // size of 0
2282                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2283                                 Assert.IsNull (ex.InnerException, "#3");
2284                                 Assert.IsNotNull (ex.Message, "#4");
2285                         }
2286                 }
2287
2288                 [Test]
2289                 public void OutputParamSizeTest4 ()
2290                 {
2291                         conn = ConnectionManager.Instance.Sql.Connection;
2292                         cmd = new SqlCommand ();
2293                         cmd.Connection = conn;
2294
2295                         cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2296                         cmd.CommandType = CommandType.Text;
2297                         cmd.ExecuteNonQuery ();
2298
2299                         cmd.CommandText = "#testsize";
2300                         cmd.CommandType = CommandType.StoredProcedure;
2301
2302                         SqlParameter p1 = new SqlParameter ();
2303                         p1.ParameterName = "@p1";
2304                         p1.Direction = ParameterDirection.Output;
2305                         p1.DbType = DbType.String;
2306                         p1.IsNullable = true;
2307                         cmd.Parameters.Add (p1);
2308
2309                         try {
2310                                 cmd.ExecuteNonQuery ();
2311                                 Assert.Fail ("#1");
2312                         } catch (InvalidOperationException ex) {
2313                                 // String[0]: the Size property has an invalid
2314                                 // size of 0
2315                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2316                                 Assert.IsNull (ex.InnerException, "#3");
2317                                 Assert.IsNotNull (ex.Message, "#4");
2318                         }
2319                 }
2320
2321                 [Test] // bug #470579
2322                 public void OutputParamTest ()
2323                 {
2324                         SqlParameter newId, id;
2325
2326                         conn = ConnectionManager.Instance.Sql.Connection;
2327
2328                         cmd = conn.CreateCommand ();
2329                         cmd.CommandText = "set @NewId=@Id + 2";
2330                         cmd.CommandType = CommandType.Text;
2331                         newId = cmd.Parameters.Add ("@NewId", SqlDbType.Int);
2332                         newId.Direction = ParameterDirection.Output;
2333                         id = cmd.Parameters.Add ("@Id", SqlDbType.Int);
2334                         id.Value = 3;
2335                         cmd.ExecuteNonQuery ();
2336
2337                         Assert.AreEqual (5, newId.Value, "#A1");
2338                         Assert.AreEqual (3, id.Value, "#A2");
2339
2340                         cmd = conn.CreateCommand ();
2341                         cmd.CommandText = "set @NewId=@Id + 2";
2342                         cmd.CommandType = CommandType.Text;
2343                         newId = cmd.Parameters.Add ("NewId", SqlDbType.Int);
2344                         newId.Direction = ParameterDirection.Output;
2345                         id = cmd.Parameters.Add ("Id", SqlDbType.Int);
2346                         id.Value = 6;
2347                         cmd.ExecuteNonQuery ();
2348
2349                         Assert.AreEqual (8, newId.Value, "#B1");
2350                         Assert.AreEqual (6, id.Value, "#B2");
2351                 }
2352
2353                 [Test]
2354                 public void SmallMoney_Overflow_Max ()
2355                 {
2356                         conn = new SqlConnection (connectionString);
2357                         conn.Open ();
2358
2359                         DBHelper.ExecuteNonQuery (conn, string.Format (
2360                                 CultureInfo.InvariantCulture, CREATE_TMP_SP_TYPE_TEST,
2361                                 "SMALLMONEY"));
2362                         //decimal overflow = 214748.36471m;
2363                         decimal overflow = 214748.3648m;
2364
2365                         cmd = conn.CreateCommand ();
2366                         cmd.CommandText = "#tmp_sp_type_test";
2367                         cmd.CommandType = CommandType.StoredProcedure;
2368
2369                         SqlParameter param = cmd.Parameters.Add ("@param",
2370                                 SqlDbType.SmallMoney);
2371                         param.Value = overflow;
2372
2373                         try {
2374                                 cmd.ExecuteScalar ();
2375                                 Assert.Fail ("#1");
2376                         } catch (OverflowException ex) {
2377                                 // SqlDbType.SmallMoney overflow.  Value '214748.36471'
2378                                 // is out of range.  Must be between -214,748.3648 and 214,748.3647
2379                                 Assert.AreEqual (typeof (OverflowException), ex.GetType (), "#2");
2380                                 Assert.IsNull (ex.InnerException, "#3");
2381                                 Assert.IsNotNull (ex.Message, "#4");
2382                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2383                                         CultureInfo.InvariantCulture, "'{0}'",
2384                                         overflow)) != -1, "#5:" + ex.Message);
2385                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2386                                         CultureInfo.InvariantCulture, "{0:N4}",
2387                                         SMALLMONEY_MIN)) != -1, "#6:" + ex.Message);
2388                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2389                                         CultureInfo.InvariantCulture, "{0:N4}",
2390                                         SMALLMONEY_MAX)) != -1, "#7:" + ex.Message);
2391                         } finally {
2392                                 DBHelper.ExecuteNonQuery (conn, string.Format (
2393                                         CultureInfo.InvariantCulture,
2394                                         DROP_STORED_PROCEDURE, "#tmp_sp_type_test"));
2395                         }
2396                 }
2397
2398                 [Test]
2399                 public void SmallMoney_Overflow_Min ()
2400                 {
2401                         conn = new SqlConnection (connectionString);
2402                         conn.Open ();
2403
2404                         DBHelper.ExecuteNonQuery (conn, string.Format (
2405                                 CultureInfo.InvariantCulture, CREATE_TMP_SP_TYPE_TEST,
2406                                 "SMALLMONEY"));
2407                         //decimal overflow = -214748.36481m;
2408                         decimal overflow = -214748.3649m;
2409
2410                         cmd = conn.CreateCommand ();
2411                         cmd.CommandText = "#tmp_sp_type_test";
2412                         cmd.CommandType = CommandType.StoredProcedure;
2413
2414                         SqlParameter param = cmd.Parameters.Add ("@param",
2415                                 SqlDbType.SmallMoney);
2416                         param.Value = overflow;
2417
2418                         try {
2419                                 cmd.ExecuteScalar ();
2420                                 Assert.Fail ("#1");
2421                         } catch (OverflowException ex) {
2422                                 // SqlDbType.SmallMoney overflow.  Value '-214748,36481'
2423                                 // is out of range.  Must be between -214,748.3648 and 214,748.3647
2424                                 Assert.AreEqual (typeof (OverflowException), ex.GetType (), "#2");
2425                                 Assert.IsNull (ex.InnerException, "#3");
2426                                 Assert.IsNotNull (ex.Message, "#4");
2427                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2428                                         CultureInfo.InvariantCulture, "'{0}'",
2429                                         overflow)) != -1, "#5:" + ex.Message);
2430                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2431                                         CultureInfo.InvariantCulture, "{0:N4}",
2432                                         SMALLMONEY_MIN)) != -1, "#6:" + ex.Message);
2433                                 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2434                                         CultureInfo.InvariantCulture, "{0:N4}",
2435                                         SMALLMONEY_MAX)) != -1, "#7:" + ex.Message);
2436                         } finally {
2437                                 DBHelper.ExecuteNonQuery (conn, string.Format (
2438                                         CultureInfo.InvariantCulture,
2439                                         DROP_STORED_PROCEDURE, "#tmp_sp_type_test"));
2440                         }
2441                 }
2442
2443                 [Test]
2444                 public void NotificationTest ()
2445                 {
2446                         cmd = new SqlCommand ();
2447                         SqlNotificationRequest notification = new SqlNotificationRequest("MyNotification","MyService",15);
2448                         Assert.AreEqual (null, cmd.Notification, "#1 The default value for this property should be null");
2449                         cmd.Notification = notification;
2450                         Assert.AreEqual ("MyService", cmd.Notification.Options, "#2 The value should be MyService as the constructor is initiated with this value");
2451                         Assert.AreEqual (15, cmd.Notification.Timeout, "#2 The value should be 15 as the constructor is initiated with this value");
2452                 }
2453
2454                 [Test]
2455                 public void NotificationAutoEnlistTest ()
2456                 {
2457                         cmd = new SqlCommand ();
2458                         Assert.AreEqual (true, cmd.NotificationAutoEnlist, "#1 Default value of the property should be true");
2459                         cmd.NotificationAutoEnlist = false;
2460                         Assert.AreEqual (false, cmd.NotificationAutoEnlist, "#2 The value of the property should be false after setting it to false");
2461                 }
2462
2463                 [Test]
2464                 public void BeginExecuteXmlReaderTest ()
2465                 {
2466                         cmd = new SqlCommand ();
2467                         string connectionString1 = null;
2468                         connectionString1 = ConnectionManager.Instance.Sql.ConnectionString + ";Asynchronous Processing=true";
2469                         try {
2470                                 SqlConnection conn1 = new SqlConnection (connectionString1);
2471                                 conn1.Open ();
2472                                 cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA";
2473                                 cmd.Connection = conn1;
2474
2475                                 IAsyncResult result = cmd.BeginExecuteXmlReader ();
2476                                 XmlReader reader = cmd.EndExecuteXmlReader (result);
2477                                 while (reader.Read ()) {
2478                                         if (reader.LocalName.ToString () == "employee")
2479                                                 Assert.AreEqual ("kumar", reader["lname"], "#1 ");
2480                                 }
2481                         } finally {
2482                                 ConnectionManager.Instance.Sql.CloseConnection ();
2483                         }
2484                 }
2485
2486                 [Test]
2487                 [Ignore("MS .NET doesn't throw IOE here. TODO: check corefx")]
2488                 public void BeginExecuteXmlReaderExceptionTest ()
2489                 {
2490                         cmd = new SqlCommand ();
2491                         try {
2492                                 SqlConnection conn = new SqlConnection (connectionString);
2493                                 conn.Open ();
2494                                 cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA";
2495                                 cmd.Connection = conn;
2496
2497                                 try {
2498                                         /*IAsyncResult result = */cmd.BeginExecuteXmlReader ();
2499                                 } catch (InvalidOperationException) {
2500                                         Assert.AreEqual (ConnectionManager.Instance.Sql.ConnectionString, connectionString, "#1 Connection string has changed");
2501                                         return;
2502                                 }
2503                                 Assert.Fail ("Expected Exception InvalidOperationException not thrown");
2504                         } finally {
2505                                 ConnectionManager.Instance.Sql.CloseConnection ();
2506                         }
2507                 }
2508
2509                 [Test]
2510                 public void SqlCommandDisposeTest ()
2511                 {
2512                         IDataReader reader = null;
2513                         conn = ConnectionManager.Instance.Sql.Connection;
2514                         try {
2515
2516                                 IDbCommand command = conn.CreateCommand ();
2517                                 try {
2518                                         string sql = "SELECT * FROM employee";
2519                                         command.CommandText = sql;
2520                                         reader = command.ExecuteReader ();
2521                                 } finally {
2522                                         command.Dispose ();
2523                                 }
2524                                 while (reader.Read ()) ;
2525                         } finally {
2526                                 reader.Dispose ();
2527                                 ConnectionManager.Instance.Sql.CloseConnection ();
2528                         }
2529                 }
2530
2531                 private void bug326182_OutputParamMixupTestCommon (int paramOrder,
2532                                                                    out int param0Val,
2533                                                                    out int param1Val,
2534                                                                    out int param2Val,
2535                                                                    out int param3Val,
2536                                                                    out int rvalVal)
2537                 {
2538                         conn = ConnectionManager.Instance.Sql.Connection;
2539                         try {
2540
2541                                 try {
2542                                         SqlParameter param0 = new SqlParameter ("@param0", SqlDbType.Int);
2543                                         param0.Direction = ParameterDirection.Output;
2544                                         SqlParameter param1 = new SqlParameter ("@param1", SqlDbType.Int);
2545                                         param1.Direction = ParameterDirection.Output;
2546                                         SqlParameter param2 = new SqlParameter ("@param2", SqlDbType.Int);
2547                                         param2.Direction = ParameterDirection.Output;
2548                                         SqlParameter param3 = new SqlParameter ("@param3", SqlDbType.Int);
2549                                         param3.Direction = ParameterDirection.Output;
2550                                         SqlParameter rval = new SqlParameter ("@RETURN_VALUE", SqlDbType.Int);
2551                                         rval.Direction = ParameterDirection.ReturnValue;
2552
2553                                         cmd = conn.CreateCommand ();
2554                                         cmd.CommandText = "dbo.[sp_326182a]";
2555                                         cmd.CommandType = CommandType.StoredProcedure;
2556
2557                                         switch (paramOrder) {
2558                                         case 1: cmd.Parameters.Add (param0);
2559                                                 cmd.Parameters.Add (param1);
2560                                                 cmd.Parameters.Add (rval);
2561                                                 cmd.Parameters.Add (param2);
2562                                                 cmd.Parameters.Add (param3);
2563                                                 break;
2564                                         case 2: cmd.Parameters.Add (rval);
2565                                                 cmd.Parameters.Add (param1);
2566                                                 cmd.Parameters.Add (param0);
2567                                                 cmd.Parameters.Add (param2);
2568                                                 cmd.Parameters.Add (param3);
2569                                                 break;
2570                                         default: cmd.Parameters.Add (param0);
2571                                                 cmd.Parameters.Add (param1);
2572                                                 cmd.Parameters.Add (param2);
2573                                                 cmd.Parameters.Add (param3);
2574                                                 cmd.Parameters.Add (rval);
2575                                                 break;
2576                                         }
2577
2578                                         cmd.ExecuteNonQuery ();
2579
2580                                         /* Copy the param values to variables, just in case if
2581                                          * tests fail, we don't want the created sp to exist */
2582                                         param3Val = (int) cmd.Parameters ["@param3"].Value;
2583                                         param1Val = (int) cmd.Parameters ["@param1"].Value;
2584                                         rvalVal = (int) cmd.Parameters ["@RETURN_VALUE"].Value;
2585                                         param2Val = (int) cmd.Parameters ["@param2"].Value;
2586                                         param0Val = (int) cmd.Parameters ["@param0"].Value;
2587                                 } finally {
2588                                         cmd.Dispose ();
2589                                         cmd = null;
2590                                 }
2591                         } finally {
2592                                 ConnectionManager.Instance.Sql.CloseConnection ();
2593                                 conn = null;
2594                         }
2595                 }
2596
2597                 [Test]
2598                 public void bug326182_OutputParamMixupTest_Normal ()
2599                 {
2600                         int param0Val, param1Val, param2Val, param3Val, rvalVal;
2601
2602                         //param0Val = param1Val = param2Val = param3Val = rvalVal = 0;
2603
2604                         bug326182_OutputParamMixupTestCommon (0, out param0Val, out param1Val,
2605                                                               out param2Val, out param3Val, out rvalVal);
2606                         Assert.AreEqual (103, param3Val);
2607                         Assert.AreEqual (101, param1Val);
2608                         Assert.AreEqual (2, rvalVal);
2609                         Assert.AreEqual (102, param2Val);
2610                         Assert.AreEqual (100, param0Val);
2611                 }
2612
2613                 [Test]
2614                 public void bug326182_OutputParamMixupTest_RValInBetween ()
2615                 {
2616                         int param0Val, param1Val, param2Val, param3Val, rvalVal;
2617
2618                         bug326182_OutputParamMixupTestCommon (1, out param0Val, out param1Val,
2619                                                               out param2Val, out param3Val, out rvalVal);
2620                         Assert.AreEqual (103, param3Val);
2621                         Assert.AreEqual (101, param1Val);
2622                         Assert.AreEqual (2, rvalVal);
2623                         Assert.AreEqual (102, param2Val);
2624                         Assert.AreEqual (100, param0Val);
2625                 }
2626
2627                 [Test]
2628                 public void bug326182_OutputParamMixupTest_RValFirst ()
2629                 {
2630                         int param0Val, param1Val, param2Val, param3Val, rvalVal;
2631
2632                         bug326182_OutputParamMixupTestCommon (2, out param0Val, out param1Val,
2633                                                               out param2Val, out param3Val, out rvalVal);
2634                         Assert.AreEqual (103, param3Val);
2635                         Assert.AreEqual (101, param1Val);
2636                         Assert.AreEqual (2, rvalVal);
2637                         Assert.AreEqual (102, param2Val);
2638                         Assert.AreEqual (100, param0Val);
2639                 }
2640
2641                 [Test]
2642                 public void DeriveParameterTest_FullSchema ()
2643                 {
2644                         string create_tbl = "CREATE TABLE decimalCheck (deccheck DECIMAL (19, 5) null)";
2645                         string create_sp = "CREATE PROCEDURE sp_bug584833(@deccheck decimal(19,5) OUT)"
2646                                     + "AS " + Environment.NewLine
2647                                     + "BEGIN" + Environment.NewLine
2648                                                 + "INSERT INTO decimalCheck values (@deccheck)" + Environment.NewLine
2649                                     + "SELECT @deccheck=deccheck from decimalCheck" + Environment.NewLine
2650                                     + "END";
2651
2652                         conn = ConnectionManager.Instance.Sql.Connection;
2653                         try {
2654                                 cmd = conn.CreateCommand ();
2655                                 cmd.CommandText = create_tbl;
2656                                 cmd.ExecuteNonQuery ();
2657
2658                                 cmd.CommandText = create_sp;
2659                                 cmd.ExecuteNonQuery ();
2660
2661                                 cmd.CommandText = "dbo.sp_bug584833";
2662                                 cmd.CommandType = CommandType.StoredProcedure;
2663
2664                                 SqlCommandBuilder.DeriveParameters (cmd);
2665                                 Assert.AreEqual (2, cmd.Parameters.Count, "#DPT - FullSchema - Parameter count mismatch");
2666                                 Assert.AreEqual ("@deccheck", cmd.Parameters[1].ParameterName, "#DPT - FullSchema - Parameter name mismatch");
2667                                 Assert.AreEqual (SqlDbType.Decimal, cmd.Parameters[1].SqlDbType, "#DPT - FullSchema - Parameter type mismatch");
2668                         } finally {
2669                                 cmd.Parameters.Clear ();
2670                                 cmd.CommandText = "drop procedure sp_bug584833";
2671                                 cmd.CommandType = CommandType.Text;
2672                                 cmd.ExecuteNonQuery ();
2673                                 cmd.CommandText = "drop table decimalCheck";
2674                                 cmd.ExecuteNonQuery ();
2675                                 cmd.Dispose ();
2676                                 cmd = null;
2677                                 ConnectionManager.Instance.Sql.CloseConnection ();
2678                                 conn = null;
2679                         }
2680
2681                 }
2682
2683                 [Test]
2684                 public void DeriveParameterTest_SPName ()
2685                 {
2686                         string create_tbl = "CREATE TABLE decimalCheck (deccheck DECIMAL (19, 5) null)";
2687                         string create_sp = "CREATE PROCEDURE sp_bug584833(@deccheck decimal(19,5) OUT)"
2688                                     + "AS " + Environment.NewLine
2689                                     + "BEGIN" + Environment.NewLine
2690                                                 + "INSERT INTO decimalCheck values (@deccheck)" + Environment.NewLine
2691                                     + "SELECT @deccheck=deccheck from decimalCheck" + Environment.NewLine
2692                                     + "END";
2693
2694                         conn = ConnectionManager.Instance.Sql.Connection;
2695                         try {
2696
2697                                 cmd = conn.CreateCommand ();
2698                                 cmd.CommandText = create_tbl;
2699                                 cmd.ExecuteNonQuery ();
2700
2701                                 cmd.CommandText = create_sp;
2702                                 cmd.ExecuteNonQuery ();
2703
2704                                 cmd.CommandText = "sp_bug584833";
2705                                 cmd.CommandType = CommandType.StoredProcedure;
2706
2707                                 SqlCommandBuilder.DeriveParameters (cmd);
2708                                 Assert.AreEqual (2, cmd.Parameters.Count, "#DPT - SPName - Parameter count mismatch");
2709                                 Assert.AreEqual ("@deccheck", cmd.Parameters[1].ParameterName, "#DPT - SPName - Parameter name mismatch");
2710                                 Assert.AreEqual (SqlDbType.Decimal, cmd.Parameters[1].SqlDbType, "#DPT - SPName - Parameter type mismatch");
2711                         } finally {
2712                                 cmd.Parameters.Clear ();
2713                                 cmd.CommandType = CommandType.Text;
2714                                 cmd.CommandText = "drop procedure sp_bug584833";
2715                                 cmd.ExecuteNonQuery ();
2716                                 cmd.CommandText = "drop table decimalCheck";
2717                                 cmd.ExecuteNonQuery ();
2718                                 cmd.Dispose ();
2719                                 cmd = null;
2720                                 ConnectionManager.Instance.Sql.CloseConnection ();
2721                                 conn = null;
2722                         }
2723                 }
2724
2725                 [Test]
2726                 public void DeriveParameterTest_UserSchema ()
2727                 {
2728                         string create_tbl = "CREATE TABLE decimalCheck (deccheck DECIMAL (19, 5) null)";
2729                         string create_sp = "CREATE PROCEDURE sp_bug584833(@deccheck decimal(19,5) OUT)"
2730                                     + "AS " + Environment.NewLine
2731                                     + "BEGIN" + Environment.NewLine
2732                                                 + "INSERT INTO decimalCheck values (@deccheck)" + Environment.NewLine
2733                                     + "SELECT @deccheck=deccheck from decimalCheck" + Environment.NewLine
2734                                     + "END";
2735
2736                         try {
2737                                 conn = ConnectionManager.Instance.Sql.Connection;
2738
2739                                 cmd = conn.CreateCommand ();
2740                                 cmd.CommandText = create_tbl;
2741                                 cmd.ExecuteNonQuery ();
2742
2743                                 cmd.CommandText = create_sp;
2744                                 cmd.ExecuteNonQuery ();
2745
2746                                 cmd.CommandText = "dbo.sp_bug584833";
2747                                 cmd.CommandType = CommandType.StoredProcedure;
2748
2749                                 SqlCommandBuilder.DeriveParameters (cmd);
2750                                 Assert.AreEqual (2, cmd.Parameters.Count, "#DPT - user schema - Parameter count mismatch");
2751                                 Assert.AreEqual ("@deccheck", cmd.Parameters[1].ParameterName, "#DPT - user schema - Parameter name mismatch");
2752                                 Assert.AreEqual (SqlDbType.Decimal, cmd.Parameters[1].SqlDbType, "#DPT - user schema - Parameter type mismatch");
2753                         } finally {
2754                                 cmd.Parameters.Clear ();
2755                                 cmd.CommandType = CommandType.Text;
2756                                 cmd.CommandText = "drop procedure dbo.sp_bug584833";
2757                                 cmd.ExecuteNonQuery ();
2758                                 cmd.CommandText = "drop table decimalCheck";
2759                                 cmd.ExecuteNonQuery ();
2760                                 cmd.Dispose ();
2761                                 cmd = null;
2762                                 ConnectionManager.Instance.Sql.CloseConnection ();
2763                                 conn = null;
2764                         }
2765                 }
2766
2767                 [Test]  // bug#561667
2768                 public void CmdDispose_DataReaderReset ()
2769                 {
2770                         conn = ConnectionManager.Instance.Sql.Connection;
2771                         try
2772                         {
2773                                 string query1 = "SELECT fname FROM employee where lname='kumar'";
2774                                 string query2 = "SELECT type_int FROM numeric_family where type_bit = 1";
2775                                 DataTable t = null;
2776
2777                                 t = GetColumns(conn, query1);
2778                                 Assert.AreEqual ("suresh", t.Rows[0][0], "CmdDD#1: Query1 result mismatch");
2779                             t = GetColumns(conn, query2);
2780                                 Assert.AreEqual (int.MaxValue, t.Rows[0][0], "CmdDD#2: Query2 result mismatch");
2781                         } finally {
2782                             ConnectionManager.Instance.Sql.CloseConnection ();
2783                                 conn = null;
2784                         }
2785                 }
2786
2787                 private DataTable GetColumns(DbConnection connection, string query)
2788                 {
2789                     DataTable t = new DataTable("Columns");
2790                     using (DbCommand c = connection.CreateCommand())
2791                     {
2792                         c.CommandText = query;
2793                         t.Load(c.ExecuteReader());
2794                     }
2795                     return t;
2796                 }
2797
2798                 // used as workaround for bugs in NUnit 2.2.0
2799                 static void AreEqual (object x, object y, string msg)
2800                 {
2801                         if (x == null && y == null)
2802                                 return;
2803                         if ((x == null || y == null))
2804                                 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2805                                         "Expected: {0}, but was: {1}. {2}",
2806                                         x == null ? "<null>" : x, y == null ? "<null>" : y, msg));
2807
2808                         bool isArrayX = x.GetType ().IsArray;
2809                         bool isArrayY = y.GetType ().IsArray;
2810
2811                         if (isArrayX && isArrayY) {
2812                                 Array arrayX = (Array) x;
2813                                 Array arrayY = (Array) y;
2814
2815                                 if (arrayX.Length != arrayY.Length)
2816                                         throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2817                                                 "Length of arrays differs. Expected: {0}, but was: {1}. {2}",
2818                                                 arrayX.Length, arrayY.Length, msg));
2819
2820                                 for (int i = 0; i < arrayX.Length; i++) {
2821                                         object itemX = arrayX.GetValue (i);
2822                                         object itemY = arrayY.GetValue (i);
2823                                         if (!itemX.Equals (itemY))
2824                                                 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2825                                                         "Arrays differ at position {0}. Expected: {1}, but was: {2}. {3}",
2826                                                         i, itemX, itemY, msg));
2827                                 }
2828                         } else if (!x.Equals (y)) {
2829                                 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2830                                         "Expected: {0} ({1}), but was: {2} ({3}). {4}",
2831                                         x, x.GetType (), y, y.GetType (), msg));
2832                         }
2833                 }
2834
2835                 int ClientVersion {
2836                         get {
2837                                 return (engine.ClientVersion);
2838                         }
2839                 }
2840
2841                 private enum Status
2842                 {
2843                         OK = 0,
2844                         Error = 3
2845                 }
2846
2847                 private readonly string CREATE_TMP_SP_PARAM_TEST =
2848                         "CREATE PROCEDURE #tmp_sp_param_test (" + Environment.NewLine +
2849                         "       @param1 {0}," + Environment.NewLine +
2850                         "       @param2 {0} output," + Environment.NewLine +
2851                         "       @param3 {0} output)" + Environment.NewLine +
2852                         "AS" + Environment.NewLine +
2853                         "BEGIN" + Environment.NewLine +
2854                         "       SELECT @param1" + Environment.NewLine +
2855                         "       SET @param2=@param1" + Environment.NewLine +
2856                         "       RETURN 5" + Environment.NewLine +
2857                         "END";
2858
2859                 private readonly string CREATE_TMP_SP_TEMP_INSERT_PERSON = ("create procedure #sp_temp_insert_employee ( " + Environment.NewLine +
2860                                                                             "@fname varchar (20), " + Environment.NewLine +
2861                                                                             "@dob datetime, " + Environment.NewLine +
2862                                                                             "@doj datetime output " + Environment.NewLine +
2863                                                                             ") " + Environment.NewLine +
2864                                                                             "as " + Environment.NewLine +
2865                                                                             "begin" + Environment.NewLine +
2866                                                                             "declare @id int;" + Environment.NewLine +
2867                                                                             "select @id = max (id) from employee;" + Environment.NewLine +
2868                                                                             "set @id = @id + 6000 + 1;" + Environment.NewLine +
2869                                                                             "set @doj = getdate();" + Environment.NewLine +
2870                                                                             "insert into employee (id, fname, dob, doj) values (@id, @fname, @dob, @doj);" + Environment.NewLine +
2871                                                                             "return @id;" + Environment.NewLine +
2872                                                                             "end");
2873
2874                 private readonly string DROP_TMP_SP_TEMP_INSERT_PERSON = ("if exists (select name from sysobjects where " + Environment.NewLine +
2875                                                                           "name = '#sp_temp_insert_employee' and type = 'P') " + Environment.NewLine +
2876                                                                           "drop procedure #sp_temp_insert_employee; ");
2877
2878                 private static readonly string CREATE_TMP_SP_TYPE_TEST =
2879                         "CREATE PROCEDURE #tmp_sp_type_test " +
2880                         "(" +
2881                         "       @param {0}" +
2882                         ") AS SELECT @param";
2883                 private static readonly string DROP_STORED_PROCEDURE =
2884                         "DROP PROCEDURE {0}";
2885         }
2886 }