/[AnywhereTS-MSSQL]/trunk/AnywhereTS.DBSupport/DBConnector.cs
ViewVC logotype

Annotation of /trunk/AnywhereTS.DBSupport/DBConnector.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 87 - (hide 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 william 84 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 william 87 using System.Text.RegularExpressions;
8 william 84
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 william 87
26     DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo);
27     public bool RunScript(string strFile, MsSqlConnector con, out Exception ErrorInfo);
28 william 84 }
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 william 85
37     public DBConnector(string DBServerAddress, string DBServerInstance, string DBDatabase)
38     {
39 william 87 this.DBServerAddress = DBServerAddress;
40     this.DBServerInstance = DBServerInstance;
41     this.DBDatabase = DBDatabase;
42 william 85 }
43    
44 william 84 protected DBConnection connection;
45 william 87 protected string DBServerAddress = "";
46     protected string DBServerInstance = "";
47     protected string DBDatabase = "";
48 william 84 private string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'", "''"); }
49     #region IDBConnector members
50 william 87 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 william 84 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 william 85 connetionString = string.Format(@"Data Source={0}\{1};Initial Catalog={2};Integrated Security=SSPI", DBServerAddress, DBServerInstance, DBDatabase);
88 william 84 connection = new DBConnection();
89     connection.ConnectionString = connetionString;
90     }
91 william 87 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 william 84 }
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 william 87 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 william 84 }
135     public virtual void CloseConnection(out Exception ErrorInfo)
136     {
137     ErrorInfo = null;
138     try
139     {
140     if (this.ConnectionIsOpen)
141     connection.Close();
142     }
143 william 87 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 william 84 }
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 william 87 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 william 84 }
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 william 87 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 william 84 }
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 william 87 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 william 84 }
263 william 87 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 william 84 #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