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