ADO数据库连接通用类
ADO数据库连接通用类
代码中包括连接读取sql server，oracle，access数据库
1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using ADODB;
5 using System.Collections;
6 using System.IO;
7 using System.Windows.Forms;
8 namespace RadioAnalysisGIS.DataToSDE
9 {
10 public enum ConnectionType //自定义连接类型枚举
11 {
12 connSQLServer=0, //表示连接类型为SQL Server数据库
13 connOracle = 1, //表示连接类型为Oracle数据库
14 connAccess = 2 //表示连接类型为Access数据库
15 }
16 public class DatabaseRecordset //获取记录集相关参数及字段值的类，使用ADO方法
17 {
18 protected _Recordset m_pRecordset = new RecordsetClass(); //ADO记录集对象
19 protected _Connection m_pConnection=new ConnectionClass();//ADO连接对象
20 public DatabaseRecordset()
21 {
22
23 }
24 ~DatabaseRecordset()
25 {
26 if (m_pRecordset.State!=0) m_pRecordset.Close();
27 }
28 public bool OnCreate(ConnectionType type, string DataSource, string DataBaseName, string UserID, string Password)
29 //该方法依据传递过来的连接类型，连接的数据源、数据库名，用户名和密码，初始化连接对象
30 {
31 if (type == ConnectionType.connSQLServer)
32 {
33 if (m_pRecordset.State != 0) m_pRecordset.Close();
34 string ConStr = "Provider=SQLOLEDB;Data Source=" + DataSource + ";Initial Catalog=" + DataBaseName + ";User ID=" + UserID + ";Password=" + Password + ";Persist Security Info=False";
35 m_pConnection.Open(ConStr, "", "", 0);
36 return true;
37 }
38 else if (type == ConnectionType.connOracle)
39 {
40 if (m_pRecordset.State != 0) m_pRecordset.Close();
41 string ConStr = "Provider=OraOLEDB.Oracle.1;Data Source=" + DataSource + ";";
42 m_pConnection.Open(ConStr, UserID, Password, 0);
43 return true;
44 }
45 else if (type == ConnectionType.connAccess)
46 {
47 if (m_pRecordset.State != 0) m_pRecordset.Close();
48 string ConStr = "Provider=Microsoft.jet.OLEDB.4.0;Data Source="+DataSource+DataBaseName;
49 m_pConnection.Open(ConStr, "", "", 0);
50 return true;
51 }
52 return false;
53 }
54 public bool Select(string SQL)
55 //通过SQL字符串创建记录集
56 {
57 if (m_pRecordset.State != 0) m_pRecordset.Close();
58 m_pRecordset.CursorLocation = CursorLocationEnum.adUseClient;
59 m_pRecordset.Open(SQL, m_pConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, 1);
60 return (m_pRecordset.State != 0);
61 }
62 public bool OpenTable(string TableName)
63 //通过表名字符串创建记录集
64 {
65 if (m_pRecordset.State != 0) m_pRecordset.Close();
66 m_pRecordset.CursorLocation = CursorLocationEnum.adUseClient;
67 m_pRecordset.Open(TableName, m_pConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, 2);
68 return (m_pRecordset.State != 0);
69 }
70 public long GetRecordCount()
71 //得到记录个数
72 {
73 return m_pRecordset.RecordCount;
74 }
75 public long GetFieldCount()
76 //得到字段个数
77 {
78 return m_pRecordset.Fields.Count;
79 }
80 public string GetFieldName(int index)
81 //通过索引得到字段名
82 {
83 Fields pFields=m_pRecordset.Fields;
84 IEnumerator pEnum=pFields.GetEnumerator();
85 pEnum.Reset();
86 pEnum.MoveNext();
87 for (int k = 0; k < index; k++)
88 {
89 pEnum.MoveNext();
90 }
91 Field pField = (Field)pEnum.Current;
92 return pField.Name;
93 }
94 public DataTypeEnum GetFieldType(int index)
95 //通过索引得到字段类型
96 {
97 Fields pFields = m_pRecordset.Fields;
98 IEnumerator pEnum = pFields.GetEnumerator();
99 pEnum.Reset();
100 pEnum.MoveNext();
101 for (int k = 0; k < index; k++)
102 {
103 pEnum.MoveNext();
104 }
105 Field pField = (Field)pEnum.Current;
106 return pField.Type;
107 }
108 public int GetFieldPrecision(int index)
109 //通过索引得到字段精度
110 {
111 Fields pFields = m_pRecordset.Fields;
112 IEnumerator pEnum = pFields.GetEnumerator();
113 pEnum.Reset();
114 pEnum.MoveNext();
115 for (int k = 0; k < index; k++)
116 {
117 pEnum.MoveNext();
118 }
119 Field pField = (Field)pEnum.Current;
120 return pField.Precision;
121 }
122 public int GetFieldNumericScale(int index)
123 //通过索引得到字段小数位数
124 {
125 Fields pFields = m_pRecordset.Fields;
126 IEnumerator pEnum = pFields.GetEnumerator();
127 pEnum.Reset();
128 pEnum.MoveNext();
129 for (int k = 0; k < index; k++)
130 {
131 pEnum.MoveNext();
132 }
133 Field pField = (Field)pEnum.Current;
134 return pField.NumericScale;
135 }
136 public int GetFiledIndex(string Name)
137 //通过字段名称返回字段索引
138 {
139 int index = -1;
140 Fields pFields = m_pRecordset.Fields;
141 IEnumerator pEnum = pFields.GetEnumerator();
142 pEnum.Reset();
143 pEnum.MoveNext();
144 int k = 0;
145 foreach (Field fn in pFields)
146 {
147 string name1 = fn.Name.ToUpper();
148 name1=name1.Trim();
149 string name2 = Name.ToUpper();
150 name2=name2.Trim();
151 if (name1 == name2)
152 {
153 index = k;
154 break;
155 }
156 k++;
157 }
158 return index;
159 }
160 public void MoveFirst()
161 //移动记录集指针到第一条记录
162 {
163 m_pRecordset.MoveFirst();
164 }
165 public void MoveNext()
166 //向下移动记录集指针
167 {
168 m_pRecordset.MoveNext();
169 }
170 public bool IsEOF()
171 {//判断是否为最后一条
172 return m_pRecordset.EOF;
173 }
174 public bool IsBOF()
175 {//判断是否为第一条
176 return m_pRecordset.BOF;
177 }
178 public List<object> GetValueAsList()
179 //得到记录集当前指针所指记录的字段值列表
180 {
181 List<object> vs = new List<object>();
182 Fields pFields = m_pRecordset.Fields;
183 IEnumerator pEnum = pFields.GetEnumerator();
184 pEnum.Reset();
185 pEnum.MoveNext();
186 int Count = pFields.Count;
187 for (int k = 0; k < Count; k++)
188 {
189 Field pField = (Field)pEnum.Current;
190 vs.Add(pField.Value);
191 pEnum.MoveNext();
192 }
193 return vs;
194 }
195 public bool AddInfo(string sql)
196 {
197 object obj = null;
198 m_pConnection.Execute(sql, out obj, 0);
199 return true;
200 }
201 }
202
203
204
205 public class DatabaseParameters
206 //数据库连接参数类。该类存储了连接关键参数，并实现从txt文件读写关键变量
207 {
208 int DataSourceType=0;//属性库类型，0表示SQL Server，1表示Oracle
209 #region 属性库连接参数
210 string DataSource = "zhangye";
211 string DataBaseName="sde";
212 string UserID="sa";
213 string Password="sa";
214 #endregion
215 public int GetDataSourceType()
216 {
217 return DataSourceType;
218 }
219 public void GetDataBaseParameters(out string datasource, out string databasename, out string userid, out string password)
220 {
221 datasource = DataSource;
222 databasename = DataBaseName;
223 userid = UserID;
224 password = Password;
225 }//得到属性库连接参数
226 public bool ReadFromFile()
227 //从连接参数txt文件读取连接关键参数
228 {
229 string lpszPathName = FilePath.GetApplicationDir();
230 lpszPathName = lpszPathName + "\\dataconn.txt";
231 StreamReader reader = null;
232 try
233 {
234 reader = new StreamReader(lpszPathName);
235 string line = reader.ReadLine();
236 line = reader.ReadLine();
237 string[] SubStrings = line.Split(':');
238 string sType = SubStrings[1];
239 sType = sType.ToUpper();
240 sType.Trim();
241 if (sType == "SQL SERVER")
242 DataSourceType = 0;
243 else if (sType == "ORACLE")
244 DataSourceType = 1;
245 else if (sType== "Access")
246 DataSourceType = 2;
247 line = reader.ReadLine();
248 SubStrings = line.Split(':');
249 DataSource = SubStrings[1];
250 line = reader.ReadLine();
251 SubStrings = line.Split(':');
252 DataBaseName = SubStrings[1];
253 line = reader.ReadLine();
254 SubStrings = line.Split(':');
255 UserID = SubStrings[1];
256 line = reader.ReadLine();
257 SubStrings = line.Split(':');
258 Password = SubStrings[1];
259 }
260 catch (IOException e)
261 {
262 Console.WriteLine(e.Message);
263 return false;
264 }
265 catch
266 {
267 return false;
268 }
269 finally
270 {
271 if (reader != null) reader.Close();
272 }
273 return true;
274 }
275 public void WhiteToConnFile()
276 //写入连接参数txt文件
277 {
278 string lpszPathName = FilePath.GetCurrentDir();
279 lpszPathName = lpszPathName + "\\dataconn.txt";
280 StreamWriter writer = File.CreateText(lpszPathName);
281 writer.Write("数据库配置参数");
282 writer.Write(writer.NewLine);
283 string sType = "SQL Server";
284 if (DataSourceType == 1) sType = "Oracle";
285 if (DataSourceType == 2) sType = "Access";
286 writer.Write("1、数据库类别:"+sType);
287 writer.Write(writer.NewLine);
288 writer.Write("2、数据源:" + DataSource);
289 writer.Write(writer.NewLine);
290 writer.Write("3、数据库:" + DataBaseName);
291 writer.Write(writer.NewLine);
292 writer.Write("4、用户:" + UserID);
293 writer.Write(writer.NewLine);
294 writer.Write("5、密码:" + Password);
295 writer.Write(writer.NewLine);
296 writer.Write(@"\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\");
297 writer.Write(writer.NewLine);
298 writer.Close();
299 }
300 }
301
302
303 }
304
2 using System.Collections.Generic;
3 using System.Text;
4 using ADODB;
5 using System.Collections;
6 using System.IO;
7 using System.Windows.Forms;
8 namespace RadioAnalysisGIS.DataToSDE
9 {
10 public enum ConnectionType //自定义连接类型枚举
11 {
12 connSQLServer=0, //表示连接类型为SQL Server数据库
13 connOracle = 1, //表示连接类型为Oracle数据库
14 connAccess = 2 //表示连接类型为Access数据库
15 }
16 public class DatabaseRecordset //获取记录集相关参数及字段值的类，使用ADO方法
17 {
18 protected _Recordset m_pRecordset = new RecordsetClass(); //ADO记录集对象
19 protected _Connection m_pConnection=new ConnectionClass();//ADO连接对象
20 public DatabaseRecordset()
21 {
22
23 }
24 ~DatabaseRecordset()
25 {
26 if (m_pRecordset.State!=0) m_pRecordset.Close();
27 }
28 public bool OnCreate(ConnectionType type, string DataSource, string DataBaseName, string UserID, string Password)
29 //该方法依据传递过来的连接类型，连接的数据源、数据库名，用户名和密码，初始化连接对象
30 {
31 if (type == ConnectionType.connSQLServer)
32 {
33 if (m_pRecordset.State != 0) m_pRecordset.Close();
34 string ConStr = "Provider=SQLOLEDB;Data Source=" + DataSource + ";Initial Catalog=" + DataBaseName + ";User ID=" + UserID + ";Password=" + Password + ";Persist Security Info=False";
35 m_pConnection.Open(ConStr, "", "", 0);
36 return true;
37 }
38 else if (type == ConnectionType.connOracle)
39 {
40 if (m_pRecordset.State != 0) m_pRecordset.Close();
41 string ConStr = "Provider=OraOLEDB.Oracle.1;Data Source=" + DataSource + ";";
42 m_pConnection.Open(ConStr, UserID, Password, 0);
43 return true;
44 }
45 else if (type == ConnectionType.connAccess)
46 {
47 if (m_pRecordset.State != 0) m_pRecordset.Close();
48 string ConStr = "Provider=Microsoft.jet.OLEDB.4.0;Data Source="+DataSource+DataBaseName;
49 m_pConnection.Open(ConStr, "", "", 0);
50 return true;
51 }
52 return false;
53 }
54 public bool Select(string SQL)
55 //通过SQL字符串创建记录集
56 {
57 if (m_pRecordset.State != 0) m_pRecordset.Close();
58 m_pRecordset.CursorLocation = CursorLocationEnum.adUseClient;
59 m_pRecordset.Open(SQL, m_pConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, 1);
60 return (m_pRecordset.State != 0);
61 }
62 public bool OpenTable(string TableName)
63 //通过表名字符串创建记录集
64 {
65 if (m_pRecordset.State != 0) m_pRecordset.Close();
66 m_pRecordset.CursorLocation = CursorLocationEnum.adUseClient;
67 m_pRecordset.Open(TableName, m_pConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, 2);
68 return (m_pRecordset.State != 0);
69 }
70 public long GetRecordCount()
71 //得到记录个数
72 {
73 return m_pRecordset.RecordCount;
74 }
75 public long GetFieldCount()
76 //得到字段个数
77 {
78 return m_pRecordset.Fields.Count;
79 }
80 public string GetFieldName(int index)
81 //通过索引得到字段名
82 {
83 Fields pFields=m_pRecordset.Fields;
84 IEnumerator pEnum=pFields.GetEnumerator();
85 pEnum.Reset();
86 pEnum.MoveNext();
87 for (int k = 0; k < index; k++)
88 {
89 pEnum.MoveNext();
90 }
91 Field pField = (Field)pEnum.Current;
92 return pField.Name;
93 }
94 public DataTypeEnum GetFieldType(int index)
95 //通过索引得到字段类型
96 {
97 Fields pFields = m_pRecordset.Fields;
98 IEnumerator pEnum = pFields.GetEnumerator();
99 pEnum.Reset();
100 pEnum.MoveNext();
101 for (int k = 0; k < index; k++)
102 {
103 pEnum.MoveNext();
104 }
105 Field pField = (Field)pEnum.Current;
106 return pField.Type;
107 }
108 public int GetFieldPrecision(int index)
109 //通过索引得到字段精度
110 {
111 Fields pFields = m_pRecordset.Fields;
112 IEnumerator pEnum = pFields.GetEnumerator();
113 pEnum.Reset();
114 pEnum.MoveNext();
115 for (int k = 0; k < index; k++)
116 {
117 pEnum.MoveNext();
118 }
119 Field pField = (Field)pEnum.Current;
120 return pField.Precision;
121 }
122 public int GetFieldNumericScale(int index)
123 //通过索引得到字段小数位数
124 {
125 Fields pFields = m_pRecordset.Fields;
126 IEnumerator pEnum = pFields.GetEnumerator();
127 pEnum.Reset();
128 pEnum.MoveNext();
129 for (int k = 0; k < index; k++)
130 {
131 pEnum.MoveNext();
132 }
133 Field pField = (Field)pEnum.Current;
134 return pField.NumericScale;
135 }
136 public int GetFiledIndex(string Name)
137 //通过字段名称返回字段索引
138 {
139 int index = -1;
140 Fields pFields = m_pRecordset.Fields;
141 IEnumerator pEnum = pFields.GetEnumerator();
142 pEnum.Reset();
143 pEnum.MoveNext();
144 int k = 0;
145 foreach (Field fn in pFields)
146 {
147 string name1 = fn.Name.ToUpper();
148 name1=name1.Trim();
149 string name2 = Name.ToUpper();
150 name2=name2.Trim();
151 if (name1 == name2)
152 {
153 index = k;
154 break;
155 }
156 k++;
157 }
158 return index;
159 }
160 public void MoveFirst()
161 //移动记录集指针到第一条记录
162 {
163 m_pRecordset.MoveFirst();
164 }
165 public void MoveNext()
166 //向下移动记录集指针
167 {
168 m_pRecordset.MoveNext();
169 }
170 public bool IsEOF()
171 {//判断是否为最后一条
172 return m_pRecordset.EOF;
173 }
174 public bool IsBOF()
175 {//判断是否为第一条
176 return m_pRecordset.BOF;
177 }
178 public List<object> GetValueAsList()
179 //得到记录集当前指针所指记录的字段值列表
180 {
181 List<object> vs = new List<object>();
182 Fields pFields = m_pRecordset.Fields;
183 IEnumerator pEnum = pFields.GetEnumerator();
184 pEnum.Reset();
185 pEnum.MoveNext();
186 int Count = pFields.Count;
187 for (int k = 0; k < Count; k++)
188 {
189 Field pField = (Field)pEnum.Current;
190 vs.Add(pField.Value);
191 pEnum.MoveNext();
192 }
193 return vs;
194 }
195 public bool AddInfo(string sql)
196 {
197 object obj = null;
198 m_pConnection.Execute(sql, out obj, 0);
199 return true;
200 }
201 }
202
203
204
205 public class DatabaseParameters
206 //数据库连接参数类。该类存储了连接关键参数，并实现从txt文件读写关键变量
207 {
208 int DataSourceType=0;//属性库类型，0表示SQL Server，1表示Oracle
209 #region 属性库连接参数
210 string DataSource = "zhangye";
211 string DataBaseName="sde";
212 string UserID="sa";
213 string Password="sa";
214 #endregion
215 public int GetDataSourceType()
216 {
217 return DataSourceType;
218 }
219 public void GetDataBaseParameters(out string datasource, out string databasename, out string userid, out string password)
220 {
221 datasource = DataSource;
222 databasename = DataBaseName;
223 userid = UserID;
224 password = Password;
225 }//得到属性库连接参数
226 public bool ReadFromFile()
227 //从连接参数txt文件读取连接关键参数
228 {
229 string lpszPathName = FilePath.GetApplicationDir();
230 lpszPathName = lpszPathName + "\\dataconn.txt";
231 StreamReader reader = null;
232 try
233 {
234 reader = new StreamReader(lpszPathName);
235 string line = reader.ReadLine();
236 line = reader.ReadLine();
237 string[] SubStrings = line.Split(':');
238 string sType = SubStrings[1];
239 sType = sType.ToUpper();
240 sType.Trim();
241 if (sType == "SQL SERVER")
242 DataSourceType = 0;
243 else if (sType == "ORACLE")
244 DataSourceType = 1;
245 else if (sType== "Access")
246 DataSourceType = 2;
247 line = reader.ReadLine();
248 SubStrings = line.Split(':');
249 DataSource = SubStrings[1];
250 line = reader.ReadLine();
251 SubStrings = line.Split(':');
252 DataBaseName = SubStrings[1];
253 line = reader.ReadLine();
254 SubStrings = line.Split(':');
255 UserID = SubStrings[1];
256 line = reader.ReadLine();
257 SubStrings = line.Split(':');
258 Password = SubStrings[1];
259 }
260 catch (IOException e)
261 {
262 Console.WriteLine(e.Message);
263 return false;
264 }
265 catch
266 {
267 return false;
268 }
269 finally
270 {
271 if (reader != null) reader.Close();
272 }
273 return true;
274 }
275 public void WhiteToConnFile()
276 //写入连接参数txt文件
277 {
278 string lpszPathName = FilePath.GetCurrentDir();
279 lpszPathName = lpszPathName + "\\dataconn.txt";
280 StreamWriter writer = File.CreateText(lpszPathName);
281 writer.Write("数据库配置参数");
282 writer.Write(writer.NewLine);
283 string sType = "SQL Server";
284 if (DataSourceType == 1) sType = "Oracle";
285 if (DataSourceType == 2) sType = "Access";
286 writer.Write("1、数据库类别:"+sType);
287 writer.Write(writer.NewLine);
288 writer.Write("2、数据源:" + DataSource);
289 writer.Write(writer.NewLine);
290 writer.Write("3、数据库:" + DataBaseName);
291 writer.Write(writer.NewLine);
292 writer.Write("4、用户:" + UserID);
293 writer.Write(writer.NewLine);
294 writer.Write("5、密码:" + Password);
295 writer.Write(writer.NewLine);
296 writer.Write(@"\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\");
297 writer.Write(writer.NewLine);
298 writer.Close();
299 }
300 }
301
302
303 }
304
