wince5使用access数据库_C#连接本地Access数据库及简单操作的winform小程序

连接本地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.