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

   1 // SqlCommandBuilderTest.cs - NUnit Test Cases for testing the
   2 // SqlCommandBuilder class
   3 //
   4 // Authors:
   5 //      Sureshkumar T (tsureshkumar@novell.com)
   6 //
   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.SqlClient;
  35 using Mono.Data;
  36
  37 using NUnit.Framework;
  38
  39 namespace MonoTests.System.Data
  40 {
  41         [TestFixture]
  42         [Category ("sqlserver")]
  43         public class SqlCommandBuilderTest
  44         {
  45                 [Test]
  46                 public void GetInsertCommandTest ()
  47                 {
  48                         IDbConnection conn = ConnectionManager.Singleton.Connection;
  49                         try {
  50                                 ConnectionManager.Singleton.OpenConnection ();
  51                                 string selectQuery = "select id, fname from employee where id = 1";
  52                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
  53                                 DataSet ds = new DataSet ();
  54                                 da.Fill (ds, "IntTest");
  55                                 Assert.AreEqual (1, ds.Tables.Count, "#1 atleast one table should be filled");
  56
  57                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
  58                                 SqlCommand cmd = cb.GetInsertCommand ();
  59                                 Assert.AreEqual ("INSERT INTO employee (id, fname) VALUES (@p1, @p2)",
  60                                                 cmd.CommandText, "#2");
  61                         } finally {
  62                                 ConnectionManager.Singleton.CloseConnection ();
  63                         }
  64                 }
  65
  66                 [Test]
  67                 public void GetInsertCommandTestWithExpression ()
  68                 {
  69                         IDbConnection conn = ConnectionManager.Singleton.Connection;
  70                         try {
  71                                 ConnectionManager.Singleton.OpenConnection ();
  72                                 string selectQuery = "select id, fname, id+1 as next_id from employee where id = 1";
  73                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
  74                                 DataSet ds = new DataSet ();
  75                                 da.Fill (ds, "IntTest");
  76                                 Assert.AreEqual (1, ds.Tables.Count, "#1 atleast one table should be filled");
  77
  78                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
  79                                 SqlCommand cmd = cb.GetInsertCommand ();
  80                                 Assert.AreEqual ("INSERT INTO employee (id, fname) VALUES (@p1, @p2)",
  81                                                 cmd.CommandText, "#2");
  82                         } finally {
  83                                 ConnectionManager.Singleton.CloseConnection ();
  84                         }
  85                 }
  86
  87                 [Test]
  88                 public void GetUpdateCommandTest ()
  89                 {
  90                         IDbConnection conn = ConnectionManager.Singleton.Connection;
  91                         try {
  92                                 string selectQuery = "select id, fname, lname, id+1 as next_id from employee where id = 1";
  93                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
  94                                 DataSet ds = new DataSet ();
  95                                 da.Fill (ds, "IntTest");
  96                                 Assert.AreEqual (1, ds.Tables.Count, "#1 atleast one table should be filled");
  97
  98                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
  99                                 SqlCommand cmd = cb.GetUpdateCommand ();
 100                                 Assert.AreEqual ("UPDATE employee SET id = @p1, fname = @p2, lname = @p3 WHERE ((id = @p4)" +
 101                                                 " AND (fname = @p5) AND ((@p6 = 1 AND lname IS NULL) OR (lname = @p7)))",
 102                                                 cmd.CommandText, "#2");
 103                                 Assert.AreEqual (7, cmd.Parameters.Count, "#3");
 104                         } finally {
 105                                 ConnectionManager.Singleton.CloseConnection ();
 106                         }
 107                 }
 108
 109                 [Test]
 110                 public void GetUpdateCommandTest_CheckNonUpdatableColumns ()
 111                 {
 112                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 113                         try {
 114                                 ConnectionManager.Singleton.OpenConnection ();
 115                                 IDbCommand cmd = conn.CreateCommand ();
 116                                 cmd.CommandText = "create table #tmp_table (id int primary key , counter int identity(1,1), value varchar(10))";
 117                                 cmd.ExecuteNonQuery ();
 118
 119                                 string selectQuery = "select id, counter, value, id+1 as next_id from #tmp_table";
 120                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
 121                                 DataSet ds = new DataSet ();
 122                                 da.Fill (ds);
 123                                 Assert.AreEqual (1, ds.Tables.Count, "#1");
 124                                 Assert.AreEqual (4, ds.Tables [0].Columns.Count, "#2");
 125
 126                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 127                                 SqlCommand updateCmd = cb.GetUpdateCommand ();
 128                                 Assert.AreEqual ("UPDATE #tmp_table SET id = @p1, value = @p2 WHERE ((id = @p3) AND (" +
 129                                                         "counter = @p4) AND ((@p5 = 1 AND value IS NULL) OR (value = @p6)))",
 130                                                 updateCmd.CommandText, "#3");
 131                                 Assert.AreEqual (6, updateCmd.Parameters.Count, "#4");
 132
 133                                 SqlCommand delCmd = cb.GetDeleteCommand ();
 134                                 Assert.AreEqual ("DELETE FROM #tmp_table WHERE ((id = @p1) AND (counter = @p2) AND " +
 135                                                 "((@p3 = 1 AND value IS NULL) OR (value = @p4)))", delCmd.CommandText, "#5");
 136                                 Assert.AreEqual (4, delCmd.Parameters.Count, "#6");
 137                         } finally {
 138                                 ConnectionManager.Singleton.CloseConnection ();
 139                         }
 140                 }
 141
 142                 [Test]
 143                 public void GetUpdateDeleteCommand_CheckParameters ()
 144                 {
 145                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 146                         try {
 147                                 ConnectionManager.Singleton.OpenConnection ();
 148                                 SqlDataAdapter adapter = new SqlDataAdapter ("select id, type_varchar from string_family",
 149                                                                 (SqlConnection)conn);
 150                                 SqlCommandBuilder cb = new SqlCommandBuilder (adapter);
 151
 152                                 SqlCommand updateCommand = cb.GetUpdateCommand ();
 153                                 Assert.AreEqual (5, updateCommand.Parameters.Count, "#1");
 154                                 Assert.AreEqual (SqlDbType.Int, updateCommand.Parameters ["@p4"].SqlDbType, "#2");
 155                                 Assert.AreEqual (1, updateCommand.Parameters ["@p4"].Value, "#3");
 156
 157                                 SqlCommand delCommand = cb.GetDeleteCommand ();
 158                                 Assert.AreEqual (3, delCommand.Parameters.Count, "#4");
 159                                 Assert.AreEqual (SqlDbType.Int, delCommand.Parameters ["@p2"].SqlDbType, "#5");
 160                                 Assert.AreEqual (1, delCommand.Parameters ["@p2"].Value, "#6");
 161                         } finally {
 162                                 ConnectionManager.Singleton.CloseConnection ();
 163                         }
 164                 }
 165
 166                 [Test]
 167                 [ExpectedException (typeof (DBConcurrencyException))]
 168                 public void GetUpdateCommandDBConcurrencyExceptionTest ()
 169                 {
 170                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 171                         try {
 172                                 ConnectionManager.Singleton.OpenConnection ();
 173                                 string selectQuery = "select id, fname from employee where id = 1";
 174                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
 175                                 DataSet ds = new DataSet ();
 176                                 da.Fill (ds, "IntTest");
 177                                 Assert.AreEqual (1, ds.Tables.Count, "#1 atleast one table should be filled");
 178
 179                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 180                                 DataRow [] rows = ds.Tables [0].Select ("id=1");
 181                                 rows [0] [0] = 6660; // non existent
 182                                 ds.Tables [0].AcceptChanges (); // moves 6660 to original value
 183                                 rows [0] [0] = 1; // moves 6660 as search key into db table
 184                                 da.Update (rows);
 185                         } finally {
 186                                 ConnectionManager.Singleton.CloseConnection ();
 187                         }
 188                 }
 189
 190                 [Test]
 191                 [ExpectedException (typeof (DBConcurrencyException))]
 192                 public void GetDeleteCommandDBConcurrencyExceptionTest ()
 193                 {
 194                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 195                         try {
 196                                 ConnectionManager.Singleton.OpenConnection ();
 197                                 string selectQuery = "select id, fname from employee where id = 1";
 198                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
 199                                 DataSet ds = new DataSet ();
 200                                 da.Fill (ds, "IntTest");
 201                                 Assert.AreEqual (1, ds.Tables.Count, "#1 atleast one table should be filled");
 202
 203                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 204                                 DataRow [] rows = ds.Tables [0].Select ("id=1");
 205                                 rows [0] [0] = 6660; // non existent
 206                                 ds.Tables [0].AcceptChanges (); // moves 6660 to original value
 207                                 rows [0].Delete ();  // moves 6660 as search key into db table
 208                                 da.Update (rows);
 209                         } finally {
 210                                 ConnectionManager.Singleton.CloseConnection ();
 211                         }
 212                 }
 213
 214                 [Test]
 215                 public void GetDeleteCommandTest ()
 216                 {
 217                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 218                         try {
 219                                 ConnectionManager.Singleton.OpenConnection ();
 220                                 string selectQuery = "select id, fname, lname, id+1 as next_id from employee where id = 1";
 221                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
 222                                 DataSet ds = new DataSet ();
 223                                 da.Fill (ds, "IntTest");
 224                                 Assert.AreEqual (1, ds.Tables.Count, "#1 atleast one table should be filled");
 225
 226                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 227                                 SqlCommand cmd = cb.GetDeleteCommand ();
 228                                 Assert.AreEqual ("DELETE FROM employee WHERE ((id = @p1) AND (fname = @p2) AND ((@p3 = 1 AND lname IS NULL) OR (lname = @p4)))",
 229                                                 cmd.CommandText, "#2");
 230                         } finally {
 231                                 ConnectionManager.Singleton.CloseConnection ();
 232                         }
 233                 }
 234
 235                 [Test]
 236                 public void DefaultPropertiesTest ()
 237                 {
 238                         SqlCommandBuilder cb = new SqlCommandBuilder ();
 239 #if NET_1_1 || NET_1_0 || ONLY_1_1
 240                         Assert.AreEqual (ConflictOption.CompareAllSearchableValues, cb.ConflictDetection);
 241 #endif // NET_1_1 || NET_1_0 || ONLY_1_1
 242                         Assert.AreEqual ("", cb.QuotePrefix, "#5");
 243                         Assert.AreEqual ("", cb.QuoteSuffix, "#6");
 244 #if NET_2_0
 245                         Assert.AreEqual (".", cb.CatalogSeparator, "#2");
 246                         Assert.AreEqual ("", cb.DecimalSeparator, "#3");
 247                         Assert.AreEqual (".", cb.SchemaSeparator, "#4");
 248                         Assert.AreEqual (CatalogLocation.Start, cb.CatalogLocation, "#1");
 249                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 250                         try {
 251                                 conn.Open ();
 252                                 cb = new SqlCommandBuilder ();
 253                                 Assert.AreEqual ("\"monotest\"", cb.QuoteIdentifier ("monotest", (SqlConnection) conn), "#7");
 254                                 Assert.AreEqual ("monotest", cb.UnquoteIdentifier ("\"monotest\"", (SqlConnection) conn), "#8");
 255                                 conn.Close ();
 256                         } finally {
 257                                 ConnectionManager.Singleton.CloseConnection ();
 258                         }
 259                         // FIXME: test SetAllValues
 260 #endif // NET_2_0
 261                 }
 262
 263                 // FIXME:  Add tests for examining RowError
 264                 // FIXME: Add test for ContinueUpdateOnError property
 265
 266                 [Test]
 267                 public void CheckParameters_BuiltCommand ()
 268                 {
 269                         using (IDbConnection conn = ConnectionManager.Singleton.Connection) {
 270                                 SqlDataAdapter adapter = new SqlDataAdapter ("select id,type_varchar from string_family", (SqlConnection)conn);
 271                                 SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
 272                                 DataSet ds = new DataSet ();
 273                                 adapter.Fill(ds);
 274
 275                                 Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#1");
 276
 277                                 DataRow row_rsInput = ds.Tables[0].NewRow();
 278                                 row_rsInput["id"] = 100;
 279                                 row_rsInput["type_varchar"] = "ttt";
 280                                 ds.Tables[0].Rows.Add(row_rsInput);
 281
 282                                 Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#2");
 283
 284                                 row_rsInput = ds.Tables[0].NewRow();
 285                                 row_rsInput["id"] = 101;
 286                                 row_rsInput["type_varchar"] = "ttt";
 287                                 ds.Tables[0].Rows.Add(row_rsInput);
 288
 289                                 Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#3");
 290                         }
 291                 }
 292         }
 293 }