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