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 System.Configuration;
39 using NUnit.Framework;
41 namespace MonoTests.System.Data.SqlClient
45 [Category ("sqlserver")]
46 public class SqlDataAdapterTest
48 SqlDataAdapter adapter = null;
50 string connectionString = ConnectionManager.Singleton.ConnectionString;
51 SqlConnection conn = null;
54 The below test will not run everytime, since the region id column is unique
55 so change the regionid if you want the test to pass.
59 public void UpdateTest () {
60 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
62 ConnectionManager.Singleton.OpenConnection ();
63 DataTable dt = new DataTable();
64 SqlDataAdapter da = null;
65 da = new SqlDataAdapter("Select * from employee", conn);
66 //SqlCommandBuilder cb = new SqlCommandBuilder (da);
68 DataRow dr = dt.NewRow();
70 dr ["fname"] = "boston";
71 dr ["dob"] = DateTime.Now.Subtract (new TimeSpan (20*365, 0, 0, 0));
72 dr ["doj"] = DateTime.Now;
77 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
78 ConnectionManager.Singleton.CloseConnection ();
82 private static void OnRowUpdatedTest (object sender, SqlRowUpdatedEventArgs e)
87 private static void OnRowUpdatingTest (object sender, SqlRowUpdatingEventArgs e)
92 private static bool rowUpdated = false;
93 private static bool rowUpdating = false;
95 public void RowUpdatedTest () {
96 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
98 ConnectionManager.Singleton.OpenConnection ();
100 DataSet ds = new DataSet ();
101 SqlDataAdapter da = null;
102 da = new SqlDataAdapter("Select * from employee", conn);
103 //SqlCommandBuilder cb = new SqlCommandBuilder (da);
106 da.RowUpdated += new SqlRowUpdatedEventHandler (OnRowUpdatedTest);
107 da.RowUpdating += new SqlRowUpdatingEventHandler (OnRowUpdatingTest);
111 da.UpdateCommand = new SqlCommand ("Update employee set id = @id");
115 da.RowUpdated -= new SqlRowUpdatedEventHandler (OnRowUpdatedTest);
116 da.RowUpdating -= new SqlRowUpdatingEventHandler (OnRowUpdatingTest);
117 Assert.AreEqual (true, rowUpdated, "RowUpdated");
118 Assert.AreEqual (true, rowUpdating, "RowUpdating");
120 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
121 ConnectionManager.Singleton.CloseConnection ();
127 This needs a errortable created as follows
128 id uniqueidentifier,name char(10) , with values
134 public void NullGuidTest()
136 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
138 ConnectionManager.Singleton.OpenConnection ();
139 DBHelper.ExecuteNonQuery (conn, "create table #tmp_guid_table ( " +
140 " id uniqueidentifier default newid (), " +
142 DBHelper.ExecuteNonQuery (conn, "insert into #tmp_guid_table (name) values (null)");
143 DBHelper.ExecuteNonQuery (conn, "insert into #tmp_guid_table (id, name) values (null, 'bbbb')");
144 SqlDataAdapter da = new SqlDataAdapter("select * from #tmp_guid_table", conn);
145 DataSet ds = new DataSet();
147 Assert.AreEqual (1, ds.Tables.Count, "#1");
148 Assert.AreEqual (DBNull.Value, ds.Tables [0].Rows [1] ["id"], "#2");
150 ConnectionManager.Singleton.CloseConnection ();
152 // the bug 68804 - is that the fill hangs!
153 Assert.AreEqual("Done","Done");
158 public void DefaultConstructorTest ()
160 adapter = new SqlDataAdapter ();
161 Assert.AreEqual (MissingMappingAction.Passthrough,
162 adapter.MissingMappingAction,
163 "#1 Missing Mapping acttion default to Passthrough");
164 Assert.AreEqual (MissingSchemaAction.Add,
165 adapter.MissingSchemaAction,
166 "#2 Missing Schme action default to Add");
170 public void OverloadedConstructorsTest ()
172 SqlCommand selCmd = new SqlCommand ("Select * from numeric_family");
173 adapter = new SqlDataAdapter (selCmd);
174 Assert.AreEqual (MissingMappingAction.Passthrough,
175 adapter.MissingMappingAction,
176 "#1 Missing Mapping acttion default to Passthrough");
177 Assert.AreEqual (MissingSchemaAction.Add,
178 adapter.MissingSchemaAction,
179 "#2 Missing Schme action default to Add");
180 Assert.AreSame (selCmd, adapter.SelectCommand,
181 "#3 Select Command shud be a ref to the arg passed");
183 conn = new SqlConnection (connectionString);
184 String selStr = "Select * from numeric_family";
185 adapter = new SqlDataAdapter (selStr, conn);
186 Assert.AreEqual (MissingMappingAction.Passthrough,
187 adapter.MissingMappingAction,
188 "#4 Missing Mapping acttion default to Passthrough");
189 Assert.AreEqual (MissingSchemaAction.Add,
190 adapter.MissingSchemaAction,
191 "#5 Missing Schme action default to Add");
192 Assert.AreSame (selStr, adapter.SelectCommand.CommandText,
193 "#6 Select Command shud be a ref to the arg passed");
194 Assert.AreSame (conn, adapter.SelectCommand.Connection,
195 "#7 cmd.connection shud be t ref to connection obj");
197 selStr = "Select * from numeric_family";
198 adapter = new SqlDataAdapter (selStr, connectionString);
199 Assert.AreEqual (MissingMappingAction.Passthrough,
200 adapter.MissingMappingAction,
201 "#8 Missing Mapping action shud default to Passthrough");
202 Assert.AreEqual (MissingSchemaAction.Add,
203 adapter.MissingSchemaAction,
204 "#9 Missing Schema action shud default to Add");
205 Assert.AreSame (selStr,
206 adapter.SelectCommand.CommandText,
208 Assert.AreEqual (connectionString,
209 adapter.SelectCommand.Connection.ConnectionString,
213 public void Fill_Test_ConnState ()
215 //Check if Connection State is maintained correctly ..
216 data = new DataSet ("test1");
217 adapter = new SqlDataAdapter ("select id from numeric_family where id=1",
219 SqlCommand cmd = adapter.SelectCommand ;
221 Assert.AreEqual (ConnectionState.Closed,
222 cmd.Connection.State, "#1 Connection shud be in closed state");
224 Assert.AreEqual (1, data.Tables.Count, "#2 One table shud be populated");
225 Assert.AreEqual (ConnectionState.Closed, cmd.Connection.State,
226 "#3 Connection shud be closed state");
228 data = new DataSet ("test2");
229 cmd.Connection.Open ();
230 Assert.AreEqual (ConnectionState.Open, cmd.Connection.State,
231 "#3 Connection shud be open");
233 Assert.AreEqual (1, data.Tables.Count, "#4 One table shud be populated");
234 Assert.AreEqual (ConnectionState.Open, cmd.Connection.State,
235 "#5 Connection shud be open");
236 cmd.Connection.Close ();
238 // Test if connection is closed when exception occurs
239 cmd.CommandText = "select id1 from numeric_family";
243 if (cmd.Connection.State == ConnectionState.Open) {
244 cmd.Connection.Close ();
245 Assert.Fail ("# Connection Shud be Closed");
251 public void Fill_Test_Data ()
253 //Check if a table is created for each resultset
254 String batchQuery = "Select id,type_bit,type_int from numeric_family;";
255 batchQuery += "Select type_bit,type_bigint from numeric_family";
256 adapter = new SqlDataAdapter (batchQuery, connectionString);
257 data = new DataSet ("test1");
259 Assert.AreEqual (2, data.Tables.Count,"#1 2 Table shud be created");
261 //Check if Table and Col are named correctly for unnamed columns
262 string query = "Select 10,20 from numeric_family;" ;
263 query += "Select 10,20 from numeric_family";
264 adapter = new SqlDataAdapter (query, connectionString);
265 data = new DataSet ("test2");
267 Assert.AreEqual (2, data.Tables.Count,
268 "#2 2 Tables shud be created");
269 Assert.AreEqual ("Table", data.Tables[0].TableName, "#3");
270 Assert.AreEqual ("Table1", data.Tables[1].TableName, "#4");
271 Assert.AreEqual ("Column1", data.Tables[0].Columns[0].ColumnName, "#5");
272 Assert.AreEqual ("Column2", data.Tables[0].Columns[1].ColumnName, "#6");
273 Assert.AreEqual ("Column1", data.Tables[1].Columns[0].ColumnName, "#7");
274 Assert.AreEqual ("Column2", data.Tables[1].Columns[1].ColumnName, "#8");
276 //Check if dup columns are named correctly
277 query = "select A.id ,B.id , C.id from numeric_family A, ";
278 query += "numeric_family B , numeric_family C";
279 adapter = new SqlDataAdapter (query, connectionString);
280 data = new DataSet ("test3");
283 // NOTE msdotnet contradicts documented behavior
284 // as per documentation the column names should be
285 // id1,id2,id3 .. but msdotnet returns id,id1,id2
286 Assert.AreEqual ("id", data.Tables[0].Columns[0].ColumnName,
287 "#9 if colname is duplicated ,shud be col,col1,col2 etc");
288 Assert.AreEqual ("id1", data.Tables[0].Columns[1].ColumnName,
289 "#10 if colname is duplicated ,shud be col,col1,col2 etc");
290 Assert.AreEqual ("id2", data.Tables[0].Columns[2].ColumnName,
291 "#11 if colname is duplicated ,shud be col,col1,col2 etc");
293 // Test if tables are created and named accordingly ,
294 // but only for those queries returning result sets
295 query = "update numeric_family set id=100 where id=50;";
296 query += "select * from numeric_family";
297 adapter = new SqlDataAdapter (query, connectionString);
298 data = new DataSet ("test4");
300 Assert.AreEqual (1 ,data.Tables.Count,
301 "#12 Tables shud be named only for queries returning a resultset");
302 Assert.AreEqual ("Table", data.Tables[0].TableName,
303 "#13 The first resutlset shud have 'Table' as its name");
305 // Test behavior with an outerjoin
306 query = "select A.id,B.type_bit from numeric_family A LEFT OUTER JOIN ";
307 query += "numeric_family B on A.id = B.type_bit";
308 adapter = new SqlDataAdapter (query, connectionString);
309 data = new DataSet ("test5");
311 Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length,
312 "#14 Primary Key shudnt be set if an outer join is performed");
313 Assert.AreEqual (0, data.Tables[0].Constraints.Count,
314 "#15 Constraints shudnt be set if an outer join is performed");
315 adapter = new SqlDataAdapter ("select id from numeric_family",
317 data = new DataSet ("test6");
318 adapter.Fill (data, 1, 1, "numeric_family");
319 Assert.AreEqual (1, data.Tables[0].Rows.Count, "#16");
320 Assert.AreEqual (2, data.Tables[0].Rows[0][0], "#17");
322 // only one test for DataTable.. DataSet tests covers others
323 adapter = new SqlDataAdapter ("select id from numeric_family",
325 DataTable table = new DataTable ("table1");
326 adapter.Fill (table);
327 Assert.AreEqual (4, table.Rows.Count , "#18");
331 public void Fill_Test_PriKey ()
333 // Test if Primary Key & Constraints Collection is correct
334 adapter = new SqlDataAdapter ("select id,type_bit from numeric_family",
336 adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
337 data = new DataSet ("test1");
339 Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length,
340 "#1 Primary Key shud be set");
341 Assert.AreEqual (1, data.Tables[0].Constraints.Count,
342 "#2 Constraints shud be set");
343 Assert.AreEqual (4, data.Tables[0].Rows.Count,
344 "#3 No Of Rows shud be 4");
346 // Test if data is correctly merged
348 Assert.AreEqual (4, data.Tables[0].Rows.Count,
349 "#4 No of Row shud still be 4");
351 // Test if rows are appended and not merged
352 // when primary key is not returned in the result-set
353 string query = "Select type_int,type_bigint from numeric_family";
354 adapter.SelectCommand.CommandText = query;
355 data = new DataSet ("test2");
357 Assert.AreEqual (4, data.Tables[0].Rows.Count,
358 "#5 No of Rows shud be 4");
360 Assert.AreEqual (8, data.Tables[0].Rows.Count,
361 "#6 No of Rows shud double now");
365 public void Fill_Test_Exceptions ()
367 adapter = new SqlDataAdapter ("select * from numeric_family",
369 data = new DataSet ("test1");
371 adapter.Fill (data, -1, 0, "numeric_family");
372 Assert.Fail ("#1 Exception shud be thrown:Incorrect Arguments");
373 }catch (AssertionException e){
375 }catch (Exception e){
376 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
377 "#2 Incorrect Exception : " + e);
380 // conn is not closed due to a bug..
381 // can be removed later
382 adapter.SelectCommand.Connection.Close ();
385 adapter.Fill (data , 0 , -1 , "numeric_family");
386 Assert.Fail ("#3 Exception shud be thrown:Incorrect Arguments");
387 }catch (AssertionException e){
389 }catch (Exception e){
390 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
391 "#4 Incorrect Exception : " + e);
393 // conn is curr not closed.. can be removed later
394 adapter.SelectCommand.Connection.Close ();
397 // NOTE msdotnet contradicts documented behavior
398 // InvalidOperationException is expected if table is not valid
400 adapter.Fill (data , 0 , 0 , "invalid_talbe_name");
401 }catch (InvalidOperationException e) {
403 }catch (Exception e){
404 Assert.Fail ("#5 Exception shud be thrown : incorrect arugments ");
406 Assert.IsNotNull (ex , "#6 Exception shud be thrown : incorrect args ");
407 adapter.SelectCommand.Connection.Close (); // tmp .. can be removed once the bug if fixed
412 adapter.Fill ( null , 0 , 0 , "numeric_family");
413 Assert.Fail ( "#7 Exception shud be thrown : Invalid Dataset");
414 }catch (AssertionException e){
416 }catch (ArgumentNullException) {
418 }catch (Exception e) {
419 Assert.AreEqual (typeof(SystemException), e.GetType(),
420 "#8 Incorrect Exception : " + e);
422 // conn is currently not being closed..
423 //need to be removed once behavior is fixed
424 adapter.SelectCommand.Connection.Close ();
426 adapter.SelectCommand.Connection = null;
429 Assert.Fail ("#9 Exception shud be thrown : Invalid Connection");
430 }catch (AssertionException e){
432 }catch (Exception e){
433 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
434 "#10 Incorrect Exception : " + e);
438 bool FillErrorContinue = false;
440 public void Fill_Test_FillErrorTest ()
442 string query = "select type_bigint from numeric_family where id=1 or id=4 ";
444 DataSet ds = new DataSet ();
445 DataTable table = ds.Tables.Add ("test");
446 table.Columns.Add ("col", typeof (int));
448 adapter = new SqlDataAdapter (query, connectionString);
449 DataTableMapping mapping = adapter.TableMappings.Add ("numeric_family", "test");
450 mapping.ColumnMappings.Add ("type_bigint", "col");
454 count = adapter.Fill (ds, "numeric_family");
455 Assert.Fail ("#1 Overflow exception must be thrown");
456 }catch (Exception e) {
457 Assert.AreEqual (typeof (OverflowException), e.GetType (), "#1a Expected exception is OverflowException");
459 Assert.AreEqual (0, ds.Tables [0].Rows.Count, "#2");
460 Assert.AreEqual (0, count, "#3");
462 adapter.FillError += new FillErrorEventHandler (ErrorHandler);
463 FillErrorContinue = false;
465 count = adapter.Fill (ds, "numeric_family");
466 Assert.Fail ("#4 Overflow exception must be thrown");
467 }catch (OverflowException e) {
469 Assert.AreEqual (0, ds.Tables [0].Rows.Count, "#5");
470 Assert.AreEqual (0, count, "#6");
472 FillErrorContinue = true;
473 count = adapter.Fill (ds, "numeric_family");
474 // 1 row shud be filled
475 Assert.AreEqual (1, ds.Tables [0].Rows.Count, "#7");
476 Assert.AreEqual (1, count, "#8");
479 void ErrorHandler (object sender, FillErrorEventArgs args)
481 args.Continue = FillErrorContinue;
485 public void GetFillParametersTest ()
487 string query = "select id, type_bit from numeric_family where id > @param1";
488 adapter = new SqlDataAdapter (query, connectionString);
489 IDataParameter[] param = adapter.GetFillParameters ();
490 Assert.AreEqual (0, param.Length, "#1 size shud be 0");
492 SqlParameter param1 = new SqlParameter ();
493 param1.ParameterName = "@param1";
495 adapter.SelectCommand.Parameters.Add (param1);
497 param = adapter.GetFillParameters ();
498 Assert.AreEqual (1, param.Length, "#2 count shud be 1");
499 Assert.AreEqual (param1, param[0], "#3 Params shud be equal");
503 public void FillSchemaTest ()
507 // Test if connection is closed if excepton occurs during fill schema
508 query = "select * from invalid_table";
509 adapter = new SqlDataAdapter (query, connectionString);
510 data = new DataSet ("test");
512 adapter.FillSchema (data , SchemaType.Source);
513 }catch (Exception e){
514 if ( adapter.SelectCommand.Connection.State != ConnectionState.Closed)
516 Assert.Fail ("#0 Conn shud be closed if exception occurs");
517 adapter.SelectCommand.Connection.Close();
521 // Test Primary Key is set (since primary key column returned)
522 query = "select id, type_int from numeric_family where id=1";
523 adapter = new SqlDataAdapter (query, connectionString);
524 data = new DataSet ("test1");
525 adapter.FillSchema (data , SchemaType.Source);
527 Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length,
528 "#1 Primary Key property must be set");
530 // Test Primary Key is not set (since primary key column is returned)
531 query = "select type_bit, type_int from numeric_family where id=1";
532 adapter = new SqlDataAdapter (query, connectionString);
533 data = new DataSet ("test2");
534 adapter.FillSchema (data, SchemaType.Source);
535 Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length,
536 "#2 Primary Key property should not be set");
538 // Test multiple tables are created for a batch query
539 query = "Select id ,type_bit from numeric_family;" ;
540 query += "Select id,type_bit,type_int from numeric_family;";
541 data = new DataSet ("test3");
542 adapter = new SqlDataAdapter (query, connectionString);
543 adapter.FillSchema (data , SchemaType.Source);
544 Assert.AreEqual (2 , data.Tables.Count , "#3 A table shud be created for each Result Set");
545 Assert.AreEqual (2 , data.Tables[0].Columns.Count , "#4 should have 2 columns");
546 Assert.AreEqual (3 , data.Tables[1].Columns.Count , "#5 Should have 3 columns");
548 // Test if table names and column names are filled correctly
549 query = "select 10,20 from numeric_family;" ;
550 query += "select 10,20 from numeric_family;";
551 adapter = new SqlDataAdapter (query, connectionString);
552 data = new DataSet ("test4");
554 adapter.FillSchema (data , SchemaType.Source);
555 }catch (Exception e){
556 Assert.Fail ("#3 Unexpected Exception : " + e);
558 Assert.AreEqual ( "Table", data.Tables[0].TableName);
559 Assert.AreEqual ( "Table1", data.Tables[1].TableName);
560 Assert.AreEqual ( "Column1", data.Tables[0].Columns[0].ColumnName,
561 "#6 Unnamed col shud be named as 'ColumnN'");
562 Assert.AreEqual ( "Column2", data.Tables[0].Columns[1].ColumnName,
563 "#7 Unnamed col shud be named as 'ColumnN'");
564 Assert.AreEqual ( "Column1", data.Tables[1].Columns[0].ColumnName,
565 "#8 Unnamed col shud be named as 'ColumnN'");
566 Assert.AreEqual ( "Column2", data.Tables[1].Columns[1].ColumnName,
567 "#9 Unnamed col shud be named as 'ColumnN'");
568 Assert.AreEqual (ConnectionState.Closed, adapter.SelectCommand.Connection.State,
569 "#10 Connection shud be closed");
571 // Test if mapping works correctly
572 // doesent work in both mono and msdotnet
573 // gotto check if something is wrong
575 query = "select id,type_bit from numeric_family";
576 adapter = new SqlDataAdapter (query, connectionString);
577 data = new DataSet ("test");
578 DataTable table = data.Tables.Add ("numeric_family_1");
579 table.Columns.Add ("id");
580 table.Columns.Add ("type_bit");
581 DataTableMapping map = adapter.TableMappings.Add("numeric_family_1",
583 map.ColumnMappings.Add ("id", "id_1");
584 map.ColumnMappings.Add ("type_bit", "type_bit_1");
585 adapter.FillSchema (data, SchemaType.Source, "numeric_family");
586 foreach (DataTable tab in data.Tables){
587 Console.WriteLine ("Table == {0}",tab.TableName);
588 foreach (DataColumn col in tab.Columns)
589 Console.WriteLine (" Col = {0} " , col.ColumnName);
595 public void MissingSchemaActionTest ()
597 adapter = new SqlDataAdapter (
598 "select id,type_bit,type_int from numeric_family where id<=4",
600 data = new DataSet ();
601 Assert.AreEqual (MissingSchemaAction.Add, adapter.MissingSchemaAction,
605 Assert.AreEqual (1, data.Tables.Count , "#1 One table shud be populated");
606 Assert.AreEqual (3, data.Tables[0].Columns.Count, "#2 Missing cols are added");
607 Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length, "#3 Default Value");
609 adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
612 Assert.AreEqual (3, data.Tables[0].Columns.Count,
613 "#4 Missing cols are added");
614 Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length, "#5 Default Value");
616 adapter.MissingSchemaAction = MissingSchemaAction.Ignore ;
619 Assert.AreEqual (0, data.Tables.Count, "#6 Data shud be ignored");
621 adapter.MissingSchemaAction = MissingSchemaAction.Error ;
625 Assert.Fail ("#8 Exception shud be thrown: Schema Mismatch");
626 }catch (AssertionException e) {
628 }catch (Exception e){
629 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
630 "#9 Incorrect Exception : "+e);
633 // Test for invalid MissingSchema Value
635 adapter.MissingSchemaAction = (MissingSchemaAction)(-5000);
636 Assert.Fail ("#10 Exception shud be thrown: Invalid Value");
637 }catch (AssertionException e){
639 }catch (Exception e){
640 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
641 "#11 Incorrect Exception : " +e);
644 // Tests if Data is filled correctly if schema is defined
645 // manually and MissingSchemaAction.Error is set
646 adapter.MissingSchemaAction = MissingSchemaAction.Error;
648 DataTable table = data.Tables.Add ("Table");
649 table.Columns.Add ("id");
650 table.Columns.Add ("type_bit");
651 table.Columns.Add ("type_int");
654 Assert.AreEqual (1, data.Tables.Count, "#12");
655 Assert.AreEqual (4, data.Tables[0].Rows.Count, "#13");
656 }catch (Exception e) {
657 Assert.Fail ("#12 Unexpected Exception : " + e);
662 public void MissingMappingActionTest ()
664 adapter = new SqlDataAdapter ("select id,type_bit from numeric_family where id=1",
666 data = new DataSet ();
667 Assert.AreEqual (adapter.MissingMappingAction,
668 MissingMappingAction.Passthrough,
671 Assert.AreEqual (1, data.Tables.Count,
672 "#2 One Table shud be created");
673 Assert.AreEqual (2, data.Tables[0].Columns.Count,
674 "#3 Two Cols shud be created");
676 adapter.MissingMappingAction = MissingMappingAction.Ignore;
679 Assert.AreEqual (0, data.Tables.Count, "#4 No table shud be created");
681 adapter.MissingMappingAction = MissingMappingAction.Error;
685 Assert.Fail ("#5 Exception shud be thrown : Mapping is missing");
686 }catch (AssertionException e){
688 }catch (Exception e) {
689 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
690 "#6 Incorrect Exception : " + e);
694 adapter.MissingMappingAction = (MissingMappingAction)(-5000);
695 Assert.Fail ("#7 Exception shud be thrown : Invalid Value");
696 }catch (AssertionException e){
698 }catch (Exception e){
699 Assert.AreEqual (typeof(ArgumentException), e.GetType(),
700 "#8 Incorrect Exception : " +e);
703 // Test if mapping the column and table names works correctly
704 adapter.MissingMappingAction = MissingMappingAction.Error;
706 DataTable table = data.Tables.Add ("numeric_family_1");
707 table.Columns.Add ("id_1");
708 table.Columns.Add ("type_bit_1");
709 table.Columns.Add ("type_int_1");
710 DataTableMapping tableMap = adapter.TableMappings.Add ("numeric_family",
712 tableMap.ColumnMappings.Add ("id", "id_1");
713 tableMap.ColumnMappings.Add ("type_bit", "type_bit_1");
714 tableMap.ColumnMappings.Add ("type_int", "type_int_1");
715 adapter.Fill (data,"numeric_family");
716 Assert.AreEqual (1, data.Tables.Count ,
717 "#8 The DataTable shud be correctly mapped");
718 Assert.AreEqual (3, data.Tables[0].Columns.Count,
719 "#9 The DataColumns shud be corectly mapped");
720 Assert.AreEqual (1, data.Tables[0].Rows.Count,
721 "#10 Data shud be populated if mapping is correct");
724 // Test case for bug #76433
726 public void FillSchema_ValuesTest()
728 SqlConnection conn = new SqlConnection(connectionString);
731 IDbCommand command = conn.CreateCommand();
734 String cmd = "Create Table #tmp_TestTable (" ;
735 cmd += "Field1 DECIMAL (10) NOT NULL,";
736 cmd += "Field2 DECIMAL(19))";
737 command.CommandText = cmd;
738 command.ExecuteNonQuery();
740 DataSet dataSet = new DataSet();
741 string selectString = "SELECT * FROM #tmp_TestTable";
742 IDbDataAdapter dataAdapter = new SqlDataAdapter (
744 dataAdapter.FillSchema(dataSet, SchemaType.Mapped);
746 Assert.AreEqual (1, dataSet.Tables.Count, "#1");
748 DataColumn col = dataSet.Tables[0].Columns[0];
749 Assert.IsFalse (dataSet.Tables[0].Columns[0].AllowDBNull,"#2");
750 Assert.IsTrue (dataSet.Tables[0].Columns[1].AllowDBNull,"#3");
755 public void Fill_CheckSchema ()
757 SqlConnection conn = new SqlConnection(connectionString);
761 IDbCommand command = conn.CreateCommand();
764 String cmd = "Create Table #tmp_TestTable (" ;
765 cmd += "id int primary key,";
766 cmd += "field int not null)";
767 command.CommandText = cmd;
768 command.ExecuteNonQuery();
770 DataSet dataSet = new DataSet();
771 string selectString = "SELECT * from #tmp_TestTable";
772 IDbDataAdapter dataAdapter = new SqlDataAdapter (
774 dataAdapter.Fill (dataSet);
775 Assert.IsTrue (dataSet.Tables[0].Columns[1].AllowDBNull, "#1");
776 Assert.AreEqual (0, dataSet.Tables[0].PrimaryKey.Length, "#2");
779 dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey ;
780 dataAdapter.Fill (dataSet);
781 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#3");
782 Assert.AreEqual (1, dataSet.Tables[0].PrimaryKey.Length, "#4");
787 public void FillSchema_CheckSchema ()
789 SqlConnection conn = new SqlConnection(connectionString);
793 IDbCommand command = conn.CreateCommand();
796 String cmd = "Create Table #tmp_TestTable (" ;
797 cmd += "id int primary key,";
798 cmd += "field int not null)";
799 command.CommandText = cmd;
800 command.ExecuteNonQuery();
802 DataSet dataSet = new DataSet();
803 string selectString = "SELECT * from #tmp_TestTable";
804 IDbDataAdapter dataAdapter = new SqlDataAdapter (
807 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
808 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#1");
811 dataAdapter.MissingSchemaAction = MissingSchemaAction.Add;
812 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
813 Assert.IsFalse (dataSet.Tables[0].Columns[1].AllowDBNull, "#2");
816 dataAdapter.MissingSchemaAction = MissingSchemaAction.Ignore;
817 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
818 Assert.AreEqual (0, dataSet.Tables.Count, "#3");
821 dataAdapter.MissingSchemaAction = MissingSchemaAction.Error;
823 dataAdapter.FillSchema (dataSet, SchemaType.Mapped);
824 Assert.Fail ("#4 Error should be thrown");
825 } catch (InvalidOperationException e) {
831 public void CreateViewSSPITest ()
833 SqlConnection conn = new SqlConnection (ConfigurationSettings.AppSettings ["SSPIConnString"]);
836 string sql = "create view MONO_TEST_VIEW as select * from Numeric_family";
838 SqlCommand dbcmd = new SqlCommand( sql, conn );
839 dbcmd.ExecuteNonQuery();
841 sql = "drop view MONO_TEST_VIEW";
843 dbcmd = new SqlCommand( sql, conn );
844 dbcmd.ExecuteNonQuery();
850 public void Fill_RelatedTables ()
852 SqlConnection conn = new SqlConnection(connectionString);
855 IDbCommand command = conn.CreateCommand();
857 DataSet dataSet = new DataSet();
858 string selectString = "SELECT id, type_int from numeric_family where id < 3";
859 DbDataAdapter dataAdapter = new SqlDataAdapter (selectString,conn);
861 DataTable table2 = dataSet.Tables.Add ("table2");
862 DataColumn ccol1 = table2.Columns.Add ("id", typeof (int));
863 DataColumn ccol2 = table2.Columns.Add ("type_int", typeof (int));
865 DataTable table1 = dataSet.Tables.Add ("table1");
866 DataColumn pcol1 = table1.Columns.Add ("id", typeof (int));
867 DataColumn pcol2 = table1.Columns.Add ("type_int", typeof (int));
869 table2.Constraints.Add ("fk", pcol1, ccol1);
871 dataSet.EnforceConstraints = false;
872 dataAdapter.Fill (dataSet, "table1");
873 dataAdapter.Fill (dataSet, "table2");
875 //Should not throw an exception
876 dataSet.EnforceConstraints = true;
878 Assert.AreEqual (2, table1.Rows.Count, "#1");
879 Assert.AreEqual (2, table2.Rows.Count, "#2");
886 [Category ("sqlserver")]
887 public class SqlDataAdapterInheritTest : DbDataAdapter
889 SqlConnection conn = null;
892 public void FillDataAdapterTest () {
893 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
895 ConnectionManager.Singleton.OpenConnection ();
896 DataTable dt = new DataTable();
897 SqlCommand command = new SqlCommand ();
898 command.CommandText = "Select * from employee;";
899 command.Connection = conn;
900 SelectCommand = command;
901 Fill (dt, command.ExecuteReader ());
902 Assert.AreEqual (6, dt.Rows.Count, "#1");
903 Assert.AreEqual (6, dt.Columns.Count, "#1");
905 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
906 ConnectionManager.Singleton.CloseConnection ();