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

   1 //\r
   2 // TestSqlConnection.cs - tests connection via ServerName:\r
   3 //   "Server=hostname"\r
   4 //   "Server=hostname\\instance"\r
   5 //   "Server=hostname,port"\r
   6 //\r
   7 // Test Connections for SqlClient, SybaseClient, and TdsClient\r
   8 //\r
   9 // Author: \r
  10 //      Daniel Morgan <danmorg@sc.rr.com>\r
  11 //\r
  12 // Copyright (C) Daniel Morgan, 2003\r
  13 //\r
  14 // To build this test on Linux:\r
  15 // mcs TestSqlConnection.cs -r System.Data.dll \\r
  16 //     -r Mono.Data.SybaseClient.dll -r Mono.Data.TdsClient.dll\r
  17 //\r
  18 // To build this test on Windows via Cygwin:\r
  19 // mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe TestSqlConnection.cs \\r
  20 //      -lib:C:/cygwin/home/MyHome/mono/install/lib -r System.Data.dll \\r
  21 //      -r Mono.Data.SybaseClient.dll -r Mono.Data.TdsClient.dll\r
  22 //\r
  23
  24 //
  25 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  26 //
  27 // Permission is hereby granted, free of charge, to any person obtaining
  28 // a copy of this software and associated documentation files (the
  29 // "Software"), to deal in the Software without restriction, including
  30 // without limitation the rights to use, copy, modify, merge, publish,
  31 // distribute, sublicense, and/or sell copies of the Software, and to
  32 // permit persons to whom the Software is furnished to do so, subject to
  33 // the following conditions:
  34 //
  35 // The above copyright notice and this permission notice shall be
  36 // included in all copies or substantial portions of the Software.
  37 //
  38 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  39 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  40 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  41 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  42 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  43 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  44 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  45 //
  46 \r
  47 //#define IncludeSybaseAndTdsClient\r
  48 \r
  49 using System;\r
  50 using System.Data;\r
  51 using System.Data.SqlClient;\r
  52 #if IncludeSybaseAndTdsClient\r
  53         using Mono.Data.TdsClient;\r
  54         using Mono.Data.SybaseClient;\r
  55 #endif // IncludeSybaseAndTdsClient\r
  56 \r
  57 public class TestSqlConnection \r
  58 {\r
  59         public static void Main(string[] args) \r
  60         {\r
  61                 Console.WriteLine("Start TestSqlConnection.");\r
  62                 if (args.Length != 6 && args.Length != 7) {\r
  63                         Console.WriteLine(\r
  64                                 "\nUsage: mono TestSqlConnection.exe Client Table Column Server Database UserID [Password]\n\n" +\r
  65 #if IncludeSybaseAndTdsClient\r
  66                                 "\tClient is one of the following: SqlClient, TdsClient, or SybaseClient\n" +\r
  67 #else\r
  68                                 "\tClient is: SqlClient.  No support for TdsClient nor SybaseClient\n" +\r
  69 #endif // IncludeSybaseAndTdsClient\r
  70                                 "\tTable is the name of the database table to select from\n" +\r
  71                                 "\tColumn is the name of the column in the Table to select from\n" +\r
  72                                 "\tServer is the SQL Server to connect.  Use one of the following forms:\n" +\r
  73                                 "\t\tHOSTNAME            Ex: MYHOST\n" +\r
  74                                 "\t\tHOSTNAME,port       Ex: MYHOST,1433\n" +\r
  75                                 "\t\tHOSTNAME\\\\instance  Ex: MYHOST\\\\NETSDK  Note: only works with SqlClient\n" +\r
  76                                 "\tDatabase is the name of the database to use\n" +\r
  77                                 "\tUser ID is the user's User ID\n" +\r
  78                                 "\tPassword is the user's Password   Note: if ommitted, a blank password is used\n" +\r
  79                                 "Exampes:\n" +\r
  80                                 "\tEx 1: SqlClient employee lname MYHOST pubs myuserid mypassword\n" +\r
  81                                 "\tEx 3: SqlClient employee lname MYHOST,1443 pubs myuserid mypassword\n" +\r
  82                                 "\tEx 2: SqlClient Products ProductName MYHOST\\\\NETSDK myuserid mypassword\n" +\r
  83                                 "\tEx 4: SqlClient employee lname MYHOST pubs myuserid\n" +\r
  84                                 "\tEx 5: TdsClient sometable somecolumn MYHOST test myuserid mypassword\n" +\r
  85                                 "\tEx 6: SybaseClient sometable somecolumn MYHOST test myuserid mypassword\n");\r
  86 \r
  87                         return;\r
  88                 }\r
  89 \r
  90                 string client = args[0];\r
  91                 string tableName = args[1];\r
  92                 string columnName = args[2];\r
  93                 \r
  94                 string server = args[3];\r
  95                 string database = args[4];\r
  96                 string userid = args[5];\r
  97                 string password = "";\r
  98                 if (args.Length == 7)\r
  99                         password  = args[6];\r
 100                 \r
 101                 string constr;\r
 102                 string sql;\r
 103 \r
 104                 Console.WriteLine("\nClient: " + client);\r
 105                 Console.WriteLine("Table Name: " + tableName);\r
 106                 Console.WriteLine("Column Name: " + columnName);\r
 107                 Console.WriteLine("Server: " + server);\r
 108                 Console.WriteLine("Database: " + database);\r
 109                 Console.WriteLine("User ID: " + userid);\r
 110                 Console.WriteLine("Password: " + password);\r
 111 \r
 112                 sql = "SELECT " + columnName + " FROM " + tableName;\r
 113                 \r
 114                 constr = \r
 115                         "Server=" + server + ";" + \r
 116                         "Database=" + database + ";" +\r
 117                         "User ID=" + userid + ";" +\r
 118                         "Password=" + password + ";";   \r
 119 \r
 120                 Console.WriteLine("\nConnectionString: " + constr);\r
 121                 Console.WriteLine("SQL: " + sql);\r
 122                 \r
 123                 Console.WriteLine("\nCreating Connection...");\r
 124 \r
 125                 IDbConnection con = null;\r
 126                 switch (client.ToUpper()) {\r
 127                 case "SQLCLIENT":\r
 128                         con = new SqlConnection();\r
 129                         break;\r
 130 #if IncludeSybaseAndTdsClient\r
 131                 case "TDSCLIENT":\r
 132                         con = new TdsConnection();\r
 133                         break;\r
 134                 case "SYBASECLIENT":\r
 135                         con = new SybaseConnection();\r
 136                         break;\r
 137                 default:\r
 138                         Console.WriteLine("Invalid client: " + client + "\nUse SqlClient, TdsClient, or SybaseClient");\r
 139                         return;\r
 140 #else\r
 141                 default:\r
 142                         Console.WriteLine("Invalid client: " + client + "\nUse SqlClient.  No support for TdsClient nor SybaseClient.");\r
 143                         return;\r
 144 \r
 145 #endif\r
 146                 }\r
 147                 Console.WriteLine("set connection string...");\r
 148                 con.ConnectionString = constr;\r
 149                 Console.WriteLine("open connection...");\r
 150                 try {\r
 151                         con.Open();\r
 152                 }\r
 153                 catch(SqlException se) {\r
 154                         Console.WriteLine("SqlException caught");\r
 155                         Console.WriteLine("Message: " + se.Message);\r
 156                         Console.WriteLine("Procedure: " + se.Procedure);\r
 157                         Console.WriteLine("Class: " + se.Class);\r
 158                         Console.WriteLine("Number: " + se.Number);\r
 159                         Console.WriteLine("Source: " + se.Source);\r
 160                         Console.WriteLine("State: " + se.State);\r
 161                         Console.WriteLine("Errors:");\r
 162                         foreach(SqlError error in se.Errors) {\r
 163                                 Console.WriteLine("  SqlError:");\r
 164                                 Console.WriteLine("     Message: " + se.Message);\r
 165                                 Console.WriteLine("     Line Number: " + se.LineNumber);\r
 166                                 Console.WriteLine("     Procedure: " + se.Procedure);\r
 167                                 Console.WriteLine("     Class: " + se.Class);\r
 168                                 Console.WriteLine("     Number: " + se.Number);\r
 169                                 Console.WriteLine("     Server: " + se.Server);\r
 170                                 Console.WriteLine("     Source: " + se.Source);\r
 171                                 Console.WriteLine("     State: " + se.State);\r
 172                         }\r
 173                         Console.WriteLine("StackTrace: " + se.StackTrace);\r
 174                         Console.WriteLine("TargetSite: " + se.TargetSite);\r
 175                         Exception ie = se.InnerException;\r
 176                         if(ie != null) {\r
 177                                 Console.WriteLine("InnerException:");\r
 178                                 Console.WriteLine("   Message: " + se.Message);\r
 179                                 Console.WriteLine("   Class: " + se.Class);\r
 180                                 Console.WriteLine("   Number: " + se.Number);\r
 181                                 Console.WriteLine("   Source: " + se.Source);\r
 182                                 Console.WriteLine("   State: " + se.State);\r
 183                                 Console.WriteLine("   StackTrace: " + se.StackTrace);\r
 184                                 Console.WriteLine("   TargetSite: " + se.TargetSite);\r
 185                         }\r
 186                         return;\r
 187                 }\r
 188                 Console.WriteLine("Creating command...");\r
 189                 IDbCommand cmd = con.CreateCommand();\r
 190                 Console.WriteLine("set SQL...");\r
 191                 cmd.CommandText = sql;\r
 192                 Console.WriteLine("execute reader...");\r
 193                 IDataReader reader = cmd.ExecuteReader();\r
 194                 Console.WriteLine("read first row...");\r
 195                 if(reader.Read()) {\r
 196                         Console.WriteLine("  Value: " + reader[columnName].ToString());\r
 197                 }\r
 198                 else {\r
 199                         Console.WriteLine("  No data returned.  Or either, no permission to read data.");\r
 200                 }\r
 201 \r
 202                 Console.WriteLine("Clean up...");\r
 203                 // clean up\r
 204                 reader.Close();\r
 205                 reader = null;\r
 206                 cmd.Dispose();\r
 207                 cmd = null;\r
 208                 con.Close();\r
 209                 con = null;\r
 210                 Console.WriteLine("Done.");\r
 211         }\r
 212 }\r
 213 \r