mcs/class/System.Data/Test/ProviderTests/System.Data.Odbc/OdbcCommandBuilderTest.cs

   1 // OdbcCommandBuilderTest.cs - NUnit Test Cases for testing the
   2 // OdbcCommandBuilder Test.
   3 //
   4 // Authors:
   5 //      Sureshkumar T (tsureshkumar@novell.com)
   6 //
   7 // Copyright (c) 2004 Novell Inc., and the individuals listed on the
   8 // ChangeLog entries.
   9 //
  10 //
  11 // Permission is hereby granted, free of charge, to any person
  12 // obtaining a copy of this software and associated documentation
  13 // files (the "Software"), to deal in the Software without
  14 // restriction, including without limitation the rights to use, copy,
  15 // modify, merge, publish, distribute, sublicense, and/or sell copies
  16 // of the Software, and to permit persons to whom the Software is
  17 // furnished to do so, subject to the following conditions:
  18 //
  19 // The above copyright notice and this permission notice shall be
  20 // included in all copies or substantial portions of the Software.
  21 //
  22 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  23 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  24 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  25 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS
  26 // BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN
  27 // ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
  28 // CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
  29 // SOFTWARE.
  30
  31 using System;
  32 using System.Data;
  33 using System.Data.Common;
  34 using System.Data.Odbc;
  35
  36 using NUnit.Framework;
  37
  38 namespace MonoTests.System.Data.Connected.Odbc
  39 {
  40         [TestFixture]
  41         [Category ("odbc")]
  42         public class OdbcCommandBuilderTest
  43         {
  44                 [Test]
  45                 public void GetInsertCommandTest ()
  46                 {
  47                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
  48                         OdbcCommand cmd = null;
  49
  50                         try {
  51                                 string selectQuery = "select id, lname from employee where id = 3";
  52                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
  53                                 DataSet ds = new DataSet ();
  54                                 da.Fill (ds, "IntTest");
  55                                 Assert.AreEqual (1, ds.Tables.Count);
  56
  57                                 OdbcCommandBuilder cb;
  58
  59                                 cb = new OdbcCommandBuilder (da);
  60                                 cmd = cb.GetInsertCommand ();
  61                                 Assert.AreEqual ("INSERT INTO employee (id, lname) VALUES (?, ?)",
  62                                                 cmd.CommandText, "#A1");
  63                                 Assert.AreSame (conn, cmd.Connection, "#A2");
  64                                 AssertInsertParameters (cmd, "#A3:");
  65
  66                                 cb = new OdbcCommandBuilder (da);
  67                                 cb.QuotePrefix = "\"";
  68
  69                                 cmd = cb.GetInsertCommand ();
  70                                 Assert.AreEqual ("INSERT INTO \"employee (\"id, \"lname) VALUES (?, ?)",
  71                                                 cmd.CommandText, "#B1");
  72                                 Assert.AreSame (conn, cmd.Connection, "#B2");
  73                                 AssertInsertParameters (cmd, "#B3:");
  74
  75                                 cb = new OdbcCommandBuilder (da);
  76                                 cb.QuoteSuffix = "´";
  77
  78                                 cmd = cb.GetInsertCommand ();
  79                                 Assert.AreEqual ("INSERT INTO employee´ (id´, lname´) VALUES (?, ?)",
  80                                                 cmd.CommandText, "#C1");
  81                                 Assert.AreSame (conn, cmd.Connection, "#C2");
  82                                 AssertInsertParameters (cmd, "#C3:");
  83
  84                                 cb = new OdbcCommandBuilder (da);
  85                                 cb.QuotePrefix = "\"";
  86                                 cb.QuoteSuffix = "´";
  87
  88                                 cmd = cb.GetInsertCommand ();
  89                                 Assert.AreEqual ("INSERT INTO \"employee´ (\"id´, \"lname´) VALUES (?, ?)",
  90                                                 cmd.CommandText, "#D1");
  91                                 Assert.AreSame (conn, cmd.Connection, "#D2");
  92                                 AssertInsertParameters (cmd, "#D3:");
  93                         } finally {
  94                                 if (cmd != null)
  95                                         cmd.Dispose ();
  96                                 ConnectionManager.Instance.Odbc.CloseConnection ();
  97                         }
  98                 }
  99
 100                 [Test]
 101                 public void GetInsertCommandTestWithExpression ()
 102                 {
 103                         if (ConnectionManager.Instance.Odbc.EngineConfig.Type == EngineType.MySQL)
 104                                 Assert.Ignore ("Schema info from MySQL is incomplete");
 105
 106                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 107                         OdbcCommand cmd = null;
 108
 109                         try {
 110                                 string selectQuery = "select id, lname, id+1 as next_id from employee where id = 3";
 111                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 112                                 DataSet ds = new DataSet ();
 113                                 da.Fill (ds, "IntTest");
 114                                 Assert.AreEqual (1, ds.Tables.Count);
 115
 116                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 117                                 cmd = cb.GetInsertCommand ();
 118                                 Assert.AreEqual ("INSERT INTO employee (id, lname) VALUES (?, ?)",
 119                                                 cmd.CommandText, "#1");
 120                                 Assert.AreSame (conn, cmd.Connection, "#2");
 121                                 AssertInsertParameters (cmd, "#3:");
 122                         } finally {
 123                                 if (cmd != null)
 124                                         cmd.Dispose ();
 125                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 126                         }
 127                 }
 128
 129                 [Test]
 130                 public void GetUpdateCommandTest ()
 131                 {
 132                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 133                         OdbcCommand cmd = null;
 134
 135                         try {
 136                                 string selectQuery = "select id, lname, id+1 as next_id from employee where id = 3";
 137                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 138                                 DataSet ds = new DataSet ();
 139                                 da.Fill (ds, "IntTest");
 140                                 Assert.AreEqual (1, ds.Tables.Count);
 141
 142                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 143                                 cmd = cb.GetUpdateCommand ();
 144                                 Assert.AreEqual ("UPDATE employee SET id = ?, lname = ? WHERE ((id = ?) AND ((? = 1 AND lname IS NULL) OR (lname = ?)))",
 145                                                 cmd.CommandText, "#A1");
 146                                 Assert.AreSame (conn, cmd.Connection, "#A2");
 147                                 AssertUpdateParameters (cmd, "#A3:");
 148
 149                                 cb = new OdbcCommandBuilder (da);
 150                                 cb.QuotePrefix = "\"";
 151
 152                                 cmd = cb.GetUpdateCommand ();
 153                                 Assert.AreEqual ("UPDATE \"employee SET \"id = ?, \"lname = ? WHERE ((\"id = ?) AND ((? = 1 AND \"lname IS NULL) OR (\"lname = ?)))",
 154                                                 cmd.CommandText, "#B1");
 155                                 Assert.AreSame (conn, cmd.Connection, "#B2");
 156                                 AssertUpdateParameters (cmd, "#B3:");
 157
 158                                 cb = new OdbcCommandBuilder (da);
 159                                 cb.QuoteSuffix = "´";
 160
 161                                 cmd = cb.GetUpdateCommand ();
 162                                 Assert.AreEqual ("UPDATE employee´ SET id´ = ?, lname´ = ? WHERE ((id´ = ?) AND ((? = 1 AND lname´ IS NULL) OR (lname´ = ?)))",
 163                                                 cmd.CommandText, "#C1");
 164                                 Assert.AreSame (conn, cmd.Connection, "#C2");
 165                                 AssertUpdateParameters (cmd, "#C3:");
 166
 167                                 cb = new OdbcCommandBuilder (da);
 168                                 cb.QuotePrefix = "\"";
 169                                 cb.QuoteSuffix = "´";
 170
 171                                 cmd = cb.GetUpdateCommand ();
 172                                 Assert.AreEqual ("UPDATE \"employee´ SET \"id´ = ?, \"lname´ = ? WHERE ((\"id´ = ?) AND ((? = 1 AND \"lname´ IS NULL) OR (\"lname´ = ?)))",
 173                                                 cmd.CommandText, "#D1");
 174                                 Assert.AreSame (conn, cmd.Connection, "#D2");
 175                                 AssertUpdateParameters (cmd, "#D3:");
 176                         } finally {
 177                                 if (cmd != null)
 178                                         cmd.Dispose ();
 179                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 180                         }
 181                 }
 182
 183                 [Test]
 184                 [Ignore ("FIXME: Auto SQL generation during Update requires a valid SelectCommand")]
 185                 public void GetUpdateCommandDBConcurrencyExceptionTest ()
 186                 {
 187                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 188                         try {
 189                                 string selectQuery = "select id, lname from employee where id = 3";
 190                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 191                                 DataSet ds = new DataSet ();
 192                                 da.Fill (ds, "IntTest");
 193                                 Assert.AreEqual (1, ds.Tables.Count);
 194
 195                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 196                                 Assert.IsNotNull (cb);
 197
 198                                 DataRow [] rows = ds.Tables [0].Select ("id=1");
 199                                 rows [0] [0] = 6660; // non existent
 200                                 ds.Tables [0].AcceptChanges (); // moves 6660 to original value
 201                                 rows [0] [0] = 1; // moves 6660 as search key into db table
 202
 203                                 try {
 204                                         da.Update (rows);
 205                                         Assert.Fail ("#1");
 206                                 } catch (DBConcurrencyException ex) {
 207                                         // Concurrency violation: the UpdateCommand
 208                                         // affected 0 records
 209                                         Assert.AreEqual (typeof (DBConcurrencyException), ex.GetType (), "#2");
 210                                         Assert.IsNull (ex.InnerException, "#3");
 211                                         Assert.IsNotNull (ex.Message, "#4");
 212                                         Assert.AreSame (rows [0], ex.Row, "#5");
 213                                         Assert.AreEqual (1, ex.RowCount, "#6");
 214                                 }
 215                         } finally {
 216                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 217                         }
 218                 }
 219
 220                 [Test]
 221                 [Category("NotWorking")]
 222                 public void GetInsertCommandTest_option_true ()
 223                 {
 224                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 225                         try {
 226                                 string selectQuery = "select id, lname from employee where id = 3";
 227                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 228                                 DataSet ds = new DataSet ();
 229                                 da.Fill (ds, "IntTest");
 230                                 Assert.AreEqual (1, ds.Tables.Count);
 231
 232                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 233                                 OdbcCommand cmd = cb.GetInsertCommand (true);
 234                                 Assert.AreEqual ("INSERT INTO employee (id, lname) VALUES (?, ?)",
 235                                                 cmd.CommandText, "#1");
 236                                 Assert.AreSame (conn, cmd.Connection, "#2");
 237                                 AssertInsertParameters (cmd, "#3:");
 238                         } finally {
 239                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 240                         }
 241                 }
 242
 243                 [Test]
 244                 public void GetInsertCommandTest_option_false ()
 245                 {
 246                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 247                         try {
 248                                 string selectQuery = "select id, lname from employee where id = 3";
 249                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 250                                 DataSet ds = new DataSet ();
 251                                 da.Fill (ds, "IntTest");
 252                                 Assert.AreEqual (1, ds.Tables.Count);
 253
 254                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 255                                 OdbcCommand cmd = cb.GetInsertCommand (false);
 256                                 Assert.AreEqual ("INSERT INTO employee (id, lname) VALUES (?, ?)",
 257                                                 cmd.CommandText, "#1");
 258                                 Assert.AreSame (conn, cmd.Connection, "#2");
 259                                 AssertInsertParameters (cmd, "#3:");
 260                         } finally {
 261                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 262                         }
 263                 }
 264
 265                 [Test]
 266                 [Category("NotWorking")]
 267                 public void GetUpdateCommandTest_option_true ()
 268                 {
 269                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 270                         try {
 271                                 string selectQuery = "select id, lname, id+1 as next_id from employee where id = 3";
 272                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 273                                 DataSet ds = new DataSet ();
 274                                 da.Fill (ds, "IntTest");
 275                                 Assert.AreEqual (1, ds.Tables.Count);
 276
 277                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 278                                 OdbcCommand cmd = cb.GetUpdateCommand (true);
 279                                 Assert.AreEqual ("UPDATE employee SET id = ?, lname = ? WHERE ((id = ?) AND ((? = 1 AND lname IS NULL) OR (lname = ?)))",
 280                                                 cmd.CommandText, "#1");
 281                                 Assert.AreSame (conn, cmd.Connection, "#2");
 282                                 AssertUpdateParameters (cmd, "#3:");
 283                         } finally {
 284                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 285                         }
 286                 }
 287
 288                 [Test]
 289                 public void GetUpdateCommandTest_option_false ()
 290                 {
 291                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 292                         try {
 293                                 string selectQuery = "select id, lname, id+1 as next_id from employee where id = 3";
 294                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 295                                 DataSet ds = new DataSet ();
 296                                 da.Fill (ds, "IntTest");
 297                                 Assert.AreEqual (1, ds.Tables.Count);
 298
 299                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 300                                 OdbcCommand cmd = cb.GetUpdateCommand (false);
 301                                 Assert.AreEqual ("UPDATE employee SET id = ?, lname = ? WHERE ((id = ?) AND ((? = 1 AND lname IS NULL) OR (lname = ?)))",
 302                                                 cmd.CommandText, "#1");
 303                                 Assert.AreSame (conn, cmd.Connection, "#2");
 304                                 AssertUpdateParameters (cmd, "#3:");
 305                         } finally {
 306                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 307                         }
 308                 }
 309
 310                 [Test]
 311                 [Category("NotWorking")]
 312                 public void GetDeleteCommandTest_option_true ()
 313                 {
 314                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 315                         try {
 316                                 string selectQuery = "select id, lname, id+1 as next_id from employee where id = 3";
 317                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 318                                 DataSet ds = new DataSet ();
 319                                 da.Fill (ds, "IntTest");
 320                                 Assert.AreEqual (1, ds.Tables.Count);
 321
 322                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 323                                 OdbcCommand cmd = cb.GetDeleteCommand (true);
 324                                 Assert.AreEqual ("DELETE FROM employee WHERE ((id = ?) AND ((? = 1 AND lname IS NULL) OR (lname = ?)))",
 325                                                 cmd.CommandText, "#1");
 326                                 Assert.AreSame (conn, cmd.Connection, "#2");
 327                                 AssertDeleteParameters (cmd, "#3:");
 328                         } finally {
 329                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 330                         }
 331                 }
 332
 333                 [Test]
 334                 public void GetDeleteCommandTest_option_false ()
 335                 {
 336                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 337                         try {
 338                                 string selectQuery = "select id, lname, id+1 as next_id from employee where id = 3";
 339                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 340                                 DataSet ds = new DataSet ();
 341                                 da.Fill (ds, "IntTest");
 342                                 Assert.AreEqual (1, ds.Tables.Count);
 343
 344                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 345                                 OdbcCommand cmd = cb.GetDeleteCommand (false);
 346                                 Assert.AreEqual ("DELETE FROM employee WHERE ((id = ?) AND ((? = 1 AND lname IS NULL) OR (lname = ?)))",
 347                                                 cmd.CommandText, "#1");
 348                                 Assert.AreSame (conn, cmd.Connection, "#2");
 349                                 AssertDeleteParameters (cmd, "#3:");
 350                         } finally {
 351                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 352                         }
 353                 }
 354
 355                 [Test]
 356                 public void GetDeleteCommandTest ()
 357                 {
 358                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 359                         OdbcCommand cmd = null;
 360
 361                         try {
 362                                 string selectQuery = "select id, lname, id+1 as next_id from employee where id = 3";
 363                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 364                                 DataSet ds = new DataSet ();
 365                                 da.Fill (ds, "IntTest");
 366                                 Assert.AreEqual (1, ds.Tables.Count);
 367
 368                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 369                                 cmd = cb.GetDeleteCommand ();
 370
 371                                 Assert.AreEqual ("DELETE FROM employee WHERE ((id = ?) AND ((? = 1 AND lname IS NULL) OR (lname = ?)))",
 372                                                 cmd.CommandText, "#A1");
 373                                 Assert.AreSame (conn, cmd.Connection, "#A2");
 374                                 AssertDeleteParameters (cmd, "#A3:");
 375
 376                                 cb = new OdbcCommandBuilder (da);
 377                                 cb.QuotePrefix = "\"";
 378
 379                                 cmd = cb.GetDeleteCommand ();
 380                                 Assert.AreEqual ("DELETE FROM \"employee WHERE ((\"id = ?) AND ((? = 1 AND \"lname IS NULL) OR (\"lname = ?)))",
 381                                                 cmd.CommandText, "#B1");
 382                                 Assert.AreSame (conn, cmd.Connection, "#B2");
 383                                 AssertDeleteParameters (cmd, "#B3:");
 384
 385                                 cb = new OdbcCommandBuilder (da);
 386                                 cb.QuoteSuffix = "´";
 387
 388                                 cmd = cb.GetDeleteCommand ();
 389                                 Assert.AreEqual ("DELETE FROM employee´ WHERE ((id´ = ?) AND ((? = 1 AND lname´ IS NULL) OR (lname´ = ?)))",
 390                                                 cmd.CommandText, "#C1");
 391                                 Assert.AreSame (conn, cmd.Connection, "#C2");
 392                                 AssertDeleteParameters (cmd, "#C3:");
 393
 394                                 cb = new OdbcCommandBuilder (da);
 395                                 cb.QuotePrefix = "\"";
 396                                 cb.QuoteSuffix = "´";
 397
 398                                 cmd = cb.GetDeleteCommand ();
 399                                 Assert.AreEqual ("DELETE FROM \"employee´ WHERE ((\"id´ = ?) AND ((? = 1 AND \"lname´ IS NULL) OR (\"lname´ = ?)))",
 400                                                 cmd.CommandText, "#D1");
 401                                 Assert.AreSame (conn, cmd.Connection, "#D2");
 402                                 AssertDeleteParameters (cmd, "#D3:");
 403                         } finally {
 404                                 if (cmd != null)
 405                                         cmd.Dispose ();
 406                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 407                         }
 408                 }
 409
 410                 [Test]
 411                 [Ignore ("QuoteSuffix and QuotePrefix are now in DbCommandBuilder, while commands are in implementation classes. Result: we cannot perform this check until we refactor this.")]
 412                 public void QuotePrefix_DeleteCommand_Generated ()
 413                 {
 414                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 415
 416                         OdbcCommand cmd = null;
 417
 418                         try {
 419                                 string selectQuery = "select id, lname from employee where id = 3";
 420                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 421                                 DataSet ds = new DataSet ();
 422                                 da.Fill (ds, "IntTest");
 423
 424                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 425                                 cmd = cb.GetDeleteCommand ();
 426                                 Assert.AreEqual (string.Empty, cb.QuotePrefix, "#1");
 427                                 try {
 428                                         cb.QuotePrefix = "";
 429                                         Assert.Fail ("#2");
 430                                 } catch (InvalidOperationException ex) {
 431                                         // The QuotePrefix and QuoteSuffix properties
 432                                         // cannot be changed once an Insert, Update, or
 433                                         // Delete command has been generated
 434                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
 435                                         Assert.IsNull (ex.InnerException, "#4");
 436                                         Assert.IsNotNull (ex.Message, "#5");
 437                                 }
 438                                 Assert.AreEqual (string.Empty, cb.QuotePrefix, "#6");
 439                                 cb.RefreshSchema ();
 440                                 cb.QuotePrefix = "";
 441                         } finally {
 442                                 if (cmd != null)
 443                                         cmd.Dispose ();
 444                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 445                         }
 446                 }
 447
 448                 [Test]
 449                 [Ignore ("QuoteSuffix and QuotePrefix are now in DbCommandBuilder, while commands are in implementation classes. Result: we cannot perform this check until we refactor this.")]
 450                 public void QuotePrefix_InsertCommand_Generated ()
 451                 {
 452                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 453
 454                         OdbcCommand cmd = null;
 455
 456                         try {
 457                                 string selectQuery = "select id, lname from employee where id = 3";
 458                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 459                                 DataSet ds = new DataSet ();
 460                                 da.Fill (ds, "IntTest");
 461
 462                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 463                                 cmd = cb.GetInsertCommand ();
 464                                 Assert.AreEqual (string.Empty, cb.QuotePrefix, "#1");
 465                                 try {
 466                                         cb.QuotePrefix = "";
 467                                         Assert.Fail ("#2");
 468                                 } catch (InvalidOperationException ex) {
 469                                         // The QuotePrefix and QuoteSuffix properties
 470                                         // cannot be changed once an Insert, Update, or
 471                                         // Delete command has been generated
 472                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
 473                                         Assert.IsNull (ex.InnerException, "#4");
 474                                         Assert.IsNotNull (ex.Message, "#5");
 475                                 }
 476                                 Assert.AreEqual (string.Empty, cb.QuotePrefix, "#6");
 477                                 cb.RefreshSchema ();
 478                                 cb.QuotePrefix = "";
 479                         } finally {
 480                                 if (cmd != null)
 481                                         cmd.Dispose ();
 482                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 483                         }
 484                 }
 485
 486                 [Test]
 487                 [Ignore ("QuoteSuffix and QuotePrefix are now in DbCommandBuilder, while commands are in implementation classes. Result: we cannot perform this check until we refactor this.")]
 488                 public void QuotePrefix_UpdateCommand_Generated ()
 489                 {
 490                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 491
 492                         OdbcCommand cmd = null;
 493
 494                         try {
 495                                 string selectQuery = "select id, lname from employee where id = 3";
 496                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 497                                 DataSet ds = new DataSet ();
 498                                 da.Fill (ds, "IntTest");
 499
 500                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 501                                 cmd = cb.GetUpdateCommand ();
 502                                 Assert.AreEqual (string.Empty, cb.QuotePrefix, "#1");
 503                                 try {
 504                                         cb.QuotePrefix = "";
 505                                         Assert.Fail ("#2");
 506                                 } catch (InvalidOperationException ex) {
 507                                         // The QuotePrefix and QuoteSuffix properties
 508                                         // cannot be changed once an Insert, Update, or
 509                                         // Delete command has been generated
 510                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
 511                                         Assert.IsNull (ex.InnerException, "#4");
 512                                         Assert.IsNotNull (ex.Message, "#5");
 513                                 }
 514                                 Assert.AreEqual (string.Empty, cb.QuotePrefix, "#6");
 515                                 cb.RefreshSchema ();
 516                                 cb.QuotePrefix = "";
 517                         } finally {
 518                                 if (cmd != null)
 519                                         cmd.Dispose ();
 520                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 521                         }
 522                 }
 523
 524                 [Test]
 525                 [Ignore ("QuoteSuffix and QuotePrefix are now in DbCommandBuilder, while commands are in implementation classes. Result: we cannot perform this check until we refactor this.")]
 526                 public void QuoteSuffix_DeleteCommand_Generated ()
 527                 {
 528                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 529
 530                         OdbcCommand cmd = null;
 531
 532                         try {
 533                                 string selectQuery = "select id, lname from employee where id = 3";
 534                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 535                                 DataSet ds = new DataSet ();
 536                                 da.Fill (ds, "IntTest");
 537
 538                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 539                                 cmd = cb.GetDeleteCommand ();
 540                                 Assert.AreEqual (string.Empty, cb.QuoteSuffix, "#1");
 541                                 try {
 542                                         cb.QuoteSuffix = "";
 543                                         Assert.Fail ("#2");
 544                                 } catch (InvalidOperationException ex) {
 545                                         // The QuotePrefix and QuoteSuffix properties
 546                                         // cannot be changed once an Insert, Update, or
 547                                         // Delete command has been generated
 548                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
 549                                         Assert.IsNull (ex.InnerException, "#4");
 550                                         Assert.IsNotNull (ex.Message, "#5");
 551                                 }
 552                                 Assert.AreEqual (string.Empty, cb.QuoteSuffix, "#6");
 553                                 cb.RefreshSchema ();
 554                                 cb.QuoteSuffix = "";
 555                         } finally {
 556                                 if (cmd != null)
 557                                         cmd.Dispose ();
 558                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 559                         }
 560                 }
 561
 562                 [Test]
 563                 [Ignore ("QuoteSuffix and QuotePrefix are now in DbCommandBuilder, while commands are in implementation classes. Result: we cannot perform this check until we refactor this.")]
 564                 public void QuoteSuffix_InsertCommand_Generated ()
 565                 {
 566                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 567
 568                         OdbcCommand cmd = null;
 569
 570                         try {
 571                                 string selectQuery = "select id, lname from employee where id = 3";
 572                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 573                                 DataSet ds = new DataSet ();
 574                                 da.Fill (ds, "IntTest");
 575
 576                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 577                                 cmd = cb.GetInsertCommand ();
 578                                 Assert.AreEqual (string.Empty, cb.QuoteSuffix, "#1");
 579                                 try {
 580                                         cb.QuoteSuffix = "";
 581                                         Assert.Fail ("#2");
 582                                 } catch (InvalidOperationException ex) {
 583                                         // The QuotePrefix and QuoteSuffix properties
 584                                         // cannot be changed once an Insert, Update, or
 585                                         // Delete command has been generated
 586                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
 587                                         Assert.IsNull (ex.InnerException, "#4");
 588                                         Assert.IsNotNull (ex.Message, "#5");
 589                                 }
 590                                 Assert.AreEqual (string.Empty, cb.QuoteSuffix, "#6");
 591                                 cb.RefreshSchema ();
 592                                 cb.QuoteSuffix = "";
 593                         } finally {
 594                                 if (cmd != null)
 595                                         cmd.Dispose ();
 596                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 597                         }
 598                 }
 599
 600                 [Test]
 601                 [Ignore ("QuoteSuffix and QuotePrefix are now in DbCommandBuilder, while commands are in implementation classes. Result: we cannot perform this check until we refactor this.")]
 602                 public void QuoteSuffix_UpdateCommand_Generated ()
 603                 {
 604                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 605
 606                         OdbcCommand cmd = null;
 607
 608                         try {
 609                                 string selectQuery = "select id, lname from employee where id = 3";
 610                                 OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
 611                                 DataSet ds = new DataSet ();
 612                                 da.Fill (ds, "IntTest");
 613
 614                                 OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
 615                                 cmd = cb.GetUpdateCommand ();
 616                                 Assert.AreEqual (string.Empty, cb.QuoteSuffix, "#1");
 617                                 try {
 618                                         cb.QuoteSuffix = "";
 619                                         Assert.Fail ("#2");
 620                                 } catch (InvalidOperationException ex) {
 621                                         // The QuotePrefix and QuoteSuffix properties
 622                                         // cannot be changed once an Insert, Update, or
 623                                         // Delete command has been generated
 624                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
 625                                         Assert.IsNull (ex.InnerException, "#4");
 626                                         Assert.IsNotNull (ex.Message, "#5");
 627                                 }
 628                                 Assert.AreEqual (string.Empty, cb.QuotePrefix, "#6");
 629                                 cb.RefreshSchema ();
 630                                 cb.QuoteSuffix = "";
 631                         } finally {
 632                                 if (cmd != null)
 633                                         cmd.Dispose ();
 634                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 635                         }
 636                 }
 637
 638                 [Test] // QuoteIdentifier (String, OdbcConnection)
 639                 public void QuoteIdentifier2 ()
 640                 {
 641                         OdbcCommandBuilder cb;
 642                         OdbcConnection conn = ConnectionManager.Instance.Odbc.Connection;
 643
 644                         string quote_char = ConnectionManager.Instance.Odbc.EngineConfig.QuoteCharacter;
 645
 646                         try {
 647                                 cb = new OdbcCommandBuilder ();
 648                                 Assert.AreEqual (quote_char + "mono" + quote_char, cb.QuoteIdentifier ("mono", conn), "#A1");
 649                                 Assert.AreEqual (quote_char + "Z" + quote_char, cb.QuoteIdentifier ("Z", conn), "#A2");
 650                                 Assert.AreEqual (quote_char + "abc" + quote_char, cb.QuoteIdentifier ("abc", conn), "#A3");
 651                                 Assert.AreEqual (quote_char + quote_char, cb.QuoteIdentifier (string.Empty, conn), "#A4");
 652                                 Assert.AreEqual (quote_char + " " + quote_char, cb.QuoteIdentifier (" ", conn), "#A5");
 653                                 Assert.AreEqual (quote_char + "\r" + quote_char, cb.QuoteIdentifier ("\r", conn), "#A6");
 654                                 cb.QuoteSuffix = "def";
 655                                 Assert.AreEqual (quote_char + "mono" + quote_char, cb.QuoteIdentifier ("mono", conn), "#A7");
 656                                 Assert.AreEqual (quote_char + "Z" + quote_char, cb.QuoteIdentifier ("Z", conn), "#A8");
 657                                 Assert.AreEqual (quote_char + "abc" + quote_char, cb.QuoteIdentifier ("abc", conn), "#A9");
 658                                 Assert.AreEqual (quote_char + quote_char, cb.QuoteIdentifier (string.Empty, conn), "#A10");
 659                                 Assert.AreEqual (quote_char + " " + quote_char, cb.QuoteIdentifier (" ", conn), "#A11");
 660                                 Assert.AreEqual (quote_char + "\r" + quote_char, cb.QuoteIdentifier ("\r", conn), "#A12");
 661
 662                                 cb = new OdbcCommandBuilder ();
 663                                 cb.QuotePrefix = "abc";
 664                                 Assert.AreEqual ("abcmono", cb.QuoteIdentifier ("mono", conn), "#B1");
 665                                 Assert.AreEqual ("abcZ", cb.QuoteIdentifier ("Z", conn), "#B2");
 666                                 Assert.AreEqual ("abcabc", cb.QuoteIdentifier ("abc", conn), "#B3");
 667                                 Assert.AreEqual ("abc", cb.QuoteIdentifier (string.Empty, conn), "#B4");
 668                                 Assert.AreEqual ("abc ", cb.QuoteIdentifier (" ", conn), "#B5");
 669                                 Assert.AreEqual ("abc\r", cb.QuoteIdentifier ("\r", conn), "#B6");
 670                                 cb.QuoteSuffix = "def";
 671                                 Assert.AreEqual ("abcmonodef", cb.QuoteIdentifier ("mono", conn), "#B7");
 672                                 Assert.AreEqual ("abcZdef", cb.QuoteIdentifier ("Z", conn), "#B8");
 673                                 Assert.AreEqual ("abcabcdef", cb.QuoteIdentifier ("abc", conn), "#B9");
 674                                 Assert.AreEqual ("abcdef", cb.QuoteIdentifier (string.Empty, conn), "#B10");
 675                                 Assert.AreEqual ("abc def", cb.QuoteIdentifier (" ", conn), "#B11");
 676                                 Assert.AreEqual ("abc\rdef", cb.QuoteIdentifier ("\r", conn), "#B12");
 677
 678                                 cb.QuotePrefix = string.Empty;
 679
 680                                 cb = new OdbcCommandBuilder ();
 681                                 cb.QuotePrefix = "X";
 682                                 Assert.AreEqual ("Xmono", cb.QuoteIdentifier ("mono", conn), "#D1");
 683                                 Assert.AreEqual ("XZ", cb.QuoteIdentifier ("Z", conn), "#D2");
 684                                 Assert.AreEqual ("XX", cb.QuoteIdentifier ("X", conn), "#D3");
 685                                 Assert.AreEqual ("X", cb.QuoteIdentifier (string.Empty, conn), "#D4");
 686                                 Assert.AreEqual ("X ", cb.QuoteIdentifier (" ", conn), "#D5");
 687                                 Assert.AreEqual ("X\r", cb.QuoteIdentifier ("\r", conn), "#D6");
 688                                 cb.QuoteSuffix = " ";
 689                                 Assert.AreEqual ("Xmono ", cb.QuoteIdentifier ("mono", conn), "#D7");
 690                                 Assert.AreEqual ("XZ ", cb.QuoteIdentifier ("Z", conn), "#D8");
 691                                 Assert.AreEqual ("XX ", cb.QuoteIdentifier ("X", conn), "#D9");
 692                                 Assert.AreEqual ("X ", cb.QuoteIdentifier (string.Empty, conn), "#D10");
 693                                 Assert.AreEqual ("X   ", cb.QuoteIdentifier (" ", conn), "#D11");
 694                                 Assert.AreEqual ("X\r ", cb.QuoteIdentifier ("\r", conn), "#D12");
 695
 696                                 cb = new OdbcCommandBuilder ();
 697                                 cb.QuotePrefix = " ";
 698                                 Assert.AreEqual ("mono", cb.QuoteIdentifier ("mono", conn), "#E1");
 699                                 Assert.AreEqual ("Z", cb.QuoteIdentifier ("Z", conn), "#E2");
 700                                 Assert.AreEqual ("abc", cb.QuoteIdentifier ("abc", conn), "#E3");
 701                                 Assert.AreEqual (string.Empty, cb.QuoteIdentifier (string.Empty, conn), "#E4");
 702                                 Assert.AreEqual (" ", cb.QuoteIdentifier (" ", conn), "#E5");
 703                                 Assert.AreEqual ("\r", cb.QuoteIdentifier ("\r", conn), "#E6");
 704                                 cb.QuoteSuffix = "def";
 705                                 Assert.AreEqual ("mono", cb.QuoteIdentifier ("mono", conn), "#E7");
 706                                 Assert.AreEqual ("Z", cb.QuoteIdentifier ("Z", conn), "#E8");
 707                                 Assert.AreEqual ("abc", cb.QuoteIdentifier ("abc", conn), "#E9");
 708                                 Assert.AreEqual (string.Empty, cb.QuoteIdentifier (string.Empty, conn), "#E10");
 709                                 Assert.AreEqual (" ", cb.QuoteIdentifier (" ", conn), "#E11");
 710                                 Assert.AreEqual ("\r", cb.QuoteIdentifier ("\r", conn), "#E12");
 711                         } finally {
 712                                 ConnectionManager.Instance.Odbc.CloseConnection ();
 713                         }
 714                 }
 715
 716                 void AssertInsertParameters (OdbcCommand cmd, string prefix)
 717                 {
 718                         Assert.AreEqual (2, cmd.Parameters.Count, prefix + "Count");
 719                         Assert.AreEqual (DbType.Int32, cmd.Parameters [0].DbType, prefix + "DbType (0)");
 720                         Assert.AreEqual ("p1", cmd.Parameters [0].ParameterName, prefix + "ParameterName (0)");
 721                         Assert.AreEqual ("id", cmd.Parameters [0].SourceColumn, prefix + "SourceColumn (0)");
 722                         Assert.IsNull (cmd.Parameters [0].Value, prefix + "Value (0)");
 723                         Assert.AreEqual (DbType.String, cmd.Parameters [1].DbType, prefix + "DbType (1)");
 724                         Assert.AreEqual ("p2", cmd.Parameters [1].ParameterName, prefix + "ParameterName (1)");
 725                         Assert.AreEqual ("lname", cmd.Parameters [1].SourceColumn, prefix + "SourceColumn (1)");
 726                         Assert.IsNull (cmd.Parameters [1].Value, prefix + "Value (1)");
 727                 }
 728
 729                 void AssertUpdateParameters (OdbcCommand cmd, string prefix)
 730                 {
 731                         Assert.AreEqual (5, cmd.Parameters.Count, prefix + "Count");
 732                         Assert.AreEqual (DbType.Int32, cmd.Parameters [0].DbType, prefix + "DbType (0)");
 733                         Assert.AreEqual ("p1", cmd.Parameters [0].ParameterName, prefix + "ParameterName (0)");
 734                         Assert.AreEqual ("id", cmd.Parameters [0].SourceColumn, prefix + "SourceColumn (0)");
 735                         Assert.IsNull (cmd.Parameters [0].Value, prefix + "Value (0)");
 736                         Assert.AreEqual (DbType.String, cmd.Parameters [1].DbType, prefix + "DbType (1)");
 737                         Assert.AreEqual ("p2", cmd.Parameters [1].ParameterName, prefix + "ParameterName (1)");
 738                         Assert.AreEqual ("lname", cmd.Parameters [1].SourceColumn, prefix + "SourceColumn (1)");
 739                         Assert.IsNull (cmd.Parameters [1].Value, prefix + "Value (1)");
 740                         Assert.AreEqual (DbType.Int32, cmd.Parameters [2].DbType, prefix + "DbType (2)");
 741                         Assert.AreEqual ("p3", cmd.Parameters [2].ParameterName, prefix + "ParameterName (2)");
 742                         Assert.AreEqual ("id", cmd.Parameters [2].SourceColumn, prefix + "SourceColumn (2)");
 743                         Assert.IsNull (cmd.Parameters [2].Value, prefix + "Value (2)");
 744                         Assert.AreEqual (DbType.Int32, cmd.Parameters [3].DbType, prefix + "DbType (3)");
 745                         Assert.AreEqual ("p4", cmd.Parameters [3].ParameterName, prefix + "ParameterName (3)");
 746                         Assert.AreEqual ("lname", cmd.Parameters [3].SourceColumn, prefix + "SourceColumn (3)");
 747                         Assert.AreEqual (1, cmd.Parameters [3].Value, prefix + "Value (3)");
 748                         Assert.AreEqual (DbType.String, cmd.Parameters [4].DbType, prefix + "DbType (4)");
 749                         Assert.AreEqual ("p5", cmd.Parameters [4].ParameterName, prefix + "ParameterName (4)");
 750                         Assert.AreEqual ("lname", cmd.Parameters [4].SourceColumn, prefix + "SourceColumn (4)");
 751                         Assert.IsNull (cmd.Parameters [4].Value, prefix + "Value (4)");
 752                 }
 753
 754                 void AssertDeleteParameters (OdbcCommand cmd, string prefix)
 755                 {
 756                         Assert.AreEqual (3, cmd.Parameters.Count, prefix + "Count");
 757                         Assert.AreEqual (DbType.Int32, cmd.Parameters [0].DbType, prefix + "DbType (0)");
 758                         Assert.AreEqual ("p1", cmd.Parameters [0].ParameterName, prefix + "ParameterName (0)");
 759                         Assert.AreEqual ("id", cmd.Parameters [0].SourceColumn, prefix + "SourceColumn (0)");
 760                         Assert.IsNull (cmd.Parameters [0].Value, prefix + "Value (0)");
 761                         Assert.AreEqual (DbType.Int32, cmd.Parameters [1].DbType, prefix + "DbType (1)");
 762                         Assert.AreEqual ("p2", cmd.Parameters [1].ParameterName, prefix + "ParameterName (1)");
 763                         Assert.AreEqual ("lname", cmd.Parameters [1].SourceColumn, prefix + "SourceColumn (1)");
 764                         Assert.AreEqual (1, cmd.Parameters [1].Value, prefix + "Value (1)");
 765                         Assert.AreEqual (DbType.String, cmd.Parameters [2].DbType, prefix + "DbType (2)");
 766                         Assert.AreEqual ("p3", cmd.Parameters [2].ParameterName, prefix + "ParameterName (2)");
 767                         Assert.AreEqual ("lname", cmd.Parameters [2].SourceColumn, prefix + "SourceColumn (2)");
 768                         Assert.IsNull (cmd.Parameters [2].Value, prefix + "Value (2)");
 769                 }
 770
 771                 // FIXME: test SetAllValues
 772                 // FIXME:  Add tests for examining RowError
 773                 // FIXME: Add test for ContinueUpdateOnError property
 774         }
 775 }