/[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 133 - (hide annotations) (download)
Sun Jul 15 05:00:59 2012 UTC (8 years, 2 months ago) by william
File size: 19664 byte(s)
+ wip to get rid of errors

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

  ViewVC Help
Powered by ViewVC 1.1.22