/[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 85 - (hide annotations) (download)
Fri Jul 13 21:10:26 2012 UTC (8 years, 2 months ago) by william
File size: 7079 byte(s)
+ add support for database access from:
* SQL Server Express
* SQL Server (not Express)
* SQL Server MDF file

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     }

  ViewVC Help
Powered by ViewVC 1.1.22