using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Collections;
6 using System.IO;
//包含File
7 using System.Data;
//包含datatable
8 using System.Data.OleDb;
//包含OleDbConnection
9 using System.Windows.Forms;
10
11 namespace CsharpAccess0903
12 {
13 public abstract class AccessClass
14 {
15 public static string myMdbPath;
16 public static string[] myTableName=
new string[
2];
17 public static ArrayList[] myMdbHead=
new ArrayList[
2];
18
19 //普通的节点
20 public struct Node
21 {
22 private string nodeType;
23 public string NodeType
//表的字段名
24 {
25 set { nodeType =
value; }
26 get {
return nodeType; }
27 }
28
29 private string nodeValue;
30 public string NodeValue
//具体的值
31 {
32 set { nodeValue =
value; }
33 get {
return nodeValue; }
34 }
35 }
36
37 //数据库初始化
38 public static bool MDBInit(
string mdbPath,
string[] tableName, ArrayList[] mdbHead,
int tableNum)
39 {
40 //检测数据库是否存在，若不存在则创建数据库并创建数据表
41 if (!
File.Exists(mdbPath))
42 {
43 CreateMDBDataBase(mdbPath);
//创建mdb
44 for (
int i =
0; i < tableNum; i++
)
45 {
46 CreateMDBTable(mdbPath, tableName[i], mdbHead[i]);
//创建数据表
47 }
48 }
49 myMdbPath =
mdbPath;
50 for (
int i =
0; i < tableNum; i++
)
51 {
52 myTableName[i] =
tableName[i].Clone().ToString();
53 }
54 for (
int i =
0; i < tableNum; i++
)
55 {
56 myMdbHead[i] =
(ArrayList)mdbHead[i].Clone();
57 }
58 return true;
59 }
60
61 //创建mdb
62 public static bool CreateMDBDataBase(
string mdbPath)
63 {
64 try
65 {
66 ADOX.CatalogClass cat =
new ADOX.CatalogClass();
67 cat.Create(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath +
";");
68 cat =
null;
69 return true;
70 }
71 catch {
return false; }
72 }
73
74 //新建mdb的表 //mdbHead是一个ArrayList，存储的是table表中的具体列名。
75 public static bool CreateMDBTable(
string mdbPath,
string tableName, ArrayList mdbHead)
76 {
77 try
78 {
79 ADOX.CatalogClass cat =
new ADOX.CatalogClass();
//=========================================//=====================================//
80 string sAccessConnection
81 =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
mdbPath;
82 ADODB.Connection cn =
new ADODB.Connection();
83 cn.Open(sAccessConnection,
null,
null, -
1);
84 cat.ActiveConnection =
cn;
85
86 //新建一个表
87 ADOX.TableClass tbl =
new ADOX.TableClass();
88 tbl.ParentCatalog =
cat;
89 tbl.Name =
tableName;
90
91 int size =
mdbHead.Count;
92 for (
int i =
0; i < size; i++
)
93 {
94 //增加一个文本字段
95 ADOX.ColumnClass col2 =
new ADOX.ColumnClass();
96 col2.ParentCatalog =
cat;
97 col2.Name = mdbHead[i].ToString();
//列的名称
98 col2.Properties[
"Jet OLEDB:Allow Zero Length"].Value =
false;
99 tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarWChar,
500);
//把列加入table
100 }
101 cat.Tables.Append(tbl);
//这句把表加入数据库(非常重要)
102 tbl =
null;
103 cat =
null;
104 cn.Close();
105 return true;
106 }
107 catch {
return false; }
108 }
109
110
111 // 读取mdb指定表的全部数据
112 public static DataTable ReadAllData(
string tableName,
string mdbPath,
ref bool success)
113 {
114 DataTable dt =
new DataTable();
115 try
116 {
117 DataRow dr;
118 //1、建立连接
119 string strConn
120 =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath +
";Jet OLEDB:Database Password=";
//创建数据库时未设用户及密码，不用提交密码即可操作数据库
121 OleDbConnection odcConnection =
new OleDbConnection(strConn);
122 //2、打开连接
123 odcConnection.Open();
124 //建立SQL查询
125 OleDbCommand odCommand =
odcConnection.CreateCommand();
126 //3、输入查询语句
127 odCommand.CommandText =
"select * from " +
tableName;
128 //建立读取
129 OleDbDataReader odrReader =
odCommand.ExecuteReader();
130 //查询并显示数据
131 int size = odrReader.FieldCount;
//获取当前行中的列数
132 for (
int i =
0; i < size; i++
)
133 {
134 DataColumn dc;
135 dc =
new DataColumn(odrReader.GetName(i));
//获取每一列的名称，放入列结构中
136 dt.Columns.Add(dc);
//新建的table完成属性列的构建
137 }
138 while (odrReader.Read())
//逐条记录(也即逐行)地读取数据
139 {
140 dr = dt.NewRow();
//一行数据
141 for (
int i =
0; i < size; i++
)
142 {
143 dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
//逐列读取每单元格数据放到行结构中
144 }
145 dt.Rows.Add(dr);
//新建的table完成每条记录的存储
146 }
147 //关闭连接
148 odrReader.Close();
149 odcConnection.Close();
150 success =
true;
151 return dt;
152 }
153 catch
154 {
155 success =
false;
156 return dt;
157 }
158 }
159
160
161 // 读取指定表的若干列的数据
162 public static DataTable ReadDataByColumns(
string mdbPath,
string tableName,
string[] columns,
ref bool success)
163 {
164 DataTable dt =
new DataTable();
165 try
166 {
167 DataRow dr;
168 //1、建立连接
169 string strConn
170 =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath +
";Jet OLEDB:Database Password=";
171 OleDbConnection odcConnection =
new OleDbConnection(strConn);
172 //2、打开连接
173 odcConnection.Open();
174 //建立SQL查询
175 OleDbCommand odCommand =
odcConnection.CreateCommand();
176 //3、输入查询语句
177 string strColumn =
"";
178 //if (columns[0] == null)
179 //{
180 // strColumn = " * ";
181 //}
182 //else
183 //{
184 for (
int i =
0; i < columns.Length; i++
)
185 {
186 strColumn += columns[i].ToString() +
",";
187 }
188 strColumn = strColumn.TrimEnd(
‘,‘);
189 //}
190
191 odCommand.CommandText =
"select " + strColumn +
" from " +
tableName;
192 //建立读取
193 OleDbDataReader odrReader =
odCommand.ExecuteReader();
194 //查询并显示数据
195 int size =
odrReader.FieldCount;
196 for (
int i =
0; i < size; i++
)
197 {
198 DataColumn dc;
199 dc =
new DataColumn(odrReader.GetName(i));
200 dt.Columns.Add(dc);
201 }
202
203 while (odrReader.Read())
204 {
205 dr =
dt.NewRow();
206 for (
int i =
0; i < size; i++
)
207 {
208 dr[odrReader.GetName(i)] =
odrReader[odrReader.GetName(i)].ToString();
209 }
210 dt.Rows.Add(dr);
211 }
212 //关闭连接
213 odrReader.Close();
214 odcConnection.Close();
215 success =
true;
216 return dt;
217 }
218 catch
219 {
220 success =
false;
221 return dt;
222 }
223 }
224
225 //读取指定列数据
226 public static DataTable ReadDataByColumnsBound(
string mdbPath,
string tableName,
string[] columns, Node bound,
ref bool success)
227 {
228 DataTable dt =
new DataTable();
229 try
230 {
231 DataRow dr;
232 //1、建立连接
233 string strConn
234 =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath +
";Jet OLEDB:Database Password=haoren";
235 OleDbConnection odcConnection =
new OleDbConnection(strConn);
236 //2、打开连接
237 odcConnection.Open();
238 //建立SQL查询
239 OleDbCommand odCommand =
odcConnection.CreateCommand();
240 //3、输入查询语句
241 string strColumn =
"";
242 if (columns[
0] ==
null)
243 {
244 strColumn =
" * ";
245 }
246 else
247 {
248 for (
int i =
0; i < columns.Length; i++
)
249 {
250 strColumn += columns[i].ToString() +
",";
251 }
252 strColumn = strColumn.TrimEnd(
‘,‘);
253 }
254
255 odCommand.CommandText =
"select " + strColumn +
" from " + tableName +
" where " + bound.NodeType +
" = ‘" + bound.NodeValue +
"‘ ";
256 //建立读取
257 OleDbDataReader odrReader =
odCommand.ExecuteReader();
258 //查询并显示数据
259 int size =
odrReader.FieldCount;
260 for (
int i =
0; i < size; i++
)
261 {
262 DataColumn dc;
263 dc =
new DataColumn(odrReader.GetName(i));
264 dt.Columns.Add(dc);
265 }
266
267 while (odrReader.Read())
268 {
269 dr =
dt.NewRow();
270 for (
int i =
0; i < size; i++
)
271 {
272 dr[odrReader.GetName(i)] =
odrReader[odrReader.GetName(i)].ToString();
273 }
274 dt.Rows.Add(dr);
275 }
276 //关闭连接
277 odrReader.Close();
278 odcConnection.Close();
279 success =
true;
280 return dt;
281 }
282 catch
283 {
284 success =
false;
285 return dt;
286 }
287 }
288 //==================================================================
289 //函数名： GetTableNameList
290 //作者： LiYang
291 //日期： 2015-6-26
292 //功能： 获取ACCESS数据库中的表名
293 //输入参数： 数据库路径
294 //返回值： List<string>
295 //修改记录：
296 //==================================================================
297 public static List<
string> GetTableNameList(
string myMdbPath)
298 {
299 List<
string> list =
new List<
string>
();
300 try
301 {
302 OleDbConnection Conn =
new OleDbConnection();
303 Conn.ConnectionString =
"Provider = Microsoft.Jet.OleDb.4.0;Data Source=" +
myMdbPath;
304 Conn.Open();
305 DataTable dt = Conn.GetSchema(
"Tables");
306 foreach (DataRow row
in dt.Rows)
307 {
308 if (row[
3].ToString() ==
"TABLE")
309 list.Add(row[
2].ToString());
310 }
311 Conn.Close();
312 return list;
313 }
314 catch (Exception ex)
315 {
316 throw ex;
317 }
318 }
319 //==================================================================
320 //函数名： GetTableFieldNameList
321 //作者： LiYang
322 //日期： 2015-6-26
323 //功能： 获取ACCESS数据库指定表名中的字段
324 //输入参数： 数据库路径，表名
325 //返回值： List<string> 属性列的名称
326 //修改记录：
327 //==================================================================
328 public static List<
string> GetTableFieldNameList(
string myMdbPath,
string TableName)
329 {
330 List<
string> list =
new List<
string>
();
331 try
332 {
333 OleDbConnection Conn =
new OleDbConnection();
334 Conn.ConnectionString =
"Provider = Microsoft.Jet.OleDb.4.0;Data Source=" +
myMdbPath;
335 Conn.Open();
336 using (OleDbCommand cmd =
new OleDbCommand())
337 {
338 cmd.CommandText =
"SELECT TOP 1 * FROM [" + TableName +
"]";
//读取一个表中第一条记录，如select top 5 * from tablename，读取表中前5条记录
339 cmd.Connection =
Conn;
340 OleDbDataReader dr = cmd.ExecuteReader();
//得到列头
341 for (
int i =
0; i < dr.FieldCount; i++
)
342 {
343 list.Add(dr.GetName(i));
//得到属性列名称
344 }
345 }
346 Conn.Close();
347 return list;
348 }
349 catch (Exception ex)
350 {
351 throw ex;
352 }
353 }
354 //==================================================================
355 //函数名： ReadDataByColumnsLike
356 //作者： LiYang
357 //日期： 2015-6-26
358 //功能： 获取ACCESS数据库指定表名指定列的相似数据到缓存DataTable
359 //输入参数： 数据库路径，表名，列名，相似结点
360 //返回值： DataTable
361 //修改记录：
362 //==================================================================
363 public static DataTable ReadDataByColumnsLike(
string mdbPath,
string tableName,
string[] columns, Node Like)
364 {
365 DataTable dt =
new DataTable();
366 DataRow dr;
367 try
368 {
369 OleDbConnection Conn =
new OleDbConnection();
370 Conn.ConnectionString =
"Provider = Microsoft.Jet.OleDb.4.0;Data Source=" +
myMdbPath;
371 Conn.Open();
372 OleDbCommand odCommand =
Conn.CreateCommand();
373 string strColumn =
"";
374 if (columns[
0] ==
null)
375 {
376 strColumn =
" * ";
377 }
378 else
379 {
380 for (
int i =
0; i < columns.Length; i++
)
381 {
382 strColumn += columns[i].ToString() +
",";
383 }
384 strColumn = strColumn.TrimEnd(
‘,‘);
385 }
386
387 odCommand.CommandText =
"select " + strColumn +
" from " + tableName +
" where " + Like.NodeType +
" LIKE ‘" + Like.NodeValue +
"‘ ";
388 OleDbDataReader odrReader =
odCommand.ExecuteReader();
389 //查询并显示数据
390 int size =
odrReader.FieldCount;
391 for (
int i =
0; i < size; i++
)
392 {
393 DataColumn dc;
394 dc =
new DataColumn(odrReader.GetName(i));
395 dt.Columns.Add(dc);
396 }
397
398 while (odrReader.Read())
399 {
400 dr =
dt.NewRow();
401 for (
int i =
0; i < size; i++
)
402 {
403 dr[odrReader.GetName(i)] =
odrReader[odrReader.GetName(i)].ToString();
404 }
405 dt.Rows.Add(dr);
406 }
407 //关闭连接
408 odrReader.Close();
409 Conn.Close();
410 return dt;
411 }
412 catch (Exception ex)
413 {
414 throw ex;
415 }
416 }
417 /// <summary>
418 /// 返回某一表的所有字段名
419 /// </summary>
420 public static string[] GetTableColumn(
string database_path,
string varTableName)
421 {
