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                         try {
 365                                 adapter.Fill ( null , 0 , 0 , "numeric_family");
 366                                 Assert.Fail ( "#7 Exception shud be thrown : Invalid Dataset");
 367                         }catch (AssertionException e){
 368                                 throw e ;
 369                         }catch (ArgumentNullException) {
 370
 371                         }catch (Exception e) {
 372                                 Assert.AreEqual (typeof(SystemException), e.GetType(),
 373                                         "#8 Incorrect Exception : " + e);
 374                         }
 375                         // conn is currently not being closed..
 376                         //need to be removed once behavior is fixed
 377                         adapter.SelectCommand.Connection.Close ();
 378
 379                         adapter.SelectCommand.Connection = null;
 380                         try {
 381                                 adapter.Fill (data);
 382                                 Assert.Fail ("#9 Exception shud be thrown : Invalid Connection");
 383                         }catch (AssertionException e){
 384                                 throw e;
 385                         }catch (Exception e){
 386                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 387                                         "#10 Incorrect Exception : " + e);
 388                         }
 389                 }
 390
 391                 bool FillErrorContinue = false;
 392                 [Test]
 393                 public void Fill_Test_FillErrorTest ()
 394                 {
 395                         string query = "select type_bigint from numeric_family where id=1 or id=4 ";
 396
 397                         DataSet ds = new DataSet ();
 398                         DataTable table = ds.Tables.Add ("test");
 399                         table.Columns.Add ("col", typeof (int));
 400
 401                         adapter = new SqlDataAdapter (query, connectionString);
 402                         DataTableMapping mapping = adapter.TableMappings.Add ("numeric_family", "test");
 403                         mapping.ColumnMappings.Add ("type_bigint", "col");
 404
 405                         int count = 0;
 406                         try {
 407                                 count = adapter.Fill (ds, "numeric_family");
 408                                 Assert.Fail ("#1 Overflow exception must be thrown");
 409                         }catch (OverflowException e) {
 410                         }
 411                         Assert.AreEqual (0, ds.Tables [0].Rows.Count, "#2");
 412                         Assert.AreEqual (0, count, "#3");
 413
 414                         adapter.FillError += new FillErrorEventHandler (ErrorHandler);
 415                         FillErrorContinue = false;
 416                         try {
 417                                 count = adapter.Fill (ds, "numeric_family");
 418                                 Assert.Fail ("#4 Overflow exception must be thrown");
 419                         }catch (OverflowException e) {
 420                         }
 421                         Assert.AreEqual (0, ds.Tables [0].Rows.Count, "#5");
 422                         Assert.AreEqual (0, count, "#6");
 423
 424                         FillErrorContinue = true;
 425                         count = adapter.Fill (ds, "numeric_family");
 426                         // 1 row shud be filled
 427                         Assert.AreEqual (1, ds.Tables [0].Rows.Count, "#7");
 428                         Assert.AreEqual (1, count, "#8");
 429                 }
 430
 431                 void ErrorHandler (object sender, FillErrorEventArgs args)
 432                 {
 433                         args.Continue = FillErrorContinue;
 434                 }
 435
 436                 [Test]
 437                 public void GetFillParametersTest ()
 438                 {
 439                         string query = "select id, type_bit from numeric_family where id > @param1";
 440                         adapter = new SqlDataAdapter (query, connectionString);
 441                         IDataParameter[] param = adapter.GetFillParameters ();
 442                         Assert.AreEqual (0, param.Length, "#1 size shud be 0");
 443
 444                         SqlParameter param1 = new SqlParameter ();
 445                         param1.ParameterName = "@param1";
 446                         param1.Value = 2;
 447                         adapter.SelectCommand.Parameters.Add (param1);
 448
 449                         param = adapter.GetFillParameters ();
 450                         Assert.AreEqual (1, param.Length, "#2 count shud be 1");
 451                         Assert.AreEqual (param1, param[0], "#3 Params shud be equal");
 452                 }
 453
 454                 [Test]
 455                 public void FillSchemaTest ()
 456                 {
 457                         string query = "";
 458
 459                         // Test if connection is closed if excepton occurs during fill schema
 460                         query = "select * from invalid_table";
 461                         adapter = new SqlDataAdapter (query, connectionString);
 462                         data = new DataSet ("test");
 463                         try {
 464                                 adapter.FillSchema (data , SchemaType.Source);
 465                         }catch (Exception e){
 466                                 if ( adapter.SelectCommand.Connection.State != ConnectionState.Closed)
 467                                 {
 468                                         Assert.Fail ("#0 Conn shud be closed if exception occurs");
 469                                         adapter.SelectCommand.Connection.Close();
 470                                 }
 471                         }
 472
 473                         // Test Primary Key is set (since primary key column returned)
 474                         query = "select id, type_int from numeric_family where id=1";
 475                         adapter = new SqlDataAdapter (query, connectionString);
 476                         data = new DataSet ("test1");
 477                         adapter.FillSchema (data , SchemaType.Source);
 478
 479                         Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length,
 480                                 "#1 Primary Key property must be set");
 481
 482                         // Test Primary Key is not set (since primary key column is returned)
 483                         query = "select type_bit, type_int from numeric_family where id=1";
 484                         adapter = new SqlDataAdapter (query, connectionString);
 485                         data = new DataSet ("test2");
 486                         adapter.FillSchema (data, SchemaType.Source);
 487                         Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length,
 488                                 "#2 Primary Key property should not be set");
 489
 490                         // Test multiple tables are created for a batch query
 491                         query = "Select id ,type_bit from numeric_family;" ;
 492                         query += "Select id,type_bit,type_int from numeric_family;";
 493                         data = new DataSet ("test3");
 494                         adapter = new SqlDataAdapter (query, connectionString);
 495                         adapter.FillSchema (data , SchemaType.Source);
 496                         Assert.AreEqual (2 , data.Tables.Count , "#3 A table shud be created for each Result Set");
 497                         Assert.AreEqual (2 , data.Tables[0].Columns.Count , "#4 should have 2 columns");
 498                         Assert.AreEqual (3 , data.Tables[1].Columns.Count , "#5 Should have 3 columns");
 499
 500                         // Test if table names and column names  are filled correctly
 501                         query = "select 10,20 from numeric_family;" ;
 502                         query += "select 10,20 from numeric_family;";
 503                         adapter = new SqlDataAdapter (query, connectionString);
 504                         data = new DataSet ("test4");
 505                         try {
 506                                 adapter.FillSchema (data , SchemaType.Source);
 507                         }catch (Exception e){
 508                                 Assert.Fail ("#3 Unexpected Exception : " + e);
 509                         }
 510                         Assert.AreEqual ( "Table", data.Tables[0].TableName);
 511                         Assert.AreEqual ( "Table1", data.Tables[1].TableName);
 512                         Assert.AreEqual ( "Column1", data.Tables[0].Columns[0].ColumnName,
 513                                 "#6 Unnamed col shud be named as 'ColumnN'");
 514                         Assert.AreEqual ( "Column2", data.Tables[0].Columns[1].ColumnName,
 515                                 "#7 Unnamed col shud be named as 'ColumnN'");
 516                         Assert.AreEqual ( "Column1", data.Tables[1].Columns[0].ColumnName,
 517                                 "#8 Unnamed col shud be named as 'ColumnN'");
 518                         Assert.AreEqual ( "Column2", data.Tables[1].Columns[1].ColumnName,
 519                                 "#9 Unnamed col shud be named as 'ColumnN'");
 520                         Assert.AreEqual (ConnectionState.Closed, adapter.SelectCommand.Connection.State,
 521                                 "#10 Connection shud be closed");
 522
 523                         // Test if mapping works correctly
 524                         // doesent work in both mono and msdotnet
 525                         // gotto check if something is wrong
 526                         /*
 527                         query = "select id,type_bit from numeric_family";
 528                         adapter = new SqlDataAdapter (query, connectionString);
 529                         data = new DataSet ("test");
 530                         DataTable table = data.Tables.Add ("numeric_family_1");
 531                         table.Columns.Add ("id");
 532                         table.Columns.Add ("type_bit");
 533                         DataTableMapping map = adapter.TableMappings.Add("numeric_family_1",
 534                                                         "numeric_family");
 535                         map.ColumnMappings.Add ("id", "id_1");
 536                         map.ColumnMappings.Add ("type_bit", "type_bit_1");
 537                         adapter.FillSchema (data, SchemaType.Source, "numeric_family");
 538                         foreach (DataTable tab in data.Tables){
 539                                 Console.WriteLine ("Table == {0}",tab.TableName);
 540                                 foreach (DataColumn col in tab.Columns)
 541                                         Console.WriteLine (" Col = {0} " , col.ColumnName);
 542                         }
 543                         */
 544                 }
 545
 546                 [Test]
 547                 public void MissingSchemaActionTest ()
 548                 {
 549                         adapter = new SqlDataAdapter (
 550                                         "select id,type_bit,type_int from numeric_family where id<=4",
 551                                          connectionString);
 552                         data = new DataSet ();
 553                         Assert.AreEqual (MissingSchemaAction.Add, adapter.MissingSchemaAction,
 554                                          "#1 Default Value");
 555
 556                         adapter.Fill (data);
 557                         Assert.AreEqual (1, data.Tables.Count , "#1 One table shud be populated");
 558                         Assert.AreEqual (3, data.Tables[0].Columns.Count, "#2 Missing cols are added");
 559                         Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length, "#3 Default Value");
 560
 561                         adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 562                         data.Reset();
 563                         adapter.Fill (data);
 564                         Assert.AreEqual (3, data.Tables[0].Columns.Count,
 565                                 "#4 Missing cols are added");
 566                         Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length, "#5 Default Value");
 567
 568                         adapter.MissingSchemaAction = MissingSchemaAction.Ignore ;
 569                         data.Reset ();
 570                         adapter.Fill (data);
 571                         Assert.AreEqual (0, data.Tables.Count, "#6 Data shud be ignored");
 572
 573                         adapter.MissingSchemaAction = MissingSchemaAction.Error ;
 574                         data.Reset();
 575                         try {
 576                                 adapter.Fill (data);
 577                                 Assert.Fail ("#8 Exception shud be thrown: Schema Mismatch");
 578                         }catch (AssertionException e) {
 579                                 throw e;
 580                         }catch (Exception e){
 581                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 582                                         "#9 Incorrect Exception : "+e);
 583                         }
 584
 585                         // Test for invalid MissingSchema Value
 586                         try {
 587                                 adapter.MissingSchemaAction = (MissingSchemaAction)(-5000);
 588                                 Assert.Fail ("#10 Exception shud be thrown: Invalid Value");
 589                         }catch (AssertionException e){
 590                                 throw e;
 591                         }catch (Exception e){
 592                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 593                                         "#11 Incorrect Exception : " +e);
 594                         }
 595
 596                         // Tests if Data is filled correctly if schema is defined
 597                         // manually and MissingSchemaAction.Error is set
 598                         adapter.MissingSchemaAction = MissingSchemaAction.Error;
 599                         data.Reset();
 600                         DataTable table = data.Tables.Add ("Table");
 601                         table.Columns.Add ("id");
 602                         table.Columns.Add ("type_bit");
 603                         table.Columns.Add ("type_int");
 604                         try {
 605                                 adapter.Fill (data);
 606                                 Assert.AreEqual (1, data.Tables.Count, "#12");
 607                                 Assert.AreEqual (4, data.Tables[0].Rows.Count, "#13");
 608                         }catch (Exception e) {
 609                                 Assert.Fail ("#12 Unexpected Exception : " + e);
 610                         }
 611                 }
 612
 613                 [Test]
 614                 public void MissingMappingActionTest ()
 615                 {
 616                         adapter = new SqlDataAdapter ("select id,type_bit from numeric_family where id=1",
 617                                         connectionString);
 618                         data = new DataSet ();
 619                         Assert.AreEqual (adapter.MissingMappingAction,
 620                                 MissingMappingAction.Passthrough,
 621                                 "#1 Default Value");
 622                         adapter.Fill(data);
 623                         Assert.AreEqual (1, data.Tables.Count,
 624                                 "#2 One Table shud be created");
 625                         Assert.AreEqual (2, data.Tables[0].Columns.Count,
 626                                 "#3 Two Cols shud be created");
 627
 628                         adapter.MissingMappingAction = MissingMappingAction.Ignore;
 629                         data.Reset ();
 630                         adapter.Fill (data);
 631                         Assert.AreEqual (0, data.Tables.Count, "#4 No table shud be created");
 632
 633                         adapter.MissingMappingAction = MissingMappingAction.Error;
 634                         data.Reset ();
 635                         try {
 636                                 adapter.Fill (data);
 637                                 Assert.Fail ("#5 Exception shud be thrown : Mapping is missing");
 638                         }catch (AssertionException e){
 639                                 throw e;
 640                         }catch (Exception e) {
 641                                 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
 642                                         "#6 Incorrect Exception : " + e);
 643                         }
 644
 645                         try {
 646                                 adapter.MissingMappingAction = (MissingMappingAction)(-5000);
 647                                 Assert.Fail ("#7 Exception shud be thrown : Invalid Value");
 648                         }catch (AssertionException e){
 649                                 throw e;
 650                         }catch (Exception e){
 651                                 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
 652                                         "#8 Incorrect Exception : " +e);
 653                         }
 654
 655                         // Test if mapping the column and table names works correctly
 656                         adapter.MissingMappingAction = MissingMappingAction.Error;
 657                         data.Reset ();
 658                         DataTable table = data.Tables.Add ("numeric_family_1");
 659                         table.Columns.Add ("id_1");
 660                         table.Columns.Add ("type_bit_1");
 661                         table.Columns.Add ("type_int_1");
 662                         DataTableMapping tableMap = adapter.TableMappings.Add ("numeric_family",
 663                                                          "numeric_family_1");
 664                         tableMap.ColumnMappings.Add ("id", "id_1");
 665                         tableMap.ColumnMappings.Add ("type_bit", "type_bit_1");
 666                         tableMap.ColumnMappings.Add ("type_int", "type_int_1");
 667                         adapter.Fill (data,"numeric_family");
 668                         Assert.AreEqual (1, data.Tables.Count ,
 669                                 "#8 The DataTable shud be correctly mapped");
 670                         Assert.AreEqual (3, data.Tables[0].Columns.Count,
 671                                 "#9 The DataColumns shud be corectly mapped");
 672                         Assert.AreEqual (1, data.Tables[0].Rows.Count,
 673                                 "#10 Data shud be populated if mapping is correct");
 674                 }
 675
 676                 // Test case for bug #76433
 677                 [Test]
 678                 public void FillSchema_ValuesTest()
 679                 {
 680                         SqlConnection conn = new SqlConnection(connectionString);
 681                         using (conn) {
 682                                 conn.Open();
 683                                 IDbCommand command = conn.CreateCommand();
 684
 685                                 // Create Temp Table
 686                                 String cmd = "Create Table #tmp_TestTable (" ;
 687                                 cmd += "Field1 DECIMAL (10) NOT NULL,";
 688                                 cmd += "Field2 DECIMAL(19))";
 689                                 command.CommandText = cmd;
 690                                 command.ExecuteNonQuery();
 691
 692                                 DataSet dataSet = new DataSet();
 693                                 string selectString = "SELECT * FROM #tmp_TestTable";
 694                                 IDbDataAdapter dataAdapter = new SqlDataAdapter (
 695                                                                         selectString,conn);
 696                                 dataAdapter.FillSchema(dataSet, SchemaType.Mapped);
 697
 698                                 Assert.AreEqual (1, dataSet.Tables.Count, "#1");
 699
 700                                 DataColumn col = dataSet.Tables[0].Columns[0];
 701                                 Assert.IsFalse (dataSet.Tables[0].Columns[0].AllowDBNull,"#2");
 702                                 Assert.IsTrue (dataSet.Tables[0].Columns[1].AllowDBNull,"#3");
 703                         }
 704                 }
 705
 706                 [Test]
 707                 public void Fill_CheckSchema ()
 708                 {
 709                         SqlConnection conn = new SqlConnection(connectionString);
 710                         using (conn) {
 711                                 conn.Open();
 712
 713                                 IDbCommand command = conn.CreateCommand();
 714
 715                                 // Create Temp Table
 716                                 String cmd = "Create Table #tmp_TestTable (" ;
 717                                 cmd += "id int primary key,";
 718                                 cmd += "field int not null)";
 719                                 command.CommandText = cmd;
 720                                 command.ExecuteNonQuery();
 721
 722                                 DataSet dataSet = new DataSet();
 723                                 string selectString = "SELECT * from #tmp_TestTable";
 724                                 IDbDataAdapter dataAdapter = new SqlDataAdapter (
 725                                                                         selectString,conn);
 726                                 dataAdapter.Fill (dataSet);
 727                                 Assert.IsTrue (dataSet.Tables[0].Columns[1].AllowDBNull, "#1");
 728                                 Assert.AreEqual (0, dataSet.Tables[0].PrimaryKey.Length, "#2");
 729
 730                                 dataSet.Reset ();
 731                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey ;
 732                                 dataAdapter.Fill (dataSet);
 733                                 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#3");
 734                                 Assert.AreEqual (1, dataSet.Tables[0].PrimaryKey.Length, "#4");
 735                         }
 736                 }
 737
 738                 [Test]
 739                 public void FillSchema_CheckSchema ()
 740                 {
 741                         SqlConnection conn = new SqlConnection(connectionString);
 742                         using (conn) {
 743                                 conn.Open();
 744
 745                                 IDbCommand command = conn.CreateCommand();
 746
 747                                 // Create Temp Table
 748                                 String cmd = "Create Table #tmp_TestTable (" ;
 749                                 cmd += "id int primary key,";
 750                                 cmd += "field int not null)";
 751                                 command.CommandText = cmd;
 752                                 command.ExecuteNonQuery();
 753
 754                                 DataSet dataSet = new DataSet();
 755                                 string selectString = "SELECT * from #tmp_TestTable";
 756                                 IDbDataAdapter dataAdapter = new SqlDataAdapter (
 757                                                                         selectString,conn);
 758
 759                                 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
 760                                 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#1");
 761
 762                                 dataSet.Reset ();
 763                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.Add;
 764                                 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
 765                                 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#2");
 766
 767                                 dataSet.Reset ();
 768                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.Ignore;
 769                                 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
 770                                 Assert.AreEqual (0, dataSet.Tables.Count, "#3");
 771
 772                                 dataSet.Reset ();
 773                                 dataAdapter.MissingSchemaAction = MissingSchemaAction.Error;
 774                                 try {
 775                                         dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
 776                                         Assert.Fail ("#4 Error should be thrown");
 777                                 } catch (InvalidOperationException e) {
 778                                 }
 779                         }
 780                 }
 781
 782                 [Test]
 783                 public void Fill_RelatedTables ()
 784                 {
 785                         SqlConnection conn = new SqlConnection(connectionString);
 786                         using (conn) {
 787                                 conn.Open();
 788                                 IDbCommand command = conn.CreateCommand();
 789
 790                                 DataSet dataSet = new DataSet();
 791                                 string selectString = "SELECT id, type_int from numeric_family where id < 3";
 792                                 DbDataAdapter dataAdapter = new SqlDataAdapter (selectString,conn);
 793
 794                                 DataTable table2 = dataSet.Tables.Add ("table2");
 795                                 DataColumn ccol1 = table2.Columns.Add ("id", typeof (int));
 796                                 DataColumn ccol2 = table2.Columns.Add ("type_int", typeof (int));
 797
 798                                 DataTable table1 = dataSet.Tables.Add ("table1");
 799                                 DataColumn pcol1 = table1.Columns.Add ("id", typeof (int));
 800                                 DataColumn pcol2 = table1.Columns.Add ("type_int", typeof (int));
 801
 802                                 table2.Constraints.Add ("fk", pcol1, ccol1);
 803
 804                                 dataSet.EnforceConstraints = false;
 805                                 dataAdapter.Fill (dataSet, "table1");
 806                                 dataAdapter.Fill (dataSet, "table2");
 807
 808                                 //Should not throw an exception
 809                                 dataSet.EnforceConstraints = true;
 810
 811                                 Assert.AreEqual (2, table1.Rows.Count, "#1");
 812                                 Assert.AreEqual (2, table2.Rows.Count, "#2");
 813                         }
 814                 }
 815         }
 816 }