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.Connected.SqlClient
45 [Category ("sqlserver")]
46 public class SqlCommandTest
50 string connectionString = ConnectionManager.Instance.Sql.ConnectionString;
53 static readonly decimal SMALLMONEY_MAX = 214748.3647m;
54 static readonly decimal SMALLMONEY_MIN = -214748.3648m;
59 engine = ConnectionManager.Instance.Sql.EngineConfig;
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 = ConnectionManager.Instance.Sql.Connection;
149 cmd = conn.CreateCommand ();
150 cmd.CommandText = "SELECT 'a'";
151 cmd.ExecuteNonQuery ();
155 Assert.AreSame (conn, cmd.Connection, "#1");
157 Assert.AreSame (conn, cmd.Connection, "#2");
161 [Category("NotWorking")]
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 = ConnectionManager.Instance.Sql.Connection;
274 cmd = conn.CreateCommand ();
277 cmd.ExecuteScalar ();
279 } catch (InvalidOperationException ex) {
280 // ExecuteScalar: CommandText property
281 // has not been initialized
282 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
283 Assert.IsNull (ex.InnerException, "#A3");
284 Assert.IsNotNull (ex.Message, "#A4");
285 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar"), "#A5:" + ex.Message);
288 cmd.CommandText = string.Empty;
291 cmd.ExecuteScalar ();
293 } catch (InvalidOperationException ex) {
294 // ExecuteScalar: CommandText property
295 // has not been initialized
296 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
297 Assert.IsNull (ex.InnerException, "#B3");
298 Assert.IsNotNull (ex.Message, "#B4");
299 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar"), "#B5:" + ex.Message);
302 cmd.CommandText = null;
305 cmd.ExecuteScalar ();
307 } catch (InvalidOperationException ex) {
308 // ExecuteScalar: CommandText property
309 // has not been initialized
310 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#C2");
311 Assert.IsNull (ex.InnerException, "#C3");
312 Assert.IsNotNull (ex.Message, "#C4");
313 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar"), "#C5:" + ex.Message);
318 public void ExecuteScalar_Connection_PendingTransaction ()
320 conn = new SqlConnection (connectionString);
323 using (SqlTransaction trans = conn.BeginTransaction ()) {
324 cmd = new SqlCommand ("select @@version", conn);
327 cmd.ExecuteScalar ();
329 } catch (InvalidOperationException ex) {
330 // ExecuteScalar requires the command
331 // to have a transaction object when the
332 // connection assigned to the command is
333 // in a pending local transaction. The
334 // Transaction property of the command
335 // has not been initialized
336 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
337 Assert.IsNull (ex.InnerException, "#3");
338 Assert.IsNotNull (ex.Message, "#4");
339 Assert.IsTrue (ex.Message.IndexOf ("ExecuteScalar") != -1, "#5:" + ex.Message);
345 public void ExecuteScalar_Query_Invalid ()
347 conn = new SqlConnection (connectionString);
350 cmd = new SqlCommand ("InvalidQuery", conn);
352 cmd.ExecuteScalar ();
354 } catch (SqlException ex) {
355 // Could not find stored procedure 'InvalidQuery'
356 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
357 Assert.AreEqual ((byte) 16, ex.Class, "#3");
358 Assert.IsNull (ex.InnerException, "#4");
359 Assert.IsNotNull (ex.Message, "#5");
360 Assert.IsTrue (ex.Message.IndexOf ("'InvalidQuery'") != -1, "#6:" + ex.Message);
361 Assert.AreEqual (2812, ex.Number, "#7");
362 Assert.AreEqual ((byte) 62, ex.State, "#8");
367 public void ExecuteScalar_Transaction_NotAssociated ()
369 SqlTransaction trans = null;
370 SqlConnection connA = null;
371 SqlConnection connB = null;
374 connA = new SqlConnection (connectionString);
377 connB = new SqlConnection (connectionString);
380 trans = connA.BeginTransaction ();
382 cmd = new SqlCommand ("select @@version", connB, trans);
385 cmd.ExecuteScalar ();
387 } catch (InvalidOperationException ex) {
388 // The transaction object is not associated
389 // with the connection object
390 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
391 Assert.IsNull (ex.InnerException, "#A3");
392 Assert.IsNotNull (ex.Message, "#A4");
397 cmd = new SqlCommand ("select @@version", connB);
398 cmd.Transaction = trans;
401 cmd.ExecuteScalar ();
403 } catch (InvalidOperationException ex) {
404 // The transaction object is not associated
405 // with the connection object
406 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
407 Assert.IsNull (ex.InnerException, "#B3");
408 Assert.IsNotNull (ex.Message, "#B4");
423 public void ExecuteScalar_Transaction_Only ()
425 SqlTransaction trans = null;
427 conn = new SqlConnection (connectionString);
429 trans = conn.BeginTransaction ();
431 cmd = new SqlCommand ("select @@version");
432 cmd.Transaction = trans;
435 cmd.ExecuteScalar ();
437 } catch (InvalidOperationException ex) {
438 // ExecuteScalar: Connection property has not
440 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
441 Assert.IsNull (ex.InnerException, "#3");
442 Assert.IsNotNull (ex.Message, "#4");
443 Assert.IsTrue (ex.Message.StartsWith ("ExecuteScalar:"), "#5");
450 [Category("NotWorking")]
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 [Category("NotWorking")]
1110 public void Prepare_Transaction_Only ()
1112 SqlTransaction trans = null;
1114 conn = new SqlConnection (connectionString);
1116 trans = conn.BeginTransaction ();
1118 // Text, without parameters
1119 cmd = new SqlCommand ("select count(*) from whatever");
1120 cmd.Transaction = trans;
1123 // Text, with parameters
1124 cmd = new SqlCommand ("select count(*) from whatever");
1125 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1126 cmd.Transaction = trans;
1127 Assert.Throws<InvalidOperationException>(() => cmd.Prepare());
1129 // Text, parameters cleared
1130 cmd = new SqlCommand ("select count(*) from whatever");
1131 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1132 cmd.Parameters.Clear ();
1133 cmd.Transaction = trans;
1136 // StoredProcedure, without parameters
1137 cmd = new SqlCommand ("FindCustomer");
1138 cmd.CommandType = CommandType.StoredProcedure;
1139 cmd.Transaction = trans;
1142 // StoredProcedure, with parameters
1143 cmd = new SqlCommand ("FindCustomer");
1144 cmd.CommandType = CommandType.StoredProcedure;
1145 cmd.Parameters.Add ("@TestPar1", SqlDbType.Int);
1146 cmd.Transaction = trans;
1150 [Test] // bug #412576
1151 public void Connection ()
1153 SqlConnection connA = null;
1154 SqlConnection connB = null;
1155 SqlTransaction trans = null;
1158 connA = new SqlConnection (connectionString);
1161 connB = new SqlConnection (connectionString);
1164 cmd = connA.CreateCommand ();
1165 cmd.Connection = connB;
1166 Assert.AreSame (connB, cmd.Connection, "#A1");
1167 Assert.IsNull (cmd.Transaction, "#A2");
1170 trans = connA.BeginTransaction ();
1171 cmd = new SqlCommand ("select @@version", connA, trans);
1172 cmd.Connection = connB;
1173 Assert.AreSame (connB, cmd.Connection, "#B1");
1174 Assert.AreSame (trans, cmd.Transaction, "#B2");
1177 trans = connA.BeginTransaction ();
1178 cmd = new SqlCommand ("select @@version", connA, trans);
1180 Assert.AreSame (connA, cmd.Connection, "#C1");
1181 Assert.IsNull (cmd.Transaction, "#C2");
1182 cmd.Connection = connB;
1183 Assert.AreSame (connB, cmd.Connection, "#C3");
1184 Assert.IsNull (cmd.Transaction, "#C4");
1186 trans = connA.BeginTransaction ();
1187 cmd = new SqlCommand ("select @@version", connA, trans);
1188 cmd.Connection = null;
1189 Assert.IsNull (cmd.Connection, "#D1");
1190 Assert.AreSame (trans, cmd.Transaction, "#D2");
1202 public void Connection_Reader_Open ()
1204 SqlConnection connA = null;
1205 SqlConnection connB = null;
1206 SqlTransaction trans = null;
1209 connA = new SqlConnection (connectionString);
1212 connB = new SqlConnection (connectionString);
1215 trans = connA.BeginTransaction ();
1216 SqlCommand cmdA = new SqlCommand ("select @@version", connA, trans);
1218 SqlCommand cmdB = new SqlCommand ("select @@version", connA, trans);
1219 using (SqlDataReader reader = cmdB.ExecuteReader ()) {
1220 cmdA.Connection = connA;
1221 Assert.AreSame (connA, cmdA.Connection, "#A1");
1222 Assert.AreSame (trans, cmdA.Transaction, "#A2");
1224 cmdA.Connection = connB;
1225 Assert.AreSame (connB, cmdA.Connection, "#B1");
1226 Assert.AreSame (trans, cmdA.Transaction, "#B2");
1228 cmdA.Connection = null;
1229 Assert.IsNull (cmdA.Connection, "#C1");
1230 Assert.AreSame (trans, cmdA.Transaction, "#C2");
1243 public void Transaction ()
1245 SqlConnection connA = null;
1246 SqlConnection connB = null;
1248 SqlTransaction transA = null;
1249 SqlTransaction transB = null;
1252 connA = new SqlConnection (connectionString);
1255 connB = new SqlConnection (connectionString);
1258 transA = connA.BeginTransaction ();
1259 transB = connB.BeginTransaction ();
1261 SqlCommand cmd = new SqlCommand ("select @@version", connA, transA);
1262 cmd.Transaction = transA;
1263 Assert.AreSame (connA, cmd.Connection, "#A1");
1264 Assert.AreSame (transA, cmd.Transaction, "#A2");
1265 cmd.Transaction = transB;
1266 Assert.AreSame (connA, cmd.Connection, "#B1");
1267 Assert.AreSame (transB, cmd.Transaction, "#B2");
1268 cmd.Transaction = null;
1269 Assert.AreSame (connA, cmd.Connection, "#C1");
1270 Assert.IsNull (cmd.Transaction, "#C2");
1283 [Test] // bug #412579
1284 public void Transaction_Reader_Open ()
1286 SqlConnection connA = null;
1287 SqlConnection connB = null;
1289 SqlTransaction transA = null;
1290 SqlTransaction transB = null;
1293 connA = new SqlConnection (connectionString);
1296 connB = new SqlConnection (connectionString);
1299 transA = connA.BeginTransaction ();
1300 transB = connB.BeginTransaction ();
1302 SqlCommand cmdA = new SqlCommand ("select * from employee", connA, transA);
1304 SqlCommand cmdB = new SqlCommand ("select * from employee", connA, transA);
1305 using (SqlDataReader reader = cmdB.ExecuteReader ()) {
1306 cmdA.Transaction = transA;
1307 Assert.AreSame (transA, cmdA.Transaction, "#A1");
1309 cmdA.Transaction = transB;
1310 Assert.AreSame (transB, cmdA.Transaction, "#B1");
1312 cmdA.Transaction = null;
1313 Assert.IsNull (cmdA.Transaction, "#C1");
1316 cmdA.Transaction = transA;
1317 Assert.AreSame (transA, cmdA.Transaction, "#D1");
1318 cmdA.Transaction = transB;
1319 Assert.AreSame (transB, cmdA.Transaction, "#D2");
1333 public void ExecuteNonQuery_StoredProcedure ()
1336 SqlCommand cmd = null;
1337 SqlDataReader dr = null;
1338 SqlParameter idParam;
1339 SqlParameter dojParam;
1341 conn = ConnectionManager.Instance.Sql.Connection;
1343 // parameters with leading '@'
1345 // create temp sp here, should normally be created in Setup of test
1346 // case, but cannot be done right now because of bug #68978
1347 DBHelper.ExecuteNonQuery (conn, CREATE_TMP_SP_TEMP_INSERT_PERSON);
1349 cmd = conn.CreateCommand ();
1350 cmd.CommandText = "#sp_temp_insert_employee";
1351 cmd.CommandType = CommandType.StoredProcedure;
1352 param = cmd.Parameters.Add ("@fname", SqlDbType.VarChar);
1353 param.Value = "testA";
1354 dojParam = cmd.Parameters.Add ("@doj", SqlDbType.DateTime);
1355 dojParam.Direction = ParameterDirection.Output;
1356 param = cmd.Parameters.Add ("@dob", SqlDbType.DateTime);
1357 param.Value = new DateTime (2004, 8, 20);
1358 idParam = cmd.Parameters.Add ("@id", SqlDbType.Int);
1359 idParam.Direction = ParameterDirection.ReturnValue;
1361 Assert.AreEqual (1, cmd.ExecuteNonQuery (), "#A1");
1364 cmd = conn.CreateCommand ();
1365 cmd.CommandText = "select fname, dob, doj from employee where id = @id";
1366 param = cmd.Parameters.Add ("@id", SqlDbType.Int);
1367 param.Value = idParam.Value;
1369 dr = cmd.ExecuteReader ();
1370 Assert.IsTrue (dr.Read (), "#A2");
1371 Assert.AreEqual (typeof (string), dr.GetFieldType (0), "#A3");
1372 Assert.AreEqual ("testA", dr.GetValue (0), "#A4");
1373 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (1), "#A5");
1374 Assert.AreEqual (new DateTime (2004, 8, 20), dr.GetValue (1), "#A6");
1375 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (2), "#A7");
1376 Assert.AreEqual (dojParam.Value, dr.GetValue (2), "#A8");
1377 Assert.IsFalse (dr.Read (), "#A9");
1385 DBHelper.ExecuteNonQuery (conn, DROP_TMP_SP_TEMP_INSERT_PERSON);
1386 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
1392 // parameters without leading '@'
1394 // create temp sp here, should normally be created in Setup of test
1395 // case, but cannot be done right now because of bug #68978
1396 DBHelper.ExecuteNonQuery (conn, CREATE_TMP_SP_TEMP_INSERT_PERSON);
1398 cmd = conn.CreateCommand ();
1399 cmd.CommandText = "#sp_temp_insert_employee";
1400 cmd.CommandType = CommandType.StoredProcedure;
1401 param = cmd.Parameters.Add ("fname", SqlDbType.VarChar);
1402 param.Value = "testB";
1403 dojParam = cmd.Parameters.Add ("doj", SqlDbType.DateTime);
1404 dojParam.Direction = ParameterDirection.Output;
1405 param = cmd.Parameters.Add ("dob", SqlDbType.DateTime);
1406 param.Value = new DateTime (2004, 8, 20);
1407 idParam = cmd.Parameters.Add ("id", SqlDbType.Int);
1408 idParam.Direction = ParameterDirection.ReturnValue;
1410 Assert.AreEqual (1, cmd.ExecuteNonQuery (), "#B1");
1413 cmd = conn.CreateCommand ();
1414 cmd.CommandText = "select fname, dob, doj from employee where id = @id";
1415 param = cmd.Parameters.Add ("id", SqlDbType.Int);
1416 param.Value = idParam.Value;
1418 dr = cmd.ExecuteReader ();
1419 Assert.IsTrue (dr.Read (), "#B2");
1420 Assert.AreEqual (typeof (string), dr.GetFieldType (0), "#B3");
1421 Assert.AreEqual ("testB", dr.GetValue (0), "#B4");
1422 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (1), "#B5");
1423 Assert.AreEqual (new DateTime (2004, 8, 20), dr.GetValue (1), "#B6");
1424 Assert.AreEqual (typeof (DateTime), dr.GetFieldType (2), "#B7");
1425 Assert.AreEqual (dojParam.Value, dr.GetValue (2), "#B8");
1426 Assert.IsFalse (dr.Read (), "#B9");
1434 DBHelper.ExecuteNonQuery (conn, DROP_TMP_SP_TEMP_INSERT_PERSON);
1435 DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
1440 [Test] // bug #319598
1441 [Category("NotWorking")]
1442 public void LongQueryTest ()
1444 if (ClientVersion == 7)
1445 Assert.Ignore ("Hangs on SQL Server 7.0");
1447 SqlConnection conn = new SqlConnection (
1448 connectionString + ";Pooling=false");
1451 SqlCommand cmd = conn.CreateCommand ();
1452 String value = new String ('a', 10000);
1453 cmd.CommandText = String.Format ("Select '{0}'", value);
1454 cmd.ExecuteNonQuery ();
1458 [Test] // bug #319598
1459 [Category("NotWorking")]
1460 public void LongStoredProcTest ()
1462 if (ClientVersion == 7)
1463 Assert.Ignore ("Hangs on SQL Server 7.0");
1465 SqlConnection conn = new SqlConnection (
1466 connectionString + ";Pooling=false");
1469 /*int size = conn.PacketSize;*/
1470 SqlCommand cmd = conn.CreateCommand ();
1471 // create a temp stored proc
1472 cmd.CommandText = "Create Procedure #sp_tmp_long_params ";
1473 cmd.CommandText += "@p1 nvarchar (4000), ";
1474 cmd.CommandText += "@p2 nvarchar (4000), ";
1475 cmd.CommandText += "@p3 nvarchar (4000), ";
1476 cmd.CommandText += "@p4 nvarchar (4000) out ";
1477 cmd.CommandText += "As ";
1478 cmd.CommandText += "Begin ";
1479 cmd.CommandText += "Set @p4 = N'Hello' ";
1480 cmd.CommandText += "Return 2 ";
1481 cmd.CommandText += "End";
1482 cmd.ExecuteNonQuery ();
1485 cmd.CommandType = CommandType.StoredProcedure;
1486 cmd.CommandText = "#sp_tmp_long_params";
1488 String value = new String ('a', 4000);
1489 SqlParameter p1 = new SqlParameter ("@p1",
1490 SqlDbType.NVarChar, 4000);
1493 SqlParameter p2 = new SqlParameter ("@p2",
1494 SqlDbType.NVarChar, 4000);
1497 SqlParameter p3 = new SqlParameter ("@p3",
1498 SqlDbType.NVarChar, 4000);
1501 SqlParameter p4 = new SqlParameter ("@p4",
1502 SqlDbType.NVarChar, 4000);
1503 p4.Direction = ParameterDirection.Output;
1505 // for now, name shud be @RETURN_VALUE
1506 // can be changed once RPC is implemented
1507 SqlParameter p5 = new SqlParameter ("@RETURN_VALUE", SqlDbType.Int);
1508 p5.Direction = ParameterDirection.ReturnValue;
1510 cmd.Parameters.Add (p1);
1511 cmd.Parameters.Add (p2);
1512 cmd.Parameters.Add (p3);
1513 cmd.Parameters.Add (p4);
1514 cmd.Parameters.Add (p5);
1516 cmd.ExecuteNonQuery ();
1517 Assert.AreEqual ("Hello", p4.Value, "#1");
1518 Assert.AreEqual (2, p5.Value, "#2");
1522 [Test] // bug #319694
1523 public void DateTimeParameterTest ()
1525 SqlConnection conn = new SqlConnection (connectionString);
1528 SqlCommand cmd = conn.CreateCommand ();
1529 cmd.CommandText = "select * from datetime_family where type_datetime=@p1";
1530 cmd.Parameters.Add ("@p1", SqlDbType.DateTime).Value = "10-10-2005";
1531 // shudnt cause and exception
1532 SqlDataReader rdr = cmd.ExecuteReader ();
1538 * Verifies whether an enum value is converted to a numeric value when
1539 * used as value for a numeric parameter (bug #66630)
1542 public void EnumParameterTest ()
1544 conn = ConnectionManager.Instance.Sql.Connection;
1546 // create temp sp here, should normally be created in Setup of test
1547 // case, but cannot be done right now because of ug #68978
1548 DBHelper.ExecuteNonQuery (conn, "CREATE PROCEDURE #Bug66630 ("
1549 + "@Status smallint = 7"
1551 + "AS" + Environment.NewLine
1552 + "BEGIN" + Environment.NewLine
1553 + "SELECT CAST(5 AS int), @Status" + Environment.NewLine
1556 SqlCommand cmd = new SqlCommand ("#Bug66630", conn);
1557 cmd.CommandType = CommandType.StoredProcedure;
1558 cmd.Parameters.Add ("@Status", SqlDbType.Int).Value = Status.Error;
1560 using (SqlDataReader dr = cmd.ExecuteReader ()) {
1561 // one record should be returned
1562 Assert.IsTrue (dr.Read (), "EnumParameterTest#1");
1563 // we should get two field in the result
1564 Assert.AreEqual (2, dr.FieldCount, "EnumParameterTest#2");
1566 Assert.AreEqual ("int", dr.GetDataTypeName (0), "EnumParameterTest#3");
1567 Assert.AreEqual (5, dr.GetInt32 (0), "EnumParameterTest#4");
1569 Assert.AreEqual ("smallint", dr.GetDataTypeName (1), "EnumParameterTest#5");
1570 Assert.AreEqual ((short) Status.Error, dr.GetInt16 (1), "EnumParameterTest#6");
1571 // only one record should be returned
1572 Assert.IsFalse (dr.Read (), "EnumParameterTest#7");
1575 DBHelper.ExecuteNonQuery (conn, "if exists (select name from sysobjects " +
1576 " where name like '#temp_Bug66630' and type like 'P') " +
1577 " drop procedure #temp_Bug66630; ");
1578 ConnectionManager.Instance.Sql.CloseConnection ();
1583 public void CloneTest ()
1585 conn = new SqlConnection (connectionString);
1588 SqlTransaction trans = conn.BeginTransaction ();
1590 cmd = new SqlCommand ();
1591 cmd.Connection = conn;
1592 cmd.Transaction = trans;
1594 SqlCommand clone = (((ICloneable) (cmd)).Clone ()) as SqlCommand;
1595 Assert.AreSame (conn, clone.Connection);
1596 Assert.AreSame (trans, clone.Transaction);
1600 public void StoredProc_NoParameterTest ()
1602 string query = "create procedure #tmp_sp_proc as begin";
1603 query += " select 'data' end";
1604 SqlConnection conn = new SqlConnection (connectionString);
1605 SqlCommand cmd = conn.CreateCommand ();
1606 cmd.CommandText = query;
1608 cmd.ExecuteNonQuery ();
1610 cmd.CommandType = CommandType.StoredProcedure;
1611 cmd.CommandText = "#tmp_sp_proc";
1612 using (SqlDataReader reader = cmd.ExecuteReader ()) {
1614 Assert.AreEqual ("data", reader.GetString (0), "#1");
1616 Assert.Fail ("#2 Select shud return data");
1622 [Category("NotWorking")]
1623 public void StoredProc_ParameterTest ()
1625 string create_query = CREATE_TMP_SP_PARAM_TEST;
1627 SqlConnection conn = new SqlConnection (connectionString);
1630 SqlCommand cmd = conn.CreateCommand ();
1632 string error = string.Empty;
1633 while (label != -1) {
1637 // Test BigInt Param
1638 DBHelper.ExecuteNonQuery (conn,
1639 String.Format (create_query, "bigint"));
1640 rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1641 Int64.MaxValue, Int64.MaxValue,
1642 Int64.MaxValue, Int64.MaxValue);
1643 rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1644 Int64.MinValue, Int64.MinValue,
1645 Int64.MinValue, Int64.MinValue);
1646 rpc_helper_function (cmd, SqlDbType.BigInt, 0,
1647 DBNull.Value, DBNull.Value,
1648 DBNull.Value, DBNull.Value);
1651 // Test Binary Param
1652 DBHelper.ExecuteNonQuery (conn,
1653 String.Format (create_query, "binary(5)"));
1654 rpc_helper_function (cmd, SqlDbType.Binary, 5,
1655 new byte [] { 1, 2, 3, 4, 5 },
1656 new byte [] { 1, 2, 3, 4, 5 },
1657 new byte [] { 1, 2, 3, 4, 5 },
1658 new byte [] { 1, 2, 3, 4, 5 });
1660 rpc_helper_function (cmd, SqlDbType.Binary, 5,
1661 DBNull.Value, DBNull.Value,
1664 rpc_helper_function (cmd, SqlDbType.Binary, 2,
1666 new byte [] { 0, 0, 0, 0, 0 },
1667 new byte [] { 0, 0 },
1668 new byte [] { 0, 0 });
1672 DBHelper.ExecuteNonQuery (conn,
1673 String.Format (create_query, "bit"));
1674 rpc_helper_function (cmd, SqlDbType.Bit, 0,
1675 true, true, true, true);
1676 rpc_helper_function (cmd, SqlDbType.Bit, 0,
1677 false, false, false, false);
1678 rpc_helper_function (cmd, SqlDbType.Bit, 0,
1679 DBNull.Value, DBNull.Value,
1680 DBNull.Value, DBNull.Value);
1684 DBHelper.ExecuteNonQuery (conn,
1685 String.Format (create_query, "char(10)"));
1686 rpc_helper_function (cmd, SqlDbType.Char, 10,
1687 "characters", "characters",
1688 "characters", "characters");
1690 rpc_helper_function (cmd, SqlDbType.Char, 3,
1691 "characters", "cha ",
1693 rpc_helper_function (cmd, SqlDbType.Char, 3,
1697 rpc_helper_function (cmd, SqlDbType.Char, 5,
1698 DBNull.Value, DBNull.Value,
1699 DBNull.Value, DBNull.Value);
1703 DBHelper.ExecuteNonQuery (conn,
1704 String.Format (create_query, "datetime"));
1705 rpc_helper_function (cmd, SqlDbType.DateTime, 0, "2079-06-06 23:59:00",
1706 new DateTime (2079, 6, 6, 23, 59, 0),
1707 new DateTime (2079, 6, 6, 23, 59, 0),
1708 new DateTime (2079, 6, 6, 23, 59, 0));
1709 rpc_helper_function (cmd, SqlDbType.DateTime, 0, "2009-04-12 10:39:45",
1710 new DateTime (2009, 4, 12, 10, 39, 45),
1711 new DateTime (2009, 4, 12, 10, 39, 45),
1712 new DateTime (2009, 4, 12, 10, 39, 45));
1713 rpc_helper_function (cmd, SqlDbType.DateTime, 0,
1714 DBNull.Value, DBNull.Value,
1715 DBNull.Value, DBNull.Value);
1718 // Test Decimal Param
1719 DBHelper.ExecuteNonQuery (conn,
1720 String.Format (create_query, "decimal(10,2)"));
1721 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1722 10.665m, 10.67m, 11m, 10.67m);
1723 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1725 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1726 -5.657m, -5.66m, -6m, -5.66m);
1727 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1728 DBNull.Value, DBNull.Value,
1729 DBNull.Value, DBNull.Value);
1732 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1733 AttributeTargets.Constructor,
1735 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1736 4.325f, 4.33m, 4m, 4.33m);
1737 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1738 10.0d, 10.00m, 10m, 10m);
1739 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1740 10.665d, 10.67m, 11m, 10.67m);
1741 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1742 -5.657d, -5.66m, -6m, -5.66m);
1743 rpc_helper_function (cmd, SqlDbType.Decimal, 0,
1748 DBHelper.ExecuteNonQuery (conn,
1749 String.Format (create_query, "float"));
1750 rpc_helper_function (cmd, SqlDbType.Float, 0,
1751 10.0, 10.0, 10.0, 10.0);
1752 rpc_helper_function (cmd, SqlDbType.Float, 0,
1753 10.54, 10.54, 10.54, 10.54);
1754 rpc_helper_function (cmd, SqlDbType.Float, 0,
1756 rpc_helper_function (cmd, SqlDbType.Float, 0,
1757 -5.34, -5.34, -5.34, -5.34);
1758 rpc_helper_function (cmd, SqlDbType.Float, 0,
1759 DBNull.Value, DBNull.Value,
1760 DBNull.Value, DBNull.Value);
1765 DBHelper.ExecuteNonQuery (conn,
1766 String.Format(create_query, "image"));
1767 rpc_helper_function (cmd, SqlDbType.Image, 0, );
1768 rpc_helper_function (cmd, SqlDbType.Image, 0, );
1769 rpc_helper_function (cmd, SqlDbType.Image, 0, );
1773 // Test Integer Param
1774 DBHelper.ExecuteNonQuery (conn,
1775 String.Format (create_query, "int"));
1776 rpc_helper_function (cmd, SqlDbType.Int, 0,
1778 rpc_helper_function (cmd, SqlDbType.Int, 0,
1780 rpc_helper_function (cmd, SqlDbType.Int, 0,
1782 rpc_helper_function (cmd, SqlDbType.Int, 0,
1783 int.MaxValue, int.MaxValue,
1784 int.MaxValue, int.MaxValue);
1785 rpc_helper_function (cmd, SqlDbType.Int, 0,
1786 int.MinValue, int.MinValue,
1787 int.MinValue, int.MinValue);
1788 rpc_helper_function (cmd, SqlDbType.Int, 0,
1789 DBNull.Value, DBNull.Value,
1790 DBNull.Value, DBNull.Value);
1794 DBHelper.ExecuteNonQuery (conn,
1795 String.Format (create_query, "money"));
1796 rpc_helper_function (cmd, SqlDbType.Money, 0,
1797 10m, 10m, 10m, 10m);
1798 rpc_helper_function (cmd, SqlDbType.Money, 0,
1799 10.54, 10.54m, 10.54m, 10.54m);
1800 rpc_helper_function (cmd, SqlDbType.Money, 0,
1802 rpc_helper_function (cmd, SqlDbType.Money, 0,
1803 -5.34, -5.34m, -5.34m, -5.34m);
1804 rpc_helper_function (cmd, SqlDbType.Money, 0,
1805 5.34, 5.34m, 5.34m, 5.34m);
1806 rpc_helper_function (cmd, SqlDbType.Money, 0,
1807 -10.1234m, -10.1234m, -10.1234m,
1809 rpc_helper_function (cmd, SqlDbType.Money, 0,
1810 10.1234m, 10.1234m, 10.1234m,
1812 rpc_helper_function (cmd, SqlDbType.Money, 0,
1813 -2000000000m, -2000000000m,
1814 -2000000000m, -2000000000m);
1815 rpc_helper_function (cmd, SqlDbType.Money, 0,
1816 2000000000m, 2000000000m,
1817 2000000000m, 2000000000m);
1818 rpc_helper_function (cmd, SqlDbType.Money, 0,
1819 -200000000.2345m, -200000000.2345m,
1820 -200000000.2345m, -200000000.2345m);
1821 rpc_helper_function (cmd, SqlDbType.Money, 0,
1822 200000000.2345m, 200000000.2345m,
1823 200000000.2345m, 200000000.2345m);
1824 rpc_helper_function (cmd, SqlDbType.Money, 0,
1825 DBNull.Value, DBNull.Value,
1826 DBNull.Value, DBNull.Value);
1829 rpc_helper_function (cmd, SqlDbType.Money, 0,
1830 -200000000.234561m, -200000000.2346m,
1831 -200000000.2346m, -200000000.2346m);
1832 rpc_helper_function (cmd, SqlDbType.Money, 0,
1833 -200000000.234551m, -200000000.2346m,
1834 -200000000.2346m, -200000000.2346m);
1835 rpc_helper_function (cmd, SqlDbType.Money, 0,
1836 -200000000.234541m, -200000000.2345m,
1837 -200000000.2345m, -200000000.2345m);
1838 rpc_helper_function (cmd, SqlDbType.Money, 0,
1839 200000000.234561m, 200000000.2346m,
1840 200000000.2346m, 200000000.2346m);
1841 rpc_helper_function (cmd, SqlDbType.Money, 0,
1842 200000000.234551m, 200000000.2346m,
1843 200000000.2346m, 200000000.2346m);
1844 rpc_helper_function (cmd, SqlDbType.Money, 0,
1845 200000000.234541m, 200000000.2345m,
1846 200000000.2345m, 200000000.2345m);
1847 rpc_helper_function (cmd, SqlDbType.Money, 0,
1848 -200000000.234461m, -200000000.2345m,
1849 -200000000.2345m, -200000000.2345m);
1850 rpc_helper_function (cmd, SqlDbType.Money, 0,
1851 -200000000.234451m, -200000000.2345m,
1852 -200000000.2345m, -200000000.2345m);
1853 rpc_helper_function (cmd, SqlDbType.Money, 0,
1854 -200000000.234441m, -200000000.2344m,
1855 -200000000.2344m, -200000000.2344m);
1856 rpc_helper_function (cmd, SqlDbType.Money, 0,
1857 200000000.234461m, 200000000.2345m,
1858 200000000.2345m, 200000000.2345m);
1859 rpc_helper_function (cmd, SqlDbType.Money, 0,
1860 200000000.234451m, 200000000.2345m,
1861 200000000.2345m, 200000000.2345m);
1862 rpc_helper_function (cmd, SqlDbType.Money, 0,
1863 200000000.234441m, 200000000.2344m,
1864 200000000.2344m, 200000000.2344m);
1865 // FIXME: we round toward even in SqlParameter.ConvertToFrameworkType
1867 rpc_helper_function (cmd, SqlDbType.Money, 0,
1868 -200000000.234550m, -200000000.2346m, -200000000.2346m);
1869 rpc_helper_function (cmd, SqlDbType.Money, 0,
1870 200000000.234550m, 200000000.2346m, 200000000.2346m);
1871 rpc_helper_function (cmd, SqlDbType.Money, 0,
1872 -200000000.234450m, -200000000.2345m, -200000000.2345m);
1873 rpc_helper_function (cmd, SqlDbType.Money, 0,
1874 200000000.234450m, 200000000.2345m, 200000000.2345m);
1879 DBHelper.ExecuteNonQuery (conn,
1880 String.Format (create_query, "nchar(10)"));
1881 rpc_helper_function (cmd, SqlDbType.NChar, 10,
1882 "characters", "characters",
1883 "characters", "characters");
1884 rpc_helper_function (cmd, SqlDbType.NChar, 3,
1885 "characters", "cha ",
1887 rpc_helper_function (cmd, SqlDbType.NChar, 3,
1891 rpc_helper_function (cmd, SqlDbType.NChar, 5,
1892 DBNull.Value, DBNull.Value,
1898 DBHelper.ExecuteNonQuery (conn,
1899 String.Format (create_query, "ntext"));
1901 rpc_helper_function (cmd, SqlDbType.NText, 0, "ntext");
1902 rpc_helper_function (cmd, SqlDbType.NText, 0, "");
1903 rpc_helper_function (cmd, SqlDbType.NText, 0, null);
1907 // Test NVarChar Param
1908 DBHelper.ExecuteNonQuery (conn,
1909 String.Format (create_query, "nvarchar(10)"));
1910 rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1911 "nvarchar", "nvarchar", "nvarchar",
1913 rpc_helper_function (cmd, SqlDbType.NVarChar, 3,
1914 "nvarchar", "nva", "nva", "nva");
1916 rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1917 string.Empty, string.Empty, string.Empty);
1918 rpc_helper_function (cmd, SqlDbType.NVarChar, 10,
1919 DBNull.Value, DBNull.Value, DBNull.Value);
1924 DBHelper.ExecuteNonQuery (conn,
1925 String.Format (create_query, "real"));
1926 rpc_helper_function (cmd, SqlDbType.Real, 0,
1927 10m, 10f, 10f, 10f);
1928 rpc_helper_function (cmd, SqlDbType.Real, 0,
1929 10d, 10f, 10f, 10f);
1930 rpc_helper_function (cmd, SqlDbType.Real, 0,
1932 rpc_helper_function (cmd, SqlDbType.Real, 0,
1933 3.54d, 3.54f, 3.54f, 3.54f);
1934 rpc_helper_function (cmd, SqlDbType.Real, 0,
1936 rpc_helper_function (cmd, SqlDbType.Real, 0,
1937 10.5f, 10.5f, 10.5f, 10.5f);
1938 rpc_helper_function (cmd, SqlDbType.Real, 0,
1939 3.5d, 3.5f, 3.5f, 3.5f);
1940 rpc_helper_function (cmd, SqlDbType.Real, 0,
1941 4.54m, 4.54f, 4.54f, 4.54f);
1942 rpc_helper_function (cmd, SqlDbType.Real, 0,
1943 -4.54m, -4.54f, -4.54f, -4.54f);
1944 rpc_helper_function (cmd, SqlDbType.Real, 0,
1945 DBNull.Value, DBNull.Value,
1946 DBNull.Value, DBNull.Value);
1949 // Test SmallDateTime Param
1950 DBHelper.ExecuteNonQuery (conn,
1951 String.Format (create_query, "smalldatetime"));
1952 rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0,
1953 "6/6/2079 11:59:00 PM",
1954 new DateTime (2079, 6, 6, 23, 59, 0),
1955 new DateTime (2079, 6, 6, 23, 59, 0),
1956 new DateTime (2079, 6, 6, 23, 59, 0));
1957 rpc_helper_function (cmd, SqlDbType.SmallDateTime, 0,
1958 DBNull.Value, DBNull.Value,
1959 DBNull.Value, DBNull.Value);
1962 // Test SmallInt Param
1963 DBHelper.ExecuteNonQuery (conn,
1964 String.Format (create_query, "smallint"));
1965 rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1966 10, (short) 10, (short) 10, (short) 10);
1967 rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1968 -10, (short) -10, (short) -10,
1970 rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1971 short.MaxValue, short.MaxValue,
1972 short.MaxValue, short.MaxValue);
1973 rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1974 short.MinValue, short.MinValue,
1975 short.MinValue, short.MinValue);
1976 rpc_helper_function (cmd, SqlDbType.SmallInt, 0,
1977 DBNull.Value, DBNull.Value,
1978 DBNull.Value, DBNull.Value);
1981 // Test SmallMoney Param
1982 DBHelper.ExecuteNonQuery (conn,
1983 String.Format (create_query, "smallmoney"));
1984 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1985 10.0d, 10m, 10m, 10m);
1986 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1988 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1989 3.54d, 3.54m, 3.54m, 3.54m);
1990 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1992 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1993 10.5f, 10.5m, 10.5m, 10.5m);
1994 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1995 3.5d, 3.5m, 3.5m, 3.5m);
1996 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1997 4.54m, 4.54m, 4.54m, 4.54m);
1998 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
1999 -4.54m, -4.54m, -4.54m, -4.54m);
2000 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2001 -214748.3648m, -214748.3648m,
2002 -214748.3648m, -214748.3648m);
2003 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2004 214748.3647m, 214748.3647m, 214748.3647m,
2006 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2007 DBNull.Value, DBNull.Value, DBNull.Value,
2011 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2012 -4.543361m, -4.5434m, -4.5434m, -4.5434m);
2013 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2014 -4.543351m, -4.5434m, -4.5434m, -4.5434m);
2015 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2016 -4.543341m, -4.5433m, -4.5433m, -4.5433m);
2017 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2018 4.543361m, 4.5434m, 4.5434m, 4.5434m);
2019 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2020 4.543351m, 4.5434m, 4.5434m, 4.5434m);
2021 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2022 4.543341m, 4.5433m, 4.5433m, 4.5433m);
2023 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2024 -4.543261m, -4.5433m, -4.5433m, -4.5433m);
2025 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2026 -4.543251m, -4.5433m, -4.5433m, -4.5433m);
2027 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2028 -4.543241m, -4.5432m, -4.5432m, -4.5432m);
2029 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2030 4.543261m, 4.5433m, 4.5433m, 4.5433m);
2031 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2032 4.543251m, 4.5433m, 4.5433m, 4.5433m);
2033 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2034 4.543241m, 4.5432m, 4.5432m, 4.5432m);
2035 // FIXME: we round toward even in SqlParameter.ConvertToFrameworkType
2037 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2038 -4.543350m, -4.5434m, -4.5434m);
2039 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2040 4.543350m, 4.5434m, 4.5434m);
2041 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2042 -4.543250m, -4.5433m, -4.5433m);
2043 rpc_helper_function (cmd, SqlDbType.SmallMoney, 0,
2044 4.543250m, 4.5433m, 4.5433m);
2049 DBHelper.ExecuteNonQuery (conn,
2050 String.Format (create_query, "text"));
2052 rpc_helper_function (cmd, SqlDbType.Text, 0, "text");
2053 rpc_helper_function (cmd, SqlDbType.Text, 0, "");
2054 rpc_helper_function (cmd, SqlDbType.Text, 0, null);
2058 // Test TimeStamp Param
2060 DBHelper.ExecuteNonQuery (conn,
2061 String.Format(create_query,"timestamp"));
2062 rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, "");
2063 rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, "");
2064 rpc_helper_function (cmd, SqlDbType.TimeStamp, 0, null);
2068 // Test TinyInt Param
2069 DBHelper.ExecuteNonQuery (conn,
2070 String.Format (create_query, "tinyint"));
2071 rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2072 10.0d, (byte) 10, (byte) 10,
2074 rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2075 0, (byte) 0, (byte) 0, (byte) 0);
2076 rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2077 byte.MaxValue, byte.MaxValue,
2078 byte.MaxValue, byte.MaxValue);
2079 rpc_helper_function (cmd, SqlDbType.TinyInt, 0,
2080 byte.MinValue, byte.MinValue,
2081 byte.MinValue, byte.MinValue);
2084 // Test UniqueIdentifier Param
2086 DBHelper.ExecuteNonQuery (conn,
2087 String.Format(create_query,"uniqueidentifier"));
2088 rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, "0f159bf395b1d04f8c2ef5c02c3add96");
2089 rpc_helper_function (cmd, SqlDbType.UniqueIdentifier, 0, null);
2093 // Test VarBinary Param
2095 DBHelper.ExecuteNonQuery (conn,
2096 String.Format(create_query,"varbinary (10)"));
2097 rpc_helper_function (cmd, SqlDbType.VarBinary, 0,);
2098 rpc_helper_function (cmd, SqlDbType.VarBinary, 0,);
2099 rpc_helper_function (cmd, SqlDbType.VarBinary, 0, null);
2103 // Test Varchar Param
2104 DBHelper.ExecuteNonQuery (conn,
2105 String.Format (create_query, "varchar(10)"));
2106 rpc_helper_function (cmd, SqlDbType.VarChar, 7,
2107 "VarChar", "VarChar", "VarChar",
2109 rpc_helper_function (cmd, SqlDbType.VarChar, 5,
2110 "Var", "Var", "Var", "Var");
2112 rpc_helper_function (cmd, SqlDbType.VarChar, 3,
2113 "Varchar", "Var", "Var");
2114 rpc_helper_function (cmd, SqlDbType.VarChar, 10,
2115 string.Empty, string.Empty, string.Empty);
2116 rpc_helper_function (cmd, SqlDbType.VarChar, 10,
2117 DBNull.Value, DBNull.Value,
2122 // Test Variant Param
2124 DBHelper.ExecuteNonQuery (conn,
2125 String.Format(create_query,"variant"));
2126 rpc_helper_function (cmd, SqlDbType.Variant, 0, );
2127 rpc_helper_function (cmd, SqlDbType.Variant, 0, );
2128 rpc_helper_function (cmd, SqlDbType.Variant, 0, null);
2135 } catch (AssertionException ex) {
2136 error += String.Format (" Case {0} INCORRECT VALUE : {1}\n", label, ex.ToString ());
2137 } catch (Exception ex) {
2138 error += String.Format (" Case {0} NOT WORKING : {1}\n", label, ex.ToString ());
2143 DBHelper.ExecuteNonQuery (conn, string.Format (
2144 CultureInfo.InvariantCulture,
2145 DROP_STORED_PROCEDURE, "#tmp_sp_param_test"));
2148 if (error.Length != 0)
2149 Assert.Fail (error);
2152 private void rpc_helper_function (SqlCommand cmd, SqlDbType type, int size, object input, object expectedRead, object expectedOut, object expectedInOut)
2154 cmd.Parameters.Clear ();
2155 SqlParameter param1, param2, param3;
2157 param1 = new SqlParameter ("@param1", type, size);
2158 param2 = new SqlParameter ("@param2", type, size);
2159 param3 = new SqlParameter ("@param3", type, size);
2161 param1 = new SqlParameter ("@param1", type);
2162 param2 = new SqlParameter ("@param2", type);
2163 param3 = new SqlParameter ("@param3", type);
2166 SqlParameter retval = new SqlParameter ("retval", SqlDbType.Int);
2167 param1.Value = input;
2168 param1.Direction = ParameterDirection.Input;
2169 param2.Direction = ParameterDirection.Output;
2170 param3.Direction = ParameterDirection.InputOutput;
2171 param3.Value = input;
2172 retval.Direction = ParameterDirection.ReturnValue;
2173 cmd.Parameters.Add (param1);
2174 cmd.Parameters.Add (param2);
2175 cmd.Parameters.Add (param3);
2176 cmd.Parameters.Add (retval);
2177 cmd.CommandText = "#tmp_sp_param_test";
2178 cmd.CommandType = CommandType.StoredProcedure;
2179 using (SqlDataReader reader = cmd.ExecuteReader ()) {
2180 Assert.IsTrue (reader.Read (), "#1");
2181 AreEqual (expectedRead, reader.GetValue (0), "#2");
2182 Assert.IsFalse (reader.Read (), "#3");
2185 AreEqual (expectedOut, param2.Value, "#4");
2186 AreEqual (expectedInOut, param3.Value, "#5");
2187 Assert.AreEqual (5, retval.Value, "#6");
2191 public void OutputParamSizeTest1 ()
2193 conn = ConnectionManager.Instance.Sql.Connection;
2194 cmd = new SqlCommand ();
2195 cmd.Connection = conn;
2197 cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2198 cmd.CommandType = CommandType.Text;
2199 cmd.ExecuteNonQuery ();
2201 cmd.CommandText = "#testsize";
2202 cmd.CommandType = CommandType.StoredProcedure;
2204 SqlParameter p1 = new SqlParameter ();
2205 p1.ParameterName = "@p1";
2206 p1.Direction = ParameterDirection.InputOutput;
2207 p1.DbType = DbType.String;
2208 p1.IsNullable = false;
2209 cmd.Parameters.Add (p1);
2212 cmd.ExecuteNonQuery ();
2214 } catch (InvalidOperationException ex) {
2215 // String[0]: the Size property has an invalid
2217 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2218 Assert.IsNull (ex.InnerException, "#3");
2219 Assert.IsNotNull (ex.Message, "#4");
2224 public void OutputParamSizeTest2 ()
2226 conn = ConnectionManager.Instance.Sql.Connection;
2227 cmd = new SqlCommand ();
2228 cmd.Connection = conn;
2230 cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2231 cmd.CommandType = CommandType.Text;
2232 cmd.ExecuteNonQuery ();
2234 cmd.CommandText = "#testsize";
2235 cmd.CommandType = CommandType.StoredProcedure;
2237 SqlParameter p1 = new SqlParameter ();
2238 p1.ParameterName = "@p1";
2239 p1.Direction = ParameterDirection.Output;
2240 p1.DbType = DbType.String;
2241 p1.IsNullable = false;
2242 cmd.Parameters.Add (p1);
2245 cmd.ExecuteNonQuery ();
2247 } catch (InvalidOperationException ex) {
2248 // String[0]: the Size property has an invalid
2250 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2251 Assert.IsNull (ex.InnerException, "#3");
2252 Assert.IsNotNull (ex.Message, "#4");
2257 public void OutputParamSizeTest3 ()
2259 conn = ConnectionManager.Instance.Sql.Connection;
2260 cmd = new SqlCommand ();
2261 cmd.Connection = conn;
2263 cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2264 cmd.CommandType = CommandType.Text;
2265 cmd.ExecuteNonQuery ();
2267 cmd.CommandText = "#testsize";
2268 cmd.CommandType = CommandType.StoredProcedure;
2270 SqlParameter p1 = new SqlParameter ();
2271 p1.ParameterName = "@p1";
2272 p1.Direction = ParameterDirection.InputOutput;
2273 p1.DbType = DbType.String;
2274 p1.IsNullable = true;
2275 cmd.Parameters.Add (p1);
2278 cmd.ExecuteNonQuery ();
2280 } catch (InvalidOperationException ex) {
2281 // String[0]: the Size property has an invalid
2283 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2284 Assert.IsNull (ex.InnerException, "#3");
2285 Assert.IsNotNull (ex.Message, "#4");
2290 public void OutputParamSizeTest4 ()
2292 conn = ConnectionManager.Instance.Sql.Connection;
2293 cmd = new SqlCommand ();
2294 cmd.Connection = conn;
2296 cmd.CommandText = "create procedure #testsize (@p1 as varchar(10) output) as return";
2297 cmd.CommandType = CommandType.Text;
2298 cmd.ExecuteNonQuery ();
2300 cmd.CommandText = "#testsize";
2301 cmd.CommandType = CommandType.StoredProcedure;
2303 SqlParameter p1 = new SqlParameter ();
2304 p1.ParameterName = "@p1";
2305 p1.Direction = ParameterDirection.Output;
2306 p1.DbType = DbType.String;
2307 p1.IsNullable = true;
2308 cmd.Parameters.Add (p1);
2311 cmd.ExecuteNonQuery ();
2313 } catch (InvalidOperationException ex) {
2314 // String[0]: the Size property has an invalid
2316 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
2317 Assert.IsNull (ex.InnerException, "#3");
2318 Assert.IsNotNull (ex.Message, "#4");
2322 [Test] // bug #470579
2323 public void OutputParamTest ()
2325 SqlParameter newId, id;
2327 conn = ConnectionManager.Instance.Sql.Connection;
2329 cmd = conn.CreateCommand ();
2330 cmd.CommandText = "set @NewId=@Id + 2";
2331 cmd.CommandType = CommandType.Text;
2332 newId = cmd.Parameters.Add ("@NewId", SqlDbType.Int);
2333 newId.Direction = ParameterDirection.Output;
2334 id = cmd.Parameters.Add ("@Id", SqlDbType.Int);
2336 cmd.ExecuteNonQuery ();
2338 Assert.AreEqual (5, newId.Value, "#A1");
2339 Assert.AreEqual (3, id.Value, "#A2");
2341 cmd = conn.CreateCommand ();
2342 cmd.CommandText = "set @NewId=@Id + 2";
2343 cmd.CommandType = CommandType.Text;
2344 newId = cmd.Parameters.Add ("NewId", SqlDbType.Int);
2345 newId.Direction = ParameterDirection.Output;
2346 id = cmd.Parameters.Add ("Id", SqlDbType.Int);
2348 cmd.ExecuteNonQuery ();
2350 Assert.AreEqual (8, newId.Value, "#B1");
2351 Assert.AreEqual (6, id.Value, "#B2");
2355 public void SmallMoney_Overflow_Max ()
2357 conn = new SqlConnection (connectionString);
2360 DBHelper.ExecuteNonQuery (conn, string.Format (
2361 CultureInfo.InvariantCulture, CREATE_TMP_SP_TYPE_TEST,
2363 //decimal overflow = 214748.36471m;
2364 decimal overflow = 214748.3648m;
2366 cmd = conn.CreateCommand ();
2367 cmd.CommandText = "#tmp_sp_type_test";
2368 cmd.CommandType = CommandType.StoredProcedure;
2370 SqlParameter param = cmd.Parameters.Add ("@param",
2371 SqlDbType.SmallMoney);
2372 param.Value = overflow;
2375 cmd.ExecuteScalar ();
2377 } catch (OverflowException ex) {
2378 // SqlDbType.SmallMoney overflow. Value '214748.36471'
2379 // is out of range. Must be between -214,748.3648 and 214,748.3647
2380 Assert.AreEqual (typeof (OverflowException), ex.GetType (), "#2");
2381 Assert.IsNull (ex.InnerException, "#3");
2382 Assert.IsNotNull (ex.Message, "#4");
2383 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2384 CultureInfo.InvariantCulture, "'{0}'",
2385 overflow)) != -1, "#5:" + ex.Message);
2386 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2387 CultureInfo.InvariantCulture, "{0:N4}",
2388 SMALLMONEY_MIN)) != -1, "#6:" + ex.Message);
2389 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2390 CultureInfo.InvariantCulture, "{0:N4}",
2391 SMALLMONEY_MAX)) != -1, "#7:" + ex.Message);
2393 DBHelper.ExecuteNonQuery (conn, string.Format (
2394 CultureInfo.InvariantCulture,
2395 DROP_STORED_PROCEDURE, "#tmp_sp_type_test"));
2400 public void SmallMoney_Overflow_Min ()
2402 conn = new SqlConnection (connectionString);
2405 DBHelper.ExecuteNonQuery (conn, string.Format (
2406 CultureInfo.InvariantCulture, CREATE_TMP_SP_TYPE_TEST,
2408 //decimal overflow = -214748.36481m;
2409 decimal overflow = -214748.3649m;
2411 cmd = conn.CreateCommand ();
2412 cmd.CommandText = "#tmp_sp_type_test";
2413 cmd.CommandType = CommandType.StoredProcedure;
2415 SqlParameter param = cmd.Parameters.Add ("@param",
2416 SqlDbType.SmallMoney);
2417 param.Value = overflow;
2420 cmd.ExecuteScalar ();
2422 } catch (OverflowException ex) {
2423 // SqlDbType.SmallMoney overflow. Value '-214748,36481'
2424 // is out of range. Must be between -214,748.3648 and 214,748.3647
2425 Assert.AreEqual (typeof (OverflowException), ex.GetType (), "#2");
2426 Assert.IsNull (ex.InnerException, "#3");
2427 Assert.IsNotNull (ex.Message, "#4");
2428 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2429 CultureInfo.InvariantCulture, "'{0}'",
2430 overflow)) != -1, "#5:" + ex.Message);
2431 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2432 CultureInfo.InvariantCulture, "{0:N4}",
2433 SMALLMONEY_MIN)) != -1, "#6:" + ex.Message);
2434 Assert.IsTrue (ex.Message.IndexOf (string.Format (
2435 CultureInfo.InvariantCulture, "{0:N4}",
2436 SMALLMONEY_MAX)) != -1, "#7:" + ex.Message);
2438 DBHelper.ExecuteNonQuery (conn, string.Format (
2439 CultureInfo.InvariantCulture,
2440 DROP_STORED_PROCEDURE, "#tmp_sp_type_test"));
2445 public void NotificationTest ()
2447 cmd = new SqlCommand ();
2448 SqlNotificationRequest notification = new SqlNotificationRequest("MyNotification","MyService",15);
2449 Assert.AreEqual (null, cmd.Notification, "#1 The default value for this property should be null");
2450 cmd.Notification = notification;
2451 Assert.AreEqual ("MyService", cmd.Notification.Options, "#2 The value should be MyService as the constructor is initiated with this value");
2452 Assert.AreEqual (15, cmd.Notification.Timeout, "#2 The value should be 15 as the constructor is initiated with this value");
2456 public void NotificationAutoEnlistTest ()
2458 cmd = new SqlCommand ();
2459 Assert.AreEqual (true, cmd.NotificationAutoEnlist, "#1 Default value of the property should be true");
2460 cmd.NotificationAutoEnlist = false;
2461 Assert.AreEqual (false, cmd.NotificationAutoEnlist, "#2 The value of the property should be false after setting it to false");
2465 public void BeginExecuteXmlReaderTest ()
2467 cmd = new SqlCommand ();
2468 string connectionString1 = null;
2469 connectionString1 = ConnectionManager.Instance.Sql.ConnectionString + ";Asynchronous Processing=true";
2471 SqlConnection conn1 = new SqlConnection (connectionString1);
2473 cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA";
2474 cmd.Connection = conn1;
2476 IAsyncResult result = cmd.BeginExecuteXmlReader ();
2477 XmlReader reader = cmd.EndExecuteXmlReader (result);
2478 while (reader.Read ()) {
2479 if (reader.LocalName.ToString () == "employee")
2480 Assert.AreEqual ("kumar", reader["lname"], "#1 ");
2483 ConnectionManager.Instance.Sql.CloseConnection ();
2488 [Ignore("MS .NET doesn't throw IOE here. TODO: check corefx")]
2489 public void BeginExecuteXmlReaderExceptionTest ()
2491 cmd = new SqlCommand ();
2493 SqlConnection conn = new SqlConnection (connectionString);
2495 cmd.CommandText = "Select lname from employee where id<2 FOR XML AUTO, XMLDATA";
2496 cmd.Connection = conn;
2499 /*IAsyncResult result = */cmd.BeginExecuteXmlReader ();
2500 } catch (InvalidOperationException) {
2501 Assert.AreEqual (ConnectionManager.Instance.Sql.ConnectionString, connectionString, "#1 Connection string has changed");
2504 Assert.Fail ("Expected Exception InvalidOperationException not thrown");
2506 ConnectionManager.Instance.Sql.CloseConnection ();
2511 public void SqlCommandDisposeTest ()
2513 IDataReader reader = null;
2514 conn = ConnectionManager.Instance.Sql.Connection;
2517 IDbCommand command = conn.CreateCommand ();
2519 string sql = "SELECT * FROM employee";
2520 command.CommandText = sql;
2521 reader = command.ExecuteReader ();
2525 while (reader.Read ()) ;
2528 ConnectionManager.Instance.Sql.CloseConnection ();
2532 private void bug326182_OutputParamMixupTestCommon (int paramOrder,
2539 conn = ConnectionManager.Instance.Sql.Connection;
2543 SqlParameter param0 = new SqlParameter ("@param0", SqlDbType.Int);
2544 param0.Direction = ParameterDirection.Output;
2545 SqlParameter param1 = new SqlParameter ("@param1", SqlDbType.Int);
2546 param1.Direction = ParameterDirection.Output;
2547 SqlParameter param2 = new SqlParameter ("@param2", SqlDbType.Int);
2548 param2.Direction = ParameterDirection.Output;
2549 SqlParameter param3 = new SqlParameter ("@param3", SqlDbType.Int);
2550 param3.Direction = ParameterDirection.Output;
2551 SqlParameter rval = new SqlParameter ("@RETURN_VALUE", SqlDbType.Int);
2552 rval.Direction = ParameterDirection.ReturnValue;
2554 cmd = conn.CreateCommand ();
2555 cmd.CommandText = "dbo.[sp_326182a]";
2556 cmd.CommandType = CommandType.StoredProcedure;
2558 switch (paramOrder) {
2559 case 1: cmd.Parameters.Add (param0);
2560 cmd.Parameters.Add (param1);
2561 cmd.Parameters.Add (rval);
2562 cmd.Parameters.Add (param2);
2563 cmd.Parameters.Add (param3);
2565 case 2: cmd.Parameters.Add (rval);
2566 cmd.Parameters.Add (param1);
2567 cmd.Parameters.Add (param0);
2568 cmd.Parameters.Add (param2);
2569 cmd.Parameters.Add (param3);
2571 default: cmd.Parameters.Add (param0);
2572 cmd.Parameters.Add (param1);
2573 cmd.Parameters.Add (param2);
2574 cmd.Parameters.Add (param3);
2575 cmd.Parameters.Add (rval);
2579 cmd.ExecuteNonQuery ();
2581 /* Copy the param values to variables, just in case if
2582 * tests fail, we don't want the created sp to exist */
2583 param3Val = (int) cmd.Parameters ["@param3"].Value;
2584 param1Val = (int) cmd.Parameters ["@param1"].Value;
2585 rvalVal = (int) cmd.Parameters ["@RETURN_VALUE"].Value;
2586 param2Val = (int) cmd.Parameters ["@param2"].Value;
2587 param0Val = (int) cmd.Parameters ["@param0"].Value;
2593 ConnectionManager.Instance.Sql.CloseConnection ();
2599 public void bug326182_OutputParamMixupTest_Normal ()
2601 int param0Val, param1Val, param2Val, param3Val, rvalVal;
2603 //param0Val = param1Val = param2Val = param3Val = rvalVal = 0;
2605 bug326182_OutputParamMixupTestCommon (0, out param0Val, out param1Val,
2606 out param2Val, out param3Val, out rvalVal);
2607 Assert.AreEqual (103, param3Val);
2608 Assert.AreEqual (101, param1Val);
2609 Assert.AreEqual (2, rvalVal);
2610 Assert.AreEqual (102, param2Val);
2611 Assert.AreEqual (100, param0Val);
2615 public void bug326182_OutputParamMixupTest_RValInBetween ()
2617 int param0Val, param1Val, param2Val, param3Val, rvalVal;
2619 bug326182_OutputParamMixupTestCommon (1, out param0Val, out param1Val,
2620 out param2Val, out param3Val, out rvalVal);
2621 Assert.AreEqual (103, param3Val);
2622 Assert.AreEqual (101, param1Val);
2623 Assert.AreEqual (2, rvalVal);
2624 Assert.AreEqual (102, param2Val);
2625 Assert.AreEqual (100, param0Val);
2629 public void bug326182_OutputParamMixupTest_RValFirst ()
2631 int param0Val, param1Val, param2Val, param3Val, rvalVal;
2633 bug326182_OutputParamMixupTestCommon (2, out param0Val, out param1Val,
2634 out param2Val, out param3Val, out rvalVal);
2635 Assert.AreEqual (103, param3Val);
2636 Assert.AreEqual (101, param1Val);
2637 Assert.AreEqual (2, rvalVal);
2638 Assert.AreEqual (102, param2Val);
2639 Assert.AreEqual (100, param0Val);
2643 public void DeriveParameterTest_FullSchema ()
2645 string create_tbl = "CREATE TABLE decimalCheck (deccheck DECIMAL (19, 5) null)";
2646 string create_sp = "CREATE PROCEDURE sp_bug584833(@deccheck decimal(19,5) OUT)"
2647 + "AS " + Environment.NewLine
2648 + "BEGIN" + Environment.NewLine
2649 + "INSERT INTO decimalCheck values (@deccheck)" + Environment.NewLine
2650 + "SELECT @deccheck=deccheck from decimalCheck" + Environment.NewLine
2653 conn = ConnectionManager.Instance.Sql.Connection;
2655 cmd = conn.CreateCommand ();
2656 cmd.CommandText = create_tbl;
2657 cmd.ExecuteNonQuery ();
2659 cmd.CommandText = create_sp;
2660 cmd.ExecuteNonQuery ();
2662 cmd.CommandText = "dbo.sp_bug584833";
2663 cmd.CommandType = CommandType.StoredProcedure;
2665 SqlCommandBuilder.DeriveParameters (cmd);
2666 Assert.AreEqual (2, cmd.Parameters.Count, "#DPT - FullSchema - Parameter count mismatch");
2667 Assert.AreEqual ("@deccheck", cmd.Parameters[1].ParameterName, "#DPT - FullSchema - Parameter name mismatch");
2668 Assert.AreEqual (SqlDbType.Decimal, cmd.Parameters[1].SqlDbType, "#DPT - FullSchema - Parameter type mismatch");
2670 cmd.Parameters.Clear ();
2671 cmd.CommandText = "drop procedure sp_bug584833";
2672 cmd.CommandType = CommandType.Text;
2673 cmd.ExecuteNonQuery ();
2674 cmd.CommandText = "drop table decimalCheck";
2675 cmd.ExecuteNonQuery ();
2678 ConnectionManager.Instance.Sql.CloseConnection ();
2685 public void DeriveParameterTest_SPName ()
2687 string create_tbl = "CREATE TABLE decimalCheck (deccheck DECIMAL (19, 5) null)";
2688 string create_sp = "CREATE PROCEDURE sp_bug584833(@deccheck decimal(19,5) OUT)"
2689 + "AS " + Environment.NewLine
2690 + "BEGIN" + Environment.NewLine
2691 + "INSERT INTO decimalCheck values (@deccheck)" + Environment.NewLine
2692 + "SELECT @deccheck=deccheck from decimalCheck" + Environment.NewLine
2695 conn = ConnectionManager.Instance.Sql.Connection;
2698 cmd = conn.CreateCommand ();
2699 cmd.CommandText = create_tbl;
2700 cmd.ExecuteNonQuery ();
2702 cmd.CommandText = create_sp;
2703 cmd.ExecuteNonQuery ();
2705 cmd.CommandText = "sp_bug584833";
2706 cmd.CommandType = CommandType.StoredProcedure;
2708 SqlCommandBuilder.DeriveParameters (cmd);
2709 Assert.AreEqual (2, cmd.Parameters.Count, "#DPT - SPName - Parameter count mismatch");
2710 Assert.AreEqual ("@deccheck", cmd.Parameters[1].ParameterName, "#DPT - SPName - Parameter name mismatch");
2711 Assert.AreEqual (SqlDbType.Decimal, cmd.Parameters[1].SqlDbType, "#DPT - SPName - Parameter type mismatch");
2713 cmd.Parameters.Clear ();
2714 cmd.CommandType = CommandType.Text;
2715 cmd.CommandText = "drop procedure sp_bug584833";
2716 cmd.ExecuteNonQuery ();
2717 cmd.CommandText = "drop table decimalCheck";
2718 cmd.ExecuteNonQuery ();
2721 ConnectionManager.Instance.Sql.CloseConnection ();
2727 public void DeriveParameterTest_UserSchema ()
2729 string create_tbl = "CREATE TABLE decimalCheck (deccheck DECIMAL (19, 5) null)";
2730 string create_sp = "CREATE PROCEDURE sp_bug584833(@deccheck decimal(19,5) OUT)"
2731 + "AS " + Environment.NewLine
2732 + "BEGIN" + Environment.NewLine
2733 + "INSERT INTO decimalCheck values (@deccheck)" + Environment.NewLine
2734 + "SELECT @deccheck=deccheck from decimalCheck" + Environment.NewLine
2738 conn = ConnectionManager.Instance.Sql.Connection;
2740 cmd = conn.CreateCommand ();
2741 cmd.CommandText = create_tbl;
2742 cmd.ExecuteNonQuery ();
2744 cmd.CommandText = create_sp;
2745 cmd.ExecuteNonQuery ();
2747 cmd.CommandText = "dbo.sp_bug584833";
2748 cmd.CommandType = CommandType.StoredProcedure;
2750 SqlCommandBuilder.DeriveParameters (cmd);
2751 Assert.AreEqual (2, cmd.Parameters.Count, "#DPT - user schema - Parameter count mismatch");
2752 Assert.AreEqual ("@deccheck", cmd.Parameters[1].ParameterName, "#DPT - user schema - Parameter name mismatch");
2753 Assert.AreEqual (SqlDbType.Decimal, cmd.Parameters[1].SqlDbType, "#DPT - user schema - Parameter type mismatch");
2755 cmd.Parameters.Clear ();
2756 cmd.CommandType = CommandType.Text;
2757 cmd.CommandText = "drop procedure dbo.sp_bug584833";
2758 cmd.ExecuteNonQuery ();
2759 cmd.CommandText = "drop table decimalCheck";
2760 cmd.ExecuteNonQuery ();
2763 ConnectionManager.Instance.Sql.CloseConnection ();
2768 [Test] // bug#561667
2769 public void CmdDispose_DataReaderReset ()
2771 conn = ConnectionManager.Instance.Sql.Connection;
2774 string query1 = "SELECT fname FROM employee where lname='kumar'";
2775 string query2 = "SELECT type_int FROM numeric_family where type_bit = 1";
2778 t = GetColumns(conn, query1);
2779 Assert.AreEqual ("suresh", t.Rows[0][0], "CmdDD#1: Query1 result mismatch");
2780 t = GetColumns(conn, query2);
2781 Assert.AreEqual (int.MaxValue, t.Rows[0][0], "CmdDD#2: Query2 result mismatch");
2783 ConnectionManager.Instance.Sql.CloseConnection ();
2788 private DataTable GetColumns(DbConnection connection, string query)
2790 DataTable t = new DataTable("Columns");
2791 using (DbCommand c = connection.CreateCommand())
2793 c.CommandText = query;
2794 t.Load(c.ExecuteReader());
2799 // used as workaround for bugs in NUnit 2.2.0
2800 static void AreEqual (object x, object y, string msg)
2802 if (x == null && y == null)
2804 if ((x == null || y == null))
2805 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2806 "Expected: {0}, but was: {1}. {2}",
2807 x == null ? "<null>" : x, y == null ? "<null>" : y, msg));
2809 bool isArrayX = x.GetType ().IsArray;
2810 bool isArrayY = y.GetType ().IsArray;
2812 if (isArrayX && isArrayY) {
2813 Array arrayX = (Array) x;
2814 Array arrayY = (Array) y;
2816 if (arrayX.Length != arrayY.Length)
2817 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2818 "Length of arrays differs. Expected: {0}, but was: {1}. {2}",
2819 arrayX.Length, arrayY.Length, msg));
2821 for (int i = 0; i < arrayX.Length; i++) {
2822 object itemX = arrayX.GetValue (i);
2823 object itemY = arrayY.GetValue (i);
2824 if (!itemX.Equals (itemY))
2825 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2826 "Arrays differ at position {0}. Expected: {1}, but was: {2}. {3}",
2827 i, itemX, itemY, msg));
2829 } else if (!x.Equals (y)) {
2830 throw new AssertionException (string.Format (CultureInfo.InvariantCulture,
2831 "Expected: {0} ({1}), but was: {2} ({3}). {4}",
2832 x, x.GetType (), y, y.GetType (), msg));
2838 return (engine.ClientVersion);
2848 private readonly string CREATE_TMP_SP_PARAM_TEST =
2849 "CREATE PROCEDURE #tmp_sp_param_test (" + Environment.NewLine +
2850 " @param1 {0}," + Environment.NewLine +
2851 " @param2 {0} output," + Environment.NewLine +
2852 " @param3 {0} output)" + Environment.NewLine +
2853 "AS" + Environment.NewLine +
2854 "BEGIN" + Environment.NewLine +
2855 " SELECT @param1" + Environment.NewLine +
2856 " SET @param2=@param1" + Environment.NewLine +
2857 " RETURN 5" + Environment.NewLine +
2860 private readonly string CREATE_TMP_SP_TEMP_INSERT_PERSON = ("create procedure #sp_temp_insert_employee ( " + Environment.NewLine +
2861 "@fname varchar (20), " + Environment.NewLine +
2862 "@dob datetime, " + Environment.NewLine +
2863 "@doj datetime output " + Environment.NewLine +
2864 ") " + Environment.NewLine +
2865 "as " + Environment.NewLine +
2866 "begin" + Environment.NewLine +
2867 "declare @id int;" + Environment.NewLine +
2868 "select @id = max (id) from employee;" + Environment.NewLine +
2869 "set @id = @id + 6000 + 1;" + Environment.NewLine +
2870 "set @doj = getdate();" + Environment.NewLine +
2871 "insert into employee (id, fname, dob, doj) values (@id, @fname, @dob, @doj);" + Environment.NewLine +
2872 "return @id;" + Environment.NewLine +
2875 private readonly string DROP_TMP_SP_TEMP_INSERT_PERSON = ("if exists (select name from sysobjects where " + Environment.NewLine +
2876 "name = '#sp_temp_insert_employee' and type = 'P') " + Environment.NewLine +
2877 "drop procedure #sp_temp_insert_employee; ");
2879 private static readonly string CREATE_TMP_SP_TYPE_TEST =
2880 "CREATE PROCEDURE #tmp_sp_type_test " +
2883 ") AS SELECT @param";
2884 private static readonly string DROP_STORED_PROCEDURE =
2885 "DROP PROCEDURE {0}";