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

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

Parent Directory Parent Directory | Revision Log Revision Log


Revision 85 - (show annotations) (download)
Fri Jul 13 21:10:26 2012 UTC (8 years, 4 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 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 }

  ViewVC Help
Powered by ViewVC 1.1.22