frmdataindex.cs

C sharp ACCESS数据库读取

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace ClassManage
{
    public partial class frmDataIndex : Form
    {
        ClassManage.EntityClass.CStudents students;
        private int pageIndex;
        private int flag;//0-null,1-edit,2-add
        public frmDataIndex()
        {
            InitializeComponent();
        }
        //获取数据集合
        public ClassManage.EntityClass.CStudents getStudents()
        {
            OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=db.mdb");
            OleDbCommand cmd = new OleDbCommand("select * from 同学录表", con);
            
            try
            {
                con.Open();
                OleDbDataReader dr = cmd.ExecuteReader();
                if (dr != null)
                {
                    ClassManage.EntityClass.CStudents stus = new ClassManage.EntityClass.CStudents();
                    while (dr.Read())
                    {
                        ClassManage.EntityClass.CStudent stu = new ClassManage.EntityClass.CStudent();
                        stu.sID = dr.GetInt32(0);
                        stu.sName = dr.GetString(1);
                        stu.sBoth = dr.GetString(2);
                        stu.sPhone = dr.GetString(3);
                        stu.sQQ = dr.GetString(4);
                        stu.sMail = dr.GetString(5);
                        stu.sAddress = dr.GetString(6);
                        stus.Add(stu);
                    }
                    return stus;
                }
                return null;
                
            }
            catch (Exception ex)
            {
                MessageBox.Show("出现错误:" + ex.Message);
                return null;
            }
            finally
            {
                con.Close();
            }
        }
        //
        public void pageChange(int index)
        {
            setEnable(false);
            if (students != null)
            {
                this.txtID.Text = students[index].sID.ToString();
                this.txtName.Text = students[index].sName;
                this.txtBoth.Text = students[index].sBoth;
                this.txtPhone.Text = students[index].sPhone;
                this.txtQQ.Text = students[index].sQQ;
                this.txtMail.Text = students[index].sMail;
                this.txtAddress.Text = students[index].sAddress;
            }
        }
        private void frmDataIndex_Load(object sender, EventArgs e)
        {
            setEnable(false);
            students = getStudents();
            if (students != null)
            {
                pageIndex = 0;
                pageChange(pageIndex);

            }
        }

        private void btnFirst_Click(object sender, EventArgs e)
        {
            if (students != null)
            {
                pageIndex = 0;
                pageChange(pageIndex);

            }
        }

        private void btnLast_Click(object sender, EventArgs e)
        {
            if (students != null)
            {
                pageIndex = students.Count-1;
                pageChange(pageIndex);

            }
        }

        private void btnPrv_Click(object sender, EventArgs e)
        {
            if (students != null && pageIndex!=0)
            {
                pageIndex -= 1;
                pageChange(pageIndex);

            }
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            if (students != null && pageIndex!=students.Count-1)
            {
                pageIndex += 1;
                pageChange(pageIndex);

            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {

            OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=db.mdb");
            OleDbCommand cmd;
            if (flag == 2)
            {
                 cmd = new OleDbCommand("insert into 同学录表 values(@id,@name,@both,@phone,@qq,@mail,@address)", con);
                 cmd.Parameters.Add("id", OleDbType.Integer).Value = Int32.Parse(txtID.Text);
                 cmd.Parameters.Add("name", OleDbType.VarChar).Value = txtName.Text;
                 cmd.Parameters.Add("both", OleDbType.VarChar).Value = txtBoth.Text;
                 cmd.Parameters.Add("phone", OleDbType.VarChar).Value = txtPhone.Text;
                 cmd.Parameters.Add("qq", OleDbType.VarChar).Value = txtQQ.Text;
                 cmd.Parameters.Add("mail", OleDbType.VarChar).Value = txtMail.Text;
                 cmd.Parameters.Add("address", OleDbType.VarChar).Value = txtAddress.Text;
                 try
                 {
                     con.Open();
                     cmd.ExecuteNonQuery();
                     MessageBox.Show("添加成功");
                 }
                 catch (Exception ex)
                 {
                     MessageBox.Show("出现错误:" + ex.Message);
                 }
                 finally
                 {
                     con.Close();
                 }
            }
            else if (flag == 1)
            {
                cmd = new OleDbCommand("update 同学录表 set 名字=@name,生日=@both,电话=@phone,QQ=@qq,EMAIL=@mail,地址=@address where 编号=@id", con);                
                cmd.Parameters.Add("name", OleDbType.VarChar).Value = txtName.Text;
                cmd.Parameters.Add("both", OleDbType.VarChar).Value = txtBoth.Text;
                cmd.Parameters.Add("phone", OleDbType.VarChar).Value = txtPhone.Text;
                cmd.Parameters.Add("qq", OleDbType.VarChar).Value = txtQQ.Text;
                cmd.Parameters.Add("mail", OleDbType.VarChar).Value = txtMail.Text;
                cmd.Parameters.Add("address", OleDbType.VarChar).Value = txtAddress.Text;
                cmd.Parameters.Add("id", OleDbType.Integer).Value = Int32.Parse(txtID.Text);
                try
                {
                    con.Open();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("修改成功");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("出现错误:" + ex.Message);
                }
                finally
                {
                    con.Close();
                }
            }
            setEnable(false);
            students = getStudents();
            if (students != null)
            {
                pageIndex = 0;
                pageChange(pageIndex);

            }
        }
        public void setEnable(bool b)
        {
            this.txtID.Enabled = b;
            this.txtName.Enabled = b;
            this.txtBoth.Enabled = b;
            this.txtQQ.Enabled = b;
            this.txtPhone.Enabled = b;
            this.txtMail.Enabled = b;
            this.txtAddress.Enabled = b;
        }
        public void setEmpty()
        {
            this.txtID.Text = string.Empty;
            this.txtName.Text = string.Empty;
            this.txtBoth.Text = string.Empty;
            this.txtQQ.Text = string.Empty;
            this.txtPhone.Text = string.Empty;
            this.txtMail.Text = string.Empty;
            this.txtAddress.Text = string.Empty;
        }
        private void btnAdd_Click(object sender, EventArgs e)
        {
            setEmpty();
            setEnable(true);
            flag = 2;
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            setEnable(true);
            flag = 1;
        }
    }
}