[添加COM组件]
A: Microsoft ADO Ext. 2.8 for DDL and Security
B: Microsoft ActiveX Data Objects 2.8 Library
[添加头文件]
using System.Data.OleDb;
using System.Data;
using ADOX;
using System.IO;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;
using ADOX;
using System.IO;
using System.Xml.Linq;
using System.Diagnostics;
namespace CsLibAccess
{
public class AccessHelper
{
private static OleDbConnection accessConnection; //Access数据库连接
private static OleDbCommand m_ole_Command = null;
private DataTable m_datatable = new DataTable();
/// 构造函数
public AccessHelper()
{
}
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="fileDBPath">数据库路径</param>
/// <param name="openpwd">数据库密码</param>
/// <returns></returns>
public bool CreateAccessDb(string fileDBPath, string openpwd)//创建数据库
{
ADOX.Catalog catalog = new Catalog();
if (!File.Exists(fileDBPath))
{
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileDBPath + ";Jet OLEDB:Database password=" + openpwd);
}
catch (System.Exception e)
{
Trace.TraceWarning("创建Access数据库出错,测试数据不能保存");
return false;
}
}
return true;
}
/// <summary>
/// 创建数据库表格
/// </summary>
/// <param name="fileDBPath">数据库路径</param>
/// <param name="openpwd">数据库密码</param>
/// <param name="tableName">数据库表格名称</param>
/// <param name="columsKey">表格字段</param>
/// <returns>成功返回1</returns>
public bool CreateAccessTable(string fileDBPath, string openpwd, string tbName, List<string> columsKey)//创建表
{
ADOX.Catalog catalog = new Catalog();
//数据库文件不存在则创建
if (!File.Exists(fileDBPath))
{
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileDBPath + ";Jet OLEDB:Database password=" + openpwd);
}
catch (System.Exception e)
{
Trace.TraceWarning("创建Access表出错", e);
return false;
}
}
ADODB.Connection cn = new ADODB.Connection();
try
{
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileDBPath + ";Jet OLEDB:Database password=" + openpwd, null, null, -1);
}
catch (System.Exception ex)
{
Trace.TraceWarning("Access连接打开失败", ex);
return false;
}
catalog.ActiveConnection = cn;
accessConnection = new OleDbConnection(cn.ConnectionString);
try
{
accessConnection.Open();
var exists = accessConnection.GetSchema("Tables", new string[4] { null, null, tbName, "TABLE" }).Rows.Count > 0;
if (exists)
{
Trace.TraceWarning("表格已存在...", tbName);
accessConnection.Close();
cn.Close();
return false;
}
}
catch (System.Exception ex)
{
Trace.TraceWarning("Access连接打开失败", ex);
return false;
}
ADOX.Table table = new ADOX.Table();
table.ParentCatalog = catalog;
table.Name = tbName;
foreach (var column in columsKey)
{
ADOX.ColumnClass col = new ADOX.ColumnClass();
col.ParentCatalog = catalog;
col.Name = column;
col.Attributes = ColumnAttributesEnum.adColNullable; //允许空值
table.Columns.Append(col, DataTypeEnum.adVarWChar, 50); //默认数据类型和字段大小
}
catalog.Tables.Append(table);
cn.Close();
return true;
}
/// <summary>
/// 通过字典方式追加数据
/// </summary>
/// <param name="fileDBPath">数据库路径</param>
/// <param name="openpwd">数据库密码</param>
/// <param name="tableName">数据库表格名称</param>
/// <param name="data"></param>
/// <returns></returns>
public bool AppendData2Access(string fileDBPath, string openpwd, string tableName, Dictionary<string, object> data)
{
ADOX.Catalog catalog = new Catalog();
//数据库文件不存在则创建
if (!File.Exists(fileDBPath))
{
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileDBPath + ";Jet OLEDB:Database password=" + openpwd);
}
catch (System.Exception e)
{
Trace.TraceWarning("创建Access库出错", e);
return false;
}
}
ADODB.Connection cn = new ADODB.Connection();
try
{
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileDBPath + ";Jet OLEDB:Database password=" + openpwd, null, null, -1);
}
catch (System.Exception ex)
{
Trace.TraceWarning("Access连接打开失败", ex);
return false;
}
catalog.ActiveConnection = cn;
accessConnection = new OleDbConnection(cn.ConnectionString);
try
{
accessConnection.Open();
var exists = accessConnection.GetSchema("Tables", new string[4] { null, null, tableName, "TABLE" }).Rows.Count > 0;
if (!exists)
{
Trace.TraceWarning("表格不存在...", tableName);
accessConnection.Close();
cn.Close();
return false;
}
}
catch (System.Exception ex)
{
Trace.TraceWarning("Access连接打开失败", ex);
cn.Close();
return false;
}
if (data.Count > 0)
{
string fields = null;
string values = null;
string sql = null;
foreach (var item in data)
{
fields += item.Key.ToString() + ',';
values += string.Format("'{0}'", item.Value.ToString()) + ',';
}
fields = fields.Remove(fields.Length - 1, 1);
values = values.Remove(values.Length - 1, 1);
sql = string.Format("insert into {0} ({1}) values ({2})", tableName, fields, values);
OleDbCommand cmd = new OleDbCommand(sql, accessConnection);
cmd.ExecuteNonQuery();
accessConnection.Close();
cn.Close();
}
return true;
}
/// <summary>
/// 从数据库里面获取数据
/// </summary>
/// <param name="fileDBPath">数据库路径</param>
/// <param name="openpwd">数据库密码</param>
/// <param name="strSql">sql语句</param>
/// <returns>返回DataTable 数据</returns>
public DataTable GetDataTableFromDB(string fileDBPath, string openpwd, string strSql)
{
ADOX.Catalog catalog = new Catalog();
//数据库文件不存在则创建
if (!File.Exists(fileDBPath))
{
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileDBPath + ";Jet OLEDB:Database password=" + openpwd);
}
catch (System.Exception e)
{
Trace.TraceWarning("创建Access库出错", e);
return null;
}
}
ADODB.Connection cn = new ADODB.Connection();
try
{
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileDBPath + ";Jet OLEDB:Database password=" + openpwd, null, null, -1);
}
catch (System.Exception ex)
{
Trace.TraceWarning("Access连接打开失败", ex);
return null;
}
catalog.ActiveConnection = cn;
accessConnection = new OleDbConnection(cn.ConnectionString);
try
{
accessConnection.Open(); //打开连接
if (accessConnection.State == ConnectionState.Closed)
{
return null;
}
OleDbDataAdapter da = new OleDbDataAdapter(strSql, accessConnection); //创建适配对象
da.Fill(m_datatable); //用适配对象填充表对象
accessConnection.Close();
cn.Close();
}
catch (System.Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
if (accessConnection.State != ConnectionState.Closed)
{
accessConnection.Close();
}
}
return m_datatable;
}
/// <summary>
/// 执行sql语句 需在确定表格存在的情况下执行
/// </summary>
/// <param name="fileDBPath">数据库路径</param>
/// <param name="openpwd">数据库密码</param>
/// <param name="strSql">sql语句</param>
/// <returns>成功返回1 </returns>
public int ExcuteSql(string fileDBPath, string openpwd, string strSql)
{
int nResult = 0;
ADOX.Catalog catalog = new Catalog();
//数据库文件不存在则创建
if (!File.Exists(fileDBPath))
{
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileDBPath + ";Jet OLEDB:Database password=" + openpwd);
}
catch (System.Exception e)
{
Trace.TraceWarning("创建Access库出错", e);
return 0;
}
}
ADODB.Connection cn = new ADODB.Connection();
try
{
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileDBPath, null, null, -1);
}
catch (System.Exception ex)
{
Trace.TraceWarning("Access连接打开失败", ex);
return 0;
}
catalog.ActiveConnection = cn;
accessConnection = new OleDbConnection(cn.ConnectionString);
try
{
accessConnection.Open(); //打开数据库连接
if (accessConnection.State == ConnectionState.Closed)
{
accessConnection.Close();
accessConnection.Dispose();
cn.Close();
return nResult;
}
m_ole_Command = new OleDbCommand(strSql, accessConnection);
nResult = m_ole_Command.ExecuteNonQuery();
}
catch (System.Exception e)
{
Console.WriteLine(e.ToString());
accessConnection.Close();
accessConnection.Dispose();
cn.Close();
return nResult;
}
finally
{
if (accessConnection.State != ConnectionState.Closed)
{
accessConnection.Close();
}
}
accessConnection.Close();
accessConnection.Dispose();
cn.Close();
return nResult;
}
}
}