asp.net连接access数据库进行添加更新删除查询操作

连接access数据库代码，写在一个单独的类里

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace AppWebDLL
{
    public class ConnApp
    {
        public OleDbConnection getCon() {
          
            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+System.Web.HttpContext.Current.Server.MapPath("App_Data//UserDB.mdb")+";Jet OLEDB:Database Password=admin";
            OleDbConnection objConnection = new OleDbConnection(strConnection);
            return objConnection;
        }
    }
}
我这习惯这样写的，在这里System.Web.HttpContext.Current.Server.MapPath是用来取得access的相对路径的，在这个类里想要使用System.Web.HttpContext.Current.Server.MapPath，先要引用system.web这个类。

 

 

增删改查代码，以我的类写的，这里要注意一个，如果你的字段和access的关键字一样，那么你就要在你的字段上加 [ ]，如password是access的关键字，如果你的字段有这个，你在做操作的时候就要这样写[password]

 

using System;using System.Collections.Generic;using System.Linq;using System.Text;using AppWebDLL;using System.Data;using System.Data.OleDb;using System.Collections;namespace AppBLL{ public class Land { ConnApp capp = new ConnApp(); OleDbConnection db; OleDbCommand olecom = null; //查询所有用户 public ArrayList GetAllUser() { ArrayList alluserlist = new ArrayList(); db = capp.getCon(); try { string sql = "select USERNAME from userinfo"; olecom = new OleDbCommand(sql,db); db.Open(); OleDbDataReader oler = olecom.ExecuteReader(CommandBehavior.CloseConnection); while (oler.Read()) { alluserlist.Add(oler.GetString(0)); } } catch (Exception e) { throw new Exception(e.Message); } return alluserlist; } //用户查询 public bool GetUser(string user,string password) { bool flg = false; db = capp.getCon(); try { string sql = string.Format("select USERNAME,[PASSWORD] from userinfo where USERNAME='{0}' and [PASSWORD]='{1}'",user,password); olecom = new OleDbCommand(sql, db); db.Open(); OleDbDataReader oler = olecom.ExecuteReader(CommandBehavior.CloseConnection); while (oler.Read()) { flg = true; } oler.Close(); } catch (OleDbException e) { throw new Exception(e.Message); } return flg; } //添加用户 public string CreatUser(string username,string password) { string str = ""; db = capp.getCon(); try { string sql1 = string.Format("select * from userinfo where USERNAME='{0}'", username); olecom = new OleDbCommand(sql1, db); db.Open(); OleDbDataReader oler = olecom.ExecuteReader(CommandBehavior.CloseConnection); if (oler.Read()) { str = "用户名已存在"; oler.Close(); } else { string sql = "insert into userinfo(USERNAME,[PASSWORD]) values('" + username + "','" + password + "')"; olecom = new OleDbCommand(sql, db); //db.Open(); int ret = olecom.ExecuteNonQuery(); str = "添加用户成功"; } } catch (OleDbException e) { throw new Exception(e.Message); } finally { olecom.Parameters.Clear(); db.Close(); } return str; } //删除用户信息 public string DelUser(string username, string password) { string str = ""; db = capp.getCon(); db.Open(); try { string sql = string.Format("delete from userinfo where USERNAME='{0}' and [PASSWORD]='{1}'",username,password); olecom = new OleDbCommand(sql,db); int res = olecom.ExecuteNonQuery(); if (res > 0) { str = "成功删除用户"; } else { string pass=""; string sql1 = "select * from userinfo where USERNAME='root'"; olecom = new OleDbCommand(sql1,db); OleDbDataReader oler = olecom.ExecuteReader(CommandBehavior.CloseConnection); while (oler.Read()) { pass = oler.GetString(2); } oler.Close(); if (password.Equals(pass)) { string sql2 = string.Format("delete from userinfo where USERNAME='{0}'", username); olecom = new OleDbCommand(sql2, db); db.Open(); int res1 = olecom.ExecuteNonQuery(); if (res1 > 0) { str = "成功删除用户"; } else { str = "删除失败，没有该用户或密码错误"; } } else { str = "删除失败，没有该用户或密码错误"; } } } catch (OleDbException e) { throw new Exception(e.Message); } finally { olecom.Parameters.Clear(); db.Close(); } return str; } //修改密码 public string ModifyUser(string username,string password,string passoword1) { string str = ""; db = capp.getCon(); try { string sql1 = "select * from userinfo where [PASSWORD]='"+passoword1+"' and USERNAME='"+username+"'"; olecom = new OleDbCommand(sql1, db); db.Open(); OleDbDataReader oler = olecom.ExecuteReader(CommandBehavior.CloseConnection); if (oler.Read()) { oler.Close(); string sql = "update userinfo set [PASSWORD]='" + password + "' where USERNAME='" + username + "'"; olecom = new OleDbCommand(sql, db); db.Open(); olecom.ExecuteNonQuery(); str = "密码修改成功"; } else { str = "用户名或原始密码错误"; oler.Close(); } } catch (OleDbException e) { throw new Exception(e.Message); } finally { olecom.Parameters.Clear(); db.Close(); } return str; }