2 // SqlParameterTest.cs - NUnit Test Cases for testing the
5 // Senganal T (tsenganal@novell.com)
6 // Amit Biswas (amit@amitbiswas.com)
7 // Veerapuram Varadhan (vvaradhan@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.SqlClient;
36 using NUnit.Framework;
38 namespace MonoTests.System.Data.SqlClient
41 [Category ("sqlserver")]
42 public class SqlParameterTest
52 conn = (SqlConnection) ConnectionManager.Singleton.Connection;
53 ConnectionManager.Singleton.OpenConnection ();
54 engine = ConnectionManager.Singleton.Engine;
58 public void TearDown ()
64 ConnectionManager.Singleton.CloseConnection ();
68 public void ParameterSizeTest ()
70 if (ClientVersion == 7)
71 Assert.Ignore ("Hangs on SQL Server 7.0");
73 string longstring = new String('x', 20480);
75 cmd = new SqlCommand ("create table #text1 (ID int not null, Val1 ntext)", conn);
76 cmd.ExecuteNonQuery ();
77 cmd.CommandText = "INSERT INTO #text1(ID,Val1) VALUES (@ID,@Val1)";
78 prm = new SqlParameter ();
79 prm.ParameterName = "@ID";
81 cmd.Parameters.Add (prm);
83 prm = new SqlParameter ();
84 prm.ParameterName = "@Val1";
85 prm.Value = longstring;
86 prm.SqlDbType = SqlDbType.NText; // Comment and enjoy the truncation
87 cmd.Parameters.Add (prm);
88 cmd.ExecuteNonQuery ();
89 cmd = new SqlCommand ("select datalength(Val1) from #text1", conn);
90 Assert.AreEqual (20480 * 2, cmd.ExecuteScalar (), "#1");
92 cmd.CommandText = "INSERT INTO #text1(ID,Val1) VALUES (@ID,@Val1)";
93 prm = new SqlParameter ();
94 prm.ParameterName = "@ID";
96 cmd.Parameters.Add (prm);
98 prm = new SqlParameter ();
99 prm.ParameterName = "@Val1";
100 prm.Value = longstring;
101 //prm.SqlDbType = SqlDbType.NText;
102 cmd.Parameters.Add (prm);
103 cmd.ExecuteNonQuery ();
104 cmd = new SqlCommand ("select datalength(Val1) from #text1", conn);
105 Assert.AreEqual (20480 * 2, cmd.ExecuteScalar (), "#2");
107 cmd.CommandText = "INSERT INTO #text1(ID,Val1) VALUES (@ID,@Val1)";
108 prm = new SqlParameter ();
109 prm.ParameterName = "@ID";
111 cmd.Parameters.Add (prm);
113 prm = new SqlParameter ();
114 prm.ParameterName = "@Val1";
115 prm.Value = longstring;
116 prm.SqlDbType = SqlDbType.VarChar;
117 cmd.Parameters.Add (prm);
118 cmd.ExecuteNonQuery ();
119 cmd = new SqlCommand ("select datalength(Val1) from #text1", conn);
120 Assert.AreEqual (20480 * 2, cmd.ExecuteScalar (), "#3");
121 cmd = new SqlCommand ("drop table #text1", conn);
122 cmd.ExecuteNonQuery ();
126 [Test] // bug #382635
127 public void ParameterSize_compatibility_Test ()
129 string longstring = "abcdefghijklmnopqrstuvwxyz";
131 cmd = new SqlCommand ("create table #bug382635 (description varchar(20))", conn);
132 cmd.ExecuteNonQuery ();
135 "CREATE PROCEDURE #sp_bug382635 (@Desc varchar(20)) "
136 + "AS " + Environment.NewLine
137 + "BEGIN" + Environment.NewLine
138 + "UPDATE #bug382635 SET description = @Desc" + Environment.NewLine
140 cmd.CommandType = CommandType.Text;
141 cmd.ExecuteNonQuery ();
143 cmd.CommandText = "INSERT INTO #bug382635 " +
144 "(description) VALUES ('Verifies bug #382635')";
145 cmd.ExecuteNonQuery ();
147 cmd.CommandText = "#sp_bug382635";
148 cmd.CommandType = CommandType.StoredProcedure;
150 SqlParameter p1 = new SqlParameter ("@Desc", SqlDbType.NVarChar, 15);
151 p1.Value = longstring;
152 Assert.AreEqual (longstring, p1.Value);
153 cmd.Parameters.Add (p1);
154 cmd.ExecuteNonQuery ();
156 // Test for truncation
157 SqlCommand selectCmd = new SqlCommand ("SELECT DATALENGTH(description), description from #bug382635", conn);
159 rdr = selectCmd.ExecuteReader ();
160 Assert.IsTrue (rdr.Read (), "#A1");
161 Assert.AreEqual (15, rdr.GetValue (0), "#A2");
162 Assert.AreEqual (longstring.Substring (0, 15), rdr.GetValue (1), "#A3");
163 Assert.AreEqual (longstring, p1.Value, "#A4");
166 // Test to ensure truncation is not done in the Value getter/setter
168 p1.Value = longstring.Substring (0, 22);
170 cmd.ExecuteNonQuery ();
172 rdr = selectCmd.ExecuteReader ();
173 Assert.IsTrue (rdr.Read (), "#B1");
174 Assert.AreEqual (14, rdr.GetValue (0), "#B2");
175 Assert.AreEqual (longstring.Substring (0, 14), rdr.GetValue (1), "#B3");
176 Assert.AreEqual (longstring.Substring (0, 22), p1.Value, "#B4");
179 // Size exceeds size of value
181 cmd.ExecuteNonQuery ();
183 rdr = selectCmd.ExecuteReader ();
184 Assert.IsTrue (rdr.Read (), "#C1");
185 Assert.AreEqual (20, rdr.GetValue (0), "#C2");
186 Assert.AreEqual (longstring.Substring (0, 20), rdr.GetValue (1), "#C3");
191 public void ConversionToSqlTypeInvalid ()
193 string insert_data = "insert into datetime_family (id, type_datetime) values (6000, @type_datetime)";
194 string delete_data = "delete from datetime_family where id = 6000";
196 object [] values = new object [] {
204 for (int i = 0; i < values.Length; i++) {
205 object value = values [i];
207 cmd = conn.CreateCommand ();
208 cmd.CommandText = insert_data;
209 SqlParameter param = cmd.Parameters.Add ("@type_datetime", SqlDbType.DateTime);
214 cmd.ExecuteNonQuery ();
215 Assert.Fail ("#1:" + i);
216 } catch (InvalidCastException) {
219 } catch (FormatException) {
220 if (!(value is string))
225 DBHelper.ExecuteNonQuery (conn, delete_data);
229 [Test] // bug #382589
230 public void DecimalMaxAsParamValueTest ()
232 if (ClientVersion == 7)
233 Assert.Ignore ("Maximum precision is 28.");
235 string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,0) OUT)"
236 + "AS " + Environment.NewLine
237 + "BEGIN" + Environment.NewLine
238 + "SET @decmax = 102.34" + Environment.NewLine
241 cmd = new SqlCommand (create_sp, conn);
242 cmd.ExecuteNonQuery ();
244 cmd.CommandText = "[#sp_bug382539]";
245 cmd.CommandType = CommandType.StoredProcedure;
246 SqlParameter pValue = new SqlParameter("@decmax", Decimal.MaxValue);
247 pValue.Direction = ParameterDirection.InputOutput;
248 cmd.Parameters.Add(pValue);
250 Assert.AreEqual (Decimal.MaxValue, pValue.Value, "Parameter initialization value mismatch");
251 cmd.ExecuteNonQuery();
253 Assert.AreEqual (102m, pValue.Value, "Parameter value mismatch");
256 [Test] // bug #382589
257 public void DecimalMinAsParamValueTest ()
259 if (ClientVersion == 7)
260 Assert.Ignore ("Maximum precision is 28.");
262 string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,0) OUT)"
263 + "AS " + Environment.NewLine
264 + "BEGIN" + Environment.NewLine
265 + "SET @decmax = 102.34" + Environment.NewLine
268 cmd = new SqlCommand (create_sp, conn);
269 cmd.ExecuteNonQuery ();
271 cmd.CommandText = "[#sp_bug382539]";
272 cmd.CommandType = CommandType.StoredProcedure;
273 SqlParameter pValue = new SqlParameter("@decmax", Decimal.MinValue);
274 pValue.Direction = ParameterDirection.InputOutput;
275 cmd.Parameters.Add(pValue);
277 Assert.AreEqual (Decimal.MinValue, pValue.Value, "Parameter initialization value mismatch");
278 cmd.ExecuteNonQuery();
280 Assert.AreEqual (102m, pValue.Value, "Parameter value mismatch");
283 [Test] // bug #382589
284 public void DecimalMaxAsParamValueExceptionTest ()
286 if (ClientVersion == 7)
287 Assert.Ignore ("Maximum precision is 28.");
289 string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,10) OUT)"
290 + "AS " + Environment.NewLine
291 + "BEGIN" + Environment.NewLine
292 + "SET @decmax = 102.36" + Environment.NewLine
295 cmd = new SqlCommand (create_sp, conn);
296 cmd.ExecuteNonQuery ();
298 cmd.CommandText = "[#sp_bug382539]";
299 cmd.CommandType = CommandType.StoredProcedure;
300 SqlParameter pValue = new SqlParameter("@decmax", Decimal.MaxValue);
301 pValue.Direction = ParameterDirection.InputOutput;
302 cmd.Parameters.Add(pValue);
305 cmd.ExecuteNonQuery ();
307 } catch (SqlException ex) {
308 // Error converting data type numeric to decimal
309 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
310 Assert.AreEqual ((byte) 16, ex.Class, "#3");
311 Assert.IsNull (ex.InnerException, "#4");
312 Assert.IsNotNull (ex.Message, "#5");
313 Assert.AreEqual (8114, ex.Number, "#6");
314 Assert.AreEqual ((byte) 5, ex.State, "#7");
318 [Test] // bug# 382589
319 public void DecimalMinAsParamValueExceptionTest ()
321 if (ClientVersion == 7)
322 Assert.Ignore ("Maximum precision is 28.");
324 string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,10) OUT)"
325 + "AS " + Environment.NewLine
326 + "BEGIN" + Environment.NewLine
327 + "SET @decmax = 102.36" + Environment.NewLine
330 cmd = new SqlCommand (create_sp, conn);
331 cmd.ExecuteNonQuery ();
333 cmd.CommandText = "[#sp_bug382539]";
334 cmd.CommandType = CommandType.StoredProcedure;
335 SqlParameter pValue = new SqlParameter("@decmax", Decimal.MinValue);
336 pValue.Direction = ParameterDirection.InputOutput;
337 cmd.Parameters.Add(pValue);
339 cmd.ExecuteNonQuery ();
341 } catch (SqlException ex) {
342 // Error converting data type numeric to decimal
343 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
344 Assert.AreEqual ((byte) 16, ex.Class, "#3");
345 Assert.IsNull (ex.InnerException, "#4");
346 Assert.IsNotNull (ex.Message, "#5");
347 Assert.AreEqual (8114, ex.Number, "#6");
348 Assert.AreEqual ((byte) 5, ex.State, "#7");
352 [Test] // bug #526794
353 public void ZeroLengthString ()
355 cmd = new SqlCommand ("create table #bug526794 (name varchar(20) NULL)", conn);
356 cmd.ExecuteNonQuery ();
360 param = new SqlParameter ("@name", SqlDbType.VarChar);
361 param.Value = string.Empty;
363 cmd = new SqlCommand ("insert into #bug526794 values (@name)", conn);
364 cmd.Parameters.Add (param);
365 cmd.ExecuteNonQuery ();
367 cmd = new SqlCommand ("select * from #bug526794", conn);
368 rdr = cmd.ExecuteReader ();
369 Assert.IsTrue (rdr.Read (), "#A1");
370 Assert.AreEqual (string.Empty, rdr.GetValue (0), "#A2");
373 param = new SqlParameter ("@name", SqlDbType.Int);
374 param.Value = string.Empty;
376 cmd = new SqlCommand ("insert into #bug526794 values (@name)", conn);
377 cmd.Parameters.Add (param);
380 cmd.ExecuteNonQuery ();
382 } catch (FormatException ex) {
384 // Failed to convert parameter value from a String to a Int32
385 Assert.AreEqual (typeof (FormatException), ex.GetType (), "#B2");
386 Assert.IsNotNull (ex.Message, "#B3");
387 Assert.IsTrue (ex.Message.IndexOf (typeof (string).Name) != -1, "#B4");
388 Assert.IsTrue (ex.Message.IndexOf (typeof (int).Name) != -1, "#B5");
390 // Input string was not in a correct format
391 Exception inner = ex.InnerException;
392 Assert.IsNotNull (inner, "#B6");
393 Assert.AreEqual (typeof (FormatException), inner.GetType (), "#B7");
394 Assert.IsNull (inner.InnerException, "#B8");
395 Assert.IsNotNull (inner.Message, "#B9");
397 // Input string was not in a correct format
398 Assert.AreEqual (typeof (FormatException), ex.GetType (), "#B2");
399 Assert.IsNull (ex.InnerException, "#B3");
400 Assert.IsNotNull (ex.Message, "#B4");
407 return (engine.ClientVersion);