/[AnywhereTS-MSSQL]/trunk/AnywhereTS.DBSupport/DBConnector.cs
ViewVC logotype

Contents of /trunk/AnywhereTS.DBSupport/DBConnector.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 133 - (show annotations) (download)
Sun Jul 15 05:00:59 2012 UTC (8 years, 2 months ago) by william
File size: 19664 byte(s)
+ wip to get rid of errors

1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.Data.SqlClient;
5 using System.Data.Common;
6 using System.Data;
7 using System.Text.RegularExpressions;
8 using System.IO;
9
10
11 namespace AnywhereTS.DBSupport
12 {
13 public interface IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> : IDisposable
14 where DBParameter : DbParameter, new()
15 where DBCommand : DbCommand, new()
16 where DBConnection : DbConnection, new()
17 where DBDataAdapter : DbDataAdapter, new()
18 {
19 bool ConnectionIsCreated { get; }
20 bool ConnectionIsOpen { get; }
21 void CreateConnection(out Exception ErrorInfo);
22 void OpenConnection(out Exception ErrorInfo);
23
24 void GetConnectionClone(out DBConnection connection, out Exception ErrorInfo);
25
26 void CloseConnection(out Exception ErrorInfo);
27
28 DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
29 void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
30 List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo);
31
32 //DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo);
33 bool RunScript(string strFile, out Exception ErrorInfo);
34 }
35 public abstract class DBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> :
36 IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter>
37 where DBParameter : DbParameter, new()
38 where DBCommand : DbCommand, new()
39 where DBConnection : DbConnection, new()
40 where DBDataAdapter : DbDataAdapter, new()
41 {
42
43 public DBConnector(string Server, string Instance, string Database)
44 {
45 DBServerAddress = Server;
46 DBServerInstance = Instance;
47 DBDatabase = Database;
48 using (log4net.NDC.Push(string.Format("[Server={0}] [Instance={1}] [Database={2}]", Server, Instance, Database)))
49 {
50 Logging.DatabaseLog.Debug("Creating DBConnector instance");
51 }
52 }
53
54 public static string GetConnectionString()
55 {
56 return string.Format(@"Data Source={0}\{1};Database={2};Integrated Security=SSPI", DBServerAddress, DBServerInstance, DBDatabase);
57 }
58 protected DBConnection connection;
59 #region DBServerAddress, DBServerInstance, DBDatabase
60 internal static string DBServerAddress = "";
61 internal static string DBServerInstance = "";
62 internal static string DBDatabase = "";
63 #endregion
64 #region private string SafeSqlLiteral(string inputSQL)
65 private string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'", "''"); }
66 #endregion
67 #region IDBConnector members
68 #region public virtual bool ConnectionIsCreated
69 public virtual bool ConnectionIsCreated { get; protected set; }
70 #endregion
71 #region public virtual bool ConnectionIsOpen
72 public virtual bool ConnectionIsOpen { get; protected set; }
73 #endregion
74 #region public virtual DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo)
75 //public virtual DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo)
76 //{
77 // ErrorInfo = null;
78 // try
79 // {
80 // command = this.SafeSqlLiteral(command);
81 // DBCommand sqlComm = new DBCommand();
82 // sqlComm.CommandText = command;
83 // sqlComm.Connection = connection;
84 // foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
85 // return sqlComm;
86 // }
87 // catch (SqlException ex)
88 // {
89 // SqlException e = (ex.GetBaseException() as SqlException);
90 // using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", e.Number.ToString(), e.Message, System.Environment.NewLine, e.ToString())))
91 // {
92 // Logging.DatabaseLog.Error(string.Format("Failed to create command instance using command: {0}", command));
93 // }
94 // ErrorInfo = ex; throw ErrorInfo;
95 // }
96 // catch (Exception ex)
97 // {
98 // Exception e = ex.GetBaseException();
99 // using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", e.GetType().Name, e.Message, System.Environment.NewLine, e.ToString())))
100 // {
101 // Logging.DatabaseLog.Error(string.Format("Failed to create command instance using command: {0}", command));
102 // }
103 // ErrorInfo = ex; throw ErrorInfo;
104 // }
105 //}
106 #endregion
107 #region public virtual void GetConnectionClone(ref DBConnection connection)
108 public virtual void GetConnectionClone(out DBConnection connection, out Exception ErrorInfo)
109 {
110 ErrorInfo = null;
111 connection = this.connection;
112 }
113 #endregion
114 #region public virtual DBConnection CreateConnection(out Exception ErrorInfo)
115 public virtual void CreateConnection(out Exception ErrorInfo)
116 {
117 ErrorInfo = null;
118 string connetionString = string.Empty;
119 try
120 {
121 if (!this.ConnectionIsCreated)
122 {
123 connetionString = GetConnectionString();
124 using (log4net.NDC.Push(string.Format("connetionString={0}", connetionString)))
125 {
126 Logging.DatabaseLog.Debug("Creating Connection");
127 connection = new DBConnection();
128 connection.ConnectionString = connetionString;
129 Logging.DatabaseLog.Debug("Created Connection");
130 this.ConnectionIsCreated = true;
131 }
132 }
133 else
134 {
135 throw new Exception("Connection has already been created.");
136 }
137 }
138 catch (SqlException ex)
139 {
140 this.ConnectionIsCreated = false;
141 using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
142 {
143 Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
144 }
145 ErrorInfo = ex; throw ErrorInfo;
146 }
147 catch (Exception ex)
148 {
149 this.ConnectionIsCreated = false;
150 using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
151 {
152 Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
153 }
154 ErrorInfo = ex; throw ErrorInfo;
155 }
156 }
157 #endregion
158 #region public virtual void OpenConnection(out Exception ErrorInfo)
159 public virtual void OpenConnection(out Exception ErrorInfo)
160 {
161 ErrorInfo = null;
162 string connetionString = string.Empty;
163 try
164 {
165 if (this.ConnectionIsCreated && this.ConnectionIsOpen)
166 this.CloseConnection(out ErrorInfo);
167 if (!this.ConnectionIsCreated)
168 this.CreateConnection(out ErrorInfo);
169 connetionString = GetConnectionString();
170 using (log4net.NDC.Push(string.Format("connectionString={0}", connetionString)))
171 {
172 Logging.DatabaseLog.Debug("Opening Connection");
173 connection.Open();
174 this.ConnectionIsOpen = true;
175 Logging.DatabaseLog.Debug("Opened Connection");
176 }
177 }
178 //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo;}
179 catch (SqlException ex)
180 {
181 this.ConnectionIsOpen = false;
182 if (ex.Message.ToLower().Contains(string.Format("Cannot open database").ToLower()))
183 {
184 ErrorInfo = null;
185 }
186 using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
187 {
188 Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
189 }
190 ErrorInfo = ex; throw ErrorInfo;
191 }
192 catch (Exception ex)
193 {
194 this.ConnectionIsOpen = false;
195 if (ex.Message.ToLower().Contains(string.Format("Cannot open database").ToLower()))
196 {
197 ErrorInfo = null;
198 }
199 using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
200 {
201 Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
202 }
203 ErrorInfo = ex; throw ErrorInfo;
204 }
205 }
206 #endregion
207 #region public virtual void CloseConnection(out Exception ErrorInfo)
208 public virtual void CloseConnection(out Exception ErrorInfo)
209 {
210 ErrorInfo = null;
211 try
212 {
213 if (this.ConnectionIsOpen)
214 {
215 connection.Close();
216 this.ConnectionIsOpen = false;
217 this.ConnectionIsCreated = false;
218 this.connection = null;
219 }
220 }
221 catch (SqlException ex)
222 {
223 using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
224 {
225 Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
226 }
227 ErrorInfo = ex; throw ErrorInfo;
228 }
229 catch (Exception ex)
230 {
231 using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
232 {
233 Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
234 }
235 ErrorInfo = ex; throw ErrorInfo;
236 }
237 }
238 #endregion
239 #region public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
240 public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
241 {
242 ErrorInfo = null;
243 if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute query.", new Exception("A connection to the database has not, yet, been established.")); }
244 try
245 {
246 command = this.SafeSqlLiteral(command);
247 DBCommand sqlComm = new DBCommand();
248 sqlComm.CommandText = command;
249 sqlComm.Connection = connection;
250 foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
251 DbDataReader r = sqlComm.ExecuteReader();
252 return r;
253 }
254 catch (SqlException ex)
255 {
256 using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
257 {
258 Logging.DatabaseLog.Error(string.Format("Failed to execute query: {0}", command));
259 }
260 ErrorInfo = ex; throw ErrorInfo;
261 }
262 catch (Exception ex)
263 {
264 using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
265 {
266 Logging.DatabaseLog.Error(string.Format("Failed to execute querey: {0}", command));
267 }
268 ErrorInfo = ex; throw ErrorInfo;
269 }
270 }
271 #endregion
272 #region public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
273 public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
274 {
275 ErrorInfo = null;
276 if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute non-query.", new Exception("A connection to the database has not, yet, been established.")); }
277 try
278 {
279 command = this.SafeSqlLiteral(command);
280 DBCommand sqlComm = new DBCommand();
281 sqlComm.CommandText = command;
282 sqlComm.Connection = connection;
283 foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
284 sqlComm.ExecuteNonQuery();
285 }
286 catch (SqlException ex)
287 {
288 using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
289 {
290 Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
291 }
292 ErrorInfo = ex; throw ErrorInfo;
293 }
294 catch (Exception ex)
295 {
296 using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
297 {
298 Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
299 }
300 ErrorInfo = ex; throw ErrorInfo;
301 }
302 }
303 #endregion
304 #region public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
305 public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
306 {
307 ErrorInfo = null;
308 try
309 {
310 List<string> ColumnNames = new List<string>();
311 DBDataAdapter da = new DBDataAdapter();
312 command = this.SafeSqlLiteral(command);
313 DBCommand sqlComm = new DBCommand();
314 sqlComm.CommandText = command;
315 sqlComm.Connection = connection;
316 foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
317 da.SelectCommand = sqlComm;
318 DataTable dt = new DataTable();
319 da.Fill(dt);
320 DataRow row = dt.Rows[0];
321 for (int ordinal = 0; ordinal < dt.Columns.Count; ordinal++)
322 {
323 string value = row[ordinal].ToString();
324 string column_name = dt.Columns[ordinal].ColumnName;
325 ColumnNames.Add(column_name);
326 }
327 return ColumnNames;
328 }
329 catch (SqlException ex)
330 {
331 using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
332 {
333 Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
334 }
335 ErrorInfo = ex; throw ErrorInfo;
336 }
337 catch (Exception ex)
338 {
339 using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
340 {
341 Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
342 }
343 ErrorInfo = ex; throw ErrorInfo;
344 }
345 }
346 #endregion
347 #region public string[] ParseScriptToCommands(string strScript)
348
349 protected abstract bool ClientRunScript(string strFile, out Exception ErrorInfo);
350 #endregion
351 #region public virtual bool RunScript(string strFile, out Exception ErrorInfo)
352 public bool RunScript(string strFile, out Exception ErrorInfo)
353 {
354 ErrorInfo = null;
355 try
356 {
357 if (this.ConnectionIsOpen)
358 {
359 if (!ClientRunScript(strFile, out ErrorInfo))
360 {
361 if(ErrorInfo != null)
362 throw ErrorInfo;
363 return false;
364 }
365 }
366 else
367 {
368 Logging.DatabaseLog.Fatal(string.Format("Failed to run script: [database connection is not open] {0}{1}", System.Environment.NewLine, strFile));
369 return false;
370 }
371 }
372 catch (SqlException ex)
373 {
374 SqlException e = (ex.GetBaseException() as SqlException);
375 using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", e.Number.ToString(), e.Message, System.Environment.NewLine, e.ToString())))
376 {
377 Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
378 }
379 ErrorInfo = ex; throw ErrorInfo;
380 }
381 catch (Exception ex)
382 {
383 Exception e = ex.GetBaseException();
384 using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", e.GetType().Name, e.Message, System.Environment.NewLine, e.ToString())))
385 {
386 Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
387 }
388 ErrorInfo = ex; throw ErrorInfo;
389 }
390 return false;
391 }
392 #endregion
393 #endregion
394
395 #region IDisposable Members
396 public virtual void Dispose()
397 {
398 try
399 {
400 Exception ErrorInfo;
401 if (this.ConnectionIsOpen)
402 CloseConnection(out ErrorInfo);
403 }
404 catch
405 {
406 }
407 }
408 #endregion
409 }
410 }

  ViewVC Help
Powered by ViewVC 1.1.22