/[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 131 - (show annotations) (download)
Sun Jul 15 02:55:20 2012 UTC (8 years, 6 months ago) by william
File size: 17902 byte(s)
+ move all database options to DatabaseLog (even if it comes from ATSAdmin.exe)

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

  ViewVC Help
Powered by ViewVC 1.1.22