ACCESS数据库增删改查

 [添加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;}}
}