当前位置:文档之家› C#代码:对Access数据库进行查找、插入、删除、修改

C#代码:对Access数据库进行查找、插入、删除、修改

using System;
using System.Collections.Generic;
using https://www.doczj.com/doc/9f5447792.html,ponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb; //数据库相关引用

namespace WindowsFormsApplication230
{
public partial class Form1 : Form
{
string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\陈超\\Desktop\\biao2.mdb";
//此句可以通过鼠标连接后在对话框中复制出来,但是\要变成\\,因为转义字符
//string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biao2.mdb"; //这种路径为相对地址,就是将数据库文件放到本工程目录下的bin\Debug里
public Form1()
{
InitializeComponent();
}
int maxid=0;
private void Form1_Load(object sender, EventArgs e)
{
for (int i = 0; i < 100; i++) //构建为空的行列框架
{
listView1.Items.Add("");
for (int a = 0; a < 5; a++)
{
listView1.Items[i].SubItems.Add("");
}
}
using (OleDbConnection conn = new OleDbConnection(strcon))
{
//select * from 表名 意思是 在某表中无限制条件(所有都符合)查找条目,显示字段为所有字段(*)
OleDbDataAdapter adc = new OleDbDataAdapter("select*from 表3", conn); //准备在之前写明的路径中的数据库的整个表3中查找所有内容
DataSet ds = new DataSet(); //创建一个数据容器(虚拟表)
adc.Fill(ds); //将adc中的查找命令执行并把查找结果放入虚拟表
for (int i = 0; i < ds.Tables[0].Rows.Count; i++) //找出虚拟表(与当前数据库中表3一致)中字段ID(主键)中的最大的一个数字并存入maxid供后续使用
{
if (Convert.ToInt32(ds.Tables[0].Rows[i][0]) > maxid)
{
maxid = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
using (OleDbConnection conn = new OleDbConnection(strcon))
{
for (int i = 0; i < listView1.Items.Count; i++) //清空所有数据
{
for (int a = 0; a < 5; a++)
{
listView1.Items[i].SubItems[a].Text = "";
}
}
conn.Open();
OleDbDataAdapter oledbba = new OleDbDataAdapter("select*from 表3", conn);
DataSet ds = new DataSet();
oledbba.Fill(ds);


for (int i = 0; i < ds.Tables[0].Rows.Count; i++) //将虚拟表ds里的数据复制到listview1中
{
for (int a = 0; a < 5; a++)
{
listView1.Items[i].SubItems[a].Text = ds.Tables[0].Rows[i][a].ToString();
}
}
conn.Close();
}
}

private void button2_Click(object sender, EventArgs e)
{
string tiaojian = "";
if (textBox17.Text != "")
{
if (tiaojian != "") tiaojian += " and"; //第一个条件前面没有and(且),其余条件都要加and
tiaojian += (" ID=" + textBox17.Text);
}
if (textBox1.Text != "")
{
if (tiaojian != "") tiaojian += " and";
tiaojian += (" 名称='" + textBox1.Text + "'"); //例:名称='陈超' SQL规定非数字(类型)要用单引号
}
if (textBox5.Text != "")
{
if (tiaojian != "") tiaojian += " and";
tiaojian += (" 数量=" + textBox5.Text);
}
if (textBox9.Text != "")
{
if (tiaojian != "") tiaojian += " and";
tiaojian += (" 单价=" + textBox9.Text);
}
if (textBox13.Text != "")
{
if (tiaojian != "") tiaojian += " and";
tiaojian += (" 总价=" + textBox13.Text);
}
using (OleDbConnection conn = new OleDbConnection(strcon))
{
for (int i = 0; i < listView1.Items.Count; i++)
{
for (int a = 0; a < 5; a++)
{
listView1.Items[i].SubItems[a].Text = "";
}
}
try //五个查找条件至少输入一个,若都为空则执行catch,弹出消息框
{
conn.Open();
OleDbDataAdapter oledbba = new OleDbDataAdapter("select*from 表3 where" + tiaojian, conn);
//select*from 表3 where 字段名='大米'(数字类型不加单引号,多个条件同时满足用and连接),由于要用到变量,所以整个SQL语句被拆成了多个字符串(用连接符+连接)
DataSet ds = new DataSet();
oledbba.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int a = 0; a < 5; a++)
{
listView1.Items[i].SubItems[a].Text = ds.Tables[0].Rows[i][a].ToString();
}
}
conn.Close();
}

catch
{
MessageBox.Show("至少输入一个条件");
}
}
}
private void button6_Click(object sender, EventArgs e)
{
using (OleDbConnection conn = new OleDbConnection(strcon))
{
conn.Open();
OleDbDataAdapter oledbba = new OleDbDataAdapter("select*from 表3 order by ID asc", conn); //order by 表示排序,ID可以换成其他字段名,asc为升序
DataSet ds = new DataSet();
oledbba.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int a = 0; a < 5; a++)
{
listView1.Items[i].SubItems[a].Text = ds.Tables[0].Rows[i][a].ToString();
}
}
conn.Close();
}
}
private void button7_Click(object sender, EventArgs e)
{
using (OleDbConnection conn = new OleDbConnection(strcon))
{
conn.Open();
OleDbDataAdapter oledbba = new OleDbDataAdapter("select*from 表3 order by ID desc", conn); //desc为降序
DataSet ds = new DataSet();
oledbba.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int a = 0; a < 5; a++)
{
listView1.Items[i].SubItems[a].Text = ds.Tables[0].Rows[i][a].ToString();
}
}
conn.Close();
}
}

private void button5_Click(object sender, EventArgs e)
{
//添加(插入)数据,为给用户提供方便,每个字段对应的数据设置有默认值,如果用户未填写则为默认值
if (textBox4.Text == "")
{
textBox4.Text = "(暂无)";
}
if (textBox8.Text == "")
{
textBox8.Text = "0";
}
if (textBox12.Text == "")
{
textBox12.Text = "0";
}
if (textBox16.Text == "")
{
textBox16.Text = "0";
}
using (OleDbConnection conn = new OleDbConnection(strcon))
{
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter oledbba = new OleDbDataAdapter("insert into 表3 values(" + (++maxid) + ",'" + textBox4.Text + "'," + textBox8.Text + "," + textBox12.Text + "," + textBox16.Text + ")", conn);
//insert into 表3 values(1,'陈超',10,20,200) 由于插入操作是在数据库最后一行的下面新添一整行,故括号里要按顺序写上每个字段的对应数据,中间用逗号隔开
//此处可以

看出本程序配套的数据库有五个字段,可自己建立一个数据库表
oledbba.Fill(ds); //执行插入命令,在数据库中已生效,但ds无数据(无表)
OleDbDataAdapter temp = new OleDbDataAdapter("select*from 表3", conn);
temp.Fill(ds); //执行查找命令,并将查找结果(虚拟表)存入ds
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int a = 0; a < 5; a++)
{
listView1.Items[i].SubItems[a].Text = ds.Tables[0].Rows[i][a].ToString();
}
}
conn.Close();

}
}


private void button3_Click(object sender, EventArgs e)
{
string tiaojian2 = "";
if (textBox18.Text != "")
{
if (tiaojian2 != "") tiaojian2 += " and";
tiaojian2 += (" ID=" + textBox18.Text);
}
if (textBox2.Text != "")
{
if (tiaojian2 != "") tiaojian2 += " and";
tiaojian2 += (" 名称='" + textBox2.Text + "'");
}
if (textBox6.Text != "")
{
if (tiaojian2 != "") tiaojian2 += " and";
tiaojian2 += (" 数量=" + textBox6.Text);
}
if (textBox10.Text != "")
{
if (tiaojian2 != "") tiaojian2 += " and";
tiaojian2 += (" 单价=" + textBox10.Text);
}
if (textBox14.Text != "")
{
if (tiaojian2 != "") tiaojian2 += " and";
tiaojian2 += (" 总价=" + textBox14.Text);
}
using (OleDbConnection conn = new OleDbConnection(strcon))
{
try
{
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter oledbba = new OleDbDataAdapter("delete from 表3 where " + tiaojian2, conn);
//delete from 表3 where 名称='大米' 删除是先按条件找到需要删除的整行,然后删除,由于必然是整行,所以from前没有*,也不需要加任何东西,where及其条件的写法与查找操作一致
oledbba.Fill(ds); //执行删除命令,在数据库中已生效,但ds无数据(无表)
OleDbDataAdapter temp = new OleDbDataAdapter("select*from 表3", conn);
temp.Fill(ds);
for (int i = 0; i < listView1.Items.Count; i++) //删除前有N行,删除后有N-1行,不清空的话,虽然listview的前N-1行显示正确,但是listview显示的第N行是数据库没有的
{
for (int a = 0; a < 5; a++)
{

listView1.Items[i].SubItems[a].Text = "";
}
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int a = 0; a < 5; a++)
{
listView1.Items[i].SubItems[a].Text = ds.Tables[0].Rows[i][a].ToString();
}
}
conn.Close();
}
catch
{
MessageBox.Show("请至少输入一个条件");
}
}
}


private void button4_Click(object sender, EventArgs e)
{
string shezhi = "",tiaojian="";
if (textBox20.Text != "")
{
if (tiaojian != "")
{
tiaojian += " and ";
}
tiaojian += ("名称='"+textBox20.Text+"'");
}
if (textBox21.Text != "")
{
if (tiaojian != "")
{
tiaojian += " and ";
}
tiaojian += ("数量=" + textBox21.Text);
}
if (textBox22.Text != "")
{
if (tiaojian != "")
{
tiaojian += " and ";
}
tiaojian += ("单价=" + textBox22.Text);
}
if (textBox23.Text != "")
{
if (tiaojian != "")
{
tiaojian += " and ";
}
tiaojian += ("总价=" + textBox23.Text);
}

if (textBox3.Text != "")
{
if (shezhi != "")
{
shezhi += " ,";
}
shezhi += ("名称='" + textBox3.Text + "'");
}
if (textBox7.Text != "")
{
if (shezhi != "")
{
shezhi += " ,";
}
shezhi += ("数量=" + textBox7.Text);
}
if (textBox11.Text != "")
{
if (shezhi != "")
{
shezhi += " ,";
}
shezhi += ("单价=" + textBox11.Text);
}
if (textBox15.Text != "")
{
if (shezhi != "")
{
shezhi += " ,";
}
shezhi += ("总价=" + textBox15.Text);
}
using (OleDbConnection conn = new OleDbConnection(strcon))
{
try
{
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter

oledbba = new OleDbDataAdapter("update 表3 set " + shezhi + " where " + tiaojian, conn);
//update 表3 set 字段名='玉米'(数字类型不加单引号,要修改多个字段的对应数据用逗号隔开) where 字段名='大米'(数字类型不加单引号,多个条件同时满足用and连接),由于要用到变量,所以整个SQL语句被拆成了多个字符串(用连接符+连接)
oledbba.Fill(ds); //执行修改命令,在数据库中已生效,但ds无数据(无表)
OleDbDataAdapter temp = new OleDbDataAdapter("select*from 表3", conn);
temp.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int a = 0; a < 5; a++)
{
listView1.Items[i].SubItems[a].Text = ds.Tables[0].Rows[i][a].ToString();
}
}
conn.Close();
}
catch
{
MessageBox.Show("请输入至少一个条件和至少一个更改值");
}

}
}
}
}


相关主题
相关文档 最新文档