/[AnywhereTS-MSSQL]/trunk/AnywhereTS.DBSupport/DBConnector.cs
ViewVC logotype

Contents of /trunk/AnywhereTS.DBSupport/DBConnector.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 116 - (show annotations) (download)
Sat Jul 14 08:03:33 2012 UTC (8 years, 3 months ago) by william
File size: 17241 byte(s)
+ more logging work

1 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 using System.Text.RegularExpressions;
8
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
26 DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo);
27 bool RunScript(string strFile, out Exception ErrorInfo);
28 }
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
37 public DBConnector(string Server, string Instance, string Database)
38 {
39 DBServerAddress = Server;
40 DBServerInstance = Instance;
41 DBDatabase = Database;
42 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 }
47
48 public static string GetConnectionString()
49 {
50 return string.Format(@"Data Source={0}\{1};Initial Catalog={2};Integrated Security=SSPI", DBServerAddress, DBServerInstance, DBDatabase);
51 }
52 protected DBConnection connection;
53 #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 private string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'", "''"); }
60 #endregion
61 #region IDBConnector members
62 #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 {
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 }
94 }
95 #endregion
96 #region public virtual void CreateConnection(out Exception ErrorInfo)
97 public virtual void CreateConnection(out Exception ErrorInfo)
98 {
99 ErrorInfo = null;
100 try
101 {
102 string connetionString = null;
103 connetionString = GetConnectionString();
104 connection = new DBConnection();
105 connection.ConnectionString = connetionString;
106 }
107 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 }
123 }
124 #endregion
125 #region public virtual void OpenConnection(out Exception ErrorInfo)
126 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 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 }
152 }
153 #endregion
154 #region public virtual void CloseConnection(out Exception ErrorInfo)
155 public virtual void CloseConnection(out Exception ErrorInfo)
156 {
157 ErrorInfo = null;
158 try
159 {
160 if (this.ConnectionIsOpen)
161 connection.Close();
162 }
163 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 }
179 }
180 #endregion
181 #region public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
182 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 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 }
212 }
213 #endregion
214 #region public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
215 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 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 }
244 }
245 #endregion
246 #region public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
247 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 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 }
287 }
288 #endregion
289 #region public string[] ParseScriptToCommands(string strScript)
290 public string[] ParseScriptToCommands(string strScript)
291 {
292 string[] commands;
293 commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
294 return commands;
295 }
296 #endregion
297 #region public virtual bool RunScript(string strFile, out Exception ErrorInfo)
298 public virtual bool RunScript(string strFile, out Exception ErrorInfo)
299 {
300 ErrorInfo = null;
301 try
302 {
303 string[] strCommands;
304 strCommands = ParseScriptToCommands(strFile);
305 if (this.ConnectionIsOpen)
306 {
307 foreach (string strCmd in strCommands)
308 {
309 if (strCmd.Length > 0)
310 {
311 // Substitute database directory with the decided one.
312 DBCommand command = this.CreateCommandInstance(strCmd, new List<DBParameter>(), out ErrorInfo);
313 command.ExecuteNonQuery();
314 }
315 }
316 return true;
317 }
318 else
319 {
320 Logging.ATSAdminLog.Fatal(string.Format("Failed to run script: [database connection is not open] {0}{1}", System.Environment.NewLine, strFile));
321 return false;
322 }
323 }
324 catch (SqlException ex)
325 {
326 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())))
327 {
328 Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
329 }
330 ErrorInfo = ex;
331 }
332 catch (Exception ex)
333 {
334 using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
335 {
336 Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
337 }
338 ErrorInfo = ex;
339 }
340 return false;
341 }
342 #endregion
343 #endregion
344
345 #region IDisposable Members
346 public virtual void Dispose()
347 {
348 try
349 {
350 Exception ErrorInfo;
351 CloseConnection(out ErrorInfo);
352 }
353 catch
354 {
355 }
356 }
357 #endregion
358 }
359 }

  ViewVC Help
Powered by ViewVC 1.1.22