/[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 123 - (show annotations) (download)
Sat Jul 14 11:14:09 2012 UTC (8 years, 2 months ago) by william
File size: 17352 byte(s)
+ bulk commit
-- add SQLSerer SMO support for executing TSQL commands

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 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
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 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 }
96 }
97 #endregion
98 #region public virtual void CreateConnection(out Exception ErrorInfo)
99 public virtual void CreateConnection(out Exception ErrorInfo)
100 {
101 ErrorInfo = null;
102 string connetionString = string.Empty;
103 try
104 {
105 connetionString = GetConnectionString();
106 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 }
114 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 }
130 }
131 #endregion
132 #region public virtual void OpenConnection(out Exception ErrorInfo)
133 public virtual void OpenConnection(out Exception ErrorInfo)
134 {
135 ErrorInfo = null;
136 string connetionString = string.Empty;
137 try
138 {
139 //this.CloseConnection(out ErrorInfo);
140 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 }
149 //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo;}
150 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 }
166 }
167 #endregion
168 #region public virtual void CloseConnection(out Exception ErrorInfo)
169 public virtual void CloseConnection(out Exception ErrorInfo)
170 {
171 ErrorInfo = null;
172 try
173 {
174 if (this.ConnectionIsOpen)
175 connection.Close();
176 }
177 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 }
193 }
194 #endregion
195 #region public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
196 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 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 }
226 }
227 #endregion
228 #region public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
229 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 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 }
258 }
259 #endregion
260 #region public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
261 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 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 }
301 }
302 #endregion
303 #region public string[] ParseScriptToCommands(string strScript)
304
305 protected abstract bool ClientRunScript(string strFile, out Exception ErrorInfo);
306 #endregion
307 #region public virtual bool RunScript(string strFile, out Exception ErrorInfo)
308 public bool RunScript(string strFile, out Exception ErrorInfo)
309 {
310 ErrorInfo = null;
311 try
312 {
313 if (this.ConnectionIsOpen)
314 {
315 if (!ClientRunScript(strFile, out ErrorInfo))
316 {
317 return false;
318 }
319 }
320 else
321 {
322 Logging.DatabaseLog.Fatal(string.Format("Failed to run script: [database connection is not open] {0}{1}", System.Environment.NewLine, strFile));
323 return false;
324 }
325 }
326 catch (SqlException ex)
327 {
328 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())))
329 {
330 Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
331 }
332 ErrorInfo = ex;
333 }
334 catch (Exception ex)
335 {
336 using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
337 {
338 Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
339 }
340 ErrorInfo = ex;
341 }
342 return false;
343 }
344 #endregion
345 #endregion
346
347 #region IDisposable Members
348 public virtual void Dispose()
349 {
350 try
351 {
352 Exception ErrorInfo;
353 CloseConnection(out ErrorInfo);
354 }
355 catch
356 {
357 }
358 }
359 #endregion
360 }
361 }

  ViewVC Help
Powered by ViewVC 1.1.22