mcs/class/System.Data/Test/SqlTest.cs

   1 /* SqlTest.cs - based on PostgresTest.cs
   2  *
   3  * Copyright (C) 2002 Gonzalo Paniagua Javier
   4  * Copyright (C) 2002 Daniel Morgan
   5  * Copyright (C) 2002 Tim Coleman
   6  *
   7  * ORIGINAL AUTHOR:
   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>
  13  *
  14  * Permission was given from the original author, Gonzalo Paniagua Javier,
  15  * to port and include his original work in Mono.
  16  *
  17  * The original work falls under the LGPL, but the port to C# falls
  18  * under the X11 license.
  19  *
  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.
  24  *
  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.
  29  *
  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.
  34  */
  35
  36 using System;
  37 using System.Data;
  38 using System.Data.SqlClient;
  39 using System.Text;
  40
  41 namespace Test.Mono.Data.SqlClient {
  42
  43         class SqlTest {
  44
  45                 // execute SQL CREATE TABLE Command using ExecuteNonQuery()
  46                 static void CreateTable (IDbConnection cnc) {
  47
  48                         IDbCommand createCommand = cnc.CreateCommand();
  49
  50                         createCommand.CommandText =
  51                                 "create table mono_sql_test (" +
  52                                 "bit_value bit, " +
  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, " +
  59                                 "int_value int, " +
  60                                 "money_value money, " +
  61                                 "nchar_value nchar(50), " +
  62                                 "ntext_value ntext, " +
  63                                 "nvarchar_value nvarchar(20), " +
  64                                 "real_value real, " +
  65                                 "smalldatetime_value smalldatetime, " +
  66                                 "smallint_value smallint, " +
  67                                 "smallmoney_value smallmoney, " +
  68                                 "text_value text, " +
  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) " +
  95                                 ")";
  96
  97                         createCommand.ExecuteNonQuery ();
  98                 }
  99
 100                 // execute SQL DROP TABLE Command using ExecuteNonQuery
 101                 static void DropTable (IDbConnection cnc) {
 102
 103                         IDbCommand dropCommand = cnc.CreateCommand ();
 104
 105                         dropCommand.CommandText =
 106                                 "drop table mono_sql_test";
 107
 108                         dropCommand.ExecuteNonQuery ();
 109                 }
 110
 111                 // execute stored procedure using ExecuteScalar()
 112                 static object CallStoredProcedure (IDbConnection cnc) {
 113
 114                         IDbCommand callStoredProcCommand = cnc.CreateCommand ();
 115                         object data;
 116
 117                         callStoredProcCommand.CommandType =
 118                                 CommandType.StoredProcedure;
 119                         callStoredProcCommand.CommandText =
 120                                 "sp_server_info";
 121
 122                         data = callStoredProcCommand.ExecuteScalar ();
 123
 124                         return data;
 125                 }
 126
 127                 // execute SQL INSERT Command using ExecuteNonQuery()
 128                 static void InsertData (IDbConnection cnc) {
 129
 130                         IDbCommand insertCommand = cnc.CreateCommand();
 131
 132                         insertCommand.CommandText =
 133                                 "insert into mono_sql_test (" +
 134                                 "bit_value, " +
 135                                 "binary_value, " +
 136                                 "char_value, " +
 137                                 "datetime_value, " +
 138                                 "decimal_value, " +
 139                                 "float_value, " +
 140                                 "image_value, " +
 141                                 "int_value, " +
 142                                 "money_value, " +
 143                                 "nchar_value, " +
 144                                 "ntext_value, " +
 145                                 "nvarchar_value, " +
 146                                 "real_value, " +
 147                                 "smalldatetime_value, " +
 148                                 "smallint_value, " +
 149                                 "smallmoney_value, " +
 150                                 "text_value, " +
 151                                 "tinyint_value, " +
 152                                 "uniqueidentifier_value, " +
 153                                 "varbinary_value, " +
 154                                 "varchar_value " +
 155                                 ") values (" +
 156                                 "@p1, " +
 157                                 "@p2, " +
 158                                 "@p3, " +
 159                                 "@p4, " +
 160                                 "@p5, " +
 161                                 "@p6, " +
 162                                 "@p7, " +
 163                                 "@p8, " +
 164                                 "@p9, " +
 165                                 "@p10, " +
 166                                 "@p11, " +
 167                                 "@p12, " +
 168                                 "@p13, " +
 169                                 "@p14, " +
 170                                 "@p15, " +
 171                                 "@p16, " +
 172                                 "@p17, " +
 173                                 "@p18, " +
 174                                 "@p19, " +
 175                                 "@p20, " +
 176                                 "@p21 " +
 177                                 ")";
 178
 179                         SqlParameterCollection parameters = ((SqlCommand) insertCommand).Parameters;
 180
 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);
 202
 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!
 207
 208                         parameters ["@p5"].Value = 123456789012.345;
 209                         parameters ["@p5"].Precision = 15;
 210                         parameters ["@p5"].Scale = 3;
 211
 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";
 228
 229                         insertCommand.ExecuteNonQuery ();
 230                 }
 231
 232                 // execute SQL INSERT Command using ExecuteNonQuery()
 233                 static void InsertEdgeCaseData (IDbConnection cnc) {
 234
 235                         IDbCommand insertCommand = cnc.CreateCommand();
 236
 237                         insertCommand.CommandText =
 238                                 "insert into mono_sql_test (" +
 239                                 "varbinary_value " +
 240                                 ") values (" +
 241                                 "@p20 " +
 242                                 ")";
 243
 244                         SqlParameterCollection parameters = ((SqlCommand) insertCommand).Parameters;
 245
 246                         parameters.Add ("@p20", SqlDbType.VarBinary, 8);
 247
 248                         parameters ["@p20"].Value = new byte[0] {};
 249
 250                         insertCommand.ExecuteNonQuery ();
 251                 }
 252
 253                 // execute a SQL SELECT Query using ExecuteReader() to retrieve
 254                 // a IDataReader so we retrieve data
 255                 static IDataReader SelectData (IDbConnection cnc) {
 256
 257                         IDbCommand selectCommand = cnc.CreateCommand();
 258                         IDataReader reader;
 259
 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
 263                         /*
 264                         selectCommand.CommandText =
 265                                 "select * " +
 266                                 "from mono_postgres_test";
 267                         */
 268
 269                         selectCommand.CommandText =
 270                                 "select " +
 271                                 "bit_value, " +
 272                                 "binary_value, " +
 273                                 "char_value, " +
 274                                 "datetime_value, " +
 275                                 "decimal_value, " +
 276                                 "float_value, " +
 277                                 "image_value, " +
 278                                 "int_value, " +
 279                                 "money_value, " +
 280                                 "nchar_value, " +
 281                                 "ntext_value, " +
 282                                 "nvarchar_value, " +
 283                                 "real_value, " +
 284                                 "smalldatetime_value, " +
 285                                 "smallint_value, " +
 286                                 "smallmoney_value, " +
 287                                 "text_value, " +
 288                                 "timestamp_value, " +
 289                                 "tinyint_value, " +
 290                                 "uniqueidentifier_value, "  +
 291                                 "varbinary_value, " +
 292                                 "varchar_value, " +
 293                                 "null_bit_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, " +
 300                                 "null_int_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";
 315
 316
 317                         reader = selectCommand.ExecuteReader ();
 318
 319                         return reader;
 320                 }
 321
 322                 // Tests a SQL Command (INSERT, UPDATE, DELETE)
 323                 // executed via ExecuteReader
 324                 static IDataReader SelectDataUsingInsertCommand (IDbConnection cnc) {
 325
 326                         IDbCommand selectCommand = cnc.CreateCommand();
 327                         IDataReader reader;
 328
 329                         // This is a SQL INSERT Command, not a Query
 330                         selectCommand.CommandText =
 331                                 "insert into mono_sql_test (" +
 332                                 "bit_value, " +
 333                                 "binary_value, " +
 334                                 "char_value, " +
 335                                 "datetime_value, " +
 336                                 "decimal_value, " +
 337                                 "float_value, " +
 338                                 "image_value, " +
 339                                 "int_value, " +
 340                                 "money_value, " +
 341                                 "nchar_value, " +
 342                                 "ntext_value, " +
 343                                 "nvarchar_value, " +
 344                                 "real_value, " +
 345                                 "smalldatetime_value, " +
 346                                 "smallint_value, " +
 347                                 "smallmoney_value, " +
 348                                 "text_value, " +
 349                                 "tinyint_value, " +
 350                                 "uniqueidentifier_value, "  +
 351                                 "varbinary_value, " +
 352                                 "varchar_value " +
 353                                 ") values (" +
 354                                 "@p1, " +
 355                                 "@p2, " +
 356                                 "@p3, " +
 357                                 "@p4, " +
 358                                 "@p5, " +
 359                                 "@p6, " +
 360                                 "@p7, " +
 361                                 "@p8, " +
 362                                 "@p9, " +
 363                                 "@p10, " +
 364                                 "@p11, " +
 365                                 "@p12, " +
 366                                 "@p13, " +
 367                                 "@p14, " +
 368                                 "@p15, " +
 369                                 "@p16, " +
 370                                 "@p17, " +
 371                                 "@p18, " +
 372                                 "@p19, " +
 373                                 "@p20, " +
 374                                 "@p21 " +
 375                                 ")";
 376
 377                         SqlParameterCollection parameters = ((SqlCommand) selectCommand).Parameters;
 378
 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);
 400
 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;
 405
 406                         parameters ["@p5"].Value = 123456789012.345;
 407                         parameters ["@p5"].Precision = 15;
 408                         parameters ["@p5"].Scale = 3;
 409
 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";
 426
 427                         reader = selectCommand.ExecuteReader ();
 428
 429                         return reader;
 430                 }
 431
 432                 // Tests a SQL Command not (INSERT, UPDATE, DELETE)
 433                 // executed via ExecuteReader
 434                 static IDataReader SelectDataUsingCommand (IDbConnection cnc) {
 435
 436                         IDbCommand selectCommand = cnc.CreateCommand();
 437                         IDataReader reader;
 438
 439                         // This is a SQL Command, not a Query
 440                         selectCommand.CommandText =
 441                                 "SET FMTONLY OFF";
 442
 443                         reader = selectCommand.ExecuteReader ();
 444
 445                         return reader;
 446                 }
 447
 448
 449                 // execute an SQL UPDATE Command using ExecuteNonQuery()
 450                 static void UpdateData (IDbConnection cnc) {
 451
 452                         IDbCommand updateCommand = cnc.CreateCommand();
 453
 454                         updateCommand.CommandText =
 455                                 "update mono_sql_test " +
 456                                 "set " +
 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";
 471
 472                         SqlParameterCollection parameters = ((SqlCommand) updateCommand).Parameters;
 473
 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);
 488
 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;
 503
 504                         updateCommand.ExecuteNonQuery ();
 505                 }
 506
 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) {
 510
 511                         IDbCommand selectCommand = cnc.CreateCommand();
 512                         object data;
 513
 514                         Console.WriteLine("Aggregate: " + agg);
 515
 516                         selectCommand.CommandType = CommandType.Text;
 517                         selectCommand.CommandText =
 518                                 "select " + agg +
 519                                 "from mono_sql_test";
 520
 521                         data = selectCommand.ExecuteScalar ();
 522
 523                         Console.WriteLine("Agg Result: " + data);
 524
 525                         return data;
 526                 }
 527
 528                 // used internally by ReadData() to read each result set
 529                 static void ReadResult(IDataReader rdr, DataTable dt) {
 530
 531                         // number of columns in the table
 532                         Console.WriteLine("   Total Columns: " +
 533                                 dt.Rows.Count);
 534
 535                         // display the schema
 536                         foreach (DataRow schemaRow in dt.Rows) {
 537                                 foreach (DataColumn schemaCol in dt.Columns)
 538                                         Console.WriteLine(schemaCol.ColumnName +
 539                                                 " = " +
 540                                                 schemaRow[schemaCol]);
 541                                 Console.WriteLine();
 542                         }
 543
 544                         int nRows = 0;
 545                         int c = 0;
 546                         string output, metadataValue, dataValue;
 547                         // Read and display the rows
 548                         Console.WriteLine("Gonna do a Read() now...");
 549                         while(rdr.Read()) {
 550                                 Console.WriteLine("   Row " + nRows + ": ");
 551
 552                                 for(c = 0; c < rdr.FieldCount; c++) {
 553                                         // column meta data
 554                                         DataRow dr = dt.Rows[c];
 555                                         metadataValue =
 556                                                 "    Col " +
 557                                                 c + ": " +
 558                                                 dr["ColumnName"];
 559
 560                                         // column data
 561                                         if (rdr.IsDBNull(c) == true)
 562                                                 dataValue = " is NULL";
 563                                         else if ((Type) dr["DataType"] == typeof (byte[]))
 564                                                 dataValue =
 565                                                         ": 0x" +
 566                                                         BitConverter.ToString ((byte[]) rdr.GetValue (c)).Replace ("-", "").ToLower ();
 567                                         else
 568                                                 dataValue =
 569                                                         ": " +
 570                                                         rdr.GetValue(c);
 571
 572                                         // display column meta data and data
 573                                         output = metadataValue + dataValue;
 574                                         Console.WriteLine(output);
 575                                 }
 576                                 nRows++;
 577                         }
 578                         Console.WriteLine("   Total Rows Retrieved: " +
 579                                 nRows);
 580                 }
 581
 582                 // Used to read data from IDataReader after calling IDbCommand:ExecuteReader()
 583                 static void ReadData(IDataReader rdr) {
 584
 585                         int results = 0;
 586                         if(rdr == null) {
 587
 588                                 Console.WriteLine("IDataReader has a Null Reference.");
 589                         }
 590                         else {
 591                                 do {
 592                                         DataTable dt = rdr.GetSchemaTable();
 593                                         if(rdr.RecordsAffected != -1) {
 594                                                 // Results for
 595                                                 // SQL INSERT, UPDATE, DELETE Commands
 596                                                 // have RecordsAffected >= 0
 597                                                 Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE).  Records Affected: " + rdr.RecordsAffected);
 598                                         }
 599                                         else if(dt == null)
 600                                                 // Results for
 601                                                 // SQL Commands not INSERT, UPDATE, nor DELETE
 602                                                 // have RecordsAffected == -1
 603                                                 // and GetSchemaTable() returns a null reference
 604                                                 Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE).   Records Affected: " + rdr.RecordsAffected);
 605                                         else {
 606                                                 // Results for
 607                                                 // SQL SELECT Queries
 608                                                 // have RecordsAffected = -1
 609                                                 // and GetSchemaTable() returns a reference to a DataTable
 610                                                 Console.WriteLine("Result is from a SELECT SQL Query.  Records Affected: " + rdr.RecordsAffected);
 611
 612                                                 results++;
 613                                                 Console.WriteLine("Result Set " + results + "...");
 614
 615                                                 ReadResult(rdr, dt);
 616                                         }
 617
 618                                 } while(rdr.NextResult());
 619                                 Console.WriteLine("Total Result sets: " + results);
 620
 621                                 rdr.Close();
 622                         }
 623                 }
 624
 625                 /* Sql provider tests */
 626                 static void DoSqlTest (IDbConnection cnc) {
 627
 628                         IDataReader reader;
 629                         Object oDataValue;
 630
 631                         Console.WriteLine ("\tSql provider specific tests...\n");
 632
 633                         /* Drops the mono_sql_test table. */
 634                         Console.WriteLine ("\t\tDrop table: ");
 635                         try {
 636                                 DropTable (cnc);
 637                                 Console.WriteLine ("OK");
 638                         }
 639                         catch (SqlException e) {
 640                                 Console.WriteLine("Error (don't worry about this one)" + e);
 641                         }
 642
 643                         try {
 644                                 /* Creates a table with all supported data types */
 645                                 Console.WriteLine ("\t\tCreate table with all supported types: ");
 646                                 CreateTable (cnc);
 647                                 Console.WriteLine ("OK");
 648
 649                                 /* Inserts values */
 650                                 Console.WriteLine ("\t\tInsert values for all known types: ");
 651                                 InsertData (cnc);
 652                                 Console.WriteLine ("OK");
 653
 654                                 /* Update values */
 655                                 Console.WriteLine ("\t\tUpdate values: ");
 656                                 UpdateData (cnc);
 657                                 Console.WriteLine ("OK");
 658
 659                                 /* Inserts values */
 660                                 Console.WriteLine ("\t\tInsert values for all known types: ");
 661                                 InsertData (cnc);
 662                                 Console.WriteLine ("OK");
 663
 664                                 /* Inserts edge case values */
 665                                 Console.WriteLine ("\t\tInsert values that require special coding: ");
 666                                 InsertEdgeCaseData (cnc);
 667                                 Console.WriteLine ("OK");
 668
 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
 675                                 //        properly.
 676                                 //SelectAggregate (cnc, "avg(int_value)");
 677                                 SelectAggregate (cnc, "min(varchar_value)");
 678                                 SelectAggregate (cnc, "max(int_value)");
 679                                 SelectAggregate (cnc, "sum(int_value)");
 680
 681                                 /* Select values */
 682                                 Console.WriteLine ("\t\tSelect values from the database: ");
 683                                 reader = SelectData (cnc);
 684                                 ReadData(reader);
 685
 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);
 690                                 ReadData(reader);
 691
 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);
 696                                 ReadData(reader);
 697
 698                                 // Call a Stored Procedure named Version()
 699                                 Console.WriteLine("\t\tCalling stored procedure sp_server_info()");
 700                                 object obj = CallStoredProcedure(cnc);
 701                                 Console.WriteLine("Result: " + obj);
 702
 703                                 Console.WriteLine("Database Server Version: " +
 704                                         ((SqlConnection)cnc).ServerVersion);
 705
 706                                 /* Clean up */
 707                                 Console.WriteLine ("Clean up...");
 708                                 Console.WriteLine ("\t\tDrop table...");
 709                                 //DropTable (cnc);
 710                                 Console.WriteLine("OK");
 711                         }
 712                         catch(Exception e) {
 713                                 Console.WriteLine("Exception caught: " + e);
 714                         }
 715                 }
 716
 717                 [STAThread]
 718                 static void Main(string[] args) {
 719                         string connectionString = "";
 720
 721                         if(args.Length == 3 || args.Length == 4) {
 722                                 if(args.Length == 3) {
 723                                         connectionString = String.Format(
 724                                                 "Server={0};" +
 725                                                 "Database={1};" +
 726                                                 "User ID={2};",
 727                                                 args[0], args[1], args[2]);
 728                                 }
 729                                 else if(args.Length == 4) {
 730                                         connectionString = String.Format(
 731                                                 "Server={0};" +
 732                                                 "Database={1};" +
 733                                                 "User ID={2};" +
 734                                                 "Password={3}",
 735                                                 args[0], args[1], args[2], args[3]);
 736                                 }
 737                         }
 738                         else {
 739                                 Console.WriteLine("Usage: mono SqlTest.exe sql_server database user_id password");
 740                                 return;
 741                         }
 742
 743                         SqlConnection cnc = new SqlConnection ();
 744                         cnc.ConnectionString =  connectionString;
 745
 746                         cnc.Open();
 747                         DoSqlTest(cnc);
 748                         cnc.Close();
 749                 }
 750         }
 751 }