/[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 121 - (show annotations) (download)
Sat Jul 14 09:40:33 2012 UTC (8 years, 11 months ago) by william
File size: 17732 byte(s)
+ log context

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

  ViewVC Help
Powered by ViewVC 1.1.22