Access数据库文件操作辅助类
public class AccessHelper {
//Jet OLEDB:Engine Type Jet x.x Format MDB Files
//1 JET10
//2 JET11
//3 JET2X
//4 JET3X
//5 JET4X
/// <summary> /// 新建带密码的空Access 2000 数据库 /// </summary> /// <param name="mdbFilePath">数据库文件路径</param> /// <param name="password">数据库密码</param> /// <returns>字符0为操作成功，否则为失败异常消息。</returns> public static string CreateMDB(string mdbFilePath, string password) { try { string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;"; if (password == null || password.Trim() == "") { connStr += "Data Source=" + mdbFilePath; } else { connStr += "Jet OLEDB:Database Password=" + password + ";Data Source=" + mdbFilePath; } object objCatalog = Activator.CreateInstance(Type.GetTypeFromProgID("ADOX.Catalog")); object[] oParams = new object[] { connStr }; objCatalog.GetType().InvokeMember("Create", BindingFlags.InvokeMethod, null, objCatalog, oParams); Marshal.ReleaseComObject(objCatalog); objCatalog = null; return "0"; } catch (Exception exp) { return exp.Message; } } /// <summary> /// 新建空的Access数据库 /// </summary> /// <param name="mdbFilePath">数据库文件路径</param> /// <returns>字符0为操作成功，否则为失败异常消息。</returns> public static string CreateMDB(string mdbFilePath) { return CreateMDB(mdbFilePath, null); } /// <summary> /// 压缩带密码Access数据库 /// </summary> /// <param name="mdbFilePath">数据库文件路径</param> /// <param name="password">数据库密码</param> /// <returns>字符0为操作成功，否则为失败异常消息。</returns> public static string CompactMDB(string mdbFilePath, string password) { string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;"; string connStrTemp = connStr; string tmpPath = mdbFilePath + ".tmp"; if (password == null || password.Trim() == "") { connStr += "Data Source=" + mdbFilePath; connStrTemp += "Data Source=" + tmpPath; } else { connStr += "Jet OLEDB:Database Password=" + password + ";Data Source=" + mdbFilePath; connStrTemp += "Jet OLEDB:Database Password=" + password + ";Data Source=" + mdbFilePath + ".tmp"; } string strRet = ""; try { object objJRO = Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine")); object[] oParams = new object[] { connStr, connStrTemp }; objJRO.GetType().InvokeMember("CompactDatabase", BindingFlags.InvokeMethod, null, objJRO, oParams); Marshal.ReleaseComObject(objJRO); objJRO = null; } catch (Exception exp) { strRet = exp.Message; } try { System.IO.File.Delete(mdbFilePath); System.IO.File.Move(tmpPath, mdbFilePath); } catch (Exception expio) { strRet += expio.Message; } return (strRet == "") ? "0" : strRet; } /// <summary> /// 压缩没有带密码Access数据库 /// </summary> /// <param name="mdbFilePath">数据库文件路径</param> /// <returns>字符0为操作成功，否则为失败异常消息。</returns> public static string CompactMDB(string mdbFilePath) { return CompactMDB(mdbFilePath, null); } /// <summary> /// 设置Access数据库的访问密码 /// </summary> /// <param name="mdbFilePath">数据库文件路径</param> /// <param name="oldPwd">旧密码</param> /// <param name="newPwd">新密码</param> /// <returns>字符0为操作成功，否则为失败异常消息。</returns> public static string SetMDBPassword(string mdbFilePath, string oldPwd, string newPwd) { string connStr = string.Concat("Provider=Microsoft.Jet.OLEDB.4.0;", "Mode=Share Deny Read|Share Deny Write;", //独占模式 "Jet OLEDB:Database Password=" + oldPwd + ";Data Source=" + mdbFilePath); using (OleDbConnection conn = new OleDbConnection(connStr)) { try { conn.Open(); //如果密码为空时，请不要写方括号，只写一个null即可 string sqlOldPwd = (oldPwd == null || oldPwd.Trim() == "") ? "null" : "[" + oldPwd + "]"; string sqlNewPwd = (newPwd == null || newPwd.Trim() == "") ? "null" : "[" + newPwd + "]"; OleDbCommand cmd = new OleDbCommand(string.Concat("ALTER DATABASE PASSWORD ", sqlNewPwd, " ", sqlOldPwd), conn); cmd.ExecuteNonQuery(); conn.Close(); return "0"; } catch (Exception exp) { return exp.Message; } } } /// <summary> /// 列出Access 2000 数据库的表名称 /// </summary> /// <param name="mdbFilePath">数据库文件路径</param> /// <param name="password">数据库密码</param> /// <returns></returns> public static List<string> ListTables(string mdbFilePath, string password) { List<string> list = new List<string>(); string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;"; if (password == null || password.Trim() == "") { connStr += "Data Source=" + mdbFilePath; } else { connStr += "Jet OLEDB:Database Password=" + password + ";Data Source=" + mdbFilePath; } using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); for (int i = 0; i < schemaTable.Rows.Count; i++) { string tablename = schemaTable.Rows[i]["TABLE_NAME"].ToString(); list.Add(tablename); } } return list; } /// <summary> /// 列出Access2000数据库的表字段 /// </summary> /// <param name="mdbFilePath">数据库文件路径</param> /// <param name="password">数据库密码</param> /// <param name="tableName">表名称</param> /// <returns>返回字段名称和对应类型的字典数据</returns> public static Dictionary<string, string> ListColumns(string mdbFilePath, string password, string tableName) { Dictionary<string, string> list = new Dictionary<string, string>(); string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;"; if (password == null || password.Trim() == "") { connStr += "Data Source=" + mdbFilePath; } else { connStr += "Jet OLEDB:Database Password=" + password + ";Data Source=" + mdbFilePath; } using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); DataTable schemaTable = GetReaderSchema(tableName, conn); foreach (DataRow dr in schemaTable.Rows) { string columnName = dr["ColumnName"].ToString(); string datatype = ((OleDbType)dr["ProviderType"]).ToString();//对应数据库类型 string netType = dr["DataType"].ToString();//对应的.NET类型，如System.String list.Add(columnName, netType); } } return list; } private static DataTable GetReaderSchema(string tableName, OleDbConnection connection) { DataTable schemaTable = null; IDbCommand cmd = new OleDbCommand(); cmd.CommandText = string.Format("select * from [{0}]", tableName); cmd.Connection = connection; using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly)) { schemaTable = reader.GetSchemaTable(); } return schemaTable; } }
