mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/MonoTests.System.Data.Utils/ADONetTesterClass.cs

   1 // Authors:
   2 //   Rafael Mizrahi   <rafim@mainsoft.com>
   3 //   Erez Lotan       <erezl@mainsoft.com>
   4 //   Oren Gurfinkel   <oreng@mainsoft.com>
   5 //   Ofer Borstein
   6 //
   7 // Copyright (c) 2004 Mainsoft Co.
   8 //
   9 // Permission is hereby granted, free of charge, to any person obtaining
  10 // a copy of this software and associated documentation files (the
  11 // "Software"), to deal in the Software without restriction, including
  12 // without limitation the rights to use, copy, modify, merge, publish,
  13 // distribute, sublicense, and/or sell copies of the Software, and to
  14 // permit persons to whom the Software is furnished to do so, subject to
  15 // the following conditions:
  16 //
  17 // The above copyright notice and this permission notice shall be
  18 // included in all copies or substantial portions of the Software.
  19 //
  20 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  21 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  22 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  23 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  24 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  25 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  26 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  27 //
  28
  29 using System;
  30 using System.Data;
  31 using System.Data.OleDb ;
  32 using System.IO;
  33 using System.Collections;
  34 using MonoTests.System.Data.Utils.Data;
  35 using Sys = System;
  36
  37 namespace MonoTests.System.Data.Utils {
  38         public class ADONetTesterClass : GHTBase {
  39                 #region " Base Constructors "
  40                 protected ADONetTesterClass(Sys.IO.TextWriter Logger, bool LogOnSuccess) : base(Logger,LogOnSuccess){}
  41                 protected ADONetTesterClass(bool LogOnSuccess):base(Console.Out, LogOnSuccess){}
  42                 protected ADONetTesterClass():base(Console.Out, false){}
  43
  44                 #endregion
  45
  46                 private MonoTests.System.Data.Utils.DataBaseServer DBType ;
  47
  48
  49                 #region "-----------  Build Update Commands --------------"
  50                 protected void OleDbDataAdapter_BuildUpdateCommands(ref Sys.Data.OleDb.OleDbDataAdapter oleDBda) {
  51                         Sys.Data.OleDb.OleDbConnection Conn = oleDBda.SelectCommand.Connection;
  52
  53                         oleDBda.DeleteCommand = new Sys.Data.OleDb.OleDbCommand();
  54                         oleDBda.InsertCommand = new Sys.Data.OleDb.OleDbCommand();
  55                         oleDBda.UpdateCommand = new Sys.Data.OleDb.OleDbCommand();
  56
  57                         oleDBda.DeleteCommand.Connection = Conn;
  58                         oleDBda.InsertCommand.Connection = Conn;
  59                         oleDBda.UpdateCommand.Connection = Conn;
  60
  61                         oleDBda.DeleteCommand.Parameters.Add(new Sys.Data.OleDb.OleDbParameter("EmployeeID",Sys.Data.OleDb.OleDbType.Integer)) ;
  62                         oleDBda.DeleteCommand.Parameters["EmployeeID"].SourceVersion = DataRowVersion.Original;
  63                         oleDBda.DeleteCommand.Parameters["EmployeeID"].SourceColumn = "EmployeeID";
  64
  65                         oleDBda.InsertCommand.Parameters.Add(new Sys.Data.OleDb.OleDbParameter("EmployeeID",Sys.Data.OleDb.OleDbType.Integer));
  66                         oleDBda.InsertCommand.Parameters.Add(new Sys.Data.OleDb.OleDbParameter("LastName",Sys.Data.OleDb.OleDbType.VarWChar ,20));
  67                         oleDBda.InsertCommand.Parameters.Add(new Sys.Data.OleDb.OleDbParameter("FirstName",Sys.Data.OleDb.OleDbType.VarWChar,10));
  68                         oleDBda.InsertCommand.Parameters["EmployeeID"].SourceColumn = "EmployeeID";
  69                         oleDBda.InsertCommand.Parameters["LastName"].SourceColumn = "LastName";
  70                         oleDBda.InsertCommand.Parameters["FirstName"].SourceColumn = "FirstName";
  71
  72                         oleDBda.UpdateCommand.Parameters.Add(new Sys.Data.OleDb.OleDbParameter("Title",Sys.Data.OleDb.OleDbType.VarWChar,30));
  73                         oleDBda.UpdateCommand.Parameters.Add(new Sys.Data.OleDb.OleDbParameter("EmployeeID",Sys.Data.OleDb.OleDbType.Integer));
  74                         oleDBda.UpdateCommand.Parameters["EmployeeID"].SourceColumn = "EmployeeID";
  75                         oleDBda.UpdateCommand.Parameters["Title"].SourceColumn = "Title";
  76
  77
  78                         //for OleDB, ODBC
  79                         string deleteSQL = "DELETE FROM Employees WHERE EmployeeID = ?";
  80                         string insertSQL = "INSERT INTO Employees (EmployeeID, LastName, FirstName) VALUES (?, ?, ?)";
  81                         string updateSQL = "UPDATE Employees SET Title = ? WHERE EmployeeID = ?";
  82
  83                         oleDBda.DeleteCommand.CommandText = deleteSQL;
  84                         oleDBda.InsertCommand.CommandText = insertSQL;
  85                         oleDBda.UpdateCommand.CommandText = updateSQL;
  86
  87                 }
  88                 protected void SqlDataAdapter_BuildUpdateCommands(ref Sys.Data.SqlClient.SqlDataAdapter Sqlda) {
  89                         Sys.Data.SqlClient.SqlConnection Conn = Sqlda.SelectCommand.Connection;
  90
  91                         Sqlda.DeleteCommand = new Sys.Data.SqlClient.SqlCommand();
  92                         Sqlda.InsertCommand = new Sys.Data.SqlClient.SqlCommand();
  93                         Sqlda.UpdateCommand = new Sys.Data.SqlClient.SqlCommand();
  94
  95                         Sqlda.DeleteCommand.Connection = Conn;
  96                         Sqlda.InsertCommand.Connection = Conn;
  97                         Sqlda.UpdateCommand.Connection = Conn;
  98
  99                         Sqlda.DeleteCommand.Parameters.Add(new Sys.Data.SqlClient.SqlParameter("@EmployeeID",DbType.Int32)) ;
 100                         Sqlda.DeleteCommand.Parameters["@EmployeeID"].SourceVersion = DataRowVersion.Original;
 101                         Sqlda.DeleteCommand.Parameters["@EmployeeID"].SourceColumn = "EmployeeID";
 102
 103                         Sqlda.InsertCommand.Parameters.Add(new Sys.Data.SqlClient.SqlParameter("@EmployeeID",DbType.Int32));
 104                         Sqlda.InsertCommand.Parameters.Add(new Sys.Data.SqlClient.SqlParameter("@LastName",Sys.Data.SqlDbType.VarChar ,20));
 105                         Sqlda.InsertCommand.Parameters.Add(new Sys.Data.SqlClient.SqlParameter("@FirstName",Sys.Data.SqlDbType.VarChar ,10));
 106                         Sqlda.InsertCommand.Parameters["@EmployeeID"].SourceColumn = "EmployeeID";
 107                         Sqlda.InsertCommand.Parameters["@LastName"].SourceColumn = "LastName";
 108                         Sqlda.InsertCommand.Parameters["@FirstName"].SourceColumn = "FirstName";
 109
 110                         Sqlda.UpdateCommand.Parameters.Add(new Sys.Data.SqlClient.SqlParameter("@Title",Sys.Data.SqlDbType.VarChar,30));
 111                         Sqlda.UpdateCommand.Parameters.Add(new Sys.Data.SqlClient.SqlParameter("@EmployeeID",DbType.Int32));
 112                         Sqlda.UpdateCommand.Parameters["@EmployeeID"].SourceColumn = "EmployeeID";
 113                         Sqlda.UpdateCommand.Parameters["@Title"].SourceColumn = "Title";
 114
 115                         //for Sql Client
 116                         string deleteSql = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";
 117                         string insertSql = "INSERT INTO Employees (EmployeeID, LastName, FirstName) VALUES (@EmployeeID, @LastName, @FirstName)";
 118                         string updateSql = "UPDATE Employees SET Title = @Title WHERE EmployeeID = @EmployeeID";
 119
 120                         Sqlda.DeleteCommand.CommandText = deleteSql;
 121                         Sqlda.InsertCommand.CommandText = insertSql;
 122                         Sqlda.UpdateCommand.CommandText = updateSql;
 123
 124                 }
 125                 #endregion
 126
 127                 #region "-----------  Sys.Data.Common.DBDataAdapter --------------"
 128
 129                 #region " DBDataAdapter - Fill / Fill Schema "
 130
 131                 protected void DbDataAdapter_Fill_Ds(Sys.Data.Common.DbDataAdapter dbDA) {
 132                         DataSet dsResult, dsExpected ;
 133                         int ExpectedRowsCount,ResultRowsCount;
 134                         DataSet ds = new DataSet();
 135
 136                         ExpectedRowsCount = ReadDBData_Fill(dbDA,ref ds,false);
 137
 138                         // create expected dataset to compare result to
 139                         dsExpected = ds.Copy();
 140
 141                         //make some changes, the fill method will overides those changes with data from DB.
 142                         foreach (DataRow dr in ds.Tables[0].Select())
 143                                 dr["Country"] = "NeverNeverLand";
 144                         ds.Tables[0].Columns.Remove("HomePhone"); //remove column, this column will be addedd during the fill process
 145                         //ds.Tables.Remove(ds.Tables[1]); //remove the table, this table will be addedd during the fill process
 146                         ds.AcceptChanges();
 147
 148                         // create source dataset to be filled
 149                         dsResult = ds.Copy();
 150
 151                         //execute fill
 152                         ResultRowsCount = dbDA.Fill(dsResult);
 153
 154                         CompareResults_Fill(dsResult,dsExpected);
 155
 156                         //close connection
 157                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
 158                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
 159                 }
 160
 161                 protected void DbDataAdapter_Fill_Dt(Sys.Data.Common.DbDataAdapter dbDA) {
 162                         DataSet dsResult, dsExpected ;
 163                         int ExpectedRowsCount,ResultRowsCount;
 164                         DataSet ds = new DataSet();
 165
 166                         ExpectedRowsCount = ReadDBData_Fill(dbDA,ref ds, false);
 167
 168                         //ds.Tables.Remove(ds.Tables[1]); //remove the table, fill only one table
 169
 170                         // create expected dataset to compare result to
 171                         dsExpected = ds.Copy();
 172
 173                         //make some changes, the fill method will overides those changes with data from DB.
 174                         foreach (DataRow dr in ds.Tables[0].Select())
 175                                 dr["Country"] = "NeverNeverLand";
 176                         ds.Tables[0].Columns.Remove("HomePhone"); //remove column, this column will be addedd during the fill process
 177                         ds.AcceptChanges();
 178
 179                         // create source dataset to be filled
 180                         dsResult = ds.Copy();
 181
 182                         //execute fill
 183                         ResultRowsCount = dbDA.Fill(dsResult.Tables["Table"]);
 184
 185                         CompareResults_Fill(dsResult,dsExpected);
 186
 187                         //close connection
 188                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
 189                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
 190
 191
 192                 }
 193
 194                 protected void DbDataAdapter_Fill_Ds_Str(Sys.Data.Common.DbDataAdapter dbDA) {
 195                         DataSet dsResult, dsExpected ;
 196                         int ExpectedRowsCount,ResultRowsCount;
 197                         DataSet ds = new DataSet();
 198
 199                         ExpectedRowsCount = ReadDBData_Fill(dbDA,ref ds, false);
 200
 201                         // create expected dataset to compare result to
 202                         dsExpected = ds.Copy();
 203
 204                         //make some changes, the fill method will overides those changes with data from DB.
 205                         foreach (DataRow dr in ds.Tables[0].Select())
 206                                 dr["Country"] = "NeverNeverLand";
 207                         ds.Tables[0].Columns.Remove("HomePhone"); //remove column, this column will be addedd during the fill process
 208                         //ds.Tables.Remove(ds.Tables[1]); //remove the table, this table will be addedd during the fill process
 209                         ds.AcceptChanges();
 210
 211                         // create source dataset to be filled
 212                         dsResult = ds.Copy();
 213
 214                         //execute fill
 215                         ResultRowsCount = dbDA.Fill(dsResult,dsResult.Tables[0].TableName );
 216
 217                         CompareResults_Fill(dsResult,dsExpected);
 218
 219                         //close connection
 220                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
 221                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
 222                 }
 223
 224                 protected void DbDataAdapter_Fill_Ds_Int_Int_Str(Sys.Data.Common.DbDataAdapter dbDA) {
 225                         DataSet dsResult, dsExpected ;
 226                         int ExpectedRowsCount,ResultRowsCount;
 227                         DataSet ds = new DataSet();
 228
 229                         ExpectedRowsCount = ReadDBData_Fill(dbDA,ref ds, false);
 230                         // create expected dataset to compare result to
 231                         dsExpected = ds.Copy();
 232
 233                         //make some changes, the fill method will overides those changes with data from DB.
 234                         foreach (DataRow dr in ds.Tables[0].Select())
 235                                 dr["Country"] = "NeverNeverLand";
 236                         ds.Tables[0].Columns.Remove("HomePhone"); //remove column, this column will be addedd during the fill process
 237                         //ds.Tables.Remove(ds.Tables[1]); //remove the table, this table will be addedd during the fill process
 238                         ds.AcceptChanges();
 239
 240                         // create source dataset to be filled
 241                         dsResult = ds.Copy();
 242
 243                         //execute fill
 244                         ResultRowsCount = dbDA.Fill(dsResult,0,0,dsResult.Tables[0].TableName);
 245                         CompareResults_Fill(dsResult,dsExpected);
 246
 247                         dsResult = ds.Copy();
 248                         //modify expected dataset to match the expected result
 249                         for (int i=0; i < dsExpected.Tables[0].Rows.Count ; i++) {
 250                                 if (i < 5 || i > 14) {
 251                                         dsExpected.Tables[0].Rows[i]["Country"] = "NeverNeverLand";
 252                                         dsExpected.Tables[0].Rows[i]["HomePhone"] = DBNull.Value;
 253                                 }
 254                         }
 255                         ResultRowsCount = dbDA.Fill(dsResult,5,10,dsResult.Tables[0].TableName);
 256                         CompareResults_Fill(dsResult,dsExpected);
 257
 258                         //close connection
 259                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
 260                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
 261                 }
 262
 263
 264                 protected void DbDataAdapter_FillSchema_Ds_SchemaType(Sys.Data.Common.DbDataAdapter dbDA) {
 265                         DataSet dsResult = new DataSet();
 266                         DataSet dsExpected = new DataSet();
 267
 268                         // create expected dataset to compare result to
 269                         ReadDBData_Fill(dbDA,ref dsExpected ,true);
 270
 271                         //  Note:   When handling batch SQL statements that return multiple results,
 272                         //      the implementation of FillSchema for the .NET Framework Data Provider for OLEDB
 273                         //      retrieves schema information for only the first result.
 274                         //      To retrieve schema information for multiple results, use Fill with the MissingSchemaAction set to AddWithKey
 275                         //                      if (dbDA.GetType() == typeof(Sys.Data.OleDb.OleDbDataAdapter))
 276                         //                              dsExpected.Tables.Remove(dsExpected.Tables[1]);
 277
 278                         //execute FillSchema
 279
 280                         //dsResult = dsExpected.Copy();
 281                         DataTable[] dtArr = dbDA.FillSchema(dsResult,SchemaType.Mapped );
 282
 283                         //************  Fix .Net bug? (FillSchema method add AutoIncrement=true) *******************
 284                         dsResult.Tables[0].Columns["EmployeeID"].AutoIncrement = false;
 285
 286                         CompareResults_Fill(dsResult,dsExpected);
 287
 288                         Exception exp = null;
 289                         try {
 290                                 BeginCase("Check return value - Table[0]");
 291                                 Compare(dtArr[0],dsResult.Tables[0] );
 292                         }
 293                         catch(Exception ex)     {exp = ex;}
 294                         finally {EndCase(exp); exp = null;}
 295
 296                         //                      if (dbDA.GetType() != typeof(Sys.Data.OleDb.OleDbDataAdapter))
 297                         //                              try
 298                         //                              {
 299                         //                                      BeginCase("Check return value - Table[1]");
 300                         //                                      Compare(dtArr[1],dsResult.Tables[1]);
 301                         //                              }
 302                         //                              catch(Exception ex)     {exp = ex;}
 303                         //                              finally {EndCase(exp); exp = null;}
 304
 305                         //close connection
 306                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
 307                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
 308                 }
 309
 310                 protected void DbDataAdapter_FillSchema_Dt_SchemaType(Sys.Data.Common.DbDataAdapter dbDA) {
 311                         DataSet dsResult = new DataSet();
 312                         DataSet dsExpected = new DataSet();
 313
 314                         // create expected dataset to compare result to
 315                         ReadDBData_Fill(dbDA,ref dsExpected ,true);
 316
 317                         //dsExpected.Tables.Remove(dsExpected.Tables[1]);
 318
 319                         //execute FillSchema
 320
 321                         dsResult.Tables.Add("Table");
 322                         DataTable dt = dbDA.FillSchema(dsResult.Tables[0],SchemaType.Mapped );
 323
 324                         //************  Fix .Net bug? (FillSchema method add AutoIncrement=true) *******************
 325                         dsResult.Tables[0].Columns["EmployeeID"].AutoIncrement = false;
 326
 327                         CompareResults_Fill(dsResult,dsExpected);
 328                         Exception exp = null;
 329                         try {
 330                                 BeginCase("Check return value - Table[0]");
 331                                 Compare(dt,dsResult.Tables[0] );
 332                         }
 333                         catch(Exception ex)     {exp = ex;}
 334                         finally {EndCase(exp); exp = null;}
 335
 336                         //close connection
 337                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
 338                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
 339                 }
 340
 341                 protected void DbDataAdapter_FillSchema_Ds_SchemaType_Str(Sys.Data.Common.DbDataAdapter dbDA) {
 342                         DataSet dsResult = new DataSet();
 343                         DataSet dsExpected = new DataSet();
 344
 345                         // create expected dataset to compare result to
 346                         ReadDBData_Fill(dbDA,ref dsExpected ,true);
 347
 348                         //dsExpected.Tables.Remove(dsExpected.Tables[1]);
 349
 350                         //execute FillSchema
 351
 352                         dsResult.Tables.Add("Table");
 353                         DataTable[] dtArr = dbDA.FillSchema(dsResult,SchemaType.Mapped,dsResult.Tables[0].TableName);
 354
 355                         //************  Fix .Net bug? (FillSchema method add AutoIncrement=true) *******************
 356                         dsResult.Tables[0].Columns["EmployeeID"].AutoIncrement = false;
 357
 358                         CompareResults_Fill(dsResult,dsExpected);
 359                         Exception exp = null;
 360                         try {
 361                                 BeginCase("Check return value - Table[0]");
 362                                 Compare(dtArr[0],dsResult.Tables[0] );
 363                         }
 364                         catch(Exception ex)     {exp = ex;}
 365                         finally {EndCase(exp); exp = null;}
 366
 367                         //close connection
 368                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
 369                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
 370                 }
 371
 372
 373                 private int ReadDBData_Fill(Sys.Data.Common.DbDataAdapter dbDA, ref DataSet ds, bool ReadSchemaOnly) {
 374                         int ExpectedRowsCount = 0;
 375                         IDbDataAdapter Ida = (IDbDataAdapter)dbDA;
 376                         IDbCommand ICmd = Ida.SelectCommand;
 377                         IDbConnection IConn = ICmd.Connection;
 378                         IDataReader Idr;
 379                         IConn.ConnectionString = MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString;
 380                         ICmd.CommandText = "SELECT EmployeeID, LastName, FirstName, Title, Address, City, Region, Country, Extension, HomePhone  FROM Employees "  ;
 381                         IConn.Open();
 382
 383                         //get db type
 384                         DBType = ConnectedDataProvider.GetDbType(((IDbDataAdapter)dbDA).SelectCommand.Connection.ConnectionString);
 385
 386                         // Execute data Reader - Get Expected results
 387                         Idr = ICmd.ExecuteReader();
 388
 389                         // create temp dataset to insert results
 390                         ExpectedRowsCount = DataReaderFill_Fill(ref ds,ref Idr,ReadSchemaOnly);
 391                         Idr.Close();
 392                         return ExpectedRowsCount;
 393                 }
 394                 private void CompareResults_Fill(DataSet dsResult,DataSet dsExpected ) {
 395                         Exception exp = null;
 396
 397                         //                      try
 398                         //                      {
 399                         //                              BeginCase("Compare Rows count");
 400                         //                              // ???????   Fill return count for first table only    ??????
 401                         //                              Compare(ExpectedRowsCount  ,ResultRowsCount );
 402                         //                      }
 403                         //                      catch(Exception ex)     {exp = ex;}
 404                         //                      finally {EndCase(exp); exp = null;}
 405
 406                         try {
 407                                 BeginCase("Compare data");
 408                                 Compare(dsResult.GetXml() ,dsExpected.GetXml());
 409                         }
 410                         catch(Exception ex)     {exp = ex;}
 411                         finally {EndCase(exp); exp = null;}
 412
 413                         try {
 414                                 BeginCase("Compare schema");
 415                                 Compare(dsResult.GetXmlSchema() ,dsExpected.GetXmlSchema());
 416                         }
 417                         catch(Exception ex)     {exp = ex;}
 418                         finally {EndCase(exp); exp = null;}
 419                 }
 420
 421                 private int DataReaderFill_Fill(ref DataSet dsExpected, ref IDataReader Idr,bool ReadSchemaOnly) {
 422                         bool blnNextResults;
 423                         int RowsAffected = 0;
 424                         object[] objArr = null;
 425                         DataTable SchemaTable = null;
 426                         do {
 427                                 SchemaTable = Idr.GetSchemaTable();
 428
 429                                 //add new table with the right amount of columns, the first table must be named "Table"
 430                                 if (dsExpected.Tables.Count == 0)
 431                                         dsExpected.Tables.Add(new DataTable("Table"));
 432                                 else
 433                                         dsExpected.Tables.Add();
 434                                 for (int i = 0 ; i < Idr.FieldCount; i++) {
 435                                         dsExpected.Tables[dsExpected.Tables.Count-1].Columns.Add(new DataColumn(Idr.GetName(i),Idr.GetFieldType(i)));
 436                                         if (ReadSchemaOnly) {   // add schema info
 437                                                 dsExpected.Tables[dsExpected.Tables.Count-1].Columns[i].AllowDBNull = (bool)SchemaTable.Rows[i]["AllowDBNull"];
 438                                                 dsExpected.Tables[dsExpected.Tables.Count-1].Columns[i].AutoIncrement = (bool)SchemaTable.Rows[i]["IsAutoIncrement"];
 439                                                 dsExpected.Tables[dsExpected.Tables.Count-1].Columns[i].ReadOnly = (bool)SchemaTable.Rows[i]["IsReadOnly"];
 440                                                 dsExpected.Tables[dsExpected.Tables.Count-1].Columns[i].Unique = (bool)SchemaTable.Rows[i]["IsUnique"];
 441                                                 if (dsExpected.Tables[dsExpected.Tables.Count-1].Columns[i].DataType == typeof(string))
 442                                                         dsExpected.Tables[dsExpected.Tables.Count-1].Columns[i].MaxLength = (int)SchemaTable.Rows[i]["ColumnSize"];
 443                                         }
 444                                 }
 445
 446                                 if (!ReadSchemaOnly) {
 447                                         //array that holds the current rows values
 448                                         objArr = new object[Idr.FieldCount];
 449
 450                                         //fill the new table
 451                                         while (Idr.Read()) {
 452                                                 Idr.GetValues(objArr);
 453                                                 //update existing row, if no row is found - add it as new row
 454                                                 dsExpected.Tables[dsExpected.Tables.Count-1].LoadDataRow(objArr,false);
 455                                                 RowsAffected++;
 456                                         }
 457                                 }
 458
 459                                 //get next record set
 460                                 blnNextResults = Idr.NextResult();
 461                         }
 462                         while (blnNextResults);
 463
 464                         // add primary key, fill method will update existing rows instead of insert new ones
 465                         dsExpected.Tables[0].PrimaryKey = new DataColumn[] {dsExpected.Tables[0].Columns["EmployeeID"]};
 466                         //if (ReadSchemaOnly)   dsExpected.Tables[1].PrimaryKey = new DataColumn[] {dsExpected.Tables[1].Columns["CustomerID"]};
 467                         dsExpected.AcceptChanges();
 468
 469                         return RowsAffected;
 470
 471                 }
 472
 473                 #endregion
 474
 475                 #region " DBDataAdapter - FillError "
 476                 private bool blnReadDBData_Fill = false;
 477
 478                 protected void DbDataAdapter_FillError(Sys.Data.Common.DbDataAdapter dbDA) {
 479                         Exception exp = null;
 480                         IDbDataAdapter Ida = (IDbDataAdapter)dbDA;
 481                         IDbCommand ICmd = Ida.SelectCommand;
 482                         IDbConnection IConn = ICmd.Connection;
 483                         IConn.ConnectionString = MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString;
 484                         ICmd.CommandText = "SELECT CustomerID, CompanyName, City, Country, Phone FROM Customers ";
 485                         IConn.Open();
 486
 487                         DataSet ds = new DataSet();
 488                         ds.Tables.Add(new DataTable("Customers"));
 489                         ds.Tables[0].Columns.Add("CustomerID",typeof(byte));
 490
 491                         //check FillError event
 492                         dbDA.FillError += new FillErrorEventHandler(dbDA_FillError);
 493                         blnReadDBData_Fill = false;
 494                         try {
 495                                 BeginCase("FillError");
 496                                 try {
 497                                         dbDA.Fill(ds,"Customers");
 498                                 }
 499                                 catch (Exception ){};
 500                                 Compare(blnReadDBData_Fill ,true );
 501                         }
 502                         catch(Exception ex)     {exp = ex;}
 503                         finally {EndCase(exp); exp = null;}
 504                         dbDA.FillError -= new FillErrorEventHandler(dbDA_FillError);
 505
 506                         //close connection
 507                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
 508                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
 509                 }
 510                 private void dbDA_FillError(object sender, FillErrorEventArgs args) {
 511                         blnReadDBData_Fill = true;
 512                         args.Continue = false;
 513                 }
 514
 515                 #endregion
 516
 517                 #region " DBDataAdapter - Update "
 518                 protected void DbDataAdapter_Update_Ds(Sys.Data.Common.DbDataAdapter dbDA) {
 519                         int NumberOfAffectedRows = 0;
 520                         Exception exp = null;
 521
 522                         // --------- get data from DB -----------------
 523                         DataSet ds = PrepareDBData_Update(dbDA);
 524
 525                         // --------- prepare dataset for update method -----------------
 526                         DataSet dsDB1 = ds.Copy();
 527
 528                         // --------- prepare dataset for DBConcurrencyException -----------------
 529                         DataSet dsDB2 = ds.Copy();
 530
 531                         //update expecteed dataset
 532                         dsDB2.Tables[0].Rows.Add(new object[] {9994,"Ofer", "Borshtein", "Delete"});
 533                         dsDB2.Tables[0].Rows.Add(new object[] {9995,"Ofer", "Borshtein", "Update"});
 534                         dsDB2.Tables[0].Rows.Find(9996).Delete();
 535                         dsDB2.AcceptChanges();
 536
 537                         //make changes to the DataBase (through the dataset)
 538                         dsDB1.Tables[0].Rows.Add(new object[] {9991,"Ofer","Borshtein","Insert"});
 539                         dsDB1.Tables[0].Rows.Find(9992).Delete();
 540                         dsDB1.Tables[0].Rows.Find(9993)["Title"] = "Jack the ripper";
 541
 542                         //execute update to db
 543                         NumberOfAffectedRows = dbDA.Update(dsDB1);
 544
 545                         try {
 546                                 BeginCase("Number Of Affected Rows");
 547                                 Compare(NumberOfAffectedRows ,3 );
 548                         }
 549                         catch(Exception ex)     {exp = ex;}
 550                         finally {EndCase(exp); exp = null;}
 551
 552
 553                         //get result from db in order to check them
 554                         DataSet dsExpected = new DataSet(); //ds.Reset();
 555                         dbDA.Fill(dsExpected);
 556                         dsExpected.Tables[0].PrimaryKey = new DataColumn[] {dsExpected.Tables[0].Columns["EmployeeID"]};
 557
 558                         CompareResults_Update(dsDB1,dsDB2,ref dbDA);
 559                         CompareResults_Update_Ds_Exception(dsDB2,ref dbDA);
 560
 561                         //Create rows which not exists in the DB but exists in the DS with row state = deleted
 562                         //this will cause the Update to fail.
 563                         dsDB1.Tables[0].Rows.Add(new object[] {9997,"Ofer", "Borshtein", "Delete"});
 564                         dsDB1.Tables[0].Rows.Add(new object[] {9998,"Ofer", "Borshtein", "Delete"});
 565                         dsDB1.AcceptChanges();
 566                         dsDB1.Tables[0].Rows.Find(9997).Delete();
 567                         dsDB1.Tables[0].Rows.Find(9998).Delete();
 568
 569                         //Check Sys.Data.DBConcurrencyException
 570                         //The exception that is thrown by the DataAdapter during the update operation if the number of rows affected equals zero.
 571                         try {
 572                                 BeginCase("Check DBConcurrencyException");
 573                                 try {
 574                                         NumberOfAffectedRows = dbDA.Update(dsDB1);
 575                                 }
 576                                 catch (DBConcurrencyException ex) {exp=ex;}
 577                                 Compare(exp.GetType(),typeof(DBConcurrencyException) );
 578                                 exp = null;
 579                         }
 580                         catch(Exception ex)     {exp = ex;}
 581                         finally {EndCase(exp); exp = null;}
 582
 583                         //close connection
 584                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
 585                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
 586
 587
 588                 }
 589
 590                 private void CompareResults_Update_Ds_Exception(DataSet dsResultException,ref Sys.Data.Common.DbDataAdapter dbDA) {
 591                         int NumberOfAffectedRows = 0;
 592                         Exception exp = null;
 593                         Exception e = null;
 594
 595
 596                         // --------- check for DBConcurrencyException /UniqueConstraint -----------------
 597                         //      call AcceptChanges after each exception check in order to make sure that we check only the the current row
 598
 599
 600                         try {
 601                                 BeginCase("DBConcurrencyException - Delete");
 602                                 dsResultException.Tables[0].Rows.Find(9994).Delete();
 603                                 //no row with row version delete exists - records affected = 0
 604                                 NumberOfAffectedRows = -1;
 605                                 try {NumberOfAffectedRows = dbDA.Update(dsResultException);}
 606                                 catch (DBConcurrencyException dbExp){e = dbExp;}
 607                                 Compare(e.GetType(),typeof(DBConcurrencyException));
 608                         }
 609                         catch(Exception ex)     {exp = ex;}
 610                         finally {EndCase(exp); exp = null; e = null;}
 611                         try {
 612                                 BeginCase("Number Of Affected Rows - Delete Exception");
 613                                 Compare(NumberOfAffectedRows ,-1 );
 614                         }
 615                         catch(Exception ex)     {exp = ex;}
 616                         finally {EndCase(exp); exp = null;}
 617                         dsResultException.AcceptChanges();
 618
 619                         try {
 620                                 BeginCase("DBConcurrencyException - Update");
 621                                 dsResultException.Tables[0].Rows.Find(9995)["Title"] = "Jack the ripper";
 622                                 //no row with row version Update exists - records affected = 0
 623                                 NumberOfAffectedRows = -1;
 624                                 try {NumberOfAffectedRows = dbDA.Update(dsResultException);}
 625                                 catch (DBConcurrencyException dbExp){e = dbExp;}
 626                                 Compare(e.GetType(),typeof(DBConcurrencyException));
 627                         }
 628                         catch(Exception ex)     {exp = ex;}
 629                         finally {EndCase(exp); exp = null; e = null;}
 630                         try {
 631                                 BeginCase("Number Of Affected Rows - Update Exception");
 632                                 Compare(NumberOfAffectedRows ,-1 );
 633                         }
 634                         catch(Exception ex)     {exp = ex;}
 635                         finally {EndCase(exp); exp = null;}
 636                         dsResultException.AcceptChanges();
 637
 638
 639                         try {
 640                                 BeginCase("DBConcurrencyException - Insert");
 641                                 dsResultException.Tables[0].Rows.Add(new object[] {9996,"Ofer","Borshtein","Insert"});
 642                                 //no row with row version Insert exists - records affected = 0
 643                                 NumberOfAffectedRows = -1;
 644                                 try {NumberOfAffectedRows = dbDA.Update(dsResultException);}
 645                                 catch (Exception dbExp){e = dbExp;} //throw Sys.Exception
 646                                 Compare(e != null ,true);
 647                         }
 648                         catch(Exception ex)     {exp = ex;}
 649                         finally {EndCase(exp); exp = null; e = null;}
 650                         try {
 651                                 BeginCase("Number Of Affected Rows - Insert Exception");
 652                                 Compare(NumberOfAffectedRows ,-1 );
 653                         }
 654                         catch(Exception ex)     {exp = ex;}
 655                         finally {EndCase(exp); exp = null;}
 656                         dsResultException.AcceptChanges();
 657
 658
 659                 }
 660
 661
 662                 protected void DbDataAdapter_Update_Dt(Sys.Data.Common.DbDataAdapter dbDA) {
 663                         int NumberOfAffectedRows = 0;
 664                         Exception exp = null;
 665
 666                         // --------- get data from DB -----------------
 667                         DataSet ds = PrepareDBData_Update(dbDA);
 668
 669                         // --------- prepare dataset for update method -----------------
 670                         DataSet dsDB1 = ds.Copy();
 671
 672                         // --------- prepare dataset for DBConcurrencyException -----------------
 673                         DataSet dsDB2 = ds.Copy();
 674
 675                         //update dataset
 676                         dsDB2.Tables[0].Rows.Add(new object[] {9994,"Ofer", "Borshtein", "Delete"});
 677                         dsDB2.Tables[0].Rows.Add(new object[] {9995,"Ofer", "Borshtein", "Update"});
 678                         dsDB2.Tables[0].Rows.Find(9996).Delete();
 679                         dsDB2.AcceptChanges();
 680
 681
 682                         dsDB1.Tables[0].Rows.Add(new object[] {9991,"Ofer","Borshtein","Insert"});
 683                         dsDB1.Tables[0].Rows.Find(9992).Delete();
 684                         dsDB1.Tables[0].Rows.Find(9993)["Title"] = "Jack the ripper";
 685
 686                         //execute update to db
 687                         NumberOfAffectedRows = dbDA.Update(dsDB1.Tables[0]);
 688
 689                         try {
 690                                 BeginCase("Number Of Affected Rows");
 691                                 Compare(NumberOfAffectedRows ,3 );
 692                         }
 693                         catch(Exception ex)     {exp = ex;}
 694                         finally {EndCase(exp); exp = null;}
 695
 696
 697                         //get result from db in order to check them
 698                         DataSet dsExpected = new DataSet(); //ds.Reset();
 699                         dbDA.Fill(dsExpected);
 700                         dsExpected.Tables[0].PrimaryKey = new DataColumn[] {dsExpected.Tables[0].Columns["EmployeeID"]};
 701
 702                         CompareResults_Update(dsDB1,dsDB2,ref dbDA);
 703                         CompareResults_Update_Dt_Exception(dsDB2,ref dbDA);
 704
 705                         //Create rows which not exists in the DB but exists in the DS with row state = deleted
 706                         //this will cause the Update to fail.
 707                         dsDB1.Tables[0].Rows.Add(new object[] {9997,"Ofer", "Borshtein", "Delete"});
 708                         dsDB1.Tables[0].Rows.Add(new object[] {9998,"Ofer", "Borshtein", "Delete"});
 709                         dsDB1.AcceptChanges();
 710                         dsDB1.Tables[0].Rows.Find(9997).Delete();
 711                         dsDB1.Tables[0].Rows.Find(9998).Delete();
 712
 713                         //Check Sys.Data.DBConcurrencyException
 714                         //The exception that is thrown by the DataAdapter during the update operation if the number of rows affected equals zero.
 715                         try {
 716                                 BeginCase("Check DBConcurrencyException");
 717                                 try {
 718                                         NumberOfAffectedRows = dbDA.Update(dsDB1.Tables[0]);
 719                                 }
 720                                 catch (DBConcurrencyException ex) {exp=ex;}
 721                                 Compare(exp.GetType(),typeof(DBConcurrencyException) );
 722                                 exp = null;
 723                         }
 724                         catch(Exception ex)     {exp = ex;}
 725                         finally {EndCase(exp); exp = null;}
 726
 727                         //close connection
 728                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
 729                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
 730
 731
 732                 }
 733
 734                 private void CompareResults_Update_Dt_Exception(DataSet dsResultException,ref Sys.Data.Common.DbDataAdapter dbDA) {
 735                         int NumberOfAffectedRows = 0;
 736                         Exception exp = null;
 737                         Exception e = null;
 738
 739
 740                         // --------- check for DBConcurrencyException /UniqueConstraint -----------------
 741                         //      call AcceptChanges after each exception check in order to make sure that we check only the the current row
 742
 743
 744                         try {
 745                                 BeginCase("DBConcurrencyException - Delete");
 746                                 dsResultException.Tables[0].Rows.Find(9994).Delete();
 747                                 //no row with row version delete exists - records affected = 0
 748                                 NumberOfAffectedRows = -1;
 749                                 try {NumberOfAffectedRows = dbDA.Update(dsResultException.Tables[0]);}
 750                                 catch (DBConcurrencyException dbExp){e = dbExp;}
 751                                 Compare(e.GetType(),typeof(DBConcurrencyException));
 752                         }
 753                         catch(Exception ex)     {exp = ex;}
 754                         finally {EndCase(exp); exp = null; e = null;}
 755                         try {
 756                                 BeginCase("Number Of Affected Rows - Delete Exception");
 757                                 Compare(NumberOfAffectedRows ,-1 );
 758                         }
 759                         catch(Exception ex)     {exp = ex;}
 760                         finally {EndCase(exp); exp = null;}
 761                         dsResultException.AcceptChanges();
 762
 763                         try {
 764                                 BeginCase("DBConcurrencyException - Update");
 765                                 dsResultException.Tables[0].Rows.Find(9995)["Title"] = "Jack the ripper";
 766                                 //no row with row version Update exists - records affected = 0
 767                                 NumberOfAffectedRows = -1;
 768                                 try {NumberOfAffectedRows = dbDA.Update(dsResultException.Tables[0]);}
 769                                 catch (DBConcurrencyException dbExp){e = dbExp;}
 770                                 Compare(e.GetType(),typeof(DBConcurrencyException));
 771                         }
 772                         catch(Exception ex)     {exp = ex;}
 773                         finally {EndCase(exp); exp = null; e = null;}
 774                         try {
 775                                 BeginCase("Number Of Affected Rows - Update Exception");
 776                                 Compare(NumberOfAffectedRows ,-1 );
 777                         }
 778                         catch(Exception ex)     {exp = ex;}
 779                         finally {EndCase(exp); exp = null;}
 780                         dsResultException.AcceptChanges();
 781
 782
 783                         try {
 784                                 BeginCase("DBConcurrencyException - Insert");
 785                                 dsResultException.Tables[0].Rows.Add(new object[] {9996,"Ofer","Borshtein","Insert"});
 786                                 //no row with row version Insert exists - records affected = 0
 787                                 NumberOfAffectedRows = -1;
 788                                 try {NumberOfAffectedRows = dbDA.Update(dsResultException.Tables[0]);}
 789                                 catch (Exception dbExp){e = dbExp;} //throw Sys.Exception
 790                                 Compare(e != null ,true);
 791                         }
 792                         catch(Exception ex)     {exp = ex;}
 793                         finally {EndCase(exp); exp = null; e = null;}
 794                         try {
 795                                 BeginCase("Number Of Affected Rows - Insert Exception");
 796                                 Compare(NumberOfAffectedRows ,-1 );
 797                         }
 798                         catch(Exception ex)     {exp = ex;}
 799                         finally {EndCase(exp); exp = null;}
 800                         dsResultException.AcceptChanges();
 801
 802                 }
 803
 804
 805                 protected void DbDataAdapter_Update_Dr(Sys.Data.Common.DbDataAdapter dbDA) {
 806                         int NumberOfAffectedRows = 0;
 807                         Exception exp = null;
 808
 809                         // --------- get data from DB -----------------
 810                         DataSet ds = PrepareDBData_Update(dbDA);
 811
 812                         // --------- prepare dataset for update method -----------------
 813                         DataSet dsDB1 = ds.Copy();
 814
 815                         // --------- prepare dataset for DBConcurrencyException -----------------
 816                         DataSet dsDB2 = ds.Copy();
 817
 818                         //update dataset
 819                         dsDB2.Tables[0].Rows.Add(new object[] {9994,"Ofer", "Borshtein", "Delete"});
 820                         dsDB2.Tables[0].Rows.Add(new object[] {9995,"Ofer", "Borshtein", "Update"});
 821                         dsDB2.Tables[0].Rows.Find(9996).Delete();
 822                         dsDB2.AcceptChanges();
 823
 824
 825                         dsDB1.Tables[0].Rows.Add(new object[] {9991,"Ofer","Borshtein","Insert"});
 826                         dsDB1.Tables[0].Rows.Find(9992).Delete();
 827                         dsDB1.Tables[0].Rows.Find(9993)["Title"] = "Jack the ripper";
 828
 829                         //execute update to db
 830
 831                         DataRow[] drArr = new DataRow[dsDB1.Tables[0].Rows.Count] ;
 832                         dsDB1.Tables[0].Rows.CopyTo(drArr,0);
 833                         NumberOfAffectedRows = dbDA.Update(drArr);
 834
 835                         try {
 836                                 BeginCase("Number Of Affected Rows");
 837                                 Compare(NumberOfAffectedRows ,3 );
 838                         }
 839                         catch(Exception ex)     {exp = ex;}
 840                         finally {EndCase(exp); exp = null;}
 841
 842
 843                         //get result from db in order to check them
 844                         DataSet dsExpected = new DataSet(); //ds.Reset();
 845                         dbDA.Fill(dsExpected);
 846                         dsExpected.Tables[0].PrimaryKey = new DataColumn[] {dsExpected.Tables[0].Columns["EmployeeID"]};
 847
 848                         CompareResults_Update(dsDB1,dsDB2,ref dbDA);
 849                         CompareResults_Update_Dr_Exception(dsDB2,ref dbDA);
 850
 851                         //Create rows which not exists in the DB but exists in the DS with row state = deleted
 852                         //this will cause the Update to fail.
 853                         dsDB1.Tables[0].Rows.Add(new object[] {9997,"Ofer", "Borshtein", "Delete"});
 854                         dsDB1.Tables[0].Rows.Add(new object[] {9998,"Ofer", "Borshtein", "Delete"});
 855                         dsDB1.AcceptChanges();
 856                         dsDB1.Tables[0].Rows.Find(9997).Delete();
 857                         dsDB1.Tables[0].Rows.Find(9998)[1] = "Updated!";
 858
 859                         drArr = new DataRow[dsDB1.Tables[0].Rows.Count];
 860                         dsDB1.Tables[0].Rows.CopyTo(drArr,0);
 861
 862                         //Check Sys.Data.DBConcurrencyException
 863                         //The exception that is thrown by the DataAdapter during the update operation if the number of rows affected equals zero.
 864                         try {
 865                                 BeginCase("Check DBConcurrencyException");
 866                                 try {
 867                                         NumberOfAffectedRows = dbDA.Update(drArr);
 868                                 }
 869                                 catch (DBConcurrencyException ex) {exp=ex;}
 870                                 Compare(exp.GetType(),typeof(DBConcurrencyException) );
 871                                 exp = null;
 872                         }
 873                         catch(Exception ex)     {exp = ex;}
 874                         finally {EndCase(exp); exp = null;}
 875
 876                         //close connection
 877                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
 878                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
 879
 880
 881                 }
 882
 883                 private void CompareResults_Update_Dr_Exception(DataSet dsResultException,ref Sys.Data.Common.DbDataAdapter dbDA) {
 884                         int NumberOfAffectedRows = 0;
 885                         Exception exp = null;
 886                         Exception e = null;
 887
 888                         DataRow[] drArr = new DataRow[dsResultException.Tables[0].Rows.Count];
 889
 890                         // --------- check for DBConcurrencyException /UniqueConstraint -----------------
 891                         //      call AcceptChanges after each exception check in order to make sure that we check only the the current row
 892
 893                         try {
 894                                 BeginCase("DBConcurrencyException - Delete");
 895                                 dsResultException.Tables[0].Rows.Find(9994).Delete();
 896                                 dsResultException.Tables[0].Rows.CopyTo(drArr,0);
 897                                 //no row with row version delete exists - records affected = 0
 898                                 NumberOfAffectedRows = -1;
 899                                 try {NumberOfAffectedRows = dbDA.Update(drArr);}
 900                                 catch (DBConcurrencyException dbExp){e = dbExp;}
 901                                 Compare(e.GetType(),typeof(DBConcurrencyException));
 902                         }
 903                         catch(Exception ex)     {exp = ex;}
 904                         finally {EndCase(exp); exp = null; e = null;}
 905                         try {
 906                                 BeginCase("Number Of Affected Rows - Delete Exception");
 907                                 Compare(NumberOfAffectedRows ,-1 );
 908                         }
 909                         catch(Exception ex)     {exp = ex;}
 910                         finally {EndCase(exp); exp = null;}
 911                         dsResultException.AcceptChanges();
 912
 913                         try {
 914                                 BeginCase("DBConcurrencyException - Update");
 915                                 dsResultException.Tables[0].Rows.Find(9995)["Title"] = "Jack the ripper";
 916                                 dsResultException.Tables[0].Rows.CopyTo(drArr,0);
 917                                 //no row with row version Update exists - records affected = 0
 918                                 NumberOfAffectedRows = -1;
 919                                 try {NumberOfAffectedRows = dbDA.Update(drArr);}
 920                                 catch (DBConcurrencyException dbExp){e = dbExp;}
 921                                 Compare(e.GetType(),typeof(DBConcurrencyException));
 922                         }
 923                         catch(Exception ex)     {exp = ex;}
 924                         finally {EndCase(exp); exp = null; e = null;}
 925                         try {
 926                                 BeginCase("Number Of Affected Rows - Update Exception");
 927                                 Compare(NumberOfAffectedRows ,-1 );
 928                         }
 929                         catch(Exception ex)     {exp = ex;}
 930                         finally {EndCase(exp); exp = null;}
 931                         dsResultException.AcceptChanges();
 932
 933
 934                         try {
 935                                 BeginCase("DBConcurrencyException - Insert");
 936                                 dsResultException.Tables[0].Rows.Add(new object[] {9996,"Ofer","Borshtein","Insert"});
 937                                 dsResultException.Tables[0].Rows.CopyTo(drArr,0);
 938                                 //no row with row version Insert exists - records affected = 0
 939                                 NumberOfAffectedRows = -1;
 940                                 try {NumberOfAffectedRows = dbDA.Update(drArr);}
 941                                 catch (Exception dbExp){e = dbExp;} //throw Sys.Exception
 942                                 Compare(e != null ,true);
 943                         }
 944                         catch(Exception ex)     {exp = ex;}
 945                         finally {EndCase(exp); exp = null; e = null;}
 946                         try {
 947                                 BeginCase("Number Of Affected Rows - Insert Exception");
 948                                 Compare(NumberOfAffectedRows ,-1 );
 949                         }
 950                         catch(Exception ex)     {exp = ex;}
 951                         finally {EndCase(exp); exp = null;}
 952                         dsResultException.AcceptChanges();
 953
 954                 }
 955
 956
 957                 protected void DbDataAdapter_Update_Ds_Str(Sys.Data.Common.DbDataAdapter dbDA) {
 958                         int NumberOfAffectedRows = 0;
 959                         Exception exp = null;
 960
 961                         // --------- get data from DB -----------------
 962                         DataSet ds = PrepareDBData_Update(dbDA);
 963
 964                         // --------- prepare dataset for update method -----------------
 965                         DataSet dsDB1 = ds.Copy();
 966
 967                         // --------- prepare dataset for DBConcurrencyException -----------------
 968                         DataSet dsDB2 = ds.Copy();
 969
 970                         //update dataset
 971                         dsDB2.Tables[0].Rows.Add(new object[] {9994,"Ofer", "Borshtein", "Delete"});
 972                         dsDB2.Tables[0].Rows.Add(new object[] {9995,"Ofer", "Borshtein", "Update"});
 973                         dsDB2.Tables[0].Rows.Find(9996).Delete();
 974                         dsDB2.AcceptChanges();
 975
 976
 977                         dsDB1.Tables[0].Rows.Add(new object[] {9991,"Ofer","Borshtein","Insert"});
 978                         dsDB1.Tables[0].Rows.Find(9992).Delete();
 979                         dsDB1.Tables[0].Rows.Find(9993)["Title"] = "Jack the ripper";
 980
 981                         //execute update to db
 982                         NumberOfAffectedRows = dbDA.Update(dsDB1,dsDB1.Tables[0].TableName);
 983
 984                         try {
 985                                 BeginCase("Number Of Affected Rows");
 986                                 Compare(NumberOfAffectedRows ,3 );
 987                         }
 988                         catch(Exception ex)     {exp = ex;}
 989                         finally {EndCase(exp); exp = null;}
 990
 991
 992                         //get result from db in order to check them
 993                         DataSet dsExpected = new DataSet(); //ds.Reset();
 994                         dbDA.Fill(dsExpected);
 995                         dsExpected.Tables[0].PrimaryKey = new DataColumn[] {dsExpected.Tables[0].Columns["EmployeeID"]};
 996
 997                         CompareResults_Update(dsDB1,dsDB2,ref dbDA);
 998                         CompareResults_Update_Ds_Str_Exception(dsDB2,ref dbDA);
 999
1000                         //Create rows which not exists in the DB but exists in the DS with row state = deleted
1001                         //this will cause the Update to fail.
1002                         dsDB1.Tables[0].Rows.Add(new object[] {9997,"Ofer", "Borshtein", "Delete"});
1003                         dsDB1.Tables[0].Rows.Add(new object[] {9998,"Ofer", "Borshtein", "Delete"});
1004                         dsDB1.AcceptChanges();
1005                         dsDB1.Tables[0].Rows.Find(9997).Delete();
1006                         dsDB1.Tables[0].Rows.Find(9998).Delete();
1007
1008
1009                         //Check Sys.Data.DBConcurrencyException
1010                         //The exception that is thrown by the DataAdapter during the update operation if the number of rows affected equals zero.
1011                         try {
1012                                 BeginCase("Check DBConcurrencyException");
1013                                 try {
1014                                         NumberOfAffectedRows = dbDA.Update(dsDB1,dsDB1.Tables[0].TableName);
1015                                 }
1016                                 catch (DBConcurrencyException ex) {exp=ex;}
1017                                 Compare(exp.GetType(),typeof(DBConcurrencyException) );
1018                                 exp = null;
1019                         }
1020                         catch(Exception ex)     {exp = ex;}
1021                         finally {EndCase(exp); exp = null;}
1022
1023                         //close connection
1024                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
1025                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
1026
1027
1028                 }
1029
1030                 private void CompareResults_Update_Ds_Str_Exception(DataSet dsResultException,ref Sys.Data.Common.DbDataAdapter dbDA) {
1031                         int NumberOfAffectedRows = 0;
1032                         Exception exp = null;
1033                         Exception e = null;
1034
1035
1036                         // --------- check for DBConcurrencyException /UniqueConstraint -----------------
1037                         //      call AcceptChanges after each exception check in order to make sure that we check only the the current row
1038
1039
1040                         try {
1041                                 BeginCase("DBConcurrencyException - Delete");
1042                                 dsResultException.Tables[0].Rows.Find(9994).Delete();
1043                                 //no row with row version delete exists - records affected = 0
1044                                 NumberOfAffectedRows = -1;
1045                                 try {NumberOfAffectedRows = dbDA.Update(dsResultException,dsResultException.Tables[0].TableName);}
1046                                 catch (DBConcurrencyException dbExp){e = dbExp;}
1047                                 Compare(e.GetType(),typeof(DBConcurrencyException));
1048                         }
1049                         catch(Exception ex)     {exp = ex;}
1050                         finally {EndCase(exp); exp = null; e = null;}
1051                         try {
1052                                 BeginCase("Number Of Affected Rows - Delete Exception");
1053                                 Compare(NumberOfAffectedRows ,-1 );
1054                         }
1055                         catch(Exception ex)     {exp = ex;}
1056                         finally {EndCase(exp); exp = null;}
1057                         dsResultException.AcceptChanges();
1058
1059                         try {
1060                                 BeginCase("DBConcurrencyException - Update");
1061                                 dsResultException.Tables[0].Rows.Find(9995)["Title"] = "Jack the ripper";
1062                                 //no row with row version Update exists - records affected = 0
1063                                 NumberOfAffectedRows = -1;
1064                                 try {NumberOfAffectedRows = dbDA.Update(dsResultException,dsResultException.Tables[0].TableName);}
1065                                 catch (DBConcurrencyException dbExp){e = dbExp;}
1066                                 Compare(e.GetType(),typeof(DBConcurrencyException));
1067                         }
1068                         catch(Exception ex)     {exp = ex;}
1069                         finally {EndCase(exp); exp = null; e = null;}
1070                         try {
1071                                 BeginCase("Number Of Affected Rows - Update Exception");
1072                                 Compare(NumberOfAffectedRows ,-1 );
1073                         }
1074                         catch(Exception ex)     {exp = ex;}
1075                         finally {EndCase(exp); exp = null;}
1076                         dsResultException.AcceptChanges();
1077
1078
1079                         try {
1080                                 BeginCase("DBConcurrencyException - Insert");
1081                                 dsResultException.Tables[0].Rows.Add(new object[] {9996,"Ofer","Borshtein","Insert"});
1082                                 //no row with row version Insert exists - records affected = 0
1083                                 NumberOfAffectedRows = -1;
1084                                 try {NumberOfAffectedRows = dbDA.Update(dsResultException,dsResultException.Tables[0].TableName);}
1085                                 catch (Exception dbExp){e = dbExp;} //throw Sys.Exception
1086                                 Compare(e != null ,true);
1087                         }
1088                         catch(Exception ex)     {exp = ex;}
1089                         finally {EndCase(exp); exp = null; e = null;}
1090                         try {
1091                                 BeginCase("Number Of Affected Rows - Insert Exception");
1092                                 Compare(NumberOfAffectedRows ,-1 );
1093                         }
1094                         catch(Exception ex)     {exp = ex;}
1095                         finally {EndCase(exp); exp = null;}
1096                         dsResultException.AcceptChanges();
1097
1098
1099                 }
1100
1101                 /*
1102                         *
1103                         * insert/update the database with data that will be used in testings
1104                         *
1105                         */
1106                 protected void PrepareDataForTesting(string ConnectionString) {
1107                         int iExists = 0;
1108                         try {
1109
1110                                 //create database specific date value
1111                                 string strBirthDateValue = "";
1112                                 switch (ConnectedDataProvider.GetDbType(ConnectionString)) {
1113                                         case DataBaseServer.DB2:
1114                                                 strBirthDateValue = "'1988-05-31-15.33.44'";
1115                                                 break;
1116                                         case DataBaseServer.Oracle:
1117                                                 strBirthDateValue = "to_date('1988-05-31 15:33:44', 'yyyy-mm-dd HH24:mi:ss')";
1118                                                 break;
1119                                         case DataBaseServer.PostgreSQL:
1120                                                 strBirthDateValue = "to_timestamp('1988-05-31 15:33:44', 'yyyy-mm-dd HH24:mi:ss')";
1121                                                 break;
1122                                         case DataBaseServer.SQLServer:
1123                                         case DataBaseServer.Sybase:
1124                                                 strBirthDateValue = "'1988-May-31 15:33:44'";
1125                                                 break;
1126                                 }
1127                                 OleDbConnection con = new OleDbConnection(ConnectionString);
1128                                 try {
1129                                         con.Open();
1130                                 }
1131                                 catch (Exception ex) {
1132                                         throw new Exception("PrepareDataForTesting failed trying to connect to DB using Connection string:" + ConnectionString + "\nException:" +  ex.ToString(),ex);
1133                                 }
1134
1135                                 OleDbCommand cmd = new OleDbCommand("",con);
1136
1137                                 //update / insert to table Employees
1138                                 for (int i = 100; i <= 700; i +=100) {
1139                                         cmd.CommandText = "select 1 from Employees where EmployeeID = " + i.ToString();
1140                                         iExists =  Sys.Convert.ToInt32(cmd.ExecuteScalar());
1141                                         if (iExists != 1) {
1142                                                 cmd.CommandText = "insert into Employees (EmployeeID, LastName, FirstName, Title, BirthDate) " +
1143                                                         " Values (" + i.ToString() +
1144                                                         ",'Last" + i.ToString()
1145                                                         + "','First" + i.ToString()
1146                                                         + "', null, "
1147                                                         + strBirthDateValue + ")";
1148                                                 cmd.ExecuteNonQuery();
1149                                         }
1150                                         else {
1151                                                 cmd.CommandText = "update Employees set "
1152                                                         + " LastName = 'Last" + i.ToString()
1153                                                         + "', FirstName = 'First" + i.ToString()
1154                                                         + "', Title = null, BirthDate = "
1155                                                         + strBirthDateValue
1156                                                         + " where EmployeeID = " + i.ToString() ;
1157                                                 Log(cmd.CommandText);
1158                                                 cmd.ExecuteNonQuery();
1159                                         }
1160                                 }
1161
1162                                 //update / insert to table Customers
1163                                 for (int i = 100; i <= 700; i +=100) {
1164                                         cmd.CommandText = "select 1 from Customers where CustomerID = 'GH" + i.ToString() + "'";
1165                                         iExists =  Sys.Convert.ToInt32(cmd.ExecuteScalar());
1166                                         if (iExists != 1) {
1167                                                 cmd.CommandText = "insert into Customers (CustomerID , CompanyName, Phone) Values ('GH" + i.ToString() + "','Company" + i.ToString() + "','00-" + i.ToString() + i.ToString() + "')";
1168                                                 cmd.ExecuteNonQuery();
1169                                         }
1170                                         else {
1171                                                 cmd.CommandText = "update Customers set CompanyName = 'Company" + i.ToString() + "', Phone = '00-" + i.ToString() + i.ToString() + "' where CustomerID = 'GH" + i.ToString() + "'";
1172                                                 cmd.ExecuteNonQuery();
1173                                         }
1174                                 }
1175
1176                                 cmd.CommandText = "select 1 from Customers where CustomerID = 'GH200'";
1177                                 iExists =  Sys.Convert.ToInt32(cmd.ExecuteScalar());
1178                                 if (iExists != 1) {
1179                                         cmd.CommandText = "insert into Customers (CustomerID , CompanyName) Values ('GH200','Company200')";
1180                                         cmd.ExecuteNonQuery();
1181                                 }
1182                                 else {
1183                                         cmd.CommandText = "update Customers set CompanyName = 'Company200' where CustomerID = 'GH200'";
1184                                         cmd.ExecuteNonQuery();
1185                                 }
1186
1187                                 con.Close();
1188                         }
1189                         catch (Exception ex) {
1190                                 throw new Exception("PrepareDataForTesting failed with exception:" + ex.ToString(),ex);
1191                         }
1192                 }
1193
1194                 /*
1195                 *       used to insert data to the database in order to check DataAdapter Update metods
1196                 */
1197                 protected DataSet PrepareDBData_Update(Sys.Data.Common.DbDataAdapter dbDA) {
1198                         return PrepareDBData_Update(dbDA,false);
1199
1200                 }
1201                 protected DataSet PrepareDBData_Update(Sys.Data.Common.DbDataAdapter dbDA,bool sqlConnectionString) {
1202                         IDbDataAdapter Ida = (IDbDataAdapter)dbDA;
1203                         IDbCommand ICmd = Ida.SelectCommand;
1204                         ICmd.CommandText = "SELECT EmployeeID, LastName, FirstName, Title FROM Employees WHERE EmployeeID in (9991,9992,9993,9994,9995,9996)";
1205                         IDbConnection IConn = ICmd.Connection;
1206                         if (!sqlConnectionString) {
1207                                 IConn.ConnectionString = MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString;
1208                         }
1209                         IConn.Open();
1210
1211                         //Insert rows to be updated (insert,delete,update)
1212                         IDbCommand cmd;
1213                         if (sqlConnectionString) {
1214                                 cmd = new Sys.Data.SqlClient.SqlCommand();
1215                                 cmd.Connection = (Sys.Data.SqlClient.SqlConnection)IConn;
1216                         }
1217                         else {
1218                                 cmd = new Sys.Data.OleDb.OleDbCommand();
1219                                 cmd.Connection = (Sys.Data.OleDb.OleDbConnection)IConn;
1220                         }
1221
1222
1223                         //run execute after each command because DB2 doesn't support multiple commands
1224                         cmd.CommandText =  "DELETE FROM Employees WHERE EmployeeID in (9991,9992,9993,9994,9995,9996,9997,9998)";
1225                         cmd.ExecuteNonQuery();
1226
1227                         //only for SQL Server
1228                         DataBaseServer DBType =  ConnectedDataProvider.GetDbType(IConn.ConnectionString);
1229
1230                         cmd.CommandText = "INSERT INTO Employees (EmployeeID, LastName, FirstName, Title) VALUES(9992, 'Ofer', 'Borshtein', 'delete')";
1231                         //if (DBType == DataBaseServer.SQLServer) cmd.CommandText = "SET IDENTITY_INSERT Employees ON;" + cmd.CommandText;
1232                         cmd.ExecuteNonQuery();
1233                         cmd.CommandText = "INSERT INTO Employees (EmployeeID, LastName, FirstName, Title) VALUES(9993, 'Ofer', 'Borshtein', 'Update')";
1234                         //if (DBType == DataBaseServer.SQLServer) cmd.CommandText = "SET IDENTITY_INSERT Employees ON;" + cmd.CommandText;
1235                         cmd.ExecuteNonQuery();
1236                         cmd.CommandText = "INSERT INTO Employees (EmployeeID, LastName, FirstName, Title) VALUES(9996, 'Ofer', 'Borshtein', 'Exp')";
1237                         //if (DBType == DataBaseServer.SQLServer) cmd.CommandText = "SET IDENTITY_INSERT Employees ON;" + cmd.CommandText;
1238                         cmd.ExecuteNonQuery();
1239
1240                         //cmd.CommandText += "INSERT INTO Employees (EmployeeID, LastName, FirstName, Title) VALUES(9991, 'Ofer', 'Borshtein', 'Insert'); ";
1241                         //cmd.CommandText += "INSERT INTO Employees (EmployeeID, LastName, FirstName, Title) VALUES(9994, 'Ofer', 'Borshtein', 'Exp'); ";
1242                         //cmd.CommandText += "INSERT INTO Employees (EmployeeID, LastName, FirstName, Title) VALUES(9995, 'Ofer', 'Borshtein', 'Exp'); ";
1243                         //cmd.CommandText += "INSERT INTO Employees (EmployeeID, LastName, FirstName, Title) VALUES(9997, 'Ofer', 'Borshtein', 'delete'); ";
1244                         //cmd.CommandText += "INSERT INTO Employees (EmployeeID, LastName, FirstName, Title) VALUES(9998, 'Ofer', 'Borshtein', 'delete'); ";
1245
1246                         DataSet ds = new DataSet();
1247                         dbDA.Fill(ds);
1248                         ds.Tables[0].PrimaryKey = new DataColumn[] {ds.Tables[0].Columns["EmployeeID"]};
1249                         return ds;
1250
1251                 }
1252
1253
1254                 private void CompareResults_Update(DataSet dsResult,DataSet dsResultException,ref Sys.Data.Common.DbDataAdapter dbDA) {
1255                         Exception exp = null;
1256
1257                         //----------------- Compare dsDB with dsExcepted -----------------
1258                         try {
1259                                 BeginCase("Insert Row ");
1260                                 Compare(dsResult.Tables[0].Rows.Find(9991) == null ,false );
1261                         }
1262                         catch(Exception ex)     {exp = ex;}
1263                         finally {EndCase(exp); exp = null;}
1264
1265                         try {
1266                                 BeginCase("Update Row ");
1267                                 Compare(dsResult.Tables[0].Rows.Find(9993)["Title"],"Jack the ripper");
1268                         }
1269                         catch(Exception ex)     {exp = ex;}
1270                         finally {EndCase(exp); exp = null;}
1271
1272                         try {
1273                                 BeginCase("Delete Row ");
1274                                 Compare(dsResult.Tables[0].Rows.Find(9992) ,null);
1275                         }
1276                         catch(Exception ex)     {exp = ex;}
1277                         finally {EndCase(exp); exp = null;}
1278
1279                 }
1280
1281                 #endregion
1282
1283                 protected void DBDataAdapter_DefaultSourceTableName() {
1284                         Exception exp = null;
1285                         try {
1286                                 BeginCase("DefaultSourceTableName");
1287                                 Compare(Sys.Data.Common.DbDataAdapter.DefaultSourceTableName , "Table");
1288                         }
1289                         catch(Exception ex)     {exp = ex;}
1290                         finally {EndCase(exp); exp = null;}
1291                 }
1292
1293                 #endregion
1294
1295                 #region "-----------  Sys.Data.Common.DataAdapter --------------"
1296                 protected void DataAdapter_AcceptChangesDuringFill(Sys.Data.Common.DbDataAdapter dbDA) {
1297                         Exception exp = null;
1298                         IDbDataAdapter Ida = (IDbDataAdapter)dbDA;
1299                         IDbCommand ICmd = Ida.SelectCommand;
1300                         IDbConnection IConn = ICmd.Connection;
1301                         IConn.ConnectionString = MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString;
1302                         IConn.Open();
1303
1304                         PrepareDataForTesting( MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
1305
1306                         //get the total rows count
1307                         ICmd.CommandText = "SELECT Count(*) FROM Customers where CustomerID in ('GH100','GH200','GH300','GH400','GH500','GH600','GH700')";
1308                         int ExpectedRows = Sys.Convert.ToInt32(ICmd.ExecuteScalar());
1309                         try {
1310                                 BeginCase("Check that Expected rows count > 0");
1311                                 Compare(ExpectedRows > 0 ,true);
1312                         }
1313                         catch(Exception ex)     {exp = ex;}
1314                         finally {EndCase(exp); exp = null;}
1315
1316                         ICmd.CommandText = "SELECT CustomerID, CompanyName, City, Country, Phone FROM Customers where CustomerID in ('GH100','GH200','GH300','GH400','GH500','GH600','GH700')";
1317
1318                         DataSet ds = new DataSet();
1319                         dbDA.AcceptChangesDuringFill = false;
1320
1321
1322                         try {
1323                                 BeginCase("Execute Fill - check return rows count");
1324                                 int i = dbDA.Fill(ds);
1325                                 Compare(i ,ExpectedRows );
1326                         }
1327                         catch(Exception ex)     {exp = ex;}
1328                         finally {EndCase(exp); exp = null;}
1329
1330
1331                         bool blnAcceptChanges = false;
1332
1333                         foreach (DataRow dr in ds.Tables[0].Rows) {
1334                                 if (dr.RowState != DataRowState.Added ) {
1335                                         blnAcceptChanges = true;
1336                                         break;
1337                                 }
1338                         }
1339                         try {
1340                                 BeginCase("AcceptChangesDuringFill - false");
1341                                 Compare(blnAcceptChanges ,false );
1342                         }
1343                         catch(Exception ex)     {exp = ex;}
1344                         finally {EndCase(exp); exp = null;}
1345
1346                         ds.Reset();
1347                         dbDA.AcceptChangesDuringFill = true;
1348                         dbDA.Fill(ds);
1349                         blnAcceptChanges = false;
1350                         foreach (DataRow dr in ds.Tables[0].Rows) {
1351                                 if (dr.RowState != DataRowState.Unchanged ) {
1352                                         blnAcceptChanges = true;
1353                                         break;
1354                                 }
1355                         }
1356                         try {
1357                                 BeginCase("AcceptChangesDuringFill - true");
1358                                 Compare(blnAcceptChanges ,false );
1359                         }
1360                         catch(Exception ex)     {exp = ex;}
1361                         finally {EndCase(exp); exp = null;}
1362
1363                         //close connection
1364                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
1365                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
1366
1367                 }
1368
1369                 protected void DataAdapter_ContinueUpdateOnError(Sys.Data.Common.DbDataAdapter dbDA) {
1370                         /*
1371                                 !!!!!! Not working (TestName "ContinueUpdateOnError - true, check value 2")!!!!!
1372                                 If ContinueUpdateOnError is set to true, no exception is thrown when an error occurs during the update of a row.
1373                                 The update of the row is skipped and the error information is placed in the RowError property of the row in error.
1374                                 The DataAdapter continues to update subsequent rows.
1375                                 If ContinueUpdateOnError is set to false, an exception is thrown when an error occurs during the update of a row.
1376                         */
1377                         Exception exp = null;
1378
1379
1380                         IDbDataAdapter Ida = (IDbDataAdapter)dbDA;
1381                         IDbCommand ICmd = Ida.SelectCommand;
1382                         IDbConnection IConn = ICmd.Connection;
1383                         IConn.ConnectionString = MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString;
1384                         IConn.Open();
1385
1386                         PrepareDataForTesting( MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
1387
1388                         //get the total rows count
1389                         ICmd.CommandText = "SELECT Count(*) FROM Customers where CustomerID in ('GH100','GH200','GH300','GH400','GH500','GH600','GH700')";
1390                         int ExpectedRows = Sys.Convert.ToInt32(ICmd.ExecuteScalar());
1391                         try {
1392                                 BeginCase("Check that Expected rows count > 0");
1393                                 Compare(ExpectedRows > 0 ,true);
1394                         }
1395                         catch(Exception ex)     {exp = ex;}
1396                         finally {EndCase(exp); exp = null;}
1397
1398                         ICmd.CommandText = "SELECT CustomerID, CompanyName, City, Country, Phone FROM Customers where CustomerID in ('GH100','GH200','GH300','GH400','GH500','GH600','GH700')";
1399
1400
1401                         DataSet dsMem = new DataSet();  //Disconected dataset
1402                         DataSet dsDB = new DataSet();   //DataBase data
1403                         dbDA.AcceptChangesDuringFill = true;
1404                         //get data from DB
1405                         try {
1406                                 BeginCase("Execute Fill - check return rows count");
1407                                 int i = dbDA.Fill(dsMem);
1408                                 Compare(i ,ExpectedRows );
1409                         }
1410                         catch(Exception ex)     {exp = ex;}
1411                         finally {EndCase(exp); exp = null;}
1412
1413
1414                         //update data with invalid information (Max. length for Phone is 24)
1415                         //                                      123456789012345678901234
1416                         string newValue1 = "Very Long String That Will Raise An Error Yep!";
1417                         string oldValue1 = dsMem.Tables[0].Rows[3]["Phone"].ToString();
1418                         string oldValue2 = dsMem.Tables[0].Rows[4]["Phone"].ToString();
1419                         string newValue2 = "03-1234";
1420
1421
1422                         dsMem.Tables[0].Rows[3]["Phone"] = newValue1;
1423                         dsMem.Tables[0].Rows[4]["Phone"] = newValue2;
1424                         dbDA.ContinueUpdateOnError = true;
1425
1426                         //will not throw exception
1427                         try {
1428                                 BeginCase("ContinueUpdateOnError - true, check exception");
1429                                 try {
1430                                         dbDA.Update(dsMem);
1431                                 }
1432                                 catch(Exception ex){exp = ex;}
1433                                 Compare(exp == null,true);
1434                                 exp = null;
1435                         }
1436                         catch(Exception ex)     {exp = ex;}
1437                         finally {EndCase(exp); exp = null;}
1438
1439                         dbDA.Fill(dsDB); //get data from DB to check the update operation
1440
1441                         try {
1442                                 BeginCase("ContinueUpdateOnError - true, check RowError");
1443                                 Compare(dsMem.Tables[0].Rows[3].RowError.Length > 0 , true);
1444                         }
1445                         catch(Exception ex)     {exp = ex;}
1446                         finally {EndCase(exp); exp = null;}
1447
1448                         try {
1449                                 BeginCase("ContinueUpdateOnError - true, check value 1");
1450                                 Compare(dsDB.Tables[0].Rows[3]["Phone"] , oldValue1);
1451                         }
1452                         catch(Exception ex)     {exp = ex;}
1453                         finally {EndCase(exp); exp = null;}
1454
1455
1456                         /*              - Test excluded, it is not working in .NET too!
1457                                         //should continue the update
1458                                         try
1459                                         {
1460                                                 BeginCase("ContinueUpdateOnError - true, check value 2");
1461                                                 Compare(dsDB.Tables[0].Rows[4]["Phone"] , newValue2);  //--------- NOT WORKING !!! -----------
1462                                         }
1463                                         catch(Exception ex)     {exp = ex;}
1464                                         finally {EndCase(exp); exp = null;}
1465                         */
1466                         dsMem.Reset();
1467                         dsDB.Reset();
1468                         dbDA.Fill(dsMem);
1469                         dsMem.Tables[0].Rows[3]["Phone"] = newValue1 ;
1470                         dsMem.Tables[0].Rows[4]["Phone"] = newValue2;
1471                         dbDA.ContinueUpdateOnError = false;
1472
1473                         try {
1474                                 BeginCase("ContinueUpdateOnError - false, check exception");
1475                                 try {
1476                                         dbDA.Update(dsMem);
1477                                 }
1478                                 catch(Exception ex){exp = ex;}
1479                                 Compare(exp == null,false);
1480                                 exp = null;
1481                         }
1482                         catch(Exception ex)     {exp = ex;}
1483                         finally {EndCase(exp); exp = null;}
1484
1485                         dbDA.Fill(dsDB); //get data from DB to check the update operation
1486                         try {
1487                                 BeginCase("ContinueUpdateOnError - false,check RowError");
1488                                 Compare(dsMem.Tables[0].Rows[3].RowError.Length > 0 ,true);
1489                         }
1490                         catch(Exception ex)     {exp = ex;}
1491                         finally {EndCase(exp); exp = null;}
1492                         try {
1493                                 BeginCase("ContinueUpdateOnError - false,check value 1");
1494                                 Compare(dsDB.Tables[0].Rows[3]["Phone"] , oldValue1 );
1495                         }
1496                         catch(Exception ex)     {exp = ex;}
1497                         finally {EndCase(exp); exp = null;}
1498
1499
1500                         try {
1501                                 BeginCase("ContinueUpdateOnError - false,check value 2");
1502                                 Compare(dsDB.Tables[0].Rows[4]["Phone"] , oldValue2 );
1503                         }
1504                         catch(Exception ex)     {exp = ex;}
1505                         finally {EndCase(exp); exp = null;}
1506
1507                         //close connection
1508                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
1509                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
1510
1511                 }
1512                 protected void DataAdapter_MissingMappingAction(Sys.Data.Common.DbDataAdapter dbDA) {
1513                         Exception exp = null;
1514                         IDbDataAdapter Ida = (IDbDataAdapter)dbDA;
1515                         IDbCommand ICmd = Ida.SelectCommand;
1516                         IDbConnection IConn = ICmd.Connection;
1517                         IConn.ConnectionString = MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString;
1518
1519                         IConn.Open();
1520
1521                         //get the total rows count
1522
1523                         PrepareDataForTesting( MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
1524
1525                         ICmd.CommandText = "SELECT Count(*) FROM Customers where CustomerID in ('GH100','GH200','GH300','GH400','GH500','GH600','GH700')";
1526                         int ExpectedRows = Sys.Convert.ToInt32(ICmd.ExecuteScalar());
1527                         try {
1528                                 BeginCase("Check that Expected rows count > 0");
1529                                 Compare(ExpectedRows > 0 ,true);
1530                         }
1531                         catch(Exception ex)     {exp = ex;}
1532                         finally {EndCase(exp); exp = null;}
1533
1534                         ICmd.CommandText = "SELECT CustomerID, CompanyName, City, Country, Phone FROM Customers where CustomerID in ('GH100','GH200','GH300','GH400','GH500','GH600','GH700')";
1535
1536
1537                         //init dataset
1538                         DataSet ds = new DataSet();
1539                         try {
1540                                 BeginCase("Execute Fill - check return rows count");
1541                                 int i = dbDA.Fill(ds);
1542                                 Compare(i ,ExpectedRows );
1543                         }
1544                         catch(Exception ex)     {exp = ex;}
1545                         finally {EndCase(exp); exp = null;}
1546
1547                         //make dataset schema mismatch with DB
1548                         ds.Tables[0].Columns.Remove("Country");
1549                         ds.Tables[0].Clear();
1550
1551                         //--- Default value ---
1552
1553                         try {
1554                                 BeginCase("MissingMappingAction Default value");
1555                                 Compare(dbDA.MissingMappingAction , MissingMappingAction.Passthrough);
1556                         }
1557                         catch(Exception ex)     {exp = ex;}
1558                         finally {EndCase(exp); exp = null;}
1559
1560                         //--- MissingMappingAction.Error ---
1561                         ds.Tables[0].Clear();
1562                         dbDA.MissingMappingAction  = MissingMappingAction.Error ;
1563                         Exception ExMissingMappingAction = null;
1564                         try {
1565                                 BeginCase("MissingMappingAction.Error");
1566                                 try {
1567                                         dbDA.Fill(ds);
1568                                 }
1569                                 catch (InvalidOperationException e) {
1570                                         ExMissingMappingAction = e;
1571                                 }
1572                                 Compare(ExMissingMappingAction.GetType() ,typeof(InvalidOperationException));
1573                         }
1574                         catch(Exception ex)     {exp = ex;}
1575                         finally {EndCase(exp); exp = null;}
1576
1577                         try {
1578                                 BeginCase("MissingMappingAction.Error, Row.Count = 0");
1579                                 Compare(ds.Tables[0].Rows.Count , 0 );
1580                         }
1581                         catch(Exception ex)     {exp = ex;}
1582                         finally {EndCase(exp); exp = null;}
1583
1584                         try {
1585                                 BeginCase("MissingMappingAction.Error, Column");
1586                                 Compare(ds.Tables[0].Columns.IndexOf("Country")  , -1 );
1587                         }
1588                         catch(Exception ex)     {exp = ex;}
1589                         finally {EndCase(exp); exp = null;}
1590
1591
1592                         //--- MissingMappingAction.Ignore ---
1593                         ds.Tables[0].Clear();
1594                         dbDA.MissingMappingAction  = MissingMappingAction.Ignore  ;
1595                         ExMissingMappingAction = null;
1596                         try {
1597                                 BeginCase("MissingMappingAction.Ignore");
1598                                 try {
1599                                         dbDA.Fill(ds);
1600                                 }
1601                                 catch (InvalidOperationException e) {
1602                                         ExMissingMappingAction = e;
1603                                 }
1604                                 Compare(ExMissingMappingAction ,null);
1605                         }
1606                         catch(Exception ex)     {exp = ex;}
1607                         finally {EndCase(exp); exp = null;}
1608
1609                         try {
1610                                 BeginCase("MissingMappingAction.Ignore, Row.Count = 0");
1611                                 Compare(ds.Tables[0].Rows.Count ,0);
1612                         }
1613                         catch(Exception ex)     {exp = ex;}
1614                         finally {EndCase(exp); exp = null;}
1615
1616                         try {
1617                                 BeginCase("MissingMappingAction.Ignore, Column");
1618                                 Compare(ds.Tables[0].Columns.IndexOf("Country")  , -1 );
1619                         }
1620                         catch(Exception ex)     {exp = ex;}
1621                         finally {EndCase(exp); exp = null;}
1622
1623                         //--- MissingMappingAction.Passthrough ---
1624                         ds.Tables[0].Clear();
1625                         dbDA.MissingMappingAction  = MissingMappingAction.Passthrough   ;
1626                         ExMissingMappingAction = null;
1627                         try {
1628                                 BeginCase("MissingMappingAction.Passthrough");
1629                                 try {
1630                                         dbDA.Fill(ds);
1631                                 }
1632                                 catch (InvalidOperationException e) {
1633                                         ExMissingMappingAction = e;
1634                                 }
1635                                 Compare(ExMissingMappingAction ,null);
1636                         }
1637                         catch(Exception ex)     {exp = ex;}
1638                         finally {EndCase(exp); exp = null;}
1639
1640                         try {
1641                                 BeginCase("MissingMappingAction.Passthrough, Row.Count > 0");
1642                                 Compare(ds.Tables[0].Rows.Count >= 0 ,true );
1643                         }
1644                         catch(Exception ex)     {exp = ex;}
1645                         finally {EndCase(exp); exp = null;}
1646
1647                         try {
1648                                 BeginCase("MissingMappingAction.Passthrough, Column");
1649                                 Compare(ds.Tables[0].Columns.IndexOf("Country") >= 0  ,true );
1650                         }
1651                         catch(Exception ex)     {exp = ex;}
1652                         finally {EndCase(exp); exp = null;}
1653
1654                         //close connection
1655                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
1656                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
1657                 }
1658                 protected void DataAdapter_MissingSchemaAction(Sys.Data.Common.DbDataAdapter dbDA) {
1659                         Exception exp = null;
1660                         IDbDataAdapter Ida = (IDbDataAdapter)dbDA;
1661                         IDbCommand ICmd = Ida.SelectCommand;
1662                         IDbConnection IConn = ICmd.Connection;
1663                         IConn.ConnectionString = MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString;
1664                         IConn.Open();
1665
1666                         PrepareDataForTesting( MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
1667
1668                         //get the total rows count
1669                         ICmd.CommandText = "SELECT Count(*) FROM Customers where CustomerID in ('GH100','GH200','GH300','GH400','GH500','GH600','GH700')";
1670                         int ExpectedRows = Sys.Convert.ToInt32(ICmd.ExecuteScalar());
1671                         try {
1672                                 BeginCase("Check that Expected rows count > 0");
1673                                 Compare(ExpectedRows > 0 ,true);
1674                         }
1675                         catch(Exception ex)     {exp = ex;}
1676                         finally {EndCase(exp); exp = null;}
1677
1678                         ICmd.CommandText = "SELECT CustomerID, CompanyName, City, Country, Phone FROM Customers where CustomerID in ('GH100','GH200','GH300','GH400','GH500','GH600','GH700')";
1679
1680                         //get db type
1681                         DBType = ConnectedDataProvider.GetDbType(((IDbDataAdapter)dbDA).SelectCommand.Connection.ConnectionString);
1682
1683                         //init dataset
1684                         DataSet ds = new DataSet();
1685                         try {
1686                                 BeginCase("Execute Fill - check return rows count");
1687                                 int i = dbDA.Fill(ds);
1688                                 Compare(i ,ExpectedRows );
1689                         }
1690                         catch(Exception ex)     {exp = ex;}
1691                         finally {EndCase(exp); exp = null;}
1692
1693                         //make dataset schema mismatch with DB
1694                         ds.Tables[0].Columns.Remove("Country");
1695                         ds.Tables[0].Clear();
1696
1697
1698                         //--- Default value ---
1699
1700                         try {
1701                                 BeginCase("MissingSchemaAction Default value");
1702                                 Compare(dbDA.MissingSchemaAction, MissingSchemaAction.Add);
1703                         }
1704                         catch(Exception ex)     {exp = ex;}
1705                         finally {EndCase(exp); exp = null;}
1706
1707                         //--- MissingSchemaAction.Error ---
1708                         ds.Tables[0].Clear();
1709                         dbDA.MissingSchemaAction  = MissingSchemaAction.Error  ;
1710                         Exception ExMissingSchemaAction = null;
1711                         try {
1712                                 BeginCase("MissingSchemaAction.Error");
1713                                 try {
1714                                         dbDA.Fill(ds);
1715                                 }
1716                                 catch (InvalidOperationException e) {
1717                                         ExMissingSchemaAction = e;
1718                                 }
1719                                 Compare(ExMissingSchemaAction.GetType() ,typeof(InvalidOperationException));
1720                         }
1721                         catch(Exception ex)     {exp = ex;}
1722                         finally {EndCase(exp); exp = null;}
1723
1724                         try {
1725                                 BeginCase("MissingSchemaAction.Error, Row.Count = 0");
1726                                 Compare(ds.Tables[0].Rows.Count , 0 );
1727                         }
1728                         catch(Exception ex)     {exp = ex;}
1729                         finally {EndCase(exp); exp = null;}
1730
1731                         try {
1732                                 BeginCase("MissingSchemaAction.Error, Column");
1733                                 Compare(ds.Tables[0].Columns.IndexOf("Country")  , -1 );
1734                         }
1735                         catch(Exception ex)     {exp = ex;}
1736                         finally {EndCase(exp); exp = null;}
1737
1738
1739                         //--- MissingSchemaAction.Ignore ---
1740                         try {
1741                                 //catch any exception that might occure
1742                                 BeginCase("MissingSchemaAction.Ignore - invoke");
1743                                 ds.Tables[0].Clear();
1744                                 dbDA.MissingSchemaAction  = MissingSchemaAction.Ignore  ;
1745                                 ExMissingSchemaAction = null;
1746                                 dbDA.Fill(ds);
1747                                 Compare(true ,true);
1748                         }
1749                         catch(Exception ex)     {exp = ex;}
1750                         finally {EndCase(exp); exp = null;}
1751
1752                         try {
1753                                 BeginCase("MissingSchemaAction.Ignore, Row.Count = 0");
1754                                 Compare(ds.Tables[0].Rows.Count > 0 ,true);
1755                         }
1756                         catch(Exception ex)     {exp = ex;}
1757                         finally {EndCase(exp); exp = null;}
1758
1759                         try {
1760                                 BeginCase("MissingSchemaAction.Ignore, Column");
1761                                 Compare(ds.Tables[0].Columns.IndexOf("Country")  , -1 );
1762                         }
1763                         catch(Exception ex)     {exp = ex;}
1764                         finally {EndCase(exp); exp = null;}
1765
1766 #if !KNOWN_BUG //BUG_NUM:1951
1767                         try {
1768                                 BeginCase("MissingSchemaAction.Ignore, PrimaryKey");
1769                                 Compare(ds.Tables[0].PrimaryKey.Length == 0 ,true );
1770                         }
1771                         catch(Exception ex)     {exp = ex;}
1772                         finally {EndCase(exp); exp = null;}
1773 #endif
1774
1775                         //--- MissingSchemaAction.Add ---
1776                         try {
1777                                 //catch any exception that might occure
1778                                 BeginCase("MissingSchemaAction.Add - invoke");
1779                                 ds.Tables[0].Clear();
1780                                 dbDA.MissingSchemaAction  = MissingSchemaAction.Add   ;
1781                                 ExMissingSchemaAction = null;
1782                                 dbDA.Fill(ds);
1783                                 Compare(true ,true);
1784                         }
1785                         catch(Exception ex)     {exp = ex;}
1786                         finally {EndCase(exp); exp = null;}
1787
1788                         try {
1789                                 BeginCase("MissingSchemaAction.Add, Row.Count > 0");
1790                                 Compare(ds.Tables[0].Rows.Count >= 0 ,true );
1791                         }
1792                         catch(Exception ex)     {exp = ex;}
1793                         finally {EndCase(exp); exp = null;}
1794
1795                         try {
1796                                 BeginCase("MissingSchemaAction.Add, Column");
1797                                 Compare(ds.Tables[0].Columns.IndexOf("Country") >= 0  ,true );
1798                         }
1799                         catch(Exception ex)     {exp = ex;}
1800                         finally {EndCase(exp); exp = null;}
1801
1802 #if !KNOWN_BUG //BUG_NUM:1952
1803                         //DB2 don't return primary key
1804                         if (DBType != DataBaseServer.DB2) {
1805                                 try {
1806                                         BeginCase("MissingSchemaAction.AddWithKey, PrimaryKey");
1807                                         Compare(ds.Tables[0].PrimaryKey.Length  ,0);
1808                                 }
1809                                 catch(Exception ex)     {exp = ex;}
1810                                 finally {EndCase(exp); exp = null;}
1811                         }
1812
1813                         //--- MissingSchemaAction.AddWithKey ---
1814                         try {
1815                                 //catch any exception that might occure
1816                                 BeginCase("MissingSchemaAction.AddWithKey - invoke");
1817                                 ds.Tables[0].Clear();
1818                                 ds.Tables[0].Columns.Remove("Country");
1819                                 dbDA.MissingSchemaAction  = MissingSchemaAction.AddWithKey    ;
1820                                 ExMissingSchemaAction = null;
1821                                 dbDA.Fill(ds);
1822                                 Compare(true ,true);
1823                         }
1824                         catch(Exception ex)     {exp = ex;}
1825                         finally {EndCase(exp); exp = null;}
1826
1827                         try {
1828                                 BeginCase("MissingSchemaAction.AddWithKey, Row.Count > 0");
1829                                 Compare(ds.Tables[0].Rows.Count >= 0 ,true );
1830                         }
1831                         catch(Exception ex)     {exp = ex;}
1832                         finally {EndCase(exp); exp = null;}
1833
1834                         try {
1835                                 BeginCase("MissingSchemaAction.AddWithKey, Column");
1836                                 Compare(ds.Tables[0].Columns.IndexOf("Country") >= 0  ,true );
1837                         }
1838                         catch(Exception ex)     {exp = ex;}
1839                         finally {EndCase(exp); exp = null;}
1840
1841                         //DB2 don't return primary key
1842                         if (DBType != DataBaseServer.DB2 &&
1843                                 DBType != DataBaseServer.Oracle) {
1844                                 try {
1845                                         BeginCase("MissingSchemaAction.AddWithKey, PrimaryKey");
1846                                         Compare(ds.Tables[0].PrimaryKey.Length > 0 ,true );
1847                                 }
1848                                 catch(Exception ex)     {exp = ex;}
1849                                 finally {EndCase(exp); exp = null;}
1850                         }
1851 #endif
1852
1853                         //close connection
1854                         if (  ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
1855                                 ((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
1856                 }
1857                 #endregion
1858
1859                 public override void BeginTest(string testName) {
1860                         base.BeginTest (testName);
1861                         Log(String.Format("DataBase Type is {0}", ConnectedDataProvider.GetDbType()));
1862                 }
1863
1864         }
1865 }