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
  37 using NUnit.Framework;
  38
  39 namespace MonoTests.System.Data.SqlClient
  40 {
  41
  42         [TestFixture]
  43         [Category ("sqlserver")]
  44         public class SqlDataAdapterTest
  45         {
  46                 SqlDataAdapter adapter =null;
  47                 DataSet data = null ;
  48                 string  connectionString = ConnectionManager.Singleton.ConnectionString;
  49                 SqlConnection conn = null;
  50
  51                 [Test]
  52                 /**
  53                    The below test will not run everytime, since the region id column is unique
  54                    so change the regionid if you want the test to pass.
  55                 **/
  56                 public void UpdateTest () {
  57                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
  58                         try {
  59                                 ConnectionManager.Singleton.OpenConnection ();
  60                                 DataTable dt = new DataTable();
  61                                 SqlDataAdapter da = null;
  62                                 da = new SqlDataAdapter("Select * from employee;", conn);
  63                                 SqlCommandBuilder cb = new SqlCommandBuilder (da);
  64                                 da.Fill(dt);
  65                                 DataRow dr = dt.NewRow();
  66                                 dr ["id"] = 6002;
  67                                 dr ["fname"] = "boston";
  68                                 dr ["dob"] = DateTime.Now.Subtract (new TimeSpan (20*365, 0, 0, 0));
  69                                 dr ["doj"] = DateTime.Now;
  70                                 dt.Rows.Add(dr);
  71
  72                                 da.Update(dt);
  73                         } finally {
  74                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
  75                                 ConnectionManager.Singleton.CloseConnection ();
  76                         }
  77                 }
  78
  79                 /**
  80                    This needs a errortable created as follows
  81                    id uniqueidentifier,name char(10) , with values
  82                    Guid         name
  83                    {A12...}     NULL
  84                    NULL         bbbbbb
  85                 **/
  86                 [Test]
  87                 public void NullGuidTest()
  88                 {
  89                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
  90                         try {
  91                                 ConnectionManager.Singleton.OpenConnection ();
  92                                 DBHelper.ExecuteNonQuery (conn, "create table #tmp_guid_table ( " +
  93                                                           " id uniqueidentifier default newid (), " +
  94                                                           " name char (10))");
  95                                 DBHelper.ExecuteNonQuery (conn, "insert into #tmp_guid_table (name) values (null)");
  96                                 DBHelper.ExecuteNonQuery (conn, "insert into #tmp_guid_table (id, name) values (null, 'bbbb')");
  97                                 SqlDataAdapter da = new SqlDataAdapter("select * from #tmp_guid_table", conn);
  98                                 DataSet ds = new DataSet();
  99                                 da.Fill(ds);
 100                                 Assert.AreEqual (1, ds.Tables.Count, "#1");
 101                                 Assert.AreEqual (DBNull.Value, ds.Tables [0].Rows [1] ["id"], "#2");
 102                         } finally {
 103                                 ConnectionManager.Singleton.CloseConnection ();
 104                         }
 105                         // the bug 68804 - is that the fill hangs!
 106                         Assert.AreEqual("Done","Done");
 107
 108                 }
 109
 110                 [Test]
 111                 public void DefaultConstructorTest ()
 112                 {
 113                         adapter = new SqlDataAdapter ();
 114                         Assert.AreEqual (MissingMappingAction.Passthrough,
 115                                 adapter.MissingMappingAction,
 116                                 "#1 Missing Mapping acttion default to Passthrough");
 117                         Assert.AreEqual (MissingSchemaAction.Add,
 118                                 adapter.MissingSchemaAction,
 119                                 "#2 Missing Schme action default to Add");
 120                 }
 121
 122                 [Test]
 123                 public void OverloadedConstructorsTest ()
 124                 {
 125                         SqlCommand selCmd = new SqlCommand ("Select * from numeric_family");
 126                         adapter = new SqlDataAdapter (selCmd);
 127                         Assert.AreEqual (MissingMappingAction.Passthrough,
 128                                 adapter.MissingMappingAction,
 129                                 "#1 Missing Mapping acttion default to Passthrough");
 130                         Assert.AreEqual (MissingSchemaAction.Add,
 131                                 adapter.MissingSchemaAction,
 132                                 "#2 Missing Schme action default to Add");
 133                         Assert.AreSame (selCmd, adapter.SelectCommand,
 134                                 "#3 Select Command shud be a ref to the arg passed");
 135
 136                         conn = new SqlConnection (connectionString);
 137                         String selStr = "Select * from numeric_family";
 138                         adapter = new SqlDataAdapter (selStr, conn);
 139                         Assert.AreEqual (MissingMappingAction.Passthrough,
 140                                 adapter.MissingMappingAction,
 141                                 "#4 Missing Mapping acttion default to Passthrough");
 142                         Assert.AreEqual (MissingSchemaAction.Add,
 143                                 adapter.MissingSchemaAction,
 144                                 "#5 Missing Schme action default to Add");
 145                         Assert.AreSame (selStr, adapter.SelectCommand.CommandText,
 146                                 "#6 Select Command shud be a ref to the arg passed");
 147                         Assert.AreSame (conn, adapter.SelectCommand.Connection,
 148                                 "#7 cmd.connection shud be t ref to connection obj");
 149
 150                         selStr = "Select * from numeric_family";
 151                         adapter = new SqlDataAdapter (selStr, connectionString);
 152                         Assert.AreEqual (MissingMappingAction.Passthrough,
 153                                 adapter.MissingMappingAction,
 154                                 "#8 Missing Mapping action shud default to Passthrough");
 155                         Assert.AreEqual (MissingSchemaAction.Add,
 156                                 adapter.MissingSchemaAction,
 157                                 "#9 Missing Schema action shud default to Add");
 158                         Assert.AreSame (selStr,
 159                                 adapter.SelectCommand.CommandText,
 160                                 "#10");
 161                         Assert.AreEqual (connectionString,
 162                                 adapter.SelectCommand.Connection.ConnectionString,
 163                                 "#11  ");
 164                 }
 165                 [Test]
 166                 public void Fill_Test_ConnState ()
 167                 {
 168                         //Check if Connection State is maintained correctly ..
 169                         data = new DataSet ("test1");
 170                         adapter = new SqlDataAdapter ("select id from numeric_family where id=1",
 171                                          connectionString);
 172                         SqlCommand cmd = adapter.SelectCommand ;
 173
 174                         Assert.AreEqual (ConnectionState.Closed,
 175                                 cmd.Connection.State, "#1 Connection shud be in closed state");
 176                         adapter.Fill (data);
 177                         Assert.AreEqual (1, data.Tables.Count, "#2 One table shud be populated");
 178                         Assert.AreEqual (ConnectionState.Closed, cmd.Connection.State,
 179                                 "#3 Connection shud be closed state");
 180
 181                         data = new DataSet ("test2");
 182                         cmd.Connection.Open ();
 183                         Assert.AreEqual (ConnectionState.Open, cmd.Connection.State,
 184                                 "#3 Connection shud be open");
 185                         adapter.Fill (data);
 186                         Assert.AreEqual (1, data.Tables.Count, "#4 One table shud be populated");
 187                         Assert.AreEqual (ConnectionState.Open, cmd.Connection.State,
 188                                 "#5 Connection shud be open");
 189                         cmd.Connection.Close ();
 190
 191                         // Test if connection is closed when exception occurs
 192                         cmd.CommandText = "select id1 from numeric_family";
 193                         try {
 194                                 adapter.Fill (data);
 195                         }catch (Exception e) {
 196                                 if (cmd.Connection.State == ConnectionState.Open) {
 197                                         cmd.Connection.Close ();
 198                                         Assert.Fail ("# Connection Shud be Closed");
 199                                 }
 200                         }
 201                 }
 202
 203                 [Test]
 204                 public void Fill_Test_Data ()
 205                 {
 206                         //Check if a table is created for each resultset
 207                         String batchQuery = "Select id,type_bit,type_int from numeric_family;";
 208                         batchQuery += "Select type_bit,type_bigint from numeric_family";
 209                         adapter = new SqlDataAdapter (batchQuery, connectionString);
 210                         data = new DataSet ("test1");
 211                         adapter.Fill (data);
 212                         Assert.AreEqual (2, data.Tables.Count,"#1 2 Table shud be created");
 213
 214                         //Check if Table and Col are named correctly for unnamed columns
 215                         string query = "Select 10,20 from numeric_family;" ;
 216                         query += "Select 10,20 from numeric_family";
 217                         adapter = new SqlDataAdapter (query, connectionString);
 218                         data = new DataSet ("test2");
 219                         adapter.Fill (data);
 220                         Assert.AreEqual (2, data.Tables.Count,
 221                                 "#2 2 Tables shud be created");
 222                         Assert.AreEqual ("Table", data.Tables[0].TableName, "#3");
 223                         Assert.AreEqual ("Table1", data.Tables[1].TableName, "#4");
 224                         Assert.AreEqual ("Column1", data.Tables[0].Columns[0].ColumnName, "#5");
 225                         Assert.AreEqual ("Column2", data.Tables[0].Columns[1].ColumnName, "#6");
 226                         Assert.AreEqual ("Column1", data.Tables[1].Columns[0].ColumnName, "#7");
 227                         Assert.AreEqual ("Column2", data.Tables[1].Columns[1].ColumnName, "#8");
 228
 229                         //Check if dup columns are named correctly
 230                         query = "select A.id ,B.id , C.id from numeric_family A, ";
 231                         query += "numeric_family B , numeric_family C";
 232                         adapter = new SqlDataAdapter (query, connectionString);
 233                         data = new DataSet ("test3");
 234                         adapter.Fill (data);
 235
 236                         // NOTE msdotnet contradicts documented behavior
 237                         // as per documentation the column names should be
 238                         // id1,id2,id3 .. but msdotnet returns id,id1,id2
 239                         Assert.AreEqual ("id", data.Tables[0].Columns[0].ColumnName,
 240                                 "#9 if colname is duplicated ,shud be col,col1,col2 etc");
 241                         Assert.AreEqual ("id1", data.Tables[0].Columns[1].ColumnName,
 242                                 "#10 if colname is duplicated ,shud be col,col1,col2 etc");
 243                         Assert.AreEqual ("id2", data.Tables[0].Columns[2].ColumnName,
 244                                 "#11 if colname is duplicated ,shud be col,col1,col2 etc");
 245
 246                         // Test if tables are created and named accordingly ,
 247                         // but only for those queries returning result sets
 248                         query = "update numeric_family set id=100 where id=50;";
 249                         query += "select * from numeric_family";
 250                         adapter = new SqlDataAdapter (query, connectionString);
 251                         data = new DataSet ("test4");
 252                         adapter.Fill (data);
 253                         Assert.AreEqual (1 ,data.Tables.Count,
 254                                 "#12 Tables shud be named only for queries returning a resultset");
 255                         Assert.AreEqual ("Table", data.Tables[0].TableName,
 256                                 "#13 The first resutlset shud have 'Table' as its name");
 257
 258                         // Test behavior with an outerjoin
 259                         query = "select A.id,B.type_bit from numeric_family A LEFT OUTER JOIN ";
 260                         query += "numeric_family B on A.id = B.type_bit";
 261                         adapter = new SqlDataAdapter (query, connectionString);
 262                         data = new DataSet ("test5");
 263                         adapter.Fill (data);
 264                         Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length,
 265                                 "#14 Primary Key shudnt be set if an outer join is performed");
 266                         Assert.AreEqual (0, data.Tables[0].Constraints.Count,
 267                                 "#15 Constraints shudnt be set if an outer join is performed");
 268                         adapter = new SqlDataAdapter ("select id from numeric_family",
 269                                         connectionString);
 270                         data = new DataSet ("test6");
 271                         adapter.Fill (data, 1, 1, "numeric_family");
 272                         Assert.AreEqual (1, data.Tables[0].Rows.Count, "#16");
 273                         Assert.AreEqual (2, data.Tables[0].Rows[0][0], "#17");
 274
 275                         // only one test for DataTable.. DataSet tests covers others
 276                         adapter = new SqlDataAdapter ("select id from numeric_family",
 277                                         connectionString);
 278                         DataTable table = new DataTable ("table1");
 279                         adapter.Fill (table);
 280                         Assert.AreEqual (4, table.Rows.Count , "#18");
 281                 }
 282
 283                 [Test]
 284                 public void Fill_Test_PriKey ()
 285                 {
 286                         // Test if Primary Key & Constraints Collection is correct
 287                         adapter = new SqlDataAdapter ("select id,type_bit from numeric_family",
 288                                         connectionString);
 289                         adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 290                         data = new DataSet ("test1");
 291                         adapter.Fill (data);
 292                         Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length,
 293                                 "#1 Primary Key shud be set");
 294                         Assert.AreEqual (1, data.Tables[0].Constraints.Count,
 295                                 "#2 Constraints shud be set");
 296                         Assert.AreEqual (4, data.Tables[0].Rows.Count,
 297                                 "#3 No Of Rows shud be 4");
 298
 299                         // Test if data is correctly merged
 300                         adapter.Fill (data);
 301                         Assert.AreEqual (4, data.Tables[0].Rows.Count,
 302                                 "#4 No of Row shud still be 4");
 303
 304                         // Test if rows are appended  and not merged
 305                         // when primary key is not returned in the result-set
 306                         string query = "Select type_int,type_bigint from numeric_family";
 307                         adapter.SelectCommand.CommandText = query;
 308                         data = new DataSet ("test2");
 309                         adapter.Fill (data);
 310                         Assert.AreEqual (4, data.Tables[0].Rows.Count,
 311                                 "#5 No of Rows shud be 4");
 312                         adapter.Fill (data);
 313                         Assert.AreEqual (8, data.Tables[0].Rows.Count,
 314                                 "#6 No of Rows shud double now");
 315                 }
 316
 317                 [Test]
 318                 public void Fill_Test_Exceptions ()
 319                 {
 320                         adapter = new SqlDataAdapter ("select * from numeric_family",
 321                                         connectionString);
 322                         data = new DataSet ("test1");
 323                         try {
 324                                 adapter.Fill (data, -1, 0, "numeric_family");
 325                                 Assert.Fail ("#1 Exception shud be thrown:Incorrect Arguments");
 326                         }catch (AssertionException e){
 327                                 throw e;
 328                         }catch (Exception e){
 329                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 330                                         "#2 Incorrect Exception : "  + e);
 331                         }
 332
 333                         // conn is not closed due to a bug..
 334                         // can be removed later
 335                         adapter.SelectCommand.Connection.Close ();
 336
 337                         try {
 338                                 adapter.Fill (data , 0 , -1 , "numeric_family");
 339                                 Assert.Fail ("#3 Exception shud be thrown:Incorrect Arguments");
 340                         }catch (AssertionException e){
 341                                 throw e;
 342                         }catch (Exception e){
 343                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 344                                         "#4 Incorrect Exception : "  + e);
 345                         }
 346                         // conn is curr not closed.. can be removed later
 347                         adapter.SelectCommand.Connection.Close ();
 348
 349                         /*
 350                         // NOTE msdotnet contradicts documented behavior
 351                         // InvalidOperationException is expected if table is not valid
 352                         try {
 353                                 adapter.Fill (data , 0 , 0 , "invalid_talbe_name");
 354                         }catch (InvalidOperationException e) {
 355                                 ex= e;
 356                         }catch (Exception e){
 357                                 Assert.Fail ("#5 Exception shud be thrown : incorrect arugments ");
 358                         }
 359                         Assert.IsNotNull (ex , "#6 Exception shud be thrown : incorrect args ");
 360                         adapter.SelectCommand.Connection.Close (); // tmp .. can be removed once the bug if fixed
 361                         ex=null;
 362                         */
 363
 364                         // NOTE msdotnet contradicts documented behavior
 365                         // SystemException is expected when Dataset is invalid
 366                         // but msdotnet throws ArgumentNullException
 367                         try {
 368                                 adapter.Fill ( null , 0 , 0 , "numeric_family");
 369                                 Assert.Fail ( "#7 Exception shud be thrown : Invalid Dataset");
 370                         }catch (AssertionException e){
 371                                 throw e ;
 372                         }catch (ArgumentNullException) {
 373
 374                         }catch (Exception e) {
 375                                 Assert.AreEqual (typeof(SystemException), e.GetType(),
 376                                         "#8 Incorrect Exception : " + e);
 377                         }
 378                         // conn is currently not being closed..
 379                         //need to be removed once behavior is fixed
 380                         adapter.SelectCommand.Connection.Close ();
 381
 382                         adapter.SelectCommand.Connection = null;
 383                         try {
 384                                 adapter.Fill (data);
 385                                 Assert.Fail ("#9 Exception shud be thrown : Invalid Connection");
 386                         }catch (AssertionException e){
 387                                 throw e;
 388                         }catch (Exception e){
 389                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 390                                         "#10 Incorrect Exception : " + e);
 391                         }
 392                 }
 393
 394                 [Test]
 395                 public void GetFillParametersTest ()
 396                 {
 397                         string query = "select id, type_bit from numeric_family where id > @param1";
 398                         adapter = new SqlDataAdapter (query, connectionString);
 399                         IDataParameter[] param = adapter.GetFillParameters ();
 400                         Assert.AreEqual (0, param.Length, "#1 size shud be 0");
 401
 402                         SqlParameter param1 = new SqlParameter ();
 403                         param1.ParameterName = "@param1";
 404                         param1.Value = 2;
 405                         adapter.SelectCommand.Parameters.Add (param1);
 406
 407                         param = adapter.GetFillParameters ();
 408                         Assert.AreEqual (1, param.Length, "#2 count shud be 1");
 409                         Assert.AreEqual (param1, param[0], "#3 Params shud be equal");
 410                 }
 411
 412                 [Test]
 413                 public void FillSchemaTest ()
 414                 {
 415                         string query = "";
 416
 417                         // Test if connection is closed if excepton occurs during fill schema
 418                         query = "select * from invalid_table";
 419                         adapter = new SqlDataAdapter (query, connectionString);
 420                         data = new DataSet ("test");
 421                         try {
 422                                 adapter.FillSchema (data , SchemaType.Source);
 423                         }catch (Exception e){
 424                                 if ( adapter.SelectCommand.Connection.State != ConnectionState.Closed)
 425                                 {
 426                                         Assert.Fail ("#0 Conn shud be closed if exception occurs");
 427                                         adapter.SelectCommand.Connection.Close();
 428                                 }
 429                         }
 430
 431                         // Test Primary Key is set (since primary key column returned)
 432                         query = "select id, type_int from numeric_family where id=1";
 433                         adapter = new SqlDataAdapter (query, connectionString);
 434                         data = new DataSet ("test1");
 435                         adapter.FillSchema (data , SchemaType.Source);
 436
 437                         Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length,
 438                                 "#1 Primary Key property must be set");
 439
 440                         // Test Primary Key is not set (since primary key column is returned)
 441                         query = "select type_bit, type_int from numeric_family where id=1";
 442                         adapter = new SqlDataAdapter (query, connectionString);
 443                         data = new DataSet ("test2");
 444                         adapter.FillSchema (data, SchemaType.Source);
 445                         Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length,
 446                                 "#2 Primary Key property should not be set");
 447
 448                         // Test multiple tables are created for a batch query
 449                         query = "Select id ,type_bit from numeric_family;" ;
 450                         query += "Select id,type_bit,type_int from numeric_family;";
 451                         data = new DataSet ("test3");
 452                         adapter = new SqlDataAdapter (query, connectionString);
 453                         adapter.FillSchema (data , SchemaType.Source);
 454                         Assert.AreEqual (2 , data.Tables.Count , "#3 A table shud be created for each Result Set");
 455                         Assert.AreEqual (2 , data.Tables[0].Columns.Count , "#4 should have 2 columns");
 456                         Assert.AreEqual (3 , data.Tables[1].Columns.Count , "#5 Should have 3 columns");
 457
 458                         // Test if table names and column names  are filled correctly
 459                         query = "select 10,20 from numeric_family;" ;
 460                         query += "select 10,20 from numeric_family;";
 461                         adapter = new SqlDataAdapter (query, connectionString);
 462                         data = new DataSet ("test4");
 463                         try {
 464                                 adapter.FillSchema (data , SchemaType.Source);
 465                         }catch (Exception e){
 466                                 Assert.Fail ("#3 Unexpected Exception : " + e);
 467                         }
 468                         Assert.AreEqual ( "Table", data.Tables[0].TableName);
 469                         Assert.AreEqual ( "Table1", data.Tables[1].TableName);
 470                         Assert.AreEqual ( "Column1", data.Tables[0].Columns[0].ColumnName,
 471                                 "#6 Unnamed col shud be named as 'ColumnN'");
 472                         Assert.AreEqual ( "Column2", data.Tables[0].Columns[1].ColumnName,
 473                                 "#7 Unnamed col shud be named as 'ColumnN'");
 474                         Assert.AreEqual ( "Column1", data.Tables[1].Columns[0].ColumnName,
 475                                 "#8 Unnamed col shud be named as 'ColumnN'");
 476                         Assert.AreEqual ( "Column2", data.Tables[1].Columns[1].ColumnName,
 477                                 "#9 Unnamed col shud be named as 'ColumnN'");
 478                         Assert.AreEqual (ConnectionState.Closed, adapter.SelectCommand.Connection.State,
 479                                 "#10 Connection shud be closed");
 480
 481                         // Test if mapping works correctly
 482                         // doesent work in both mono and msdotnet
 483                         // gotto check if something is wrong
 484                         /*
 485                         query = "select id,type_bit from numeric_family";
 486                         adapter = new SqlDataAdapter (query, connectionString);
 487                         data = new DataSet ("test");
 488                         DataTable table = data.Tables.Add ("numeric_family_1");
 489                         table.Columns.Add ("id");
 490                         table.Columns.Add ("type_bit");
 491                         DataTableMapping map = adapter.TableMappings.Add("numeric_family_1",
 492                                                         "numeric_family");
 493                         map.ColumnMappings.Add ("id", "id_1");
 494                         map.ColumnMappings.Add ("type_bit", "type_bit_1");
 495                         adapter.FillSchema (data, SchemaType.Source, "numeric_family");
 496                         foreach (DataTable tab in data.Tables){
 497                                 Console.WriteLine ("Table == {0}",tab.TableName);
 498                                 foreach (DataColumn col in tab.Columns)
 499                                         Console.WriteLine (" Col = {0} " , col.ColumnName);
 500                         }
 501                         */
 502                 }
 503
 504                 [Test]
 505                 public void MissingSchemaActionTest ()
 506                 {
 507                         adapter = new SqlDataAdapter (
 508                                         "select id,type_bit,type_int from numeric_family where id<=4",
 509                                          connectionString);
 510                         data = new DataSet ();
 511                         Assert.AreEqual (MissingSchemaAction.Add, adapter.MissingSchemaAction,
 512                                          "#1 Default Value");
 513
 514                         adapter.Fill (data);
 515                         Assert.AreEqual (1, data.Tables.Count , "#1 One table shud be populated");
 516                         Assert.AreEqual (3, data.Tables[0].Columns.Count, "#2 Missing cols are added");
 517                         Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length, "#3 Default Value");
 518
 519                         adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 520                         data.Reset();
 521                         adapter.Fill (data);
 522                         Assert.AreEqual (3, data.Tables[0].Columns.Count,
 523                                 "#4 Missing cols are added");
 524                         Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length, "#5 Default Value");
 525
 526                         adapter.MissingSchemaAction = MissingSchemaAction.Ignore ;
 527                         data.Reset ();
 528                         adapter.Fill (data);
 529                         Assert.AreEqual (0, data.Tables.Count, "#6 Data shud be ignored");
 530
 531                         adapter.MissingSchemaAction = MissingSchemaAction.Error ;
 532                         data.Reset();
 533                         try {
 534                                 adapter.Fill (data);
 535                                 Assert.Fail ("#8 Exception shud be thrown: Schema Mismatch");
 536                         }catch (AssertionException e) {
 537                                 throw e;
 538                         }catch (Exception e){
 539                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 540                                         "#9 Incorrect Exception : "+e);
 541                         }
 542
 543                         // Test for invalid MissingSchema Value
 544                         try {
 545                                 adapter.MissingSchemaAction = (MissingSchemaAction)(-5000);
 546                                 Assert.Fail ("#10 Exception shud be thrown: Invalid Value");
 547                         }catch (AssertionException e){
 548                                 throw e;
 549                         }catch (Exception e){
 550                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 551                                         "#11 Incorrect Exception : " +e);
 552                         }
 553
 554                         // Tests if Data is filled correctly if schema is defined
 555                         // manually and MissingSchemaAction.Error is set
 556                         adapter.MissingSchemaAction = MissingSchemaAction.Error;
 557                         data.Reset();
 558                         DataTable table = data.Tables.Add ("Table");
 559                         table.Columns.Add ("id");
 560                         table.Columns.Add ("type_bit");
 561                         table.Columns.Add ("type_int");
 562                         try {
 563                                 adapter.Fill (data);
 564                                 Assert.AreEqual (1, data.Tables.Count, "#12");
 565                                 Assert.AreEqual (4, data.Tables[0].Rows.Count, "#13");
 566                         }catch (Exception e) {
 567                                 Assert.Fail ("#12 Unexpected Exception : " + e);
 568                         }
 569                 }
 570
 571                 [Test]
 572                 public void MissingMappingActionTest ()
 573                 {
 574                         adapter = new SqlDataAdapter ("select id,type_bit from numeric_family where id=1",
 575                                         connectionString);
 576                         data = new DataSet ();
 577                         Assert.AreEqual (adapter.MissingMappingAction,
 578                                 MissingMappingAction.Passthrough,
 579                                 "#1 Default Value");
 580                         adapter.Fill(data);
 581                         Assert.AreEqual (1, data.Tables.Count,
 582                                 "#2 One Table shud be created");
 583                         Assert.AreEqual (2, data.Tables[0].Columns.Count,
 584                                 "#3 Two Cols shud be created");
 585
 586                         adapter.MissingMappingAction = MissingMappingAction.Ignore;
 587                         data.Reset ();
 588                         adapter.Fill (data);
 589                         Assert.AreEqual (0, data.Tables.Count, "#4 No table shud be created");
 590
 591                         adapter.MissingMappingAction = MissingMappingAction.Error;
 592                         data.Reset ();
 593                         try {
 594                                 adapter.Fill (data);
 595                                 Assert.Fail ("#5 Exception shud be thrown : Mapping is missing");
 596                         }catch (AssertionException e){
 597                                 throw e;
 598                         }catch (Exception e) {
 599                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 600                                         "#6 Incorrect Exception : " + e);
 601                         }
 602
 603                         try {
 604                                 adapter.MissingMappingAction = (MissingMappingAction)(-5000);
 605                                 Assert.Fail ("#7 Exception shud be thrown : Invalid Value");
 606                         }catch (AssertionException e){
 607                                 throw e;
 608                         }catch (Exception e){
 609                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 610                                         "#8 Incorrect Exception : " +e);
 611                         }
 612
 613                         // Test if mapping the column and table names works correctly
 614                         adapter.MissingMappingAction = MissingMappingAction.Error;
 615                         data.Reset ();
 616                         DataTable table = data.Tables.Add ("numeric_family_1");
 617                         table.Columns.Add ("id_1");
 618                         table.Columns.Add ("type_bit_1");
 619                         table.Columns.Add ("type_int_1");
 620                         DataTableMapping tableMap = adapter.TableMappings.Add ("numeric_family",
 621                                                          "numeric_family_1");
 622                         tableMap.ColumnMappings.Add ("id", "id_1");
 623                         tableMap.ColumnMappings.Add ("type_bit", "type_bit_1");
 624                         tableMap.ColumnMappings.Add ("type_int", "type_int_1");
 625                         adapter.Fill (data,"numeric_family");
 626                         Assert.AreEqual (1, data.Tables.Count ,
 627                                 "#8 The DataTable shud be correctly mapped");
 628                         Assert.AreEqual (3, data.Tables[0].Columns.Count,
 629                                 "#9 The DataColumns shud be corectly mapped");
 630                         Assert.AreEqual (1, data.Tables[0].Rows.Count,
 631                                 "#10 Data shud be populated if mapping is correct");
 632                 }
 633
 634                 // Test case for bug #76433
 635                 [Test]
 636                 public void FillSchema_ValuesTest()
 637                 {
 638                         SqlConnection conn = new SqlConnection(connectionString);
 639                         using (conn) {
 640                                 conn.Open();
 641                                 IDbCommand command = conn.CreateCommand();
 642
 643                                 // Create Temp Table
 644                                 String cmd = "Create Table #tmp_TestTable (" ;
 645                                 cmd += "Field1 DECIMAL (10) NOT NULL,";
 646                                 cmd += "Field2 DECIMAL(19))";
 647                                 command.CommandText = cmd;
 648                                 command.ExecuteNonQuery();
 649
 650                                 DataSet dataSet = new DataSet();
 651                                 string selectString = "SELECT * FROM #tmp_TestTable";
 652                                 IDbDataAdapter dataAdapter = new SqlDataAdapter (
 653                                                                         selectString,conn);
 654                                 dataAdapter.FillSchema(dataSet, SchemaType.Mapped);
 655
 656                                 Assert.AreEqual (1, dataSet.Tables.Count, "#1");
 657
 658                                 DataColumn col = dataSet.Tables[0].Columns[0];
 659                                 Assert.IsFalse (dataSet.Tables[0].Columns[0].AllowDBNull,"#2");
 660                                 Assert.IsTrue (dataSet.Tables[0].Columns[1].AllowDBNull,"#3");
 661                         }
 662                 }
 663         }
 664 }