C# 操作Access數據庫，導出數據到dmb文件

{
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="/> 

/// <summary>
///
/// </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

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

/// <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();//打開數據庫連接

cn.Open(sAccessConnection, null, null, -1);

//新建一個表
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);
*/

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);
}

//設置主鍵
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

/// （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;
}
}
}

{
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.Prepare();
insertCommand.Parameters[1].Value = item.outputtime;

insertCommand.Prepare();
insertCommand.Parameters[2].Value = item.sender;

insertCommand.Prepare();
insertCommand.Parameters[3].Value = item.email;

insertCommand.Prepare();
insertCommand.Parameters[4].Value = item.tel;

insertCommand.Prepare();
insertCommand.Parameters[5].Value = item.adds;

insertCommand.Prepare();
insertCommand.Parameters[6].Value = item.zipcode;

insertCommand.Prepare();
insertCommand.Parameters[7].Value = item.receiver;

insertCommand.Prepare();
insertCommand.Parameters[8].Value = item.title;

insertCommand.Prepare();
insertCommand.Parameters[9].Value = item.strIPAddr;

insertCommand.Prepare();
insertCommand.Parameters[10].Value = item.time;

insertCommand.Prepare();
insertCommand.Parameters[11].Value = item.content;

if (row > 0)
{
logger.Info(item.title.ToString() + "導出成功");
logger.Flush();
}
}
}
}
catch (Exception ex)
{
logger.Error("導出數據到mdb文件出錯：" + ex.Message);
logger.Flush();
throw ex;
}