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)
7 // Satya Sudha K (ksathyasudha@novell.com)
10 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
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:
20 // The above copyright notice and this permission notice shall be
21 // included in all copies or substantial portions of the Software.
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.
34 using System
.Data
.SqlClient
;
35 using System
.Data
.SqlTypes
;
36 using System
.Text
.RegularExpressions
;
39 namespace MonoTests
.System
.Data
{
41 public class SqlRetrieve
: BaseRetrieve
{
43 public SqlRetrieve (string dbConfigFile
) : base (dbConfigFile
)
47 // returns a Open connection
48 public override void GetConnection ()
50 string connectionString
= null;
52 connectionString
= ConfigClass
.GetElement (configDoc
, "database", "connectionString");
53 } catch (Exception e
) {
54 Console
.WriteLine ("Error reading the config file");
55 Console
.WriteLine (e
.Message
);
60 con
= new SqlConnection (connectionString
);
63 } catch (SqlException e
) {
64 Console
.WriteLine ("Cannot establish connection with the database");
65 Console
.WriteLine ("Probably the database is down");
67 } catch (InvalidOperationException e
) {
68 Console
.WriteLine ("Cannot open connection!! Probably the connection is already open!!");
70 } catch (Exception e
) {
71 Console
.WriteLine ("Cannot open connection ");
76 public override object GetValue (IDataReader reader
, int columnIndex
)
80 SqlDataReader rdr
= (SqlDataReader
) reader
;
81 if (rdr
.IsDBNull (columnIndex
))
84 if (rdr
.GetDataTypeName (columnIndex
) == "money") {
85 value = rdr
.GetSqlMoney (columnIndex
);
89 Type type
= rdr
.GetFieldType (columnIndex
);
91 switch (type
.Name
.ToLower ()) {
93 case "byte" : value = rdr
.GetSqlByte (columnIndex
);
95 case "sbyte" : value = rdr
.GetSqlInt16 (columnIndex
);
97 case "boolean" : value = rdr
.GetSqlBoolean (columnIndex
);
99 case "int16" : value = rdr
.GetSqlInt16 (columnIndex
);
102 case "int32" : value = rdr
.GetSqlInt32 (columnIndex
);
105 case "int64" : value = rdr
.GetSqlInt64 (columnIndex
);
107 case "single" : value = rdr
.GetSqlSingle (columnIndex
);
109 case "double" : value = rdr
.GetSqlDouble (columnIndex
);
112 case "decimal" : value = rdr
.GetSqlDecimal (columnIndex
);
114 case "datetime": value = rdr
.GetSqlDateTime (columnIndex
);
116 case "string": value = rdr
.GetSqlString (columnIndex
);
118 default : value = rdr
.GetValue (columnIndex
);
125 public override object ConvertToByte (Type type
, string value, ref string errorMsg
)
130 byteval
= Convert
.ToByte (value);
131 } catch (Exception e
) {
132 errorMsg
= "ERROR : " + e
.Message
;
133 errorMsg
+= "\nSTACKTRACE : " + e
.StackTrace
;
137 return new SqlByte (byteval
);
140 public override object ConvertToBoolean (Type type
, string value, ref string errorMsg
)
144 boolval
= Convert
.ToBoolean (Convert
.ToInt16 (value));
145 } catch (Exception e
) {
146 errorMsg
= "ERROR : " + e
.Message
;
147 errorMsg
+= "\nSTACKTRACE : " + e
.StackTrace
;
151 return new SqlBoolean (boolval
);
154 public override object ConvertToInt16 (Type type
, string value, ref string errorMsg
)
158 shortval
= Convert
.ToInt16 (value);
159 } catch (Exception e
) {
160 errorMsg
= "ERROR : " + e
.Message
;
161 errorMsg
+= "\nSTACKTRACE : " + e
.StackTrace
;
165 return new SqlInt16 (shortval
);
168 public override object ConvertToInt32 (Type type
, string value, ref string errorMsg
)
172 intval
= Convert
.ToInt32 (value);
173 } catch (Exception e
) {
174 errorMsg
= "ERROR : " + e
.Message
;
175 errorMsg
+= "\nSTACKTRACE : " + e
.StackTrace
;
179 return new SqlInt32 (intval
);
182 public override object ConvertToInt64 (Type type
, string value, ref string errorMsg
)
186 longval
= Convert
.ToInt64 (value);
187 } catch (Exception e
) {
188 errorMsg
= "ERROR : " + e
.Message
;
189 errorMsg
+= "\nSTACKTRACE : " + e
.StackTrace
;
193 return new SqlInt64 (longval
);
196 public override object ConvertToSingle (Type type
, string value, ref string errorMsg
)
200 floatval
= Convert
.ToSingle (value);
201 } catch (Exception e
) {
202 errorMsg
= "ERROR : " + e
.Message
;
203 errorMsg
+= "\nSTACKTRACE : " + e
.StackTrace
;
207 return new SqlSingle (floatval
);
210 public override object ConvertToDouble (Type type
, string value, ref string errorMsg
)
214 doubleval
= Convert
.ToDouble (value);
215 } catch (Exception e
) {
216 errorMsg
= "ERROR : " + e
.Message
;
217 errorMsg
+= "\nSTACKTRACE : " + e
.StackTrace
;
221 return new SqlDouble (doubleval
);
224 public object ConvertToMoney (Type type
, string value, ref string errorMsg
)
228 decimalval
= Convert
.ToDecimal (value);
229 } catch (FormatException e
) {
230 errorMsg
= "ERROR : " + e
.Message
;
231 errorMsg
+= "\nSTACKTRACE : " + e
.StackTrace
;
235 return new SqlMoney (decimalval
);
238 public override object ConvertToDecimal (Type type
, string value, ref string errorMsg
)
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
);
249 return new SqlDecimal (decimalval
);
252 public override object ConvertToDateTime (Type type
, string value, ref string errorMsg
)
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}))*)*");
257 value = value.Trim ('\'');
258 Match m
= re
.Match (value);
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
);
279 if (hour
== 0 && min
== 0 && sec
== 0)
280 dateObj
= new SqlDateTime (year
, month
, day
);
283 dateObj
= new SqlDateTime (year
, month
, day
, hour
, min
, sec
, msec
);
285 dateObj
= new SqlDateTime (year
, month
, day
, hour
, min
, sec
);
288 } catch (Exception e
) {
289 errorMsg
= "Invalid date time\n";
290 errorMsg
+= "ERROR : " + e
.Message
;
291 errorMsg
+= "\nSTACKTRACE : " + e
.StackTrace
;
298 public override Boolean
AreEqual (object obj
, string value, ref string errorMsg
)
301 if ((obj
== null ) || (value.Equals ("null"))) {
302 if (obj
== null && value.Equals ("null"))
307 object valObj
= ConvertToValueType (obj
.GetType (), value, ref errorMsg
);
308 return obj
.Equals (valObj
);
311 public override object ConvertToValueType (Type objType
, string value, ref string errorMsg
)
314 value = value.Trim ('\'');
315 value = value.Trim ('\"');
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
);
342 if (objType
.ToString () == "System.TimeSpan")
343 return ConvertToTimespan (objType
, value, ref errorMsg
);
345 return ConvertValue (objType
, value, ref errorMsg
);