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

   1 //
   2 // SqlParameterTest.cs - NUnit Test Cases for testing the
   3 //                          SqlParameter class
   4 // Author:
   5 //      Senganal T (tsenganal@novell.com)
   6 //      Amit Biswas (amit@amitbiswas.com)
   7 //      Veerapuram Varadhan  (vvaradhan@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.SqlClient;
  35
  36 using NUnit.Framework;
  37
  38 namespace MonoTests.System.Data.SqlClient
  39 {
  40         [TestFixture]
  41         [Category ("sqlserver")]
  42         public class SqlParameterTest
  43         {
  44                 SqlConnection conn;
  45                 SqlCommand cmd;
  46                 SqlDataReader rdr;
  47                 EngineConfig engine;
  48
  49                 [SetUp]
  50                 public void SetUp ()
  51                 {
  52                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
  53                         ConnectionManager.Singleton.OpenConnection ();
  54                         engine = ConnectionManager.Singleton.Engine;
  55                 }
  56
  57                 [TearDown]
  58                 public void TearDown ()
  59                 {
  60                         if (cmd != null)
  61                                 cmd.Dispose ();
  62                         if (rdr != null)
  63                                 rdr.Close ();
  64                         ConnectionManager.Singleton.CloseConnection ();
  65                 }
  66
  67                 [Test] // bug #324840
  68                 public void ParameterSizeTest ()
  69                 {
  70                         if (ClientVersion == 7)
  71                                 Assert.Ignore ("Hangs on SQL Server 7.0");
  72
  73                         string longstring = new String('x', 20480);
  74                         SqlParameter prm;
  75                         cmd = new SqlCommand ("create table #text1 (ID int not null, Val1 ntext)", conn);
  76                         cmd.ExecuteNonQuery ();
  77                         cmd.CommandText = "INSERT INTO #text1(ID,Val1) VALUES (@ID,@Val1)";
  78                         prm = new SqlParameter ();
  79                         prm.ParameterName = "@ID";
  80                         prm.Value = 1;
  81                         cmd.Parameters.Add (prm);
  82
  83                         prm = new SqlParameter ();
  84                         prm.ParameterName = "@Val1";
  85                         prm.Value = longstring;
  86                         prm.SqlDbType = SqlDbType.NText; // Comment and enjoy the truncation
  87                         cmd.Parameters.Add (prm);
  88                         cmd.ExecuteNonQuery ();
  89                         cmd = new SqlCommand ("select datalength(Val1) from #text1", conn);
  90                         Assert.AreEqual (20480 * 2, cmd.ExecuteScalar (), "#1");
  91
  92                         cmd.CommandText = "INSERT INTO #text1(ID,Val1) VALUES (@ID,@Val1)";
  93                         prm = new SqlParameter ();
  94                         prm.ParameterName = "@ID";
  95                         prm.Value = 1;
  96                         cmd.Parameters.Add (prm);
  97
  98                         prm = new SqlParameter ();
  99                         prm.ParameterName = "@Val1";
 100                         prm.Value = longstring;
 101                         //prm.SqlDbType = SqlDbType.NText;
 102                         cmd.Parameters.Add (prm);
 103                         cmd.ExecuteNonQuery ();
 104                         cmd = new SqlCommand ("select datalength(Val1) from #text1", conn);
 105                         Assert.AreEqual (20480 * 2, cmd.ExecuteScalar (), "#2");
 106
 107                         cmd.CommandText = "INSERT INTO #text1(ID,Val1) VALUES (@ID,@Val1)";
 108                         prm = new SqlParameter ();
 109                         prm.ParameterName = "@ID";
 110                         prm.Value = 1;
 111                         cmd.Parameters.Add (prm);
 112
 113                         prm = new SqlParameter ();
 114                         prm.ParameterName = "@Val1";
 115                         prm.Value = longstring;
 116                         prm.SqlDbType = SqlDbType.VarChar;
 117                         cmd.Parameters.Add (prm);
 118                         cmd.ExecuteNonQuery ();
 119                         cmd = new SqlCommand ("select datalength(Val1) from #text1", conn);
 120                         Assert.AreEqual (20480 * 2, cmd.ExecuteScalar (), "#3");
 121                         cmd = new SqlCommand ("drop table #text1", conn);
 122                         cmd.ExecuteNonQuery ();
 123                         conn.Close ();
 124                 }
 125
 126                 [Test] // bug #382635
 127                 public void ParameterSize_compatibility_Test ()
 128                 {
 129                         string longstring = "abcdefghijklmnopqrstuvwxyz";
 130
 131                         cmd = new SqlCommand ("create table #bug382635 (description varchar(20))", conn);
 132                         cmd.ExecuteNonQuery ();
 133
 134                         cmd.CommandText =
 135                                         "CREATE PROCEDURE #sp_bug382635 (@Desc varchar(20)) "
 136                                         + "AS " + Environment.NewLine
 137                                         + "BEGIN" + Environment.NewLine
 138                                         + "UPDATE #bug382635 SET description = @Desc" + Environment.NewLine
 139                                         + "END";
 140                         cmd.CommandType = CommandType.Text;
 141                         cmd.ExecuteNonQuery ();
 142
 143                         cmd.CommandText = "INSERT INTO #bug382635 " +
 144                                           "(description) VALUES ('Verifies bug #382635')";
 145                         cmd.ExecuteNonQuery ();
 146
 147                         cmd.CommandText = "#sp_bug382635";
 148                         cmd.CommandType = CommandType.StoredProcedure;
 149
 150                         SqlParameter p1 = new SqlParameter ("@Desc", SqlDbType.NVarChar, 15);
 151                         p1.Value = longstring;
 152                         Assert.AreEqual (longstring, p1.Value);
 153                         cmd.Parameters.Add (p1);
 154                         cmd.ExecuteNonQuery ();
 155
 156                         // Test for truncation
 157                         SqlCommand selectCmd = new SqlCommand ("SELECT DATALENGTH(description), description from #bug382635", conn);
 158
 159                         rdr = selectCmd.ExecuteReader ();
 160                         Assert.IsTrue (rdr.Read (), "#A1");
 161                         Assert.AreEqual (15, rdr.GetValue (0), "#A2");
 162                         Assert.AreEqual (longstring.Substring (0, 15), rdr.GetValue (1), "#A3");
 163                         Assert.AreEqual (longstring, p1.Value, "#A4");
 164                         rdr.Close ();
 165
 166                         // Test to ensure truncation is not done in the Value getter/setter
 167                         p1.Size = 12;
 168                         p1.Value = longstring.Substring (0, 22);
 169                         p1.Size = 14;
 170                         cmd.ExecuteNonQuery ();
 171
 172                         rdr = selectCmd.ExecuteReader ();
 173                         Assert.IsTrue (rdr.Read (), "#B1");
 174                         Assert.AreEqual (14, rdr.GetValue (0), "#B2");
 175                         Assert.AreEqual (longstring.Substring (0, 14), rdr.GetValue (1), "#B3");
 176                         Assert.AreEqual (longstring.Substring (0, 22), p1.Value, "#B4");
 177                         rdr.Close ();
 178
 179                         // Size exceeds size of value
 180                         p1.Size = 40;
 181                         cmd.ExecuteNonQuery ();
 182
 183                         rdr = selectCmd.ExecuteReader ();
 184                         Assert.IsTrue (rdr.Read (), "#C1");
 185                         Assert.AreEqual (20, rdr.GetValue (0), "#C2");
 186                         Assert.AreEqual (longstring.Substring (0, 20), rdr.GetValue (1), "#C3");
 187                         rdr.Close ();
 188                 }
 189
 190                 [Test]
 191                 public void ConversionToSqlTypeInvalid ()
 192                 {
 193                         string insert_data = "insert into datetime_family (id, type_datetime) values (6000, @type_datetime)";
 194                         string delete_data = "delete from datetime_family where id = 6000";
 195
 196                         object [] values = new object [] {
 197                                 5,
 198                                 true,
 199                                 40L,
 200                                 "invalid date",
 201                                 };
 202
 203                         try {
 204                                 for (int i = 0; i < values.Length; i++) {
 205                                         object value = values [i];
 206
 207                                         cmd = conn.CreateCommand ();
 208                                         cmd.CommandText = insert_data;
 209                                         SqlParameter param = cmd.Parameters.Add ("@type_datetime", SqlDbType.DateTime);
 210                                         param.Value = value;
 211                                         cmd.Prepare ();
 212
 213                                         try {
 214                                                 cmd.ExecuteNonQuery ();
 215                                                 Assert.Fail ("#1:" + i);
 216                                         } catch (InvalidCastException) {
 217                                                 if (value is string)
 218                                                         Assert.Fail ("#2");
 219                                         } catch (FormatException) {
 220                                                 if (!(value is string))
 221                                                         Assert.Fail ("#3");
 222                                         }
 223                                 }
 224                         } finally {
 225                                 DBHelper.ExecuteNonQuery (conn, delete_data);
 226                         }
 227                 }
 228
 229                 [Test] // bug #382589
 230                 public void DecimalMaxAsParamValueTest ()
 231                 {
 232                         if (ClientVersion == 7)
 233                                 Assert.Ignore ("Maximum precision is 28.");
 234
 235                         string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,0) OUT)"
 236                                 + "AS " + Environment.NewLine
 237                                 + "BEGIN" + Environment.NewLine
 238                                 + "SET @decmax = 102.34" + Environment.NewLine
 239                                 + "END";
 240
 241                         cmd = new SqlCommand (create_sp, conn);
 242                         cmd.ExecuteNonQuery ();
 243
 244                         cmd.CommandText = "[#sp_bug382539]";
 245                         cmd.CommandType = CommandType.StoredProcedure;
 246                         SqlParameter pValue = new SqlParameter("@decmax", Decimal.MaxValue);
 247                         pValue.Direction = ParameterDirection.InputOutput;
 248                         cmd.Parameters.Add(pValue);
 249
 250                         Assert.AreEqual (Decimal.MaxValue, pValue.Value, "Parameter initialization value mismatch");
 251                         cmd.ExecuteNonQuery();
 252
 253                         Assert.AreEqual (102m, pValue.Value, "Parameter value mismatch");
 254                 }
 255
 256                 [Test] // bug #382589
 257                 public void DecimalMinAsParamValueTest ()
 258                 {
 259                         if (ClientVersion == 7)
 260                                 Assert.Ignore ("Maximum precision is 28.");
 261
 262                         string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,0) OUT)"
 263                                 + "AS " + Environment.NewLine
 264                                 + "BEGIN" + Environment.NewLine
 265                                 + "SET @decmax = 102.34" + Environment.NewLine
 266                                 + "END";
 267
 268                         cmd = new SqlCommand (create_sp, conn);
 269                         cmd.ExecuteNonQuery ();
 270
 271                         cmd.CommandText = "[#sp_bug382539]";
 272                         cmd.CommandType = CommandType.StoredProcedure;
 273                         SqlParameter pValue = new SqlParameter("@decmax", Decimal.MinValue);
 274                         pValue.Direction = ParameterDirection.InputOutput;
 275                         cmd.Parameters.Add(pValue);
 276
 277                         Assert.AreEqual (Decimal.MinValue, pValue.Value, "Parameter initialization value mismatch");
 278                         cmd.ExecuteNonQuery();
 279
 280                         Assert.AreEqual (102m, pValue.Value, "Parameter value mismatch");
 281                 }
 282
 283                 [Test] // bug #382589
 284                 public void DecimalMaxAsParamValueExceptionTest ()
 285                 {
 286                         if (ClientVersion == 7)
 287                                 Assert.Ignore ("Maximum precision is 28.");
 288
 289                         string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,10) OUT)"
 290                                 + "AS " + Environment.NewLine
 291                                 + "BEGIN" + Environment.NewLine
 292                                 + "SET @decmax = 102.36" + Environment.NewLine
 293                                 + "END";
 294
 295                         cmd = new SqlCommand (create_sp, conn);
 296                         cmd.ExecuteNonQuery ();
 297
 298                         cmd.CommandText = "[#sp_bug382539]";
 299                         cmd.CommandType = CommandType.StoredProcedure;
 300                         SqlParameter pValue = new SqlParameter("@decmax", Decimal.MaxValue);
 301                         pValue.Direction = ParameterDirection.InputOutput;
 302                         cmd.Parameters.Add(pValue);
 303
 304                         try {
 305                                 cmd.ExecuteNonQuery ();
 306                                 Assert.Fail ("#1");
 307                         } catch (SqlException ex) {
 308                                 // Error converting data type numeric to decimal
 309                                 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
 310                                 Assert.AreEqual ((byte) 16, ex.Class, "#3");
 311                                 Assert.IsNull (ex.InnerException, "#4");
 312                                 Assert.IsNotNull (ex.Message, "#5");
 313                                 Assert.AreEqual (8114, ex.Number, "#6");
 314                                 Assert.AreEqual ((byte) 5, ex.State, "#7");
 315                         }
 316                 }
 317
 318                 [Test] // bug# 382589
 319                 public void DecimalMinAsParamValueExceptionTest ()
 320                 {
 321                         if (ClientVersion == 7)
 322                                 Assert.Ignore ("Maximum precision is 28.");
 323
 324                         string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,10) OUT)"
 325                                 + "AS " + Environment.NewLine
 326                                 + "BEGIN" + Environment.NewLine
 327                                 + "SET @decmax = 102.36" + Environment.NewLine
 328                                 + "END";
 329
 330                         cmd = new SqlCommand (create_sp, conn);
 331                         cmd.ExecuteNonQuery ();
 332
 333                         cmd.CommandText = "[#sp_bug382539]";
 334                         cmd.CommandType = CommandType.StoredProcedure;
 335                         SqlParameter pValue = new SqlParameter("@decmax", Decimal.MinValue);
 336                         pValue.Direction = ParameterDirection.InputOutput;
 337                         cmd.Parameters.Add(pValue);
 338                         try {
 339                                 cmd.ExecuteNonQuery ();
 340                                 Assert.Fail ("#1");
 341                         } catch (SqlException ex) {
 342                                 // Error converting data type numeric to decimal
 343                                 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
 344                                 Assert.AreEqual ((byte) 16, ex.Class, "#3");
 345                                 Assert.IsNull (ex.InnerException, "#4");
 346                                 Assert.IsNotNull (ex.Message, "#5");
 347                                 Assert.AreEqual (8114, ex.Number, "#6");
 348                                 Assert.AreEqual ((byte) 5, ex.State, "#7");
 349                         }
 350                 }
 351
 352                 [Test] // bug #526794
 353                 public void ZeroLengthString ()
 354                 {
 355                         cmd = new SqlCommand ("create table #bug526794 (name varchar(20) NULL)", conn);
 356                         cmd.ExecuteNonQuery ();
 357
 358                         SqlParameter param;
 359
 360                         param = new SqlParameter ("@name", SqlDbType.VarChar);
 361                         param.Value = string.Empty;
 362
 363                         cmd = new SqlCommand ("insert into #bug526794 values (@name)", conn);
 364                         cmd.Parameters.Add (param);
 365                         cmd.ExecuteNonQuery ();
 366
 367                         cmd = new SqlCommand ("select * from #bug526794", conn);
 368                         rdr = cmd.ExecuteReader ();
 369                         Assert.IsTrue (rdr.Read (), "#A1");
 370                         Assert.AreEqual (string.Empty, rdr.GetValue (0), "#A2");
 371                         rdr.Close ();
 372
 373                         param = new SqlParameter ("@name", SqlDbType.Int);
 374                         param.Value = string.Empty;
 375
 376                         cmd = new SqlCommand ("insert into #bug526794 values (@name)", conn);
 377                         cmd.Parameters.Add (param);
 378
 379                         try {
 380                                 cmd.ExecuteNonQuery ();
 381                                 Assert.Fail ("#B1");
 382                         } catch (FormatException ex) {
 383 #if NET_2_0
 384                                 // Failed to convert parameter value from a String to a Int32
 385                                 Assert.AreEqual (typeof (FormatException), ex.GetType (), "#B2");
 386                                 Assert.IsNotNull (ex.Message, "#B3");
 387                                 Assert.IsTrue (ex.Message.IndexOf (typeof (string).Name) != -1, "#B4");
 388                                 Assert.IsTrue (ex.Message.IndexOf (typeof (int).Name) != -1, "#B5");
 389
 390                                 // Input string was not in a correct format
 391                                 Exception inner = ex.InnerException;
 392                                 Assert.IsNotNull (inner, "#B6");
 393                                 Assert.AreEqual (typeof (FormatException), inner.GetType (), "#B7");
 394                                 Assert.IsNull (inner.InnerException, "#B8");
 395                                 Assert.IsNotNull (inner.Message, "#B9");
 396 #else
 397                                 // Input string was not in a correct format
 398                                 Assert.AreEqual (typeof (FormatException), ex.GetType (), "#B2");
 399                                 Assert.IsNull (ex.InnerException, "#B3");
 400                                 Assert.IsNotNull (ex.Message, "#B4");
 401 #endif
 402                         }
 403                 }
 404
 405                 [Test] // bug #595918
 406                 public void DecimalDefaultScaleTest ()
 407                 {
 408                         string create_tbl = "CREATE TABLE #decimalScaleCheck (decsclcheck DECIMAL (19, 5) null)";
 409                         string create_sp = "CREATE PROCEDURE #sp_bug595918(@decsclcheck decimal(19,5) OUT)"
 410                                 + "AS " + Environment.NewLine
 411                                 + "BEGIN" + Environment.NewLine
 412                                 + "INSERT INTO #decimalScaleCheck values (@decsclcheck)" + Environment.NewLine
 413                                 + "SELECT @decsclcheck=decsclcheck from #decimalScaleCheck" + Environment.NewLine
 414                                 + "END";
 415
 416                         cmd = new SqlCommand (create_tbl, conn);
 417                         cmd.ExecuteNonQuery ();
 418
 419                         cmd.CommandText = create_sp;
 420                         cmd.ExecuteNonQuery ();
 421
 422                         cmd.CommandText = "[#sp_bug595918]";
 423                         cmd.CommandType = CommandType.StoredProcedure;
 424                         SqlParameter pValue = new SqlParameter("@decsclcheck", SqlDbType.Decimal);
 425                         pValue.Value = 128.425;
 426                         pValue.Precision = 19;
 427                         pValue.Scale = 3;
 428                         pValue.Direction = ParameterDirection.InputOutput;
 429                         cmd.Parameters.Add(pValue);
 430                         cmd.ExecuteNonQuery();
 431
 432                         Assert.AreEqual (128.425, pValue.Value, "Stored decimal value is incorrect - DS - Bug#595918");
 433                 }
 434
 435                 [Test] // bug #595918
 436                 public void DecimalGreaterScaleTest ()
 437                 {
 438                         string create_tbl = "CREATE TABLE #decimalScaleCheck (decsclcheck DECIMAL (19, 5) null)";
 439                         string create_sp = "CREATE PROCEDURE #sp_bug595918(@decsclcheck decimal(19,5) OUT)"
 440                                 + "AS " + Environment.NewLine
 441                                 + "BEGIN" + Environment.NewLine
 442                                 + "INSERT INTO #decimalScaleCheck values (@decsclcheck)" + Environment.NewLine
 443                                 + "SELECT @decsclcheck=decsclcheck from #decimalScaleCheck" + Environment.NewLine
 444                                 + "END";
 445
 446                         cmd = new SqlCommand (create_tbl, conn);
 447                         cmd.ExecuteNonQuery ();
 448
 449                         cmd.CommandText = create_sp;
 450                         cmd.ExecuteNonQuery ();
 451
 452                         cmd.CommandText = "[#sp_bug595918]";
 453                         cmd.CommandType = CommandType.StoredProcedure;
 454                         SqlParameter pValue = new SqlParameter("@decsclcheck", SqlDbType.Decimal);
 455                         pValue.Value = 128.425;
 456                         pValue.Precision = 19;
 457                         pValue.Scale = 5;
 458                         pValue.Direction = ParameterDirection.InputOutput;
 459                         cmd.Parameters.Add(pValue);
 460                         cmd.ExecuteNonQuery();
 461
 462                         Assert.AreEqual (128.42500, pValue.Value, "Stored decimal value is incorrect - GS - Bug#595918");
 463                 }
 464
 465                 [Test] // bug #595918
 466                 public void DecimalLesserScaleTest ()
 467                 {
 468                         string create_tbl = "CREATE TABLE #decimalScaleCheck (decsclcheck DECIMAL (19, 5) null)";
 469                         string create_sp = "CREATE PROCEDURE #sp_bug595918(@decsclcheck decimal(19,5) OUT)"
 470                                 + "AS " + Environment.NewLine
 471                                 + "BEGIN" + Environment.NewLine
 472                                 + "INSERT INTO #decimalScaleCheck values (@decsclcheck)" + Environment.NewLine
 473                                 + "SELECT @decsclcheck=decsclcheck from #decimalScaleCheck" + Environment.NewLine
 474                                 + "END";
 475
 476                         cmd = new SqlCommand (create_tbl, conn);
 477                         cmd.ExecuteNonQuery ();
 478
 479                         cmd.CommandText = create_sp;
 480                         cmd.ExecuteNonQuery ();
 481
 482                         cmd.CommandText = "[#sp_bug595918]";
 483                         cmd.CommandType = CommandType.StoredProcedure;
 484                         SqlParameter pValue = new SqlParameter("@decsclcheck", SqlDbType.Decimal);
 485                         pValue.Value = 128.425;
 486                         pValue.Precision = 19;
 487                         pValue.Scale = 2;
 488                         pValue.Direction = ParameterDirection.InputOutput;
 489                         cmd.Parameters.Add(pValue);
 490                         cmd.ExecuteNonQuery();
 491
 492                         Assert.AreEqual (128.42, pValue.Value, "Stored decimal value is incorrect - LS - Bug#595918");
 493                 }
 494
 495                 int ClientVersion {
 496                         get {
 497                                 return (engine.ClientVersion);
 498                         }
 499                 }
 500         }
 501 }