连接本地Access数据库及简单操作的winform小程序

一、准备工作

用Access创建一个数据库并创建一个表格。（对于非远程数据库，Access十分简单。表格可参考三、界面设计）。

二、代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.Common;

namespace MyFirstDatabase
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        OleDbConnection conn;
        OleDbCommand da;
        private void Form1_Load(object sender, EventArgs e)
        {
            string txtConn =
           "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\JOKER\\Documents\\Database2.mdb";
            conn = new OleDbConnection(txtConn);
            string txtCommand = "SELECT StudentName, StudentNum, StudentSex FROM Student";
            OleDbDataAdapter da = new OleDbDataAdapter(txtCommand, conn);
            DataSet ds = new DataSet("ds");
            da.Fill(ds, "Student");
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "Student";
        //dataGridView2.DataSource = ds;
        //dataGridView2.DataMember = "Student";
    }

    private void button1_Click(object sender, EventArgs e) //增加纪录
    {
        if (textBox1.Text == "" ||textBox2.Text == "" || textBox3.Text == "")
        {
            MessageBox.Show("所有项都必须填写！ ");
            return;
        }//注意，下边语句必须保证数据库文件studentI.mdb在文件夹路径C:\\**中
        string txt1 =
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\JOKER\\Documents\\Database2.mdb";
        string txt2 =
        "Insert Into Student(StudentNum,StudentName,StudentSex) Values('";
        txt2 += textBox1.Text + "' , '";
        txt2 += textBox2.Text + "' , '";
        txt2 += textBox3.Text + "')";//最后结果要保证在TextBox中输入的内容被单引号括住
        conn = new OleDbConnection(txt1);
        conn.Open();
        da = new OleDbCommand();
        da.CommandText = txt2;
        da.Connection = conn;
        da.ExecuteNonQuery();
        textBox1.Text = "";
        textBox2.Text = "";
        textBox3.Text = "";
        conn.Close();

    }

    private void button2_Click(object sender, EventArgs e)  //修改纪录
    {
        if (textBox1.Text == "" )
        {
            MessageBox.Show("学生编号项必须填写！ ");
            return;
        }//注意，下边语句必须保证数据库文件studentI.mdb在文件夹路径C:\\**中
        string txt1 =
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\JOKER\\Documents\\Database2.mdb";
        string txt2 = "Update Student Set StudentName = '" + textBox2.Text +"',StudentSex = '"+textBox3.Text+"'Where StudentNum = " + textBox1.Text;

        conn = new OleDbConnection(txt1);
        conn.Open();
        da = new OleDbCommand();
        da.CommandText = txt2;
        da.Connection = conn;
        da.ExecuteNonQuery();
        textBox1.Text = "";
        textBox2.Text = "";
        textBox3.Text = "";
        conn.Close();

    }

    private void button3_Click(object sender, EventArgs e)  //更新纪录
    {
        string txtConn =
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\JOKER\\Documents\\Database2.mdb";
        conn = new OleDbConnection(txtConn);
        string txtCommand = "SELECT StudentName, StudentNum, StudentSex FROM Student";
        OleDbDataAdapter da = new OleDbDataAdapter(txtCommand, conn);
        DataSet ds = new DataSet("ds");
        da.Fill(ds, "Student");
        dataGridView1.DataSource = ds;
        dataGridView1.DataMember = "Student";

    }

    private void button4_Click(object sender, EventArgs e) //删除纪录
    {
        if (textBox1.Text == "")
        {
            MessageBox.Show("学生编号项必须填写！ ");
            return;
        }//注意，下边语句必须保证数据库文件studentI.mdb在文件夹路径C:\\**中
        string txt1 =
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\JOKER\\Documents\\Database2.mdb";
        string txt2 = "Delete From Student Where StudentNum = " + textBox1.Text;

        conn = new OleDbConnection(txt1);
        conn.Open();
        da = new OleDbCommand();
        da.CommandText = txt2;
        da.Connection = conn;
        da.ExecuteNonQuery();
        textBox1.Text = "";
        textBox2.Text = "";
        textBox3.Text = "";
        conn.Close();
    }

    private void button5_Click(object sender, EventArgs e) //筛选数据
    {
        string txtConn =
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\JOKER\\Documents\\Database2.mdb";
        conn = new OleDbConnection(txtConn);
        string txtCommand = "SELECT "+comboBox1.Text+ ","+comboBox2.Text+" FROM Student";
        OleDbDataAdapter da = new OleDbDataAdapter(txtCommand, conn);
        DataSet ds = new DataSet("ds");
        da.Fill(ds, "Student");
        dataGridView2.DataSource = ds;
        dataGridView2.DataMember = "Student";
    }
}
}

三、界面设计

如下图：

Written with StackEdit.