C#对Access进行增删改查的完整示例
这篇文章整理了C#对Access数据库的查询、添加记录、删除记录和更新数据等一系列的操作示例，有需要的可以参考学习。
首先是AccessHelper.cs，网上有下载，下面附送一份；
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Threading.Tasks;
usingSystem.Data.OleDb;
usingSystem.Data;
usingSystem.Windows.Forms;
namespaceyxdain
{
publicclassAccessHelper
{
privatestringconn_str=null;
privateOleDbConnectionole_connection=null;
privateOleDbCommandole_command=null;
privateOleDbDataReaderole_reader=null;
privateDataTabledt=null;
///<summary>
///构造函数
///</summary>
publicAccessHelper()
{
//conn_str=@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource='"+Environment.CurrentDirectory+"\\yxdain.accdb'";
conn_str=@"Provider=Microsoft.ACE.OLEDB.12.0;DataSource='"+Environment.CurrentDirectory+"\\yxdain.accdb'";
InitDB();
}
privatevoidInitDB()
{
ole_connection=newOleDbConnection(conn_str);//创建实例
ole_command=newOleDbCommand();
}
///<summary>
///构造函数
///</summary>
///<paramname="db_path">数据库路径
publicAccessHelper(stringdb_path)
{
//conn_str="Provider=Microsoft.Jet.OLEDB.4.0;DataSource='"+db_path+"'";
conn_str="Provider=Microsoft.ACE.OLEDB.12.0;DataSource='"+db_path+"'";
InitDB();
}
///<summary>
///转换数据格式
///</summary>
///<paramname="reader">数据源
///<returns>数据列表</returns>
privateDataTableConvertOleDbReaderToDataTable(refOleDbDataReaderreader)
{
DataTabledt_tmp=null;
DataRowdr=null;
intdata_column_count=0;
inti=0;
data_column_count=reader.FieldCount;
dt_tmp=BuildAndInitDataTable(data_column_count);
if(dt_tmp==null)
{
returnnull;
}
while(reader.Read())
{
dr=dt_tmp.NewRow();
for(i=0;i<data_column_count;++i)
{
dr[i]=reader[i];
}
dt_tmp.Rows.Add(dr);
}
returndt_tmp;
}
///<summary>
///创建并初始化数据列表
///</summary>
///<paramname="Field_Count">列的个数
///<returns>数据列表</returns>
privateDataTableBuildAndInitDataTable(intField_Count)
{
DataTabledt_tmp=null;
DataColumndc=null;
inti=0;
if(Field_Count<=0)
{
returnnull;
}
dt_tmp=newDataTable();
for(i=0;i<Field_Count;++i)
{
dc=newDataColumn(i.ToString());
dt_tmp.Columns.Add(dc);
}
returndt_tmp;
}
///<summary>
///从数据库里面获取数据
///</summary>
///<paramname="strSql">查询语句
///<returns>数据列表</returns>
publicDataTableGetDataTableFromDB(stringstrSql)
{
if(conn_str==null)
{
returnnull;
}
try
{
ole_connection.Open();//打开连接
if(ole_connection.State==ConnectionState.Closed)
{
returnnull;
}
ole_command.CommandText=strSql;
ole_command.Connection=ole_connection;
ole_reader=ole_command.ExecuteReader(CommandBehavior.Default);
dt=ConvertOleDbReaderToDataTable(refole_reader);
ole_reader.Close();
ole_reader.Dispose();
}
catch(System.Exceptione)
{
//Console.WriteLine(e.ToString());
MessageBox.Show(e.Message);
}
finally
{
if(ole_connection.State!=ConnectionState.Closed)
{
ole_connection.Close();
}
}
returndt;
}
///<summary>
///执行sql语句
///</summary>
///<paramname="strSql">sql语句
///<returns>返回结果</returns>
publicintExcuteSql(stringstrSql)
{
intnResult=0;
try
{
ole_connection.Open();//打开数据库连接
if(ole_connection.State==ConnectionState.Closed)
{
returnnResult;
}
ole_command.Connection=ole_connection;
ole_command.CommandText=strSql;
nResult=ole_command.ExecuteNonQuery();
}
catch(System.Exceptione)
{
//Console.WriteLine(e.ToString());
MessageBox.Show(e.Message);
returnnResult;
}
finally
{
if(ole_connection.State!=ConnectionState.Closed)
{
ole_connection.Close();
}
}
returnnResult;
}
}
}
定义变量，设置列标题；
privateAccessHelperachelp;
......
privatevoidForm1_Load(objectsender,EventArgse)
{
achelp=newAccessHelper();
stringsql1="select*fromycyx";
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="当前状态";
}
显示数据表全部内容；
privatevoiddatabind1(stringsqlstr)
{
DataTabledt=newDataTable();
dt=achelp.GetDataTableFromDB(sqlstr);
dataGridView1.DataSource=dt;
}
读取要更新记录到更新窗体控件；
privatevoidbutton3_Click(objectsender,EventArgse)
{
if(dataGridView1.SelectedRows.Count<1||dataGridView1.SelectedRows[0].Cells[1].Value==null)
{
MessageBox.Show("没有选中行。","M营销");
return;
}
//f3.Owner=this;
DataTabledt=newDataTable();
objectoid=dataGridView1.SelectedRows[0].Cells[0].Value;
stringsql="select*fromycyxwhereID="+oid;
dt=achelp.GetDataTableFromDB(sql);
f3=newForm3();
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();
}
添加记录；
privatevoidbutton4_Click(objectsender,EventArgse)
{
if(textBox1.Text==""&&textBox2.Text==""&&textBox3.Text==""&&textBox4.Text==""&&textBox5.Text==""&&textBox6.Text=="")
{
MessageBox.Show("没有要添加的内容","M营销添加");
return;
}
else
{
stringsql="insertintoycyx(fwhm,khmc,gsdq,dqpp,dqtc,dqzt)values('"+textBox1.Text+"','"+textBox2.Text+"','"+
textBox3.Text+"','"+textBox4.Text+"','"+textBox5.Text+"','"+textBox6.Text+"')";
intret=achelp.ExcuteSql(sql);
stringsql1="select*fromycyx";
databind1(sql1);
textBox1.Text="";
textBox2.Text="";
textBox3.Text="";
textBox4.Text="";
textBox5.Text="";
textBox6.Text="";
}
}
删除记录；
privatevoidbutton2_Click(objectsender,EventArgse)
{
if(dataGridView1.SelectedRows.Count<1||dataGridView1.SelectedRows[0].Cells[1].Value==null)
{
MessageBox.Show("没有选中行。","M营销");
}
else
{
objectoid=dataGridView1.SelectedRows[0].Cells[0].Value;
if(DialogResult.No==MessageBox.Show("将删除第"+(dataGridView1.CurrentCell.RowIndex+1).ToString()+"行，确定？","M营销",MessageBoxButtons.YesNo))
{
return;
}
else
{
stringsql="deletefromycyxwhereID="+oid;
intret=achelp.ExcuteSql(sql);
}
stringsql1="select*fromycyx";
databind1(sql1);
}
}
查询；
privatevoidbutton13_Click(objectsender,EventArgse)
{
if(textBox23.Text=="")
{
MessageBox.Show("请输入要查询的当前品牌","M营销");
return;
}
else
{
stringsql="select*fromycyxwheredqpp='"+textBox23.Text+"'";
DataTabledt=newSystem.Data.DataTable();
dt=achelp.GetDataTableFromDB(sql);
dataGridView1.DataSource=dt;
}
}
用户确定显示或不显示哪些数据列；
privatevoidbutton15_Click(objectsender,EventArgse)
{
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;
}
}
更新数据；
publicpartialclassForm3:Form
{
privateAccessHelperachelp;
privateintiid;
publicForm3()
{
InitializeComponent();
achelp=newAccessHelper();
iid=0;
}
//更新
privatevoidbutton1_Click(objectsender,EventArgse)
{
try
{
//UPDATEPersonSETAddress='Zhongshan23',City='Nanjing'WHERELastName='Wilson'
stringsql="updateycyxsetfwhm='"+textBox1.Text+"',khmc='"+textBox2.Text+"',gsdq='"+textBox3.Text+"',dqpp='"+textBox4.Text+
"',dqtc='"+textBox5.Text+"',dqzt='"+textBox6.Text+"'whereID="+iid;
intret=achelp.ExcuteSql(sql);
if(ret>-1)
{
this.Hide();
MessageBox.Show("更新成功","M营销");
}
}
catch(Exceptionex)
{
MessageBox.Show(ex.Message);
}
}
privatevoidForm3_Load(objectsender,EventArgse)
{
}
publicintid
{
get{returnthis.iid;}
set{this.iid=value;}
}
publicstringText1
{
get{returnthis.textBox1.Text;}
set{this.textBox1.Text=value;}
}
publicstringText2
{
get{returnthis.textBox2.Text;}
set{this.textBox2.Text=value;}
}
publicstringText3
{
get{returnthis.textBox3.Text;}
set{this.textBox3.Text=value;}
}
publicstringText4
{
get{returnthis.textBox4.Text;}
set{this.textBox4.Text=value;}
}
publicstringText5
{
get{returnthis.textBox5.Text;}
set{this.textBox5.Text=value;}
}
publicstringText6
{
get{returnthis.textBox6.Text;}
set{this.textBox6.Text=value;}
}
//取消
privatevoidbutton2_Click(objectsender,EventArgse)
{
this.Hide();
}
}
}
注意此处有一个技巧；C#Winform，在窗体之间传值，或在一个窗体中设置另一个窗体的控件的值时，有多种方式；最好方式是如上代码所示；使用.net的get、set属性；
控件是一个窗体的私有变量，不能在另一个窗体中直接访问；为了在a窗体中设置b窗体的控件的值，对b窗体的控件都添加一个带get、set的公共属性，就可在a中设置b中控件的值，具体看代码；
以上就是C#对Access进行增删改查的完整示例代码，希望对大家学习C#能有所帮助。