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
  44         [TestFixture]
  45         [Category ("sqlserver")]
  46         public class SqlDataAdapterTest
  47         {
  48                 SqlDataAdapter adapter = null;
  49                 DataSet data = null ;
  50                 string  connectionString = ConnectionManager.Singleton.ConnectionString;
  51                 SqlConnection conn = null;
  52
  53                 [Test]
  54                 /**
  55                    The below test will not run everytime, since the region id column is unique
  56                    so change the regionid if you want the test to pass.
  57                 **/
  58                 [Category ("NotWorking")]
  59                 public void UpdateTest () {
  60                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
  61                         try {
  62                                 ConnectionManager.Singleton.OpenConnection ();
  63                                 DataTable dt = new DataTable();
  64                                 SqlDataAdapter da = null;
  65                                 da = new SqlDataAdapter("Select * from employee", conn);
  66                                 //SqlCommandBuilder cb = new SqlCommandBuilder (da);
  67                                 da.Fill(dt);
  68                                 DataRow dr = dt.NewRow();
  69                                 dr ["id"] = 6002;
  70                                 dr ["fname"] = "boston";
  71                                 dr ["dob"] = DateTime.Now.Subtract (new TimeSpan (20*365, 0, 0, 0));
  72                                 dr ["doj"] = DateTime.Now;
  73                                 dt.Rows.Add(dr);
  74
  75                                 da.Update(dt);
  76                         } finally {
  77                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
  78                                 ConnectionManager.Singleton.CloseConnection ();
  79                         }
  80                 }
  81
  82                 private static void OnRowUpdatedTest (object sender, SqlRowUpdatedEventArgs e)
  83                 {
  84                         rowUpdated = true;
  85                 }
  86
  87                 private static void OnRowUpdatingTest (object sender, SqlRowUpdatingEventArgs e)
  88                 {
  89                         rowUpdating = true;
  90                 }
  91
  92                 private static bool rowUpdated = false;
  93                 private static bool rowUpdating = false;
  94                 [Test]
  95                 [Category ("NotWorking")]
  96                 public void RowUpdatedTest () {
  97                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
  98                         try {
  99                                 ConnectionManager.Singleton.OpenConnection ();
 100                                 DataTable dt = null;
 101                                 DataSet ds = new DataSet ();
 102                                 SqlDataAdapter da = null;
 103                                 da = new SqlDataAdapter("Select * from employee", conn);
 104                                 //SqlCommandBuilder cb = new SqlCommandBuilder (da);
 105                                 rowUpdated = false;
 106                                 rowUpdating = false;
 107                                 da.RowUpdated += new SqlRowUpdatedEventHandler (OnRowUpdatedTest);
 108                                 da.RowUpdating += new SqlRowUpdatingEventHandler (OnRowUpdatingTest);
 109                                 da.Fill (ds);
 110                                 dt = ds.Tables [0];
 111                                 dt.Rows[0][0] = 200;
 112                                 da.UpdateCommand = new SqlCommand ("Update employee set id = @id");
 113                                 da.Update (dt);
 114                                 dt.Rows[0][0] = 1;
 115                                 da.Update (dt);
 116                                 da.RowUpdated -= new SqlRowUpdatedEventHandler (OnRowUpdatedTest);
 117                                 da.RowUpdating -= new SqlRowUpdatingEventHandler (OnRowUpdatingTest);
 118                                 Assert.AreEqual (true, rowUpdated, "RowUpdated");
 119                                 Assert.AreEqual (true, rowUpdating, "RowUpdating");
 120                         } finally {
 121                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
 122                                 ConnectionManager.Singleton.CloseConnection ();
 123                         }
 124                 }
 125
 126                 /**
 127                    This needs a errortable created as follows
 128                    id uniqueidentifier,name char(10) , with values
 129                    Guid         name
 130                    {A12...}     NULL
 131                    NULL         bbbbbb
 132                 **/
 133                 [Test]
 134                 public void NullGuidTest()
 135                 {
 136                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
 137                         try {
 138                                 ConnectionManager.Singleton.OpenConnection ();
 139                                 DBHelper.ExecuteNonQuery (conn, "create table #tmp_guid_table ( " +
 140                                                           " id uniqueidentifier default newid (), " +
 141                                                           " name char (10))");
 142                                 DBHelper.ExecuteNonQuery (conn, "insert into #tmp_guid_table (name) values (null)");
 143                                 DBHelper.ExecuteNonQuery (conn, "insert into #tmp_guid_table (id, name) values (null, 'bbbb')");
 144                                 SqlDataAdapter da = new SqlDataAdapter("select * from #tmp_guid_table", conn);
 145                                 DataSet ds = new DataSet();
 146                                 da.Fill(ds);
 147                                 Assert.AreEqual (1, ds.Tables.Count, "#1");
 148                                 Assert.AreEqual (DBNull.Value, ds.Tables [0].Rows [1] ["id"], "#2");
 149                         } finally {
 150                                 ConnectionManager.Singleton.CloseConnection ();
 151                         }
 152                         // the bug 68804 - is that the fill hangs!
 153                         Assert.AreEqual("Done","Done");
 154
 155                 }
 156
 157                 [Test]
 158                 public void DefaultConstructorTest ()
 159                 {
 160                         adapter = new SqlDataAdapter ();
 161                         Assert.AreEqual (MissingMappingAction.Passthrough,
 162                                 adapter.MissingMappingAction,
 163                                 "#1 Missing Mapping acttion default to Passthrough");
 164                         Assert.AreEqual (MissingSchemaAction.Add,
 165                                 adapter.MissingSchemaAction,
 166                                 "#2 Missing Schme action default to Add");
 167                 }
 168
 169                 [Test]
 170                 public void OverloadedConstructorsTest ()
 171                 {
 172                         SqlCommand selCmd = new SqlCommand ("Select * from numeric_family");
 173                         adapter = new SqlDataAdapter (selCmd);
 174                         Assert.AreEqual (MissingMappingAction.Passthrough,
 175                                 adapter.MissingMappingAction,
 176                                 "#1 Missing Mapping acttion default to Passthrough");
 177                         Assert.AreEqual (MissingSchemaAction.Add,
 178                                 adapter.MissingSchemaAction,
 179                                 "#2 Missing Schme action default to Add");
 180                         Assert.AreSame (selCmd, adapter.SelectCommand,
 181                                 "#3 Select Command shud be a ref to the arg passed");
 182
 183                         conn = new SqlConnection (connectionString);
 184                         String selStr = "Select * from numeric_family";
 185                         adapter = new SqlDataAdapter (selStr, conn);
 186                         Assert.AreEqual (MissingMappingAction.Passthrough,
 187                                 adapter.MissingMappingAction,
 188                                 "#4 Missing Mapping acttion default to Passthrough");
 189                         Assert.AreEqual (MissingSchemaAction.Add,
 190                                 adapter.MissingSchemaAction,
 191                                 "#5 Missing Schme action default to Add");
 192                         Assert.AreSame (selStr, adapter.SelectCommand.CommandText,
 193                                 "#6 Select Command shud be a ref to the arg passed");
 194                         Assert.AreSame (conn, adapter.SelectCommand.Connection,
 195                                 "#7 cmd.connection shud be t ref to connection obj");
 196
 197                         selStr = "Select * from numeric_family";
 198                         adapter = new SqlDataAdapter (selStr, connectionString);
 199                         Assert.AreEqual (MissingMappingAction.Passthrough,
 200                                 adapter.MissingMappingAction,
 201                                 "#8 Missing Mapping action shud default to Passthrough");
 202                         Assert.AreEqual (MissingSchemaAction.Add,
 203                                 adapter.MissingSchemaAction,
 204                                 "#9 Missing Schema action shud default to Add");
 205                         Assert.AreSame (selStr,
 206                                 adapter.SelectCommand.CommandText,
 207                                 "#10");
 208                         Assert.AreEqual (connectionString,
 209                                 adapter.SelectCommand.Connection.ConnectionString,
 210                                 "#11  ");
 211                 }
 212                 [Test]
 213                 public void Fill_Test_ConnState ()
 214                 {
 215                         //Check if Connection State is maintained correctly ..
 216                         data = new DataSet ("test1");
 217                         adapter = new SqlDataAdapter ("select id from numeric_family where id=1",
 218                                          connectionString);
 219                         SqlCommand cmd = adapter.SelectCommand ;
 220
 221                         Assert.AreEqual (ConnectionState.Closed,
 222                                 cmd.Connection.State, "#1 Connection shud be in closed state");
 223                         adapter.Fill (data);
 224                         Assert.AreEqual (1, data.Tables.Count, "#2 One table shud be populated");
 225                         Assert.AreEqual (ConnectionState.Closed, cmd.Connection.State,
 226                                 "#3 Connection shud be closed state");
 227
 228                         data = new DataSet ("test2");
 229                         cmd.Connection.Open ();
 230                         Assert.AreEqual (ConnectionState.Open, cmd.Connection.State,
 231                                 "#3 Connection shud be open");
 232                         adapter.Fill (data);
 233                         Assert.AreEqual (1, data.Tables.Count, "#4 One table shud be populated");
 234                         Assert.AreEqual (ConnectionState.Open, cmd.Connection.State,
 235                                 "#5 Connection shud be open");
 236                         cmd.Connection.Close ();
 237
 238                         // Test if connection is closed when exception occurs
 239                         cmd.CommandText = "select id1 from numeric_family";
 240                         try {
 241                                 adapter.Fill (data);
 242                         } catch {
 243                                 if (cmd.Connection.State == ConnectionState.Open) {
 244                                         cmd.Connection.Close ();
 245                                         Assert.Fail ("# Connection Shud be Closed");
 246                                 }
 247                         }
 248                 }
 249
 250                 [Test]
 251                 public void Fill_Test_Data ()
 252                 {
 253                         //Check if a table is created for each resultset
 254                         String batchQuery = "Select id,type_bit,type_int from numeric_family;";
 255                         batchQuery += "Select type_bit,type_bigint from numeric_family";
 256                         adapter = new SqlDataAdapter (batchQuery, connectionString);
 257                         data = new DataSet ("test1");
 258                         adapter.Fill (data);
 259                         Assert.AreEqual (2, data.Tables.Count,"#1 2 Table shud be created");
 260
 261                         //Check if Table and Col are named correctly for unnamed columns
 262                         string query = "Select 10,20 from numeric_family;" ;
 263                         query += "Select 10,20 from numeric_family";
 264                         adapter = new SqlDataAdapter (query, connectionString);
 265                         data = new DataSet ("test2");
 266                         adapter.Fill (data);
 267                         Assert.AreEqual (2, data.Tables.Count,
 268                                 "#2 2 Tables shud be created");
 269                         Assert.AreEqual ("Table", data.Tables[0].TableName, "#3");
 270                         Assert.AreEqual ("Table1", data.Tables[1].TableName, "#4");
 271                         Assert.AreEqual ("Column1", data.Tables[0].Columns[0].ColumnName, "#5");
 272                         Assert.AreEqual ("Column2", data.Tables[0].Columns[1].ColumnName, "#6");
 273                         Assert.AreEqual ("Column1", data.Tables[1].Columns[0].ColumnName, "#7");
 274                         Assert.AreEqual ("Column2", data.Tables[1].Columns[1].ColumnName, "#8");
 275
 276                         //Check if dup columns are named correctly
 277                         query = "select A.id ,B.id , C.id from numeric_family A, ";
 278                         query += "numeric_family B , numeric_family C";
 279                         adapter = new SqlDataAdapter (query, connectionString);
 280                         data = new DataSet ("test3");
 281                         adapter.Fill (data);
 282
 283                         // NOTE msdotnet contradicts documented behavior
 284                         // as per documentation the column names should be
 285                         // id1,id2,id3 .. but msdotnet returns id,id1,id2
 286                         Assert.AreEqual ("id", data.Tables[0].Columns[0].ColumnName,
 287                                 "#9 if colname is duplicated ,shud be col,col1,col2 etc");
 288                         Assert.AreEqual ("id1", data.Tables[0].Columns[1].ColumnName,
 289                                 "#10 if colname is duplicated ,shud be col,col1,col2 etc");
 290                         Assert.AreEqual ("id2", data.Tables[0].Columns[2].ColumnName,
 291                                 "#11 if colname is duplicated ,shud be col,col1,col2 etc");
 292
 293                         // Test if tables are created and named accordingly ,
 294                         // but only for those queries returning result sets
 295                         query = "update numeric_family set id=100 where id=50;";
 296                         query += "select * from numeric_family";
 297                         adapter = new SqlDataAdapter (query, connectionString);
 298                         data = new DataSet ("test4");
 299                         adapter.Fill (data);
 300                         Assert.AreEqual (1 ,data.Tables.Count,
 301                                 "#12 Tables shud be named only for queries returning a resultset");
 302                         Assert.AreEqual ("Table", data.Tables[0].TableName,
 303                                 "#13 The first resutlset shud have 'Table' as its name");
 304
 305                         // Test behavior with an outerjoin
 306                         query = "select A.id,B.type_bit from numeric_family A LEFT OUTER JOIN ";
 307                         query += "numeric_family B on A.id = B.type_bit";
 308                         adapter = new SqlDataAdapter (query, connectionString);
 309                         data = new DataSet ("test5");
 310                         adapter.Fill (data);
 311                         Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length,
 312                                 "#14 Primary Key shudnt be set if an outer join is performed");
 313                         Assert.AreEqual (0, data.Tables[0].Constraints.Count,
 314                                 "#15 Constraints shudnt be set if an outer join is performed");
 315                         adapter = new SqlDataAdapter ("select id from numeric_family",
 316                                         connectionString);
 317                         data = new DataSet ("test6");
 318                         adapter.Fill (data, 1, 1, "numeric_family");
 319                         Assert.AreEqual (1, data.Tables[0].Rows.Count, "#16");
 320                         Assert.AreEqual (2, data.Tables[0].Rows[0][0], "#17");
 321
 322                         // only one test for DataTable.. DataSet tests covers others
 323                         adapter = new SqlDataAdapter ("select id from numeric_family",
 324                                         connectionString);
 325                         DataTable table = new DataTable ("table1");
 326                         adapter.Fill (table);
 327                         Assert.AreEqual (4, table.Rows.Count , "#18");
 328                 }
 329
 330                 [Test]
 331                 public void Fill_Test_PriKey ()
 332                 {
 333                         // Test if Primary Key & Constraints Collection is correct
 334                         adapter = new SqlDataAdapter ("select id,type_bit from numeric_family",
 335                                         connectionString);
 336                         adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 337                         data = new DataSet ("test1");
 338                         adapter.Fill (data);
 339                         Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length,
 340                                 "#1 Primary Key shud be set");
 341                         Assert.AreEqual (1, data.Tables[0].Constraints.Count,
 342                                 "#2 Constraints shud be set");
 343                         Assert.AreEqual (4, data.Tables[0].Rows.Count,
 344                                 "#3 No Of Rows shud be 4");
 345
 346                         // Test if data is correctly merged
 347                         adapter.Fill (data);
 348                         Assert.AreEqual (4, data.Tables[0].Rows.Count,
 349                                 "#4 No of Row shud still be 4");
 350
 351                         // Test if rows are appended  and not merged
 352                         // when primary key is not returned in the result-set
 353                         string query = "Select type_int,type_bigint from numeric_family";
 354                         adapter.SelectCommand.CommandText = query;
 355                         data = new DataSet ("test2");
 356                         adapter.Fill (data);
 357                         Assert.AreEqual (4, data.Tables[0].Rows.Count,
 358                                 "#5 No of Rows shud be 4");
 359                         adapter.Fill (data);
 360                         Assert.AreEqual (8, data.Tables[0].Rows.Count,
 361                                 "#6 No of Rows shud double now");
 362                 }
 363
 364                 [Test]
 365                 public void Fill_Test_Exceptions ()
 366                 {
 367                         adapter = new SqlDataAdapter ("select * from numeric_family",
 368                                         connectionString);
 369                         data = new DataSet ("test1");
 370                         try {
 371                                 adapter.Fill (data, -1, 0, "numeric_family");
 372                                 Assert.Fail ("#1 Exception shud be thrown:Incorrect Arguments");
 373                         }catch (AssertionException e){
 374                                 throw e;
 375                         }catch (Exception e){
 376                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 377                                         "#2 Incorrect Exception : "  + e);
 378                         }
 379
 380                         // conn is not closed due to a bug..
 381                         // can be removed later
 382                         adapter.SelectCommand.Connection.Close ();
 383
 384                         try {
 385                                 adapter.Fill (data , 0 , -1 , "numeric_family");
 386                                 Assert.Fail ("#3 Exception shud be thrown:Incorrect Arguments");
 387                         }catch (AssertionException e){
 388                                 throw e;
 389                         }catch (Exception e){
 390                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 391                                         "#4 Incorrect Exception : "  + e);
 392                         }
 393                         // conn is curr not closed.. can be removed later
 394                         adapter.SelectCommand.Connection.Close ();
 395
 396                         /*
 397                         // NOTE msdotnet contradicts documented behavior
 398                         // InvalidOperationException is expected if table is not valid
 399                         try {
 400                                 adapter.Fill (data , 0 , 0 , "invalid_talbe_name");
 401                         }catch (InvalidOperationException e) {
 402                                 ex= e;
 403                         }catch (Exception e){
 404                                 Assert.Fail ("#5 Exception shud be thrown : incorrect arugments ");
 405                         }
 406                         Assert.IsNotNull (ex , "#6 Exception shud be thrown : incorrect args ");
 407                         adapter.SelectCommand.Connection.Close (); // tmp .. can be removed once the bug if fixed
 408                         ex=null;
 409                         */
 410
 411                         try {
 412                                 adapter.Fill ( null , 0 , 0 , "numeric_family");
 413                                 Assert.Fail ( "#7 Exception shud be thrown : Invalid Dataset");
 414                         }catch (AssertionException e){
 415                                 throw e ;
 416                         }catch (ArgumentNullException) {
 417
 418                         }catch (Exception e) {
 419                                 Assert.AreEqual (typeof(SystemException), e.GetType(),
 420                                         "#8 Incorrect Exception : " + e);
 421                         }
 422                         // conn is currently not being closed..
 423                         //need to be removed once behavior is fixed
 424                         adapter.SelectCommand.Connection.Close ();
 425
 426                         adapter.SelectCommand.Connection = null;
 427                         try {
 428                                 adapter.Fill (data);
 429                                 Assert.Fail ("#9 Exception shud be thrown : Invalid Connection");
 430                         }catch (AssertionException e){
 431                                 throw e;
 432                         }catch (Exception e){
 433                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 434                                         "#10 Incorrect Exception : " + e);
 435                         }
 436                 }
 437
 438                 bool FillErrorContinue = false;
 439                 [Test]
 440                 public void Fill_Test_FillErrorTest ()
 441                 {
 442                         string query = "select type_bigint from numeric_family where id=1 or id=4 ";
 443
 444                         DataSet ds = new DataSet ();
 445                         DataTable table = ds.Tables.Add ("test");
 446                         table.Columns.Add ("col", typeof (int));
 447
 448                         adapter = new SqlDataAdapter (query, connectionString);
 449                         DataTableMapping mapping = adapter.TableMappings.Add ("numeric_family", "test");
 450                         mapping.ColumnMappings.Add ("type_bigint", "col");
 451
 452                         int count = 0;
 453                         try {
 454                                 count = adapter.Fill (ds, "numeric_family");
 455                                 Assert.Fail ("#1 Overflow exception must be thrown");
 456                         }catch (Exception e) {
 457                                 Assert.AreEqual (typeof (OverflowException), e.GetType (), "#1a Expected exception is OverflowException");
 458                         }
 459                         Assert.AreEqual (0, ds.Tables [0].Rows.Count, "#2");
 460                         Assert.AreEqual (0, count, "#3");
 461
 462                         adapter.FillError += new FillErrorEventHandler (ErrorHandler);
 463                         FillErrorContinue = false;
 464                         try {
 465                                 count = adapter.Fill (ds, "numeric_family");
 466                                 Assert.Fail ("#4 Overflow exception must be thrown");
 467                         }catch (OverflowException e) {
 468                         }
 469                         Assert.AreEqual (0, ds.Tables [0].Rows.Count, "#5");
 470                         Assert.AreEqual (0, count, "#6");
 471
 472                         FillErrorContinue = true;
 473                         count = adapter.Fill (ds, "numeric_family");
 474                         // 1 row shud be filled
 475                         Assert.AreEqual (1, ds.Tables [0].Rows.Count, "#7");
 476                         Assert.AreEqual (1, count, "#8");
 477                 }
 478
 479                 void ErrorHandler (object sender, FillErrorEventArgs args)
 480                 {
 481                         args.Continue = FillErrorContinue;
 482                 }
 483
 484                 [Test]
 485                 public void GetFillParametersTest ()
 486                 {
 487                         string query = "select id, type_bit from numeric_family where id > @param1";
 488                         adapter = new SqlDataAdapter (query, connectionString);
 489                         IDataParameter[] param = adapter.GetFillParameters ();
 490                         Assert.AreEqual (0, param.Length, "#1 size shud be 0");
 491
 492                         SqlParameter param1 = new SqlParameter ();
 493                         param1.ParameterName = "@param1";
 494                         param1.Value = 2;
 495                         adapter.SelectCommand.Parameters.Add (param1);
 496
 497                         param = adapter.GetFillParameters ();
 498                         Assert.AreEqual (1, param.Length, "#2 count shud be 1");
 499                         Assert.AreEqual (param1, param[0], "#3 Params shud be equal");
 500                 }
 501
 502                 [Test]
 503                 public void FillSchemaTest ()
 504                 {
 505                         string query = "";
 506
 507                         // Test if connection is closed if excepton occurs during fill schema
 508                         query = "select * from invalid_table";
 509                         adapter = new SqlDataAdapter (query, connectionString);
 510                         data = new DataSet ("test");
 511                         try {
 512                                 adapter.FillSchema (data , SchemaType.Source);
 513                         }catch (Exception e){
 514                                 if ( adapter.SelectCommand.Connection.State != ConnectionState.Closed)
 515                                 {
 516                                         Assert.Fail ("#0 Conn shud be closed if exception occurs");
 517                                         adapter.SelectCommand.Connection.Close();
 518                                 }
 519                         }
 520
 521                         // Test Primary Key is set (since primary key column returned)
 522                         query = "select id, type_int from numeric_family where id=1";
 523                         adapter = new SqlDataAdapter (query, connectionString);
 524                         data = new DataSet ("test1");
 525                         adapter.FillSchema (data , SchemaType.Source);
 526
 527                         Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length,
 528                                 "#1 Primary Key property must be set");
 529
 530                         // Test Primary Key is not set (since primary key column is returned)
 531                         query = "select type_bit, type_int from numeric_family where id=1";
 532                         adapter = new SqlDataAdapter (query, connectionString);
 533                         data = new DataSet ("test2");
 534                         adapter.FillSchema (data, SchemaType.Source);
 535                         Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length,
 536                                 "#2 Primary Key property should not be set");
 537
 538                         // Test multiple tables are created for a batch query
 539                         query = "Select id ,type_bit from numeric_family;" ;
 540                         query += "Select id,type_bit,type_int from numeric_family;";
 541                         data = new DataSet ("test3");
 542                         adapter = new SqlDataAdapter (query, connectionString);
 543                         adapter.FillSchema (data , SchemaType.Source);
 544                         Assert.AreEqual (2 , data.Tables.Count , "#3 A table shud be created for each Result Set");
 545                         Assert.AreEqual (2 , data.Tables[0].Columns.Count , "#4 should have 2 columns");
 546                         Assert.AreEqual (3 , data.Tables[1].Columns.Count , "#5 Should have 3 columns");
 547
 548                         // Test if table names and column names  are filled correctly
 549                         query = "select 10,20 from numeric_family;" ;
 550                         query += "select 10,20 from numeric_family;";
 551                         adapter = new SqlDataAdapter (query, connectionString);
 552                         data = new DataSet ("test4");
 553                         try {
 554                                 adapter.FillSchema (data , SchemaType.Source);
 555                         }catch (Exception e){
 556                                 Assert.Fail ("#3 Unexpected Exception : " + e);
 557                         }
 558                         Assert.AreEqual ( "Table", data.Tables[0].TableName);
 559                         Assert.AreEqual ( "Table1", data.Tables[1].TableName);
 560                         Assert.AreEqual ( "Column1", data.Tables[0].Columns[0].ColumnName,
 561                                 "#6 Unnamed col shud be named as 'ColumnN'");
 562                         Assert.AreEqual ( "Column2", data.Tables[0].Columns[1].ColumnName,
 563                                 "#7 Unnamed col shud be named as 'ColumnN'");
 564                         Assert.AreEqual ( "Column1", data.Tables[1].Columns[0].ColumnName,
 565                                 "#8 Unnamed col shud be named as 'ColumnN'");
 566                         Assert.AreEqual ( "Column2", data.Tables[1].Columns[1].ColumnName,
 567                                 "#9 Unnamed col shud be named as 'ColumnN'");
 568                         Assert.AreEqual (ConnectionState.Closed, adapter.SelectCommand.Connection.State,
 569                                 "#10 Connection shud be closed");
 570
 571                         // Test if mapping works correctly
 572                         // doesent work in both mono and msdotnet
 573                         // gotto check if something is wrong
 574                         /*
 575                         query = "select id,type_bit from numeric_family";
 576                         adapter = new SqlDataAdapter (query, connectionString);
 577                         data = new DataSet ("test");
 578                         DataTable table = data.Tables.Add ("numeric_family_1");
 579                         table.Columns.Add ("id");
 580                         table.Columns.Add ("type_bit");
 581                         DataTableMapping map = adapter.TableMappings.Add("numeric_family_1",
 582                                                         "numeric_family");
 583                         map.ColumnMappings.Add ("id", "id_1");
 584                         map.ColumnMappings.Add ("type_bit", "type_bit_1");
 585                         adapter.FillSchema (data, SchemaType.Source, "numeric_family");
 586                         foreach (DataTable tab in data.Tables){
 587                                 Console.WriteLine ("Table == {0}",tab.TableName);
 588                                 foreach (DataColumn col in tab.Columns)
 589                                         Console.WriteLine (" Col = {0} " , col.ColumnName);
 590                         }
 591                         */
 592                 }
 593
 594                 [Test]
 595                 public void MissingSchemaActionTest ()
 596                 {
 597                         adapter = new SqlDataAdapter (
 598                                         "select id,type_bit,type_int from numeric_family where id<=4",
 599                                          connectionString);
 600                         data = new DataSet ();
 601                         Assert.AreEqual (MissingSchemaAction.Add, adapter.MissingSchemaAction,
 602                                          "#1 Default Value");
 603
 604                         adapter.Fill (data);
 605                         Assert.AreEqual (1, data.Tables.Count , "#1 One table shud be populated");
 606                         Assert.AreEqual (3, data.Tables[0].Columns.Count, "#2 Missing cols are added");
 607                         Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length, "#3 Default Value");
 608
 609                         adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 610                         data.Reset();
 611                         adapter.Fill (data);
 612                         Assert.AreEqual (3, data.Tables[0].Columns.Count,
 613                                 "#4 Missing cols are added");
 614                         Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length, "#5 Default Value");
 615
 616                         adapter.MissingSchemaAction = MissingSchemaAction.Ignore ;
 617                         data.Reset ();
 618                         adapter.Fill (data);
 619                         Assert.AreEqual (0, data.Tables.Count, "#6 Data shud be ignored");
 620
 621                         adapter.MissingSchemaAction = MissingSchemaAction.Error ;
 622                         data.Reset();
 623                         try {
 624                                 adapter.Fill (data);
 625                                 Assert.Fail ("#8 Exception shud be thrown: Schema Mismatch");
 626                         }catch (AssertionException e) {
 627                                 throw e;
 628                         }catch (Exception e){
 629                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 630                                         "#9 Incorrect Exception : "+e);
 631                         }
 632
 633                         // Test for invalid MissingSchema Value
 634                         try {
 635                                 adapter.MissingSchemaAction = (MissingSchemaAction)(-5000);
 636                                 Assert.Fail ("#10 Exception shud be thrown: Invalid Value");
 637                         }catch (AssertionException e){
 638                                 throw e;
 639                         }catch (Exception e){
 640                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 641                                         "#11 Incorrect Exception : " +e);
 642                         }
 643
 644                         // Tests if Data is filled correctly if schema is defined
 645                         // manually and MissingSchemaAction.Error is set
 646                         adapter.MissingSchemaAction = MissingSchemaAction.Error;
 647                         data.Reset();
 648                         DataTable table = data.Tables.Add ("Table");
 649                         table.Columns.Add ("id");
 650                         table.Columns.Add ("type_bit");
 651                         table.Columns.Add ("type_int");
 652                         try {
 653                                 adapter.Fill (data);
 654                                 Assert.AreEqual (1, data.Tables.Count, "#12");
 655                                 Assert.AreEqual (4, data.Tables[0].Rows.Count, "#13");
 656                         }catch (Exception e) {
 657                                 Assert.Fail ("#12 Unexpected Exception : " + e);
 658                         }
 659                 }
 660
 661                 [Test]
 662                 public void MissingMappingActionTest ()
 663                 {
 664                         adapter = new SqlDataAdapter ("select id,type_bit from numeric_family where id=1",
 665                                         connectionString);
 666                         data = new DataSet ();
 667                         Assert.AreEqual (adapter.MissingMappingAction,
 668                                 MissingMappingAction.Passthrough,
 669                                 "#1 Default Value");
 670                         adapter.Fill(data);
 671                         Assert.AreEqual (1, data.Tables.Count,
 672                                 "#2 One Table shud be created");
 673                         Assert.AreEqual (2, data.Tables[0].Columns.Count,
 674                                 "#3 Two Cols shud be created");
 675
 676                         adapter.MissingMappingAction = MissingMappingAction.Ignore;
 677                         data.Reset ();
 678                         adapter.Fill (data);
 679                         Assert.AreEqual (0, data.Tables.Count, "#4 No table shud be created");
 680
 681                         adapter.MissingMappingAction = MissingMappingAction.Error;
 682                         data.Reset ();
 683                         try {
 684                                 adapter.Fill (data);
 685                                 Assert.Fail ("#5 Exception shud be thrown : Mapping is missing");
 686                         }catch (AssertionException e){
 687                                 throw e;
 688                         }catch (Exception e) {
 689                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 690                                         "#6 Incorrect Exception : " + e);
 691                         }
 692
 693                         try {
 694                                 adapter.MissingMappingAction = (MissingMappingAction)(-5000);
 695                                 Assert.Fail ("#7 Exception shud be thrown : Invalid Value");
 696                         }catch (AssertionException e){
 697                                 throw e;
 698                         }catch (Exception e){
 699                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 700                                         "#8 Incorrect Exception : " +e);
 701                         }
 702
 703                         // Test if mapping the column and table names works correctly
 704                         adapter.MissingMappingAction = MissingMappingAction.Error;
 705                         data.Reset ();
 706                         DataTable table = data.Tables.Add ("numeric_family_1");
 707                         table.Columns.Add ("id_1");
 708                         table.Columns.Add ("type_bit_1");
 709                         table.Columns.Add ("type_int_1");
 710                         DataTableMapping tableMap = adapter.TableMappings.Add ("numeric_family",
 711                                                          "numeric_family_1");
 712                         tableMap.ColumnMappings.Add ("id", "id_1");
 713                         tableMap.ColumnMappings.Add ("type_bit", "type_bit_1");
 714                         tableMap.ColumnMappings.Add ("type_int", "type_int_1");
 715                         adapter.Fill (data,"numeric_family");
 716                         Assert.AreEqual (1, data.Tables.Count ,
 717                                 "#8 The DataTable shud be correctly mapped");
 718                         Assert.AreEqual (3, data.Tables[0].Columns.Count,
 719                                 "#9 The DataColumns shud be corectly mapped");
 720                         Assert.AreEqual (1, data.Tables[0].Rows.Count,
 721                                 "#10 Data shud be populated if mapping is correct");
 722                 }
 723
 724                 // Test case for bug #76433
 725                 [Test]
 726                 public void FillSchema_ValuesTest()
 727                 {
 728                         SqlConnection conn = new SqlConnection(connectionString);
 729                         using (conn) {
 730                                 conn.Open();
 731                                 IDbCommand command = conn.CreateCommand();
 732
 733                                 // Create Temp Table
 734                                 String cmd = "Create Table #tmp_TestTable (" ;
 735                                 cmd += "Field1 DECIMAL (10) NOT NULL,";
 736                                 cmd += "Field2 DECIMAL(19))";
 737                                 command.CommandText = cmd;
 738                                 command.ExecuteNonQuery();
 739
 740                                 DataSet dataSet = new DataSet();
 741                                 string selectString = "SELECT * FROM #tmp_TestTable";
 742                                 IDbDataAdapter dataAdapter = new SqlDataAdapter (
 743                                                                         selectString,conn);
 744                                 dataAdapter.FillSchema(dataSet, SchemaType.Mapped);
 745
 746                                 Assert.AreEqual (1, dataSet.Tables.Count, "#1");
 747
 748                                 DataColumn col = dataSet.Tables[0].Columns[0];
 749                                 Assert.IsFalse (dataSet.Tables[0].Columns[0].AllowDBNull,"#2");
 750                                 Assert.IsTrue (dataSet.Tables[0].Columns[1].AllowDBNull,"#3");
 751                         }
 752                 }
 753
 754                 [Test]
 755                 public void Fill_CheckSchema ()
 756                 {
 757                         SqlConnection conn = new SqlConnection(connectionString);
 758                         using (conn) {
 759                                 conn.Open();
 760
 761                                 IDbCommand command = conn.CreateCommand();
 762
 763                                 // Create Temp Table
 764                                 String cmd = "Create Table #tmp_TestTable (" ;
 765                                 cmd += "id int primary key,";
 766                                 cmd += "field int not null)";
 767                                 command.CommandText = cmd;
 768                                 command.ExecuteNonQuery();
 769
 770                                 DataSet dataSet = new DataSet();
 771                                 string selectString = "SELECT * from #tmp_TestTable";
 772                                 IDbDataAdapter dataAdapter = new SqlDataAdapter (
 773                                                                         selectString,conn);
 774                                 dataAdapter.Fill (dataSet);
 775                                 Assert.IsTrue (dataSet.Tables[0].Columns[1].AllowDBNull, "#1");
 776                                 Assert.AreEqual (0, dataSet.Tables[0].PrimaryKey.Length, "#2");
 777
 778                                 dataSet.Reset ();
 779                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey ;
 780                                 dataAdapter.Fill (dataSet);
 781                                 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#3");
 782                                 Assert.AreEqual (1, dataSet.Tables[0].PrimaryKey.Length, "#4");
 783                         }
 784                 }
 785
 786                 [Test]
 787                 public void FillSchema_CheckSchema ()
 788                 {
 789                         SqlConnection conn = new SqlConnection(connectionString);
 790                         using (conn) {
 791                                 conn.Open();
 792
 793                                 IDbCommand command = conn.CreateCommand();
 794
 795                                 // Create Temp Table
 796                                 String cmd = "Create Table #tmp_TestTable (" ;
 797                                 cmd += "id int primary key,";
 798                                 cmd += "field int not null)";
 799                                 command.CommandText = cmd;
 800                                 command.ExecuteNonQuery();
 801
 802                                 DataSet dataSet = new DataSet();
 803                                 string selectString = "SELECT * from #tmp_TestTable";
 804                                 IDbDataAdapter dataAdapter = new SqlDataAdapter (
 805                                                                         selectString,conn);
 806
 807                                 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
 808                                 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#1");
 809
 810                                 dataSet.Reset ();
 811                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.Add;
 812                                 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
 813                                 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#2");
 814
 815                                 dataSet.Reset ();
 816                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.Ignore;
 817                                 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
 818                                 Assert.AreEqual (0, dataSet.Tables.Count, "#3");
 819
 820                                 dataSet.Reset ();
 821                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.Error;
 822                                 try {
 823                                         dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
 824                                         Assert.Fail ("#4 Error should be thrown");
 825                                 } catch (InvalidOperationException e) {
 826                                 }
 827                         }
 828                 }
 829
 830                 [Test]
 831                 public void CreateViewSSPITest ()
 832                 {
 833                         SqlConnection conn = new SqlConnection (ConfigurationSettings.AppSettings ["SSPIConnString"]);
 834                         conn.Open ();
 835
 836                         string sql = "create view MONO_TEST_VIEW as select * from Numeric_family";
 837
 838                         SqlCommand dbcmd = new SqlCommand( sql, conn );
 839                         dbcmd.ExecuteNonQuery();
 840
 841                         sql = "drop view MONO_TEST_VIEW";
 842
 843                         dbcmd = new SqlCommand( sql, conn );
 844                         dbcmd.ExecuteNonQuery();
 845
 846                         conn.Close();
 847                 }
 848
 849                 [Test]
 850                 public void Fill_RelatedTables ()
 851                 {
 852                         SqlConnection conn = new SqlConnection(connectionString);
 853                         using (conn) {
 854                                 conn.Open();
 855                                 IDbCommand command = conn.CreateCommand();
 856
 857                                 DataSet dataSet = new DataSet();
 858                                 string selectString = "SELECT id, type_int from numeric_family where id < 3";
 859                                 DbDataAdapter dataAdapter = new SqlDataAdapter (selectString,conn);
 860
 861                                 DataTable table2 = dataSet.Tables.Add ("table2");
 862                                 DataColumn ccol1 = table2.Columns.Add ("id", typeof (int));
 863                                 DataColumn ccol2 = table2.Columns.Add ("type_int", typeof (int));
 864
 865                                 DataTable table1 = dataSet.Tables.Add ("table1");
 866                                 DataColumn pcol1 = table1.Columns.Add ("id", typeof (int));
 867                                 DataColumn pcol2 = table1.Columns.Add ("type_int", typeof (int));
 868
 869                                 table2.Constraints.Add ("fk", pcol1, ccol1);
 870
 871                                 dataSet.EnforceConstraints = false;
 872                                 dataAdapter.Fill (dataSet, "table1");
 873                                 dataAdapter.Fill (dataSet, "table2");
 874
 875                                 //Should not throw an exception
 876                                 dataSet.EnforceConstraints = true;
 877
 878                                 Assert.AreEqual (2, table1.Rows.Count, "#1");
 879                                 Assert.AreEqual (2, table2.Rows.Count, "#2");
 880                         }
 881                 }
 882         }
 883
 884 #if NET_2_0
 885         [TestFixture]
 886         [Category ("sqlserver")]
 887         public class SqlDataAdapterInheritTest : DbDataAdapter
 888         {
 889                 SqlConnection conn = null;
 890
 891                 [Test]
 892                 public void FillDataAdapterTest () {
 893                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
 894                         try {
 895                                 ConnectionManager.Singleton.OpenConnection ();
 896                                 DataTable dt = new DataTable();
 897                                 SqlCommand command = new SqlCommand ();
 898                                 command.CommandText = "Select * from employee;";
 899                                 command.Connection = conn;
 900                                 SelectCommand = command;
 901                                 Fill (dt, command.ExecuteReader ());
 902                                 Assert.AreEqual (4, dt.Rows.Count, "#1");
 903                                 Assert.AreEqual (6, dt.Columns.Count, "#1");
 904                         } finally {
 905                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
 906                                 ConnectionManager.Singleton.CloseConnection ();
 907                         }
 908                 }
 909         }
 910 #endif
 911 }