trunk/AnywhereTS.DBSupport/DBConnector.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using System.Text.RegularExpressions;
using System.IO;


namespace AnywhereTS.DBSupport
{
    public interface IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> : IDisposable
        where DBParameter : DbParameter, new()
        where DBCommand : DbCommand, new()
        where DBConnection : DbConnection, new()
        where DBDataAdapter : DbDataAdapter, new()
    {
        bool ConnectionIsOpen { get; }
        DBConnection CreateConnection(out Exception ErrorInfo);
        void OpenConnection(out Exception ErrorInfo);
        void CloseConnection(out Exception ErrorInfo);

        DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
        void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
        List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo);

        //DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo);
        bool RunScript(string strFile, out Exception ErrorInfo);
    }
    public abstract class DBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> :
        IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter>
        where DBParameter : DbParameter, new()
        where DBCommand : DbCommand, new()
        where DBConnection : DbConnection, new()
        where DBDataAdapter : DbDataAdapter, new()
    {

        public DBConnector(string Server, string Instance, string Database)
        {
            DBServerAddress = Server;
            DBServerInstance = Instance;
            DBDatabase = Database;
            using (log4net.NDC.Push(string.Format("[Server={0}] [Instance={1}] [Database={2}]", Server, Instance, Database)))
            {
                Logging.DatabaseLog.Debug("Creating DBConnector instance");
            }
        }

        public static string GetConnectionString()
        {
            return string.Format(@"Data Source={0}\{1};Database={2};Integrated Security=SSPI", DBServerAddress, DBServerInstance, DBDatabase);
        }
        protected DBConnection connection;
        #region DBServerAddress, DBServerInstance, DBDatabase
        internal static string DBServerAddress = "";
        internal static string DBServerInstance = "";
        internal static string DBDatabase = "";
        #endregion
        #region  private string SafeSqlLiteral(string inputSQL)
        private string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'", "''"); }
        #endregion
        #region IDBConnector members
        #region  public virtual bool ConnectionIsOpen
        public virtual bool ConnectionIsOpen { get; protected set; }
        #endregion
        #region public virtual DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo)
        //public virtual DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo)
        //{
        //    ErrorInfo = null;
        //    try
        //    {
        //        command = this.SafeSqlLiteral(command);
        //        DBCommand sqlComm = new DBCommand();
        //        sqlComm.CommandText = command;
        //        sqlComm.Connection = connection;
        //        foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
        //        return sqlComm;
        //    }
        //    catch (SqlException ex)
        //    {
        //        SqlException e = (ex.GetBaseException() as SqlException);
        //        using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", e.Number.ToString(), e.Message, System.Environment.NewLine, e.ToString())))
        //        {
        //            Logging.DatabaseLog.Error(string.Format("Failed to create command instance using command: {0}", command));
        //        }
        //        ErrorInfo = ex; throw ErrorInfo;
        //    }
        //    catch (Exception ex)
        //    {
        //        Exception e = ex.GetBaseException();
        //        using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", e.GetType().Name, e.Message, System.Environment.NewLine, e.ToString())))
        //        {
        //            Logging.DatabaseLog.Error(string.Format("Failed to create command instance using command: {0}", command));
        //        }
        //        ErrorInfo = ex; throw ErrorInfo;
        //    }
        //}
        #endregion
        #region  public virtual DBConnection CreateConnection(out Exception ErrorInfo)
        public virtual DBConnection CreateConnection(out Exception ErrorInfo)
        {
            ErrorInfo = null;
            string connetionString = string.Empty;
            try
            {                
                connetionString = GetConnectionString();
                using (log4net.NDC.Push(string.Format("connetionString={0}", connetionString)))
                {
                    Logging.DatabaseLog.Debug("Creating Connection");
                    connection = new DBConnection();
                    connection.ConnectionString = connetionString;
                    Logging.DatabaseLog.Debug("Created Connection");
                }
                return this.connection;
            }
            catch (SqlException ex)
            {
                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())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
        }
        #endregion
        #region public virtual void OpenConnection(out Exception ErrorInfo)
        public virtual void OpenConnection(out Exception ErrorInfo)
        {
            ErrorInfo = null;
            string connetionString = string.Empty;
            try
            {
                //this.CloseConnection(out ErrorInfo);
                connetionString = GetConnectionString();
                using (log4net.NDC.Push(string.Format("connectionString={0}", connetionString)))
                {
                    Logging.DatabaseLog.Debug("Opening Connection");
                    connection.Open();
                    this.ConnectionIsOpen = true;
                    Logging.DatabaseLog.Debug("Opened Connection");
                }
            }
            //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex;  throw ErrorInfo;}
            catch (SqlException ex)
            {
                if (ex.Message.ToLower().Contains(string.Format("Cannot open database").ToLower()))
                {
                    ErrorInfo = null;
                    return;
                }
                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())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo; 
            }
            catch (Exception ex)
            {
                if (ex.Message.ToLower().Contains(string.Format("Cannot open database").ToLower()))
                {
                    ErrorInfo = null;
                    return;
                }
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
        }
        #endregion
        #region public virtual void CloseConnection(out Exception ErrorInfo)
        public virtual void CloseConnection(out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                if (this.ConnectionIsOpen)
                    connection.Close();
            }
            catch (SqlException ex)
            {
                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())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
        }
        #endregion
        #region public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
        public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute query.", new Exception("A connection to the database has not, yet, been established.")); }
            try
            {
                command = this.SafeSqlLiteral(command);
                DBCommand sqlComm = new DBCommand();
                sqlComm.CommandText = command;
                sqlComm.Connection = connection;
                foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
                DbDataReader r = sqlComm.ExecuteReader();
                return r;
            }
            catch (SqlException ex)
            {
                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())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to execute query: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to execute querey: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
        }
        #endregion
        #region public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
        public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute non-query.", new Exception("A connection to the database has not, yet, been established.")); }
            try
            {
                command = this.SafeSqlLiteral(command);
                DBCommand sqlComm = new DBCommand();
                sqlComm.CommandText = command;
                sqlComm.Connection = connection;
                foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
                sqlComm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                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())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
        }
        #endregion
        #region public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
        public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                List<string> ColumnNames = new List<string>();
                DBDataAdapter da = new DBDataAdapter();
                command = this.SafeSqlLiteral(command);
                DBCommand sqlComm = new DBCommand();
                sqlComm.CommandText = command;
                sqlComm.Connection = connection;
                foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
                da.SelectCommand = sqlComm;
                DataTable dt = new DataTable();
                da.Fill(dt);
                DataRow row = dt.Rows[0];
                for (int ordinal = 0; ordinal < dt.Columns.Count; ordinal++)
                {
                    string value = row[ordinal].ToString();
                    string column_name = dt.Columns[ordinal].ColumnName;
                    ColumnNames.Add(column_name);
                }
                return ColumnNames;
            }
            catch (SqlException ex)
            {
                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())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
        }
        #endregion
        #region public string[] ParseScriptToCommands(string strScript)
     
        protected abstract bool ClientRunScript(string strFile, out Exception ErrorInfo);
        #endregion
        #region public virtual bool RunScript(string strFile, out Exception ErrorInfo)
        public bool RunScript(string strFile, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                if (this.ConnectionIsOpen)
                {
                    if (!ClientRunScript(strFile, out ErrorInfo))
                    {
                        if(ErrorInfo != null)
                            throw ErrorInfo;
                        return false;
                    }
                }
                else
                {
                    Logging.DatabaseLog.Fatal(string.Format("Failed to run script: [database connection is not open] {0}{1}", System.Environment.NewLine, strFile));
                    return false;
                }
            }
            catch (SqlException ex)
            {
                SqlException e = (ex.GetBaseException() as SqlException);
                using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", e.Number.ToString(), e.Message, System.Environment.NewLine, e.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                Exception e = ex.GetBaseException();
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", e.GetType().Name, e.Message, System.Environment.NewLine, e.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            return false;
        }
        #endregion
        #endregion

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