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

   1 // SqlCommandBuilderTest.cs - NUnit Test Cases for testing the
   2 // SqlCommandBuilder class
   3 //
   4 // Authors:
   5 //      Sureshkumar T (tsureshkumar@novell.com)
   6 //      Veerapuram Varadhan  (vvaradhan@novell.com)
   7 //
   8 // Copyright (c) 2004 Novell Inc., and the individuals listed on the
   9 // ChangeLog entries.
  10 //
  11 //
  12 // Permission is hereby granted, free of charge, to any person
  13 // obtaining a copy of this software and associated documentation
  14 // files (the "Software"), to deal in the Software without
  15 // restriction, including without limitation the rights to use, copy,
  16 // modify, merge, publish, distribute, sublicense, and/or sell copies
  17 // of the Software, and to permit persons to whom the Software is
  18 // furnished to do so, subject to 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
  27 // BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN
  28 // ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
  29 // CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
  30 // SOFTWARE.
  31
  32 using System;
  33 using System.Data;
  34 using System.Data.Common;
  35 using System.Data.SqlClient;
  36 using System.Data.SqlTypes;
  37 using Mono.Data;
  38
  39 using NUnit.Framework;
  40
  41 namespace MonoTests.System.Data
  42 {
  43         [TestFixture]
  44         [Category ("sqlserver")]
  45         public class SqlCommandBuilderTest
  46         {
  47                 SqlConnection conn = null;
  48                 static EngineConfig engine;
  49
  50                 [TestFixtureSetUp]
  51                 public void init ()
  52                 {
  53                         conn = new SqlConnection (ConnectionManager.Singleton.ConnectionString);
  54                         engine = ConnectionManager.Singleton.Engine;
  55                 }
  56
  57                 private static EngineConfig Engine {
  58                         get {
  59                                 return engine;
  60                         }
  61                 }
  62
  63                 [SetUp]
  64                 public void Setup ()
  65                 {
  66                         conn.Open ();
  67                 }
  68                 [TearDown]
  69                 public void TearDown ()
  70                 {
  71                         conn.Close ();
  72                 }
  73
  74                 [Test]
  75                 public void GetInsertCommand1 ()
  76                 {
  77                         SqlCommand cmd = null;
  78
  79                         try {
  80                                 string selectQuery = "select id, fname, lname " +
  81                                         "from employee where id = 1";
  82                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
  83                                 DataSet ds = new DataSet ();
  84                                 da.Fill (ds, "IntTest");
  85                                 Assert.AreEqual (1, ds.Tables.Count);
  86
  87                                 SqlCommandBuilder cb;
  88
  89                                 cb = new SqlCommandBuilder (da);
  90                                 cmd = cb.GetInsertCommand ();
  91                                 Assert.AreEqual ("INSERT INTO [employee] ([id], " +
  92                                         "[fname], [lname]) VALUES (@p1, @p2, @p3)",
  93                                         cmd.CommandText, "#A1");
  94                                 Assert.AreSame (conn, cmd.Connection, "#A2");
  95                                 AssertInsertParameters (cmd, false, "#A3:");
  96                                 Assert.AreSame (cmd, cb.GetInsertCommand (), "#A4");
  97
  98                                 cb.RefreshSchema ();
  99                                 cb.QuotePrefix = "\"";
 100                                 cmd = cb.GetInsertCommand ();
 101                                 Assert.AreEqual ("INSERT INTO \"employee] (\"id], " +
 102                                         "\"fname], \"lname]) VALUES (@p1, @p2, @p3)",
 103                                         cmd.CommandText, "#B1");
 104                                 Assert.AreSame (conn, cmd.Connection, "#B2");
 105                                 AssertInsertParameters (cmd, false, "#B3:");
 106                                 Assert.AreSame (cmd, cb.GetInsertCommand (), "#B4");
 107
 108                                 cb.RefreshSchema ();
 109                                 cb.QuoteSuffix = "\"";
 110                                 cmd = cb.GetInsertCommand ();
 111                                 Assert.AreEqual ("INSERT INTO \"employee\" (\"id\", "
 112                                         + "\"fname\", \"lname\") VALUES (@p1, @p2, @p3)",
 113                                         cmd.CommandText, "#C1");
 114                                 Assert.AreSame (conn, cmd.Connection, "#C2");
 115                                 AssertInsertParameters (cmd, false, "#C3");
 116                                 Assert.AreSame (cmd, cb.GetInsertCommand (), "#C4");
 117                         } finally {
 118                                 if (cmd != null)
 119                                         cmd.Dispose ();
 120                         }
 121                 }
 122
 123                 [Test]
 124                 public void GetInsertCommand1_Expression ()
 125                 {
 126                         SqlCommand cmd = null;
 127
 128                         try {
 129                                 string selectQuery = "select id, fname, lname, " +
 130                                         "id+1 as next_id from employee where " +
 131                                         "id = 1";
 132                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 133                                 DataSet ds = new DataSet ();
 134                                 da.Fill (ds, "IntTest");
 135                                 Assert.AreEqual (1, ds.Tables.Count);
 136
 137                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 138                                 cmd = cb.GetInsertCommand ();
 139                                 Assert.AreEqual ("INSERT INTO [employee] " +
 140                                         "([id], [fname], [lname]) VALUES " +
 141                                         "(@p1, @p2, @p3)", cmd.CommandText, "#1");
 142                                 Assert.AreSame (conn, cmd.Connection, "#2");
 143                                 AssertInsertParameters (cmd, false, "#3:");
 144                         } finally {
 145                                 if (cmd != null)
 146                                         cmd.Dispose ();
 147                         }
 148                 }
 149
 150                 [Test] // GetInsertCommand (Boolean)
 151                 public void GetInsertCommand2 ()
 152                 {
 153                         SqlCommand cmd = null;
 154
 155                         try {
 156                                 string selectQuery = "select id, fname, lname " +
 157                                         "from employee where id = 1";
 158                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 159                                 DataSet ds = new DataSet ();
 160                                 da.Fill (ds, "IntTest");
 161                                 Assert.AreEqual (1, ds.Tables.Count);
 162
 163                                 SqlCommandBuilder cb;
 164
 165                                 cb = new SqlCommandBuilder (da);
 166                                 cmd = cb.GetInsertCommand (true);
 167                                 Assert.AreEqual ("INSERT INTO [employee] ([id], " +
 168                                         "[fname], [lname]) VALUES (@id, @fname, " +
 169                                         "@lname)", cmd.CommandText, "#A1");
 170                                 Assert.AreSame (conn, cmd.Connection, "#A2");
 171                                 AssertInsertParameters (cmd, true, "#A3:");
 172
 173                                 cmd = cb.GetInsertCommand (false);
 174                                 Assert.AreEqual ("INSERT INTO [employee] ([id], " +
 175                                         "[fname], [lname]) VALUES (@id, @fname, " +
 176                                         "@lname)", cmd.CommandText, "#B1");
 177                                 Assert.AreSame (conn, cmd.Connection, "#B2");
 178                                 AssertInsertParameters (cmd, true, "#B3:");
 179
 180                                 cb = new SqlCommandBuilder (da);
 181                                 cmd = cb.GetInsertCommand (false);
 182                                 Assert.AreEqual ("INSERT INTO [employee] ([id], " +
 183                                         "[fname], [lname]) VALUES (@p1, @p2, @p3)",
 184                                         cmd.CommandText, "#C1");
 185                                 Assert.AreSame (conn, cmd.Connection, "#C2");
 186                                 AssertInsertParameters (cmd, false, "#C3:");
 187
 188                                 cmd = cb.GetInsertCommand (true);
 189                                 Assert.AreEqual ("INSERT INTO [employee] ([id], " +
 190                                         "[fname], [lname]) VALUES (@id, @fname, " +
 191                                         "@lname)", cmd.CommandText, "#D1");
 192                                 Assert.AreSame (conn, cmd.Connection, "#D2");
 193                                 AssertInsertParameters (cmd, true, "#D3:");
 194                         } finally {
 195                                 if (cmd != null)
 196                                         cmd.Dispose ();
 197                         }
 198                 }
 199
 200                 [Test] // GetUpdateCommand ()
 201                 public void GetUpdateCommand1 ()
 202                 {
 203                         SqlCommand cmd = null;
 204
 205                         try {
 206                                 string selectQuery = "select id, fname, lname, " +
 207                                         "id+1 as next_id from employee where " +
 208                                         "id = 3 and lname = 'A' and fname = 'B'";
 209                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 210                                 DataSet ds = new DataSet ();
 211                                 da.Fill (ds, "IntTest");
 212                                 Assert.AreEqual (1, ds.Tables.Count);
 213
 214                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 215                                 cmd = cb.GetUpdateCommand ();
 216                                 Assert.AreEqual ("UPDATE [employee] SET [id] = @p1, " +
 217                                         "[fname] = @p2, [lname] = @p3 WHERE (([id] = @p4) " +
 218                                         "AND ([fname] = @p5) AND ((@p6 = 1 " +
 219                                         "AND [lname] IS NULL) OR ([lname] = @p7)))",
 220                                         cmd.CommandText, "#A1");
 221                                 Assert.AreSame (conn, cmd.Connection, "#A2");
 222                                 AssertUpdateParameters (cmd, false, "#A3:");
 223                                 Assert.AreSame (cmd, cb.GetUpdateCommand (), "#A4");
 224
 225                                 cb.RefreshSchema ();
 226                                 cb.QuotePrefix = "\"";
 227                                 cmd = cb.GetUpdateCommand ();
 228                                 Assert.AreEqual ("UPDATE \"employee] SET \"id] = @p1, " +
 229                                         "\"fname] = @p2, \"lname] = @p3 WHERE ((\"id] = @p4) " +
 230                                         "AND (\"fname] = @p5) AND ((@p6 = 1 " +
 231                                         "AND \"lname] IS NULL) OR (\"lname] = @p7)))",
 232                                         cmd.CommandText, "#B1");
 233                                 Assert.AreSame (conn, cmd.Connection, "#B2");
 234                                 AssertUpdateParameters (cmd, false, "#B3:");
 235                                 Assert.AreSame (cmd, cb.GetUpdateCommand (), "#B4");
 236
 237                                 cb.RefreshSchema ();
 238                                 cb.QuoteSuffix = "\"";
 239                                 cmd = cb.GetUpdateCommand ();
 240                                 Assert.AreEqual ("UPDATE \"employee\" SET \"id\" = @p1, " +
 241                                         "\"fname\" = @p2, \"lname\" = @p3 WHERE ((\"id\" = @p4) " +
 242                                         "AND (\"fname\" = @p5) AND ((@p6 = 1 " +
 243                                         "AND \"lname\" IS NULL) OR (\"lname\" = @p7)))",
 244                                         cmd.CommandText, "#C1");
 245                                 Assert.AreSame (conn, cmd.Connection, "#C2");
 246                                 AssertUpdateParameters (cmd, false, "#C3:");
 247                                 Assert.AreSame (cmd, cb.GetUpdateCommand (), "#C4");
 248                         } finally {
 249                                 if (cmd != null)
 250                                         cmd.Dispose ();
 251                         }
 252                 }
 253
 254                 [Test] // GetUpdateCommand ()
 255                 public void GetUpdateCommand1_AutoIncrement ()
 256                 {
 257                         if (ClientVersion == 7)
 258                                 Assert.Ignore ("Key information is not available for temporary tables.");
 259
 260                         SqlCommand cmd = null;
 261
 262                         try {
 263                                 cmd = conn.CreateCommand ();
 264                                 cmd.CommandText = "create table #tmp_table (id int primary key , counter int identity(1,1), value varchar(10))";
 265                                 cmd.ExecuteNonQuery ();
 266
 267                                 string selectQuery = "select id, counter, value, id+1 as next_id from #tmp_table";
 268                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 269                                 DataSet ds = new DataSet ();
 270                                 da.Fill (ds);
 271                                 Assert.AreEqual (1, ds.Tables.Count);
 272                                 Assert.AreEqual (4, ds.Tables [0].Columns.Count);
 273
 274                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 275                                 cmd = cb.GetUpdateCommand ();
 276                                 Assert.AreEqual ("UPDATE [#tmp_table] SET [id] = @p1, " +
 277                                         "[value] = @p2 WHERE (([id] = @p3) AND (" +
 278                                         "[counter] = @p4) AND ((@p5 = 1 AND [value] IS NULL) " +
 279                                         "OR ([value] = @p6)))", cmd.CommandText, "#1");
 280                                 Assert.AreEqual (6, cmd.Parameters.Count, "#2");
 281                         } finally {
 282                                 if (cmd != null)
 283                                         cmd.Dispose ();
 284                         }
 285                 }
 286
 287                 [Test] // GetUpdateCommand ()
 288                 public void GetUpdateCommand1_CheckParameters ()
 289                 {
 290                         SqlCommand cmd = null;
 291
 292                         try {
 293                                 SqlDataAdapter adapter = new SqlDataAdapter (
 294                                         "select id, type_varchar from string_family",
 295                                         conn);
 296                                 SqlCommandBuilder cb = new SqlCommandBuilder (adapter);
 297
 298                                 cmd = cb.GetUpdateCommand ();
 299                                 Assert.AreEqual (5, cmd.Parameters.Count, "#1");
 300                                 Assert.AreEqual (SqlDbType.Int, cmd.Parameters ["@p4"].SqlDbType, "#2");
 301                                 Assert.AreEqual (1, cmd.Parameters ["@p4"].Value, "#3");
 302
 303                                 cmd = cb.GetDeleteCommand ();
 304                                 Assert.AreEqual (3, cmd.Parameters.Count, "#4");
 305                                 Assert.AreEqual (SqlDbType.Int, cmd.Parameters ["@p2"].SqlDbType, "#5");
 306                                 Assert.AreEqual (1, cmd.Parameters ["@p2"].Value, "#6");
 307                         } finally {
 308                                 if (cmd != null)
 309                                         cmd.Dispose ();
 310                         }
 311                 }
 312
 313                 [Test] // GetUpdateCommand (Boolean)
 314                 public void GetUpdateCommand2 ()
 315                 {
 316                         SqlCommand cmd = null;
 317
 318                         try {
 319                                 string selectQuery = "select id, fname, lname, id+1 as next_id from employee where id = 1";
 320                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 321                                 DataSet ds = new DataSet ();
 322                                 da.Fill (ds, "IntTest");
 323                                 Assert.AreEqual (1, ds.Tables.Count);
 324
 325                                 SqlCommandBuilder cb;
 326
 327                                 cb = new SqlCommandBuilder (da);
 328                                 cmd = cb.GetUpdateCommand (true);
 329                                 Assert.AreEqual ("UPDATE [employee] SET [id] = @id, " +
 330                                         "[fname] = @fname, [lname] = @lname WHERE " +
 331                                         "(([id] = @Original_id) AND ([fname] = " +
 332                                         "@Original_fname) AND ((@IsNull_lname = 1 " +
 333                                         "AND [lname] IS NULL) OR ([lname] = " +
 334                                         "@Original_lname)))", cmd.CommandText, "#A1");
 335                                 Assert.AreSame (conn, cmd.Connection, "#A2");
 336                                 AssertUpdateParameters (cmd, true, "#A3:");
 337
 338                                 cmd = cb.GetUpdateCommand (false);
 339                                 Assert.AreEqual ("UPDATE [employee] SET [id] = @id, " +
 340                                         "[fname] = @fname, [lname] = @lname WHERE " +
 341                                         "(([id] = @Original_id) AND ([fname] = " +
 342                                         "@Original_fname) AND ((@IsNull_lname = 1 " +
 343                                         "AND [lname] IS NULL) OR ([lname] = " +
 344                                         "@Original_lname)))", cmd.CommandText, "#B1");
 345                                 Assert.AreSame (conn, cmd.Connection, "#B2");
 346                                 AssertUpdateParameters (cmd, true, "#B3:");
 347
 348                                 cb = new SqlCommandBuilder (da);
 349                                 cmd = cb.GetUpdateCommand (false);
 350                                 Assert.AreEqual ("UPDATE [employee] SET [id] = @p1, " +
 351                                         "[fname] = @p2, [lname] = @p3 WHERE " +
 352                                         "(([id] = @p4) AND ([fname] = @p5) AND " +
 353                                         "((@p6 = 1 AND [lname] IS NULL) OR " +
 354                                         "([lname] = @p7)))", cmd.CommandText, "#C1");
 355                                 Assert.AreSame (conn, cmd.Connection, "#C2");
 356                                 AssertUpdateParameters (cmd, false, "#C3:");
 357
 358                                 cmd = cb.GetUpdateCommand (true);
 359                                 Assert.AreEqual ("UPDATE [employee] SET [id] = @id, " +
 360                                         "[fname] = @fname, [lname] = @lname WHERE " +
 361                                         "(([id] = @Original_id) AND ([fname] = " +
 362                                         "@Original_fname) AND ((@IsNull_lname = 1 " +
 363                                         "AND [lname] IS NULL) OR ([lname] = " +
 364                                         "@Original_lname)))", cmd.CommandText, "#D1");
 365                                 Assert.AreSame (conn, cmd.Connection, "#D2");
 366                                 AssertUpdateParameters (cmd, true, "#D3:");
 367                         } finally {
 368                                 if (cmd != null)
 369                                         cmd.Dispose ();
 370                         }
 371                 }
 372
 373                 [Test] // GetUpdateCommand (Boolean)
 374                 public void GetUpdateCommand2_AutoIncrement ()
 375                 {
 376                         SqlCommand cmd = null;
 377
 378                         try {
 379                                 cmd = conn.CreateCommand ();
 380                                 cmd.CommandText = "create table #tmp_table (id int primary key , counter int identity(1,1), value varchar(10))";
 381                                 cmd.ExecuteNonQuery ();
 382
 383                                 string selectQuery = "select id, counter, value, id+1 as next_id from #tmp_table";
 384                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 385                                 DataSet ds = new DataSet ();
 386                                 da.Fill (ds);
 387                                 Assert.AreEqual (1, ds.Tables.Count);
 388                                 Assert.AreEqual (4, ds.Tables [0].Columns.Count);
 389
 390                                 SqlCommandBuilder cb;
 391
 392                                 cb = new SqlCommandBuilder (da);
 393                                 cmd = cb.GetUpdateCommand (true);
 394                                 Assert.AreEqual ("UPDATE [#tmp_table] SET [id] = @id, " +
 395                                         "[value] = @value WHERE (([id] = @Original_id) " +
 396                                         "AND ([counter] = @Original_counter) AND " +
 397                                         "((@IsNull_value = 1 AND [value] IS NULL) " +
 398                                         "OR ([value] = @Original_value)))",
 399                                         cmd.CommandText, "#A1");
 400                                 Assert.AreEqual (6, cmd.Parameters.Count, "#A2");
 401
 402                                 cmd = cb.GetUpdateCommand (false);
 403                                 Assert.AreEqual ("UPDATE [#tmp_table] SET [id] = @id, " +
 404                                         "[value] = @value WHERE (([id] = @Original_id) " +
 405                                         "AND ([counter] = @Original_counter) AND " +
 406                                         "((@IsNull_value = 1 AND [value] IS NULL) " +
 407                                         "OR ([value] = @Original_value)))",
 408                                         cmd.CommandText, "#B1");
 409                                 Assert.AreEqual (6, cmd.Parameters.Count, "#B2");
 410
 411                                 cb = new SqlCommandBuilder (da);
 412                                 cmd = cb.GetUpdateCommand (false);
 413                                 Assert.AreEqual ("UPDATE [#tmp_table] SET [id] = @p1, " +
 414                                         "[value] = @p2 WHERE (([id] = @p3) " +
 415                                         "AND ([counter] = @p4) AND ((@p5 = 1 " +
 416                                         "AND [value] IS NULL) OR ([value] = @p6)))",
 417                                         cmd.CommandText, "#C1");
 418                                 Assert.AreEqual (6, cmd.Parameters.Count, "#C2");
 419
 420                                 cmd = cb.GetUpdateCommand (true);
 421                                 Assert.AreEqual ("UPDATE [#tmp_table] SET [id] = @id, " +
 422                                         "[value] = @value WHERE (([id] = @Original_id) " +
 423                                         "AND ([counter] = @Original_counter) AND " +
 424                                         "((@IsNull_value = 1 AND [value] IS NULL) " +
 425                                         "OR ([value] = @Original_value)))",
 426                                         cmd.CommandText, "#D1");
 427                                 Assert.AreEqual (6, cmd.Parameters.Count, "#D2");
 428                         } finally {
 429                                 if (cmd != null)
 430                                         cmd.Dispose ();
 431                         }
 432                 }
 433
 434                 [Test] // GetUpdateCommand (Boolean)
 435                 public void GetUpdateDeleteCommand2_CheckParameters ()
 436                 {
 437                         SqlCommand cmd = null;
 438
 439                         try {
 440                                 SqlDataAdapter adapter = new SqlDataAdapter (
 441                                         "select id, type_varchar from string_family",
 442                                         conn);
 443                                 SqlCommandBuilder cb = new SqlCommandBuilder (adapter);
 444
 445                                 SqlCommand updateCommand = cb.GetUpdateCommand (true);
 446                                 Assert.AreEqual (5, updateCommand.Parameters.Count, "#A1");
 447                                 Assert.AreEqual (SqlDbType.VarChar, updateCommand.Parameters ["@type_varchar"].SqlDbType, "#A2");
 448                                 // FIXME: NotWorking
 449                                 //Assert.AreEqual (1, updateCommand.Parameters ["@type_char"].Value, "#A3");
 450
 451                                 SqlCommand delCommand = cb.GetDeleteCommand (true);
 452                                 Assert.AreEqual (3, delCommand.Parameters.Count, "#B");
 453                                 Assert.AreEqual (DbType.Int32, delCommand.Parameters [0].DbType, "#B: DbType (0)");
 454                                 Assert.AreEqual ("@Original_id", delCommand.Parameters [0].ParameterName, "#B: ParameterName (0)");
 455                                 Assert.AreEqual ("id", delCommand.Parameters [0].SourceColumn, "#B: SourceColumn (0)");
 456                                 Assert.AreEqual (SqlDbType.Int, delCommand.Parameters [0].SqlDbType, "#B: SqlDbType (0)");
 457                                 Assert.IsNull (delCommand.Parameters [0].Value, "#B: Value (0)");
 458
 459                                 Assert.AreEqual (DbType.Int32, delCommand.Parameters [1].DbType, "#B: DbType (1)");
 460                                 Assert.AreEqual ("@IsNull_type_varchar", delCommand.Parameters [1].ParameterName, "#B: ParameterName (1)");
 461                                 Assert.AreEqual ("type_varchar", delCommand.Parameters [1].SourceColumn, "#B: SourceColumn (1)");
 462                                 Assert.AreEqual (SqlDbType.Int, delCommand.Parameters [1].SqlDbType, "#B: SqlDbType (1)");
 463                                 Assert.AreEqual (1, delCommand.Parameters [1].Value, "#B: Value (1)");
 464
 465                                 Assert.AreEqual (DbType.AnsiString, delCommand.Parameters [2].DbType, "#B: DbType (2)");
 466                                 Assert.AreEqual ("@Original_type_varchar", delCommand.Parameters [2].ParameterName, "#B: ParameterName (2)");
 467                                 Assert.AreEqual ("type_varchar", delCommand.Parameters [2].SourceColumn, "#B: SourceColumn (2)");
 468                                 Assert.AreEqual (SqlDbType.VarChar, delCommand.Parameters [2].SqlDbType, "#B: SqlDbType (2)");
 469                                 Assert.IsNull (delCommand.Parameters [2].Value, "#B: Value (2)");
 470                         } finally {
 471                                 if (cmd != null)
 472                                         cmd.Dispose ();
 473                         }
 474                 }
 475
 476                 [Test]
 477                 public void GetUpdateCommandDBConcurrencyExceptionTest ()
 478                 {
 479                         string selectQuery = "select id, fname from employee where id = 1";
 480                         SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 481                         DataSet ds = new DataSet ();
 482                         da.Fill (ds, "IntTest");
 483                         Assert.AreEqual (1, ds.Tables.Count);
 484
 485                         SqlCommandBuilder cb = new SqlCommandBuilder (da);
 486                         Assert.IsNotNull (cb);
 487
 488                         DataRow [] rows = ds.Tables [0].Select ("id=1");
 489                         rows [0] [0] = 6660; // non existent
 490                         ds.Tables [0].AcceptChanges (); // moves 6660 to original value
 491                         rows [0] [0] = 1; // moves 6660 as search key into db table
 492                         try {
 493                                 da.Update (rows);
 494                                 Assert.Fail ("#1");
 495                         } catch (DBConcurrencyException ex) {
 496                                 // Concurrency violation: the UpdateCommand
 497                                 // affected 0 of the expected 1 records
 498                                 Assert.AreEqual (typeof (DBConcurrencyException), ex.GetType (), "#3");
 499                                 Assert.IsNull (ex.InnerException, "#4");
 500                                 Assert.IsNotNull (ex.Message, "#5");
 501                                 Assert.AreSame (rows [0], ex.Row, "#6");
 502                                 Assert.AreEqual (1, ex.RowCount, "#7");
 503                         }
 504                 }
 505
 506                 [Test]
 507                 public void GetDeleteCommandDBConcurrencyExceptionTest ()
 508                 {
 509                         string selectQuery = "select id, fname from employee where id = 1";
 510                         SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 511                         DataSet ds = new DataSet ();
 512                         da.Fill (ds, "IntTest");
 513                         Assert.AreEqual (1, ds.Tables.Count);
 514
 515                         SqlCommandBuilder cb = new SqlCommandBuilder (da);
 516                         Assert.IsNotNull (cb);
 517
 518                         DataRow [] rows = ds.Tables [0].Select ("id=1");
 519                         rows [0] [0] = 6660; // non existent
 520                         ds.Tables [0].AcceptChanges (); // moves 6660 to original value
 521                         rows [0].Delete ();  // moves 6660 as search key into db table
 522                         try {
 523                                 da.Update (rows);
 524                                 Assert.Fail ("#1");
 525                         } catch (DBConcurrencyException ex) {
 526                                 // Concurrency violation: the DeleteCommand
 527                                 // affected 0 of the expected 1 records
 528                                 Assert.AreEqual (typeof (DBConcurrencyException), ex.GetType (), "#2");
 529                                 Assert.IsNull (ex.InnerException, "#3");
 530                                 Assert.IsNotNull (ex.Message, "#4");
 531                                 Assert.AreSame (rows [0], ex.Row, "#5");
 532                                 Assert.AreEqual (1, ex.RowCount, "#6");
 533                         }
 534                 }
 535
 536                 [Test] // GetDeleteCommand ()
 537                 public void GetDeleteCommand1 ()
 538                 {
 539                         SqlCommand cmd = null;
 540
 541                         try {
 542                                 string selectQuery = "select id, fname, lname, " +
 543                                         "id+2 as next_id from employee where " +
 544                                         "id = 3 and lname = 'A' and fname = 'B'";
 545                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 546                                 DataSet ds = new DataSet ();
 547                                 da.Fill (ds, "IntTest");
 548                                 Assert.AreEqual (1, ds.Tables.Count);
 549
 550                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 551                                 cmd = cb.GetDeleteCommand ();
 552                                 Assert.AreEqual ("DELETE FROM [employee] WHERE " +
 553                                         "(([id] = @p1) AND ([fname] = @p2) AND " +
 554                                         "((@p3 = 1 AND [lname] IS NULL) OR " +
 555                                         "([lname] = @p4)))", cmd.CommandText, "#A1");
 556                                 Assert.AreSame (conn, cmd.Connection, "#A2");
 557                                 AssertDeleteParameters (cmd, false, "#A3:");
 558                                 Assert.AreSame (cmd, cb.GetDeleteCommand (), "#A4");
 559
 560                                 cb.RefreshSchema ();
 561                                 cb.QuotePrefix = "\"";
 562                                 cmd = cb.GetDeleteCommand ();
 563
 564                                 Assert.AreEqual ("DELETE FROM \"employee] WHERE " +
 565                                         "((\"id] = @p1) AND (\"fname] = @p2) AND " +
 566                                         "((@p3 = 1 AND \"lname] IS NULL) OR " +
 567                                         "(\"lname] = @p4)))", cmd.CommandText, "#B1");
 568                                 Assert.AreSame (conn, cmd.Connection, "#B2");
 569                                 AssertDeleteParameters (cmd, false, "#B3:");
 570                                 Assert.AreSame (cmd, cb.GetDeleteCommand (), "#B4");
 571
 572                                 cb.RefreshSchema ();
 573                                 cb.QuoteSuffix = "\"";
 574                                 cmd = cb.GetDeleteCommand ();
 575
 576                                 Assert.AreEqual ("DELETE FROM \"employee\" WHERE " +
 577                                         "((\"id\" = @p1) AND (\"fname\" = @p2) AND " +
 578                                         "((@p3 = 1 AND \"lname\" IS NULL) OR " +
 579                                         "(\"lname\" = @p4)))", cmd.CommandText, "#C1");
 580                                 Assert.AreSame (conn, cmd.Connection, "#C2");
 581                                 AssertDeleteParameters (cmd, false, "#C3:");
 582                                 Assert.AreSame (cmd, cb.GetDeleteCommand (), "#C4");
 583                         } finally {
 584                                 if (cmd != null)
 585                                         cmd.Dispose ();
 586                         }
 587                 }
 588
 589                 [Test] // GetDeleteCommand ()
 590                 public void GetDeleteCommand1_AutoIncrement ()
 591                 {
 592                         if (ClientVersion == 7)
 593                                 Assert.Ignore ("Key information is not available for temporary tables.");
 594
 595                         SqlCommand cmd = null;
 596
 597                         try {
 598                                 cmd = conn.CreateCommand ();
 599                                 cmd.CommandText = "create table #tmp_table (id int primary key , counter int identity(1,1), value varchar(10))";
 600                                 cmd.ExecuteNonQuery ();
 601
 602                                 string selectQuery = "select id, counter, value, id+1 as next_id from #tmp_table";
 603                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 604                                 DataSet ds = new DataSet ();
 605                                 da.Fill (ds);
 606                                 Assert.AreEqual (1, ds.Tables.Count);
 607                                 Assert.AreEqual (4, ds.Tables [0].Columns.Count);
 608
 609                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 610                                 cmd = cb.GetDeleteCommand ();
 611                                 Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE " +
 612                                         "(([id] = @p1) AND ([counter] = @p2) AND " +
 613                                         "((@p3 = 1 AND [value] IS NULL) OR ([value] = @p4)))",
 614                                         cmd.CommandText, "#1");
 615                                 Assert.AreEqual (4, cmd.Parameters.Count, "#2");
 616                         } finally {
 617                                 if (cmd != null)
 618                                         cmd.Dispose ();
 619                         }
 620                 }
 621
 622                 [Test] // GetDeleteCommand ()
 623                 public void GetDeleteCommand1_CheckParameters ()
 624                 {
 625                         SqlCommand cmd = null;
 626
 627                         try {
 628                                 SqlDataAdapter adapter = new SqlDataAdapter (
 629                                         "select id, type_varchar from string_family",
 630                                         conn);
 631                                 SqlCommandBuilder cb = new SqlCommandBuilder (adapter);
 632
 633                                 cmd = cb.GetDeleteCommand ();
 634                                 Assert.AreEqual (3, cmd.Parameters.Count, "#1");
 635                                 Assert.AreEqual (SqlDbType.Int, cmd.Parameters ["@p2"].SqlDbType, "#2");
 636                                 Assert.AreEqual (1, cmd.Parameters ["@p2"].Value, "#3");
 637                         } finally {
 638                                 if (cmd != null)
 639                                         cmd.Dispose ();
 640                         }
 641                 }
 642
 643                 [Test] // GetDeleteCommand ()
 644                 public void GetDeleteCommand2 ()
 645                 {
 646                         SqlCommand cmd = null;
 647
 648                         try {
 649                                 string selectQuery = "select id, fname, lname, id+2 as next_id from employee where id = 3";
 650                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 651                                 DataSet ds = new DataSet ();
 652                                 da.Fill (ds, "IntTest");
 653                                 Assert.AreEqual (1, ds.Tables.Count);
 654
 655                                 SqlCommandBuilder cb;
 656
 657                                 cb = new SqlCommandBuilder (da);
 658                                 cmd = cb.GetDeleteCommand (true);
 659                                 Assert.AreEqual ("DELETE FROM [employee] WHERE " +
 660                                         "(([id] = @Original_id) AND ([fname] = " +
 661                                         "@Original_fname) AND ((@IsNull_lname = 1 " +
 662                                         "AND [lname] IS NULL) OR ([lname] = " +
 663                                         "@Original_lname)))", cmd.CommandText, "#A1");
 664                                 Assert.AreSame (conn, cmd.Connection, "#A2");
 665                                 AssertDeleteParameters (cmd, true, "#A3:");
 666                                 Assert.AreSame (cmd, cb.GetDeleteCommand (true), "#A4");
 667
 668                                 cmd = cb.GetDeleteCommand (false);
 669                                 Assert.AreEqual ("DELETE FROM [employee] WHERE " +
 670                                         "(([id] = @Original_id) AND ([fname] = " +
 671                                         "@Original_fname) AND ((@IsNull_lname = 1 " +
 672                                         "AND [lname] IS NULL) OR ([lname] = " +
 673                                         "@Original_lname)))", cmd.CommandText, "#B1");
 674                                 Assert.AreSame (conn, cmd.Connection, "#B2");
 675                                 AssertDeleteParameters (cmd, true, "#B3:");
 676                                 Assert.AreSame (cmd, cb.GetDeleteCommand (false), "#B4");
 677
 678                                 cb = new SqlCommandBuilder (da);
 679                                 cmd = cb.GetDeleteCommand (false);
 680                                 Assert.AreEqual ("DELETE FROM [employee] WHERE " +
 681                                         "(([id] = @p1) AND ([fname] = @p2) AND " +
 682                                         "((@p3 = 1 AND [lname] IS NULL) OR " +
 683                                         "([lname] = @p4)))", cmd.CommandText, "#C1");
 684                                 Assert.AreSame (conn, cmd.Connection, "#C2");
 685                                 AssertDeleteParameters (cmd, false, "#C3:");
 686                                 Assert.AreSame (cmd, cb.GetDeleteCommand (false), "#C4");
 687
 688                                 cmd = cb.GetDeleteCommand (true);
 689                                 Assert.AreEqual ("DELETE FROM [employee] WHERE " +
 690                                         "(([id] = @Original_id) AND ([fname] = " +
 691                                         "@Original_fname) AND ((@IsNull_lname = 1 " +
 692                                         "AND [lname] IS NULL) OR ([lname] = " +
 693                                         "@Original_lname)))", cmd.CommandText, "#D1");
 694                                 Assert.AreSame (conn, cmd.Connection, "#D2");
 695                                 AssertDeleteParameters (cmd, true, "#D3:");
 696                                 Assert.AreSame (cmd, cb.GetDeleteCommand (false), "#D4");
 697                         } finally {
 698                                 if (cmd != null)
 699                                         cmd.Dispose ();
 700                         }
 701                 }
 702
 703                 [Test] // GetDeleteCommand (Boolean)
 704                 public void GetDeleteCommand2_AutoIncrement ()
 705                 {
 706                         SqlCommand cmd = null;
 707
 708                         try {
 709                                 cmd = conn.CreateCommand ();
 710                                 cmd.CommandText = "create table #tmp_table (id int primary key , counter int identity(1,1), value varchar(10))";
 711                                 cmd.ExecuteNonQuery ();
 712
 713                                 string selectQuery = "select id, counter, value, id+1 as next_id from #tmp_table";
 714                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 715                                 DataSet ds = new DataSet ();
 716                                 da.Fill (ds);
 717                                 Assert.AreEqual (1, ds.Tables.Count);
 718                                 Assert.AreEqual (4, ds.Tables [0].Columns.Count);
 719
 720                                 SqlCommandBuilder cb;
 721
 722                                 cb = new SqlCommandBuilder (da);
 723                                 cmd = cb.GetDeleteCommand (true);
 724                                 Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE " +
 725                                         "(([id] = @Original_id) AND ([counter] = @Original_counter) " +
 726                                         "AND ((@IsNull_value = 1 AND [value] IS NULL) " +
 727                                         "OR ([value] = @Original_value)))",
 728                                         cmd.CommandText, "#A1");
 729                                 Assert.AreEqual (4, cmd.Parameters.Count, "#A2");
 730
 731                                 cmd = cb.GetDeleteCommand (false);
 732                                 Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE " +
 733                                         "(([id] = @Original_id) AND ([counter] = @Original_counter) " +
 734                                         "AND ((@IsNull_value = 1 AND [value] IS NULL) " +
 735                                         "OR ([value] = @Original_value)))",
 736                                         cmd.CommandText, "#B1");
 737                                 Assert.AreEqual (4, cmd.Parameters.Count, "#B2");
 738
 739                                 cb = new SqlCommandBuilder (da);
 740                                 cmd = cb.GetDeleteCommand (false);
 741                                 Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE " +
 742                                         "(([id] = @p1) AND ([counter] = @p2) " +
 743                                         "AND ((@p3 = 1 AND [value] IS NULL) " +
 744                                         "OR ([value] = @p4)))",
 745                                         cmd.CommandText, "#C1");
 746                                 Assert.AreEqual (4, cmd.Parameters.Count, "#C2");
 747
 748                                 cmd = cb.GetDeleteCommand (true);
 749                                 Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE " +
 750                                         "(([id] = @Original_id) AND ([counter] = @Original_counter) " +
 751                                         "AND ((@IsNull_value = 1 AND [value] IS NULL) " +
 752                                         "OR ([value] = @Original_value)))",
 753                                         cmd.CommandText, "#D1");
 754                                 Assert.AreEqual (4, cmd.Parameters.Count, "#D2");
 755                         } finally {
 756                                 if (cmd != null)
 757                                         cmd.Dispose ();
 758                         }
 759                 }
 760
 761                 [Test]
 762                 public void DefaultProperties ()
 763                 {
 764                         SqlCommandBuilder cb = new SqlCommandBuilder ();
 765                         Assert.AreEqual ("[", cb.QuotePrefix, "#5");
 766                         Assert.AreEqual ("]", cb.QuoteSuffix, "#6");
 767                         Assert.AreEqual (".", cb.CatalogSeparator, "#2");
 768                         //Assert.AreEqual ("", cb.DecimalSeparator, "#3");
 769                         Assert.AreEqual (".", cb.SchemaSeparator, "#4");
 770                         Assert.AreEqual (CatalogLocation.Start, cb.CatalogLocation, "#1");
 771                         Assert.AreEqual ("[monotest]", cb.QuoteIdentifier ("monotest"), "#7");
 772                         Assert.AreEqual ("\"monotest\"", cb.UnquoteIdentifier ("\"monotest\""), "#8");
 773                         //Assert.AreEqual (cb.ConflictOption.CompareAllSearchableValues, cb.ConflictDetection);
 774                         // FIXME: test SetAllValues
 775                 }
 776
 777                 // FIXME:  Add tests for examining RowError
 778                 // FIXME: Add test for ContinueUpdateOnError property
 779
 780                 [Test]
 781                 public void CheckParameters_BuiltCommand ()
 782                 {
 783                         SqlDataAdapter adapter = new SqlDataAdapter ("select id,type_varchar from string_family", conn);
 784                         SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
 785                         DataSet ds = new DataSet ();
 786                         adapter.Fill(ds);
 787
 788                         Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#1");
 789
 790                         DataRow row_rsInput = ds.Tables[0].NewRow();
 791                         row_rsInput["id"] = 100;
 792                         row_rsInput["type_varchar"] = "ttt";
 793                         ds.Tables[0].Rows.Add(row_rsInput);
 794
 795                         Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#2");
 796
 797                         row_rsInput = ds.Tables[0].NewRow();
 798                         row_rsInput["id"] = 101;
 799                         row_rsInput["type_varchar"] = "ttt";
 800                         ds.Tables[0].Rows.Add(row_rsInput);
 801
 802                         Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#3");
 803                 }
 804
 805                 [Test]
 806                 public void DeriveParameters ()
 807                 {
 808                         SqlCommand cmd = null;
 809                         SqlParameter param;
 810
 811                         try {
 812                                 cmd = conn.CreateCommand ();
 813                                 cmd.CommandText = "sp_326182a";
 814                                 cmd.CommandType = CommandType.StoredProcedure;
 815                                 cmd.CommandTimeout = 90;
 816                                 cmd.Parameters.Add ("dummy", SqlDbType.Image, 5);
 817
 818                                 SqlCommandBuilder.DeriveParameters (cmd);
 819                                 Assert.AreEqual (5, cmd.Parameters.Count, "#A1");
 820
 821                                 cmd = conn.CreateCommand ();
 822                                 cmd.CommandText = "sp_326182b";
 823                                 cmd.CommandType = CommandType.StoredProcedure;
 824                                 cmd.CommandTimeout = 90;
 825                                 cmd.Parameters.Add ("dummy", SqlDbType.Image, 5);
 826
 827                                 SqlCommandBuilder.DeriveParameters (cmd);
 828                                 Assert.AreEqual (4, cmd.Parameters.Count, "#A");
 829
 830                                 param = cmd.Parameters [0];
 831                                 Assert.AreEqual (ParameterDirection.ReturnValue, param.Direction, "#B:Direction");
 832                                 Assert.IsFalse (param.IsNullable, "#B:IsNullable");
 833                                 if (ClientVersion == 7)
 834                                         Assert.AreEqual ("RETURN_VALUE", param.ParameterName, "#B:ParameterName");
 835                                 else
 836                                         Assert.AreEqual ("@RETURN_VALUE", param.ParameterName, "#B:ParameterName");
 837                                 Assert.AreEqual (0, param.Precision, "#B:Precision");
 838                                 Assert.AreEqual (0, param.Scale, "#B:Scale");
 839                                 //Assert.AreEqual (0, param.Size, "#B:Size");
 840                                 Assert.AreEqual (SqlDbType.Int, param.SqlDbType, "#B:SqlDbType");
 841                                 Assert.IsNull (param.Value, "#B:Value");
 842
 843                                 param = cmd.Parameters [1];
 844                                 Assert.AreEqual (ParameterDirection.Input, param.Direction, "#C:Direction");
 845                                 Assert.IsFalse (param.IsNullable, "#C:IsNullable");
 846                                 Assert.AreEqual ("@param0", param.ParameterName, "#C:ParameterName");
 847                                 Assert.AreEqual (0, param.Precision, "#C:Precision");
 848                                 Assert.AreEqual (0, param.Scale, "#C:Scale");
 849                                 //Assert.AreEqual (0, param.Size, "#C:Size");
 850                                 Assert.AreEqual (SqlDbType.Int, param.SqlDbType, "#C:SqlDbType");
 851                                 Assert.IsNull (param.Value, "#C:Value");
 852
 853                                 param = cmd.Parameters [2];
 854                                 Assert.AreEqual (ParameterDirection.InputOutput, param.Direction, "#D:Direction");
 855                                 Assert.IsFalse (param.IsNullable, "#D:IsNullable");
 856                                 Assert.AreEqual ("@param1", param.ParameterName, "#D:ParameterName");
 857                                 Assert.AreEqual (5, param.Precision, "#D:Precision");
 858                                 if (ClientVersion == 7)
 859                                         Assert.AreEqual (2, param.Scale, "#D:Scale");
 860                                 else
 861                                         Assert.AreEqual (3, param.Scale, "#D:Scale");
 862                                 //Assert.AreEqual (0, param.Size, "#D:Size");
 863                                 Assert.AreEqual (SqlDbType.Decimal, param.SqlDbType, "#D:SqlDbType");
 864                                 Assert.IsNull (param.Value, "#D:Value");
 865
 866                                 param = cmd.Parameters [3];
 867                                 Assert.AreEqual (ParameterDirection.Input, param.Direction, "#E:Direction");
 868                                 Assert.IsFalse (param.IsNullable, "#E:IsNullable");
 869                                 Assert.AreEqual ("@param2", param.ParameterName, "#E:ParameterName");
 870                                 Assert.AreEqual (0, param.Precision, "#E:Precision");
 871                                 Assert.AreEqual (0, param.Scale, "#E:Scale");
 872                                 Assert.AreEqual (12, param.Size, "#E:Size");
 873                                 Assert.AreEqual (SqlDbType.VarChar, param.SqlDbType, "#E:SqlDbType");
 874                                 Assert.IsNull (param.Value, "#E:Value");
 875
 876                                 cmd.Parameters ["@param0"].Value = 5;
 877                                 cmd.Parameters ["@param1"].Value = 4.000m;
 878                                 cmd.Parameters ["@param2"].Value = DBNull.Value;
 879                                 cmd.ExecuteNonQuery ();
 880                                 if (ClientVersion == 7)
 881                                         Assert.AreEqual (666, cmd.Parameters ["RETURN_VALUE"].Value, "#F1");
 882                                 else
 883                                         Assert.AreEqual (666, cmd.Parameters ["@RETURN_VALUE"].Value, "#F1");
 884                                 Assert.AreEqual (5, cmd.Parameters ["@param0"].Value, "#F2");
 885                                 Assert.AreEqual (11m, cmd.Parameters ["@param1"].Value, "#F3");
 886                                 Assert.AreEqual (DBNull.Value, cmd.Parameters ["@param2"].Value, "#F4");
 887                         } finally {
 888                                 if (cmd != null)
 889                                         cmd.Dispose ();
 890                         }
 891                 }
 892
 893                 [Test]
 894                 public void QuotePrefix_DeleteCommand_Generated ()
 895                 {
 896                         SqlCommand cmd = null;
 897
 898                         try {
 899                                 string selectQuery = "select id, lname from employee where id = 3";
 900                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 901                                 DataSet ds = new DataSet ();
 902                                 da.Fill (ds, "IntTest");
 903
 904                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 905                                 cmd = cb.GetDeleteCommand ();
 906                                 Assert.AreEqual ("[", cb.QuotePrefix, "#1");
 907                                 try {
 908                                         cb.QuotePrefix = "\"";
 909                                         Assert.Fail ("#2");
 910                                 } catch (InvalidOperationException ex) {
 911                                         // The QuotePrefix and QuoteSuffix properties
 912                                         // cannot be changed once an Insert, Update, or
 913                                         // Delete command has been generated
 914                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
 915                                         Assert.IsNull (ex.InnerException, "#4");
 916                                         Assert.IsNotNull (ex.Message, "#5");
 917                                 }
 918                                 Assert.AreEqual ("[", cb.QuotePrefix, "#6");
 919                                 cb.RefreshSchema ();
 920                                 cb.QuotePrefix = "\"";
 921                         } finally {
 922                                 if (cmd != null)
 923                                         cmd.Dispose ();
 924                         }
 925                 }
 926
 927                 [Test]
 928                 public void QuotePrefix_InsertCommand_Generated ()
 929                 {
 930                         SqlCommand cmd = null;
 931
 932                         try {
 933                                 string selectQuery = "select id, lname from employee where id = 3";
 934                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 935                                 DataSet ds = new DataSet ();
 936                                 da.Fill (ds, "IntTest");
 937
 938                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 939                                 cmd = cb.GetInsertCommand ();
 940                                 Assert.AreEqual ("[", cb.QuotePrefix, "#1");
 941                                 try {
 942                                         cb.QuotePrefix = "\"";
 943                                         Assert.Fail ("#2");
 944                                 } catch (InvalidOperationException ex) {
 945                                         // The QuotePrefix and QuoteSuffix properties
 946                                         // cannot be changed once an Insert, Update, or
 947                                         // Delete command has been generated
 948                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
 949                                         Assert.IsNull (ex.InnerException, "#4");
 950                                         Assert.IsNotNull (ex.Message, "#5");
 951                                 }
 952                                 Assert.AreEqual ("[", cb.QuotePrefix, "#6");
 953                                 cb.RefreshSchema ();
 954                                 cb.QuotePrefix = "\"";
 955                         } finally {
 956                                 if (cmd != null)
 957                                         cmd.Dispose ();
 958                         }
 959                 }
 960
 961                 [Test]
 962                 public void QuotePrefix_UpdateCommand_Generated ()
 963                 {
 964                         SqlCommand cmd = null;
 965
 966                         try {
 967                                 string selectQuery = "select id, lname from employee where id = 3";
 968                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
 969                                 DataSet ds = new DataSet ();
 970                                 da.Fill (ds, "IntTest");
 971
 972                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 973                                 cmd = cb.GetUpdateCommand ();
 974                                 Assert.AreEqual ("[", cb.QuotePrefix, "#1");
 975                                 try {
 976                                         cb.QuotePrefix = "\"";
 977                                         Assert.Fail ("#2");
 978                                 } catch (InvalidOperationException ex) {
 979                                         // The QuotePrefix and QuoteSuffix properties
 980                                         // cannot be changed once an Insert, Update, or
 981                                         // Delete command has been generated
 982                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
 983                                         Assert.IsNull (ex.InnerException, "#4");
 984                                         Assert.IsNotNull (ex.Message, "#5");
 985                                 }
 986                                 Assert.AreEqual ("[", cb.QuotePrefix, "#6");
 987                                 cb.RefreshSchema ();
 988                                 cb.QuotePrefix = "\"";
 989                         } finally {
 990                                 if (cmd != null)
 991                                         cmd.Dispose ();
 992                         }
 993                 }
 994
 995                 [Test]
 996                 public void QuoteSuffix_DeleteCommand_Generated ()
 997                 {
 998                         SqlCommand cmd = null;
 999
1000                         try {
1001                                 string selectQuery = "select id, lname from employee where id = 3";
1002                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
1003                                 DataSet ds = new DataSet ();
1004                                 da.Fill (ds, "IntTest");
1005
1006                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
1007                                 cmd = cb.GetDeleteCommand ();
1008                                 Assert.AreEqual ("]", cb.QuoteSuffix, "#1");
1009                                 try {
1010                                         cb.QuoteSuffix = "\"";
1011                                         Assert.Fail ("#2");
1012                                 } catch (InvalidOperationException ex) {
1013                                         // The QuotePrefix and QuoteSuffix properties
1014                                         // cannot be changed once an Insert, Update, or
1015                                         // Delete command has been generated
1016                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
1017                                         Assert.IsNull (ex.InnerException, "#4");
1018                                         Assert.IsNotNull (ex.Message, "#5");
1019                                 }
1020                                 Assert.AreEqual ("]", cb.QuoteSuffix, "#6");
1021                                 cb.RefreshSchema ();
1022                                 cb.QuoteSuffix = "\"";
1023                         } finally {
1024                                 if (cmd != null)
1025                                         cmd.Dispose ();
1026                         }
1027                 }
1028
1029                 [Test]
1030                 public void QuoteSuffix_InsertCommand_Generated ()
1031                 {
1032                         SqlCommand cmd = null;
1033
1034                         try {
1035                                 string selectQuery = "select id, lname from employee where id = 3";
1036                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
1037                                 DataSet ds = new DataSet ();
1038                                 da.Fill (ds, "IntTest");
1039
1040                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
1041                                 cmd = cb.GetInsertCommand ();
1042                                 Assert.AreEqual ("]", cb.QuoteSuffix, "#1");
1043                                 try {
1044                                         cb.QuoteSuffix = "\"";
1045                                         Assert.Fail ("#2");
1046                                 } catch (InvalidOperationException ex) {
1047                                         // The QuotePrefix and QuoteSuffix properties
1048                                         // cannot be changed once an Insert, Update, or
1049                                         // Delete command has been generated
1050                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
1051                                         Assert.IsNull (ex.InnerException, "#4");
1052                                         Assert.IsNotNull (ex.Message, "#5");
1053                                 }
1054                                 Assert.AreEqual ("]", cb.QuoteSuffix, "#6");
1055                                 cb.RefreshSchema ();
1056                                 cb.QuoteSuffix = "\"";
1057                         } finally {
1058                                 if (cmd != null)
1059                                         cmd.Dispose ();
1060                         }
1061                 }
1062
1063                 [Test]
1064                 public void QuoteSuffix_UpdateCommand_Generated ()
1065                 {
1066                         SqlCommand cmd = null;
1067
1068                         try {
1069                                 string selectQuery = "select id, lname from employee where id = 3";
1070                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
1071                                 DataSet ds = new DataSet ();
1072                                 da.Fill (ds, "IntTest");
1073
1074                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
1075                                 cmd = cb.GetUpdateCommand ();
1076                                 Assert.AreEqual ("]", cb.QuoteSuffix, "#1");
1077                                 try {
1078                                         cb.QuoteSuffix = "\"";
1079                                         Assert.Fail ("#2");
1080                                 } catch (InvalidOperationException ex) {
1081                                         // The QuotePrefix and QuoteSuffix properties
1082                                         // cannot be changed once an Insert, Update, or
1083                                         // Delete command has been generated
1084                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
1085                                         Assert.IsNull (ex.InnerException, "#4");
1086                                         Assert.IsNotNull (ex.Message, "#5");
1087                                 }
1088                                 Assert.AreEqual ("]", cb.QuoteSuffix, "#6");
1089                                 cb.RefreshSchema ();
1090                                 cb.QuoteSuffix = "\"";
1091                         } finally {
1092                                 if (cmd != null)
1093                                         cmd.Dispose ();
1094                         }
1095                 }
1096
1097                 static void AssertDeleteParameters (SqlCommand cmd, bool useColumnsForParameterNames, string prefix)
1098                 {
1099                         SqlParameter param;
1100
1101                         Assert.AreEqual (4, cmd.Parameters.Count, prefix + "Count");
1102
1103                         param = cmd.Parameters [0];
1104                         Assert.AreEqual (DbType.Int32, param.DbType, prefix + "DbType (0)");
1105                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (0)");
1106                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (0)");
1107                         Assert.AreEqual (0, param.Offset, prefix + "Offset (0)");
1108                         if (useColumnsForParameterNames)
1109                                 Assert.AreEqual ("@Original_id", param.ParameterName, prefix + "ParameterName (0)");
1110                         else
1111                                 Assert.AreEqual ("@p1", param.ParameterName, prefix + "ParameterName (0)");
1112
1113                         if (ClientVersion > 7)
1114                                 Assert.AreEqual (0, param.Precision, prefix + "Precision (0)");
1115                         else
1116                                 Assert.AreEqual (10, param.Precision, prefix + "Precision (0)");
1117
1118                         Assert.AreEqual (0, param.Scale, prefix + "Scale (0)");
1119                         //Assert.AreEqual (0, param.Size, prefix + "Size (0)");
1120                         Assert.AreEqual ("id", param.SourceColumn, prefix + "SourceColumn (0)");
1121                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (0)");
1122                         Assert.AreEqual (DataRowVersion.Original, param.SourceVersion, prefix + "SourceVersion (0)");
1123                         Assert.AreEqual (SqlDbType.Int, param.SqlDbType, prefix + "SqlDbType (0)");
1124                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (0)");
1125                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (0)");
1126                         Assert.IsNull (param.Value, prefix + "Value (0)");
1127                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (0)");
1128                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (0)");
1129                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (0)");
1130
1131 #if ONLY_1_1
1132                         param = cmd.Parameters [1];
1133                         Assert.AreEqual (DbType.Int32, param.DbType, prefix + "DbType (1)");
1134                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (1)");
1135                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (1)");
1136                         Assert.AreEqual (0, param.Offset, prefix + "Offset (1)");
1137                         Assert.AreEqual ("@p2", param.ParameterName, prefix + "ParameterName (1)");
1138                         Assert.AreEqual (0, param.Precision, prefix + "Precision (1)");
1139                         Assert.AreEqual (0, param.Scale, prefix + "Scale (1)");
1140                         //Assert.AreEqual (0, param.Size, prefix + "Size (1)");
1141                         Assert.AreEqual (string.Empty, param.SourceColumn, prefix + "SourceColumn (1)");
1142                         Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, prefix + "SourceVersion (1)");
1143                         Assert.AreEqual (SqlDbType.Int, param.SqlDbType, prefix + "SqlDbType (1)");
1144                         Assert.AreEqual (1, param.Value, prefix + "Value (1)");
1145 #endif
1146
1147                         param = cmd.Parameters [1];
1148                         Assert.AreEqual (DbType.AnsiString, param.DbType, prefix + "DbType (2)");
1149                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (2)");
1150                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (2)");
1151                         Assert.AreEqual (0, param.Offset, prefix + "Offset (2)");
1152                         if (useColumnsForParameterNames)
1153                                 Assert.AreEqual ("@Original_fname", param.ParameterName, prefix + "ParameterName (2)");
1154                         else
1155                                 Assert.AreEqual ("@p2", param.ParameterName, prefix + "ParameterName (2)");
1156                         Assert.AreEqual (0, param.Precision, prefix + "Precision (2)");
1157                         Assert.AreEqual (0, param.Scale, prefix + "Scale (2)");
1158                         Assert.AreEqual (0, param.Size, prefix + "Size (2)");
1159                         Assert.AreEqual ("fname", param.SourceColumn, prefix + "SourceColumn (2)");
1160                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (2)");
1161                         Assert.AreEqual (DataRowVersion.Original, param.SourceVersion, prefix + "SourceVersion (2)");
1162                         Assert.AreEqual (SqlDbType.VarChar, param.SqlDbType, prefix + "SqlDbType (2)");
1163                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (2)");
1164                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (2)");
1165                         Assert.IsNull (param.Value, prefix + "Value (2)");
1166                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (2)");
1167                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (2)");
1168                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (2)");
1169
1170                         param = cmd.Parameters [2];
1171                         Assert.AreEqual (DbType.Int32, param.DbType, prefix + "DbType (3)");
1172                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (3)");
1173                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (3)");
1174                         Assert.AreEqual (0, param.Offset, prefix + "Offset (3)");
1175                         if (useColumnsForParameterNames)
1176                                 Assert.AreEqual ("@IsNull_lname", param.ParameterName, prefix + "ParameterName (3)");
1177                         else
1178                                 Assert.AreEqual ("@p3", param.ParameterName, prefix + "ParameterName (3)");
1179                         Assert.AreEqual (0, param.Precision, prefix + "Precision (3)");
1180                         Assert.AreEqual (0, param.Scale, prefix + "Scale (3)");
1181                         //Assert.AreEqual (0, param.Size, prefix + "Size (3)");
1182                         Assert.AreEqual ("lname", param.SourceColumn, prefix + "SourceColumn (3)");
1183                         Assert.IsTrue (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (3)");
1184                         Assert.AreEqual (DataRowVersion.Original, param.SourceVersion, prefix + "SourceVersion (3)");
1185                         Assert.AreEqual (SqlDbType.Int, param.SqlDbType, prefix + "SqlDbType (3)");
1186                         Assert.AreEqual (new SqlInt32 (1), param.SqlValue, prefix + "SqlValue (3)");
1187                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (3)");
1188                         Assert.AreEqual (1, param.Value, prefix + "Value (3)");
1189                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (3)");
1190                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (3)");
1191                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (3)");
1192
1193                         param = cmd.Parameters [3];
1194                         Assert.AreEqual (DbType.AnsiString, param.DbType, prefix + "DbType (4)");
1195                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (4)");
1196                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (4)");
1197                         Assert.AreEqual (0, param.Offset, prefix + "Offset (4)");
1198                         if (useColumnsForParameterNames)
1199                                 Assert.AreEqual ("@Original_lname", param.ParameterName, prefix + "ParameterName (4)");
1200                         else
1201                                 Assert.AreEqual ("@p4", param.ParameterName, prefix + "ParameterName (4)");
1202                         Assert.AreEqual (0, param.Precision, prefix + "Precision (4)");
1203                         Assert.AreEqual (0, param.Scale, prefix + "Scale (4)");
1204                         Assert.AreEqual (0, param.Size, prefix + "Size (4)");
1205                         Assert.AreEqual ("lname", param.SourceColumn, prefix + "SourceColumn (4)");
1206                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (4)");
1207                         Assert.AreEqual (DataRowVersion.Original, param.SourceVersion, prefix + "SourceVersion (4)");
1208                         Assert.AreEqual (SqlDbType.VarChar, param.SqlDbType, prefix + "SqlDbType (4)");
1209                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (4)");
1210                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (4)");
1211                         Assert.IsNull (param.Value, prefix + "Value (4)");
1212                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (4)");
1213                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (4)");
1214                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (4)");
1215                 }
1216
1217                 static void AssertInsertParameters (SqlCommand cmd, bool useColumnsForParameterNames, string prefix)
1218                 {
1219                         SqlParameter param;
1220
1221                         Assert.AreEqual (3, cmd.Parameters.Count, prefix + "Count");
1222
1223                         param = cmd.Parameters [0];
1224                         Assert.AreEqual (DbType.Int32, param.DbType, prefix + "DbType (0)");
1225                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (0)");
1226                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (0)");
1227                         Assert.AreEqual (0, param.Offset, prefix + "Offset (0)");
1228                         if (useColumnsForParameterNames)
1229                                 Assert.AreEqual ("@id", param.ParameterName, prefix + "ParameterName (0)");
1230                         else
1231                                 Assert.AreEqual ("@p1", param.ParameterName, prefix + "ParameterName (0)");
1232
1233                         if (ClientVersion > 7)
1234                                 Assert.AreEqual (0, param.Precision, prefix + "Precision (0)");
1235                         else
1236                                 Assert.AreEqual (10, param.Precision, prefix + "Precision (0)");
1237
1238                         Assert.AreEqual (0, param.Scale, prefix + "Scale (0)");
1239                         //Assert.AreEqual (0, param.Size, prefix + "Size (0)");
1240                         Assert.AreEqual ("id", param.SourceColumn, prefix + "SourceColumn (0)");
1241                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (0)");
1242                         Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, prefix + "SourceVersion (0)");
1243                         Assert.AreEqual (SqlDbType.Int, param.SqlDbType, prefix + "SqlDbType (0)");
1244                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (0)");
1245                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (0)");
1246                         Assert.IsNull (param.Value, prefix + "Value (0)");
1247                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (0)");
1248                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (0)");
1249                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (0)");
1250
1251                         param = cmd.Parameters [1];
1252                         Assert.AreEqual (DbType.AnsiString, param.DbType, prefix + "DbType (1)");
1253                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (1)");
1254                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (1)");
1255                         Assert.AreEqual (0, param.Offset, prefix + "Offset (1)");
1256                         if (useColumnsForParameterNames)
1257                                 Assert.AreEqual ("@fname", param.ParameterName, prefix + "ParameterName (1)");
1258                         else
1259                                 Assert.AreEqual ("@p2", param.ParameterName, prefix + "ParameterName (1)");
1260                         Assert.AreEqual (0, param.Precision, prefix + "Precision (1)");
1261                         Assert.AreEqual (0, param.Scale, prefix + "Scale (1)");
1262                         Assert.AreEqual (0, param.Size, prefix + "Size (1)");
1263                         Assert.AreEqual ("fname", param.SourceColumn, prefix + "SourceColumn (1)");
1264                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (1)");
1265                         Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, prefix + "SourceVersion (1)");
1266                         Assert.AreEqual (SqlDbType.VarChar, param.SqlDbType, prefix + "SqlDbType (1)");
1267                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (1)");
1268                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (1)");
1269                         Assert.IsNull (param.Value, prefix + "Value (1)");
1270                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (1)");
1271                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (1)");
1272                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (1)");
1273
1274                         param = cmd.Parameters [2];
1275                         Assert.AreEqual (DbType.AnsiString, param.DbType, prefix + "DbType (2)");
1276                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (2)");
1277                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (2)");
1278                         Assert.AreEqual (0, param.Offset, prefix + "Offset (2)");
1279                         if (useColumnsForParameterNames)
1280                                 Assert.AreEqual ("@lname", param.ParameterName, prefix + "ParameterName (2)");
1281                         else
1282                                 Assert.AreEqual ("@p3", param.ParameterName, prefix + "ParameterName (2)");
1283                         Assert.AreEqual (0, param.Precision, prefix + "Precision (2)");
1284                         Assert.AreEqual (0, param.Scale, prefix + "Scale (2)");
1285                         Assert.AreEqual (0, param.Size, prefix + "Size (2)");
1286                         Assert.AreEqual ("lname", param.SourceColumn, prefix + "SourceColumn (2)");
1287                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (2)");
1288                         Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, prefix + "SourceVersion (2)");
1289                         Assert.AreEqual (SqlDbType.VarChar, param.SqlDbType, prefix + "SqlDbType (2)");
1290                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (2)");
1291                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (2)");
1292                         Assert.IsNull (param.Value, prefix + "Value (2)");
1293                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (2)");
1294                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (2)");
1295                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (2)");
1296                 }
1297
1298                 static void AssertUpdateParameters (SqlCommand cmd, bool useColumnsForParameterNames, string prefix)
1299                 {
1300                         SqlParameter param;
1301
1302                         Assert.AreEqual (7, cmd.Parameters.Count, prefix + "Count");
1303
1304                         param = cmd.Parameters [0];
1305                         Assert.AreEqual (DbType.Int32, param.DbType, prefix + "DbType (0)");
1306                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (0)");
1307                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (0)");
1308                         Assert.AreEqual (0, param.Offset, prefix + "Offset (0)");
1309                         if (useColumnsForParameterNames)
1310                                 Assert.AreEqual ("@id", param.ParameterName, prefix + "ParameterName (0)");
1311                         else
1312                                 Assert.AreEqual ("@p1", param.ParameterName, prefix + "ParameterName (0)");
1313
1314                         if (ClientVersion > 7)
1315                                 Assert.AreEqual (0, param.Precision, prefix + "Precision (0)");
1316                         else
1317                                 Assert.AreEqual (10, param.Precision, prefix + "Precision (0)");
1318
1319                         Assert.AreEqual (0, param.Scale, prefix + "Scale (0)");
1320                         //Assert.AreEqual (0, param.Size, prefix + "Size (0)");
1321                         Assert.AreEqual ("id", param.SourceColumn, prefix + "SourceColumn (0)");
1322                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (0)");
1323                         Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, prefix + "SourceVersion (0)");
1324                         Assert.AreEqual (SqlDbType.Int, param.SqlDbType, prefix + "SqlDbType (0)");
1325                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (0)");
1326                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (0)");
1327                         Assert.IsNull (param.Value, prefix + "Value (0)");
1328                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (0)");
1329                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (0)");
1330                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (0)");
1331
1332                         param = cmd.Parameters [1];
1333                         Assert.AreEqual (DbType.AnsiString, param.DbType, prefix + "DbType (1)");
1334                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (1)");
1335                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (1)");
1336                         Assert.AreEqual (0, param.Offset, prefix + "Offset (1)");
1337                         if (useColumnsForParameterNames)
1338                                 Assert.AreEqual ("@fname", param.ParameterName, prefix + "ParameterName (1)");
1339                         else
1340                                 Assert.AreEqual ("@p2", param.ParameterName, prefix + "ParameterName (1)");
1341                         Assert.AreEqual (0, param.Precision, prefix + "Precision (1)");
1342                         Assert.AreEqual (0, param.Scale, prefix + "Scale (1)");
1343                         //Assert.AreEqual (0, param.Size, prefix + "Size (1)");
1344                         Assert.AreEqual ("fname", param.SourceColumn, prefix + "SourceColumn (1)");
1345                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (1)");
1346                         Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, prefix + "SourceVersion (1)");
1347                         Assert.AreEqual (SqlDbType.VarChar, param.SqlDbType, prefix + "SqlDbType (1)");
1348                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (1)");
1349                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (1)");
1350                         Assert.IsNull (param.Value, prefix + "Value (1)");
1351                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (1)");
1352                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (1)");
1353                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (1)");
1354
1355                         param = cmd.Parameters [2];
1356                         Assert.AreEqual (DbType.AnsiString, param.DbType, prefix + "DbType (2)");
1357                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (2)");
1358                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (2)");
1359                         Assert.AreEqual (0, param.Offset, prefix + "Offset (2)");
1360                         if (useColumnsForParameterNames)
1361                                 Assert.AreEqual ("@lname", param.ParameterName, prefix + "ParameterName (2)");
1362                         else
1363                                 Assert.AreEqual ("@p3", param.ParameterName, prefix + "ParameterName (2)");
1364                         Assert.AreEqual (0, param.Precision, prefix + "Precision (2)");
1365                         Assert.AreEqual (0, param.Scale, prefix + "Scale (2)");
1366                         Assert.AreEqual (0, param.Size, prefix + "Size (2)");
1367                         Assert.AreEqual ("lname", param.SourceColumn, prefix + "SourceColumn (2)");
1368                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (2)");
1369                         Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, prefix + "SourceVersion (2)");
1370                         Assert.AreEqual (SqlDbType.VarChar, param.SqlDbType, prefix + "SqlDbType (2)");
1371                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (2)");
1372                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (2)");
1373                         Assert.IsNull (param.Value, prefix + "Value (2)");
1374                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (2)");
1375                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (2)");
1376                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (2)");
1377
1378                         param = cmd.Parameters [3];
1379                         Assert.AreEqual (DbType.Int32, param.DbType, prefix + "DbType (3)");
1380                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (3)");
1381                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (3)");
1382                         Assert.AreEqual (0, param.Offset, prefix + "Offset (3)");
1383                         if (useColumnsForParameterNames)
1384                                 Assert.AreEqual ("@Original_id", param.ParameterName, prefix + "ParameterName (3)");
1385                         else
1386                                 Assert.AreEqual ("@p4", param.ParameterName, prefix + "ParameterName (3)");
1387
1388                         if (ClientVersion > 7)
1389                                 Assert.AreEqual (0, param.Precision, prefix + "Precision (0)");
1390                         else
1391                                 Assert.AreEqual (10, param.Precision, prefix + "Precision (0)");
1392
1393                         Assert.AreEqual (0, param.Scale, prefix + "Scale (3)");
1394                         //Assert.AreEqual (0, param.Size, prefix + "Size (3)");
1395                         Assert.AreEqual ("id", param.SourceColumn, prefix + "SourceColumn (3)");
1396                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (3)");
1397                         Assert.AreEqual (DataRowVersion.Original, param.SourceVersion, prefix + "SourceVersion (3)");
1398                         Assert.AreEqual (SqlDbType.Int, param.SqlDbType, prefix + "SqlDbType (3)");
1399                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (3)");
1400                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (3)");
1401                         Assert.IsNull (param.Value, prefix + "Value (3)");
1402                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (3)");
1403                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (3)");
1404                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (3)");
1405
1406 #if ONLY_1_1
1407                         param = cmd.Parameters [4];
1408                         Assert.AreEqual (DbType.Int32, param.DbType, prefix + "DbType (4)");
1409                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (4)");
1410                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (4)");
1411                         Assert.AreEqual (0, param.Offset, prefix + "Offset (4)");
1412                         Assert.AreEqual ("@p5", param.ParameterName, prefix + "ParameterName (4)");
1413                         Assert.AreEqual (0, param.Precision, prefix + "Precision (4)");
1414                         Assert.AreEqual (0, param.Scale, prefix + "Scale (4)");
1415                         //Assert.AreEqual (0, param.Size, prefix + "Size (4)");
1416                         Assert.AreEqual (string.Empty, param.SourceColumn, prefix + "SourceColumn (4)");
1417                         Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, prefix + "SourceVersion (4)");
1418                         Assert.AreEqual (SqlDbType.Int, param.SqlDbType, prefix + "SqlDbType (4)");
1419                         Assert.AreEqual (1, param.Value, prefix + "Value (4)");
1420 #endif
1421
1422                         param = cmd.Parameters [4];
1423                         Assert.AreEqual (DbType.AnsiString, param.DbType, prefix + "DbType (5)");
1424                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (5)");
1425                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (5)");
1426                         Assert.AreEqual (0, param.Offset, prefix + "Offset (5)");
1427                         if (useColumnsForParameterNames)
1428                                 Assert.AreEqual ("@Original_fname", param.ParameterName, prefix + "ParameterName (5)");
1429                         else
1430                                 Assert.AreEqual ("@p5", param.ParameterName, prefix + "ParameterName (5)");
1431                         Assert.AreEqual (0, param.Precision, prefix + "Precision (5)");
1432                         Assert.AreEqual (0, param.Scale, prefix + "Scale (5)");
1433                         Assert.AreEqual (0, param.Size, prefix + "Size (5)");
1434                         Assert.AreEqual ("fname", param.SourceColumn, prefix + "SourceColumn (5)");
1435                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (5)");
1436                         Assert.AreEqual (DataRowVersion.Original, param.SourceVersion, prefix + "SourceVersion (5)");
1437                         Assert.AreEqual (SqlDbType.VarChar, param.SqlDbType, prefix + "SqlDbType (5)");
1438                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (5)");
1439                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (5)");
1440                         Assert.IsNull (param.Value, prefix + "Value (5)");
1441                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (5)");
1442                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (5)");
1443                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (5)");
1444
1445                         param = cmd.Parameters [5];
1446                         Assert.AreEqual (DbType.Int32, param.DbType, prefix + "DbType (6)");
1447                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (6)");
1448                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (6)");
1449                         Assert.AreEqual (0, param.Offset, prefix + "Offset (6)");
1450                         if (useColumnsForParameterNames)
1451                                 Assert.AreEqual ("@IsNull_lname", param.ParameterName, prefix + "ParameterName (6)");
1452                         else
1453                                 Assert.AreEqual ("@p6", param.ParameterName, prefix + "ParameterName (6)");
1454                         Assert.AreEqual (0, param.Precision, prefix + "Precision (6)");
1455                         Assert.AreEqual (0, param.Scale, prefix + "Scale (6)");
1456                         //Assert.AreEqual (0, param.Size, prefix + "Size (6)");
1457                         Assert.AreEqual ("lname", param.SourceColumn, prefix + "SourceColumn (6)");
1458                         Assert.IsTrue (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (6)");
1459                         Assert.AreEqual (DataRowVersion.Original, param.SourceVersion, prefix + "SourceVersion (6)");
1460                         Assert.AreEqual (SqlDbType.Int, param.SqlDbType, prefix + "SqlDbType (6)");
1461                         Assert.AreEqual (new SqlInt32 (1), param.SqlValue, prefix + "SqlValue (6)");
1462                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (6)");
1463                         Assert.AreEqual (1, param.Value, prefix + "Value (6)");
1464                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (6)");
1465                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (6)");
1466                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (6)");
1467
1468                         param = cmd.Parameters [6];
1469                         Assert.AreEqual (DbType.AnsiString, param.DbType, prefix + "DbType (7)");
1470                         Assert.AreEqual (ParameterDirection.Input, param.Direction, prefix + "Direction (7)");
1471                         Assert.IsFalse (param.IsNullable, prefix + "IsNullable (7)");
1472                         Assert.AreEqual (0, param.Offset, prefix + "Offset (7)");
1473                         if (useColumnsForParameterNames)
1474                                 Assert.AreEqual ("@Original_lname", param.ParameterName, prefix + "ParameterName (7)");
1475                         else
1476                                 Assert.AreEqual ("@p7", param.ParameterName, prefix + "ParameterName (7)");
1477                         Assert.AreEqual (0, param.Precision, prefix + "Precision (7)");
1478                         Assert.AreEqual (0, param.Scale, prefix + "Scale (7)");
1479                         Assert.AreEqual (0, param.Size, prefix + "Size (7)");
1480                         Assert.AreEqual ("lname", param.SourceColumn, prefix + "SourceColumn (7)");
1481                         Assert.IsFalse (param.SourceColumnNullMapping, prefix + "SourceColumnNullMapping (7)");
1482                         Assert.AreEqual (DataRowVersion.Original, param.SourceVersion, prefix + "SourceVersion (7)");
1483                         Assert.AreEqual (SqlDbType.VarChar, param.SqlDbType, prefix + "SqlDbType (7)");
1484                         Assert.IsNull (param.SqlValue, prefix + "SqlValue (7)");
1485                         //Assert.AreEqual (string.Empty, param.UdtTypeName, prefix + "UdtTypeName (7)");
1486                         Assert.IsNull (param.Value, prefix + "Value (7)");
1487                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionDatabase, prefix + "XmlSchemaCollectionDatabase (7)");
1488                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionName, prefix + "XmlSchemaCollectionName (7)");
1489                         Assert.AreEqual (string.Empty, param.XmlSchemaCollectionOwningSchema, prefix + "XmlSchemaCollectionOwningSchema (7)");
1490                 }
1491
1492                 static int ClientVersion {
1493                         get {
1494                                 return (SqlCommandBuilderTest.Engine.ClientVersion);
1495                         }
1496                 }
1497         }
1498 }