C# 操作Access数据库，导出数据到dmb文件

前几天做了个小工具，今天闲来没事儿，做一下整理

1.MdbOperate类，操作access的方法

public class MdbOperate
{
static Logger logger = new Logger();
static OleDbConnection connection;
static string mdbConn = ConfigurationManager.AppSettings["mdbConn"];//web.config配置  <add key="mdbConn" value="provider=Microsoft.ACE.OLEDB.12.0;Data Source="/> 

 

下载地址：http://www.microsoft.com/zh-cn/download/details.aspx?id=13255

#region 创建数据库
/// <summary>
/// 创建mdb文件
/// </summary>
/// <param name="filename">数据库名称 全路径</param>
public static void creatMDB(string filename)
{
try
{
ADOX.CatalogClass cat = new CatalogClass();//Microsoft.ACE.OLEDB.12.0
string str = mdbConn + filename + ";";
cat.Create(str);
cat = null;
}
catch (Exception ex)
{
logger.Error("创建mdb文件有错误:" + ex.Message);
logger.Flush();
}

}
#endregion

#region 判断数据库中是否存在某表
public static bool GetTables(OleDbConnection conn)
{
int result = 0;
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (schemaTable != null)
{
for (Int32 row = 0; row < schemaTable.Rows.Count; row++)
{
string col_name = schemaTable.Rows[row]["TABLE_NAME"].ToString();
if (col_name == "mailtable")
{
result++;
}
}
}
if (result == 0)
return false;
return true;
}
#endregion

#region
/// <summary>
/// 创建表
/// </summary>
/// <param name="mdbPath"></param>
/// <param name="tableName"></param>
/// <param name="mdbHead">mdbHead是一个ArrayList，存储的是table表中的具体列名。</param>
public static void CreateMDBTable(string mdbPath, string tableName, ArrayList mdbHead)
{
try
{
ADOX.CatalogClass cat = new CatalogClass();
string sAccessConnection = mdbConn + mdbPath + ";Jet OLEDB:Engine Type=5";
connection = new OleDbConnection(sAccessConnection);
connection.Open();//打开数据库连接

ADODB.Connection cn = new ADODB.Connection();
cn.Open(sAccessConnection, null, null, -1);

cat.ActiveConnection = cn;
//新建一个表
ADOX.TableClass table = new TableClass();
table.ParentCatalog = cat;
table.Name = tableName;

//增加一个自动增长列
/* ADOX.ColumnClass column = new ColumnClass();
column.ParentCatalog = cat;
column.Type = ADOX.DataTypeEnum.adInteger;//设置字段类型
column.Name = "indexno";
column.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adInteger, 0);
*/

/*ADOX.ColumnClass column = new ColumnClass();
column.ParentCatalog = cat;
column.Type = ADOX.DataTypeEnum.adInteger;//设置字段类型
column.Name = "indexno";
column.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
table.Columns.Append(column, DataTypeEnum.adInteger, 0);*/

// 增加一个文本字段
int size = mdbHead.Count;
for (int i = 0; i < size; i++)
{
//增加一个文本字段
ADOX.ColumnClass col2 = new ADOX.ColumnClass();
col2.ParentCatalog = cat;
col2.Name = mdbHead[i].ToString(); //列的名称
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
table.Columns.Append(col2, ADOX.DataTypeEnum.adVarWChar, 500);
}

//ALTER TABLE user ALTER COLUMN userinfo Memo
//设置主键
table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "indexno", "", "");
cat.Tables.Append(table); //这句把表加入数据库(非常重要) ,C#操作Access之创建表
table = null;
cat = null;
connection.Close();
}
catch (Exception ex)
{
logger.Error("创建表出错:" + ex.Message);
logger.Flush();
}
}
#endregion

/// <summary>
/// 修改content类型为备注类型（content字段类型太长，只能用备注类型）
/// </summary>
/// <param name="oleDBconn"></param>
/// <returns></returns>
public static bool UpdateType(OleDbConnection oleDBconn)
{
OleDbCommand updateCommand = oleDBconn.CreateCommand();
updateCommand.CommandText = "ALTER TABLE mailtable ALTER COLUMN content Memo";
int row = updateCommand.ExecuteNonQuery();//这里不知道是不是用这个方法，虽然最好返回0.但是发现文件类型确实修改为备注类型
if (row > 0)
{
return true;
}
else
{
return false;
}
}
}

2.调用方法，并导出数据到dmb文件

if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + ".mdb";
string fullpathMDB = Path.Combine(filePath, filename);
if (!File.Exists(fullpathMDB))
{
MdbOperate.creatMDB(fullpathMDB);
}
oleDBconn = new OleDbConnection(mdbConn + fullpathMDB);
oleDBconn.Open();
//判断数据库是否存在表mailtable
if (!MdbOperate.GetTables(oleDBconn))
{
string table_name = "mailtable";
ArrayList col_names = new ArrayList();
col_names.Add("indexno");
col_names.Add("outputtime");//添加列
col_names.Add("sender");
col_names.Add("email");
col_names.Add("tel");
col_names.Add("adds");
col_names.Add("zipcode");
col_names.Add("receiver");
col_names.Add("title");
col_names.Add("strIPAddr");
col_names.Add("time");
col_names.Add("content");
MdbOperate.CreateMDBTable(fullpathMDB, table_name, col_names);//创建表
oleDBconn.Close();
oleDBconn.Open();
}
//查询信息并将数据填写到新建的mailtable表中
List<MailEntity> list = new List<MailEntity>();
int totalCount = 0;
list = DataBindFile(this.StartCalendar.Text.ToShortDateString(), this.EndCalendar.Text.ToShortDateString(), out totalCount, true);//从sql数据库查询数据
try
{
if (list.Count > 0)
{
foreach (var item in list)
{
OleDbCommand insertCommand = oleDBconn.CreateCommand();
insertCommand.CommandText = "insert into mailtable(indexno,outputtime,sender,email,tel,adds,zipcode,receiver,title,strIPAddr,[time],content) values (?,?,?,?,?,?,?,?,?,?,?,?)";//使用'？'代替，相当于c#中的占位符'{0},{1}...'
//修改字段为备注类型
bool m = MdbOperate.UpdateType(oleDBconn);
insertCommand.Parameters.Add("indexno", OleDbType.Char, 500);
insertCommand.Prepare();
insertCommand.Parameters[0].Value = item.indexno;

insertCommand.Parameters.Add("outputtime", OleDbType.DBDate, 500);
insertCommand.Prepare();
insertCommand.Parameters[1].Value = item.outputtime;

insertCommand.Parameters.Add("sender", OleDbType.Char, 500);
insertCommand.Prepare();
insertCommand.Parameters[2].Value = item.sender;

insertCommand.Parameters.Add("email", OleDbType.Char, 500);
insertCommand.Prepare();
insertCommand.Parameters[3].Value = item.email;

insertCommand.Parameters.Add("tel", OleDbType.Char, 500);
insertCommand.Prepare();
insertCommand.Parameters[4].Value = item.tel;

insertCommand.Parameters.Add("adds", OleDbType.Char, 500);
insertCommand.Prepare();
insertCommand.Parameters[5].Value = item.adds;

insertCommand.Parameters.Add("zipcode", OleDbType.Char, 500);
insertCommand.Prepare();
insertCommand.Parameters[6].Value = item.zipcode;

insertCommand.Parameters.Add("receiver", OleDbType.Char, 500);
insertCommand.Prepare();
insertCommand.Parameters[7].Value = item.receiver;

insertCommand.Parameters.Add("title", OleDbType.Char, 500);
insertCommand.Prepare();
insertCommand.Parameters[8].Value = item.title;

insertCommand.Parameters.Add("strIPAddr", OleDbType.Char, 500);
insertCommand.Prepare();
insertCommand.Parameters[9].Value = item.strIPAddr;

insertCommand.Parameters.Add("time", OleDbType.DBDate, 500);
insertCommand.Prepare();
insertCommand.Parameters[10].Value = item.time;

insertCommand.Parameters.Add("content", OleDbType.VarWChar, 8000);
insertCommand.Prepare();
insertCommand.Parameters[11].Value = item.content;

Int32 row = insertCommand.ExecuteNonQuery();
if (row > 0)
{
logger.Info(item.title.ToString() + "导出成功");
logger.Flush();
}
}
}
}
catch (Exception ex)
{
logger.Error("导出数据到mdb文件出错：" + ex.Message);
logger.Flush();
throw ex;
}

 

注意：遇到The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine 错误，请下载Access驱动程序安装即可，之前遇到这个问题，下载了驱动安装了一哈，就行了。本地环境是Win7 64位系统+iis7+vs2010 ，服务器window server2003 64位系统，项目编译的时候是any cpu。