/[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 128 - (hide annotations) (download)
Sat Jul 14 13:01:52 2012 UTC (8 years, 2 months ago) by william
File size: 17725 byte(s)
+ fix RunScript ==> CommandType must be set to Text

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

  ViewVC Help
Powered by ViewVC 1.1.22