实现效果
本辅助类主要是用来方便实现Access数据库文件操作，包括
- 新建Access数据库（可含密码）
- 压缩数据库
- 设置数据库密码
- 列出数据库表
- 列出数据库表字段
实现步骤
- 在代码引用相关的代码实现动态调用。
实现代码
辅助类库JetAccessUtil的相关方法定义
/// 新建带密码的空Access 2000 数据库
public static string CreateMDB(string mdbFilePath, string password)
/// 新建空的Access数据库
public static string CreateMDB(string mdbFilePath)
/// 压缩带密码Access数据库
public static string CompactMDB(string mdbFilePath, string password)
/// 压缩没有带密码Access数据库
public static string CompactMDB(string mdbFilePath)
/// 设置Access数据库的访问密码
public static string SetMDBPassword(string mdbFilePath, string oldPwd, string newPwd)
/// 列出Access 2000 数据库的表名称
public static List<string> ListTables(string mdbFilePath, string password)
/// 列出Access2000数据库的表字段
public static Dictionary<string, string> ListColumns(string mdbFilePath, string password, string tableName)
辅助类库的使用例子
string fileNoPass = Path.Combine(Path.GetTempPath(), "EmptyNoPass.mdb");
string filePass = Path.Combine(Path.GetTempPath(), "EmptyWithPass.mdb");
//创建不带密码的空数据库
JetAccessUtil.CreateMDB(fileNoPass);
//创建带密码的空数据库
JetAccessUtil.CreateMDB(filePass, "admin@laoguo.me");
//压缩不带密码的数据库
JetAccessUtil.CompactMDB(fileNoPass);
//压缩带密码的数据库
JetAccessUtil.CompactMDB(filePass, "admin@laoguo.me");
//重新设置数据库的密码
JetAccessUtil.SetMDBPassword(filePass, "admin@laoguo.me", "123456789");
//列出数据库的表名称
List<string> tableNameList = JetAccessUtil.ListTables(filePass, "123456789");
string strNameList = "";
foreach (string name in tableNameList)
{
strNameList += string.Format(",{0}", name);
}
if (!string.IsNullOrEmpty(strNameList))
{
MessageUtil.ShowTips(strNameList);
}
Process.Start(Path.GetTempPath());
附上源码
/// <summary>
/// Access数据库文件操作辅助类
/// </summary>
public class JetAccessUtil
{
//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;
}
/// <summary>
/// 查看数据表的Schema信息
/// </summary>
/// <param name="tableName">表名称</param>
/// <param name="connection">连接对象</param>
/// <returns></returns>
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;
}
}
网友评论