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