Access数据库——设计试图，可进行表字段设计
Access数据库——双击表，可维护数据
常用CRUD帮助方法如下所示：
using System.Data;
using System.Data.OleDb;
namespace DBHelper
{
public static class AccessHelper
{
//private static string connString = ConfigurationManager.ConnectionStrings["dbConnstr"].ConnectionString;
public static OleDbConnection GetConnection(string connString)
{
OleDbConnection conn = new OleDbConnection(connString);
return conn;
}
/// <summary>
/// 获取数据总条数
/// </summary>
/// <returns></returns>
public static int GetCount(string connString, string sql)
{
return (int)ExecuteScalar(connString, sql);
}
/// <summary>
/// 删除制定ID行数据
/// </summary>
/// <param name="ID"></param>
public static int DeleteByField(string connString, string tableName, string fieldName, string fieldValue)
{
//ExecuteNonQuery(connString,"delete from T_Person where ID=@ID", new OleDbParameter("@ID", fieldValue));
string sql = "delete from " + tableName + " where " + fieldName + "=@" + fieldName;
return ExecuteNonQuery(connString, sql, new OleDbParameter("@" + fieldName, fieldValue));
}
/// <summary>
/// 执行 Transact-SQL 语句并返回受影响的行数
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <param name="parameters">sql语句中的参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string connString, string sql, params OleDbParameter[] parameters)
{
using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters); //AddRange添加的是数组
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <param name="parameters">sql语句中的参数</param>
/// <returns></returns>
public static object ExecuteScalar(string connString, string sql, params OleDbParameter[] parameters)
{
using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//方法2
cmd.Parameters.AddRange(parameters); //AddRange添加的是数组
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 只用来执行查询结果比较少的sql
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <param name="parameters">sql语句中的参数</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string connString, string sql, params OleDbParameter[] parameters)
{
using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//方法2
cmd.Parameters.AddRange(parameters); //AddRange添加的是数组
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0]; //可以查询很多表，默认第一个
}
}
}
}
}
应用调用示例：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace AccessDemo
{
public class UserInfo
{
public string UserId { get; set; }
public string UserName { get; set; }
public string PassWord { get; set; }
public string NickName { get; set; }
}
}
窗体：
using DBHelper;
using System;
using System.Windows.Forms;
namespace AccessDemo
{
public partial class Form1 : Form
{
//office 版本不同对应连接串不同
private static string ConnString = "Provider= Microsoft.ACE.OLEDB.12.0;Jet OLEDB:DataBase Password = ''; Data Source = "
+ Application.StartupPath + "\\DB\\RFDB.accdb";
//string strExePath = Application.StartupPath + @"MedViewer.mdb"; //mdb";
//ConnString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + strExePath;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
this.dgvList.AutoGenerateColumns = false;
string sql = "select * from UserInfo";
//List<UserInfo> list = (List<UserInfo>)AccessHelper.ExecuteScalar(ConnString, sql);
var list = AccessHelper.ExecuteDataTable(ConnString, sql);
this.dgvList.DataSource = list;
}
private void btnAdd_Click(object sender, EventArgs e)
{
try
{
string sql = @"insert into [UserInfo] ([UserId],[UserName],[PassWord],[NickName]) values ("
+ "'" + this.txtUserId.Text + "',"
+ "'" + this.txtUserName.Text + "',"
+ "'" + this.txtPwd.Text + "',"
+ "'" + this.txtNickName.Text + "')";
int count = AccessHelper.ExecuteNonQuery(ConnString, sql);
MessageBox.Show("新增成功：" + count);
//UserId,UserName,PassWord,NickName
}
catch (Exception ex)
{
}
}
private void btnDel_Click(object sender, EventArgs e)
{
int count = AccessHelper.DeleteByField(ConnString, "UserInfo", "UserId", this.txtUserId.Text);
MessageBox.Show("删除成功:" + count);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
string sql = string.Format(@"update [UserInfo] set [PassWord] = '001' where [PassWord] ='{0}'", 1);
int count = AccessHelper.ExecuteNonQuery(ConnString, sql);
MessageBox.Show("修改成功：" + count);
}
private void btnQuery_Click(object sender, EventArgs e)
{
string sql = "select * from UserInfo";
var list = AccessHelper.ExecuteDataTable(ConnString, sql);
this.dgvList.DataSource = list;
}
}
}