下面是近期学习C#连接MDB数据库，通过路径创建MDB数据库，根据名字创建创建对用MDB数据库中的表格，再通过DataTable创建一致的表并将数据放入，，，这是一个类，用完之后记得用Close()方法。
运行环境：x86
添加引用： 目的是//创建Catalog目录类
ADOX.CatalogClass catalog = new ADOX.CatalogClass();
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;
using ADOX;
namespace DataHandle
{
public class MdbHelper
{
private OleDbConnection myConn;
private CatalogClass createCatalog;
public string[] GetTablename()
{
DataTable dt = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string[] Tablename = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
Tablename[i] = dt.Rows[i].ItemArray[2].ToString();
}
return Tablename;
}
public MdbHelper() {
}
public MdbHelper(string address)
{
try
{
//创建一个 OleDbConnection对象
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + address;
myConn = new OleDbConnection(strCon);
myConn.Open();
}
catch (Exception ex)
{
ex.ToString();
}
}
/// <summary>
/// 打开mdb
/// </summary>
/// <param name="address">mdb路径</param>
public void OpenMDB(string address)
{
try
{
//创建一个 OleDbConnection对象
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + address;
myConn = new OleDbConnection(strCon);
myConn.Open();
}
catch (Exception ex)
{
ex.ToString();
}
}
/// <summary>
/// 连接数据库
/// </summary>
/// <param name = "tableName" ></ param >
/// < param name="isClose"></param>
/// <returns></returns>
public DataTable GetTable(string tableName)
{
//创建一个 DataSet对象
DataTable dt = new DataTable();
try
{
myConn.Open();
string strCom = string.Format("select * from {0}", tableName);
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
myCommand.Fill(dt, tableName);
myConn.Close();
}
catch (Exception ex)
{
ex.ToString();
}
return dt;
}
/// <summary>
/// 获取数据库内的所有表的名称
/// </summary>
/// <returns></returns>
public DataTable GetTable()
{
DataTable dt = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string[] Tablename = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
Tablename[i] = dt.Rows[i].ItemArray[2].ToString();
}
return strArrayToTable(Tablename);
}
/// <summary>
/// 数组转据表
/// </summary>
/// <param name = "strArray" ></ param >
/// < returns ></ returns >
public DataTable strArrayToTable(string[] strArray)
{
//数组 转 数据表DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
for (int i = 0; i < strArray.Length; i++)
{
DataRow dr = dt.NewRow();
dr["name"] = strArray[i];
dt.Rows.Add(dr);
}
return dt;
}
/// <summary>
/// 关闭数据连接
/// </summary>
public void CloseConnection()
{
myConn.Close();
}
/// <summary>
/// 创建一个类型和datatable一致的空表
/// </summary>
/// <param name="tableName"></param>
public void CreateTable(string tableName,DataTable dt)
{
string sql = "create table " + tableName;
string tableAttribute = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
tableAttribute = tableAttribute + dt.Columns[i].ColumnName + " " + GetType(dt.Columns[i].DataType.ToString());
if (i < dt.Columns.Count - 1)
{
tableAttribute = tableAttribute + ",";
}
}
sql = sql + "(" + tableAttribute + ");";
OleDbCommand cmd = new OleDbCommand(sql, myConn);
cmd.ExecuteNonQuery();
}
/// <summary>
/// 将datatable导入对应名字的表中
/// </summary>
/// <param name="path"></param>
/// <param name="name"></param>
/// <param name="dt"></param>
public void DatatableToMdb(string name,DataTable dt)
{
try
{
string strCom = string.Format("select * from {0}", name);
OleDbDataAdapter da = new OleDbDataAdapter(strCom, myConn);
//****
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);//这里的CommandBuilder对象一定不要忘了,一般就是写在DataAdapter定义的后面
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
DataSet midData = new DataSet();
da.Fill(midData, name);
foreach (DataRow dR in dt.Rows)
{
DataRow dr = midData.Tables[name].NewRow();
dr.ItemArray = dR.ItemArray;//行复制
midData.Tables[name].Rows.Add(dr);
}
//helper.OfficeHelper.ExcelHelper.DataTableCSV(midData.Tables[name], @"E:\PR\CQYB\MDB\"+name+".csv");
da.Update(midData,name);
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 获取创建mdb表格的属性字段类型
/// </summary>
/// <param name="datatype"></param>
/// <returns></returns>
private string GetType(string datatype)
{
switch (datatype)//匹配类型选择
{
case "System.String":
return "TEXT(255)";
case "System.DateTime":
return "DateTime";
case "System.Double":
return "Double";
case "System.Int32":
case "System.Int16":
case "System.Int64":
return "Int";
default:
return "TEXT(50)";
}
}
/// <summary>
/// 创建Access数据库
/// </summary>
/// <param name="path">文件和文件路径</param>
/// <returns>真为创建成功，假为创建失败或是文件已存在</returns>
public bool CreateAccessDatabase(string path)
{
//如果文件存在反回假
if (File.Exists(path))
{
MessageBox.Show("文件已存在！");
return false;
}
try
{
//如果目录不存在，则创建目录
string dirName = Path.GetDirectoryName(path);
if (!Directory.Exists(dirName))
{
Directory.CreateDirectory(dirName);
}
//创建Catalog目录类
ADOX.CatalogClass catalog = new ADOX.CatalogClass();
string _connectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path
+ ";";
//根据联结字符串使用Jet数据库引擎创建数据库
catalog.Create(_connectionStr);
return true;
}
catch (Exception)
{
throw new Exception("数据库创建失败!");
}
}
/// <summary>
/// 创建数据表
/// </summary>
/// <param name="tableName"></param>
/// <param name="dt"></param>
public void CreateAccessTable(string tableName,DataTable dt)
{
Table table = new Table();
table.ParentCatalog = createCatalog;
table.Name = tableName;
foreach (DataColumn column in dt.Columns)
{
ColumnClass col = new ColumnClass();
col.ParentCatalog = createCatalog;
col.Name = column.ColumnName;
col.Attributes = ColumnAttributesEnum.adColNullable; //允许空值
var type = GetDataColType(column.DataType.ToString());
table.Columns.Append(col, type); //默认数据类型和字段大小
}
createCatalog.Tables.Append(table);
}
/// <summary>
/// 获取数据类型
/// </summary>
/// <param name="typeName"></param>
/// <returns></returns>
public DataTypeEnum GetDataColType(string typeName)
{
switch (typeName)//匹配类型选择
{
case "System.String":
return DataTypeEnum.adVarChar;
case "System.DateTime":
return DataTypeEnum.adDBDate;
case "System.Double":
return DataTypeEnum.adDouble;
case "System.Int32":
case "System.Int16":
case "System.Int64":
return DataTypeEnum.adInteger;
default:
return DataTypeEnum.adVarChar;
}
}
}
}
版权声明：本文为qq_38370387原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。