2 // SqlDataAdapterTest.cs - NUnit Test Cases for testing the
3 // SqlDataAdapter class
5 // Umadevi S (sumadevi@novell.com)
6 // Sureshkumar T (tsureshkumar@novell.com)
7 // Senganal T (tsenganal@novell.com)
9 // Copyright (c) 2004 Novell Inc., and the individuals listed
10 // on the ChangeLog entries.
12 // Permission is hereby granted, free of charge, to any person obtaining
13 // a copy of this software and associated documentation files (the
14 // "Software"), to deal in the Software without restriction, including
15 // without limitation the rights to use, copy, modify, merge, publish,
16 // distribute, sublicense, and/or sell copies of the Software, and to
17 // permit persons to whom the Software is furnished to do so, subject to
18 // the following conditions:
20 // The above copyright notice and this permission notice shall be
21 // included in all copies or substantial portions of the Software.
23 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
24 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
25 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
26 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
27 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
28 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
29 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
34 using System.Data.Common;
35 using System.Data.SqlClient;
37 using NUnit.Framework;
39 namespace MonoTests.System.Data.SqlClient
43 [Category ("sqlserver")]
44 public class SqlDataAdapterTest
46 SqlDataAdapter adapter =null;
48 string connectionString = ConnectionManager.Singleton.ConnectionString;
49 SqlConnection conn = null;
53 The below test will not run everytime, since the region id column is unique
54 so change the regionid if you want the test to pass.
56 public void UpdateTest () {
57 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
59 ConnectionManager.Singleton.OpenConnection ();
60 DataTable dt = new DataTable();
61 SqlDataAdapter da = null;
62 da = new SqlDataAdapter("Select * from employee;", conn);
63 SqlCommandBuilder cb = new SqlCommandBuilder (da);
65 DataRow dr = dt.NewRow();
67 dr ["fname"] = "boston";
68 dr ["dob"] = DateTime.Now.Subtract (new TimeSpan (20*365, 0, 0, 0));
69 dr ["doj"] = DateTime.Now;
74 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
75 ConnectionManager.Singleton.CloseConnection ();
80 This needs a errortable created as follows
81 id uniqueidentifier,name char(10) , with values
87 public void NullGuidTest()
89 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
91 ConnectionManager.Singleton.OpenConnection ();
92 DBHelper.ExecuteNonQuery (conn, "create table #tmp_guid_table ( " +
93 " id uniqueidentifier default newid (), " +
95 DBHelper.ExecuteNonQuery (conn, "insert into #tmp_guid_table (name) values (null)");
96 DBHelper.ExecuteNonQuery (conn, "insert into #tmp_guid_table (id, name) values (null, 'bbbb')");
97 SqlDataAdapter da = new SqlDataAdapter("select * from #tmp_guid_table", conn);
98 DataSet ds = new DataSet();
100 Assert.AreEqual (1, ds.Tables.Count, "#1");
101 Assert.AreEqual (DBNull.Value, ds.Tables [0].Rows [1] ["id"], "#2");
103 ConnectionManager.Singleton.CloseConnection ();
105 // the bug 68804 - is that the fill hangs!
106 Assert.AreEqual("Done","Done");
111 public void DefaultConstructorTest ()
113 adapter = new SqlDataAdapter ();
114 Assert.AreEqual (MissingMappingAction.Passthrough,
115 adapter.MissingMappingAction,
116 "#1 Missing Mapping acttion default to Passthrough");
117 Assert.AreEqual (MissingSchemaAction.Add,
118 adapter.MissingSchemaAction,
119 "#2 Missing Schme action default to Add");
123 public void OverloadedConstructorsTest ()
125 SqlCommand selCmd = new SqlCommand ("Select * from numeric_family");
126 adapter = new SqlDataAdapter (selCmd);
127 Assert.AreEqual (MissingMappingAction.Passthrough,
128 adapter.MissingMappingAction,
129 "#1 Missing Mapping acttion default to Passthrough");
130 Assert.AreEqual (MissingSchemaAction.Add,
131 adapter.MissingSchemaAction,
132 "#2 Missing Schme action default to Add");
133 Assert.AreSame (selCmd, adapter.SelectCommand,
134 "#3 Select Command shud be a ref to the arg passed");
136 conn = new SqlConnection (connectionString);
137 String selStr = "Select * from numeric_family";
138 adapter = new SqlDataAdapter (selStr, conn);
139 Assert.AreEqual (MissingMappingAction.Passthrough,
140 adapter.MissingMappingAction,
141 "#4 Missing Mapping acttion default to Passthrough");
142 Assert.AreEqual (MissingSchemaAction.Add,
143 adapter.MissingSchemaAction,
144 "#5 Missing Schme action default to Add");
145 Assert.AreSame (selStr, adapter.SelectCommand.CommandText,
146 "#6 Select Command shud be a ref to the arg passed");
147 Assert.AreSame (conn, adapter.SelectCommand.Connection,
148 "#7 cmd.connection shud be t ref to connection obj");
150 selStr = "Select * from numeric_family";
151 adapter = new SqlDataAdapter (selStr, connectionString);
152 Assert.AreEqual (MissingMappingAction.Passthrough,
153 adapter.MissingMappingAction,
154 "#8 Missing Mapping action shud default to Passthrough");
155 Assert.AreEqual (MissingSchemaAction.Add,
156 adapter.MissingSchemaAction,
157 "#9 Missing Schema action shud default to Add");
158 Assert.AreSame (selStr,
159 adapter.SelectCommand.CommandText,
161 Assert.AreEqual (connectionString,
162 adapter.SelectCommand.Connection.ConnectionString,
166 public void Fill_Test_ConnState ()
168 //Check if Connection State is maintained correctly ..
169 data = new DataSet ("test1");
170 adapter = new SqlDataAdapter ("select id from numeric_family where id=1",
172 SqlCommand cmd = adapter.SelectCommand ;
174 Assert.AreEqual (ConnectionState.Closed,
175 cmd.Connection.State, "#1 Connection shud be in closed state");
177 Assert.AreEqual (1, data.Tables.Count, "#2 One table shud be populated");
178 Assert.AreEqual (ConnectionState.Closed, cmd.Connection.State,
179 "#3 Connection shud be closed state");
181 data = new DataSet ("test2");
182 cmd.Connection.Open ();
183 Assert.AreEqual (ConnectionState.Open, cmd.Connection.State,
184 "#3 Connection shud be open");
186 Assert.AreEqual (1, data.Tables.Count, "#4 One table shud be populated");
187 Assert.AreEqual (ConnectionState.Open, cmd.Connection.State,
188 "#5 Connection shud be open");
189 cmd.Connection.Close ();
191 // Test if connection is closed when exception occurs
192 cmd.CommandText = "select id1 from numeric_family";
195 }catch (Exception e) {
196 if (cmd.Connection.State == ConnectionState.Open) {
197 cmd.Connection.Close ();
198 Assert.Fail ("# Connection Shud be Closed");
204 public void Fill_Test_Data ()
206 //Check if a table is created for each resultset
207 String batchQuery = "Select id,type_bit,type_int from numeric_family;";
208 batchQuery += "Select type_bit,type_bigint from numeric_family";
209 adapter = new SqlDataAdapter (batchQuery, connectionString);
210 data = new DataSet ("test1");
212 Assert.AreEqual (2, data.Tables.Count,"#1 2 Table shud be created");
214 //Check if Table and Col are named correctly for unnamed columns
215 string query = "Select 10,20 from numeric_family;" ;
216 query += "Select 10,20 from numeric_family";
217 adapter = new SqlDataAdapter (query, connectionString);
218 data = new DataSet ("test2");
220 Assert.AreEqual (2, data.Tables.Count,
221 "#2 2 Tables shud be created");
222 Assert.AreEqual ("Table", data.Tables[0].TableName, "#3");
223 Assert.AreEqual ("Table1", data.Tables[1].TableName, "#4");
224 Assert.AreEqual ("Column1", data.Tables[0].Columns[0].ColumnName, "#5");
225 Assert.AreEqual ("Column2", data.Tables[0].Columns[1].ColumnName, "#6");
226 Assert.AreEqual ("Column1", data.Tables[1].Columns[0].ColumnName, "#7");
227 Assert.AreEqual ("Column2", data.Tables[1].Columns[1].ColumnName, "#8");
229 //Check if dup columns are named correctly
230 query = "select A.id ,B.id , C.id from numeric_family A, ";
231 query += "numeric_family B , numeric_family C";
232 adapter = new SqlDataAdapter (query, connectionString);
233 data = new DataSet ("test3");
236 // NOTE msdotnet contradicts documented behavior
237 // as per documentation the column names should be
238 // id1,id2,id3 .. but msdotnet returns id,id1,id2
239 Assert.AreEqual ("id", data.Tables[0].Columns[0].ColumnName,
240 "#9 if colname is duplicated ,shud be col,col1,col2 etc");
241 Assert.AreEqual ("id1", data.Tables[0].Columns[1].ColumnName,
242 "#10 if colname is duplicated ,shud be col,col1,col2 etc");
243 Assert.AreEqual ("id2", data.Tables[0].Columns[2].ColumnName,
244 "#11 if colname is duplicated ,shud be col,col1,col2 etc");
246 // Test if tables are created and named accordingly ,
247 // but only for those queries returning result sets
248 query = "update numeric_family set id=100 where id=50;";
249 query += "select * from numeric_family";
250 adapter = new SqlDataAdapter (query, connectionString);
251 data = new DataSet ("test4");
253 Assert.AreEqual (1 ,data.Tables.Count,
254 "#12 Tables shud be named only for queries returning a resultset");
255 Assert.AreEqual ("Table", data.Tables[0].TableName,
256 "#13 The first resutlset shud have 'Table' as its name");
258 // Test behavior with an outerjoin
259 query = "select A.id,B.type_bit from numeric_family A LEFT OUTER JOIN ";
260 query += "numeric_family B on A.id = B.type_bit";
261 adapter = new SqlDataAdapter (query, connectionString);
262 data = new DataSet ("test5");
264 Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length,
265 "#14 Primary Key shudnt be set if an outer join is performed");
266 Assert.AreEqual (0, data.Tables[0].Constraints.Count,
267 "#15 Constraints shudnt be set if an outer join is performed");
268 adapter = new SqlDataAdapter ("select id from numeric_family",
270 data = new DataSet ("test6");
271 adapter.Fill (data, 1, 1, "numeric_family");
272 Assert.AreEqual (1, data.Tables[0].Rows.Count, "#16");
273 Assert.AreEqual (2, data.Tables[0].Rows[0][0], "#17");
275 // only one test for DataTable.. DataSet tests covers others
276 adapter = new SqlDataAdapter ("select id from numeric_family",
278 DataTable table = new DataTable ("table1");
279 adapter.Fill (table);
280 Assert.AreEqual (4, table.Rows.Count , "#18");
284 public void Fill_Test_PriKey ()
286 // Test if Primary Key & Constraints Collection is correct
287 adapter = new SqlDataAdapter ("select id,type_bit from numeric_family",
289 adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
290 data = new DataSet ("test1");
292 Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length,
293 "#1 Primary Key shud be set");
294 Assert.AreEqual (1, data.Tables[0].Constraints.Count,
295 "#2 Constraints shud be set");
296 Assert.AreEqual (4, data.Tables[0].Rows.Count,
297 "#3 No Of Rows shud be 4");
299 // Test if data is correctly merged
301 Assert.AreEqual (4, data.Tables[0].Rows.Count,
302 "#4 No of Row shud still be 4");
304 // Test if rows are appended and not merged
305 // when primary key is not returned in the result-set
306 string query = "Select type_int,type_bigint from numeric_family";
307 adapter.SelectCommand.CommandText = query;
308 data = new DataSet ("test2");
310 Assert.AreEqual (4, data.Tables[0].Rows.Count,
311 "#5 No of Rows shud be 4");
313 Assert.AreEqual (8, data.Tables[0].Rows.Count,
314 "#6 No of Rows shud double now");
318 public void Fill_Test_Exceptions ()
320 adapter = new SqlDataAdapter ("select * from numeric_family",
322 data = new DataSet ("test1");
324 adapter.Fill (data, -1, 0, "numeric_family");
325 Assert.Fail ("#1 Exception shud be thrown:Incorrect Arguments");
326 }catch (AssertionException e){
328 }catch (Exception e){
329 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
330 "#2 Incorrect Exception : " + e);
333 // conn is not closed due to a bug..
334 // can be removed later
335 adapter.SelectCommand.Connection.Close ();
338 adapter.Fill (data , 0 , -1 , "numeric_family");
339 Assert.Fail ("#3 Exception shud be thrown:Incorrect Arguments");
340 }catch (AssertionException e){
342 }catch (Exception e){
343 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
344 "#4 Incorrect Exception : " + e);
346 // conn is curr not closed.. can be removed later
347 adapter.SelectCommand.Connection.Close ();
350 // NOTE msdotnet contradicts documented behavior
351 // InvalidOperationException is expected if table is not valid
353 adapter.Fill (data , 0 , 0 , "invalid_talbe_name");
354 }catch (InvalidOperationException e) {
356 }catch (Exception e){
357 Assert.Fail ("#5 Exception shud be thrown : incorrect arugments ");
359 Assert.IsNotNull (ex , "#6 Exception shud be thrown : incorrect args ");
360 adapter.SelectCommand.Connection.Close (); // tmp .. can be removed once the bug if fixed
365 adapter.Fill ( null , 0 , 0 , "numeric_family");
366 Assert.Fail ( "#7 Exception shud be thrown : Invalid Dataset");
367 }catch (AssertionException e){
369 }catch (ArgumentNullException) {
371 }catch (Exception e) {
372 Assert.AreEqual (typeof(SystemException), e.GetType(),
373 "#8 Incorrect Exception : " + e);
375 // conn is currently not being closed..
376 //need to be removed once behavior is fixed
377 adapter.SelectCommand.Connection.Close ();
379 adapter.SelectCommand.Connection = null;
382 Assert.Fail ("#9 Exception shud be thrown : Invalid Connection");
383 }catch (AssertionException e){
385 }catch (Exception e){
386 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
387 "#10 Incorrect Exception : " + e);
391 bool FillErrorContinue = false;
393 public void Fill_Test_FillErrorTest ()
395 string query = "select type_bigint from numeric_family where id=1 or id=4 ";
397 DataSet ds = new DataSet ();
398 DataTable table = ds.Tables.Add ("test");
399 table.Columns.Add ("col", typeof (int));
401 adapter = new SqlDataAdapter (query, connectionString);
402 DataTableMapping mapping = adapter.TableMappings.Add ("numeric_family", "test");
403 mapping.ColumnMappings.Add ("type_bigint", "col");
407 count = adapter.Fill (ds, "numeric_family");
408 Assert.Fail ("#1 Overflow exception must be thrown");
409 }catch (OverflowException e) {
411 Assert.AreEqual (0, ds.Tables [0].Rows.Count, "#2");
412 Assert.AreEqual (0, count, "#3");
414 adapter.FillError += new FillErrorEventHandler (ErrorHandler);
415 FillErrorContinue = false;
417 count = adapter.Fill (ds, "numeric_family");
418 Assert.Fail ("#4 Overflow exception must be thrown");
419 }catch (OverflowException e) {
421 Assert.AreEqual (0, ds.Tables [0].Rows.Count, "#5");
422 Assert.AreEqual (0, count, "#6");
424 FillErrorContinue = true;
425 count = adapter.Fill (ds, "numeric_family");
426 // 1 row shud be filled
427 Assert.AreEqual (1, ds.Tables [0].Rows.Count, "#7");
428 Assert.AreEqual (1, count, "#8");
431 void ErrorHandler (object sender, FillErrorEventArgs args)
433 args.Continue = FillErrorContinue;
437 public void GetFillParametersTest ()
439 string query = "select id, type_bit from numeric_family where id > @param1";
440 adapter = new SqlDataAdapter (query, connectionString);
441 IDataParameter[] param = adapter.GetFillParameters ();
442 Assert.AreEqual (0, param.Length, "#1 size shud be 0");
444 SqlParameter param1 = new SqlParameter ();
445 param1.ParameterName = "@param1";
447 adapter.SelectCommand.Parameters.Add (param1);
449 param = adapter.GetFillParameters ();
450 Assert.AreEqual (1, param.Length, "#2 count shud be 1");
451 Assert.AreEqual (param1, param[0], "#3 Params shud be equal");
455 public void FillSchemaTest ()
459 // Test if connection is closed if excepton occurs during fill schema
460 query = "select * from invalid_table";
461 adapter = new SqlDataAdapter (query, connectionString);
462 data = new DataSet ("test");
464 adapter.FillSchema (data , SchemaType.Source);
465 }catch (Exception e){
466 if ( adapter.SelectCommand.Connection.State != ConnectionState.Closed)
468 Assert.Fail ("#0 Conn shud be closed if exception occurs");
469 adapter.SelectCommand.Connection.Close();
473 // Test Primary Key is set (since primary key column returned)
474 query = "select id, type_int from numeric_family where id=1";
475 adapter = new SqlDataAdapter (query, connectionString);
476 data = new DataSet ("test1");
477 adapter.FillSchema (data , SchemaType.Source);
479 Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length,
480 "#1 Primary Key property must be set");
482 // Test Primary Key is not set (since primary key column is returned)
483 query = "select type_bit, type_int from numeric_family where id=1";
484 adapter = new SqlDataAdapter (query, connectionString);
485 data = new DataSet ("test2");
486 adapter.FillSchema (data, SchemaType.Source);
487 Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length,
488 "#2 Primary Key property should not be set");
490 // Test multiple tables are created for a batch query
491 query = "Select id ,type_bit from numeric_family;" ;
492 query += "Select id,type_bit,type_int from numeric_family;";
493 data = new DataSet ("test3");
494 adapter = new SqlDataAdapter (query, connectionString);
495 adapter.FillSchema (data , SchemaType.Source);
496 Assert.AreEqual (2 , data.Tables.Count , "#3 A table shud be created for each Result Set");
497 Assert.AreEqual (2 , data.Tables[0].Columns.Count , "#4 should have 2 columns");
498 Assert.AreEqual (3 , data.Tables[1].Columns.Count , "#5 Should have 3 columns");
500 // Test if table names and column names are filled correctly
501 query = "select 10,20 from numeric_family;" ;
502 query += "select 10,20 from numeric_family;";
503 adapter = new SqlDataAdapter (query, connectionString);
504 data = new DataSet ("test4");
506 adapter.FillSchema (data , SchemaType.Source);
507 }catch (Exception e){
508 Assert.Fail ("#3 Unexpected Exception : " + e);
510 Assert.AreEqual ( "Table", data.Tables[0].TableName);
511 Assert.AreEqual ( "Table1", data.Tables[1].TableName);
512 Assert.AreEqual ( "Column1", data.Tables[0].Columns[0].ColumnName,
513 "#6 Unnamed col shud be named as 'ColumnN'");
514 Assert.AreEqual ( "Column2", data.Tables[0].Columns[1].ColumnName,
515 "#7 Unnamed col shud be named as 'ColumnN'");
516 Assert.AreEqual ( "Column1", data.Tables[1].Columns[0].ColumnName,
517 "#8 Unnamed col shud be named as 'ColumnN'");
518 Assert.AreEqual ( "Column2", data.Tables[1].Columns[1].ColumnName,
519 "#9 Unnamed col shud be named as 'ColumnN'");
520 Assert.AreEqual (ConnectionState.Closed, adapter.SelectCommand.Connection.State,
521 "#10 Connection shud be closed");
523 // Test if mapping works correctly
524 // doesent work in both mono and msdotnet
525 // gotto check if something is wrong
527 query = "select id,type_bit from numeric_family";
528 adapter = new SqlDataAdapter (query, connectionString);
529 data = new DataSet ("test");
530 DataTable table = data.Tables.Add ("numeric_family_1");
531 table.Columns.Add ("id");
532 table.Columns.Add ("type_bit");
533 DataTableMapping map = adapter.TableMappings.Add("numeric_family_1",
535 map.ColumnMappings.Add ("id", "id_1");
536 map.ColumnMappings.Add ("type_bit", "type_bit_1");
537 adapter.FillSchema (data, SchemaType.Source, "numeric_family");
538 foreach (DataTable tab in data.Tables){
539 Console.WriteLine ("Table == {0}",tab.TableName);
540 foreach (DataColumn col in tab.Columns)
541 Console.WriteLine (" Col = {0} " , col.ColumnName);
547 public void MissingSchemaActionTest ()
549 adapter = new SqlDataAdapter (
550 "select id,type_bit,type_int from numeric_family where id<=4",
552 data = new DataSet ();
553 Assert.AreEqual (MissingSchemaAction.Add, adapter.MissingSchemaAction,
557 Assert.AreEqual (1, data.Tables.Count , "#1 One table shud be populated");
558 Assert.AreEqual (3, data.Tables[0].Columns.Count, "#2 Missing cols are added");
559 Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length, "#3 Default Value");
561 adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
564 Assert.AreEqual (3, data.Tables[0].Columns.Count,
565 "#4 Missing cols are added");
566 Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length, "#5 Default Value");
568 adapter.MissingSchemaAction = MissingSchemaAction.Ignore ;
571 Assert.AreEqual (0, data.Tables.Count, "#6 Data shud be ignored");
573 adapter.MissingSchemaAction = MissingSchemaAction.Error ;
577 Assert.Fail ("#8 Exception shud be thrown: Schema Mismatch");
578 }catch (AssertionException e) {
580 }catch (Exception e){
581 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
582 "#9 Incorrect Exception : "+e);
585 // Test for invalid MissingSchema Value
587 adapter.MissingSchemaAction = (MissingSchemaAction)(-5000);
588 Assert.Fail ("#10 Exception shud be thrown: Invalid Value");
589 }catch (AssertionException e){
591 }catch (Exception e){
592 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
593 "#11 Incorrect Exception : " +e);
596 // Tests if Data is filled correctly if schema is defined
597 // manually and MissingSchemaAction.Error is set
598 adapter.MissingSchemaAction = MissingSchemaAction.Error;
600 DataTable table = data.Tables.Add ("Table");
601 table.Columns.Add ("id");
602 table.Columns.Add ("type_bit");
603 table.Columns.Add ("type_int");
606 Assert.AreEqual (1, data.Tables.Count, "#12");
607 Assert.AreEqual (4, data.Tables[0].Rows.Count, "#13");
608 }catch (Exception e) {
609 Assert.Fail ("#12 Unexpected Exception : " + e);
614 public void MissingMappingActionTest ()
616 adapter = new SqlDataAdapter ("select id,type_bit from numeric_family where id=1",
618 data = new DataSet ();
619 Assert.AreEqual (adapter.MissingMappingAction,
620 MissingMappingAction.Passthrough,
623 Assert.AreEqual (1, data.Tables.Count,
624 "#2 One Table shud be created");
625 Assert.AreEqual (2, data.Tables[0].Columns.Count,
626 "#3 Two Cols shud be created");
628 adapter.MissingMappingAction = MissingMappingAction.Ignore;
631 Assert.AreEqual (0, data.Tables.Count, "#4 No table shud be created");
633 adapter.MissingMappingAction = MissingMappingAction.Error;
637 Assert.Fail ("#5 Exception shud be thrown : Mapping is missing");
638 }catch (AssertionException e){
640 }catch (Exception e) {
641 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
642 "#6 Incorrect Exception : " + e);
646 adapter.MissingMappingAction = (MissingMappingAction)(-5000);
647 Assert.Fail ("#7 Exception shud be thrown : Invalid Value");
648 }catch (AssertionException e){
650 }catch (Exception e){
651 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
652 "#8 Incorrect Exception : " +e);
655 // Test if mapping the column and table names works correctly
656 adapter.MissingMappingAction = MissingMappingAction.Error;
658 DataTable table = data.Tables.Add ("numeric_family_1");
659 table.Columns.Add ("id_1");
660 table.Columns.Add ("type_bit_1");
661 table.Columns.Add ("type_int_1");
662 DataTableMapping tableMap = adapter.TableMappings.Add ("numeric_family",
664 tableMap.ColumnMappings.Add ("id", "id_1");
665 tableMap.ColumnMappings.Add ("type_bit", "type_bit_1");
666 tableMap.ColumnMappings.Add ("type_int", "type_int_1");
667 adapter.Fill (data,"numeric_family");
668 Assert.AreEqual (1, data.Tables.Count ,
669 "#8 The DataTable shud be correctly mapped");
670 Assert.AreEqual (3, data.Tables[0].Columns.Count,
671 "#9 The DataColumns shud be corectly mapped");
672 Assert.AreEqual (1, data.Tables[0].Rows.Count,
673 "#10 Data shud be populated if mapping is correct");
676 // Test case for bug #76433
678 public void FillSchema_ValuesTest()
680 SqlConnection conn = new SqlConnection(connectionString);
683 IDbCommand command = conn.CreateCommand();
686 String cmd = "Create Table #tmp_TestTable (" ;
687 cmd += "Field1 DECIMAL (10) NOT NULL,";
688 cmd += "Field2 DECIMAL(19))";
689 command.CommandText = cmd;
690 command.ExecuteNonQuery();
692 DataSet dataSet = new DataSet();
693 string selectString = "SELECT * FROM #tmp_TestTable";
694 IDbDataAdapter dataAdapter = new SqlDataAdapter (
696 dataAdapter.FillSchema(dataSet, SchemaType.Mapped);
698 Assert.AreEqual (1, dataSet.Tables.Count, "#1");
700 DataColumn col = dataSet.Tables[0].Columns[0];
701 Assert.IsFalse (dataSet.Tables[0].Columns[0].AllowDBNull,"#2");
702 Assert.IsTrue (dataSet.Tables[0].Columns[1].AllowDBNull,"#3");
707 public void Fill_CheckSchema ()
709 SqlConnection conn = new SqlConnection(connectionString);
713 IDbCommand command = conn.CreateCommand();
716 String cmd = "Create Table #tmp_TestTable (" ;
717 cmd += "id int primary key,";
718 cmd += "field int not null)";
719 command.CommandText = cmd;
720 command.ExecuteNonQuery();
722 DataSet dataSet = new DataSet();
723 string selectString = "SELECT * from #tmp_TestTable";
724 IDbDataAdapter dataAdapter = new SqlDataAdapter (
726 dataAdapter.Fill (dataSet);
727 Assert.IsTrue (dataSet.Tables[0].Columns[1].AllowDBNull, "#1");
728 Assert.AreEqual (0, dataSet.Tables[0].PrimaryKey.Length, "#2");
731 dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey ;
732 dataAdapter.Fill (dataSet);
733 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#3");
734 Assert.AreEqual (1, dataSet.Tables[0].PrimaryKey.Length, "#4");
739 public void FillSchema_CheckSchema ()
741 SqlConnection conn = new SqlConnection(connectionString);
745 IDbCommand command = conn.CreateCommand();
748 String cmd = "Create Table #tmp_TestTable (" ;
749 cmd += "id int primary key,";
750 cmd += "field int not null)";
751 command.CommandText = cmd;
752 command.ExecuteNonQuery();
754 DataSet dataSet = new DataSet();
755 string selectString = "SELECT * from #tmp_TestTable";
756 IDbDataAdapter dataAdapter = new SqlDataAdapter (
759 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
760 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#1");
763 dataAdapter.MissingSchemaAction = MissingSchemaAction.Add;
764 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
765 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#2");
768 dataAdapter.MissingSchemaAction = MissingSchemaAction.Ignore;
769 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
770 Assert.AreEqual (0, dataSet.Tables.Count, "#3");
773 dataAdapter.MissingSchemaAction = MissingSchemaAction.Error;
775 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
776 Assert.Fail ("#4 Error should be thrown");
777 } catch (InvalidOperationException e) {
783 public void Fill_RelatedTables ()
785 SqlConnection conn = new SqlConnection(connectionString);
788 IDbCommand command = conn.CreateCommand();
790 DataSet dataSet = new DataSet();
791 string selectString = "SELECT id, type_int from numeric_family where id < 3";
792 DbDataAdapter dataAdapter = new SqlDataAdapter (selectString,conn);
794 DataTable table2 = dataSet.Tables.Add ("table2");
795 DataColumn ccol1 = table2.Columns.Add ("id", typeof (int));
796 DataColumn ccol2 = table2.Columns.Add ("type_int", typeof (int));
798 DataTable table1 = dataSet.Tables.Add ("table1");
799 DataColumn pcol1 = table1.Columns.Add ("id", typeof (int));
800 DataColumn pcol2 = table1.Columns.Add ("type_int", typeof (int));
802 table2.Constraints.Add ("fk", pcol1, ccol1);
804 dataSet.EnforceConstraints = false;
805 dataAdapter.Fill (dataSet, "table1");
806 dataAdapter.Fill (dataSet, "table2");
808 //Should not throw an exception
809 dataSet.EnforceConstraints = true;
811 Assert.AreEqual (2, table1.Rows.Count, "#1");
812 Assert.AreEqual (2, table2.Rows.Count, "#2");