C#连接Access数据库 查询和新增 示例
C#连接Access数据库 查询和新增 示例
项目中需要做一个写程序操作日志的需求，仅本机使用。这时Access数据库就是一个非常好的选择，简单，好用。
下面仅仅是一个示例，简单写出 查询 和 新增 等 C#操作 Access 数据库的 代码
效果图
clsDBHelperAccess.cs
using System; using System.Collections.Generic; using System.Text; using System.Data.OleDb; using System.Data; namespace myClass { class clsDBHelperAccess { private string conn_str = null; private OleDbConnection ole_connection = null; private OleDbCommand ole_command = null; private OleDbDataReader ole_reader = null; private DataTable dt = null; /// <summary> /// 构造函数 /// </summary> public clsDBHelperAccess() { conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\我的文档\Visual Studio 2008\Projects\AccessOperator\AccessOperator\bin\Debug\myDb.mdb'"; InitDB(); } /// <summary> /// 构造函数 /// </summary> /// <param name="db_path">数据库路径</param> public clsDBHelperAccess(string db_path) { conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + db_path + "'"; InitDB(); } private void InitDB() { ole_connection = new OleDbConnection(conn_str); //创建实例 ole_command = new OleDbCommand(); } /// <summary> /// 转换数据格式 /// </summary> /// <param name="reader">数据源</param> /// <returns>数据列表</returns> private DataTable ConvertOleDbReaderToDataTable(ref OleDbDataReader reader) { DataTable dt_tmp = null; DataRow dr = null; int data_column_count = 0; int i = 0; data_column_count = reader.FieldCount; dt_tmp = BuildAndInitDataTable(data_column_count); if (dt_tmp == null) { return null; } while (reader.Read()) { dr = dt_tmp.NewRow(); for (i = 0; i < data_column_count; ++i) { dr[i] = reader[i]; } dt_tmp.Rows.Add(dr); } return dt_tmp; } /// <summary> /// 创建并初始化数据列表 /// </summary> /// <param name="Field_Count">列的个数</param> /// <returns>数据列表</returns> private DataTable BuildAndInitDataTable(int Field_Count) { DataTable dt_tmp = null; DataColumn dc = null; int i = 0; if (Field_Count <= 0) { return null; } dt_tmp = new DataTable(); for (i = 0; i < Field_Count; ++i) { dc = new DataColumn(i.ToString()); dt_tmp.Columns.Add(dc); } return dt_tmp; }
/// <summary> /// 从数据库里面获取数据 /// </summary> /// <param name="strSql">查询语句</param> /// <returns>数据列表</returns> public DataTable GetDataTableFromDB(string strSql) { if (conn_str == null) { return null; } try { ole_connection.Open(); //打开连接 if (ole_connection.State == ConnectionState.Closed) { return null; } ole_command.CommandText = strSql; ole_command.Connection = ole_connection; ole_reader = ole_command.ExecuteReader(CommandBehavior.Default); dt = ConvertOleDbReaderToDataTable(ref ole_reader); ole_reader.Close(); ole_reader.Dispose(); } catch (System.Exception e) { //Console.WriteLine(e.ToString()); ///clsLogHelper.m_CreateErrorLogTxt("clsDBHelperAccess ", "GetDataTableFromDB", e.Message.ToString()); } finally { if (ole_connection.State != ConnectionState.Closed) { ole_connection.Close(); } } return dt; } /// <summary> /// 执行sql语句 /// </summary> /// <param name="strSql">sql语句</param> /// <returns>返回结果</returns> public int ExcuteSql(string strSql) { int nResult = 0; try { ole_connection.Open(); //打开数据库连接 if (ole_connection.State == ConnectionState.Closed) { return nResult; } ole_command.Connection = ole_connection; ole_command.CommandText = strSql; nResult = ole_command.ExecuteNonQuery(); } catch (System.Exception e) { //Console.WriteLine(e.ToString()); //clsLogHelper.m_CreateErrorLogTxt("clsDBHelperAccess ", "ExcuteSql", e.Message.ToString()); return nResult; } finally { if (ole_connection.State != ConnectionState.Closed) { ole_connection.Close(); } } return nResult; } } }
Form1.cs 窗体中的部分主要代码
private void butSelect_Click(object sender, EventArgs e) { DataTable dt = Select("2024-01-01 00:00:00", "2024-12-30 00:00:00"); if (dt == null) MessageBox.Show("查询信息发生异常!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); else if (dt.Rows.Count <= 0) { this.dataGridView1.DataSource = dt; MessageBox.Show("没有符合条件的信息!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } else { this.dataGridView1.DataSource = dt; this.lblRowCount.Text = dt.Rows.Count.ToString(); } } //按 时间 public DataTable Select(string strStartTime,string strStopTime) { try { string strSql = ""; string strCol = "*"; string strWhere = " ( date_time>='" + strStartTime + "' and date_time<='" + strStopTime + "') "; strSql = "select " + strCol + " from operation_log where " + strWhere; //return DbHelperSQL.Query(strSql).Tables[0]; string path = System.Windows.Forms.Application.StartupPath + @"\data\lansever_db.mdb"; myClass.clsDBHelperAccess Helper = new myClass.clsDBHelperAccess(path); return Helper.GetDataTableFromDB(strSql); } catch (System.Exception e) { return null; } } private void butAdd_Click(object sender, EventArgs e) { int intResult = Add(); if (intResult >= 1) { MessageBox.Show("添加成功!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("添加失败!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } //添加一条数据 public int Add() { try { string strId = System.Guid.NewGuid().ToString(); string strSize = "AAAAAA"; string strDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); string strButton = "BBBBB1"; string strType = "CCCCCC2"; string strRemarks = ""; string strSql = ""; strSql += "insert into operation_log (id,site,date_time,button,type,remarks "; strSql += " ) values( "; strSql += " '" + strId + "','" + strSize + "','" + strDateTime + "','" + strButton + "','" + strType + "','" + strRemarks + "' "; strSql += " )"; string path = System.Windows.Forms.Application.StartupPath + @"\data\lansever_db.mdb"; myClass.clsDBHelperAccess Helper = new myClass.clsDBHelperAccess(path); return Helper.ExcuteSql(strSql); } catch (System.Exception e) { return 0; } }
