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 (11 years, 2 months ago) by william
File size:	17035 byte(s)
Log Message:	+ bulk commit again
#	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
9	namespace AnywhereTS.DBSupport
10	{
11	public interface IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> : IDisposable
12	where DBParameter : DbParameter, new()
13	where DBCommand : DbCommand, new()
14	where DBConnection : DbConnection, new()
15	where DBDataAdapter : DbDataAdapter, new()
16	{
17	bool ConnectionIsOpen { get; }
18	void CreateConnection(out Exception ErrorInfo);
19	void OpenConnection(out Exception ErrorInfo);
20	void CloseConnection(out Exception ErrorInfo);
21
22	DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
23	void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
24	List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo);
25
26	DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo);
27	bool RunScript(string strFile, out Exception ErrorInfo);
28	}
29	public abstract class DBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> :
30	IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter>
31	where DBParameter : DbParameter, new()
32	where DBCommand : DbCommand, new()
33	where DBConnection : DbConnection, new()
34	where DBDataAdapter : DbDataAdapter, new()
35	{
36
37	public DBConnector(string Server, string Instance, string Database)
38	{
39	DBServerAddress = Server;
40	DBServerInstance = Instance;
41	DBDatabase = Database;
42	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	}
47
48	public static string GetConnectionString()
49	{
50	return string.Format(@"Data Source={0}\{1};Database={2};Integrated Security=SSPI", DBServerAddress, DBServerInstance, DBDatabase);
51	}
52	protected DBConnection connection;
53	#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	private string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'", "''"); }
60	#endregion
61	#region IDBConnector members
62	#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	{
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	}
94	}
95	#endregion
96	#region public virtual void CreateConnection(out Exception ErrorInfo)
97	public virtual void CreateConnection(out Exception ErrorInfo)
98	{
99	ErrorInfo = null;
100	try
101	{
102	string connetionString = null;
103	connetionString = GetConnectionString();
104	connection = new DBConnection();
105	connection.ConnectionString = connetionString;
106	}
107	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	}
123	}
124	#endregion
125	#region public virtual void OpenConnection(out Exception ErrorInfo)
126	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	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	}
152	}
153	#endregion
154	#region public virtual void CloseConnection(out Exception ErrorInfo)
155	public virtual void CloseConnection(out Exception ErrorInfo)
156	{
157	ErrorInfo = null;
158	try
159	{
160	if (this.ConnectionIsOpen)
161	connection.Close();
162	}
163	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	}
179	}
180	#endregion
181	#region public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
182	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	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	}
212	}
213	#endregion
214	#region public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
215	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	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	}
244	}
245	#endregion
246	#region public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
247	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	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	}
287	}
288	#endregion
289	#region public string[] ParseScriptToCommands(string strScript)
290	public string[] ParseScriptToCommands(string strScript)
291	{
292	string[] commands;
293	commands = strScript.Split(new string[] { string.Format("GO{0}", System.Environment.NewLine) }, StringSplitOptions.RemoveEmptyEntries);
294	return commands;
295	}
296	#endregion
297	#region public virtual bool RunScript(string strFile, out Exception ErrorInfo)
298	public bool RunScript(string strFile, out Exception ErrorInfo)
299	{
300	ErrorInfo = null;
301	try
302	{
303	string[] strCommands;
304	strCommands = ParseScriptToCommands(strFile);
305	if (this.ConnectionIsOpen)
306	{
307	if (!ClientRunScript(strCommands, out ErrorInfo))
308	{
309	return false;
310	}
311	}
312	else
313	{
314	Logging.DatabaseLog.Fatal(string.Format("Failed to run script: [database connection is not open] {0}{1}", System.Environment.NewLine, strFile));
315	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	public abstract bool ClientRunScript(string[] strCommands, out Exception ErrorInfo);
337	#endregion
338	#endregion
339
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	}