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 Address, string Instance, string Database)
        {
            DBServerAddress = Address;
            DBServerInstance = Instance;
            DBDatabase = Database;
        }

        public static string GetConnectionString()
        {
            return string.Format(@"Data Source={0}\{1};Initial Catalog={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 = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
            return commands;
        }
        #endregion
        #region public virtual bool RunScript(string strFile, out Exception ErrorInfo)
        public virtual bool RunScript(string strFile, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                string[] strCommands;
                strCommands = ParseScriptToCommands(strFile);
                if (this.ConnectionIsOpen)
                {
                    foreach (string strCmd in strCommands)
                    {
                        if (strCmd.Length > 0)
                        {
                            // Substitute database directory with the decided one.
                            DBCommand command = this.CreateCommandInstance(strCmd, new List<DBParameter>(), out ErrorInfo);
                            command.ExecuteNonQuery();
                        }
                    }
                    return true;
                }
                else
                {
                    Logging.ATSAdminLog.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;
        }
        #endregion
        #endregion

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