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	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
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	william	85
33			public DBConnector(string DBServerAddress, string DBServerInstance, string DBDatabase)
34			{
35			}
36
37	william	84	protected DBConnection connection;
38	william	85	protected static string DBServerAddress = "";
39			protected static string DBServerInstance = "";
40			protected static string DBDatabase = "";
41	william	84	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	william	85	connetionString = string.Format(@"Data Source={0}\{1};Initial Catalog={2};Integrated Security=SSPI", DBServerAddress, DBServerInstance, DBDatabase);
52	william	84	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			}