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;
}
}
标签：return,ole,示例,C#,Access,connection,dt,null,string From： https://www.cnblogs.com/hailexuexi/p/18203751