/[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 89 - (hide annotations) (download)
Fri Jul 13 22:56:50 2012 UTC (8 years, 4 months ago) by william
File size: 17009 byte(s)
+ fix compile errors
+ add ability to get Connection string (made it static)

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

  ViewVC Help
Powered by ViewVC 1.1.22