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;

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; }
        void 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)
            {
                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 command instance using command: {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 create command instance using command: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
        }
        #endregion
        #region  public virtual void CreateConnection(out Exception ErrorInfo)
        public virtual void CreateConnection(out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                string connetionString = null;
                connetionString = GetConnectionString();
                connection = new DBConnection();
                connection.ConnectionString = connetionString;
            }
            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;
            try
            {
                //this.CloseConnection(out ErrorInfo);
                connection.Open();
                this.ConnectionIsOpen = true;
            }
            //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex;  throw ErrorInfo;}
            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 open 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 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)
        public string[] ParseScriptToCommands(string strScript)
        {
            string[] commands;
            commands = strScript.Split(new string[] { string.Format("GO{0}", System.Environment.NewLine) }, StringSplitOptions.RemoveEmptyEntries);
            return commands;
        }
        #endregion
        #region public virtual bool RunScript(string strFile, out Exception ErrorInfo)
        public bool RunScript(string strFile, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                string[] strCommands;
                strCommands = ParseScriptToCommands(strFile);
                if (this.ConnectionIsOpen)
                {
                    if (!ClientRunScript(strCommands, out 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)
            {
                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 run script: {0}{1}", System.Environment.NewLine, strFile));
                }
                ErrorInfo = ex;
            }
            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 run script: {0}{1}", System.Environment.NewLine, strFile));
                }
                ErrorInfo = ex;
            }
            return false;
        }
        public abstract bool ClientRunScript(string[] strCommands, out Exception ErrorInfo);
        #endregion
        #endregion

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