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 ConnectionIsCreated { get; }
        bool ConnectionIsOpen { get; }
        void CreateConnection(out Exception ErrorInfo);
        void OpenConnection(out Exception ErrorInfo);

        void GetConnectionClone(out DBConnection connection, 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(string server, string instance, string database)
        {
            return string.Format(@"Data Source={0}\{1};Database={2};Integrated Security=SSPI", server, instance, database);
        }
        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 ConnectionIsCreated
        public virtual bool ConnectionIsCreated { get; protected set; }
        #endregion
        #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 void GetConnectionClone(ref DBConnection connection)
        public virtual void GetConnectionClone(out DBConnection connection, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            connection = this.connection;
        }
        #endregion
        #region  public virtual DBConnection CreateConnection(out Exception ErrorInfo)
        public virtual void CreateConnection(out Exception ErrorInfo)
        {
            ErrorInfo = null;
            string connetionString = string.Empty;
            try
            {
                if (!this.ConnectionIsCreated)
                {
                    connetionString = GetConnectionString(DBServerAddress, DBServerInstance, DBDatabase);
                    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");
                        this.ConnectionIsCreated = true;
                    }
                }
                else
                {
                    throw new Exception("Connection has already been created.");
                }
            }
            catch (SqlException ex)
            {
                this.ConnectionIsCreated = false;
                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)
            {
                this.ConnectionIsCreated = false;
                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
            {
                if (this.ConnectionIsCreated && this.ConnectionIsOpen)
                    this.CloseConnection(out ErrorInfo);
                if (!this.ConnectionIsCreated)
                    this.CreateConnection(out ErrorInfo);
                connetionString = GetConnectionString(DBServerAddress, DBServerInstance, DBDatabase);
                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)
            {
                this.ConnectionIsOpen = false;
                if (ex.Message.ToLower().Contains(string.Format("Cannot open database").ToLower()))
                {
                    ErrorInfo = null;
                }
                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)
            {
                this.ConnectionIsOpen = false;
                if (ex.Message.ToLower().Contains(string.Format("Cannot open database").ToLower()))
                {
                    ErrorInfo = null;
                }
                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();
                    this.ConnectionIsOpen = false;
                    this.ConnectionIsCreated = false;
                    this.connection = null;
                }
            }
            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:	154
Committed:	Sun Jul 15 13:27:58 2012 UTC (10 years, 10 months ago) by william
File size:	19782 byte(s)
Log Message:	+ remove old connection string property
#	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 ConnectionIsCreated { get; }
20	bool ConnectionIsOpen { get; }
21	void CreateConnection(out Exception ErrorInfo);
22	void OpenConnection(out Exception ErrorInfo);
23
24	void GetConnectionClone(out DBConnection connection, out Exception ErrorInfo);
25
26	void CloseConnection(out Exception ErrorInfo);
27
28	DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
29	void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
30	List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo);
31
32	//DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo);
33	bool RunScript(string strFile, out Exception ErrorInfo);
34	}
35	public abstract class DBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> :
36	IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter>
37	where DBParameter : DbParameter, new()
38	where DBCommand : DbCommand, new()
39	where DBConnection : DbConnection, new()
40	where DBDataAdapter : DbDataAdapter, new()
41	{
42
43	public DBConnector(string Server, string Instance, string Database)
44	{
45	DBServerAddress = Server;
46	DBServerInstance = Instance;
47	DBDatabase = Database;
48	using (log4net.NDC.Push(string.Format("[Server={0}] [Instance={1}] [Database={2}]", Server, Instance, Database)))
49	{
50	Logging.DatabaseLog.Debug("Creating DBConnector instance");
51	}
52	}
53
54	public static string GetConnectionString(string server, string instance, string database)
55	{
56	return string.Format(@"Data Source={0}\{1};Database={2};Integrated Security=SSPI", server, instance, database);
57	}
58	protected DBConnection connection;
59	#region DBServerAddress, DBServerInstance, DBDatabase
60	internal static string DBServerAddress = "";
61	internal static string DBServerInstance = "";
62	internal static string DBDatabase = "";
63	#endregion
64	#region private string SafeSqlLiteral(string inputSQL)
65	private string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'", "''"); }
66	#endregion
67	#region IDBConnector members
68	#region public virtual bool ConnectionIsCreated
69	public virtual bool ConnectionIsCreated { get; protected set; }
70	#endregion
71	#region public virtual bool ConnectionIsOpen
72	public virtual bool ConnectionIsOpen { get; protected set; }
73	#endregion
74	#region public virtual DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo)
75	//public virtual DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo)
76	//{
77	// ErrorInfo = null;
78	// try
79	// {
80	// command = this.SafeSqlLiteral(command);
81	// DBCommand sqlComm = new DBCommand();
82	// sqlComm.CommandText = command;
83	// sqlComm.Connection = connection;
84	// foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
85	// return sqlComm;
86	// }
87	// catch (SqlException ex)
88	// {
89	// SqlException e = (ex.GetBaseException() as SqlException);
90	// 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())))
91	// {
92	// Logging.DatabaseLog.Error(string.Format("Failed to create command instance using command: {0}", command));
93	// }
94	// ErrorInfo = ex; throw ErrorInfo;
95	// }
96	// catch (Exception ex)
97	// {
98	// Exception e = ex.GetBaseException();
99	// using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", e.GetType().Name, e.Message, System.Environment.NewLine, e.ToString())))
100	// {
101	// Logging.DatabaseLog.Error(string.Format("Failed to create command instance using command: {0}", command));
102	// }
103	// ErrorInfo = ex; throw ErrorInfo;
104	// }
105	//}
106	#endregion
107	#region public virtual void GetConnectionClone(ref DBConnection connection)
108	public virtual void GetConnectionClone(out DBConnection connection, out Exception ErrorInfo)
109	{
110	ErrorInfo = null;
111	connection = this.connection;
112	}
113	#endregion
114	#region public virtual DBConnection CreateConnection(out Exception ErrorInfo)
115	public virtual void CreateConnection(out Exception ErrorInfo)
116	{
117	ErrorInfo = null;
118	string connetionString = string.Empty;
119	try
120	{
121	if (!this.ConnectionIsCreated)
122	{
123	connetionString = GetConnectionString(DBServerAddress, DBServerInstance, DBDatabase);
124	using (log4net.NDC.Push(string.Format("connetionString={0}", connetionString)))
125	{
126	Logging.DatabaseLog.Debug("Creating Connection");
127	connection = new DBConnection();
128	connection.ConnectionString = connetionString;
129	Logging.DatabaseLog.Debug("Created Connection");
130	this.ConnectionIsCreated = true;
131	}
132	}
133	else
134	{
135	throw new Exception("Connection has already been created.");
136	}
137	}
138	catch (SqlException ex)
139	{
140	this.ConnectionIsCreated = false;
141	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())))
142	{
143	Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
144	}
145	ErrorInfo = ex; throw ErrorInfo;
146	}
147	catch (Exception ex)
148	{
149	this.ConnectionIsCreated = false;
150	using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
151	{
152	Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
153	}
154	ErrorInfo = ex; throw ErrorInfo;
155	}
156	}
157	#endregion
158	#region public virtual void OpenConnection(out Exception ErrorInfo)
159	public virtual void OpenConnection(out Exception ErrorInfo)
160	{
161	ErrorInfo = null;
162	string connetionString = string.Empty;
163	try
164	{
165	if (this.ConnectionIsCreated && this.ConnectionIsOpen)
166	this.CloseConnection(out ErrorInfo);
167	if (!this.ConnectionIsCreated)
168	this.CreateConnection(out ErrorInfo);
169	connetionString = GetConnectionString(DBServerAddress, DBServerInstance, DBDatabase);
170	using (log4net.NDC.Push(string.Format("connectionString={0}", connetionString)))
171	{
172	Logging.DatabaseLog.Debug("Opening Connection");
173	connection.Open();
174	this.ConnectionIsOpen = true;
175	Logging.DatabaseLog.Debug("Opened Connection");
176	}
177	}
178	//catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo;}
179	catch (SqlException ex)
180	{
181	this.ConnectionIsOpen = false;
182	if (ex.Message.ToLower().Contains(string.Format("Cannot open database").ToLower()))
183	{
184	ErrorInfo = null;
185	}
186	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())))
187	{
188	Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
189	}
190	ErrorInfo = ex; throw ErrorInfo;
191	}
192	catch (Exception ex)
193	{
194	this.ConnectionIsOpen = false;
195	if (ex.Message.ToLower().Contains(string.Format("Cannot open database").ToLower()))
196	{
197	ErrorInfo = null;
198	}
199	using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
200	{
201	Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
202	}
203	ErrorInfo = ex; throw ErrorInfo;
204	}
205	}
206	#endregion
207	#region public virtual void CloseConnection(out Exception ErrorInfo)
208	public virtual void CloseConnection(out Exception ErrorInfo)
209	{
210	ErrorInfo = null;
211	try
212	{
213	if (this.ConnectionIsOpen)
214	{
215	connection.Close();
216	this.ConnectionIsOpen = false;
217	this.ConnectionIsCreated = false;
218	this.connection = null;
219	}
220	}
221	catch (SqlException ex)
222	{
223	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())))
224	{
225	Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
226	}
227	ErrorInfo = ex; throw ErrorInfo;
228	}
229	catch (Exception ex)
230	{
231	using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
232	{
233	Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
234	}
235	ErrorInfo = ex; throw ErrorInfo;
236	}
237	}
238	#endregion
239	#region public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
240	public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
241	{
242	ErrorInfo = null;
243	if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute query.", new Exception("A connection to the database has not, yet, been established.")); }
244	try
245	{
246	command = this.SafeSqlLiteral(command);
247	DBCommand sqlComm = new DBCommand();
248	sqlComm.CommandText = command;
249	sqlComm.Connection = connection;
250	foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
251	DbDataReader r = sqlComm.ExecuteReader();
252	return r;
253	}
254	catch (SqlException ex)
255	{
256	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())))
257	{
258	Logging.DatabaseLog.Error(string.Format("Failed to execute query: {0}", command));
259	}
260	ErrorInfo = ex; throw ErrorInfo;
261	}
262	catch (Exception ex)
263	{
264	using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
265	{
266	Logging.DatabaseLog.Error(string.Format("Failed to execute querey: {0}", command));
267	}
268	ErrorInfo = ex; throw ErrorInfo;
269	}
270	}
271	#endregion
272	#region public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
273	public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
274	{
275	ErrorInfo = null;
276	if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute non-query.", new Exception("A connection to the database has not, yet, been established.")); }
277	try
278	{
279	command = this.SafeSqlLiteral(command);
280	DBCommand sqlComm = new DBCommand();
281	sqlComm.CommandText = command;
282	sqlComm.Connection = connection;
283	foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
284	sqlComm.ExecuteNonQuery();
285	}
286	catch (SqlException ex)
287	{
288	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())))
289	{
290	Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
291	}
292	ErrorInfo = ex; throw ErrorInfo;
293	}
294	catch (Exception ex)
295	{
296	using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
297	{
298	Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
299	}
300	ErrorInfo = ex; throw ErrorInfo;
301	}
302	}
303	#endregion
304	#region public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
305	public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
306	{
307	ErrorInfo = null;
308	try
309	{
310	List<string> ColumnNames = new List<string>();
311	DBDataAdapter da = new DBDataAdapter();
312	command = this.SafeSqlLiteral(command);
313	DBCommand sqlComm = new DBCommand();
314	sqlComm.CommandText = command;
315	sqlComm.Connection = connection;
316	foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
317	da.SelectCommand = sqlComm;
318	DataTable dt = new DataTable();
319	da.Fill(dt);
320	DataRow row = dt.Rows[0];
321	for (int ordinal = 0; ordinal < dt.Columns.Count; ordinal++)
322	{
323	string value = row[ordinal].ToString();
324	string column_name = dt.Columns[ordinal].ColumnName;
325	ColumnNames.Add(column_name);
326	}
327	return ColumnNames;
328	}
329	catch (SqlException ex)
330	{
331	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())))
332	{
333	Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
334	}
335	ErrorInfo = ex; throw ErrorInfo;
336	}
337	catch (Exception ex)
338	{
339	using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
340	{
341	Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
342	}
343	ErrorInfo = ex; throw ErrorInfo;
344	}
345	}
346	#endregion
347	#region public string[] ParseScriptToCommands(string strScript)
348
349	protected abstract bool ClientRunScript(string strFile, out Exception ErrorInfo);
350	#endregion
351	#region public virtual bool RunScript(string strFile, out Exception ErrorInfo)
352	public bool RunScript(string strFile, out Exception ErrorInfo)
353	{
354	ErrorInfo = null;
355	try
356	{
357	if (this.ConnectionIsOpen)
358	{
359	if (!ClientRunScript(strFile, out ErrorInfo))
360	{
361	if(ErrorInfo != null)
362	throw ErrorInfo;
363	return false;
364	}
365	}
366	else
367	{
368	Logging.DatabaseLog.Fatal(string.Format("Failed to run script: [database connection is not open] {0}{1}", System.Environment.NewLine, strFile));
369	return false;
370	}
371	}
372	catch (SqlException ex)
373	{
374	SqlException e = (ex.GetBaseException() as SqlException);
375	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())))
376	{
377	Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
378	}
379	ErrorInfo = ex; throw ErrorInfo;
380	}
381	catch (Exception ex)
382	{
383	Exception e = ex.GetBaseException();
384	using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", e.GetType().Name, e.Message, System.Environment.NewLine, e.ToString())))
385	{
386	Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
387	}
388	ErrorInfo = ex; throw ErrorInfo;
389	}
390	return false;
391	}
392	#endregion
393	#endregion
394
395	#region IDisposable Members
396	public virtual void Dispose()
397	{
398	try
399	{
400	Exception ErrorInfo;
401	if (this.ConnectionIsOpen)
402	CloseConnection(out ErrorInfo);
403	}
404	catch
405	{
406	}
407	}
408	#endregion
409	}
410	}