/[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 89 - (show annotations) (download)
Fri Jul 13 22:56:50 2012 UTC (8 years, 2 months ago) by william
File size: 17009 byte(s)
+ fix compile errors
+ add ability to get Connection string (made it static)

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

  ViewVC Help
Powered by ViewVC 1.1.22