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                         using (conn) {
  92                                 string selectQuery = "select id, fname, 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 WHERE ((id = @p3) AND ((@p4 = 1 AND age IS NULL) OR (fname = @p5)))",
 101                                                 cmd.CommandText, "#2");
 102                                 Assert.AreEqual (5, cmd.Parameters.Count, "#3");
 103                         }
 104                 }
 105
 106                 [Test]
 107                 [ExpectedException (typeof (DBConcurrencyException))]
 108                 public void GetUpdateCommandDBConcurrencyExceptionTest ()
 109                 {
 110                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 111                         try {
 112                                 ConnectionManager.Singleton.OpenConnection ();
 113                                 string selectQuery = "select id, fname from employee where id = 1";
 114                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
 115                                 DataSet ds = new DataSet ();
 116                                 da.Fill (ds, "IntTest");
 117                                 Assert.AreEqual (1, ds.Tables.Count, "#1 atleast one table should be filled");
 118
 119                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 120                                 DataRow [] rows = ds.Tables [0].Select ("id=1");
 121                                 rows [0] [0] = 6660; // non existent
 122                                 ds.Tables [0].AcceptChanges (); // moves 6660 to original value
 123                                 rows [0] [0] = 1; // moves 6660 as search key into db table
 124                                 da.Update (rows);
 125                         } finally {
 126                                 ConnectionManager.Singleton.CloseConnection ();
 127                         }
 128                 }
 129
 130                 [Test]
 131                 public void GetDeleteCommandTest ()
 132                 {
 133                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 134                         try {
 135                                 ConnectionManager.Singleton.OpenConnection ();
 136                                 string selectQuery = "select id, fname, id+1 as next_id from employee where id = 1";
 137                                 SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
 138                                 DataSet ds = new DataSet ();
 139                                 da.Fill (ds, "IntTest");
 140                                 Assert.AreEqual (1, ds.Tables.Count, "#1 atleast one table should be filled");
 141
 142                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
 143                                 SqlCommand cmd = cb.GetDeleteCommand ();
 144                                 Assert.AreEqual ("DELETE FROM employee WHERE ((id = @p1) AND ((@p2 = 1 AND fname IS NULL) OR (fname = @p3)))",
 145                                                 cmd.CommandText, "#2");
 146                         } finally {
 147                                 ConnectionManager.Singleton.CloseConnection ();
 148                         }
 149                 }
 150
 151                 [Test]
 152                 public void DefaultPropertiesTest ()
 153                 {
 154                         SqlCommandBuilder cb = new SqlCommandBuilder ();
 155 #if NET_1_1 || NET_1_0 || ONLY_1_1
 156                         Assert.AreEqual (ConflictOption.CompareAllSearchableValues, cb.ConflictDetection);
 157 #endif // NET_1_1 || NET_1_0 || ONLY_1_1
 158                         Assert.AreEqual ("", cb.QuotePrefix, "#5");
 159                         Assert.AreEqual ("", cb.QuoteSuffix, "#6");
 160 #if NET_2_0
 161                         Assert.AreEqual (".", cb.CatalogSeparator, "#2");
 162                         Assert.AreEqual ("", cb.DecimalSeparator, "#3");
 163                         Assert.AreEqual (".", cb.SchemaSeparator, "#4");
 164                         Assert.AreEqual (CatalogLocation.Start, cb.CatalogLocation, "#1");
 165                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 166                         try {
 167                                 conn.Open ();
 168                                 cb = new SqlCommandBuilder ();
 169                                 Assert.AreEqual ("\"monotest\"", cb.QuoteIdentifier ("monotest", (SqlConnection) conn), "#7");
 170                                 Assert.AreEqual ("monotest", cb.UnquoteIdentifier ("\"monotest\"", (SqlConnection) conn), "#8");
 171                                 conn.Close ();
 172                         } finally {
 173                                 ConnectionManager.Singleton.CloseConnection ();
 174                         }
 175                         // FIXME: test SetAllValues
 176 #endif // NET_2_0
 177                 }
 178
 179                 // FIXME:  Add tests for examining RowError
 180                 // FIXME: Add test for ContinueUpdateOnError property
 181
 182                 [Test]
 183                 public void CheckParameters_BuiltCommand ()
 184                 {
 185                         using (IDbConnection conn = ConnectionManager.Singleton.Connection) {
 186                                 SqlDataAdapter adapter = new SqlDataAdapter ("select id,type_varchar from string_family", (SqlConnection)conn);
 187                                 SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
 188                                 DataSet ds = new DataSet ();
 189                                 adapter.Fill(ds);
 190
 191                                 Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#1");
 192
 193                                 DataRow row_rsInput = ds.Tables[0].NewRow();
 194                                 row_rsInput["id"] = 100;
 195                                 row_rsInput["type_varchar"] = "ttt";
 196                                 ds.Tables[0].Rows.Add(row_rsInput);
 197
 198                                 Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#2");
 199
 200                                 row_rsInput = ds.Tables[0].NewRow();
 201                                 row_rsInput["id"] = 101;
 202                                 row_rsInput["type_varchar"] = "ttt";
 203                                 ds.Tables[0].Rows.Add(row_rsInput);
 204
 205                                 Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#3");
 206                         }
 207                 }
 208         }
 209 }