1 /* SqlTest.cs - based on PostgresTest.cs
3 * Copyright (C) 2002 Gonzalo Paniagua Javier
4 * Copyright (C) 2002 Daniel Morgan
5 * Copyright (C) 2002 Tim Coleman
8 * Gonzalo Paniagua Javier <gonzalo@gnome-db.org>
9 * PORTING FROM C TO C# AUTHOR:
10 * Daniel Morgan <danmorg@sc.rr.com>
11 * PORTING TO SQL SERVER AUTHOR:
12 * Tim Coleman <tim@timcoleman.com>
14 * Permission was given from the original author, Gonzalo Paniagua Javier,
15 * to port and include his original work in Mono.
17 * The original work falls under the LGPL, but the port to C# falls
18 * under the X11 license.
20 * This program is free software; you can redistribute it and/or
21 * modify it under the terms of the GNU General Public License as
22 * published by the Free Software Foundation; either version 2 of the
23 * License, or (at your option) any later version.
25 * This program is distributed in the hope that it will be useful,
26 * but WITHOUT ANY WARRANTY; without even the implied warranty of
27 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
28 * Library General Public License for more details.
30 * You should have received a copy of the GNU General Public
31 * License along with this program; see the file COPYING. If not,
32 * write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
33 * Boston, MA 02111-1307, USA.
38 using System.Data.SqlClient;
41 namespace Test.Mono.Data.SqlClient {
45 // execute SQL CREATE TABLE Command using ExecuteNonQuery()
46 static void CreateTable (IDbConnection cnc) {
48 IDbCommand createCommand = cnc.CreateCommand();
50 createCommand.CommandText =
51 "create table mono_sql_test (" +
53 "binary_value binary (8), " +
54 "char_value char(50), " +
55 "datetime_value datetime, " +
56 "decimal_value decimal(15, 3), " +
57 "float_value float, " +
58 "image_value image, " +
60 "money_value money, " +
61 "nchar_value nchar(50), " +
62 "ntext_value ntext, " +
63 "nvarchar_value nvarchar(20), " +
65 "smalldatetime_value smalldatetime, " +
66 "smallint_value smallint, " +
67 "smallmoney_value smallmoney, " +
69 "timestamp_value timestamp, " +
70 "tinyint_value tinyint, " +
71 "uniqueidentifier_value uniqueidentifier, " +
72 "varbinary_value varbinary (8), " +
73 "varchar_value varchar(20), " +
74 "null_bit_value bit, " +
75 "null_binary_value binary (8), " +
76 "null_char_value char(50), " +
77 "null_datetime_value datetime, " +
78 "null_decimal_value decimal(15, 3), " +
79 "null_float_value float, " +
80 "null_image_value image, " +
81 "null_int_value int, " +
82 "null_money_value int, " +
83 "null_nchar_value nchar(50), " +
84 "null_ntext_value ntext, " +
85 "null_nvarchar_value nvarchar(20), " +
86 "null_real_value real, " +
87 "null_smalldatetime_value smalldatetime, " +
88 "null_smallint_value smallint, " +
89 "null_smallmoney_value int, " +
90 "null_text_value text, " +
91 "null_tinyint_value tinyint, " +
92 "null_uniqueidentifier_value uniqueidentifier, " +
93 "null_varbinary_value varbinary (8), " +
94 "null_varchar_value varchar(20) " +
97 createCommand.ExecuteNonQuery ();
100 // execute SQL DROP TABLE Command using ExecuteNonQuery
101 static void DropTable (IDbConnection cnc) {
103 IDbCommand dropCommand = cnc.CreateCommand ();
105 dropCommand.CommandText =
106 "drop table mono_sql_test";
108 dropCommand.ExecuteNonQuery ();
111 // execute stored procedure using ExecuteScalar()
112 static object CallStoredProcedure (IDbConnection cnc) {
114 IDbCommand callStoredProcCommand = cnc.CreateCommand ();
117 callStoredProcCommand.CommandType =
118 CommandType.StoredProcedure;
119 callStoredProcCommand.CommandText =
122 data = callStoredProcCommand.ExecuteScalar ();
127 // execute SQL INSERT Command using ExecuteNonQuery()
128 static void InsertData (IDbConnection cnc) {
130 IDbCommand insertCommand = cnc.CreateCommand();
132 insertCommand.CommandText =
133 "insert into mono_sql_test (" +
147 "smalldatetime_value, " +
149 "smallmoney_value, " +
152 "uniqueidentifier_value, " +
153 "varbinary_value, " +
179 SqlParameterCollection parameters = ((SqlCommand) insertCommand).Parameters;
181 parameters.Add ("@p1", SqlDbType.Bit);
182 parameters.Add ("@p2", SqlDbType.Binary, 8);
183 parameters.Add ("@p3", SqlDbType.Char, 14);
184 parameters.Add ("@p4", SqlDbType.DateTime);
185 parameters.Add ("@p5", SqlDbType.Decimal);
186 parameters.Add ("@p6", SqlDbType.Float);
187 parameters.Add ("@p7", SqlDbType.Image);
188 parameters.Add ("@p8", SqlDbType.Int);
189 parameters.Add ("@p9", SqlDbType.Money);
190 parameters.Add ("@p10", SqlDbType.NChar, 16);
191 parameters.Add ("@p11", SqlDbType.NText);
192 parameters.Add ("@p12", SqlDbType.NVarChar, 19);
193 parameters.Add ("@p13", SqlDbType.Real);
194 parameters.Add ("@p14", SqlDbType.SmallDateTime);
195 parameters.Add ("@p15", SqlDbType.SmallInt);
196 parameters.Add ("@p16", SqlDbType.SmallMoney);
197 parameters.Add ("@p17", SqlDbType.Text);
198 parameters.Add ("@p18", SqlDbType.TinyInt);
199 parameters.Add ("@p19", SqlDbType.UniqueIdentifier);
200 parameters.Add ("@p20", SqlDbType.VarBinary, 8);
201 parameters.Add ("@p21", SqlDbType.VarChar, 17);
203 parameters ["@p1"].Value = true;
204 parameters ["@p2"].Value = new byte[2] {0x12,0x34};
205 parameters ["@p3"].Value = "This is a char";
206 parameters ["@p4"].Value = new DateTime (1959, 7, 17); // My mom's birthday!
208 parameters ["@p5"].Value = 123456789012.345;
209 parameters ["@p5"].Precision = 15;
210 parameters ["@p5"].Scale = 3;
212 parameters ["@p6"].Value = 3.1415926969696;
213 parameters ["@p7"].Value = new byte[4] {0xde, 0xad, 0xbe, 0xef};
214 parameters ["@p8"].Value = 1048000;
215 parameters ["@p9"].Value = 31337.456;
216 parameters ["@p10"].Value = "This is an nchar";
217 parameters ["@p11"].Value = "This is an ntext";
218 parameters ["@p12"].Value = "This is an nvarchar";
219 parameters ["@p13"].Value = 3.141592;
220 parameters ["@p14"].Value = new DateTime (1976, 10, 31); // My birthday!
221 parameters ["@p15"].Value = -22;
222 parameters ["@p16"].Value = 31337.456;
223 parameters ["@p17"].Value = "This is a text";
224 parameters ["@p18"].Value = 15;
225 parameters ["@p19"].Value = Guid.NewGuid ();
226 parameters ["@p20"].Value = new byte[2] {0x56,0x78};
227 parameters ["@p21"].Value = "This is a varchar";
229 insertCommand.ExecuteNonQuery ();
232 // execute SQL INSERT Command using ExecuteNonQuery()
233 static void InsertEdgeCaseData (IDbConnection cnc) {
235 IDbCommand insertCommand = cnc.CreateCommand();
237 insertCommand.CommandText =
238 "insert into mono_sql_test (" +
244 SqlParameterCollection parameters = ((SqlCommand) insertCommand).Parameters;
246 parameters.Add ("@p20", SqlDbType.VarBinary, 8);
248 parameters ["@p20"].Value = new byte[0] {};
250 insertCommand.ExecuteNonQuery ();
253 // execute a SQL SELECT Query using ExecuteReader() to retrieve
254 // a IDataReader so we retrieve data
255 static IDataReader SelectData (IDbConnection cnc) {
257 IDbCommand selectCommand = cnc.CreateCommand();
260 // FIXME: System.Data classes need to handle NULLs
261 // this would be done by System.DBNull ?
262 // FIXME: System.Data needs to handle more data types
264 selectCommand.CommandText =
266 "from mono_postgres_test";
269 selectCommand.CommandText =
284 "smalldatetime_value, " +
286 "smallmoney_value, " +
288 "timestamp_value, " +
290 "uniqueidentifier_value, " +
291 "varbinary_value, " +
294 "null_binary_value, " +
295 "null_char_value, " +
296 "null_datetime_value, " +
297 "null_decimal_value, " +
298 "null_float_value, " +
299 "null_image_value, " +
301 "null_money_value, " +
302 "null_nchar_value, " +
303 "null_ntext_value, " +
304 "null_nvarchar_value, " +
305 "null_real_value, " +
306 "null_smalldatetime_value, " +
307 "null_smallint_value, " +
308 "null_smallmoney_value, " +
309 "null_text_value, " +
310 "null_tinyint_value, " +
311 "null_uniqueidentifier_value, " +
312 "null_varbinary_value, " +
313 "null_varchar_value " +
314 "from mono_sql_test";
317 reader = selectCommand.ExecuteReader ();
322 // Tests a SQL Command (INSERT, UPDATE, DELETE)
323 // executed via ExecuteReader
324 static IDataReader SelectDataUsingInsertCommand (IDbConnection cnc) {
326 IDbCommand selectCommand = cnc.CreateCommand();
329 // This is a SQL INSERT Command, not a Query
330 selectCommand.CommandText =
331 "insert into mono_sql_test (" +
345 "smalldatetime_value, " +
347 "smallmoney_value, " +
350 "uniqueidentifier_value, " +
351 "varbinary_value, " +
377 SqlParameterCollection parameters = ((SqlCommand) selectCommand).Parameters;
379 parameters.Add ("@p1", SqlDbType.Bit);
380 parameters.Add ("@p2", SqlDbType.Binary, 8);
381 parameters.Add ("@p3", SqlDbType.Char, 14);
382 parameters.Add ("@p4", SqlDbType.DateTime);
383 parameters.Add ("@p5", SqlDbType.Decimal);
384 parameters.Add ("@p6", SqlDbType.Float);
385 parameters.Add ("@p7", SqlDbType.Image);
386 parameters.Add ("@p8", SqlDbType.Int);
387 parameters.Add ("@p9", SqlDbType.Money);
388 parameters.Add ("@p10", SqlDbType.NChar, 16);
389 parameters.Add ("@p11", SqlDbType.NText);
390 parameters.Add ("@p12", SqlDbType.NVarChar, 19);
391 parameters.Add ("@p13", SqlDbType.Real);
392 parameters.Add ("@p14", SqlDbType.SmallDateTime);
393 parameters.Add ("@p15", SqlDbType.SmallInt);
394 parameters.Add ("@p16", SqlDbType.SmallMoney);
395 parameters.Add ("@p17", SqlDbType.Text);
396 parameters.Add ("@p18", SqlDbType.TinyInt);
397 parameters.Add ("@p19", SqlDbType.UniqueIdentifier);
398 parameters.Add ("@p20", SqlDbType.VarBinary, 8);
399 parameters.Add ("@p21", SqlDbType.VarChar, 17);
401 parameters ["@p1"].Value = true;
402 parameters ["@p2"].Value = new byte[2] {0x9a,0xbc};
403 parameters ["@p3"].Value = "This is a char";
404 parameters ["@p4"].Value = DateTime.Now;
406 parameters ["@p5"].Value = 123456789012.345;
407 parameters ["@p5"].Precision = 15;
408 parameters ["@p5"].Scale = 3;
410 parameters ["@p6"].Value = 3.1415926969696;
411 parameters ["@p7"].Value = new byte[4] {0xfe, 0xeb, 0xda, 0xed};
412 parameters ["@p8"].Value = 1048000;
413 parameters ["@p9"].Value = 31337.456;
414 parameters ["@p10"].Value = "This is an nchar";
415 parameters ["@p11"].Value = "This is an ntext";
416 parameters ["@p12"].Value = "This is an nvarchar";
417 parameters ["@p13"].Value = 3.141592;
418 parameters ["@p14"].Value = new DateTime (1978, 6, 30); // My brother's birthday!
419 parameters ["@p15"].Value = -22;
420 parameters ["@p16"].Value = 31337.456;
421 parameters ["@p17"].Value = "This is a text";
422 parameters ["@p18"].Value = 15;
423 parameters ["@p19"].Value = Guid.NewGuid ();
424 parameters ["@p20"].Value = new byte[2] {0xde, 0xef};
425 parameters ["@p21"].Value = "This is a varchar";
427 reader = selectCommand.ExecuteReader ();
432 // Tests a SQL Command not (INSERT, UPDATE, DELETE)
433 // executed via ExecuteReader
434 static IDataReader SelectDataUsingCommand (IDbConnection cnc) {
436 IDbCommand selectCommand = cnc.CreateCommand();
439 // This is a SQL Command, not a Query
440 selectCommand.CommandText =
443 reader = selectCommand.ExecuteReader ();
449 // execute an SQL UPDATE Command using ExecuteNonQuery()
450 static void UpdateData (IDbConnection cnc) {
452 IDbCommand updateCommand = cnc.CreateCommand();
454 updateCommand.CommandText =
455 "update mono_sql_test " +
457 "bit_value = @p1, " +
458 "tinyint_value = @p2, " +
459 "smallint_value = @p3, " +
460 "int_value = @p4, " +
461 "char_value = @p5, " +
462 "nchar_value = @p6, " +
463 "varchar_value = @p7, " +
464 "nvarchar_value = @p8, " +
465 "text_value = @p9, " +
466 "ntext_value = @p10, " +
467 "uniqueidentifier_value = @p11, " +
468 "binary_value = @p12, " +
469 "varbinary_value = @p13 " +
470 "where smallint_value = @p14";
472 SqlParameterCollection parameters = ((SqlCommand) updateCommand).Parameters;
474 parameters.Add ("@p1", SqlDbType.Bit);
475 parameters.Add ("@p2", SqlDbType.TinyInt);
476 parameters.Add ("@p3", SqlDbType.SmallInt);
477 parameters.Add ("@p4", SqlDbType.Int);
478 parameters.Add ("@p5", SqlDbType.Char, 10);
479 parameters.Add ("@p6", SqlDbType.NChar, 10);
480 parameters.Add ("@p7", SqlDbType.VarChar, 14);
481 parameters.Add ("@p8", SqlDbType.NVarChar, 14);
482 parameters.Add ("@p9", SqlDbType.Text);
483 parameters.Add ("@p10", SqlDbType.NText);
484 parameters.Add ("@p11", SqlDbType.UniqueIdentifier);
485 parameters.Add ("@p12", SqlDbType.Binary, 8);
486 parameters.Add ("@p13", SqlDbType.VarBinary, 8);
487 parameters.Add ("@p14", SqlDbType.SmallInt);
489 parameters ["@p1"].Value = false;
490 parameters ["@p2"].Value = 2;
491 parameters ["@p3"].Value = 5;
492 parameters ["@p4"].Value = 3;
493 parameters ["@p5"].Value = "Mono.Data!";
494 parameters ["@p6"].Value = "Mono.Data!";
495 parameters ["@p7"].Value = "It was not me!";
496 parameters ["@p8"].Value = "It was not me!";
497 parameters ["@p9"].Value = "We got data!";
498 parameters ["@p10"].Value = "We got data!";
499 parameters ["@p11"].Value = Guid.NewGuid ();
500 parameters ["@p12"].Value = new byte[2] {0x57,0x3e};
501 parameters ["@p13"].Value = new byte[2] {0xa2,0xf7};
502 parameters ["@p14"].Value = -22;
504 updateCommand.ExecuteNonQuery ();
507 // used to do a min(), max(), count(), sum(), or avg()
508 // execute SQL SELECT Query using ExecuteScalar
509 static object SelectAggregate (IDbConnection cnc, String agg) {
511 IDbCommand selectCommand = cnc.CreateCommand();
514 Console.WriteLine("Aggregate: " + agg);
516 selectCommand.CommandType = CommandType.Text;
517 selectCommand.CommandText =
519 "from mono_sql_test";
521 data = selectCommand.ExecuteScalar ();
523 Console.WriteLine("Agg Result: " + data);
528 // used internally by ReadData() to read each result set
529 static void ReadResult(IDataReader rdr, DataTable dt) {
531 // number of columns in the table
\r
532 Console.WriteLine(" Total Columns: " +
\r
535 // display the schema
\r
536 foreach (DataRow schemaRow in dt.Rows) {
\r
537 foreach (DataColumn schemaCol in dt.Columns)
\r
538 Console.WriteLine(schemaCol.ColumnName +
\r
540 schemaRow[schemaCol]);
\r
541 Console.WriteLine();
\r
546 string output, metadataValue, dataValue;
\r
547 // Read and display the rows
\r
548 Console.WriteLine("Gonna do a Read() now...");
\r
549 while(rdr.Read()) {
\r
550 Console.WriteLine(" Row " + nRows + ": ");
\r
552 for(c = 0; c < rdr.FieldCount; c++) {
\r
553 // column meta data
\r
554 DataRow dr = dt.Rows[c];
\r
561 if (rdr.IsDBNull(c) == true)
\r
562 dataValue = " is NULL";
\r
563 else if ((Type) dr["DataType"] == typeof (byte[]))
566 BitConverter.ToString ((byte[]) rdr.GetValue (c)).Replace ("-", "").ToLower ();
572 // display column meta data and data
\r
573 output = metadataValue + dataValue;
\r
574 Console.WriteLine(output);
\r
578 Console.WriteLine(" Total Rows Retrieved: " +
\r
582 // Used to read data from IDataReader after calling IDbCommand:ExecuteReader()
583 static void ReadData(IDataReader rdr) {
588 Console.WriteLine("IDataReader has a Null Reference.");
592 DataTable dt = rdr.GetSchemaTable();
\r
593 if(rdr.RecordsAffected != -1) {
\r
595 // SQL INSERT, UPDATE, DELETE Commands
\r
596 // have RecordsAffected >= 0
\r
597 Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
\r
599 else if(dt == null)
\r
601 // SQL Commands not INSERT, UPDATE, nor DELETE
\r
602 // have RecordsAffected == -1
\r
603 // and GetSchemaTable() returns a null reference
\r
604 Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
\r
607 // SQL SELECT Queries
\r
608 // have RecordsAffected = -1
\r
609 // and GetSchemaTable() returns a reference to a DataTable
\r
610 Console.WriteLine("Result is from a SELECT SQL Query. Records Affected: " + rdr.RecordsAffected);
\r
613 Console.WriteLine("Result Set " + results + "...");
\r
615 ReadResult(rdr, dt);
\r
618 } while(rdr.NextResult());
\r
619 Console.WriteLine("Total Result sets: " + results);
\r
625 /* Sql provider tests */
626 static void DoSqlTest (IDbConnection cnc) {
631 Console.WriteLine ("\tSql provider specific tests...\n");
633 /* Drops the mono_sql_test table. */
634 Console.WriteLine ("\t\tDrop table: ");
637 Console.WriteLine ("OK");
639 catch (SqlException e) {
640 Console.WriteLine("Error (don't worry about this one)" + e);
644 /* Creates a table with all supported data types */
645 Console.WriteLine ("\t\tCreate table with all supported types: ");
647 Console.WriteLine ("OK");
650 Console.WriteLine ("\t\tInsert values for all known types: ");
652 Console.WriteLine ("OK");
655 Console.WriteLine ("\t\tUpdate values: ");
657 Console.WriteLine ("OK");
660 Console.WriteLine ("\t\tInsert values for all known types: ");
662 Console.WriteLine ("OK");
664 /* Inserts edge case values */
665 Console.WriteLine ("\t\tInsert values that require special coding: ");
666 InsertEdgeCaseData (cnc);
667 Console.WriteLine ("OK");
669 /* Select aggregates */
670 SelectAggregate (cnc, "count(*)");
671 // FIXME: still having a problem with avg()
672 // because it returns a decimal.
673 // It may have something to do
674 // with culture not being set
676 //SelectAggregate (cnc, "avg(int_value)");
677 SelectAggregate (cnc, "min(varchar_value)");
678 SelectAggregate (cnc, "max(int_value)");
679 SelectAggregate (cnc, "sum(int_value)");
682 Console.WriteLine ("\t\tSelect values from the database: ");
683 reader = SelectData (cnc);
686 /* SQL Command via ExecuteReader/SqlDataReader */
687 /* Command is not INSERT, UPDATE, or DELETE */
688 Console.WriteLine("\t\tCall ExecuteReader with a SQL Command. (Not INSERT,UPDATE,DELETE).");
689 reader = SelectDataUsingCommand(cnc);
692 /* SQL Command via ExecuteReader/SqlDataReader */
693 /* Command is INSERT, UPDATE, or DELETE */
694 Console.WriteLine("\t\tCall ExecuteReader with a SQL Command. (Is INSERT,UPDATE,DELETE).");
695 reader = SelectDataUsingInsertCommand(cnc);
698 // Call a Stored Procedure named Version()
\r
699 Console.WriteLine("\t\tCalling stored procedure sp_server_info()");
\r
700 object obj = CallStoredProcedure(cnc);
\r
701 Console.WriteLine("Result: " + obj);
\r
703 Console.WriteLine("Database Server Version: " +
\r
704 ((SqlConnection)cnc).ServerVersion);
\r
707 Console.WriteLine ("Clean up...");
708 Console.WriteLine ("\t\tDrop table...");
710 Console.WriteLine("OK");
713 Console.WriteLine("Exception caught: " + e);
718 static void Main(string[] args) {
\r
719 string connectionString = "";
\r
721 if(args.Length == 3 || args.Length == 4) {
\r
722 if(args.Length == 3) {
\r
723 connectionString = String.Format(
727 args[0], args[1], args[2]);
729 else if(args.Length == 4) {
730 connectionString = String.Format(
735 args[0], args[1], args[2], args[3]);
739 Console.WriteLine("Usage: mono SqlTest.exe sql_server database user_id password");
743 SqlConnection cnc = new SqlConnection ();
\r
744 cnc.ConnectionString = connectionString;
\r