2 // SqlCommandTest.cs - NUnit Test Cases for testing the
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;
36 using System.Data.Sql;
37 using System.Globalization;
40 using NUnit.Framework;
42 namespace MonoTests.System.Data.SqlClient
45 [Category ("sqlserver")]
46 public class SqlCommandTest
50 string connectionString = ConnectionManager.Singleton.ConnectionString;
53 static readonly decimal SMALLMONEY_MAX = 214748.3647m;
54 static readonly decimal SMALLMONEY_MIN = -214748.3648m;
59 engine = ConnectionManager.Singleton.Engine;
63 public void TearDown ()
76 [Test] // ctor (String, SqlConnection, SqlTransaction)
77 public void Constructor4 ()
79 string cmdText = "select @@version";
81 SqlTransaction trans = null;
82 SqlConnection connA = null;
83 SqlConnection connB = null;
85 // transaction from same connection
87 connA = new SqlConnection (connectionString);
90 trans = connA.BeginTransaction ();
91 cmd = new SqlCommand (cmdText, connA, trans);
93 Assert.AreEqual (cmdText, cmd.CommandText, "#A1");
94 Assert.AreEqual (30, cmd.CommandTimeout, "#A2");
95 Assert.AreEqual (CommandType.Text, cmd.CommandType, "#A3");
96 Assert.AreSame (connA, cmd.Connection, "#A4");
97 Assert.IsNull (cmd.Container, "#A5");
98 Assert.IsTrue (cmd.DesignTimeVisible, "#A6");
99 Assert.IsNull (cmd.Notification, "#A7");
100 Assert.IsTrue (cmd.NotificationAutoEnlist, "#A8");
101 Assert.IsNotNull (cmd.Parameters, "#A9");
102 Assert.AreEqual (0, cmd.Parameters.Count, "#A10");
103 Assert.IsNull (cmd.Site, "#A11");
104 Assert.AreSame (trans, cmd.Transaction, "#A12");
105 Assert.AreEqual (UpdateRowSource.Both, cmd.UpdatedRowSource, "#A13");
113 // transaction from other connection
115 connA = new SqlConnection (connectionString);
117 connB = new SqlConnection (connectionString);
120 trans = connB.BeginTransaction ();
121 cmd = new SqlCommand (cmdText, connA, trans);
123 Assert.AreEqual (cmdText, cmd.CommandText, "#B1");
124 Assert.AreEqual (30, cmd.CommandTimeout, "#B2");
125 Assert.AreEqual (CommandType.Text, cmd.CommandType, "#B3");
126 Assert.AreSame (connA, cmd.Connection, "#B4");
127 Assert.IsNull (cmd.Container, "#B5");
128 Assert.IsTrue (cmd.DesignTimeVisible, "#B6");
129 Assert.IsNull (cmd.Notification, "#B7");
130 Assert.IsTrue (cmd.NotificationAutoEnlist, "#B8");
131 Assert.IsNotNull (cmd.Parameters, "#B9");
132 Assert.AreEqual (0, cmd.Parameters.Count, "#B10");
133 Assert.IsNull (cmd.Site, "#B11");
134 Assert.AreSame (trans, cmd.Transaction, "#B12");
135 Assert.AreEqual (UpdateRowSource.Both, cmd.UpdatedRowSource, "#B13");
144 [Test] // bug #341743
145 public void Dispose_Connection_Disposed ()
147 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
148 ConnectionManager.Singleton.OpenConnection ();
150 cmd = conn.CreateCommand ();
151 cmd.CommandText = "SELECT 'a'";
152 cmd.ExecuteNonQuery ();
156 Assert.AreSame (conn, cmd.Connection, "#1");
158 Assert.AreSame (conn, cmd.Connection, "#2");
162 public void ExecuteScalar ()
164 conn = new SqlConnection (connectionString);
165 cmd = new SqlCommand ("", conn);
166 cmd.CommandText = "Select count(*) from numeric_family where id<=4";
168 // Check the Return value for a Correct Query
171 result = cmd.ExecuteScalar ();
172 Assert.AreEqual (4, (int) result, "#A1 Query Result returned is incorrect");
174 cmd.CommandText = "select id , type_bit from numeric_family order by id asc";
175 result = Convert.ToInt32 (cmd.ExecuteScalar ());
176 Assert.AreEqual (1, result,
177 "#A2 ExecuteScalar Should return (1,1) the result set");
179 cmd.CommandText = "select id from numeric_family where id=-1";
180 result = cmd.ExecuteScalar ();
181 Assert.IsNull (result, "#A3 Null should be returned if result set is empty");
183 // Check SqlException is thrown for Invalid Query
184 cmd.CommandText = "select count* from numeric_family";
186 result = cmd.ExecuteScalar ();
188 } catch (SqlException ex) {
189 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#B2");
190 Assert.AreEqual ((byte) 15, ex.Class, "#B3");
191 Assert.IsNull (ex.InnerException, "#B4");
192 Assert.IsNotNull (ex.Message, "#B5");
193 if (ClientVersion == 7) {
194 // Incorrect syntax near '*'
195 Assert.IsTrue (ex.Message.IndexOf ("'*'") != -1, "#B6: " + ex.Message);
196 Assert.AreEqual (170, ex.Number, "#B7");
198 // Incorrect syntax near the keyword 'from'
199 Assert.IsTrue (ex.Message.IndexOf ("'from'") != -1, "#B6: " + ex.Message);
200 Assert.AreEqual (156, ex.Number, "#B7");
202 Assert.AreEqual ((byte) 1, ex.State, "#B8");
205 // Parameterized stored procedure calls
208 string string_value = "output value changed";
209 string return_value = "first column of first rowset";
212 "create procedure #tmp_executescalar_outparams " +
213 " (@p1 int, @p2 int out, @p3 varchar(200) out) " +
215 "select '" + return_value + "' as 'col1', @p1 as 'col2' " +
216 "set @p2 = @p2 * 2 " +
217 "set @p3 = N'" + string_value + "' " +
218 "select 'second rowset' as 'col1', 2 as 'col2' " +
221 cmd.CommandType = CommandType.Text;
222 cmd.ExecuteNonQuery ();
224 cmd.CommandText = "#tmp_executescalar_outparams";
225 cmd.CommandType = CommandType.StoredProcedure;
227 SqlParameter p1 = new SqlParameter ();
228 p1.ParameterName = "@p1";
229 p1.Direction = ParameterDirection.Input;
230 p1.DbType = DbType.Int32;
231 p1.Value = int_value;
232 cmd.Parameters.Add (p1);
234 SqlParameter p2 = new SqlParameter ();
235 p2.ParameterName = "@p2";
236 p2.Direction = ParameterDirection.InputOutput;
237 p2.DbType = DbType.Int32;
238 p2.Value = int_value;
239 cmd.Parameters.Add (p2);
241 SqlParameter p3 = new SqlParameter ();
242 p3.ParameterName = "@p3";
243 p3.Direction = ParameterDirection.Output;
244 p3.DbType = DbType.String;
246 cmd.Parameters.Add (p3);
248 result = cmd.ExecuteScalar ();
249 Assert.AreEqual (return_value, result, "#C1 ExecuteScalar Should return 'first column of first rowset'");
250 Assert.AreEqual (int_value * 2, p2.Value, "#C2 ExecuteScalar should fill the parameter collection with the outputted values");
251 Assert.AreEqual (string_value, p3.Value, "#C3 ExecuteScalar should fill the parameter collection with the outputted values");
256 cmd.ExecuteScalar ();
257 Assert.Fail ("#D1 Query should throw System.InvalidOperationException due to size = 0 and value = null");
258 } catch (InvalidOperationException ex) {
259 // String[2]: the Size property has an invalid
261 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#D2");
262 Assert.IsNull (ex.InnerException, "#D3");
263 Assert.IsNotNull (ex.Message, "#D4");
270 public void ExecuteScalar_CommandText_Empty ()
272 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
273 ConnectionManager.Singleton.OpenConnection ();
275 cmd = conn.CreateCommand ();
278 cmd.ExecuteScalar ();
280 } catch (InvalidOperationException ex) {
281 // ExecuteScalar: CommandText property
282 // has not been initialized
283 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
284 Assert.IsNull (ex.InnerException, "#A3");
285 Assert.IsNotNull (ex.Message, "#A4");
286 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar"), "#A5:" + ex.Message);
289 cmd.CommandText = string.Empty;
292 cmd.ExecuteScalar ();
294 } catch (InvalidOperationException ex) {
295 // ExecuteScalar: CommandText property
296 // has not been initialized
297 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
298 Assert.IsNull (ex.InnerException, "#B3");
299 Assert.IsNotNull (ex.Message, "#B4");
300 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar"), "#B5:" + ex.Message);
303 cmd.CommandText = null;
306 cmd.ExecuteScalar ();
308 } catch (InvalidOperationException ex) {
309 // ExecuteScalar: CommandText property
310 // has not been initialized
311 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#C2");
312 Assert.IsNull (ex.InnerException, "#C3");
313 Assert.IsNotNull (ex.Message, "#C4");
314 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar"), "#C5:" + ex.Message);
319 public void ExecuteScalar_Connection_PendingTransaction ()
321 conn = new SqlConnection (connectionString);
324 using (SqlTransaction trans = conn.BeginTransaction ()) {
325 cmd = new SqlCommand ("select @@version", conn);
328 cmd.ExecuteScalar ();
330 } catch (InvalidOperationException ex) {
331 // ExecuteScalar requires the command
332 // to have a transaction object when the
333 // connection assigned to the command is
334 // in a pending local transaction. The
335 // Transaction property of the command
336 // has not been initialized
337 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
338 Assert.IsNull (ex.InnerException, "#3");
339 Assert.IsNotNull (ex.Message, "#4");
340 Assert.IsTrue (ex.Message.IndexOf ("ExecuteScalar") != -1, "#5:" + ex.Message);
346 public void ExecuteScalar_Query_Invalid ()
348 conn = new SqlConnection (connectionString);
351 cmd = new SqlCommand ("InvalidQuery", conn);
353 cmd.ExecuteScalar ();
355 } catch (SqlException ex) {
356 // Could not find stored procedure 'InvalidQuery'
357 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
358 Assert.AreEqual ((byte) 16, ex.Class, "#3");
359 Assert.IsNull (ex.InnerException, "#4");
360 Assert.IsNotNull (ex.Message, "#5");
361 Assert.IsTrue (ex.Message.IndexOf ("'InvalidQuery'") != -1, "#6:" + ex.Message);
362 Assert.AreEqual (2812, ex.Number, "#7");
363 Assert.AreEqual ((byte) 62, ex.State, "#8");
368 public void ExecuteScalar_Transaction_NotAssociated ()
370 SqlTransaction trans = null;
371 SqlConnection connA = null;
372 SqlConnection connB = null;
375 connA = new SqlConnection (connectionString);
378 connB = new SqlConnection (connectionString);
381 trans = connA.BeginTransaction ();
383 cmd = new SqlCommand ("select @@version", connB, trans);
386 cmd.ExecuteScalar ();
388 } catch (InvalidOperationException ex) {
389 // The transaction object is not associated
390 // with the connection object
391 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
392 Assert.IsNull (ex.InnerException, "#A3");
393 Assert.IsNotNull (ex.Message, "#A4");
398 cmd = new SqlCommand ("select @@version", connB);
399 cmd.Transaction = trans;
402 cmd.ExecuteScalar ();
404 } catch (InvalidOperationException ex) {
405 // The transaction object is not associated
406 // with the connection object
407 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
408 Assert.IsNull (ex.InnerException, "#B3");
409 Assert.IsNotNull (ex.Message, "#B4");
424 public void ExecuteScalar_Transaction_Only ()
426 SqlTransaction trans = null;
428 conn = new SqlConnection (connectionString);
430 trans = conn.BeginTransaction ();
432 cmd = new SqlCommand ("select @@version");
433 cmd.Transaction = trans;
436 cmd.ExecuteScalar ();
438 } catch (InvalidOperationException ex) {
439 // ExecuteScalar: Connection property has not
441 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
442 Assert.IsNull (ex.InnerException, "#3");
443 Assert.IsNotNull (ex.Message, "#4");
444 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar:"), "#5");
451 public void ExecuteNonQuery ()
453 conn = new SqlConnection (connectionString);
456 SqlTransaction trans = conn.BeginTransaction ();
458 cmd = conn.CreateCommand ();
459 cmd.Transaction = trans;
464 cmd.CommandText = "Select id from numeric_family where id=1";
465 result = cmd.ExecuteNonQuery ();
466 Assert.AreEqual (-1, result, "#A1");
468 cmd.CommandText = "Insert into numeric_family (id,type_int) values (100,200)";
469 result = cmd.ExecuteNonQuery ();
470 Assert.AreEqual (1, result, "#A2 One row shud be inserted");
472 cmd.CommandText = "Update numeric_family set type_int=300 where id=100";
473 result = cmd.ExecuteNonQuery ();
474 Assert.AreEqual (1, result, "#A3 One row shud be updated");
476 // Test Batch Commands
477 cmd.CommandText = "Select id from numeric_family where id=1;";
478 cmd.CommandText += "update numeric_family set type_int=10 where id=1000";
479 cmd.CommandText += "update numeric_family set type_int=10 where id=100";
480 result = cmd.ExecuteNonQuery ();
481 Assert.AreEqual (1, result, "#A4 One row shud be updated");
483 cmd.CommandText = "Delete from numeric_family where id=100";
484 result = cmd.ExecuteNonQuery ();
485 Assert.AreEqual (1, result, "#A5 One row shud be deleted");
490 // Parameterized stored procedure calls
493 string string_value = "output value changed";
496 "create procedure #tmp_executescalar_outparams " +
497 " (@p1 int, @p2 int out, @p3 varchar(200) out) " +
499 "select 'test' as 'col1', @p1 as 'col2' " +
500 "set @p2 = @p2 * 2 " +
501 "set @p3 = N'" + string_value + "' " +
502 "select 'second rowset' as 'col1', 2 as 'col2' " +
505 cmd.CommandType = CommandType.Text;
506 cmd.ExecuteNonQuery ();
508 cmd.CommandText = "#tmp_executescalar_outparams";
509 cmd.CommandType = CommandType.StoredProcedure;
511 SqlParameter p1 = new SqlParameter ();
512 p1.ParameterName = "@p1";
513 p1.Direction = ParameterDirection.Input;
514 p1.DbType = DbType.Int32;
515 p1.Value = int_value;
516 cmd.Parameters.Add (p1);
518 SqlParameter p2 = new SqlParameter ("@p2", int_value);
519 p2.Direction = ParameterDirection.InputOutput;
520 cmd.Parameters.Add (p2);
522 SqlParameter p3 = new SqlParameter ();
523 p3.ParameterName = "@p3";
524 p3.Direction = ParameterDirection.Output;
525 p3.DbType = DbType.String;
527 cmd.Parameters.Add (p3);
529 cmd.ExecuteNonQuery ();
530 Assert.AreEqual (int_value * 2, p2.Value, "#B1");
531 Assert.AreEqual (string_value, p3.Value, "#B2");
535 public void ExecuteNonQuery_Connection_PendingTransaction ()
537 conn = new SqlConnection (connectionString);
540 using (SqlTransaction trans = conn.BeginTransaction ()) {
541 cmd = new SqlCommand ("select @@version", conn);
544 cmd.ExecuteNonQuery ();
546 } catch (InvalidOperationException ex) {
547 // ExecuteNonQuery requires the command
548 // to have a transaction object when the
549 // connection assigned to the command is
550 // in a pending local transaction. The
551 // Transaction property of the command
552 // has not been initialized
553 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
554 Assert.IsNull (ex.InnerException, "#3");
555 Assert.IsNotNull (ex.Message, "#4");
556 Assert.IsTrue (ex.Message.IndexOf ("ExecuteNonQuery") != -1, "#5:" + ex.Message);
562 public void ExecuteNonQuery_Query_Invalid ()
564 conn = new SqlConnection (connectionString);
566 cmd = new SqlCommand ("select id1 from numeric_family", conn);
569 cmd.ExecuteNonQuery ();
571 } catch (SqlException ex) {
572 // Invalid column name 'id1'
573 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#A2");
574 Assert.AreEqual ((byte) 16, ex.Class, "#A3");
575 Assert.IsNull (ex.InnerException, "#A4");
576 Assert.IsNotNull (ex.Message, "#A5");
577 Assert.IsTrue (ex.Message.IndexOf ("'id1'") != -1, "#A6:" + ex.Message);
578 Assert.AreEqual (207, ex.Number, "#A7");
579 if (ClientVersion == 7)
580 Assert.AreEqual ((byte) 3, ex.State, "#A8");
582 Assert.AreEqual ((byte) 1, ex.State, "#A8");
585 // ensure connection is not closed after error
589 cmd.CommandText = "INSERT INTO numeric_family (id, type_int) VALUES (6100, 200)";
590 result = cmd.ExecuteNonQuery ();
591 Assert.AreEqual (1, result, "#B1");
593 cmd.CommandText = "DELETE FROM numeric_family WHERE id = 6100";
594 result = cmd.ExecuteNonQuery ();
595 Assert.AreEqual (1, result, "#B1");
599 public void ExecuteNonQuery_Transaction_NotAssociated ()
601 SqlTransaction trans = null;
602 SqlConnection connA = null;
603 SqlConnection connB = null;
606 connA = new SqlConnection (connectionString);
609 connB = new SqlConnection (connectionString);
612 trans = connA.BeginTransaction ();
614 cmd = new SqlCommand ("select @@version", connB, trans);
617 cmd.ExecuteNonQuery ();
619 } catch (InvalidOperationException ex) {
620 // The transaction object is not associated
621 // with the connection object
622 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
623 Assert.IsNull (ex.InnerException, "#A3");
624 Assert.IsNotNull (ex.Message, "#A4");
629 cmd = new SqlCommand ("select @@version", connB);
630 cmd.Transaction = trans;
633 cmd.ExecuteNonQuery ();
635 } catch (InvalidOperationException ex) {
636 // The transaction object is not associated
637 // with the connection object
638 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
639 Assert.IsNull (ex.InnerException, "#B3");
640 Assert.IsNotNull (ex.Message, "#B4");
655 public void ExecuteNonQuery_Transaction_Only ()
657 conn = new SqlConnection (connectionString);
660 SqlTransaction trans = conn.BeginTransaction ();
662 cmd = new SqlCommand ("select @@version");
663 cmd.Transaction = trans;
666 cmd.ExecuteNonQuery ();
668 } catch (InvalidOperationException ex) {
669 // ExecuteNonQuery: Connection property has not
671 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
672 Assert.IsNull (ex.InnerException, "#3");
673 Assert.IsNotNull (ex.Message, "#4");
674 Assert.IsTrue (ex.Message.StartsWith ("ExecuteNonQuery:"), "#5");
680 [Test] // bug #412569
681 public void ExecuteReader ()
683 // Test for command behaviors
684 DataTable schemaTable = null;
685 SqlDataReader reader = null;
687 conn = new SqlConnection (connectionString);
689 cmd = new SqlCommand ("", conn);
690 cmd.CommandText = "Select id from numeric_family where id <=4 order by id asc;";
691 cmd.CommandText += "Select type_bit from numeric_family where id <=4 order by id asc";
693 // Test for default command behavior
694 reader = cmd.ExecuteReader ();
698 while (reader.Read ())
700 Assert.AreEqual (4, rows, "#1 Multiple rows shud be returned");
703 } while (reader.NextResult ());
704 Assert.AreEqual (2, results, "#2 Multiple result sets shud be returned");
707 // Test if closing reader, closes the connection
708 reader = cmd.ExecuteReader (CommandBehavior.CloseConnection);
710 Assert.AreEqual (ConnectionState.Closed, conn.State,
711 "#3 Command Behavior is not followed");
714 // Test if row info and primary Key info is returned
715 reader = cmd.ExecuteReader (CommandBehavior.KeyInfo);
716 schemaTable = reader.GetSchemaTable ();
717 Assert.IsTrue (reader.HasRows, "#4 Data Rows shud also be returned");
718 Assert.IsTrue ((bool) schemaTable.Rows [0] ["IsKey"],
719 "#5 Primary Key info shud be returned");
722 // Test only column information is returned
723 reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly);
724 schemaTable = reader.GetSchemaTable ();
725 Assert.IsFalse (reader.HasRows, "#6 row data shud not be returned");
726 Assert.AreEqual (DBNull.Value, schemaTable.Rows [0] ["IsKey"],
727 "#7 Primary Key info shud not be returned");
728 Assert.AreEqual ("id", schemaTable.Rows [0] ["ColumnName"],
729 "#8 Schema Data is Incorrect");
732 // Test only one result set (first) is returned
733 reader = cmd.ExecuteReader (CommandBehavior.SingleResult);
734 schemaTable = reader.GetSchemaTable ();
735 Assert.IsFalse (reader.NextResult (),
736 "#9 Only one result set shud be returned");
737 Assert.AreEqual ("id", schemaTable.Rows [0] ["ColumnName"],
738 "#10 The result set returned shud be the first result set");
741 // Test only one row is returned for all result sets
742 // msdotnet doesnt work correctly.. returns only one result set
743 reader = cmd.ExecuteReader (CommandBehavior.SingleRow);
747 while (reader.Read ())
749 Assert.AreEqual (1, rows, "#11 Only one row shud be returned");
752 } while (reader.NextResult ());
755 // https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=357085
756 Assert.AreEqual (1, results, "#12 Multiple result sets shud be returned");
761 public void ExecuteReader_Connection_PendingTransaction ()
763 conn = new SqlConnection (connectionString);
766 using (SqlTransaction trans = conn.BeginTransaction ()) {
767 cmd = new SqlCommand ("select @@version", conn);
770 cmd.ExecuteReader ();
772 } catch (InvalidOperationException ex) {
773 // ExecuteReader requires the command
774 // to have a transaction object when the
775 // connection assigned to the command is
776 // in a pending local transaction. The
777 // Transaction property of the command
778 // has not been initialized
779 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
780 Assert.IsNull (ex.InnerException, "#3");
781 Assert.IsNotNull (ex.Message, "#4");
782 Assert.IsTrue (ex.Message.IndexOf ("ExecuteReader") != -1, "#5:" + ex.Message);
788 public void ExecuteReader_Query_Invalid ()
790 conn = new SqlConnection (connectionString);
793 cmd = new SqlCommand ("InvalidQuery", conn);
795 cmd.ExecuteReader ();
797 } catch (SqlException ex) {
798 // Could not find stored procedure 'InvalidQuery'
799 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#A2");
800 Assert.AreEqual ((byte) 16, ex.Class, "#A3");
801 Assert.IsNull (ex.InnerException, "#A4");
802 Assert.IsNotNull (ex.Message, "#A5");
803 Assert.IsTrue (ex.Message.IndexOf ("'InvalidQuery'") != -1, "#A6:" + ex.Message);
804 Assert.AreEqual (2812, ex.Number, "#A7");
805 Assert.AreEqual ((byte) 62, ex.State, "#A8");
807 // connection is not closed
808 Assert.AreEqual (ConnectionState.Open, conn.State, "#A9");
812 cmd.ExecuteReader (CommandBehavior.CloseConnection);
814 } catch (SqlException ex) {
815 // Could not find stored procedure 'InvalidQuery'
816 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#B2");
817 Assert.AreEqual ((byte) 16, ex.Class, "#B3");
818 Assert.IsNull (ex.InnerException, "#B4");
819 Assert.IsNotNull (ex.Message, "#B5");
820 Assert.IsTrue (ex.Message.IndexOf ("'InvalidQuery'") != -1, "#B6:" + ex.Message);
821 Assert.AreEqual (2812, ex.Number, "#B7");
822 Assert.AreEqual ((byte) 62, ex.State, "#B8");
824 // connection is closed
825 Assert.AreEqual (ConnectionState.Closed, conn.State, "#B9");
830 public void ExecuteReader_Transaction_NotAssociated ()
832 SqlTransaction trans = null;
833 SqlConnection connA = null;
834 SqlConnection connB = null;
837 connA = new SqlConnection (connectionString);
840 connB = new SqlConnection (connectionString);
843 trans = connA.BeginTransaction ();
845 cmd = new SqlCommand ("select @@version", connB, trans);
848 cmd.ExecuteReader ();
850 } catch (InvalidOperationException ex) {
851 // The transaction object is not associated
852 // with the connection object
853 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
854 Assert.IsNull (ex.InnerException, "#A3");
855 Assert.IsNotNull (ex.Message, "#A4");
860 cmd = new SqlCommand ("select @@version", connB);
861 cmd.Transaction = trans;
864 cmd.ExecuteReader ();
866 } catch (InvalidOperationException ex) {
867 // The transaction object is not associated
868 // with the connection object
869 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
870 Assert.IsNull (ex.InnerException, "#B3");
871 Assert.IsNotNull (ex.Message, "#B4");
886 public void ExecuteReader_Transaction_Only ()
888 SqlTransaction trans = null;
890 conn = new SqlConnection (connectionString);
892 trans = conn.BeginTransaction ();
894 cmd = new SqlCommand ("select @@version");
895 cmd.Transaction = trans;
898 cmd.ExecuteReader ();
900 } catch (InvalidOperationException ex) {
901 // ExecuteReader: Connection property has not
903 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
904 Assert.IsNull (ex.InnerException, "#3");
905 Assert.IsNotNull (ex.Message, "#4");
906 Assert.IsTrue (ex.Message.StartsWith ("ExecuteReader:"), "#5");
914 public void PrepareTest_CheckValidStatement ()
916 cmd = new SqlCommand ();
917 conn = new SqlConnection (connectionString);
920 cmd.CommandText = "Select id from numeric_family where id=@ID";
921 cmd.Connection = conn;
923 // Test if Parameters are correctly populated
924 cmd.Parameters.Clear ();
925 cmd.Parameters.Add ("@ID", SqlDbType.TinyInt);
926 cmd.Parameters ["@ID"].Value = 2;
928 Assert.AreEqual (2, cmd.ExecuteScalar (), "#3 Prepared Stmt not working");
930 cmd.Parameters [0].Value = 3;
931 Assert.AreEqual (3, cmd.ExecuteScalar (), "#4 Prep Stmt not working");
936 public void Prepare ()
938 cmd = new SqlCommand ();
939 conn = new SqlConnection (connectionString);
942 cmd.CommandText = "Select id from numeric_family where id=@ID";
943 cmd.Connection = conn;
945 // Test InvalidOperation Exception is thrown if Parameter Type
946 // is not explicitly set
947 cmd.Parameters.AddWithValue ("@ID", 2);
951 } catch (InvalidOperationException ex) {
952 // SqlCommand.Prepare method requires all parameters
953 // to have an explicitly set type
954 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
955 Assert.IsNull (ex.InnerException, "#A3");
956 Assert.IsNotNull (ex.Message, "#A4");
959 // Test Exception is thrown for variable size data if precision/scale
961 cmd.CommandText = "select type_varchar from string_family where type_varchar=@p1";
962 cmd.Parameters.Clear ();
963 cmd.Parameters.Add ("@p1", SqlDbType.VarChar);
964 cmd.Parameters ["@p1"].Value = "afasasadadada";
968 } catch (InvalidOperationException ex) {
969 // SqlCommand.Prepare method requires all variable
970 // length parameters to have an explicitly set
972 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
973 Assert.IsNull (ex.InnerException, "#B3");
974 Assert.IsNotNull (ex.Message, "#B4");
977 // Test Exception is not thrown if DbType is set - #569543
979 cmd.CommandText = "select type_guid from string_family where type_guid=@p1";
980 cmd.Parameters.Clear ();
981 cmd.Parameters.Add ("@p1", SqlDbType.UniqueIdentifier);
982 cmd.Parameters ["@p1"].Value = new Guid ("1C47DD1D-891B-47E8-AAC8-F36608B31BC5");
984 } catch (Exception ex) {
985 Assert.Fail ("#B5 "+ex.Message);
988 // Test Exception is not thrown for Stored Procs
989 cmd.CommandType = CommandType.StoredProcedure;
990 cmd.CommandText = "ABFSDSFSF";
993 cmd.CommandType = CommandType.Text;
998 public void Prepare_Connection_PendingTransaction ()
1000 conn = new SqlConnection (connectionString);
1003 using (SqlTransaction trans = conn.BeginTransaction ()) {
1004 // Text, without parameters
1005 cmd = new SqlCommand ("select * from whatever where name=?", conn);
1008 // Text, with parameters
1009 cmd = new SqlCommand ("select * from whatever where name=?", conn);
1010 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1014 } catch (InvalidOperationException ex) {
1015 // Prepare requires the command to have a
1016 // transaction object when the connection
1017 // assigned to the command is in a pending
1018 // local transaction. The Transaction
1019 // property of the command has not been
1021 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
1022 Assert.IsNull (ex.InnerException, "#3");
1023 Assert.IsNotNull (ex.Message, "#4");
1024 Assert.IsTrue (ex.Message.IndexOf ("Prepare") != -1, "#5:" + ex.Message);
1027 // Text, parameters cleared
1028 cmd = new SqlCommand ("select * from whatever where name=?", conn);
1029 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1030 cmd.Parameters.Clear ();
1033 // StoredProcedure, without parameters
1034 cmd = new SqlCommand ("FindCustomer", conn);
1035 cmd.CommandType = CommandType.StoredProcedure;
1038 // StoredProcedure, with parameters
1039 cmd = new SqlCommand ("FindCustomer", conn);
1040 cmd.CommandType = CommandType.StoredProcedure;
1041 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1047 public void Prepare_Transaction_NotAssociated ()
1049 SqlTransaction trans = null;
1050 SqlConnection connA = null;
1051 SqlConnection connB = null;
1054 connA = new SqlConnection (connectionString);
1057 connB = new SqlConnection (connectionString);
1060 trans = connA.BeginTransaction ();
1062 // Text, without parameters
1063 cmd = new SqlCommand ("select @@version", connB, trans);
1064 cmd.Transaction = trans;
1067 // Text, with parameters
1068 cmd = new SqlCommand ("select @@version", connB, trans);
1069 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1073 } catch (InvalidOperationException ex) {
1074 // The transaction is either not associated
1075 // with the current connection or has been
1077 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
1078 Assert.IsNull (ex.InnerException, "#3");
1079 Assert.IsNotNull (ex.Message, "#4");
1082 // Text, parameters cleared
1083 cmd = new SqlCommand ("select @@version", connB, trans);
1084 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1085 cmd.Parameters.Clear ();
1088 // StoredProcedure, without parameters
1089 cmd = new SqlCommand ("FindCustomer", connB, trans);
1090 cmd.CommandType = CommandType.StoredProcedure;
1093 // StoredProcedure, with parameters
1094 cmd = new SqlCommand ("FindCustomer", connB, trans);
1095 cmd.CommandType = CommandType.StoredProcedure;
1096 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1109 public void Prepare_Transaction_Only ()
1111 SqlTransaction trans = null;
1113 conn = new SqlConnection (connectionString);
1115 trans = conn.BeginTransaction ();
1117 // Text, without parameters
1118 cmd = new SqlCommand ("select count(*) from whatever");
1119 cmd.Transaction = trans;
1122 Assert.Fail ("#A1");
1123 } catch (NullReferenceException) {
1126 // Text, with parameters
1127 cmd = new SqlCommand ("select count(*) from whatever");
1128 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1129 cmd.Transaction = trans;
1132 Assert.Fail ("#B1");
1133 } catch (NullReferenceException) {
1136 // Text, parameters cleared
1137 cmd = new SqlCommand ("select count(*) from whatever");
1138 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1139 cmd.Parameters.Clear ();
1140 cmd.Transaction = trans;
1143 Assert.Fail ("#C1");
1144 } catch (NullReferenceException) {
1147 // StoredProcedure, without parameters
1148 cmd = new SqlCommand ("FindCustomer");
1149 cmd.CommandType = CommandType.StoredProcedure;
1150 cmd.Transaction = trans;
1153 Assert.Fail ("#D1");
1154 } catch (NullReferenceException) {
1157 // StoredProcedure, with parameters
1158 cmd = new SqlCommand ("FindCustomer");
1159 cmd.CommandType = CommandType.StoredProcedure;
1160 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1161 cmd.Transaction = trans;
1164 Assert.Fail ("#E1");
1165 } catch (NullReferenceException) {
1169 [Test] // bug #412576
1170 public void Connection ()
1172 SqlConnection connA = null;
1173 SqlConnection connB = null;
1174 SqlTransaction trans = null;
1177 connA = new SqlConnection (connectionString);
1180 connB = new SqlConnection (connectionString);
1183 cmd = connA.CreateCommand ();
1184 cmd.Connection = connB;
1185 Assert.AreSame (connB, cmd.Connection, "#A1");
1186 Assert.IsNull (cmd.Transaction, "#A2");
1189 trans = connA.BeginTransaction ();
1190 cmd = new SqlCommand ("select @@version", connA, trans);
1191 cmd.Connection = connB;
1192 Assert.AreSame (connB, cmd.Connection, "#B1");
1193 Assert.AreSame (trans, cmd.Transaction, "#B2");
1196 trans = connA.BeginTransaction ();
1197 cmd = new SqlCommand ("select @@version", connA, trans);
1199 Assert.AreSame (connA, cmd.Connection, "#C1");
1200 Assert.IsNull (cmd.Transaction, "#C2");
1201 cmd.Connection = connB;
1202 Assert.AreSame (connB, cmd.Connection, "#C3");
1203 Assert.IsNull (cmd.Transaction, "#C4");
1205 trans = connA.BeginTransaction ();
1206 cmd = new SqlCommand ("select @@version", connA, trans);
1207 cmd.Connection = null;
1208 Assert.IsNull (cmd.Connection, "#D1");
1209 Assert.AreSame (trans, cmd.Transaction, "#D2");
1221 public void Connection_Reader_Open ()
1223 SqlConnection connA = null;
1224 SqlConnection connB = null;
1225 SqlTransaction trans = null;
1228 connA = new SqlConnection (connectionString);
1231 connB = new SqlConnection (connectionString);
1234 trans = connA.BeginTransaction ();
1235 SqlCommand cmdA = new SqlCommand ("select @@version", connA, trans);
1237 SqlCommand cmdB = new SqlCommand ("select @@version", connA, trans);
1238 using (SqlDataReader reader = cmdB.ExecuteReader ()) {
1239 cmdA.Connection = connA;
1240 Assert.AreSame (connA, cmdA.Connection, "#A1");
1241 Assert.AreSame (trans, cmdA.Transaction, "#A2");
1243 cmdA.Connection = connB;
1244 Assert.AreSame (connB, cmdA.Connection, "#B1");
1245 Assert.AreSame (trans, cmdA.Transaction, "#B2");
1247 cmdA.Connection = null;
1248 Assert.IsNull (cmdA.Connection, "#C1");
1249 Assert.AreSame (trans, cmdA.Transaction, "#C2");
1262 public void Transaction ()
1264 SqlConnection connA = null;
1265 SqlConnection connB = null;
1267 SqlTransaction transA = null;
1268 SqlTransaction transB = null;
1271 connA = new SqlConnection (connectionString);
1274 connB = new SqlConnection (connectionString);
1277 transA = connA.BeginTransaction ();
1278 transB = connB.BeginTransaction ();
1280 SqlCommand cmd = new SqlCommand ("select @@version", connA, transA);
1281 cmd.Transaction = transA;
1282 Assert.AreSame (connA, cmd.Connection, "#A1");
1283 Assert.AreSame (transA, cmd.Transaction, "#A2");
1284 cmd.Transaction = transB;
1285 Assert.AreSame (connA, cmd.Connection, "#B1");
1286 Assert.AreSame (transB, cmd.Transaction, "#B2");
1287 cmd.Transaction = null;
1288 Assert.AreSame (connA, cmd.Connection, "#C1");
1289 Assert.IsNull (cmd.Transaction, "#C2");
1302 [Test] // bug #412579
1303 public void Transaction_Reader_Open ()
1305 SqlConnection connA = null;
1306 SqlConnection connB = null;
1308 SqlTransaction transA = null;
1309 SqlTransaction transB = null;
1312 connA = new SqlConnection (connectionString);
1315 connB = new SqlConnection (connectionString);
1318 transA = connA.BeginTransaction ();
1319 transB = connB.BeginTransaction ();
1321 SqlCommand cmdA = new SqlCommand ("select * from employee", connA, transA);
1323 SqlCommand cmdB = new SqlCommand ("select * from employee", connA, transA);
1324 using (SqlDataReader reader = cmdB.ExecuteReader ()) {
1325 cmdA.Transaction = transA;
1326 Assert.AreSame (transA, cmdA.Transaction, "#A1");
1328 cmdA.Transaction = transB;
1329 Assert.AreSame (transB, cmdA.Transaction, "#B1");
1331 cmdA.Transaction = null;
1332 Assert.IsNull (cmdA.Transaction, "#C1");
1335 cmdA.Transaction = transA;
1336 Assert.AreSame (transA, cmdA.Transaction, "#D1");
1337 cmdA.Transaction = transB;
1338 Assert.AreSame (transB, cmdA.Transaction, "#D2");
1352 public void ExecuteNonQuery_StoredProcedure ()
1355 SqlCommand cmd = null;
1356 SqlDataReader dr = null;
1357 SqlParameter idParam;
1358 SqlParameter dojParam;
1360 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
1363 // parameters with leading '@'
1365 // create temp sp here, should normally be created in Setup of test
1366 // case, but cannot be done right now because of bug #68978
1367 DBHelper.ExecuteNonQuery (conn, CREATE_TMP_SP_TEMP_INSERT_PERSON);
1369 cmd = conn.CreateCommand ();
1370 cmd.CommandText = "#sp_temp_insert_employee";
1371 cmd.CommandType = CommandType.StoredProcedure;
1372 param = cmd.Parameters.Add ("@fname", SqlDbType.VarChar);
1373 param.Value = "testA";
1374 dojParam = cmd.Parameters.Add ("@doj", SqlDbType.DateTime);
1375 dojParam.Direction = ParameterDirection.Output;
1376 param = cmd.Parameters.Add ("@dob", SqlDbType.DateTime);
1377 param.Value = new DateTime (2004, 8, 20);
1378 idParam = cmd.Parameters.Add ("@id", SqlDbType.Int);
1379 idParam.Direction = ParameterDirection.ReturnValue;
1381 Assert.AreEqual (1, cmd.ExecuteNonQuery (), "#A1");
1384 cmd = conn.CreateCommand ();
1385 cmd.CommandText = "select fname, dob, doj from employee where id = @id";
1386 param = cmd.Parameters.Add ("@id", SqlDbType.Int);
1387 param.Value = idParam.Value;
1389 dr = cmd.ExecuteReader ();
1390 Assert.IsTrue (dr.Read (), "#A2");
1391 Assert.AreEqual (typeof (string), dr.GetFieldType (0), "#A3");
1392 Assert.AreEqual ("testA", dr.GetValue (0), "#A4");
1393 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (1), "#A5");
1394 Assert.AreEqual (new DateTime (2004, 8, 20), dr.GetValue (1), "#A6");
1395 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (2), "#A7");
1396 Assert.AreEqual (dojParam.Value, dr.GetValue (2), "#A8");
1397 Assert.IsFalse (dr.Read (), "#A9");
1405 DBHelper.ExecuteNonQuery (conn, DROP_TMP_SP_TEMP_INSERT_PERSON);
1406 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
1412 // parameters without leading '@'
1414 // create temp sp here, should normally be created in Setup of test
1415 // case, but cannot be done right now because of bug #68978
1416 DBHelper.ExecuteNonQuery (conn, CREATE_TMP_SP_TEMP_INSERT_PERSON);
1418 cmd = conn.CreateCommand ();
1419 cmd.CommandText = "#sp_temp_insert_employee";
1420 cmd.CommandType = CommandType.StoredProcedure;
1421 param = cmd.Parameters.Add ("fname", SqlDbType.VarChar);
1422 param.Value = "testB";
1423 dojParam = cmd.Parameters.Add ("doj", SqlDbType.DateTime);
1424 dojParam.Direction = ParameterDirection.Output;
1425 param = cmd.Parameters.Add ("dob", SqlDbType.DateTime);
1426 param.Value = new DateTime (2004, 8, 20);
1427 idParam = cmd.Parameters.Add ("id", SqlDbType.Int);
1428 idParam.Direction = ParameterDirection.ReturnValue;
1430 Assert.AreEqual (1, cmd.ExecuteNonQuery (), "#B1");
1433 cmd = conn.CreateCommand ();
1434 cmd.CommandText = "select fname, dob, doj from employee where id = @id";
1435 param = cmd.Parameters.Add ("id", SqlDbType.Int);
1436 param.Value = idParam.Value;
1438 dr = cmd.ExecuteReader ();
1439 Assert.IsTrue (dr.Read (), "#B2");
1440 Assert.AreEqual (typeof (string), dr.GetFieldType (0), "#B3");
1441 Assert.AreEqual ("testB", dr.GetValue (0), "#B4");
1442 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (1), "#B5");
1443 Assert.AreEqual (new DateTime (2004, 8, 20), dr.GetValue (1), "#B6");
1444 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (2), "#B7");
1445 Assert.AreEqual (dojParam.Value, dr.GetValue (2), "#B8");
1446 Assert.IsFalse (dr.Read (), "#B9");
1454 DBHelper.ExecuteNonQuery (conn, DROP_TMP_SP_TEMP_INSERT_PERSON);
1455 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
1460 [Test] // bug #319598
1461 public void LongQueryTest ()
1463 if (ClientVersion == 7)
1464 Assert.Ignore ("Hangs on SQL Server 7.0");
1466 SqlConnection conn = new SqlConnection (
1467 connectionString + ";Pooling=false");
1470 SqlCommand cmd = conn.CreateCommand ();
1471 String value = new String ('a', 10000);
1472 cmd.CommandText = String.Format ("Select '{0}'", value);
1473 cmd.ExecuteNonQuery ();
1477 [Test] // bug #319598
1478 public void LongStoredProcTest ()
1480 if (ClientVersion == 7)
1481 Assert.Ignore ("Hangs on SQL Server 7.0");
1483 SqlConnection conn = new SqlConnection (
1484 connectionString + ";Pooling=false");
1487 /*int size = conn.PacketSize;*/
1488 SqlCommand cmd = conn.CreateCommand ();
1489 // create a temp stored proc
1490 cmd.CommandText = "Create Procedure #sp_tmp_long_params ";
1491 cmd.CommandText += "@p1 nvarchar (4000), ";
1492 cmd.CommandText += "@p2 nvarchar (4000), ";
1493 cmd.CommandText += "@p3 nvarchar (4000), ";
1494 cmd.CommandText += "@p4 nvarchar (4000) out ";
1495 cmd.CommandText += "As ";
1496 cmd.CommandText += "Begin ";
1497 cmd.CommandText += "Set @p4 = N'Hello' ";
1498 cmd.CommandText += "Return 2 ";
1499 cmd.CommandText += "End";
1500 cmd.ExecuteNonQuery ();
1503 cmd.CommandType = CommandType.StoredProcedure;
1504 cmd.CommandText = "#sp_tmp_long_params";
1506 String value = new String ('a', 4000);
1507 SqlParameter p1 = new SqlParameter ("@p1",
1508 SqlDbType.NVarChar, 4000);
1511 SqlParameter p2 = new SqlParameter ("@p2",
1512 SqlDbType.NVarChar, 4000);
1515 SqlParameter p3 = new SqlParameter ("@p3",
1516 SqlDbType.NVarChar, 4000);
1519 SqlParameter p4 = new SqlParameter ("@p4",
1520 SqlDbType.NVarChar, 4000);
1521 p4.Direction = ParameterDirection.Output;
1523 // for now, name shud be @RETURN_VALUE
1524 // can be changed once RPC is implemented
1525 SqlParameter p5 = new SqlParameter ("@RETURN_VALUE", SqlDbType.Int);
1526 p5.Direction = ParameterDirection.ReturnValue;
1528 cmd.Parameters.Add (p1);
1529 cmd.Parameters.Add (p2);
1530 cmd.Parameters.Add (p3);
1531 cmd.Parameters.Add (p4);
1532 cmd.Parameters.Add (p5);
1534 cmd.ExecuteNonQuery ();
1535 Assert.AreEqual ("Hello", p4.Value, "#1");
1536 Assert.AreEqual (2, p5.Value, "#2");
1540 [Test] // bug #319694
1541 public void DateTimeParameterTest ()
1543 SqlConnection conn = new SqlConnection (connectionString);
1546 SqlCommand cmd = conn.CreateCommand ();
1547 cmd.CommandText = "select * from datetime_family where type_datetime=@p1";
1548 cmd.Parameters.Add ("@p1", SqlDbType.DateTime).Value = "10-10-2005";
1549 // shudnt cause and exception
1550 SqlDataReader rdr = cmd.ExecuteReader ();
1556 * Verifies whether an enum value is converted to a numeric value when
1557 * used as value for a numeric parameter (bug #66630)
1560 public void EnumParameterTest ()
1562 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
1564 ConnectionManager.Singleton.OpenConnection ();
1565 // create temp sp here, should normally be created in Setup of test
1566 // case, but cannot be done right now because of ug #68978
1567 DBHelper.ExecuteNonQuery (conn, "CREATE PROCEDURE #Bug66630 ("
1568 + "@Status smallint = 7"
1570 + "AS" + Environment.NewLine
1571 + "BEGIN" + Environment.NewLine
1572 + "SELECT CAST(5 AS int), @Status" + Environment.NewLine
1575 SqlCommand cmd = new SqlCommand ("#Bug66630", conn);
1576 cmd.CommandType = CommandType.StoredProcedure;
1577 cmd.Parameters.Add ("@Status", SqlDbType.Int).Value = Status.Error;
1579 using (SqlDataReader dr = cmd.ExecuteReader ()) {
1580 // one record should be returned
1581 Assert.IsTrue (dr.Read (), "EnumParameterTest#1");
1582 // we should get two field in the result
1583 Assert.AreEqual (2, dr.FieldCount, "EnumParameterTest#2");
1585 Assert.AreEqual ("int", dr.GetDataTypeName (0), "EnumParameterTest#3");
1586 Assert.AreEqual (5, dr.GetInt32 (0), "EnumParameterTest#4");
1588 Assert.AreEqual ("smallint", dr.GetDataTypeName (1), "EnumParameterTest#5");
1589 Assert.AreEqual ((short) Status.Error, dr.GetInt16 (1), "EnumParameterTest#6");
1590 // only one record should be returned
1591 Assert.IsFalse (dr.Read (), "EnumParameterTest#7");
1594 DBHelper.ExecuteNonQuery (conn, "if exists (select name from sysobjects " +
1595 " where name like '#temp_Bug66630' and type like 'P') " +
1596 " drop procedure #temp_Bug66630; ");
1597 ConnectionManager.Singleton.CloseConnection ();
1602 public void CloneTest ()
1604 conn = new SqlConnection (connectionString);
1607 SqlTransaction trans = conn.BeginTransaction ();
1609 cmd = new SqlCommand ();
1610 cmd.Connection = conn;
1611 cmd.Transaction = trans;
1613 SqlCommand clone = (((ICloneable) (cmd)).Clone ()) as SqlCommand;
1614 Assert.AreSame (conn, clone.Connection);
1615 Assert.AreSame (trans, clone.Transaction);
1619 public void StoredProc_NoParameterTest ()
1621 string query = "create procedure #tmp_sp_proc as begin";
1622 query += " select 'data' end";
1623 SqlConnection conn = new SqlConnection (connectionString);
1624 SqlCommand cmd = conn.CreateCommand ();
1625 cmd.CommandText = query;
1627 cmd.ExecuteNonQuery ();
1629 cmd.CommandType = CommandType.StoredProcedure;
1630 cmd.CommandText = "#tmp_sp_proc";
1631 using (SqlDataReader reader = cmd.ExecuteReader ()) {
1633 Assert.AreEqual ("data", reader.GetString (0), "#1");
1635 Assert.Fail ("#2 Select shud return data");
1641 public void StoredProc_ParameterTest ()
1643 string create_query = CREATE_TMP_SP_PARAM_TEST;
1645 SqlConnection conn = new SqlConnection (connectionString);
1648 SqlCommand cmd = conn.CreateCommand ();
1650 string error = string.Empty;
1651 while (label != -1) {
1655 // Test BigInt Param
1656 DBHelper.ExecuteNonQuery (conn,
1657 String.Format (create_query, "bigint"));
1658 rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1659 Int64.MaxValue, Int64.MaxValue,
1660 Int64.MaxValue, Int64.MaxValue);
1661 rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1662 Int64.MinValue, Int64.MinValue,
1663 Int64.MinValue, Int64.MinValue);
1664 rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1665 DBNull.Value, DBNull.Value,
1666 DBNull.Value, DBNull.Value);
1669 // Test Binary Param
1670 DBHelper.ExecuteNonQuery (conn,
1671 String.Format (create_query, "binary(5)"));
1672 rpc_helper_function (cmd, SqlDbType.Binary, 5,
1673 new byte [] { 1, 2, 3, 4, 5 },
1674 new byte [] { 1, 2, 3, 4, 5 },
1675 new byte [] { 1, 2, 3, 4, 5 },
1676 new byte [] { 1, 2, 3, 4, 5 });
1678 rpc_helper_function (cmd, SqlDbType.Binary, 5,
1679 DBNull.Value, DBNull.Value,
1682 rpc_helper_function (cmd, SqlDbType.Binary, 2,
1684 new byte [] { 0, 0, 0, 0, 0 },
1685 new byte [] { 0, 0 },
1686 new byte [] { 0, 0 });
1690 DBHelper.ExecuteNonQuery (conn,
1691 String.Format (create_query, "bit"));
1692 rpc_helper_function (cmd, SqlDbType.Bit, 0,
1693 true, true, true, true);
1694 rpc_helper_function (cmd, SqlDbType.Bit, 0,
1695 false, false, false, false);
1696 rpc_helper_function (cmd, SqlDbType.Bit, 0,
1697 DBNull.Value, DBNull.Value,
1698 DBNull.Value, DBNull.Value);
1702 DBHelper.ExecuteNonQuery (conn,
1703 String.Format (create_query, "char(10)"));
1704 rpc_helper_function (cmd, SqlDbType.Char, 10,
1705 "characters", "characters",
1706 "characters", "characters");
1708 rpc_helper_function (cmd, SqlDbType.Char, 3,
1709 "characters", "cha ",
1711 rpc_helper_function (cmd, SqlDbType.Char, 3,
1715 rpc_helper_function (cmd, SqlDbType.Char, 5,
1716 DBNull.Value, DBNull.Value,
1717 DBNull.Value, DBNull.Value);
1721 DBHelper.ExecuteNonQuery (conn,
1722 String.Format (create_query, "datetime"));
1723 rpc_helper_function (cmd, SqlDbType.DateTime, 0, "2079-06-06 23:59:00",
1724 new DateTime (2079, 6, 6, 23, 59, 0),
1725 new DateTime (2079, 6, 6, 23, 59, 0),
1726 new DateTime (2079, 6, 6, 23, 59, 0));
1727 rpc_helper_function (cmd, SqlDbType.DateTime, 0, "2009-04-12 10:39:45",
1728 new DateTime (2009, 4, 12, 10, 39, 45),
1729 new DateTime (2009, 4, 12, 10, 39, 45),
1730 new DateTime (2009, 4, 12, 10, 39, 45));
1731 rpc_helper_function (cmd, SqlDbType.DateTime, 0,
1732 DBNull.Value, DBNull.Value,
1733 DBNull.Value, DBNull.Value);
1736 // Test Decimal Param
1737 DBHelper.ExecuteNonQuery (conn,
1738 String.Format (create_query, "decimal(10,2)"));
1739 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1740 10.665m, 10.67m, 11m, 10.67m);
1741 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1743 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1744 -5.657m, -5.66m, -6m, -5.66m);
1745 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1746 DBNull.Value, DBNull.Value,
1747 DBNull.Value, DBNull.Value);
1750 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1751 AttributeTargets.Constructor,
1753 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1754 4.325f, 4.33m, 4m, 4.33m);
1755 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1756 10.0d, 10.00m, 10m, 10m);
1757 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1758 10.665d, 10.67m, 11m, 10.67m);
1759 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1760 -5.657d, -5.66m, -6m, -5.66m);
1761 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1766 DBHelper.ExecuteNonQuery (conn,
1767 String.Format (create_query, "float"));
1768 rpc_helper_function (cmd, SqlDbType.Float, 0,
1769 10.0, 10.0, 10.0, 10.0);
1770 rpc_helper_function (cmd, SqlDbType.Float, 0,
1771 10.54, 10.54, 10.54, 10.54);
1772 rpc_helper_function (cmd, SqlDbType.Float, 0,
1774 rpc_helper_function (cmd, SqlDbType.Float, 0,
1775 -5.34, -5.34, -5.34, -5.34);
1776 rpc_helper_function (cmd, SqlDbType.Float, 0,
1777 DBNull.Value, DBNull.Value,
1778 DBNull.Value, DBNull.Value);
1783 DBHelper.ExecuteNonQuery (conn,
1784 String.Format(create_query, "image"));
1785 rpc_helper_function (cmd, SqlDbType.Image, 0, );
1786 rpc_helper_function (cmd, SqlDbType.Image, 0, );
1787 rpc_helper_function (cmd, SqlDbType.Image, 0, );
1791 // Test Integer Param
1792 DBHelper.ExecuteNonQuery (conn,
1793 String.Format (create_query, "int"));
1794 rpc_helper_function (cmd, SqlDbType.Int, 0,
1796 rpc_helper_function (cmd, SqlDbType.Int, 0,
1798 rpc_helper_function (cmd, SqlDbType.Int, 0,
1800 rpc_helper_function (cmd, SqlDbType.Int, 0,
1801 int.MaxValue, int.MaxValue,
1802 int.MaxValue, int.MaxValue);
1803 rpc_helper_function (cmd, SqlDbType.Int, 0,
1804 int.MinValue, int.MinValue,
1805 int.MinValue, int.MinValue);
1806 rpc_helper_function (cmd, SqlDbType.Int, 0,
1807 DBNull.Value, DBNull.Value,
1808 DBNull.Value, DBNull.Value);
1812 DBHelper.ExecuteNonQuery (conn,
1813 String.Format (create_query, "money"));
1814 rpc_helper_function (cmd, SqlDbType.Money, 0,
1815 10m, 10m, 10m, 10m);
1816 rpc_helper_function (cmd, SqlDbType.Money, 0,
1817 10.54, 10.54m, 10.54m, 10.54m);
1818 rpc_helper_function (cmd, SqlDbType.Money, 0,
1820 rpc_helper_function (cmd, SqlDbType.Money, 0,
1821 -5.34, -5.34m, -5.34m, -5.34m);
1822 rpc_helper_function (cmd, SqlDbType.Money, 0,
1823 5.34, 5.34m, 5.34m, 5.34m);
1824 rpc_helper_function (cmd, SqlDbType.Money, 0,
1825 -10.1234m, -10.1234m, -10.1234m,
1827 rpc_helper_function (cmd, SqlDbType.Money, 0,
1828 10.1234m, 10.1234m, 10.1234m,
1830 rpc_helper_function (cmd, SqlDbType.Money, 0,
1831 -2000000000m, -2000000000m,
1832 -2000000000m, -2000000000m);
1833 rpc_helper_function (cmd, SqlDbType.Money, 0,
1834 2000000000m, 2000000000m,
1835 2000000000m, 2000000000m);
1836 rpc_helper_function (cmd, SqlDbType.Money, 0,
1837 -200000000.2345m, -200000000.2345m,
1838 -200000000.2345m, -200000000.2345m);
1839 rpc_helper_function (cmd, SqlDbType.Money, 0,
1840 200000000.2345m, 200000000.2345m,
1841 200000000.2345m, 200000000.2345m);
1842 rpc_helper_function (cmd, SqlDbType.Money, 0,
1843 DBNull.Value, DBNull.Value,
1844 DBNull.Value, DBNull.Value);
1847 rpc_helper_function (cmd, SqlDbType.Money, 0,
1848 -200000000.234561m, -200000000.2346m,
1849 -200000000.2346m, -200000000.2346m);
1850 rpc_helper_function (cmd, SqlDbType.Money, 0,
1851 -200000000.234551m, -200000000.2346m,
1852 -200000000.2346m, -200000000.2346m);
1853 rpc_helper_function (cmd, SqlDbType.Money, 0,
1854 -200000000.234541m, -200000000.2345m,
1855 -200000000.2345m, -200000000.2345m);
1856 rpc_helper_function (cmd, SqlDbType.Money, 0,
1857 200000000.234561m, 200000000.2346m,
1858 200000000.2346m, 200000000.2346m);
1859 rpc_helper_function (cmd, SqlDbType.Money, 0,
1860 200000000.234551m, 200000000.2346m,
1861 200000000.2346m, 200000000.2346m);
1862 rpc_helper_function (cmd, SqlDbType.Money, 0,
1863 200000000.234541m, 200000000.2345m,
1864 200000000.2345m, 200000000.2345m);
1865 rpc_helper_function (cmd, SqlDbType.Money, 0,
1866 -200000000.234461m, -200000000.2345m,
1867 -200000000.2345m, -200000000.2345m);
1868 rpc_helper_function (cmd, SqlDbType.Money, 0,
1869 -200000000.234451m, -200000000.2345m,
1870 -200000000.2345m, -200000000.2345m);
1871 rpc_helper_function (cmd, SqlDbType.Money, 0,
1872 -200000000.234441m, -200000000.2344m,
1873 -200000000.2344m, -200000000.2344m);
1874 rpc_helper_function (cmd, SqlDbType.Money, 0,
1875 200000000.234461m, 200000000.2345m,
1876 200000000.2345m, 200000000.2345m);
1877 rpc_helper_function (cmd, SqlDbType.Money, 0,
1878 200000000.234451m, 200000000.2345m,
1879 200000000.2345m, 200000000.2345m);
1880 rpc_helper_function (cmd, SqlDbType.Money, 0,
1881 200000000.234441m, 200000000.2344m,
1882 200000000.2344m, 200000000.2344m);
1883 // FIXME: we round toward even in SqlParameter.ConvertToFrameworkType
1885 rpc_helper_function (cmd, SqlDbType.Money, 0,
1886 -200000000.234550m, -200000000.2346m, -200000000.2346m);
1887 rpc_helper_function (cmd, SqlDbType.Money, 0,
1888 200000000.234550m, 200000000.2346m, 200000000.2346m);
1889 rpc_helper_function (cmd, SqlDbType.Money, 0,
1890 -200000000.234450m, -200000000.2345m, -200000000.2345m);
1891 rpc_helper_function (cmd, SqlDbType.Money, 0,
1892 200000000.234450m, 200000000.2345m, 200000000.2345m);
1897 DBHelper.ExecuteNonQuery (conn,
1898 String.Format (create_query, "nchar(10)"));
1899 rpc_helper_function (cmd, SqlDbType.NChar, 10,
1900 "characters", "characters",
1901 "characters", "characters");
1902 rpc_helper_function (cmd, SqlDbType.NChar, 3,
1903 "characters", "cha ",
1905 rpc_helper_function (cmd, SqlDbType.NChar, 3,
1909 rpc_helper_function (cmd, SqlDbType.NChar, 5,
1910 DBNull.Value, DBNull.Value,
1916 DBHelper.ExecuteNonQuery (conn,
1917 String.Format (create_query, "ntext"));
1919 rpc_helper_function (cmd, SqlDbType.NText, 0, "ntext");
1920 rpc_helper_function (cmd, SqlDbType.NText, 0, "");
1921 rpc_helper_function (cmd, SqlDbType.NText, 0, null);
1925 // Test NVarChar Param
1926 DBHelper.ExecuteNonQuery (conn,
1927 String.Format (create_query, "nvarchar(10)"));
1928 rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1929 "nvarchar", "nvarchar", "nvarchar",
1931 rpc_helper_function (cmd, SqlDbType.NVarChar, 3,
1932 "nvarchar", "nva", "nva", "nva");
1934 rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1935 string.Empty, string.Empty, string.Empty);
1936 rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1937 DBNull.Value, DBNull.Value, DBNull.Value);
1942 DBHelper.ExecuteNonQuery (conn,
1943 String.Format (create_query, "real"));
1944 rpc_helper_function (cmd, SqlDbType.Real, 0,
1945 10m, 10f, 10f, 10f);
1946 rpc_helper_function (cmd, SqlDbType.Real, 0,
1947 10d, 10f, 10f, 10f);
1948 rpc_helper_function (cmd, SqlDbType.Real, 0,
1950 rpc_helper_function (cmd, SqlDbType.Real, 0,
1951 3.54d, 3.54f, 3.54f, 3.54f);
1952 rpc_helper_function (cmd, SqlDbType.Real, 0,
1954 rpc_helper_function (cmd, SqlDbType.Real, 0,
1955 10.5f, 10.5f, 10.5f, 10.5f);
1956 rpc_helper_function (cmd, SqlDbType.Real, 0,
1957 3.5d, 3.5f, 3.5f, 3.5f);
1958 rpc_helper_function (cmd, SqlDbType.Real, 0,
1959 4.54m, 4.54f, 4.54f, 4.54f);
1960 rpc_helper_function (cmd, SqlDbType.Real, 0,
1961 -4.54m, -4.54f, -4.54f, -4.54f);
1962 rpc_helper_function (cmd, SqlDbType.Real, 0,
1963 DBNull.Value, DBNull.Value,
1964 DBNull.Value, DBNull.Value);
1967 // Test SmallDateTime Param
1968 DBHelper.ExecuteNonQuery (conn,
1969 String.Format (create_query, "smalldatetime"));
1970 rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0,
1971 "6/6/2079 11:59:00 PM",
1972 new DateTime (2079, 6, 6, 23, 59, 0),
1973 new DateTime (2079, 6, 6, 23, 59, 0),
1974 new DateTime (2079, 6, 6, 23, 59, 0));
1975 rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0,
1976 DBNull.Value, DBNull.Value,
1977 DBNull.Value, DBNull.Value);
1980 // Test SmallInt Param
1981 DBHelper.ExecuteNonQuery (conn,
1982 String.Format (create_query, "smallint"));
1983 rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1984 10, (short) 10, (short) 10, (short) 10);
1985 rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1986 -10, (short) -10, (short) -10,
1988 rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1989 short.MaxValue, short.MaxValue,
1990 short.MaxValue, short.MaxValue);
1991 rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1992 short.MinValue, short.MinValue,
1993 short.MinValue, short.MinValue);
1994 rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1995 DBNull.Value, DBNull.Value,
1996 DBNull.Value, DBNull.Value);
1999 // Test SmallMoney Param
2000 DBHelper.ExecuteNonQuery (conn,
2001 String.Format (create_query, "smallmoney"));
2002 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2003 10.0d, 10m, 10m, 10m);
2004 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2006 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2007 3.54d, 3.54m, 3.54m, 3.54m);
2008 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2010 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2011 10.5f, 10.5m, 10.5m, 10.5m);
2012 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2013 3.5d, 3.5m, 3.5m, 3.5m);
2014 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2015 4.54m, 4.54m, 4.54m, 4.54m);
2016 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2017 -4.54m, -4.54m, -4.54m, -4.54m);
2018 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2019 -214748.3648m, -214748.3648m,
2020 -214748.3648m, -214748.3648m);
2021 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2022 214748.3647m, 214748.3647m, 214748.3647m,
2024 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2025 DBNull.Value, DBNull.Value, DBNull.Value,
2029 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2030 -4.543361m, -4.5434m, -4.5434m, -4.5434m);
2031 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2032 -4.543351m, -4.5434m, -4.5434m, -4.5434m);
2033 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2034 -4.543341m, -4.5433m, -4.5433m, -4.5433m);
2035 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2036 4.543361m, 4.5434m, 4.5434m, 4.5434m);
2037 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2038 4.543351m, 4.5434m, 4.5434m, 4.5434m);
2039 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2040 4.543341m, 4.5433m, 4.5433m, 4.5433m);
2041 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2042 -4.543261m, -4.5433m, -4.5433m, -4.5433m);
2043 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2044 -4.543251m, -4.5433m, -4.5433m, -4.5433m);
2045 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2046 -4.543241m, -4.5432m, -4.5432m, -4.5432m);
2047 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2048 4.543261m, 4.5433m, 4.5433m, 4.5433m);
2049 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2050 4.543251m, 4.5433m, 4.5433m, 4.5433m);
2051 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2052 4.543241m, 4.5432m, 4.5432m, 4.5432m);
2053 // FIXME: we round toward even in SqlParameter.ConvertToFrameworkType
2055 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2056 -4.543350m, -4.5434m, -4.5434m);
2057 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2058 4.543350m, 4.5434m, 4.5434m);
2059 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2060 -4.543250m, -4.5433m, -4.5433m);
2061 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2062 4.543250m, 4.5433m, 4.5433m);
2067 DBHelper.ExecuteNonQuery (conn,
2068 String.Format (create_query, "text"));
2070 rpc_helper_function (cmd, SqlDbType.Text, 0, "text");
2071 rpc_helper_function (cmd, SqlDbType.Text, 0, "");
2072 rpc_helper_function (cmd, SqlDbType.Text, 0, null);
2076 // Test TimeStamp Param
2078 DBHelper.ExecuteNonQuery (conn,
2079 String.Format(create_query,"timestamp"));
2080 rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, "");
2081 rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, "");
2082 rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, null);
2086 // Test TinyInt Param
2087 DBHelper.ExecuteNonQuery (conn,
2088 String.Format (create_query, "tinyint"));
2089 rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2090 10.0d, (byte) 10, (byte) 10,
2092 rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2093 0, (byte) 0, (byte) 0, (byte) 0);
2094 rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2095 byte.MaxValue, byte.MaxValue,
2096 byte.MaxValue, byte.MaxValue);
2097 rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2098 byte.MinValue, byte.MinValue,
2099 byte.MinValue, byte.MinValue);
2102 // Test UniqueIdentifier Param
2104 DBHelper.ExecuteNonQuery (conn,
2105 String.Format(create_query,"uniqueidentifier"));
2106 rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, "0f159bf395b1d04f8c2ef5c02c3add96");
2107 rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, null);
2111 // Test VarBinary Param
2113 DBHelper.ExecuteNonQuery (conn,
2114 String.Format(create_query,"varbinary (10)"));
2115 rpc_helper_function (cmd, SqlDbType.VarBinary, 0,);
2116 rpc_helper_function (cmd, SqlDbType.VarBinary, 0,);
2117 rpc_helper_function (cmd, SqlDbType.VarBinary, 0, null);
2121 // Test Varchar Param
2122 DBHelper.ExecuteNonQuery (conn,
2123 String.Format (create_query, "varchar(10)"));
2124 rpc_helper_function (cmd, SqlDbType.VarChar, 7,
2125 "VarChar", "VarChar", "VarChar",
2127 rpc_helper_function (cmd, SqlDbType.VarChar, 5,
2128 "Var", "Var", "Var", "Var");
2130 rpc_helper_function (cmd, SqlDbType.VarChar, 3,
2131 "Varchar", "Var", "Var");
2132 rpc_helper_function (cmd, SqlDbType.VarChar, 10,
2133 string.Empty, string.Empty, string.Empty);
2134 rpc_helper_function (cmd, SqlDbType.VarChar, 10,
2135 DBNull.Value, DBNull.Value,
2140 // Test Variant Param
2142 DBHelper.ExecuteNonQuery (conn,
2143 String.Format(create_query,"variant"));
2144 rpc_helper_function (cmd, SqlDbType.Variant, 0, );
2145 rpc_helper_function (cmd, SqlDbType.Variant, 0, );
2146 rpc_helper_function (cmd, SqlDbType.Variant, 0, null);
2153 } catch (AssertionException ex) {
2154 error += String.Format (" Case {0} INCORRECT VALUE : {1}\n", label, ex.ToString ());
2155 } catch (Exception ex) {
2156 error += String.Format (" Case {0} NOT WORKING : {1}\n", label, ex.ToString ());
2161 DBHelper.ExecuteNonQuery (conn, string.Format (
2162 CultureInfo.InvariantCulture,
2163 DROP_STORED_PROCEDURE, "#tmp_sp_param_test"));
2166 if (error.Length != 0)
2167 Assert.Fail (error);
2170 private void rpc_helper_function (SqlCommand cmd, SqlDbType type, int size, object input, object expectedRead, object expectedOut, object expectedInOut)
2172 cmd.Parameters.Clear ();
2173 SqlParameter param1, param2, param3;
2175 param1 = new SqlParameter ("@param1", type, size);
2176 param2 = new SqlParameter ("@param2", type, size);
2177 param3 = new SqlParameter ("@param3", type, size);
2179 param1 = new SqlParameter ("@param1", type);
2180 param2 = new SqlParameter ("@param2", type);
2181 param3 = new SqlParameter ("@param3", type);
2184 SqlParameter retval = new SqlParameter ("retval", SqlDbType.Int);
2185 param1.Value = input;
2186 param1.Direction = ParameterDirection.Input;
2187 param2.Direction = ParameterDirection.Output;
2188 param3.Direction = ParameterDirection.InputOutput;
2189 param3.Value = input;
2190 retval.Direction = ParameterDirection.ReturnValue;
2191 cmd.Parameters.Add (param1);
2192 cmd.Parameters.Add (param2);
2193 cmd.Parameters.Add (param3);
2194 cmd.Parameters.Add (retval);
2195 cmd.CommandText = "#tmp_sp_param_test";
2196 cmd.CommandType = CommandType.StoredProcedure;
2197 using (SqlDataReader reader = cmd.ExecuteReader ()) {
2198 Assert.IsTrue (reader.Read (), "#1");
2199 AreEqual (expectedRead, reader.GetValue (0), "#2");
2200 Assert.IsFalse (reader.Read (), "#3");
2203 AreEqual (expectedOut, param2.Value, "#4");
2204 AreEqual (expectedInOut, param3.Value, "#5");
2205 Assert.AreEqual (5, retval.Value, "#6");
2209 public void OutputParamSizeTest1 ()
2211 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2212 ConnectionManager.Singleton.OpenConnection ();
2213 cmd = new SqlCommand ();
2214 cmd.Connection = conn;
2216 cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2217 cmd.CommandType = CommandType.Text;
2218 cmd.ExecuteNonQuery ();
2220 cmd.CommandText = "#testsize";
2221 cmd.CommandType = CommandType.StoredProcedure;
2223 SqlParameter p1 = new SqlParameter ();
2224 p1.ParameterName = "@p1";
2225 p1.Direction = ParameterDirection.InputOutput;
2226 p1.DbType = DbType.String;
2227 p1.IsNullable = false;
2228 cmd.Parameters.Add (p1);
2231 cmd.ExecuteNonQuery ();
2233 } catch (InvalidOperationException ex) {
2234 // String[0]: the Size property has an invalid
2236 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2237 Assert.IsNull (ex.InnerException, "#3");
2238 Assert.IsNotNull (ex.Message, "#4");
2243 public void OutputParamSizeTest2 ()
2245 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2246 ConnectionManager.Singleton.OpenConnection ();
2247 cmd = new SqlCommand ();
2248 cmd.Connection = conn;
2250 cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2251 cmd.CommandType = CommandType.Text;
2252 cmd.ExecuteNonQuery ();
2254 cmd.CommandText = "#testsize";
2255 cmd.CommandType = CommandType.StoredProcedure;
2257 SqlParameter p1 = new SqlParameter ();
2258 p1.ParameterName = "@p1";
2259 p1.Direction = ParameterDirection.Output;
2260 p1.DbType = DbType.String;
2261 p1.IsNullable = false;
2262 cmd.Parameters.Add (p1);
2265 cmd.ExecuteNonQuery ();
2267 } catch (InvalidOperationException ex) {
2268 // String[0]: the Size property has an invalid
2270 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2271 Assert.IsNull (ex.InnerException, "#3");
2272 Assert.IsNotNull (ex.Message, "#4");
2277 public void OutputParamSizeTest3 ()
2279 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2280 ConnectionManager.Singleton.OpenConnection ();
2281 cmd = new SqlCommand ();
2282 cmd.Connection = conn;
2284 cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2285 cmd.CommandType = CommandType.Text;
2286 cmd.ExecuteNonQuery ();
2288 cmd.CommandText = "#testsize";
2289 cmd.CommandType = CommandType.StoredProcedure;
2291 SqlParameter p1 = new SqlParameter ();
2292 p1.ParameterName = "@p1";
2293 p1.Direction = ParameterDirection.InputOutput;
2294 p1.DbType = DbType.String;
2295 p1.IsNullable = true;
2296 cmd.Parameters.Add (p1);
2299 cmd.ExecuteNonQuery ();
2301 } catch (InvalidOperationException ex) {
2302 // String[0]: the Size property has an invalid
2304 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2305 Assert.IsNull (ex.InnerException, "#3");
2306 Assert.IsNotNull (ex.Message, "#4");
2311 public void OutputParamSizeTest4 ()
2313 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2314 ConnectionManager.Singleton.OpenConnection ();
2315 cmd = new SqlCommand ();
2316 cmd.Connection = conn;
2318 cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2319 cmd.CommandType = CommandType.Text;
2320 cmd.ExecuteNonQuery ();
2322 cmd.CommandText = "#testsize";
2323 cmd.CommandType = CommandType.StoredProcedure;
2325 SqlParameter p1 = new SqlParameter ();
2326 p1.ParameterName = "@p1";
2327 p1.Direction = ParameterDirection.Output;
2328 p1.DbType = DbType.String;
2329 p1.IsNullable = true;
2330 cmd.Parameters.Add (p1);
2333 cmd.ExecuteNonQuery ();
2335 } catch (InvalidOperationException ex) {
2336 // String[0]: the Size property has an invalid
2338 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2339 Assert.IsNull (ex.InnerException, "#3");
2340 Assert.IsNotNull (ex.Message, "#4");
2344 [Test] // bug #470579
2345 public void OutputParamTest ()
2347 SqlParameter newId, id;
2349 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2350 ConnectionManager.Singleton.OpenConnection ();
2352 cmd = conn.CreateCommand ();
2353 cmd.CommandText = "set @NewId=@Id + 2";
2354 cmd.CommandType = CommandType.Text;
2355 newId = cmd.Parameters.Add ("@NewId", SqlDbType.Int);
2356 newId.Direction = ParameterDirection.Output;
2357 id = cmd.Parameters.Add ("@Id", SqlDbType.Int);
2359 cmd.ExecuteNonQuery ();
2361 Assert.AreEqual (5, newId.Value, "#A1");
2362 Assert.AreEqual (3, id.Value, "#A2");
2364 cmd = conn.CreateCommand ();
2365 cmd.CommandText = "set @NewId=@Id + 2";
2366 cmd.CommandType = CommandType.Text;
2367 newId = cmd.Parameters.Add ("NewId", SqlDbType.Int);
2368 newId.Direction = ParameterDirection.Output;
2369 id = cmd.Parameters.Add ("Id", SqlDbType.Int);
2371 cmd.ExecuteNonQuery ();
2373 Assert.AreEqual (8, newId.Value, "#B1");
2374 Assert.AreEqual (6, id.Value, "#B2");
2378 public void SmallMoney_Overflow_Max ()
2380 conn = new SqlConnection (connectionString);
2383 DBHelper.ExecuteNonQuery (conn, string.Format (
2384 CultureInfo.InvariantCulture, CREATE_TMP_SP_TYPE_TEST,
2386 //decimal overflow = 214748.36471m;
2387 decimal overflow = 214748.3648m;
2389 cmd = conn.CreateCommand ();
2390 cmd.CommandText = "#tmp_sp_type_test";
2391 cmd.CommandType = CommandType.StoredProcedure;
2393 SqlParameter param = cmd.Parameters.Add ("@param",
2394 SqlDbType.SmallMoney);
2395 param.Value = overflow;
2398 cmd.ExecuteScalar ();
2400 } catch (OverflowException ex) {
2401 // SqlDbType.SmallMoney overflow. Value '214748.36471'
2402 // is out of range. Must be between -214,748.3648 and 214,748.3647
2403 Assert.AreEqual (typeof (OverflowException), ex.GetType (), "#2");
2404 Assert.IsNull (ex.InnerException, "#3");
2405 Assert.IsNotNull (ex.Message, "#4");
2406 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2407 CultureInfo.InvariantCulture, "'{0}'",
2408 overflow)) != -1, "#5:" + ex.Message);
2409 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2410 CultureInfo.InvariantCulture, "{0:N4}",
2411 SMALLMONEY_MIN)) != -1, "#6:" + ex.Message);
2412 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2413 CultureInfo.InvariantCulture, "{0:N4}",
2414 SMALLMONEY_MAX)) != -1, "#7:" + ex.Message);
2416 DBHelper.ExecuteNonQuery (conn, string.Format (
2417 CultureInfo.InvariantCulture,
2418 DROP_STORED_PROCEDURE, "#tmp_sp_type_test"));
2423 public void SmallMoney_Overflow_Min ()
2425 conn = new SqlConnection (connectionString);
2428 DBHelper.ExecuteNonQuery (conn, string.Format (
2429 CultureInfo.InvariantCulture, CREATE_TMP_SP_TYPE_TEST,
2431 //decimal overflow = -214748.36481m;
2432 decimal overflow = -214748.3649m;
2434 cmd = conn.CreateCommand ();
2435 cmd.CommandText = "#tmp_sp_type_test";
2436 cmd.CommandType = CommandType.StoredProcedure;
2438 SqlParameter param = cmd.Parameters.Add ("@param",
2439 SqlDbType.SmallMoney);
2440 param.Value = overflow;
2443 cmd.ExecuteScalar ();
2445 } catch (OverflowException ex) {
2446 // SqlDbType.SmallMoney overflow. Value '-214748,36481'
2447 // is out of range. Must be between -214,748.3648 and 214,748.3647
2448 Assert.AreEqual (typeof (OverflowException), ex.GetType (), "#2");
2449 Assert.IsNull (ex.InnerException, "#3");
2450 Assert.IsNotNull (ex.Message, "#4");
2451 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2452 CultureInfo.InvariantCulture, "'{0}'",
2453 overflow)) != -1, "#5:" + ex.Message);
2454 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2455 CultureInfo.InvariantCulture, "{0:N4}",
2456 SMALLMONEY_MIN)) != -1, "#6:" + ex.Message);
2457 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2458 CultureInfo.InvariantCulture, "{0:N4}",
2459 SMALLMONEY_MAX)) != -1, "#7:" + ex.Message);
2461 DBHelper.ExecuteNonQuery (conn, string.Format (
2462 CultureInfo.InvariantCulture,
2463 DROP_STORED_PROCEDURE, "#tmp_sp_type_test"));
2468 public void NotificationTest ()
2470 cmd = new SqlCommand ();
2471 SqlNotificationRequest notification = new SqlNotificationRequest("MyNotification","MyService",15);
2472 Assert.AreEqual (null, cmd.Notification, "#1 The default value for this property should be null");
2473 cmd.Notification = notification;
2474 Assert.AreEqual ("MyService", cmd.Notification.Options, "#2 The value should be MyService as the constructor is initiated with this value");
2475 Assert.AreEqual (15, cmd.Notification.Timeout, "#2 The value should be 15 as the constructor is initiated with this value");
2479 public void NotificationAutoEnlistTest ()
2481 cmd = new SqlCommand ();
2482 Assert.AreEqual (true, cmd.NotificationAutoEnlist, "#1 Default value of the property should be true");
2483 cmd.NotificationAutoEnlist = false;
2484 Assert.AreEqual (false, cmd.NotificationAutoEnlist, "#2 The value of the property should be false after setting it to false");
2488 public void BeginExecuteXmlReaderTest ()
2490 cmd = new SqlCommand ();
2491 string connectionString1 = null;
2492 connectionString1 = ConnectionManager.Singleton.ConnectionString + "Asynchronous Processing=true";
2494 SqlConnection conn1 = new SqlConnection (connectionString1);
2496 cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA";
2497 cmd.Connection = conn1;
2499 IAsyncResult result = cmd.BeginExecuteXmlReader ();
2500 XmlReader reader = cmd.EndExecuteXmlReader (result);
2501 while (reader.Read ()) {
2502 if (reader.LocalName.ToString () == "employee")
2503 Assert.AreEqual ("kumar", reader["lname"], "#1 ");
2506 ConnectionManager.Singleton.CloseConnection ();
2511 public void BeginExecuteXmlReaderExceptionTest ()
2513 cmd = new SqlCommand ();
2515 SqlConnection conn = new SqlConnection (connectionString);
2517 cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA";
2518 cmd.Connection = conn;
2521 /*IAsyncResult result = */cmd.BeginExecuteXmlReader ();
2522 } catch (InvalidOperationException) {
2523 Assert.AreEqual (ConnectionManager.Singleton.ConnectionString, connectionString, "#1 Connection string has changed");
2526 Assert.Fail ("Expected Exception InvalidOperationException not thrown");
2528 ConnectionManager.Singleton.CloseConnection ();
2533 public void SqlCommandDisposeTest ()
2535 IDataReader reader = null;
2537 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2538 ConnectionManager.Singleton.OpenConnection ();
2540 IDbCommand command = conn.CreateCommand ();
2542 string sql = "SELECT * FROM employee";
2543 command.CommandText = sql;
2544 reader = command.ExecuteReader ();
2548 while (reader.Read ()) ;
2551 ConnectionManager.Singleton.CloseConnection ();
2555 private void bug326182_OutputParamMixupTestCommon (int paramOrder,
2563 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2564 ConnectionManager.Singleton.OpenConnection ();
2567 SqlParameter param0 = new SqlParameter ("@param0", SqlDbType.Int);
2568 param0.Direction = ParameterDirection.Output;
2569 SqlParameter param1 = new SqlParameter ("@param1", SqlDbType.Int);
2570 param1.Direction = ParameterDirection.Output;
2571 SqlParameter param2 = new SqlParameter ("@param2", SqlDbType.Int);
2572 param2.Direction = ParameterDirection.Output;
2573 SqlParameter param3 = new SqlParameter ("@param3", SqlDbType.Int);
2574 param3.Direction = ParameterDirection.Output;
2575 SqlParameter rval = new SqlParameter ("@RETURN_VALUE", SqlDbType.Int);
2576 rval.Direction = ParameterDirection.ReturnValue;
2578 cmd = conn.CreateCommand ();
2579 cmd.CommandText = "dbo.[sp_326182a]";
2580 cmd.CommandType = CommandType.StoredProcedure;
2582 switch (paramOrder) {
2583 case 1: cmd.Parameters.Add (param0);
2584 cmd.Parameters.Add (param1);
2585 cmd.Parameters.Add (rval);
2586 cmd.Parameters.Add (param2);
2587 cmd.Parameters.Add (param3);
2589 case 2: cmd.Parameters.Add (rval);
2590 cmd.Parameters.Add (param1);
2591 cmd.Parameters.Add (param0);
2592 cmd.Parameters.Add (param2);
2593 cmd.Parameters.Add (param3);
2595 default: cmd.Parameters.Add (param0);
2596 cmd.Parameters.Add (param1);
2597 cmd.Parameters.Add (param2);
2598 cmd.Parameters.Add (param3);
2599 cmd.Parameters.Add (rval);
2603 cmd.ExecuteNonQuery ();
2605 /* Copy the param values to variables, just in case if
2606 * tests fail, we don't want the created sp to exist */
2607 param3Val = (int) cmd.Parameters ["@param3"].Value;
2608 param1Val = (int) cmd.Parameters ["@param1"].Value;
2609 rvalVal = (int) cmd.Parameters ["@RETURN_VALUE"].Value;
2610 param2Val = (int) cmd.Parameters ["@param2"].Value;
2611 param0Val = (int) cmd.Parameters ["@param0"].Value;
2617 ConnectionManager.Singleton.CloseConnection ();
2623 public void bug326182_OutputParamMixupTest_Normal ()
2625 int param0Val, param1Val, param2Val, param3Val, rvalVal;
2627 //param0Val = param1Val = param2Val = param3Val = rvalVal = 0;
2629 bug326182_OutputParamMixupTestCommon (0, out param0Val, out param1Val,
2630 out param2Val, out param3Val, out rvalVal);
2631 Assert.AreEqual (103, param3Val);
2632 Assert.AreEqual (101, param1Val);
2633 Assert.AreEqual (2, rvalVal);
2634 Assert.AreEqual (102, param2Val);
2635 Assert.AreEqual (100, param0Val);
2639 public void bug326182_OutputParamMixupTest_RValInBetween ()
2641 int param0Val, param1Val, param2Val, param3Val, rvalVal;
2643 bug326182_OutputParamMixupTestCommon (1, out param0Val, out param1Val,
2644 out param2Val, out param3Val, out rvalVal);
2645 Assert.AreEqual (103, param3Val);
2646 Assert.AreEqual (101, param1Val);
2647 Assert.AreEqual (2, rvalVal);
2648 Assert.AreEqual (102, param2Val);
2649 Assert.AreEqual (100, param0Val);
2653 public void bug326182_OutputParamMixupTest_RValFirst ()
2655 int param0Val, param1Val, param2Val, param3Val, rvalVal;
2657 bug326182_OutputParamMixupTestCommon (2, out param0Val, out param1Val,
2658 out param2Val, out param3Val, out rvalVal);
2659 Assert.AreEqual (103, param3Val);
2660 Assert.AreEqual (101, param1Val);
2661 Assert.AreEqual (2, rvalVal);
2662 Assert.AreEqual (102, param2Val);
2663 Assert.AreEqual (100, param0Val);
2667 public void DeriveParameterTest_FullSchema ()
2669 string create_tbl = "CREATE TABLE decimalCheck (deccheck DECIMAL (19, 5) null)";
2670 string create_sp = "CREATE PROCEDURE sp_bug584833(@deccheck decimal(19,5) OUT)"
2671 + "AS " + Environment.NewLine
2672 + "BEGIN" + Environment.NewLine
2673 + "INSERT INTO decimalCheck values (@deccheck)" + Environment.NewLine
2674 + "SELECT @deccheck=deccheck from decimalCheck" + Environment.NewLine
2678 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2679 ConnectionManager.Singleton.OpenConnection ();
2681 cmd = conn.CreateCommand ();
2682 cmd.CommandText = create_tbl;
2683 cmd.ExecuteNonQuery ();
2685 cmd.CommandText = create_sp;
2686 cmd.ExecuteNonQuery ();
2688 cmd.CommandText = "monotest.dbo.sp_bug584833";
2689 cmd.CommandType = CommandType.StoredProcedure;
2691 SqlCommandBuilder.DeriveParameters (cmd);
2692 Assert.AreEqual (2, cmd.Parameters.Count, "#DPT - FullSchema - Parameter count mismatch");
2693 Assert.AreEqual ("@deccheck", cmd.Parameters[1].ParameterName, "#DPT - FullSchema - Parameter name mismatch");
2694 Assert.AreEqual (SqlDbType.Decimal, cmd.Parameters[1].SqlDbType, "#DPT - FullSchema - Parameter type mismatch");
2696 cmd.Parameters.Clear ();
2697 cmd.CommandText = "drop procedure sp_bug584833";
2698 cmd.CommandType = CommandType.Text;
2699 cmd.ExecuteNonQuery ();
2700 cmd.CommandText = "drop table decimalCheck";
2701 cmd.ExecuteNonQuery ();
2704 ConnectionManager.Singleton.CloseConnection ();
2711 public void DeriveParameterTest_SPName ()
2713 string create_tbl = "CREATE TABLE decimalCheck (deccheck DECIMAL (19, 5) null)";
2714 string create_sp = "CREATE PROCEDURE sp_bug584833(@deccheck decimal(19,5) OUT)"
2715 + "AS " + Environment.NewLine
2716 + "BEGIN" + Environment.NewLine
2717 + "INSERT INTO decimalCheck values (@deccheck)" + Environment.NewLine
2718 + "SELECT @deccheck=deccheck from decimalCheck" + Environment.NewLine
2722 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2723 ConnectionManager.Singleton.OpenConnection ();
2725 cmd = conn.CreateCommand ();
2726 cmd.CommandText = create_tbl;
2727 cmd.ExecuteNonQuery ();
2729 cmd.CommandText = create_sp;
2730 cmd.ExecuteNonQuery ();
2732 cmd.CommandText = "sp_bug584833";
2733 cmd.CommandType = CommandType.StoredProcedure;
2735 SqlCommandBuilder.DeriveParameters (cmd);
2736 Assert.AreEqual (2, cmd.Parameters.Count, "#DPT - SPName - Parameter count mismatch");
2737 Assert.AreEqual ("@deccheck", cmd.Parameters[1].ParameterName, "#DPT - SPName - Parameter name mismatch");
2738 Assert.AreEqual (SqlDbType.Decimal, cmd.Parameters[1].SqlDbType, "#DPT - SPName - Parameter type mismatch");
2740 cmd.Parameters.Clear ();
2741 cmd.CommandType = CommandType.Text;
2742 cmd.CommandText = "drop procedure sp_bug584833";
2743 cmd.ExecuteNonQuery ();
2744 cmd.CommandText = "drop table decimalCheck";
2745 cmd.ExecuteNonQuery ();
2748 ConnectionManager.Singleton.CloseConnection ();
2754 public void DeriveParameterTest_UserSchema ()
2756 string create_tbl = "CREATE TABLE decimalCheck (deccheck DECIMAL (19, 5) null)";
2757 string create_sp = "CREATE PROCEDURE sp_bug584833(@deccheck decimal(19,5) OUT)"
2758 + "AS " + Environment.NewLine
2759 + "BEGIN" + Environment.NewLine
2760 + "INSERT INTO decimalCheck values (@deccheck)" + Environment.NewLine
2761 + "SELECT @deccheck=deccheck from decimalCheck" + Environment.NewLine
2765 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2766 ConnectionManager.Singleton.OpenConnection ();
2768 cmd = conn.CreateCommand ();
2769 cmd.CommandText = create_tbl;
2770 cmd.ExecuteNonQuery ();
2772 cmd.CommandText = create_sp;
2773 cmd.ExecuteNonQuery ();
2775 cmd.CommandText = "dbo.sp_bug584833";
2776 cmd.CommandType = CommandType.StoredProcedure;
2778 SqlCommandBuilder.DeriveParameters (cmd);
2779 Assert.AreEqual (2, cmd.Parameters.Count, "#DPT - user schema - Parameter count mismatch");
2780 Assert.AreEqual ("@deccheck", cmd.Parameters[1].ParameterName, "#DPT - user schema - Parameter name mismatch");
2781 Assert.AreEqual (SqlDbType.Decimal, cmd.Parameters[1].SqlDbType, "#DPT - user schema - Parameter type mismatch");
2783 cmd.Parameters.Clear ();
2784 cmd.CommandType = CommandType.Text;
2785 cmd.CommandText = "drop procedure dbo.sp_bug584833";
2786 cmd.ExecuteNonQuery ();
2787 cmd.CommandText = "drop table decimalCheck";
2788 cmd.ExecuteNonQuery ();
2791 ConnectionManager.Singleton.CloseConnection ();
2796 [Test] // bug#561667
2797 public void CmdDispose_DataReaderReset ()
2800 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
2801 ConnectionManager.Singleton.OpenConnection ();
2802 string query1 = "SELECT fname FROM employee where lname='kumar'";
2803 string query2 = "SELECT type_int FROM numeric_family where type_bit = 1";
2806 t = GetColumns(conn, query1);
2807 Assert.AreEqual ("suresh", t.Rows[0][0], "CmdDD#1: Query1 result mismatch");
2808 t = GetColumns(conn, query2);
2809 Assert.AreEqual (int.MaxValue, t.Rows[0][0], "CmdDD#2: Query2 result mismatch");
2811 ConnectionManager.Singleton.CloseConnection ();
2816 private DataTable GetColumns(DbConnection connection, string query)
2818 DataTable t = new DataTable("Columns");
2819 using (DbCommand c = connection.CreateCommand())
2821 c.CommandText = query;
2822 t.Load(c.ExecuteReader());
2827 // used as workaround for bugs in NUnit 2.2.0
2828 static void AreEqual (object x, object y, string msg)
2830 if (x == null && y == null)
2832 if ((x == null || y == null))
2833 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2834 "Expected: {0}, but was: {1}. {2}",
2835 x == null ? "<null>" : x, y == null ? "<null>" : y, msg));
2837 bool isArrayX = x.GetType ().IsArray;
2838 bool isArrayY = y.GetType ().IsArray;
2840 if (isArrayX && isArrayY) {
2841 Array arrayX = (Array) x;
2842 Array arrayY = (Array) y;
2844 if (arrayX.Length != arrayY.Length)
2845 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2846 "Length of arrays differs. Expected: {0}, but was: {1}. {2}",
2847 arrayX.Length, arrayY.Length, msg));
2849 for (int i = 0; i < arrayX.Length; i++) {
2850 object itemX = arrayX.GetValue (i);
2851 object itemY = arrayY.GetValue (i);
2852 if (!itemX.Equals (itemY))
2853 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2854 "Arrays differ at position {0}. Expected: {1}, but was: {2}. {3}",
2855 i, itemX, itemY, msg));
2857 } else if (!x.Equals (y)) {
2858 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2859 "Expected: {0} ({1}), but was: {2} ({3}). {4}",
2860 x, x.GetType (), y, y.GetType (), msg));
2866 return (engine.ClientVersion);
2876 private readonly string CREATE_TMP_SP_PARAM_TEST =
2877 "CREATE PROCEDURE #tmp_sp_param_test (" + Environment.NewLine +
2878 " @param1 {0}," + Environment.NewLine +
2879 " @param2 {0} output," + Environment.NewLine +
2880 " @param3 {0} output)" + Environment.NewLine +
2881 "AS" + Environment.NewLine +
2882 "BEGIN" + Environment.NewLine +
2883 " SELECT @param1" + Environment.NewLine +
2884 " SET @param2=@param1" + Environment.NewLine +
2885 " RETURN 5" + Environment.NewLine +
2888 private readonly string CREATE_TMP_SP_TEMP_INSERT_PERSON = ("create procedure #sp_temp_insert_employee ( " + Environment.NewLine +
2889 "@fname varchar (20), " + Environment.NewLine +
2890 "@dob datetime, " + Environment.NewLine +
2891 "@doj datetime output " + Environment.NewLine +
2892 ") " + Environment.NewLine +
2893 "as " + Environment.NewLine +
2894 "begin" + Environment.NewLine +
2895 "declare @id int;" + Environment.NewLine +
2896 "select @id = max (id) from employee;" + Environment.NewLine +
2897 "set @id = @id + 6000 + 1;" + Environment.NewLine +
2898 "set @doj = getdate();" + Environment.NewLine +
2899 "insert into employee (id, fname, dob, doj) values (@id, @fname, @dob, @doj);" + Environment.NewLine +
2900 "return @id;" + Environment.NewLine +
2903 private readonly string DROP_TMP_SP_TEMP_INSERT_PERSON = ("if exists (select name from sysobjects where " + Environment.NewLine +
2904 "name = '#sp_temp_insert_employee' and type = 'P') " + Environment.NewLine +
2905 "drop procedure #sp_temp_insert_employee; ");
2907 private static readonly string CREATE_TMP_SP_TYPE_TEST =
2908 "CREATE PROCEDURE #tmp_sp_type_test " +
2911 ") AS SELECT @param";
2912 private static readonly string DROP_STORED_PROCEDURE =
2913 "DROP PROCEDURE {0}";