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;

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

        protected DBConnection connection;
        protected static string DBServerAddress = "";
        protected static string DBServerInstance = "";
        protected static string DBDatabase = "";
        private string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'", "''"); }
        #region IDBConnector members
        private bool _ConnectionIsOpen;
        public virtual bool ConnectionIsOpen { get { return _ConnectionIsOpen; } protected set { _ConnectionIsOpen = value; } }
        public virtual void CreateConnection(out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                string connetionString = null;
                connetionString = string.Format(@"Data Source={0}\{1};Initial Catalog={2};Integrated Security=SSPI", DBServerAddress, DBServerInstance, DBDatabase);
                connection = new DBConnection();
                connection.ConnectionString = connetionString;
            }
            //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex;  throw ErrorInfo;}
            catch (Exception ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw 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 (Exception ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo; }
        }
        public virtual void CloseConnection(out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                if (this.ConnectionIsOpen)
                    connection.Close();
            }
            //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex;  throw ErrorInfo;}
            catch (Exception ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw 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 (Exception ex) { ErrorInfo = new Exception("Cannot execute query.", ex); }
            return null;
        }
        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 (Exception ex) { ErrorInfo = new Exception("Cannot execute non-query.", ex); }
        }
        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 (Exception ex) { ErrorInfo = new Exception("Cannot get column names from reader.", ex); }
            return new List<string>();
        }

        #endregion

        #region IDisposable Members
        public virtual void Dispose()
        {
            try
            {
                Exception ErrorInfo;
                CloseConnection(out ErrorInfo);
            }
            catch
            {
            }
        }
        #endregion
    }
}
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
8	namespace AnywhereTS.DBSupport
9	{
10	public interface IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> : IDisposable
11	where DBParameter : DbParameter, new()
12	where DBCommand : DbCommand, new()
13	where DBConnection : DbConnection, new()
14	where DBDataAdapter : DbDataAdapter, new()
15	{
16	bool ConnectionIsOpen { get; }
17	void CreateConnection(out Exception ErrorInfo);
18	void OpenConnection(out Exception ErrorInfo);
19	void CloseConnection(out Exception ErrorInfo);
20
21	DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
22	void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
23	List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo);
24	}
25	public abstract class DBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> :
26	IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter>
27	where DBParameter : DbParameter, new()
28	where DBCommand : DbCommand, new()
29	where DBConnection : DbConnection, new()
30	where DBDataAdapter : DbDataAdapter, new()
31	{
32
33	public DBConnector(string DBServerAddress, string DBServerInstance, string DBDatabase)
34	{
35	}
36
37	protected DBConnection connection;
38	protected static string DBServerAddress = "";
39	protected static string DBServerInstance = "";
40	protected static string DBDatabase = "";
41	private string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'", "''"); }
42	#region IDBConnector members
43	private bool _ConnectionIsOpen;
44	public virtual bool ConnectionIsOpen { get { return _ConnectionIsOpen; } protected set { _ConnectionIsOpen = value; } }
45	public virtual void CreateConnection(out Exception ErrorInfo)
46	{
47	ErrorInfo = null;
48	try
49	{
50	string connetionString = null;
51	connetionString = string.Format(@"Data Source={0}\{1};Initial Catalog={2};Integrated Security=SSPI", DBServerAddress, DBServerInstance, DBDatabase);
52	connection = new DBConnection();
53	connection.ConnectionString = connetionString;
54	}
55	//catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo;}
56	catch (Exception ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo; }
57	}
58	public virtual void OpenConnection(out Exception ErrorInfo)
59	{
60	ErrorInfo = null;
61	try
62	{
63	//this.CloseConnection(out ErrorInfo);
64	connection.Open();
65	this.ConnectionIsOpen = true;
66	}
67	//catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo;}
68	catch (Exception ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo; }
69	}
70	public virtual void CloseConnection(out Exception ErrorInfo)
71	{
72	ErrorInfo = null;
73	try
74	{
75	if (this.ConnectionIsOpen)
76	connection.Close();
77	}
78	//catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo;}
79	catch (Exception ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo; }
80	}
81
82	public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
83	{
84	ErrorInfo = null;
85	if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute query.", new Exception("A connection to the database has not, yet, been established.")); }
86	try
87	{
88	command = this.SafeSqlLiteral(command);
89	DBCommand sqlComm = new DBCommand();
90	sqlComm.CommandText = command;
91	sqlComm.Connection = connection;
92	foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
93	DbDataReader r = sqlComm.ExecuteReader();
94	return r;
95	}
96	catch (Exception ex) { ErrorInfo = new Exception("Cannot execute query.", ex); }
97	return null;
98	}
99	public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
100	{
101	ErrorInfo = null;
102	if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute non-query.", new Exception("A connection to the database has not, yet, been established.")); }
103	try
104	{
105	command = this.SafeSqlLiteral(command);
106	DBCommand sqlComm = new DBCommand();
107	sqlComm.CommandText = command;
108	sqlComm.Connection = connection;
109	foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
110	sqlComm.ExecuteNonQuery();
111	}
112	catch (Exception ex) { ErrorInfo = new Exception("Cannot execute non-query.", ex); }
113	}
114	public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
115	{
116	ErrorInfo = null;
117	try
118	{
119	List<string> ColumnNames = new List<string>();
120	DBDataAdapter da = new DBDataAdapter();
121	command = this.SafeSqlLiteral(command);
122	DBCommand sqlComm = new DBCommand();
123	sqlComm.CommandText = command;
124	sqlComm.Connection = connection;
125	foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
126	da.SelectCommand = sqlComm;
127	DataTable dt = new DataTable();
128	da.Fill(dt);
129	DataRow row = dt.Rows[0];
130	for (int ordinal = 0; ordinal < dt.Columns.Count; ordinal++)
131	{
132	string value = row[ordinal].ToString();
133	string column_name = dt.Columns[ordinal].ColumnName;
134	ColumnNames.Add(column_name);
135	}
136	return ColumnNames;
137	}
138	catch (Exception ex) { ErrorInfo = new Exception("Cannot get column names from reader.", ex); }
139	return new List<string>();
140	}
141
142	#endregion
143
144	#region IDisposable Members
145	public virtual void Dispose()
146	{
147	try
148	{
149	Exception ErrorInfo;
150	CloseConnection(out ErrorInfo);
151	}
152	catch
153	{
154	}
155	}
156	#endregion
157	}
158	}