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

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