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;

}