当前位置： 首页 > news >正文

ACCESS数据库增删改查

news 2025/7/18 10:07:54

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

http://www.lryc.cn/news/120277.html

相关文章：

  • 动捕系统mockup_optitrack替换为VRPN传递信息
  • 【服务平台】Rancher运行和管理Docker和Kubernetes，提供管理生产中的容器所需的整个软件堆栈
  • 二叉树的完全性检验
  • 激活函数总结（六）：ReLU系列激活函数补充(RReLU、CELU、ReLU6)
  • tp5中的事务处理
  • 论文总结《Towards Evaluating the Robustness of Neural Networks(CW)》
  • 2024重庆邮电大学软件工程809题库（带答案）
  • 三种目标检测方法（基于传统数字图像处理的识别方法、基于传统机器学习的识别方法和基于深度学习的识别方法）的区别
  • 制造业为什么要建设数字化供应链
  • webrtc Thread 和 TaskQueue 的 应用和思考
  • 无涯教程-Perl - pos函数
  • 【腾讯云 Cloud Studio 实战训练营】使用Cloud Studio构建Java、Python项目
  • Java的Class类：每一个类都对应着一个Class对象
  • JavaScript预编译机制
  • 【ARM 嵌入式 编译系列 4.1 -- GCC 编译属性 likely与unlikely 学习】
  • 《算法竞赛·快冲300题》每日一题：“造电梯”
  • NSS [MoeCTF 2022]baby_file
  • 喜报！诚恒科技与赛时达科技达成BI金蝶云星空项目合作
  • Vscode python调试和运行环境设置
  • lua中执行luci.sys.call、luci.sys.exec、os.execute的区别
  • Python-OpenCV中的图像处理-模板匹配
  • 模拟队列(c++题解)
  • Redis_哨兵模式
  • Mysql中如果建立了索引，索引所占的空间随着数据量增长而变大，这样无论写入还是查询，性能都会有所下降，怎么处理？
  • MySQL 约束
  • unity实现角色体力功能【体力条+体力计算】
  • 【深度学习所有损失函数】在 NumPy、TensorFlow 和 PyTorch 中实现（1/2）
  • 七夕好物分享，哪些礼物适合送男/女朋友？这几款好物最为合适！
  • C语言学习系列--＞看淡指针（2）
  • Java基础篇--Character 类