/[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 125 - (hide annotations) (download)
Sat Jul 14 11:44:52 2012 UTC (8 years, 2 months ago) by william
File size: 17445 byte(s)
+ remove reference to  Sql Server SMO components
+ attempt to fix .sql script parsing to split on GO statements

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

  ViewVC Help
Powered by ViewVC 1.1.22