mcs/class/System.Data/Test/DataProviderTests/datareadertests/MsSqlSpecificTest.cs

   1 //
   2 // MsSqlSpecificTest.cs :- A class derived from 'BaseRetrieve' class
   3 //                         - Contains code specific to ms sql database
   4 //                           (Retrieves data from the database as sql-specific types)
   5 //
   6 // Author:
   7 //   Satya Sudha K (ksathyasudha@novell.com)
   8 //
   9 //
  10 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  11 //
  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:
  19 //
  20 // The above copyright notice and this permission notice shall be
  21 // included in all copies or substantial portions of the Software.
  22 //
  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.
  30 //
  31
  32 using System;
  33 using System.Data;
  34 using System.Data.SqlClient;
  35 using System.Data.SqlTypes;
  36 using System.Text.RegularExpressions;
  37
  38
  39 namespace MonoTests.System.Data {
  40
  41         public class SqlRetrieve : BaseRetrieve {
  42
  43                 public SqlRetrieve (string dbConfigFile) : base (dbConfigFile)
  44                 {
  45                 }
  46
  47                 // returns a Open connection
  48                 public override void GetConnection ()
  49                 {
  50                         string connectionString = null;
  51                         try {
  52                                 connectionString = ConfigClass.GetElement (configDoc, "database", "connectionString");
  53                         } catch (Exception e) {
  54                                 Console.WriteLine ("Error reading the config file");
  55                                 Console.WriteLine (e.Message);
  56                                 con = null;
  57                                 return;
  58                         }
  59
  60                         con = new SqlConnection (connectionString);
  61                         try {
  62                                 con.Open ();
  63                         } catch (SqlException e) {
  64                                 Console.WriteLine ("Cannot establish connection with the database");
  65                                 Console.WriteLine ("Probably the database is down");
  66                                 con = null;
  67                         } catch (InvalidOperationException e) {
  68                                 Console.WriteLine ("Cannot open connection!! Probably the connection is already open!!");
  69                                 con = null;
  70                         } catch (Exception e) {
  71                                 Console.WriteLine ("Cannot open connection ");
  72                                 con = null;
  73                         }
  74                 }
  75
  76                 public override object GetValue (IDataReader reader, int columnIndex)
  77                 {
  78
  79                         object value = null;
  80                         SqlDataReader rdr = (SqlDataReader) reader;
  81                         if (rdr.IsDBNull (columnIndex))
  82                                 return null;
  83
  84                         if (rdr.GetDataTypeName (columnIndex) == "money") {
  85                                 value = rdr.GetSqlMoney (columnIndex);
  86                                 return value;
  87                         }
  88
  89                         Type type = rdr.GetFieldType (columnIndex);
  90
  91                         switch (type.Name.ToLower ()) {
  92
  93                         case "byte"    : value = rdr.GetSqlByte (columnIndex);
  94                                         break;
  95                         case "sbyte"   : value = rdr.GetSqlInt16 (columnIndex);
  96                                         break;
  97                         case "boolean" : value = rdr.GetSqlBoolean (columnIndex);
  98                                         break;
  99                         case "int16"   : value = rdr.GetSqlInt16 (columnIndex);
 100                                         break;
 101                         case "uint16"  :
 102                         case "int32"   : value = rdr.GetSqlInt32 (columnIndex);
 103                                         break;
 104                         case "uint32"  :
 105                         case "int64"   : value = rdr.GetSqlInt64 (columnIndex);
 106                                         break;
 107                         case "single"  : value = rdr.GetSqlSingle (columnIndex);
 108                                         break;
 109                         case "double"  : value = rdr.GetSqlDouble (columnIndex);
 110                                         break;
 111                         case "uint64"  :
 112                         case "decimal" : value = rdr.GetSqlDecimal (columnIndex);
 113                                         break;
 114                         case "datetime": value = rdr.GetSqlDateTime (columnIndex);
 115                                         break;
 116                         case "string": value = rdr.GetSqlString (columnIndex);
 117                                         break;
 118                         default :      value = rdr.GetValue (columnIndex);
 119                                         break;
 120                         }
 121
 122                         return value;
 123                 }
 124
 125                 public override object ConvertToByte (Type type, string value, ref string errorMsg)
 126                 {
 127                         byte byteval;
 128
 129                         try {
 130                                 byteval = Convert.ToByte (value);
 131                         } catch (Exception e) {
 132                                 errorMsg = "ERROR : " + e.Message;
 133                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 134                                 return null;
 135                         }
 136
 137                         return new SqlByte (byteval);
 138                 }
 139
 140                 public override object ConvertToBoolean (Type type, string value, ref string errorMsg)
 141                 {
 142                         bool boolval;
 143                         try {
 144                                 boolval = Convert.ToBoolean (Convert.ToInt16 (value));
 145                         } catch (Exception e) {
 146                                 errorMsg = "ERROR : " + e.Message;
 147                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 148                                 return null;
 149                         }
 150
 151                         return new SqlBoolean (boolval);
 152                 }
 153
 154                 public override object ConvertToInt16 (Type type, string value, ref string errorMsg)
 155                 {
 156                         short shortval;
 157                         try {
 158                                 shortval = Convert.ToInt16 (value);
 159                         } catch (Exception e) {
 160                                 errorMsg = "ERROR : " + e.Message;
 161                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 162                                 return null;
 163                         }
 164
 165                         return new SqlInt16 (shortval);
 166                 }
 167
 168                 public override object ConvertToInt32 (Type type, string value, ref string errorMsg)
 169                 {
 170                         int intval;
 171                         try {
 172                                 intval = Convert.ToInt32 (value);
 173                         } catch (Exception e) {
 174                                 errorMsg = "ERROR : " + e.Message;
 175                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 176                                 return null;
 177                         }
 178
 179                         return new SqlInt32 (intval);
 180                 }
 181
 182                 public override object ConvertToInt64 (Type type, string value, ref string errorMsg)
 183                 {
 184                         long longval;
 185                         try {
 186                                 longval = Convert.ToInt64 (value);
 187                         } catch (Exception e) {
 188                                 errorMsg = "ERROR : " + e.Message;
 189                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 190                                 return null;
 191                         }
 192
 193                         return new SqlInt64 (longval);
 194                 }
 195
 196                 public override object ConvertToSingle (Type type, string value, ref string errorMsg)
 197                 {
 198                         float floatval;
 199                         try {
 200                                 floatval = Convert.ToSingle (value);
 201                         } catch (Exception e) {
 202                                 errorMsg = "ERROR : " + e.Message;
 203                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 204                                 return null;
 205                         }
 206
 207                         return new SqlSingle (floatval);
 208                 }
 209
 210                 public override object ConvertToDouble (Type type, string value, ref string errorMsg)
 211                 {
 212                         double doubleval;
 213                         try {
 214                                 doubleval = Convert.ToDouble (value);
 215                         } catch (Exception e) {
 216                                 errorMsg = "ERROR : " + e.Message;
 217                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 218                                 return null;
 219                         }
 220
 221                         return new SqlDouble (doubleval);
 222                 }
 223
 224                 public object ConvertToMoney (Type type, string value, ref string errorMsg)
 225                 {
 226                         decimal decimalval;
 227                         try {
 228                                 decimalval = Convert.ToDecimal (value);
 229                         } catch (FormatException e) {
 230                                 errorMsg = "ERROR : " + e.Message;
 231                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 232                                 return null;
 233                         }
 234
 235                         return new SqlMoney (decimalval);
 236                 }
 237
 238                 public override object ConvertToDecimal (Type type, string value, ref string errorMsg)
 239                 {
 240                         decimal decimalval;
 241                         try {
 242                                 decimalval = Convert.ToDecimal (value);
 243                         } catch (FormatException e) {
 244                                 // This may be bcoz value is of the form 'd.ddEdd'
 245                                 Double doubleVal = Convert.ToDouble (value);
 246                                 decimalval = Convert.ToDecimal (doubleVal);
 247                         }
 248
 249                         return new SqlDecimal (decimalval);
 250                 }
 251
 252                 public override object ConvertToDateTime (Type type, string value, ref string errorMsg)
 253                 {
 254
 255                         Regex re = new Regex ("\\b(?<month>\\d{1,2})/(?<day>\\d{1,2})/(?<year>\\d{2,4})\\s+(?<hour>\\d{1,2}):(?<min>\\d{1,2})(:(?<sec>\\d{1,2})(\\.(?<msec>\\d{1,3}))*)*");
 256
 257                         value = value.Trim ('\'');
 258                         Match m = re.Match (value);
 259
 260                         int month, day, year, hour, min, sec, msec;
 261                         month = day = year = hour = min = sec = msec = 0;
 262                         month  = Convert.ToInt32 (m.Result ("${month}"));
 263                         day = Convert.ToInt32 (m.Result ("${day}"));
 264                         year = Convert.ToInt32 (m.Result ("${year}"));
 265                         string str = m.Result ("${hour}");
 266                         if (!str.Equals (""))
 267                                 hour = Convert.ToInt32 (str);
 268                         str = m.Result ("${min}");
 269                         if (!str.Equals (""))
 270                                 min = Convert.ToInt32 (str);
 271                         str = m.Result ("${sec}");
 272                         if (!str.Equals (""))
 273                                 sec = Convert.ToInt32 (str);
 274                         str = m.Result ("${msec}");
 275                         if (!str.Equals (""))
 276                                 msec = Convert.ToInt32 (str);
 277                         SqlDateTime dateObj;
 278                         try {
 279                                 if (hour == 0 && min == 0 && sec == 0)
 280                                         dateObj = new SqlDateTime (year, month, day);
 281                                 else {
 282                                         if (msec != 0) {
 283                                                 dateObj = new SqlDateTime (year, month, day, hour, min, sec, msec);
 284                                         } else {
 285                                                 dateObj = new SqlDateTime (year, month, day, hour, min, sec);
 286                                         }
 287                                 }
 288                         } catch (Exception e) {
 289                                 errorMsg = "Invalid date time\n";
 290                                 errorMsg += "ERROR : " + e.Message;
 291                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 292                                 return null;
 293                         }
 294
 295                         return dateObj;
 296                 }
 297
 298                 public override Boolean AreEqual (object obj, string value, ref string errorMsg)
 299                 {
 300
 301                         if ((obj == null ) || (value.Equals ("null"))) {
 302                                 if (obj == null && value.Equals ("null"))
 303                                         return true;
 304                                 return false;
 305                         }
 306
 307                         object valObj = ConvertToValueType (obj.GetType (), value, ref errorMsg);
 308                         return obj.Equals (valObj);
 309                 }
 310
 311                 public override object ConvertToValueType (Type objType, string value, ref string errorMsg)
 312                 {
 313
 314                         value = value.Trim ('\'');
 315                         value = value.Trim ('\"');
 316
 317                         switch (objType.ToString ()) {
 318                         case "System.Data.SqlTypes.SqlInt16" :
 319                                 return ConvertToInt16 (objType, value, ref errorMsg);
 320                         case "System.Data.SqlTypes.SqlInt32" :
 321                                 return ConvertToInt32 (objType, value, ref errorMsg);
 322                         case "System.Data.SqlTypes.SqlInt64" :
 323                                 return ConvertToInt64 (objType, value, ref errorMsg);
 324                         case "System.Data.SqlTypes.SqlString" :
 325                                 return new SqlString (value);
 326                         case "System.Data.SqlTypes.SqlBoolean" :
 327                                 return ConvertToBoolean (objType, value, ref errorMsg);
 328                         case "System.Data.SqlTypes.SqlByte" :
 329                                 return ConvertToByte (objType, value, ref errorMsg);
 330                         case "System.Data.SqlTypes.SqlDateTime" :
 331                                 return ConvertToDateTime (objType, value, ref errorMsg);
 332                         case "System.Data.SqlTypes.SqlDecimal" :
 333                                 return ConvertToDecimal (objType, value, ref errorMsg);
 334                         case "System.Data.SqlTypes.SqlDouble" :
 335                                 return ConvertToDouble (objType, value, ref errorMsg);
 336                         case "System.Data.SqlTypes.SqlSingle" :
 337                                 return ConvertToSingle (objType, value, ref errorMsg);
 338                         case "System.Data.SqlTypes.SqlMoney" :
 339                                 return ConvertToMoney (objType, value, ref errorMsg);
 340                         }
 341
 342                         if (objType.ToString () == "System.TimeSpan")
 343                                 return ConvertToTimespan (objType, value, ref errorMsg);
 344
 345                         return ConvertValue (objType, value, ref errorMsg);
 346                 }
 347
 348         }
 349 }