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 \r
  24 //#define IncludeSybaseAndTdsClient\r
  25 \r
  26 using System;\r
  27 using System.Data;\r
  28 using System.Data.SqlClient;\r
  29 #if IncludeSybaseAndTdsClient\r
  30         using Mono.Data.TdsClient;\r
  31         using Mono.Data.SybaseClient;\r
  32 #endif // IncludeSybaseAndTdsClient\r
  33 \r
  34 public class TestSqlConnection \r
  35 {\r
  36         public static void Main(string[] args) \r
  37         {\r
  38                 Console.WriteLine("Start TestSqlConnection.");\r
  39                 if (args.Length != 6 && args.Length != 7) {\r
  40                         Console.WriteLine(\r
  41                                 "\nUsage: mono TestSqlConnection.exe Client Table Column Server Database UserID [Password]\n\n" +\r
  42 #if IncludeSybaseAndTdsClient\r
  43                                 "\tClient is one of the following: SqlClient, TdsClient, or SybaseClient\n" +\r
  44 #else\r
  45                                 "\tClient is: SqlClient.  No support for TdsClient nor SybaseClient\n" +\r
  46 #endif // IncludeSybaseAndTdsClient\r
  47                                 "\tTable is the name of the database table to select from\n" +\r
  48                                 "\tColumn is the name of the column in the Table to select from\n" +\r
  49                                 "\tServer is the SQL Server to connect.  Use one of the following forms:\n" +\r
  50                                 "\t\tHOSTNAME            Ex: MYHOST\n" +\r
  51                                 "\t\tHOSTNAME,port       Ex: MYHOST,1433\n" +\r
  52                                 "\t\tHOSTNAME\\\\instance  Ex: MYHOST\\\\NETSDK  Note: only works with SqlClient\n" +\r
  53                                 "\tDatabase is the name of the database to use\n" +\r
  54                                 "\tUser ID is the user's User ID\n" +\r
  55                                 "\tPassword is the user's Password   Note: if ommitted, a blank password is used\n" +\r
  56                                 "Exampes:\n" +\r
  57                                 "\tEx 1: SqlClient employee lname MYHOST pubs myuserid mypassword\n" +\r
  58                                 "\tEx 3: SqlClient employee lname MYHOST,1443 pubs myuserid mypassword\n" +\r
  59                                 "\tEx 2: SqlClient Products ProductName MYHOST\\\\NETSDK myuserid mypassword\n" +\r
  60                                 "\tEx 4: SqlClient employee lname MYHOST pubs myuserid\n" +\r
  61                                 "\tEx 5: TdsClient sometable somecolumn MYHOST test myuserid mypassword\n" +\r
  62                                 "\tEx 6: SybaseClient sometable somecolumn MYHOST test myuserid mypassword\n");\r
  63 \r
  64                         return;\r
  65                 }\r
  66 \r
  67                 string client = args[0];\r
  68                 string tableName = args[1];\r
  69                 string columnName = args[2];\r
  70                 \r
  71                 string server = args[3];\r
  72                 string database = args[4];\r
  73                 string userid = args[5];\r
  74                 string password = "";\r
  75                 if (args.Length == 7)\r
  76                         password  = args[6];\r
  77                 \r
  78                 string constr;\r
  79                 string sql;\r
  80 \r
  81                 Console.WriteLine("\nClient: " + client);\r
  82                 Console.WriteLine("Table Name: " + tableName);\r
  83                 Console.WriteLine("Column Name: " + columnName);\r
  84                 Console.WriteLine("Server: " + server);\r
  85                 Console.WriteLine("Database: " + database);\r
  86                 Console.WriteLine("User ID: " + userid);\r
  87                 Console.WriteLine("Password: " + password);\r
  88 \r
  89                 sql = "SELECT " + columnName + " FROM " + tableName;\r
  90                 \r
  91                 constr = \r
  92                         "Server=" + server + ";" + \r
  93                         "Database=" + database + ";" +\r
  94                         "User ID=" + userid + ";" +\r
  95                         "Password=" + password + ";";   \r
  96 \r
  97                 Console.WriteLine("\nConnectionString: " + constr);\r
  98                 Console.WriteLine("SQL: " + sql);\r
  99                 \r
 100                 Console.WriteLine("\nCreating Connection...");\r
 101 \r
 102                 IDbConnection con = null;\r
 103                 switch (client.ToUpper()) {\r
 104                 case "SQLCLIENT":\r
 105                         con = new SqlConnection();\r
 106                         break;\r
 107 #if IncludeSybaseAndTdsClient\r
 108                 case "TDSCLIENT":\r
 109                         con = new TdsConnection();\r
 110                         break;\r
 111                 case "SYBASECLIENT":\r
 112                         con = new SybaseConnection();\r
 113                         break;\r
 114                 default:\r
 115                         Console.WriteLine("Invalid client: " + client + "\nUse SqlClient, TdsClient, or SybaseClient");\r
 116                         return;\r
 117 #else\r
 118                 default:\r
 119                         Console.WriteLine("Invalid client: " + client + "\nUse SqlClient.  No support for TdsClient nor SybaseClient.");\r
 120                         return;\r
 121 \r
 122 #endif\r
 123                 }\r
 124                 Console.WriteLine("set connection string...");\r
 125                 con.ConnectionString = constr;\r
 126                 Console.WriteLine("open connection...");\r
 127                 try {\r
 128                         con.Open();\r
 129                 }\r
 130                 catch(SqlException se) {\r
 131                         Console.WriteLine("SqlException caught");\r
 132                         Console.WriteLine("Message: " + se.Message);\r
 133                         Console.WriteLine("Procedure: " + se.Procedure);\r
 134                         Console.WriteLine("Class: " + se.Class);\r
 135                         Console.WriteLine("Number: " + se.Number);\r
 136                         Console.WriteLine("Source: " + se.Source);\r
 137                         Console.WriteLine("State: " + se.State);\r
 138                         Console.WriteLine("Errors:");\r
 139                         foreach(SqlError error in se.Errors) {\r
 140                                 Console.WriteLine("  SqlError:");\r
 141                                 Console.WriteLine("     Message: " + se.Message);\r
 142                                 Console.WriteLine("     Line Number: " + se.LineNumber);\r
 143                                 Console.WriteLine("     Procedure: " + se.Procedure);\r
 144                                 Console.WriteLine("     Class: " + se.Class);\r
 145                                 Console.WriteLine("     Number: " + se.Number);\r
 146                                 Console.WriteLine("     Server: " + se.Server);\r
 147                                 Console.WriteLine("     Source: " + se.Source);\r
 148                                 Console.WriteLine("     State: " + se.State);\r
 149                         }\r
 150                         Console.WriteLine("StackTrace: " + se.StackTrace);\r
 151                         Console.WriteLine("TargetSite: " + se.TargetSite);\r
 152                         Exception ie = se.InnerException;\r
 153                         if(ie != null) {\r
 154                                 Console.WriteLine("InnerException:");\r
 155                                 Console.WriteLine("   Message: " + se.Message);\r
 156                                 Console.WriteLine("   Class: " + se.Class);\r
 157                                 Console.WriteLine("   Number: " + se.Number);\r
 158                                 Console.WriteLine("   Source: " + se.Source);\r
 159                                 Console.WriteLine("   State: " + se.State);\r
 160                                 Console.WriteLine("   StackTrace: " + se.StackTrace);\r
 161                                 Console.WriteLine("   TargetSite: " + se.TargetSite);\r
 162                         }\r
 163                         return;\r
 164                 }\r
 165                 Console.WriteLine("Creating command...");\r
 166                 IDbCommand cmd = con.CreateCommand();\r
 167                 Console.WriteLine("set SQL...");\r
 168                 cmd.CommandText = sql;\r
 169                 Console.WriteLine("execute reader...");\r
 170                 IDataReader reader = cmd.ExecuteReader();\r
 171                 Console.WriteLine("read first row...");\r
 172                 if(reader.Read()) {\r
 173                         Console.WriteLine("  Value: " + reader[columnName].ToString());\r
 174                 }\r
 175                 else {\r
 176                         Console.WriteLine("  No data returned.  Or either, no permission to read data.");\r
 177                 }\r
 178 \r
 179                 Console.WriteLine("Clean up...");\r
 180                 // clean up\r
 181                 reader.Close();\r
 182                 reader = null;\r
 183                 cmd.Dispose();\r
 184                 cmd = null;\r
 185                 con.Close();\r
 186                 con = null;\r
 187                 Console.WriteLine("Done.");\r
 188         }\r
 189 }\r
 190 \r