java access数据库实现增删改查_C#对Access进行增删改查的完整示例

这篇文章整理了C#对Access数据库的查询、添加记录、删除记录和更新数据等一系列的操作示例，有需要的可以参考学习。

首先是AccessHelper.cs，网上有下载，下面附送一份；

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.OleDb;

using System.Data;

using System.Windows.Forms;

namespace yxdain

{

public class AccessHelper

{

private string conn_str = null;

private OleDbConnection ole_connection = null;

private OleDbCommand ole_command = null;

private OleDbDataReader ole_reader = null;

private DataTable dt = null;

///

/// 构造函数

///

public AccessHelper()

{

//conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Environment.CurrentDirectory + "\\yxdain.accdb'";

conn_str = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Environment.CurrentDirectory + "\\yxdain.accdb'";

InitDB();

}

private void InitDB()

{

ole_connection =new OleDbConnection(conn_str);//创建实例

ole_command =new OleDbCommand();

}

///

/// 构造函数

///

///数据库路径

public AccessHelper(string db_path)

{

//conn_str ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"+ db_path + "'";

conn_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + db_path + "'";

InitDB();

}

///

/// 转换数据格式

///

///数据源

/// 数据列表

private DataTable ConvertOleDbReaderToDataTable(ref OleDbDataReader reader)

{

DataTable dt_tmp =null;

DataRow dr =null;

int data_column_count = 0;

int i = 0;

data_column_count = reader.FieldCount;

dt_tmp = BuildAndInitDataTable(data_column_count);

if(dt_tmp == null)

{

return null;

}

while(reader.Read())

{

dr = dt_tmp.NewRow();

for(i = 0; i < data_column_count; ++i)

{

dr[i] = reader[i];

}

dt_tmp.Rows.Add(dr);

}

return dt_tmp;

}

///

/// 创建并初始化数据列表

///

///列的个数

/// 数据列表

private DataTable BuildAndInitDataTable(int Field_Count)

{

DataTable dt_tmp =null;

DataColumn dc =null;

int i = 0;

if(Field_Count <= 0)

{

return null;

}

dt_tmp =new DataTable();

for(i = 0; i < Field_Count; ++i)

{

dc =new DataColumn(i.ToString());

dt_tmp.Columns.Add(dc);

}

return dt_tmp;

}

///

/// 从数据库里面获取数据

///

///查询语句

/// 数据列表

public DataTable GetDataTableFromDB(string strSql)

{

if(conn_str == null)

{

return null;

}

try

{

ole_connection.Open();//打开连接

if(ole_connection.State == ConnectionState.Closed)

{

return null;

}

ole_command.CommandText = strSql;

ole_command.Connection = ole_connection;

ole_reader = ole_command.ExecuteReader(CommandBehavior.Default);

dt = ConvertOleDbReaderToDataTable(ref ole_reader);

ole_reader.Close();

ole_reader.Dispose();

}

catch(System.Exception e)

{

//Console.WriteLine(e.ToString());

MessageBox.Show(e.Message);

}

finally

{

if(ole_connection.State != ConnectionState.Closed)

{

ole_connection.Close();

}

}

return dt;

}

///

/// 执行sql语句

///

///sql语句

/// 返回结果

public int ExcuteSql(string strSql)

{

int nResult = 0;

try

{

ole_connection.Open();//打开数据库连接

if(ole_connection.State == ConnectionState.Closed)

{

return nResult;

}

ole_command.Connection = ole_connection;

ole_command.CommandText = strSql;

nResult = ole_command.ExecuteNonQuery();

}

catch(System.Exception e)

{

//Console.WriteLine(e.ToString());

MessageBox.Show(e.Message);

return nResult;

}

finally

{

if(ole_connection.State != ConnectionState.Closed)

{

ole_connection.Close();

}

}

return nResult;

}

}

}

定义变量，设置列标题；

private AccessHelper achelp;

......

private void Form1_Load(object sender, EventArgs e)

{

achelp = new AccessHelper();

string sql1 = "select * from ycyx";

databind1(sql1);

dataGridView1.Columns[0].Visible = false;

dataGridView1.Columns[1].HeaderCell.Value = "服务号码";

dataGridView1.Columns[2].HeaderCell.Value = "客户名称";

dataGridView1.Columns[3].HeaderCell.Value = "归属地区";

dataGridView1.Columns[4].HeaderCell.Value = "当前品牌";

dataGridView1.Columns[5].HeaderCell.Value = "当前套餐";

dataGridView1.Columns[6].HeaderCell.Value = "当前状态";

}

显示数据表全部内容；

private void databind1(string sqlstr)

{

DataTable dt = new DataTable();

dt = achelp.GetDataTableFromDB(sqlstr);

dataGridView1.DataSource = dt;

}

读取要更新记录到更新窗体控件；

private void button3_Click(object sender, EventArgs e)

{

if (dataGridView1.SelectedRows.Count < 1 || dataGridView1.SelectedRows[0].Cells[1].Value == null)

{

MessageBox.Show("没有选中行。", "M营销");

return;

}

//f3.Owner = this;

DataTable dt = new DataTable();

object oid = dataGridView1.SelectedRows[0].Cells[0].Value;

string sql = "select * from ycyx where ID=" + oid;

dt = achelp.GetDataTableFromDB(sql);

f3 = new Form3();

f3.id = int.Parse(oid.ToString());

//f3.id = 2;

f3.Text1 = dt.Rows[0][1].ToString();

f3.Text2 = dt.Rows[0][2].ToString();

f3.Text3 = dt.Rows[0][3].ToString();

f3.Text4 = dt.Rows[0][4].ToString();

f3.Text5 = dt.Rows[0][5].ToString();

f3.Text6 = dt.Rows[0][6].ToString();

f3.ShowDialog();

}

添加记录；

private void button4_Click(object sender, EventArgs e)

{

if (textBox1.Text == "" && textBox2.Text == "" && textBox3.Text == "" && textBox4.Text == "" && textBox5.Text == "" && textBox6.Text == "")

{

MessageBox.Show("没有要添加的内容", "M营销添加");

return;

}

else

{

string sql = "insert into ycyx (fwhm,khmc,gsdq,dqpp,dqtc,dqzt) values ('" + textBox1.Text + "','" + textBox2.Text + "','"+

textBox3.Text + "','"+ textBox4.Text + "','"+ textBox5.Text + "','"+ textBox6.Text + "')";

int ret = achelp.ExcuteSql(sql);

string sql1 = "select * from ycyx";

databind1(sql1);

textBox1.Text = "";

textBox2.Text = "";

textBox3.Text = "";

textBox4.Text = "";

textBox5.Text = "";

textBox6.Text = "";

}

}

删除记录；

private void button2_Click(object sender, EventArgs e)

{

if (dataGridView1.SelectedRows.Count < 1 || dataGridView1.SelectedRows[0].Cells[1].Value == null)

{

MessageBox.Show("没有选中行。", "M营销");

}

else

{

object oid = dataGridView1.SelectedRows[0].Cells[0].Value;

if (DialogResult.No == MessageBox.Show("将删除第 " + (dataGridView1.CurrentCell.RowIndex + 1).ToString() + " 行，确定？", "M营销", MessageBoxButtons.YesNo))

{

return;

}

else

{

string sql = "delete from ycyx where ID=" + oid;

int ret = achelp.ExcuteSql(sql);

}

string sql1 = "select * from ycyx";

databind1(sql1);

}

}

查询；

private void button13_Click(object sender, EventArgs e)

{

if (textBox23.Text == "")

{

MessageBox.Show("请输入要查询的当前品牌", "M营销");

return;

}

else

{

string sql = "select * from ycyx where dqpp='" + textBox23.Text + "'";

DataTable dt = new System.Data.DataTable();

dt = achelp.GetDataTableFromDB(sql);

dataGridView1.DataSource = dt;

}

}

用户确定显示或不显示哪些数据列；

private void button15_Click(object sender, EventArgs e)

{

if (checkBox1.Checked == true)

{

dataGridView1.Columns[1].Visible = true;

}

else

{

dataGridView1.Columns[1].Visible = false;

}

if (checkBox2.Checked == true)

{

dataGridView1.Columns[2].Visible = true;

}

else

{

dataGridView1.Columns[2].Visible = false;

}

if (checkBox3.Checked == true)

{

dataGridView1.Columns[3].Visible = true;

}

else

{

dataGridView1.Columns[3].Visible = false;

}

if (checkBox4.Checked == true)

{

dataGridView1.Columns[4].Visible = true;

}

else

{

dataGridView1.Columns[4].Visible = false;

}

if (checkBox5.Checked == true)

{

dataGridView1.Columns[5].Visible = true;

}

else

{

dataGridView1.Columns[5].Visible = false;

}

if (checkBox6.Checked == true)

{

dataGridView1.Columns[6].Visible = true;

}

else

{

dataGridView1.Columns[6].Visible = false;

}

}

更新数据；

public partial class Form3 : Form

{

private AccessHelper achelp;

private int iid;

public Form3()

{

InitializeComponent();

achelp = new AccessHelper();

iid = 0;

}

// 更新

private void button1_Click(object sender, EventArgs e)

{

try

{

//UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'WHERE LastName = 'Wilson'

string sql = "update ycyx set fwhm='"+textBox1.Text+"',khmc='"+textBox2.Text+"',gsdq='"+textBox3.Text+"',dqpp='"+textBox4.Text+

"',dqtc='"+textBox5.Text+"',dqzt='"+textBox6.Text+"' where ID="+iid;

int ret = achelp.ExcuteSql(sql);

if (ret > -1)

{

this.Hide();

MessageBox.Show("更新成功", "M营销");

}

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

}

private void Form3_Load(object sender, EventArgs e)

{

}

public int id

{

get { return this.iid; }

set { this.iid = value; }

}

public string Text1

{

get { return this.textBox1.Text; }

set { this.textBox1.Text = value; }

}

public string Text2

{

get { return this.textBox2.Text; }

set { this.textBox2.Text = value; }

}

public string Text3

{

get { return this.textBox3.Text; }

set { this.textBox3.Text = value; }

}

public string Text4

{

get { return this.textBox4.Text; }

set { this.textBox4.Text = value; }

}

public string Text5

{

get { return this.textBox5.Text; }

set { this.textBox5.Text = value; }

}

public string Text6

{

get { return this.textBox6.Text; }

set { this.textBox6.Text = value; }

}

//取消

private void button2_Click(object sender, EventArgs e)

{

this.Hide();

}

}

}

注意此处有一个技巧；C# Winform，在窗体之间传值，或在一个窗体中设置另一个窗体的控件的值时，有多种方式；最好方式是如上代码所示；使用.net的get、set属性；

控件是一个窗体的私有变量，不能在另一个窗体中直接访问；为了在a窗体中设置b窗体的控件的值，对b窗体的控件都添加一个带get、set的公共属性，就可在a中设置b中控件的值，具体看代码；

以上就是C#对Access进行增删改查的完整示例代码，希望对大家学习C#能有所帮助。