/[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 87 - (show annotations) (download)
Fri Jul 13 22:37:40 2012 UTC (8 years, 2 months ago) by william
File size: 15735 byte(s)
+ start implementation of AnywhereTS.DBSupport

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

  ViewVC Help
Powered by ViewVC 1.1.22