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; } } }
博客内容主要用于日常学习记录,内容比较随意,如有问题,还需谅解!!!