mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlCommandTest.cs

   1 //
   2 // SqlCommandTest.cs - NUnit Test Cases for testing the
   3 //                          SqlCommand class
   4 // Author:
   5 //      Umadevi S (sumadevi@novell.com)
   6 //      Sureshkumar T (tsureshkumar@novell.com)
   7 //
   8 // Copyright (c) 2004 Novell Inc., and the individuals listed
   9 // on the ChangeLog entries.
  10 //
  11 // Permission is hereby granted, free of charge, to any person obtaining
  12 // a copy of this software and associated documentation files (the
  13 // "Software"), to deal in the Software without restriction, including
  14 // without limitation the rights to use, copy, modify, merge, publish,
  15 // distribute, sublicense, and/or sell copies of the Software, and to
  16 // permit persons to whom the Software is furnished to do so, subject to
  17 // the following conditions:
  18 //
  19 // The above copyright notice and this permission notice shall be
  20 // included in all copies or substantial portions of the Software.
  21 //
  22 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  23 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  24 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  25 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  26 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  27 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  28 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  29 //
  30
  31 using System;
  32 using System.Data;
  33 using System.Data.Common;
  34 using System.Data.SqlClient;
  35
  36 using NUnit.Framework;
  37
  38 namespace MonoTests.System.Data.SqlClient
  39 {
  40         [TestFixture]
  41         [Category ("sqlserver")]
  42         public class SqlCommandTest
  43         {
  44
  45                 public SqlConnection conn;
  46
  47                 [Test]
  48                 public void ExecuteNonQueryTempProcedureTest () {
  49                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
  50                         try {
  51                                 ConnectionManager.Singleton.OpenConnection ();
  52                                 // create temp sp here, should normally be created in Setup of test
  53                                 // case, but cannot be done right now because of ug #68978
  54                                 DBHelper.ExecuteNonQuery (conn, CREATE_TMP_SP_TEMP_INSERT_PERSON);
  55                                 SqlCommand cmd = new SqlCommand();
  56                                 cmd.Connection = conn;
  57                                 cmd.CommandText = "#sp_temp_insert_employee";
  58                                 cmd.CommandType = CommandType.StoredProcedure;
  59                                 Object TestPar = "test";
  60                                 cmd.Parameters.Add("@fname", SqlDbType.VarChar);
  61                                 cmd.Parameters ["@fname"].Value = TestPar;
  62                                 Assert.AreEqual(-1,cmd.ExecuteNonQuery());
  63                         } finally {
  64                                 DBHelper.ExecuteNonQuery (conn, DROP_TMP_SP_TEMP_INSERT_PERSON);
  65                                 DBHelper.ExecuteSimpleSP (conn, "sp_clean_person_table");
  66                                 ConnectionManager.Singleton.CloseConnection ();
  67                         }
  68                 }
  69
  70                 /**
  71                  * Verifies whether an enum value is converted to a numeric value when
  72                  * used as value for a numeric parameter (bug #66630)
  73                  */
  74                 [Test]
  75                 public void EnumParameterTest() {
  76                         conn = (SqlConnection) ConnectionManager.Singleton.Connection;
  77                         try {
  78                                 ConnectionManager.Singleton.OpenConnection ();
  79                                 // create temp sp here, should normally be created in Setup of test
  80                                 // case, but cannot be done right now because of ug #68978
  81                                 DBHelper.ExecuteNonQuery (conn, "CREATE PROCEDURE #Bug66630 ("
  82                                                           + "@Status smallint = 7"
  83                                                           + ")"
  84                                                           + "AS" + Environment.NewLine
  85                                                           + "BEGIN" + Environment.NewLine
  86                                                           + "SELECT CAST(5 AS int), @Status" + Environment.NewLine
  87                                                           + "END");
  88
  89                                 SqlCommand cmd = new SqlCommand("#Bug66630", conn);
  90                                 cmd.CommandType = CommandType.StoredProcedure;
  91                                 cmd.Parameters.Add("@Status", SqlDbType.Int).Value = Status.Error;
  92
  93                                 using (SqlDataReader dr = cmd.ExecuteReader()) {
  94                                         // one record should be returned
  95                                         Assert.IsTrue(dr.Read(), "EnumParameterTest#1");
  96                                         // we should get two field in the result
  97                                         Assert.AreEqual(2, dr.FieldCount, "EnumParameterTest#2");
  98                                         // field 1
  99                                         Assert.AreEqual("int", dr.GetDataTypeName(0), "EnumParameterTest#3");
 100                                         Assert.AreEqual(5, dr.GetInt32(0), "EnumParameterTest#4");
 101                                         // field 2
 102                                         Assert.AreEqual("smallint", dr.GetDataTypeName(1), "EnumParameterTest#5");
 103                                         Assert.AreEqual((short) Status.Error, dr.GetInt16(1), "EnumParameterTest#6");
 104                                         // only one record should be returned
 105                                         Assert.IsFalse(dr.Read(), "EnumParameterTest#7");
 106                                 }
 107                         } finally {
 108                                 DBHelper.ExecuteNonQuery (conn, "if exists (select name from sysobjects " +
 109                                                           " where name like '#temp_Bug66630' and type like 'P') " +
 110                                                           " drop procedure #temp_Bug66630; ");
 111                                 ConnectionManager.Singleton.CloseConnection ();
 112                         }
 113                 }
 114
 115                 /**
 116                  * The below test does not need a connection but since the setup opens
 117                  * the connection i will need to close it
 118                  */
 119                 [Test]
 120                 public void CloneTest() {
 121                         ConnectionManager.Singleton.OpenConnection ();
 122                         SqlCommand cmd = new SqlCommand();
 123                         cmd.Connection = null;
 124                         cmd.CommandText = "sp_insert";
 125                         cmd.CommandType = CommandType.StoredProcedure;
 126                         Object TestPar = DBNull.Value;
 127                         cmd.Parameters.Add("@TestPar1", SqlDbType.Int);
 128                         cmd.Parameters["@TestPar1"].Value = TestPar;
 129                         cmd.Parameters.Add("@BirthDate", DateTime.Now);
 130                         cmd.DesignTimeVisible = true;
 131                         cmd.CommandTimeout = 100;
 132                         Object clone1 = ((ICloneable)(cmd)).Clone();
 133                         SqlCommand cmd1 = (SqlCommand) clone1;
 134                         Assert.AreEqual(2, cmd1.Parameters.Count);
 135                         Assert.AreEqual(100, cmd1.CommandTimeout);
 136                         cmd1.Parameters.Add("@test", DateTime.Now);
 137                         // to check that it is deep copy and not a shallow copy of the
 138                         // parameter collection
 139                         Assert.AreEqual(3, cmd1.Parameters.Count);
 140                         Assert.AreEqual(2, cmd.Parameters.Count);
 141                 }
 142
 143                 private enum Status {
 144                         OK = 0,
 145                         Error = 3
 146                 }
 147
 148                 private readonly string CREATE_TMP_SP_TEMP_INSERT_PERSON = ("create procedure #sp_temp_insert_employee ( " + Environment.NewLine +
 149                                                                             "@fname varchar (20), " + Environment.NewLine +
 150                                                                             "as " + Environment.NewLine +
 151                                                                             "begin" + Environment.NewLine +
 152                                                                             "declare @id int;" + Environment.NewLine +
 153                                                                             "select @id = max (id) from employee;" + Environment.NewLine +
 154                                                                             "set @id = @id + 6000 + 1;" + Environment.NewLine +
 155                                                                             "insert into employee (id, fname, dob, doj) values (@id, @fname, '1980-02-11', getdate ());" + Environment.NewLine +
 156                                                                             "return @id;" + Environment.NewLine +
 157                                                                             "end");
 158
 159                 private readonly string DROP_TMP_SP_TEMP_INSERT_PERSON = ("if exists (select name from sysobjects where " + Environment.NewLine +
 160                                                                           "name = '#sp_temp_insert_employee' and type = 'P') " + Environment.NewLine +
 161                                                                           "drop procedure #sp_temp_insert_employee; ");
 162         }
 163 }