此文章转载与http://blog.csdn.net/gisfarmer/
看到CSDN上不断兄弟姐妹提问关于c#操作Access的问题,于是本人利用闲暇将c#操作Access的方法加以总结,主要解决的问题有:
创建mdb
创建table
读取table内容
查询table中的内容
向table中插入数据
删除table中的记录
向table中插入照片
读取table中的照片等。
另:本人水平有限,不当之处还请斧正。废话少说,开始正题。
文介绍C#访问操作Access数据库的基础知识,并提供一个相关的例程。C#的ADO.NET还不能通过编程方式创建全新的ACCESS(MDB)数据库,所以还只能使用ADOX这个来自COM的链接库来操作。
主要知识点如下:
using System.Data.OleDb;
using System.Data;
连接字符串:String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=product.mdb";
建立连接:OleDbConnection connection = new OleDbConnection(connectionString);
使用OleDbCommand类来执行Sql语句:
OleDbCommand cmd = new OleDbCommand(sql, connection);
connection.Open();
cmd.ExecuteNonQuery();
1.创建mdb库,例程如下:
需要注意的是:参数mdbPath是mdb的完整路径(不包含表的名称)。例如:D:\\test.mdb
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
//创建mdb
2
public static bool CreateMDBDataBase(string mdbPath)
3![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
4
try
5![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
6
ADOX.CatalogClass cat = new ADOX.CatalogClass();
7
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";");
8
cat = null;
9
return true;
10
}
11![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
catch
{ return false; }
12
}
13![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2.创建具体的表,例程如下:
通常一个mdb的可以包含n个表。下面的程序主要是创建一个table。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
//新建mdb的表
2
//mdbHead是一个ArrayList,存储的是table表中的具体列名。
3
public static bool CreateMDBTable(string mdbPath,string tableName, ArrayList mdbHead)
4![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
5
try
6![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
7
ADOX.CatalogClass cat = new ADOX.CatalogClass();
8
string sAccessConnection
9
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath;
10
ADODB.Connection cn = new ADODB.Connection();
11
cn.Open(sAccessConnection, null, null, -1);
12
cat.ActiveConnection = cn;
13![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
//新建一个表
15
ADOX.TableClass tbl = new ADOX.TableClass();
16
tbl.ParentCatalog = cat;
17
tbl.Name = tableName;
18![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
int size = mdbHead.Count;
20
for (int i = 0; i < size; i++)
21![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
22
//增加一个文本字段
23
ADOX.ColumnClass col2 = new ADOX.ColumnClass();
24
col2.ParentCatalog = cat;
25
col2.Name = mdbHead[i].ToString();//列的名称
26
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
27
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarWChar, 500);
28
}
29
cat.Tables.Append(tbl); //这句把表加入数据库(非常重要)
30
tbl = null;
31
cat = null;
32
cn.Close();
33
return true;
34
}
35![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
catch
{ return false; }
36
}
37![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3.读取mdb内容(完全读取),例程如下:
本例程返回的是一个DataTable,如需其他格式可以自行转换。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
// 读取mdb数据
2
public static DataTable ReadAllData(string tableName, string mdbPath,ref bool success)
3![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
4
DataTable dt = new DataTable();
5
try
6![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
7
DataRow dr;
8
//1、建立连接
9
string strConn
10
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database
11![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
Password=haoren";
13
OleDbConnection odcConnection = new OleDbConnection(strConn);
14
//2、打开连接
15
odcConnection.Open();
16
//建立SQL查询
17
OleDbCommand odCommand = odcConnection.CreateCommand();
18
//3、输入查询语句
19
odCommand.CommandText = "select * from " + tableName;
20
//建立读取
21
OleDbDataReader odrReader = odCommand.ExecuteReader();
22
//查询并显示数据
23
int size = odrReader.FieldCount;
24
for (int i = 0; i < size; i++)
25![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
26
DataColumn dc;
27
dc = new DataColumn(odrReader.GetName(i));
28
dt.Columns.Add(dc);
29
}
30
while (odrReader.Read())
31![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
32
dr = dt.NewRow();
33
for (int i = 0; i < size; i++)
34![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
35
dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
36
}
37
dt.Rows.Add(dr);
38
}
39
//关闭连接
40
odrReader.Close();
41
odcConnection.Close();
42
success = true;
43
return dt;
44
}
45
catch
46![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
47
success = false;
48
return dt;
49
}
50
}
51![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4.读取mdb内容(按列读取),例程如下:
columns数组存储的是你要查询的列名称(必须确保mdb表中存在你要的列)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
// 读取mdb数据
2
public static DataTable ReadDataByColumns(string mdbPaht,string tableName, string[] columns, ref bool success)
3![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
4
DataTable dt = new DataTable();
5
try
6![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
7
DataRow dr;
8
//1、建立连接
9
string strConn
10
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren";
11
OleDbConnection odcConnection = new OleDbConnection(strConn);
12
//2、打开连接
13
odcConnection.Open();
14
//建立SQL查询
15
OleDbCommand odCommand = odcConnection.CreateCommand();
16
//3、输入查询语句
17
string strColumn = "";
18
for (int i = 0; i < columns.Length; i++)
19![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
20
strColumn += columns[i].ToString() + ",";
21
}
22
strColumn = strColumn.TrimEnd(',');
23
odCommand.CommandText = "select "+strColumn+" from " + tableName;
24
//建立读取
25
OleDbDataReader odrReader = odCommand.ExecuteReader();
26
//查询并显示数据
27
int size = odrReader.FieldCount;
28
for (int i = 0; i < size; i++)
29![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
30
DataColumn dc;
31
dc = new DataColumn(odrReader.GetName(i));
32
dt.Columns.Add(dc);
33
}
34![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
35
while (odrReader.Read())
36![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
37
dr = dt.NewRow();
38
for (int i = 0; i < size; i++)
39![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
40
dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
41
}
42
dt.Rows.Add(dr);
43
}
44
//关闭连接
45
odrReader.Close();
46
odcConnection.Close();
47
success = true;
48
return dt;
49
}
50
catch
51![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
52
success = false;
53
return dt;
54
}
55
}
56![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)