/[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 84 - (show 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 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