/[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 131 - (hide annotations) (download)
Sun Jul 15 02:55:20 2012 UTC (8 years, 11 months ago) by william
File size: 17902 byte(s)
+ move all database options to DatabaseLog (even if it comes from ATSAdmin.exe)

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     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())))
156     {
157     Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
158     }
159     ErrorInfo = ex; throw ErrorInfo;
160     }
161     catch (Exception ex)
162     {
163     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
164     {
165     Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
166     }
167     ErrorInfo = ex; throw ErrorInfo;
168 william 89 }
169 william 84 }
170 william 89 #endregion
171     #region public virtual void CloseConnection(out Exception ErrorInfo)
172 william 84 public virtual void CloseConnection(out Exception ErrorInfo)
173     {
174     ErrorInfo = null;
175     try
176     {
177     if (this.ConnectionIsOpen)
178     connection.Close();
179     }
180 william 87 catch (SqlException ex)
181     {
182     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())))
183     {
184     Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
185     }
186     ErrorInfo = ex; throw ErrorInfo;
187     }
188     catch (Exception ex)
189     {
190     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
191     {
192     Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
193     }
194     ErrorInfo = ex; throw ErrorInfo;
195 william 89 }
196 william 84 }
197 william 89 #endregion
198     #region public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
199 william 84 public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
200     {
201     ErrorInfo = null;
202     if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute query.", new Exception("A connection to the database has not, yet, been established.")); }
203     try
204     {
205     command = this.SafeSqlLiteral(command);
206     DBCommand sqlComm = new DBCommand();
207     sqlComm.CommandText = command;
208     sqlComm.Connection = connection;
209     foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
210     DbDataReader r = sqlComm.ExecuteReader();
211     return r;
212     }
213 william 87 catch (SqlException ex)
214     {
215     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())))
216     {
217     Logging.DatabaseLog.Error(string.Format("Failed to execute query: {0}", command));
218     }
219     ErrorInfo = ex; throw ErrorInfo;
220     }
221     catch (Exception ex)
222     {
223     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
224     {
225     Logging.DatabaseLog.Error(string.Format("Failed to execute querey: {0}", command));
226     }
227     ErrorInfo = ex; throw ErrorInfo;
228 william 89 }
229 william 84 }
230 william 89 #endregion
231     #region public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
232 william 84 public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
233     {
234     ErrorInfo = null;
235     if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute non-query.", new Exception("A connection to the database has not, yet, been established.")); }
236     try
237     {
238     command = this.SafeSqlLiteral(command);
239     DBCommand sqlComm = new DBCommand();
240     sqlComm.CommandText = command;
241     sqlComm.Connection = connection;
242     foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
243     sqlComm.ExecuteNonQuery();
244     }
245 william 87 catch (SqlException ex)
246     {
247     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())))
248     {
249     Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
250     }
251     ErrorInfo = ex; throw ErrorInfo;
252     }
253     catch (Exception ex)
254     {
255     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
256     {
257     Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
258     }
259     ErrorInfo = ex; throw ErrorInfo;
260 william 89 }
261 william 84 }
262 william 89 #endregion
263     #region public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
264 william 84 public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
265     {
266     ErrorInfo = null;
267     try
268     {
269     List<string> ColumnNames = new List<string>();
270     DBDataAdapter da = new DBDataAdapter();
271     command = this.SafeSqlLiteral(command);
272     DBCommand sqlComm = new DBCommand();
273     sqlComm.CommandText = command;
274     sqlComm.Connection = connection;
275     foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
276     da.SelectCommand = sqlComm;
277     DataTable dt = new DataTable();
278     da.Fill(dt);
279     DataRow row = dt.Rows[0];
280     for (int ordinal = 0; ordinal < dt.Columns.Count; ordinal++)
281     {
282     string value = row[ordinal].ToString();
283     string column_name = dt.Columns[ordinal].ColumnName;
284     ColumnNames.Add(column_name);
285     }
286     return ColumnNames;
287     }
288 william 87 catch (SqlException ex)
289     {
290     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())))
291     {
292     Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
293     }
294     ErrorInfo = ex; throw ErrorInfo;
295     }
296     catch (Exception ex)
297     {
298     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
299     {
300     Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
301     }
302     ErrorInfo = ex; throw ErrorInfo;
303 william 89 }
304 william 84 }
305 william 89 #endregion
306     #region public string[] ParseScriptToCommands(string strScript)
307 william 123
308     protected abstract bool ClientRunScript(string strFile, out Exception ErrorInfo);
309 william 89 #endregion
310     #region public virtual bool RunScript(string strFile, out Exception ErrorInfo)
311 william 119 public bool RunScript(string strFile, out Exception ErrorInfo)
312 william 87 {
313     ErrorInfo = null;
314     try
315     {
316     if (this.ConnectionIsOpen)
317     {
318 william 123 if (!ClientRunScript(strFile, out ErrorInfo))
319 william 87 {
320 william 125 if(ErrorInfo != null)
321 william 124 throw ErrorInfo;
322 william 119 return false;
323 william 87 }
324     }
325     else
326     {
327 william 119 Logging.DatabaseLog.Fatal(string.Format("Failed to run script: [database connection is not open] {0}{1}", System.Environment.NewLine, strFile));
328 william 87 return false;
329     }
330     }
331     catch (SqlException ex)
332     {
333 william 128 SqlException e = (ex.GetBaseException() as SqlException);
334     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())))
335 william 87 {
336     Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
337     }
338 william 128 ErrorInfo = ex; throw ErrorInfo;
339 william 87 }
340     catch (Exception ex)
341     {
342 william 128 Exception e = ex.GetBaseException();
343     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", e.GetType().Name, e.Message, System.Environment.NewLine, e.ToString())))
344 william 87 {
345     Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
346     }
347 william 128 ErrorInfo = ex; throw ErrorInfo;
348 william 87 }
349     return false;
350     }
351 william 84 #endregion
352 william 89 #endregion
353 william 84
354     #region IDisposable Members
355     public virtual void Dispose()
356     {
357     try
358     {
359     Exception ErrorInfo;
360 william 131 if (this.ConnectionIsOpen)
361     CloseConnection(out ErrorInfo);
362 william 84 }
363     catch
364     {
365     }
366     }
367     #endregion
368     }
369     }

  ViewVC Help
Powered by ViewVC 1.1.22