/[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 121 - (hide annotations) (download)
Sat Jul 14 09:40:33 2012 UTC (8 years, 2 months ago) by william
File size: 17732 byte(s)
+ log context

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

  ViewVC Help
Powered by ViewVC 1.1.22