/[AnywhereTS-MSSQL]/trunk/AnywhereTS.DBSupport/DBConnector.cs
ViewVC logotype

Annotation of /trunk/AnywhereTS.DBSupport/DBConnector.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 84 - (hide annotations) (download)
Fri Jul 13 20:58:17 2012 UTC (8 years, 2 months ago) by william
File size: 6852 byte(s)
+ add initial database support (using the base .net database classes)
in case we need to use different database classes

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     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     }

  ViewVC Help
Powered by ViewVC 1.1.22