C#操作Access資料庫的方法

//取得連接配接

public OleDbConnection getConn()

{

   ConnectDatabase connstr=new ConnectDatabase();

   string connStr=connstr.GetConnectionString();

   OleDbConnection oledb=new OleDbConnection(connStr);

   return oledb;

}

（1）采用OleDbCommand,OleDbDataReader通路資料庫

1.查詢

public User getUserFromName(string Searchname)

{

   User tempUser=new User();

   try

   {

    OleDbConnection oleconn=getConn();//資料庫連接配接

    string strSel="select * from MyUser where UserName='"+

Searchname+"'";//查詢語句

    OleDbCommand myCommand=new OleDbCommand(strSel,oleconn);//查詢指令

    oleconn.Open();//打開資料庫連接配接

    OleDbDataReader reader;

    reader=myCommand.ExecuteReader();//執行查詢指令，傳回記錄集

    if(reader.Read())

    {

     tempUser.ID=(int)reader["UserID"];

     tempUser.Name=reader["UserName"].ToString();

     tempUser.Salary=(float)reader["UserSalary"];

     tempUser.Password=reader["UserPassword"].ToString();

     tempUser.Memo=reader["UserMemo"].ToString();

     tempUser.Birthday=(DateTime)reader["UserBirthday"];

     tempUser.Address=reader["UserAddress"].ToString();

    }

    else

    {

     throw new Exception("沒有記錄");

    }

    reader.Close();//關閉記錄集

    oleconn.Close();//關閉連接配接

   }

   catch(Exception e)

   {

    throw new Exception("打開資料庫出錯"+e.Message);

   }

   return tempUser;

}

   2.插入記錄

public void InsertUser(User insertuser)

{

   try

   {

    OleDbConnection oleconn=getConn();//資料庫連接配接

    oleconn.Open();//打開資料庫連接配接

    string strSel="insert into [MyUser]([UserName],[UserPassword],

[UserSalary],[UserAddress],[UserBirthday],[UserMemo])"; //插入語句

    strSel+=" values

('"+insertuser.Name+"','"+insertuser.Password+"',"+insertuser.Salary.ToSt

ring();

    strSel+=",'"+insertuser.Address+"',#"+insertuser.Birthday.ToString()

+"#,'"+insertuser.Memo+"')";

    OleDbCommand myCommand=new OleDbCommand(strSel,oleconn);//查詢指令

    myCommand.ExecuteNonQuery();

    oleconn.Close();//關閉連接配接

   }

   catch(Exception e)

   {

    throw new Exception("打開資料庫出錯"+e.Message);

   }

}

3.删除記錄

public void DeleteUser(int m_id)

{

   try

   {

    OleDbConnection oleconn=getConn();

    oleconn.Open();

    string strSel="Delete From [Myuser] where UserID="+m_id.ToString();

    OleDbCommand myCommand=new OleDbCommand(strSel,oleconn);

    myCommand.ExecuteNonQuery();

    oleconn.Close();

   }

   catch(Exception e)

   {

    throw new Exception("删除記錄出錯"+e.Message);

   }

}

（2）采用OleDbDataAdapter,OleDbCommandBuilder,DataSet,DataTable,DataRow訪

問資料庫

添加記錄如下

public void InsertUserA(User insertUser)

{

   using(OleDbConnection conn=getConn())

   {

    OleDbDataAdapter adapter = new OleDbDataAdapter();

    string queryString="Select * from MyUser order by UserID";

    adapter.SelectCommand = new OleDbCommand(queryString, conn);

    OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

               // builder.QuotePrefix="[";

               // builder.QuoteSuffix="]";

    conn.Open();

    DataSet users = new DataSet();

    adapter.Fill(users,"MyUser");

    DataTable dt=new DataTable();

    dt=users.Tables["MyUser"];

    DataRow r=dt.NewRow();

    r["UserName"]=insertUser.Name;

    r["UserPassword"]=insertUser.Password;

    r["UserAddress"]=insertUser.Address;

    r["UserSalary"]=insertUser.Salary;

    r["UserBirthday"]=insertUser.Birthday;

    r["UserMemo"]=insertUser.Memo;

    dt.Rows.Add(r);

    adapter.Update(users, "MyUser");

   }

}

需要注意字段不能和關鍵字相同，否則會出現Insert into出錯的提示。解決辦法在

前一篇

(3)采用參數化查詢的方式

public class AccessUtil

{

public AccessUtil()

{

}

        private string connString;

        public string ConnString

        {

            get { return connString; }

            set { connString = value; }

        }

        public AccessUtil(string connstr)

        {

            this.connString = connstr;

        }

        //帶參數的插入語句,傳回值為id關鍵字的值,單條插入語句

        public int ExecuteInsert(string SQL, OleDbParameter[]

parameters)

        {

            using(OleDbConnection conn=new OleDbConnection(connString))

            {

                OleDbCommand cmd = new OleDbCommand(SQL, conn);

                try

                {

                    conn.Open();

                    if (parameters!=null)

                    {

                        cmd.Parameters.AddRange(parameters);

                    }

                    cmd.ExecuteNonQuery();

                    cmd.CommandText = @"Select @@identity";

                    int value = Int32.Parse(cmd.ExecuteScalar().ToString

());

                    return value;

                }

                catch (System.Exception e)

                {

                    throw e;

                }

            }

        }

        //不帶參數的插入語句，傳回值為關鍵字的值

        public int ExecuteInsert(string SQL)

        {

            return ExecuteInsert(SQL, null);

        }

        //帶參數的插入、删除、更新語句，傳回受影響的記錄的個數

        public int ExecuteNoQuery(string SQL, OleDbParameter[]

parameters)

        {

            using(OleDbConnection conn=new OleDbConnection(connString))

            {

                conn.Open();

                OleDbCommand cmd = new OleDbCommand(SQL, conn);

                try

                {  

                    if (parameters!=null)

                    {

                        cmd.Parameters.AddRange(parameters);

                    }

                    int rows=cmd.ExecuteNonQuery();

                    return rows;

                }

                catch (System.Exception e)

                {

                    throw e;

                }

            }

        }

        //不帶參數的插入、删除、更新語句，傳回受影響的記錄的個數

        public int ExecuteNoQuery(string SQL)

        {

            return ExecuteNoQuery(SQL, null);

        }

        //帶參數的查詢語句，傳回所查詢到的記錄集

        public DataSet ExecuteQuery(string SQL, OleDbParameter[]

parameters)

        {

            using(OleDbConnection conn=new OleDbConnection(connString))

            {

                DataSet ds = new DataSet();

                try

                {

                    conn.Open();

                    OleDbDataAdapter da = new OleDbDataAdapter(SQL,

conn);

                    if (parameters != null)

                    {

                        da.SelectCommand.Parameters.AddRange(parameters);

                    }

                    da.Fill(ds, "ds");

                }

                catch(System.Exception e)

                {

                    throw e;

                }

                return ds;

            }

        }

        //不帶參數的查詢，傳回所查詢到的記錄集

        public DataSet ExecuteQuery(string SQL)

        {

            return ExecuteQuery(SQL, null);

        }

}

class ManageUser

    {

        //Access資料庫工具對象

        AccessUtil accessutil = new AccessUtil

(ConnectDatabase.GetConnectionString());

        public ArrayList GetAllUserArr()//獲得User表中的所有記錄，存儲進

ArrayList。

        {

            string SQL = "select * from MyUser order by ID";

            DataSet ds=accessutil.ExecuteQuery(SQL);//傳回的臨時表的名稱

為“ds”

            ArrayList arr = DataTable2ArrayList(ds.Tables["ds"]);

            return arr;

        }

        public DataSet GetAllUserDataSet()//存儲成DataSet

        {

            string SQL = "select * from MyUser order by ID";

            DataSet ds = accessutil.ExecuteQuery(SQL);

            return ds;

        }

       private User DataRow2User(DataRow dr)//将資料表中的一條記錄轉換為

一個User類的執行個體

       {

               User user = new User();

               user.ID = Int32.Parse(dr["ID"].ToString());

               user.Name = dr["Name"].ToString();

               user.Address = dr["Address"].ToString();

               user.Birthday = Convert.ToDateTime(dr

["Birthday"].ToString());

               user.Memo = dr["Memo"].ToString();

               user.Salary =(float) Convert.ToDouble(dr

["Salary"].ToString());

               user.Password = dr["Password"].ToString();

               return user;

       }

       private ArrayList DataTable2ArrayList(DataTable dt)//将一個表中的

記錄轉化為ArrayList對象

       {

           ArrayList tempArr = new ArrayList();

           DataTableReader dr = new DataTableReader(dt);

           while(dr.Read())

           {

           User user = new User();

           user.ID = Int32.Parse(dr["ID"].ToString());

           user.Name = dr["Name"].ToString();

           user.Address = dr["Address"].ToString();

           user.Birthday = Convert.ToDateTime(dr["Birthday"].ToString());

           user.Memo = dr["Memo"].ToString();

           user.Salary = (float)Convert.ToDouble(dr["Salary"].ToString

());

           user.Password = dr["Password"].ToString();

           tempArr.Add(user);

           }

           return tempArr;

       }

        public DataSet GetUserByName(string name)

        {

            String SQL = "Select * from MyUser where Name=?";

            OleDbParameter[] parameter = new OleDbParameter[1];

            parameter[0] = new OleDbParameter("@Name",

OleDbType.VarChar);

            parameter[0].Value = name;

            DataSet dt= accessutil.ExecuteQuery(SQL, parameter);

            return dt;

        }

        public int InsertUser(User inUser)

        {

            String SQL = "insert into [MyUser]([Name],[Password],

[Salary],[Address],[Birthday],[Memo]) values(?,?,?,?,?,?)";

            OleDbParameter[] parameters = new OleDbParameter[6];

            parameters[0] = new OleDbParameter("@Name",

OleDbType.VarChar);

            parameters[0].Value = inUser.Name;

            parameters[1] = new OleDbParameter("@Password",

OleDbType.VarChar);

            parameters[1].Value = inUser.Password;

            parameters[2] = new OleDbParameter("@Salary",

OleDbType.Single);

            parameters[2].Value = inUser.Salary;

            parameters[3] = new OleDbParameter("@Address",

OleDbType.VarChar);

            parameters[3].Value = inUser.Address;

            parameters[4] = new OleDbParameter("@Birthday",

OleDbType.Date);

            parameters[4].Value = inUser.Birthday;

            parameters[5] = new OleDbParameter("@Memo",

OleDbType.VarChar);

            parameters[5].Value = inUser.Memo;

            return accessutil.ExecuteInsert(SQL, parameters);

        }

        public void DelUserById(int id)

        {

            String SQL = "DELETE FROM [MyUser] where ID=?";

            OleDbParameter[] parameters = new OleDbParameter[1];

            parameters[0] = new OleDbParameter("@ID", OleDbType.Integer);

            parameters[0].Value = id;

            accessutil.ExecuteNoQuery(SQL, parameters);

        }

        public void UpdateUser(User userupdate)

        {

            String SQL = "update [MyUser] Set [Name]=?,[Password]=?,

[Salary]=?,[Address]=?,[Birthday]=?,[Memo]=? where [ID]=?";

            OleDbParameter[] parameters = new OleDbParameter[7];

            parameters[0] = new OleDbParameter("@Name",

OleDbType.VarChar);

            parameters[0].Value = userupdate.Name;

            parameters[1] = new OleDbParameter("@Password",

OleDbType.VarChar);

            parameters[1].Value = userupdate.Password;

            parameters[2] = new OleDbParameter("@Salary",

OleDbType.Single);

            parameters[2].Value = userupdate.Salary;

            parameters[3] = new OleDbParameter("@Address",

OleDbType.VarChar);

            parameters[3].Value = userupdate.Address;

            parameters[4] = new OleDbParameter("@Birthday",

OleDbType.Date);

            parameters[4].Value = userupdate.Birthday;

            parameters[5] = new OleDbParameter("@Memo",

OleDbType.VarChar);

            parameters[5].Value = userupdate.Memo;

            parameters[6] = new OleDbParameter("@ID", OleDbType.Integer);

            parameters[6].Value = userupdate.ID;

            accessutil.ExecuteNoQuery(SQL, parameters);

        }

    }

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.OleDb;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace Andy

{

    public partial class Form1 : Form

    {

        int nRecordPointer,nRecordCount;

        OleDbConnection olecn = new OleDbConnection();

        OleDbDataAdapter oleda=new OleDbDataAdapter();

        OleDbCommand selectcmd = new OleDbCommand();

        OleDbCommand updatecmd = new OleDbCommand();

        DataSet oleds=new DataSet();

        public Form1()

        {

            InitializeComponent();

        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)

        {

            Application.Exit();

        }

        private void adsfaaToolStripMenuItem_Click(object sender, EventArgs e)

        {

            tabControl1.Visible = true;

            button1.Enabled = false;

            button2.Enabled = false;

            olecn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Andy\\C#\\Andy.accdb";

            selectcmd.Connection = olecn;

            updatecmd.Connection = olecn;

            oleda.SelectCommand = selectcmd;

            try

            {

                olecn.Open();

                selectcmd.CommandText = "select * from bank order by bankid";

                oleda.Fill(oleds);

                nRecordCount = oleds.Tables[0].Rows.Count;

            }

            catch (OleDbException oledbex)

            {

                MessageBox.Show(oledbex.Message);

            }

            finally

            {

                olecn.Close();

                nRecordPointer = 0;

                DisplayData();

            }

        }

        private void Form1_Load(object sender, EventArgs e)

        {

            tabControl1.Visible = false;

            nRecordPointer = 0;

            nRecordCount = 0;

            LockEdit();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            nRecordPointer = 0;

            button1.Enabled = false;

            button2.Enabled = false;

            button3.Enabled = true;

            button4.Enabled = true;

            DisplayData();

        }

        private void DisplayData()

        {

            textBox1.Text = oleds.Tables[0].Rows[nRecordPointer][0].ToString();

            textBox2.Text = oleds.Tables[0].Rows[nRecordPointer][1].ToString();

            textBox3.Text = oleds.Tables[0].Rows[nRecordPointer][2].ToString();

            textBox4.Text = oleds.Tables[0].Rows[nRecordPointer][3].ToString();

            textBox5.Text = oleds.Tables[0].Rows[nRecordPointer][4].ToString();

        }

        private void LockEdit()

        {

            textBox1.ReadOnly = true;

            textBox2.ReadOnly = true;

            textBox3.ReadOnly = true;

            textBox4.ReadOnly = true;

            textBox5.ReadOnly = true;

        }

        private void button3_Click(object sender, EventArgs e)

        {

            nRecordPointer = nRecordPointer + 1;

            if (nRecordPointer== (nRecordCount-1))

            {

                button3.Enabled = false;

                button4.Enabled = false;

            }

            button1.Enabled = true;

            button2.Enabled = true;

            DisplayData();

        }

        private void button2_Click(object sender, EventArgs e)

        {

            nRecordPointer = nRecordPointer - 1;

            if (nRecordPointer == 0)

            {

                button1.Enabled = false;

                button2.Enabled = false;

            }

            button3.Enabled = true;

            button4.Enabled = true;

            DisplayData();

        }

        private void button4_Click(object sender, EventArgs e)

        {

            nRecordPointer = nRecordCount - 1;

            button1.Enabled = true;

            button2.Enabled = true;

            button3.Enabled = false;

            button4.Enabled = false;

            DisplayData();

        }

        private void button5_Click(object sender, EventArgs e)

        {

        }

    }

}