/[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 119 - (hide annotations) (download)
Sat Jul 14 09:08:55 2012 UTC (8 years, 2 months ago) by william
File size: 17035 byte(s)
+ bulk commit again

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     try
101     {
102     string connetionString = null;
103 william 89 connetionString = GetConnectionString();
104 william 84 connection = new DBConnection();
105     connection.ConnectionString = connetionString;
106     }
107 william 87 catch (SqlException ex)
108     {
109     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())))
110     {
111     Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
112     }
113     ErrorInfo = ex; throw ErrorInfo;
114     }
115     catch (Exception ex)
116     {
117     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
118     {
119     Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
120     }
121     ErrorInfo = ex; throw ErrorInfo;
122 william 89 }
123 william 84 }
124 william 89 #endregion
125     #region public virtual void OpenConnection(out Exception ErrorInfo)
126 william 84 public virtual void OpenConnection(out Exception ErrorInfo)
127     {
128     ErrorInfo = null;
129     try
130     {
131     //this.CloseConnection(out ErrorInfo);
132     connection.Open();
133     this.ConnectionIsOpen = true;
134     }
135     //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo;}
136 william 87 catch (SqlException ex)
137     {
138     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())))
139     {
140     Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
141     }
142     ErrorInfo = ex; throw ErrorInfo;
143     }
144     catch (Exception ex)
145     {
146     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
147     {
148     Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
149     }
150     ErrorInfo = ex; throw ErrorInfo;
151 william 89 }
152 william 84 }
153 william 89 #endregion
154     #region public virtual void CloseConnection(out Exception ErrorInfo)
155 william 84 public virtual void CloseConnection(out Exception ErrorInfo)
156     {
157     ErrorInfo = null;
158     try
159     {
160     if (this.ConnectionIsOpen)
161     connection.Close();
162     }
163 william 87 catch (SqlException ex)
164     {
165     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())))
166     {
167     Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
168     }
169     ErrorInfo = ex; throw ErrorInfo;
170     }
171     catch (Exception ex)
172     {
173     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 close 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 DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
182 william 84 public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
183     {
184     ErrorInfo = null;
185     if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute query.", new Exception("A connection to the database has not, yet, been established.")); }
186     try
187     {
188     command = this.SafeSqlLiteral(command);
189     DBCommand sqlComm = new DBCommand();
190     sqlComm.CommandText = command;
191     sqlComm.Connection = connection;
192     foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
193     DbDataReader r = sqlComm.ExecuteReader();
194     return r;
195     }
196 william 87 catch (SqlException ex)
197     {
198     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())))
199     {
200     Logging.DatabaseLog.Error(string.Format("Failed to execute query: {0}", command));
201     }
202     ErrorInfo = ex; throw ErrorInfo;
203     }
204     catch (Exception ex)
205     {
206     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
207     {
208     Logging.DatabaseLog.Error(string.Format("Failed to execute querey: {0}", command));
209     }
210     ErrorInfo = ex; throw ErrorInfo;
211 william 89 }
212 william 84 }
213 william 89 #endregion
214     #region public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
215 william 84 public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
216     {
217     ErrorInfo = null;
218     if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute non-query.", new Exception("A connection to the database has not, yet, been established.")); }
219     try
220     {
221     command = this.SafeSqlLiteral(command);
222     DBCommand sqlComm = new DBCommand();
223     sqlComm.CommandText = command;
224     sqlComm.Connection = connection;
225     foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
226     sqlComm.ExecuteNonQuery();
227     }
228 william 87 catch (SqlException ex)
229     {
230     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())))
231     {
232     Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
233     }
234     ErrorInfo = ex; throw ErrorInfo;
235     }
236     catch (Exception ex)
237     {
238     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
239     {
240     Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
241     }
242     ErrorInfo = ex; throw ErrorInfo;
243 william 89 }
244 william 84 }
245 william 89 #endregion
246     #region public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
247 william 84 public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
248     {
249     ErrorInfo = null;
250     try
251     {
252     List<string> ColumnNames = new List<string>();
253     DBDataAdapter da = new DBDataAdapter();
254     command = this.SafeSqlLiteral(command);
255     DBCommand sqlComm = new DBCommand();
256     sqlComm.CommandText = command;
257     sqlComm.Connection = connection;
258     foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
259     da.SelectCommand = sqlComm;
260     DataTable dt = new DataTable();
261     da.Fill(dt);
262     DataRow row = dt.Rows[0];
263     for (int ordinal = 0; ordinal < dt.Columns.Count; ordinal++)
264     {
265     string value = row[ordinal].ToString();
266     string column_name = dt.Columns[ordinal].ColumnName;
267     ColumnNames.Add(column_name);
268     }
269     return ColumnNames;
270     }
271 william 87 catch (SqlException ex)
272     {
273     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())))
274     {
275     Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
276     }
277     ErrorInfo = ex; throw ErrorInfo;
278     }
279     catch (Exception ex)
280     {
281     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
282     {
283     Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
284     }
285     ErrorInfo = ex; throw ErrorInfo;
286 william 89 }
287 william 84 }
288 william 89 #endregion
289     #region public string[] ParseScriptToCommands(string strScript)
290 william 87 public string[] ParseScriptToCommands(string strScript)
291     {
292     string[] commands;
293 william 119 commands = strScript.Split(new string[] { string.Format("GO{0}", System.Environment.NewLine) }, StringSplitOptions.RemoveEmptyEntries);
294 william 87 return commands;
295     }
296 william 89 #endregion
297     #region public virtual bool RunScript(string strFile, out Exception ErrorInfo)
298 william 119 public bool RunScript(string strFile, out Exception ErrorInfo)
299 william 87 {
300     ErrorInfo = null;
301     try
302     {
303     string[] strCommands;
304     strCommands = ParseScriptToCommands(strFile);
305     if (this.ConnectionIsOpen)
306     {
307 william 119 if (!ClientRunScript(strCommands, out ErrorInfo))
308 william 87 {
309 william 119 return false;
310 william 87 }
311     }
312     else
313     {
314 william 119 Logging.DatabaseLog.Fatal(string.Format("Failed to run script: [database connection is not open] {0}{1}", System.Environment.NewLine, strFile));
315 william 87 return false;
316     }
317     }
318     catch (SqlException ex)
319     {
320     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())))
321     {
322     Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
323     }
324     ErrorInfo = ex;
325     }
326     catch (Exception ex)
327     {
328     using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
329     {
330     Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
331     }
332     ErrorInfo = ex;
333     }
334     return false;
335     }
336 william 119 public abstract bool ClientRunScript(string[] strCommands, out Exception ErrorInfo);
337 william 84 #endregion
338 william 89 #endregion
339 william 84
340     #region IDisposable Members
341     public virtual void Dispose()
342     {
343     try
344     {
345     Exception ErrorInfo;
346     CloseConnection(out ErrorInfo);
347     }
348     catch
349     {
350     }
351     }
352     #endregion
353     }
354     }

  ViewVC Help
Powered by ViewVC 1.1.22