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

   1 //
   2 // SqlDataAdapterTest.cs - NUnit Test Cases for testing the
   3 //                          SqlDataAdapter class
   4 // Author:
   5 //      Umadevi S (sumadevi@novell.com)
   6 //      Sureshkumar T (tsureshkumar@novell.com)
   7 //      Senganal T (tsenganal@novell.com)
   8 //
   9 // Copyright (c) 2004 Novell Inc., and the individuals listed
  10 // on the ChangeLog entries.
  11 //
  12 // Permission is hereby granted, free of charge, to any person obtaining
  13 // a copy of this software and associated documentation files (the
  14 // "Software"), to deal in the Software without restriction, including
  15 // without limitation the rights to use, copy, modify, merge, publish,
  16 // distribute, sublicense, and/or sell copies of the Software, and to
  17 // permit persons to whom the Software is furnished to do so, subject to
  18 // the following conditions:
  19 //
  20 // The above copyright notice and this permission notice shall be
  21 // included in all copies or substantial portions of the Software.
  22 //
  23 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  24 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  25 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  26 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  27 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  28 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  29 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  30 //
  31
  32 using System;
  33 using System.Data;
  34 using System.Data.Common;
  35 using System.Data.SqlClient;
  36 using Mono.Data;
  37 using System.Configuration;
  38
  39 using NUnit.Framework;
  40
  41 namespace MonoTests.System.Data.SqlClient
  42 {
  43         [TestFixture]
  44         [Category ("sqlserver")]
  45         public class SqlDataAdapterTest
  46         {
  47                 SqlDataAdapter adapter;
  48                 SqlDataReader dr;
  49                 DataSet data;
  50                 string connectionString = ConnectionManager.Singleton.ConnectionString;
  51                 SqlConnection conn;
  52                 EngineConfig engine;
  53
  54                 [SetUp]
  55                 public void SetUp ()
  56                 {
  57                         engine = ConnectionManager.Singleton.Engine;
  58                 }
  59
  60                 [TearDown]
  61                 public void TearDown ()
  62                 {
  63                         if (adapter != null) {
  64                                 adapter.Dispose ();
  65                                 adapter = null;
  66                         }
  67
  68                         if (dr != null) {
  69                                 dr.Close ();
  70                                 dr = null;
  71                         }
  72
  73                         if (conn != null) {
  74                                 conn.Close ();
  75                                 conn = null;
  76                         }
  77                 }
  78
  79                 [Test]
  80                 public void Update_DeleteRow ()
  81                 {
  82                         conn = new SqlConnection (ConnectionManager.Singleton.ConnectionString);
  83                         conn.Open ();
  84
  85                         DataTable dt = new DataTable ();
  86                         adapter = new SqlDataAdapter ("SELECT * FROM employee", conn);
  87                         SqlCommandBuilder builder = new SqlCommandBuilder (adapter);
  88                         adapter.DeleteCommand = builder.GetDeleteCommand ();
  89                         adapter.Fill (dt);
  90
  91                         DateTime now = DateTime.Now;
  92
  93                         DateTime doj = new DateTime (now.Year, now.Month, now.Day, now.Hour,
  94                                 now.Minute, now.Second);
  95
  96                         DateTime dob = new DateTime (now.Year, now.Month, now.Day, now.Hour,
  97                                 now.Minute, now.Second);
  98                         dob.Subtract (new TimeSpan (20 * 365, 0, 0, 0));
  99
 100                         try {
 101                                 DataRow newRow = dt.NewRow ();
 102                                 newRow ["id"] = 6002;
 103                                 newRow ["fname"] = "boston";
 104                                 newRow ["dob"] = dob;
 105                                 newRow ["doj"] = doj;
 106                                 newRow ["email"] = "mono@novell.com";
 107                                 dt.Rows.Add (newRow);
 108                                 adapter.Update (dt);
 109
 110                                 foreach (DataRow row in dt.Rows)
 111                                         if (((int) row ["id"]) == 6002)
 112                                                 row.Delete ();
 113                                 adapter.Update (dt);
 114
 115                                 SqlCommand cmd = conn.CreateCommand ();
 116                                 cmd.CommandText = "SELECT id, fname, lname, dob, doj, email FROM employee WHERE id = 6002";
 117                                 dr = cmd.ExecuteReader ();
 118                                 Assert.IsFalse (dr.Read ());
 119                                 dr.Close ();
 120                         } finally {
 121                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
 122                         }
 123                 }
 124
 125                 [Test]
 126                 public void Update_InsertRow ()
 127                 {
 128                         conn = new SqlConnection (ConnectionManager.Singleton.ConnectionString);
 129                         conn.Open ();
 130
 131                         DataTable dt = new DataTable ();
 132                         adapter = new SqlDataAdapter ("SELECT * FROM employee", conn);
 133
 134                         SqlCommandBuilder builder = new SqlCommandBuilder (adapter);
 135                         adapter.InsertCommand = builder.GetInsertCommand ();
 136                         adapter.Fill (dt);
 137
 138                         DateTime now = DateTime.Now;
 139
 140                         DateTime doj = new DateTime (now.Year, now.Month, now.Day, now.Hour,
 141                                 now.Minute, now.Second);
 142
 143                         DateTime dob = new DateTime (now.Year, now.Month, now.Day, now.Hour,
 144                                 now.Minute, now.Second);
 145                         dob.Subtract (new TimeSpan (20 * 365, 0, 0, 0));
 146
 147                         try {
 148                                 DataRow newRow = dt.NewRow ();
 149                                 newRow ["id"] = 6002;
 150                                 newRow ["fname"] = "boston";
 151                                 newRow ["dob"] = dob;
 152                                 newRow ["doj"] = doj;
 153                                 newRow ["email"] = "mono@novell.com";
 154                                 dt.Rows.Add (newRow);
 155                                 adapter.Update (dt);
 156
 157                                 SqlCommand cmd = conn.CreateCommand ();
 158                                 cmd.CommandText = "SELECT id, fname, lname, dob, doj, email FROM employee WHERE id = 6002";
 159                                 dr = cmd.ExecuteReader ();
 160                                 Assert.IsTrue (dr.Read (), "#A1");
 161                                 Assert.AreEqual (6002, dr.GetValue (0), "#A2");
 162                                 Assert.AreEqual ("boston", dr.GetValue (1), "#A3");
 163                                 Assert.AreEqual (DBNull.Value, dr.GetValue (2), "#A4");
 164                                 Assert.AreEqual (dob, dr.GetValue (3), "#A5");
 165                                 Assert.AreEqual (doj, dr.GetValue (4), "#A6");
 166                                 Assert.AreEqual ("mono@novell.com", dr.GetValue (5), "#A7");
 167                                 Assert.IsFalse (dr.Read (), "#A8");
 168                                 dr.Close ();
 169                         } finally {
 170                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
 171                         }
 172                 }
 173
 174                 [Test]
 175                 public void Update_UpdateRow ()
 176                 {
 177                         conn = new SqlConnection (ConnectionManager.Singleton.ConnectionString);
 178                         conn.Open ();
 179
 180                         DataTable dt = new DataTable ();
 181                         adapter = new SqlDataAdapter ("SELECT * FROM employee", conn);
 182                         SqlCommandBuilder builder = new SqlCommandBuilder (adapter);
 183                         adapter.UpdateCommand = builder.GetUpdateCommand ();
 184                         adapter.Fill (dt);
 185
 186                         DateTime now = DateTime.Now;
 187
 188                         DateTime doj = new DateTime (now.Year, now.Month, now.Day, now.Hour,
 189                                 now.Minute, now.Second);
 190
 191                         DateTime dob = new DateTime (now.Year, now.Month, now.Day, now.Hour,
 192                                 now.Minute, now.Second);
 193                         dob.Subtract (new TimeSpan (20 * 365, 0, 0, 0));
 194
 195                         try {
 196                                 DataRow newRow = dt.NewRow ();
 197                                 newRow ["id"] = 6002;
 198                                 newRow ["fname"] = "boston";
 199                                 newRow ["dob"] = dob;
 200                                 newRow ["doj"] = doj;
 201                                 newRow ["email"] = "mono@novell.com";
 202                                 dt.Rows.Add (newRow);
 203                                 adapter.Update (dt);
 204
 205                                 foreach (DataRow row in dt.Rows)
 206                                         if (((int) row ["id"]) == 6002)
 207                                                 row ["lname"] = "de Icaza";
 208                                 adapter.Update (dt);
 209
 210                                 SqlCommand cmd = conn.CreateCommand ();
 211                                 cmd.CommandText = "SELECT id, fname, lname, dob, doj, email FROM employee WHERE id = 6002";
 212                                 dr = cmd.ExecuteReader ();
 213                                 Assert.IsTrue (dr.Read (), "#A1");
 214                                 Assert.AreEqual (6002, dr.GetValue (0), "#A2");
 215                                 Assert.AreEqual ("boston", dr.GetValue (1), "#A3");
 216                                 Assert.AreEqual ("de Icaza", dr.GetValue (2), "#A4");
 217                                 Assert.AreEqual (dob, dr.GetValue (3), "#A5");
 218                                 Assert.AreEqual (doj, dr.GetValue (4), "#A6");
 219                                 Assert.AreEqual ("mono@novell.com", dr.GetValue (5), "#A7");
 220                                 Assert.IsFalse (dr.Read (), "#A8");
 221                                 dr.Close ();
 222                         } finally {
 223                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
 224                         }
 225                 }
 226
 227                 /**
 228                    The below test will not run everytime, since the region id column is unique
 229                    so change the regionid if you want the test to pass.
 230                 **/
 231                 /*
 232                 [Test]
 233                 public void UpdateTest () {
 234                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
 235                         try {
 236                                 ConnectionManager.Singleton.OpenConnection ();
 237                                 DataTable dt = new DataTable();
 238                                 SqlDataAdapter da = null;
 239                                 da = new SqlDataAdapter("Select * from employee", conn);
 240                                 //SqlCommandBuilder cb = new SqlCommandBuilder (da);
 241                                 da.Fill(dt);
 242                                 DataRow dr = dt.NewRow();
 243                                 dr ["id"] = 6002;
 244                                 dr ["fname"] = "boston";
 245                                 dr ["dob"] = DateTime.Now.Subtract (new TimeSpan (20*365, 0, 0, 0));
 246                                 dr ["doj"] = DateTime.Now;
 247                                 dt.Rows.Add(dr);
 248
 249                                 da.Update(dt);
 250                         } finally {
 251                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
 252                                 ConnectionManager.Singleton.CloseConnection ();
 253                         }
 254                 }
 255
 256                 private static void OnRowUpdatedTest (object sender, SqlRowUpdatedEventArgs e)
 257                 {
 258                         rowUpdated = true;
 259                 }
 260
 261                 private static void OnRowUpdatingTest (object sender, SqlRowUpdatingEventArgs e)
 262                 {
 263                         rowUpdating = true;
 264                 }
 265
 266                 private static bool rowUpdated = false;
 267                 private static bool rowUpdating = false;
 268                 [Test]
 269                 public void RowUpdatedTest () {
 270                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
 271                         try {
 272                                 ConnectionManager.Singleton.OpenConnection ();
 273                                 DataTable dt = null;
 274                                 DataSet ds = new DataSet ();
 275                                 SqlDataAdapter da = null;
 276                                 da = new SqlDataAdapter("Select * from employee", conn);
 277                                 //SqlCommandBuilder cb = new SqlCommandBuilder (da);
 278                                 rowUpdated = false;
 279                                 rowUpdating = false;
 280                                 da.RowUpdated += new SqlRowUpdatedEventHandler (OnRowUpdatedTest);
 281                                 da.RowUpdating += new SqlRowUpdatingEventHandler (OnRowUpdatingTest);
 282                                 da.Fill (ds);
 283                                 dt = ds.Tables [0];
 284                                 dt.Rows[0][0] = 200;
 285                                 da.UpdateCommand = new SqlCommand ("Update employee set id = @id");
 286                                 da.Update (dt);
 287                                 dt.Rows[0][0] = 1;
 288                                 da.Update (dt);
 289                                 da.RowUpdated -= new SqlRowUpdatedEventHandler (OnRowUpdatedTest);
 290                                 da.RowUpdating -= new SqlRowUpdatingEventHandler (OnRowUpdatingTest);
 291                                 Assert.AreEqual (true, rowUpdated, "RowUpdated");
 292                                 Assert.AreEqual (true, rowUpdating, "RowUpdating");
 293                         } finally {
 294                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
 295                                 ConnectionManager.Singleton.CloseConnection ();
 296                         }
 297                 }
 298                 */
 299
 300                 /**
 301                    This needs a errortable created as follows
 302                    id uniqueidentifier,name char(10) , with values
 303                    Guid         name
 304                    {A12...}     NULL
 305                    NULL         bbbbbb
 306                 **/
 307                 [Test]
 308                 public void NullGuidTest()
 309                 {
 310                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
 311                         try {
 312                                 ConnectionManager.Singleton.OpenConnection ();
 313                                 DBHelper.ExecuteNonQuery (conn, "create table #tmp_guid_table ( " +
 314                                                           " id uniqueidentifier default newid (), " +
 315                                                           " name char (10))");
 316                                 DBHelper.ExecuteNonQuery (conn, "insert into #tmp_guid_table (name) values (null)");
 317                                 DBHelper.ExecuteNonQuery (conn, "insert into #tmp_guid_table (id, name) values (null, 'bbbb')");
 318                                 SqlDataAdapter da = new SqlDataAdapter("select * from #tmp_guid_table", conn);
 319                                 DataSet ds = new DataSet();
 320                                 da.Fill(ds);
 321                                 Assert.AreEqual (1, ds.Tables.Count, "#1");
 322                                 Assert.AreEqual (DBNull.Value, ds.Tables [0].Rows [1] ["id"], "#2");
 323                         } finally {
 324                                 ConnectionManager.Singleton.CloseConnection ();
 325                         }
 326                         // the bug 68804 - is that the fill hangs!
 327                         Assert.AreEqual("Done","Done");
 328                 }
 329
 330                 [Test]
 331                 public void DefaultConstructorTest ()
 332                 {
 333                         adapter = new SqlDataAdapter ();
 334                         Assert.AreEqual (MissingMappingAction.Passthrough,
 335                                 adapter.MissingMappingAction,
 336                                 "#1 Missing Mapping acttion default to Passthrough");
 337                         Assert.AreEqual (MissingSchemaAction.Add,
 338                                 adapter.MissingSchemaAction,
 339                                 "#2 Missing Schme action default to Add");
 340                 }
 341
 342                 [Test]
 343                 public void OverloadedConstructorsTest ()
 344                 {
 345                         SqlCommand selCmd = new SqlCommand ("Select * from numeric_family");
 346                         adapter = new SqlDataAdapter (selCmd);
 347                         Assert.AreEqual (MissingMappingAction.Passthrough,
 348                                 adapter.MissingMappingAction,
 349                                 "#1 Missing Mapping acttion default to Passthrough");
 350                         Assert.AreEqual (MissingSchemaAction.Add,
 351                                 adapter.MissingSchemaAction,
 352                                 "#2 Missing Schme action default to Add");
 353                         Assert.AreSame (selCmd, adapter.SelectCommand,
 354                                 "#3 Select Command shud be a ref to the arg passed");
 355
 356                         conn = new SqlConnection (connectionString);
 357                         String selStr = "Select * from numeric_family";
 358                         adapter = new SqlDataAdapter (selStr, conn);
 359                         Assert.AreEqual (MissingMappingAction.Passthrough,
 360                                 adapter.MissingMappingAction,
 361                                 "#4 Missing Mapping acttion default to Passthrough");
 362                         Assert.AreEqual (MissingSchemaAction.Add,
 363                                 adapter.MissingSchemaAction,
 364                                 "#5 Missing Schme action default to Add");
 365                         Assert.AreSame (selStr, adapter.SelectCommand.CommandText,
 366                                 "#6 Select Command shud be a ref to the arg passed");
 367                         Assert.AreSame (conn, adapter.SelectCommand.Connection,
 368                                 "#7 cmd.connection shud be t ref to connection obj");
 369
 370                         selStr = "Select * from numeric_family";
 371                         adapter = new SqlDataAdapter (selStr, connectionString);
 372                         Assert.AreEqual (MissingMappingAction.Passthrough,
 373                                 adapter.MissingMappingAction,
 374                                 "#8 Missing Mapping action shud default to Passthrough");
 375                         Assert.AreEqual (MissingSchemaAction.Add,
 376                                 adapter.MissingSchemaAction,
 377                                 "#9 Missing Schema action shud default to Add");
 378                         Assert.AreSame (selStr,
 379                                 adapter.SelectCommand.CommandText,
 380                                 "#10");
 381                         Assert.AreEqual (connectionString,
 382                                 adapter.SelectCommand.Connection.ConnectionString,
 383                                 "#11  ");
 384                 }
 385
 386                 [Test]
 387                 public void Fill_Test_ConnState ()
 388                 {
 389                         //Check if Connection State is maintained correctly ..
 390                         data = new DataSet ("test1");
 391                         adapter = new SqlDataAdapter ("select id from numeric_family where id=1",
 392                                          connectionString);
 393                         SqlCommand cmd = adapter.SelectCommand ;
 394
 395                         Assert.AreEqual (ConnectionState.Closed,
 396                                 cmd.Connection.State, "#1 Connection shud be in closed state");
 397                         adapter.Fill (data);
 398                         Assert.AreEqual (1, data.Tables.Count, "#2 One table shud be populated");
 399                         Assert.AreEqual (ConnectionState.Closed, cmd.Connection.State,
 400                                 "#3 Connection shud be closed state");
 401
 402                         data = new DataSet ("test2");
 403                         cmd.Connection.Open ();
 404                         Assert.AreEqual (ConnectionState.Open, cmd.Connection.State,
 405                                 "#3 Connection shud be open");
 406                         adapter.Fill (data);
 407                         Assert.AreEqual (1, data.Tables.Count, "#4 One table shud be populated");
 408                         Assert.AreEqual (ConnectionState.Open, cmd.Connection.State,
 409                                 "#5 Connection shud be open");
 410                         cmd.Connection.Close ();
 411
 412                         // Test if connection is closed when exception occurs
 413                         cmd.CommandText = "select id1 from numeric_family";
 414                         try {
 415                                 adapter.Fill (data);
 416                         } catch {
 417                                 if (cmd.Connection.State == ConnectionState.Open) {
 418                                         cmd.Connection.Close ();
 419                                         Assert.Fail ("# Connection Shud be Closed");
 420                                 }
 421                         }
 422                 }
 423
 424                 [Test]
 425                 public void Fill_Test_Data ()
 426                 {
 427                         //Check if a table is created for each resultset
 428                         String batchQuery = "Select id,type_bit,type_int from numeric_family;";
 429                         batchQuery += "Select type_bit from numeric_family";
 430                         adapter = new SqlDataAdapter (batchQuery, connectionString);
 431                         data = new DataSet ("test1");
 432                         adapter.Fill (data);
 433                         Assert.AreEqual (2, data.Tables.Count,"#1 2 Table shud be created");
 434
 435                         //Check if Table and Col are named correctly for unnamed columns
 436                         string query = "Select 10,20 from numeric_family;" ;
 437                         query += "Select 10,20 from numeric_family";
 438                         adapter = new SqlDataAdapter (query, connectionString);
 439                         data = new DataSet ("test2");
 440                         adapter.Fill (data);
 441                         Assert.AreEqual (2, data.Tables.Count,
 442                                 "#2 2 Tables shud be created");
 443                         Assert.AreEqual ("Table", data.Tables[0].TableName, "#3");
 444                         Assert.AreEqual ("Table1", data.Tables[1].TableName, "#4");
 445                         Assert.AreEqual ("Column1", data.Tables[0].Columns[0].ColumnName, "#5");
 446                         Assert.AreEqual ("Column2", data.Tables[0].Columns[1].ColumnName, "#6");
 447                         Assert.AreEqual ("Column1", data.Tables[1].Columns[0].ColumnName, "#7");
 448                         Assert.AreEqual ("Column2", data.Tables[1].Columns[1].ColumnName, "#8");
 449
 450                         //Check if dup columns are named correctly
 451                         query = "select A.id ,B.id , C.id from numeric_family A, ";
 452                         query += "numeric_family B , numeric_family C";
 453                         adapter = new SqlDataAdapter (query, connectionString);
 454                         data = new DataSet ("test3");
 455                         adapter.Fill (data);
 456
 457                         // NOTE msdotnet contradicts documented behavior
 458                         // as per documentation the column names should be
 459                         // id1,id2,id3 .. but msdotnet returns id,id1,id2
 460                         Assert.AreEqual ("id", data.Tables[0].Columns[0].ColumnName,
 461                                 "#9 if colname is duplicated ,shud be col,col1,col2 etc");
 462                         Assert.AreEqual ("id1", data.Tables[0].Columns[1].ColumnName,
 463                                 "#10 if colname is duplicated ,shud be col,col1,col2 etc");
 464                         Assert.AreEqual ("id2", data.Tables[0].Columns[2].ColumnName,
 465                                 "#11 if colname is duplicated ,shud be col,col1,col2 etc");
 466
 467                         // Test if tables are created and named accordingly ,
 468                         // but only for those queries returning result sets
 469                         query = "update numeric_family set id=100 where id=50;";
 470                         query += "select * from numeric_family";
 471                         adapter = new SqlDataAdapter (query, connectionString);
 472                         data = new DataSet ("test4");
 473                         adapter.Fill (data);
 474                         Assert.AreEqual (1 ,data.Tables.Count,
 475                                 "#12 Tables shud be named only for queries returning a resultset");
 476                         Assert.AreEqual ("Table", data.Tables[0].TableName,
 477                                 "#13 The first resutlset shud have 'Table' as its name");
 478
 479                         // Test behavior with an outerjoin
 480                         query = "select A.id,B.type_bit from numeric_family A LEFT OUTER JOIN ";
 481                         query += "numeric_family B on A.id = B.type_bit";
 482                         adapter = new SqlDataAdapter (query, connectionString);
 483                         data = new DataSet ("test5");
 484                         adapter.Fill (data);
 485                         Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length,
 486                                 "#14 Primary Key shudnt be set if an outer join is performed");
 487                         Assert.AreEqual (0, data.Tables[0].Constraints.Count,
 488                                 "#15 Constraints shudnt be set if an outer join is performed");
 489                         adapter = new SqlDataAdapter ("select id from numeric_family",
 490                                         connectionString);
 491                         data = new DataSet ("test6");
 492                         adapter.Fill (data, 1, 1, "numeric_family");
 493                         Assert.AreEqual (1, data.Tables[0].Rows.Count, "#16");
 494                         Assert.AreEqual (2, data.Tables[0].Rows[0][0], "#17");
 495
 496                         // only one test for DataTable.. DataSet tests covers others
 497                         adapter = new SqlDataAdapter ("select id from numeric_family",
 498                                         connectionString);
 499                         DataTable table = new DataTable ("table1");
 500                         adapter.Fill (table);
 501                         Assert.AreEqual (4, table.Rows.Count , "#18");
 502                 }
 503
 504                 [Test]
 505                 public void Fill_Test_PriKey ()
 506                 {
 507                         // Test if Primary Key & Constraints Collection is correct
 508                         adapter = new SqlDataAdapter ("select id,type_bit from numeric_family",
 509                                         connectionString);
 510                         adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 511                         data = new DataSet ("test1");
 512                         adapter.Fill (data);
 513                         Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length,
 514                                 "#1 Primary Key shud be set");
 515                         Assert.AreEqual (1, data.Tables[0].Constraints.Count,
 516                                 "#2 Constraints shud be set");
 517                         Assert.AreEqual (4, data.Tables[0].Rows.Count,
 518                                 "#3 No Of Rows shud be 4");
 519
 520                         // Test if data is correctly merged
 521                         adapter.Fill (data);
 522                         Assert.AreEqual (4, data.Tables[0].Rows.Count,
 523                                 "#4 No of Row shud still be 4");
 524
 525                         // Test if rows are appended  and not merged
 526                         // when primary key is not returned in the result-set
 527                         string query = "Select type_int from numeric_family";
 528                         adapter.SelectCommand.CommandText = query;
 529                         data = new DataSet ("test2");
 530                         adapter.Fill (data);
 531                         Assert.AreEqual (4, data.Tables[0].Rows.Count,
 532                                 "#5 No of Rows shud be 4");
 533                         adapter.Fill (data);
 534                         Assert.AreEqual (8, data.Tables[0].Rows.Count,
 535                                 "#6 No of Rows shud double now");
 536                 }
 537
 538                 [Test]
 539                 public void Fill_Test_Exceptions ()
 540                 {
 541                         adapter = new SqlDataAdapter ("select * from numeric_family",
 542                                         connectionString);
 543                         data = new DataSet ("test1");
 544                         try {
 545                                 adapter.Fill (data, -1, 0, "numeric_family");
 546                                 Assert.Fail ("#1 Exception shud be thrown:Incorrect Arguments");
 547                         }catch (AssertionException e){
 548                                 throw e;
 549                         }catch (Exception e){
 550                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 551                                         "#2 Incorrect Exception : "  + e);
 552                         }
 553
 554                         // conn is not closed due to a bug..
 555                         // can be removed later
 556                         adapter.SelectCommand.Connection.Close ();
 557
 558                         try {
 559                                 adapter.Fill (data , 0 , -1 , "numeric_family");
 560                                 Assert.Fail ("#3 Exception shud be thrown:Incorrect Arguments");
 561                         }catch (AssertionException e){
 562                                 throw e;
 563                         }catch (Exception e){
 564                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 565                                         "#4 Incorrect Exception : "  + e);
 566                         }
 567                         // conn is curr not closed.. can be removed later
 568                         adapter.SelectCommand.Connection.Close ();
 569
 570                         /*
 571                         // NOTE msdotnet contradicts documented behavior
 572                         // InvalidOperationException is expected if table is not valid
 573                         try {
 574                                 adapter.Fill (data , 0 , 0 , "invalid_talbe_name");
 575                         }catch (InvalidOperationException e) {
 576                                 ex= e;
 577                         }catch (Exception e){
 578                                 Assert.Fail ("#5 Exception shud be thrown : incorrect arugments ");
 579                         }
 580                         Assert.IsNotNull (ex , "#6 Exception shud be thrown : incorrect args ");
 581                         adapter.SelectCommand.Connection.Close (); // tmp .. can be removed once the bug if fixed
 582                         ex=null;
 583                         */
 584
 585                         try {
 586                                 adapter.Fill ( null , 0 , 0 , "numeric_family");
 587                                 Assert.Fail ( "#7 Exception shud be thrown : Invalid Dataset");
 588                         }catch (AssertionException e){
 589                                 throw e ;
 590                         }catch (ArgumentNullException) {
 591
 592                         }catch (Exception e) {
 593                                 Assert.AreEqual (typeof(SystemException), e.GetType(),
 594                                         "#8 Incorrect Exception : " + e);
 595                         }
 596                         // conn is currently not being closed..
 597                         //need to be removed once behavior is fixed
 598                         adapter.SelectCommand.Connection.Close ();
 599
 600                         adapter.SelectCommand.Connection = null;
 601                         try {
 602                                 adapter.Fill (data);
 603                                 Assert.Fail ("#9 Exception shud be thrown : Invalid Connection");
 604                         }catch (AssertionException e){
 605                                 throw e;
 606                         }catch (Exception e){
 607                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 608                                         "#10 Incorrect Exception : " + e);
 609                         }
 610                 }
 611
 612                 bool FillErrorContinue = false;
 613                 [Test]
 614                 public void Fill_Test_FillErrorTest ()
 615                 {
 616                         string query = "select type_int from numeric_family where id=1 or id=4 ";
 617
 618                         DataSet ds = new DataSet ();
 619                         DataTable table = ds.Tables.Add ("test");
 620                         table.Columns.Add ("col", typeof (short));
 621
 622                         adapter = new SqlDataAdapter (query, connectionString);
 623                         DataTableMapping mapping = adapter.TableMappings.Add ("numeric_family", "test");
 624                         mapping.ColumnMappings.Add ("type_int", "col");
 625
 626                         try {
 627                                 adapter.Fill (ds, "numeric_family");
 628                                 Assert.Fail ("#A1");
 629                         } catch (OverflowException) {
 630                         } catch (ArgumentException ex) {
 631                                 // System.OverflowException: Value was either too large or too
 632                                 // small for an Int16
 633                                 Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#A2");
 634                                 Assert.IsNotNull (ex.InnerException, "#A3");
 635                                 Assert.IsNotNull (ex.Message, "#A4");
 636                                 Assert.IsNull (ex.ParamName, "#A5");
 637
 638                                 OverflowException inner = ex.InnerException as OverflowException;
 639                                 Assert.IsNotNull (inner, "#A6");
 640                                 Assert.AreEqual (typeof (OverflowException), inner.GetType (), "#A7");
 641                                 Assert.IsNull (inner.InnerException, "#A8");
 642                                 Assert.IsNotNull (inner.Message, "#A9");
 643                         }
 644                         Assert.AreEqual (0, ds.Tables [0].Rows.Count, "#A10");
 645
 646                         adapter.FillError += new FillErrorEventHandler (ErrorHandler);
 647                         FillErrorContinue = false;
 648                         try {
 649                                 adapter.Fill (ds, "numeric_family");
 650                                 Assert.Fail ("#B1");
 651                         } catch (OverflowException) {
 652                         } catch (ArgumentException ex) {
 653                                 // System.OverflowException: Value was either too large or too
 654                                 // small for an Int16
 655                                 Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#B2");
 656                                 Assert.IsNotNull (ex.InnerException, "#B3");
 657                                 Assert.IsNotNull (ex.Message, "#B4");
 658                                 Assert.IsNull (ex.ParamName, "#B5");
 659
 660                                 OverflowException inner = ex.InnerException as OverflowException;
 661                                 Assert.IsNotNull (inner, "#B6");
 662                                 Assert.AreEqual (typeof (OverflowException), inner.GetType (), "#B7");
 663                                 Assert.IsNull (inner.InnerException, "#B8");
 664                                 Assert.IsNotNull (inner.Message, "#B9");
 665                         }
 666                         Assert.AreEqual (0, ds.Tables [0].Rows.Count, "#B10");
 667
 668                         FillErrorContinue = true;
 669                         int count = adapter.Fill (ds, "numeric_family");
 670                         Assert.AreEqual (1, ds.Tables [0].Rows.Count, "#C1");
 671                         Assert.AreEqual (1, count, "#C2");
 672                 }
 673
 674                 void ErrorHandler (object sender, FillErrorEventArgs args)
 675                 {
 676                         args.Continue = FillErrorContinue;
 677                 }
 678
 679                 [Test]
 680                 public void GetFillParametersTest ()
 681                 {
 682                         string query = "select id, type_bit from numeric_family where id > @param1";
 683                         adapter = new SqlDataAdapter (query, connectionString);
 684                         IDataParameter[] param = adapter.GetFillParameters ();
 685                         Assert.AreEqual (0, param.Length, "#1 size shud be 0");
 686
 687                         SqlParameter param1 = new SqlParameter ();
 688                         param1.ParameterName = "@param1";
 689                         param1.Value = 2;
 690                         adapter.SelectCommand.Parameters.Add (param1);
 691
 692                         param = adapter.GetFillParameters ();
 693                         Assert.AreEqual (1, param.Length, "#2 count shud be 1");
 694                         Assert.AreEqual (param1, param[0], "#3 Params shud be equal");
 695                 }
 696
 697                 [Test]
 698                 public void FillSchemaTest ()
 699                 {
 700                         string query;
 701
 702                         // Test if connection is closed if excepton occurs during fill schema
 703                         query = "select * from invalid_table";
 704                         adapter = new SqlDataAdapter (query, connectionString);
 705                         data = new DataSet ("test");
 706                         try {
 707                                 adapter.FillSchema (data , SchemaType.Source);
 708                         } catch {
 709                                 if (adapter.SelectCommand.Connection.State != ConnectionState.Closed) {
 710                                         Assert.Fail ("#0 Conn shud be closed if exception occurs");
 711                                         adapter.SelectCommand.Connection.Close();
 712                                 }
 713                         }
 714
 715                         // Test Primary Key is set (since primary key column returned)
 716                         query = "select id, type_int from numeric_family where id=1";
 717                         adapter = new SqlDataAdapter (query, connectionString);
 718                         data = new DataSet ("test1");
 719                         adapter.FillSchema (data , SchemaType.Source);
 720
 721                         Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length,
 722                                 "#1 Primary Key property must be set");
 723
 724                         // Test Primary Key is not set (since primary key column is returned)
 725                         query = "select type_bit, type_int from numeric_family where id=1";
 726                         adapter = new SqlDataAdapter (query, connectionString);
 727                         data = new DataSet ("test2");
 728                         adapter.FillSchema (data, SchemaType.Source);
 729                         Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length,
 730                                 "#2 Primary Key property should not be set");
 731
 732                         // Test multiple tables are created for a batch query
 733                         query = "Select id ,type_bit from numeric_family;" ;
 734                         query += "Select id,type_bit,type_int from numeric_family;";
 735                         data = new DataSet ("test3");
 736                         adapter = new SqlDataAdapter (query, connectionString);
 737                         adapter.FillSchema (data , SchemaType.Source);
 738                         Assert.AreEqual (2 , data.Tables.Count , "#3 A table shud be created for each Result Set");
 739                         Assert.AreEqual (2 , data.Tables[0].Columns.Count , "#4 should have 2 columns");
 740                         Assert.AreEqual (3 , data.Tables[1].Columns.Count , "#5 Should have 3 columns");
 741
 742                         // Test if table names and column names  are filled correctly
 743                         query = "select 10,20 from numeric_family;" ;
 744                         query += "select 10,20 from numeric_family;";
 745                         adapter = new SqlDataAdapter (query, connectionString);
 746                         data = new DataSet ("test4");
 747                         try {
 748                                 adapter.FillSchema (data , SchemaType.Source);
 749                         }catch (Exception e){
 750                                 Assert.Fail ("#3 Unexpected Exception : " + e);
 751                         }
 752                         Assert.AreEqual ( "Table", data.Tables[0].TableName);
 753                         Assert.AreEqual ( "Table1", data.Tables[1].TableName);
 754                         Assert.AreEqual ( "Column1", data.Tables[0].Columns[0].ColumnName,
 755                                 "#6 Unnamed col shud be named as 'ColumnN'");
 756                         Assert.AreEqual ( "Column2", data.Tables[0].Columns[1].ColumnName,
 757                                 "#7 Unnamed col shud be named as 'ColumnN'");
 758                         Assert.AreEqual ( "Column1", data.Tables[1].Columns[0].ColumnName,
 759                                 "#8 Unnamed col shud be named as 'ColumnN'");
 760                         Assert.AreEqual ( "Column2", data.Tables[1].Columns[1].ColumnName,
 761                                 "#9 Unnamed col shud be named as 'ColumnN'");
 762                         Assert.AreEqual (ConnectionState.Closed, adapter.SelectCommand.Connection.State,
 763                                 "#10 Connection shud be closed");
 764
 765                         // Test if mapping works correctly
 766                         // doesent work in both mono and msdotnet
 767                         // gotto check if something is wrong
 768                         /*
 769                         query = "select id,type_bit from numeric_family";
 770                         adapter = new SqlDataAdapter (query, connectionString);
 771                         data = new DataSet ("test");
 772                         DataTable table = data.Tables.Add ("numeric_family_1");
 773                         table.Columns.Add ("id");
 774                         table.Columns.Add ("type_bit");
 775                         DataTableMapping map = adapter.TableMappings.Add("numeric_family_1",
 776                                                         "numeric_family");
 777                         map.ColumnMappings.Add ("id", "id_1");
 778                         map.ColumnMappings.Add ("type_bit", "type_bit_1");
 779                         adapter.FillSchema (data, SchemaType.Source, "numeric_family");
 780                         foreach (DataTable tab in data.Tables){
 781                                 Console.WriteLine ("Table == {0}",tab.TableName);
 782                                 foreach (DataColumn col in tab.Columns)
 783                                         Console.WriteLine (" Col = {0} " , col.ColumnName);
 784                         }
 785                         */
 786                 }
 787
 788                 [Test]
 789                 public void MissingSchemaActionTest ()
 790                 {
 791                         adapter = new SqlDataAdapter (
 792                                         "select id,type_bit,type_int from numeric_family where id<=4",
 793                                          connectionString);
 794                         data = new DataSet ();
 795                         Assert.AreEqual (MissingSchemaAction.Add, adapter.MissingSchemaAction,
 796                                          "#1 Default Value");
 797
 798                         adapter.Fill (data);
 799                         Assert.AreEqual (1, data.Tables.Count , "#1 One table shud be populated");
 800                         Assert.AreEqual (3, data.Tables[0].Columns.Count, "#2 Missing cols are added");
 801                         Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length, "#3 Default Value");
 802
 803                         adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 804                         data.Reset();
 805                         adapter.Fill (data);
 806                         Assert.AreEqual (3, data.Tables[0].Columns.Count,
 807                                 "#4 Missing cols are added");
 808                         Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length, "#5 Default Value");
 809
 810                         adapter.MissingSchemaAction = MissingSchemaAction.Ignore ;
 811                         data.Reset ();
 812                         adapter.Fill (data);
 813                         Assert.AreEqual (0, data.Tables.Count, "#6 Data shud be ignored");
 814
 815                         adapter.MissingSchemaAction = MissingSchemaAction.Error ;
 816                         data.Reset();
 817                         try {
 818                                 adapter.Fill (data);
 819                                 Assert.Fail ("#8 Exception shud be thrown: Schema Mismatch");
 820                         } catch (InvalidOperationException ex) {
 821                                 Assert.AreEqual (typeof(InvalidOperationException), ex.GetType(),
 822                                         "#9");
 823                         }
 824
 825                         // Test for invalid MissingSchema Value
 826                         try {
 827                                 adapter.MissingSchemaAction = (MissingSchemaAction)(-5000);
 828                                 Assert.Fail ("#10 Exception shud be thrown: Invalid Value");
 829                         } catch (ArgumentOutOfRangeException ex) {
 830                                 Assert.AreEqual (typeof (ArgumentOutOfRangeException), ex.GetType (), "#11");
 831                         }
 832
 833                         // Tests if Data is filled correctly if schema is defined
 834                         // manually and MissingSchemaAction.Error is set
 835                         adapter.MissingSchemaAction = MissingSchemaAction.Error;
 836                         data.Reset();
 837                         DataTable table = data.Tables.Add ("Table");
 838                         table.Columns.Add ("id");
 839                         table.Columns.Add ("type_bit");
 840                         table.Columns.Add ("type_int");
 841                         adapter.Fill (data);
 842                         Assert.AreEqual (1, data.Tables.Count, "#12");
 843                         Assert.AreEqual (4, data.Tables[0].Rows.Count, "#13");
 844                 }
 845
 846                 [Test]
 847                 public void MissingMappingActionTest ()
 848                 {
 849                         adapter = new SqlDataAdapter ("select id,type_bit from numeric_family where id=1",
 850                                         connectionString);
 851                         data = new DataSet ();
 852                         Assert.AreEqual (adapter.MissingMappingAction,
 853                                 MissingMappingAction.Passthrough,
 854                                 "#1 Default Value");
 855                         adapter.Fill(data);
 856                         Assert.AreEqual (1, data.Tables.Count,
 857                                 "#2 One Table shud be created");
 858                         Assert.AreEqual (2, data.Tables[0].Columns.Count,
 859                                 "#3 Two Cols shud be created");
 860
 861                         adapter.MissingMappingAction = MissingMappingAction.Ignore;
 862                         data.Reset ();
 863                         adapter.Fill (data);
 864                         Assert.AreEqual (0, data.Tables.Count, "#4 No table shud be created");
 865
 866                         adapter.MissingMappingAction = MissingMappingAction.Error;
 867                         data.Reset ();
 868                         try {
 869                                 adapter.Fill (data);
 870                                 Assert.Fail ("#5 Exception shud be thrown : Mapping is missing");
 871                         } catch (InvalidOperationException ex) {
 872                                 Assert.AreEqual (typeof(InvalidOperationException), ex.GetType(),
 873                                         "#6");
 874                         }
 875
 876                         try {
 877                                 adapter.MissingMappingAction = (MissingMappingAction)(-5000);
 878                                 Assert.Fail ("#7 Exception shud be thrown : Invalid Value");
 879                         } catch (ArgumentOutOfRangeException ex) {
 880                                 Assert.AreEqual (typeof (ArgumentOutOfRangeException), ex.GetType (),
 881                                         "#8");
 882                         }
 883
 884                         // Test if mapping the column and table names works correctly
 885                         adapter.MissingMappingAction = MissingMappingAction.Error;
 886                         data.Reset ();
 887                         DataTable table = data.Tables.Add ("numeric_family_1");
 888                         table.Columns.Add ("id_1");
 889                         table.Columns.Add ("type_bit_1");
 890                         table.Columns.Add ("type_int_1");
 891                         DataTableMapping tableMap = adapter.TableMappings.Add ("numeric_family",
 892                                                          "numeric_family_1");
 893                         tableMap.ColumnMappings.Add ("id", "id_1");
 894                         tableMap.ColumnMappings.Add ("type_bit", "type_bit_1");
 895                         tableMap.ColumnMappings.Add ("type_int", "type_int_1");
 896                         adapter.Fill (data,"numeric_family");
 897                         Assert.AreEqual (1, data.Tables.Count ,
 898                                 "#8 The DataTable shud be correctly mapped");
 899                         Assert.AreEqual (3, data.Tables[0].Columns.Count,
 900                                 "#9 The DataColumns shud be corectly mapped");
 901                         Assert.AreEqual (1, data.Tables[0].Rows.Count,
 902                                 "#10 Data shud be populated if mapping is correct");
 903                 }
 904
 905                 [Test] // bug #76433
 906                 public void FillSchema_ValuesTest()
 907                 {
 908                         using (SqlConnection conn = new SqlConnection(connectionString)) {
 909                                 conn.Open();
 910                                 IDbCommand command = conn.CreateCommand();
 911
 912                                 // Create Temp Table
 913                                 String cmd = "Create Table #tmp_TestTable (" ;
 914                                 cmd += "Field1 DECIMAL (10) NOT NULL,";
 915                                 cmd += "Field2 DECIMAL(19))";
 916                                 command.CommandText = cmd;
 917                                 command.ExecuteNonQuery();
 918
 919                                 DataSet dataSet = new DataSet();
 920                                 string selectString = "SELECT * FROM #tmp_TestTable";
 921                                 IDbDataAdapter dataAdapter = new SqlDataAdapter (
 922                                                                         selectString, conn);
 923                                 dataAdapter.FillSchema(dataSet, SchemaType.Mapped);
 924
 925                                 Assert.AreEqual (1, dataSet.Tables.Count, "#1");
 926                                 Assert.IsFalse (dataSet.Tables[0].Columns[0].AllowDBNull,"#2");
 927                                 Assert.IsTrue (dataSet.Tables[0].Columns[1].AllowDBNull,"#3");
 928                         }
 929                 }
 930
 931                 [Test]
 932                 public void Fill_CheckSchema ()
 933                 {
 934                         using (SqlConnection conn = new SqlConnection(connectionString)) {
 935                                 conn.Open();
 936
 937                                 IDbCommand command = conn.CreateCommand();
 938
 939                                 // Create Temp Table
 940                                 String cmd = "Create Table #tmp_TestTable (" ;
 941                                 cmd += "id int primary key,";
 942                                 cmd += "field int not null)";
 943                                 command.CommandText = cmd;
 944                                 command.ExecuteNonQuery();
 945
 946                                 DataSet dataSet = new DataSet();
 947                                 string selectString = "SELECT * from #tmp_TestTable";
 948                                 IDbDataAdapter dataAdapter = new SqlDataAdapter (
 949                                                                         selectString,conn);
 950                                 dataAdapter.Fill (dataSet);
 951                                 Assert.AreEqual (1, dataSet.Tables.Count, "#A1");
 952                                 Assert.AreEqual (2, dataSet.Tables [0].Columns.Count, "#A2");
 953                                 Assert.IsTrue (dataSet.Tables [0].Columns [1].AllowDBNull, "#A3");
 954                                 Assert.AreEqual (0, dataSet.Tables [0].PrimaryKey.Length, "#A4");
 955
 956                                 dataSet.Reset ();
 957                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 958                                 dataAdapter.Fill (dataSet);
 959                                 Assert.AreEqual (1, dataSet.Tables.Count, "#B1");
 960                                 Assert.AreEqual (2, dataSet.Tables [0].Columns.Count, "#B2");
 961                                 Assert.IsFalse (dataSet.Tables [0].Columns [1].AllowDBNull, "#B3");
 962                                 if (ClientVersion == 7)
 963                                         Assert.AreEqual (0, dataSet.Tables [0].PrimaryKey.Length, "#B4");
 964                                 else
 965                                         Assert.AreEqual (1, dataSet.Tables [0].PrimaryKey.Length, "#B4");
 966                         }
 967                 }
 968
 969                 [Test]
 970                 public void FillSchema_CheckSchema ()
 971                 {
 972                         using (SqlConnection conn = new SqlConnection(connectionString)) {
 973                                 conn.Open();
 974
 975                                 IDbCommand command = conn.CreateCommand();
 976
 977                                 // Create Temp Table
 978                                 String cmd = "Create Table #tmp_TestTable (" ;
 979                                 cmd += "id int primary key,";
 980                                 cmd += "field int not null)";
 981                                 command.CommandText = cmd;
 982                                 command.ExecuteNonQuery();
 983
 984                                 DataSet dataSet = new DataSet();
 985                                 string selectString = "SELECT * from #tmp_TestTable";
 986                                 IDbDataAdapter dataAdapter = new SqlDataAdapter (
 987                                                                         selectString,conn);
 988
 989                                 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
 990                                 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#1");
 991
 992                                 dataSet.Reset ();
 993                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.Add;
 994                                 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
 995                                 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#2");
 996
 997                                 dataSet.Reset ();
 998                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.Ignore;
 999                                 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
1000                                 Assert.AreEqual (0, dataSet.Tables.Count, "#3");
1001
1002                                 dataSet.Reset ();
1003                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.Error;
1004                                 try {
1005                                         dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
1006                                         Assert.Fail ("#4 Error should be thrown");
1007                                 } catch (InvalidOperationException ex) {
1008                                         Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#4");
1009                                 }
1010                         }
1011                 }
1012
1013                 [Test]
1014                 public void CreateViewSSPITest ()
1015                 {
1016                         SqlConnection conn = new SqlConnection (ConfigurationSettings.AppSettings ["SSPIConnString"]);
1017                         conn.Open ();
1018
1019                         string sql = "create view MONO_TEST_VIEW as select * from Numeric_family";
1020
1021                         SqlCommand dbcmd = new SqlCommand( sql, conn );
1022                         dbcmd.ExecuteNonQuery();
1023
1024                         sql = "drop view MONO_TEST_VIEW";
1025
1026                         dbcmd = new SqlCommand( sql, conn );
1027                         dbcmd.ExecuteNonQuery();
1028
1029                         conn.Close();
1030                 }
1031
1032                 [Test]
1033                 public void Fill_RelatedTables ()
1034                 {
1035                         SqlConnection conn = new SqlConnection(connectionString);
1036                         using (conn) {
1037                                 conn.Open();
1038                                 IDbCommand command = conn.CreateCommand();
1039
1040                                 DataSet dataSet = new DataSet();
1041                                 string selectString = "SELECT id, type_int from numeric_family where id < 3";
1042                                 DbDataAdapter dataAdapter = new SqlDataAdapter (selectString,conn);
1043
1044                                 DataTable table2 = dataSet.Tables.Add ("table2");
1045                                 DataColumn ccol1 = table2.Columns.Add ("id", typeof (int));
1046                                 DataColumn ccol2 = table2.Columns.Add ("type_int", typeof (int));
1047
1048                                 DataTable table1 = dataSet.Tables.Add ("table1");
1049                                 DataColumn pcol1 = table1.Columns.Add ("id", typeof (int));
1050                                 DataColumn pcol2 = table1.Columns.Add ("type_int", typeof (int));
1051
1052                                 table2.Constraints.Add ("fk", pcol1, ccol1);
1053                                 //table1.Constraints.Add ("fk1", pcol2, ccol2);
1054
1055                                 dataSet.EnforceConstraints = false;
1056                                 dataAdapter.Fill (dataSet, "table1");
1057                                 dataAdapter.Fill (dataSet, "table2");
1058
1059                                 //Should not throw an exception
1060                                 dataSet.EnforceConstraints = true;
1061
1062                                 Assert.AreEqual (2, table1.Rows.Count, "#1");
1063                                 Assert.AreEqual (2, table2.Rows.Count, "#2");
1064                         }
1065                 }
1066
1067                 [Test]
1068                 public void UpdateBatchSizeTest ()
1069                 {
1070                         adapter = new SqlDataAdapter();
1071                         Assert.AreEqual (1, adapter.UpdateBatchSize, "#1 The default value should be 1");
1072                         adapter.UpdateBatchSize = 3;
1073                         Assert.AreEqual (3, adapter.UpdateBatchSize, "#2 The value should be 3 after setting the property UpdateBatchSize to 3");
1074                 }
1075
1076                 [Test]
1077                 [ExpectedException (typeof (ArgumentOutOfRangeException))]
1078                 public void UpdateBatchSizeArgumentOutOfRangeTest ()
1079                 {
1080                         adapter = new SqlDataAdapter();
1081                         adapter.UpdateBatchSize = -2;
1082                 }
1083
1084                 int ClientVersion {
1085                         get {
1086                                 return (engine.ClientVersion);
1087                         }
1088                 }
1089         }
1090
1091         [TestFixture]
1092         [Category ("sqlserver")]
1093         public class SqlDataAdapterInheritTest : DbDataAdapter
1094         {
1095                 SqlConnection conn = null;
1096
1097                 [Test]
1098                 public void FillDataAdapterTest () {
1099                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
1100                         try {
1101                                 ConnectionManager.Singleton.OpenConnection ();
1102                                 DataTable dt = new DataTable();
1103                                 SqlCommand command = new SqlCommand ();
1104                                 command.CommandText = "Select * from employee;";
1105                                 command.Connection = conn;
1106                                 SelectCommand = command;
1107                                 Fill (dt, command.ExecuteReader ());
1108                                 Assert.AreEqual (4, dt.Rows.Count, "#1");
1109                                 Assert.AreEqual (6, dt.Columns.Count, "#2");
1110                         } finally {
1111                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
1112                                 ConnectionManager.Singleton.CloseConnection ();
1113                         }
1114                 }
1115         }
1116 }