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

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

Parent Directory Parent Directory | Revision Log Revision Log


Revision 132 - (hide annotations) (download)
Sun Jul 15 03:09:38 2012 UTC (8 years, 9 months ago) by william
File size: 18316 byte(s)

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

  ViewVC Help
Powered by ViewVC 1.1.22