C#操作Access的查询、添加、删除、修改源程序
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.Common; using System.Drawing; using System.Linq; using System.Data.OleDb; using System.Text; using System.Windows.Forms; namespace Location { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { this.Text = "Location System"; button1.Text = "连接数据库"; button2.Text = "查询"; button3.Text = "退出"; button4.Text = "添加"; button5.Text = "删除"; button6.Text = "修改"; label1.Text = "ID："; textBox1.Text = "0"; } private void button1_Click(object sender, EventArgs e) { string ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=Location.mdb";//创建OleDbConnection对象 OleDbConnection con = new OleDbConnection(ConStr); con.Open(); if (con.State == ConnectionState.Open) { MessageBox.Show("Access数据库的连接成功!", "Access数据库的连接"); } else { MessageBox.Show("Access数据库的连接失败!", "Access数据库的连接"); } con.Close(); } private void button3_Click(object sender, EventArgs e) //退出 { this.Close(); } private void button2_Click(object sender, EventArgs e) //查询模块 { string ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=Location.mdb";//创建OleDbConnection对象 OleDbConnection con = new OleDbConnection(ConStr); con.Open(); int i = Convert.ToInt16(textBox1 .Text); OleDbCommand cmd = new OleDbCommand("Select * From data where ID>=@id", con); cmd.Parameters.Add("@id",i); OleDbDataReader reader = cmd.ExecuteReader(); reader.Read(); //textBox1.Text = reader[0].ToString(); textBox2.Text = reader[1].ToString(); textBox3.Text = reader[2].ToString(); textBox4.Text = reader[3].ToString(); textBox5.Text = reader[4].ToString(); textBox6.Text = reader[5].ToString(); textBox7.Text = reader[6].ToString(); reader.Close(); con.Close(); } private void button4_Click(object sender, EventArgs e) //添加 { string ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=Location.mdb";//创建OleDbConnection对象 OleDbConnection con = new OleDbConnection(ConStr); con.Open(); for (int i = 0; i < 1000; i++) { string sql = "insert into data(ID)values(" + i + ")"; OleDbCommand cmd = new OleDbCommand(sql, con); cmd.ExecuteNonQuery(); } con.Close(); } private void button5_Click(object sender, EventArgs e) //删除 { string ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=Location.mdb";//创建OleDbConnection对象 OleDbConnection con = new OleDbConnection(ConStr); con.Open(); OleDbCommand cmd = new OleDbCommand("delete from data", con); cmd.ExecuteNonQuery(); } private void button6_Click(object sender, EventArgs e) //修改 { string ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=Location.mdb";//创建OleDbConnection对象 OleDbConnection con = new OleDbConnection(ConStr); con.Open(); string sql = "update data set longitude=12 where ID=1"; OleDbCommand cmd = new OleDbCommand(sql, con); cmd.ExecuteNonQuery(); } } }
C#操作Access类
using System; using System.Data; using System.Data.OleDb; namespace AccessDb { /**//// <summary> /// AccessDb 的摘要说明，以下信息请完整保留 /// 请在数据传递完毕后调用Close()方法，关闭数据链接。 /// </summary> public class AccessDbClass { //变量声明处#region 变量声明处 public OleDbConnection Conn; public string ConnString;//连接字符串 #endregion //构造函数与连接关闭数据库#region 构造函数与连接关闭数据库 /**//// <summary> /// 构造函数 /// </summary> /// <param name="Dbpath">ACCESS数据库路径</param> public AccessDbClass(string Dbpath) { ConnString ="Provider=Microsoft.Jet.OleDb.4.0;Data Source="; ConnString += Dbpath; Conn =new OleDbConnection(ConnString); Conn.Open(); } /**//// <summary> /// 打开数据源链接 /// </summary> /// <returns></returns> public OleDbConnection DbConn() { Conn.Open(); return Conn; } /**//// <summary> /// 请在数据传递完毕后调用该函数，关闭数据链接。 /// </summary> public void Close() { Conn.Close(); } #endregion //数据库基本操作 #region 数据库基本操作 /**//// <summary> /// 根据SQL命令返回数据DataTable数据表, /// 可直接作为dataGridView的数据源 /// </summary> /// <param name="SQL"></param> /// <returns></returns> public DataTable SelectToDataTable(string SQL) { OleDbDataAdapter adapter =new OleDbDataAdapter(); OleDbCommand command =new OleDbCommand(SQL, Conn); adapter.SelectCommand = command; DataTable Dt =new DataTable(); adapter.Fill(Dt); return Dt; } /**//// <summary> /// 根据SQL命令返回数据DataSet数据集，其中的表可直接作为dataGridView的数据源。 /// </summary> /// <param name="SQL"></param> /// <param name="subtableName">在返回的数据集中所添加的表的名称</param> /// <returns></returns> public DataSet SelectToDataSet(string SQL,string subtableName) { OleDbDataAdapter adapter =new OleDbDataAdapter(); OleDbCommand command =new OleDbCommand(SQL, Conn); adapter.SelectCommand = command; DataSet Ds =new DataSet(); Ds.Tables.Add(subtableName); adapter.Fill(Ds, subtableName); return Ds; } /**//// <summary> /// 在指定的数据集中添加带有指定名称的表，由于存在覆盖已有名称表的危险，返回操作之前的数据集。 /// </summary> /// <param name="SQL"></param> /// <param name="subtableName">添加的表名</param> /// <param name="DataSetName">被添加的数据集名</param> /// <returns></returns> public DataSet SelectToDataSet (string SQL,string subtableName, DataSet DataSetName) { OleDbDataAdapter adapter =new OleDbDataAdapter(); OleDbCommand command =new OleDbCommand(SQL, Conn); adapter.SelectCommand = command; DataTable Dt =new DataTable(); DataSet Ds =new DataSet(); Ds = DataSetName; adapter.Fill(DataSetName, subtableName); return Ds; } /**//// <summary> /// 根据SQL命令返回OleDbDataAdapter， /// 使用前请在主程序中添加命名空间System.Data.OleDb /// </summary> /// <param name="SQL"></param> /// <returns></returns> public OleDbDataAdapter SelectToOleDbDataAdapter(string SQL) { OleDbDataAdapter adapter =new OleDbDataAdapter(); OleDbCommand command =new OleDbCommand(SQL, Conn); adapter.SelectCommand = command; return adapter; } /**//// <summary> /// 执行SQL命令，不需要返回数据的修改，删除可以使用本函数 /// </summary> /// <param name="SQL"></param> /// <returns></returns> public bool ExecuteSQLNonquery(string SQL) { OleDbCommand cmd =new OleDbCommand(SQL, Conn); try { cmd.ExecuteNonQuery(); return true; } catch { return false; } } #endregion } } 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/stxyc/archive/2010/04/19/5501232.aspx 使用例子： using AccessDb; ... //初始化，载入数据库路径 AccessDbClass mydb = new AccessDbClass("c:/db.mdb"); //返回符合SQL要求的DataTable，并且与控件dataGridView1绑定 DataTable dt = new DataTable(); dt = mydb.SelectToDataTable(@"select * from student"); this.dataGridView1.DataSource = dt; //返回DataSet，其中包括一个符合SQL要求和给定名称的DataTable，并且与控件dataGridView1绑定 DataSet ds = new DataSet(); ds = mydb.SelectToDataSet(@"select * from student","student"); this.dataGridView1.DataSource = ds.Tables["student"]; //关闭数据库 mydb.Close(); 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/stxyc/archive/2010/04/19/5501232.aspx
c#.NET实现数据读写Access
1.写入数据库 --------------------------------- using System.Data.OleDb; using System.IO; --------------------------------- //ACCESS数据库的连接字符串 string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source="; strConn += Server.MapPath(".\Data\accDB.mdb"); //生成一个新的连接 string strCom = "Insert into jieguo (RESULT,GRAND,imgURL) Values ('" + TextBox1.Text.ToString() + "','" + TextBox2.Text.ToString() + "','" + "~//houseImg//" + FileUpload1.FileName + "')"; OleDbConnection myConn = new OleDbConnection(strConn); myConn.Open(); OleDbCommand oldConn = new OleDbCommand(strCom, myConn); oldConn.ExecuteNonQuery(); myConn.Close(); Response.Redirect("Default.aspx"); 2.读数据库（以登录验证为例） --------------------------------- using System.Data.OleDb; using System.IO; --------------------------------- string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source="; strConn += Server.MapPath(".\Data\accDB.mdb"); OleDbConnection myConn = new OleDbConnection(strConn); //定义数据适配器 OleDbDataAdapter oda = new OleDbDataAdapter(); string strsql; strsql = "select * from users where name='" + AdminName.Text.ToString() + "'and pass='" + AdminPwd.Text.ToString() + "'"; DataSet dsMsg = new DataSet(); oda.SelectCommand = new OleDbCommand(strsql, myConn); oda.Fill(dsMsg); if (dsMsg.Tables[0].Rows.Count == 0) Response.Write("<script>alert("用户名不存在");</script>"); else Response.Redirect("next.aspx");
using System; using System.ComponentModel; using System.Data; using System.Data.OleDb; using System.Drawing; using System.IO; using System.Text; using System.Windows.Forms; namespace WindowsFormsApplication7 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private string strCnn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:cWindowsFormsApplication7aspxWeb.mdb;Persist Security Info=True"; private void button2_Click(object sender, EventArgs e) { if (this.openFileDialog1.ShowDialog() == DialogResult.OK) { this.textBox1.Text = this.openFileDialog1.FileName; } } private void button1_Click(object sender, EventArgs e) { if (this.textBox1.Text.Equals(String.Empty)) { MessageBox.Show("先选择文件。"); return; } FileStream fs = new FileStream(this.textBox1.Text, FileMode.Open); BinaryReader br = new BinaryReader(fs); byte[] buffer = br.ReadBytes((int)fs.Length); OleDbConnection myConnection = new OleDbConnection(strCnn); OleDbCommand command = new OleDbCommand("INSERT INTO TestTable (Title,Description,nr)" + "VALUES (@Title,@Description,@nr)", myConnection); command.Parameters.AddWithValue("@Title", "a"); command.Parameters.AddWithValue("@Description", "mengxianhui@dotnet.aspx.cc"); command.Parameters.AddWithValue("@nr", buffer); //打开连接，执行查询 myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); br.Close(); fs.Close(); MessageBox.Show("保存完毕。"); } private void button3_Click(object sender, EventArgs e) { //构建数据库连接，SQL语句，创建参数 OleDbConnection myConnection = new OleDbConnection(strCnn); myConnection.Open(); OleDbCommand command = new OleDbCommand("select top 1 * from TestTable Order By id DESC", myConnection); OleDbDataReader dr = command.ExecuteReader(); byte[] buff = null; if (dr.Read()) { buff = (byte[])dr["nr"]; } String p = Application.ExecutablePath; p = p.Substring(0,p.LastIndexOf("\")); p += "\m.doc"; this.textBox2.Text = p ; if (File.Exists(p)) File.Delete(p); myConnection.Close(); System.IO.FileStream stream = new System.IO.FileStream(p, System.IO.FileMode.CreateNew, System.IO.FileAccess.Write); System.IO.BinaryWriter bw = new System.IO.BinaryWriter(stream); bw.Write(buff); bw.Close(); stream.Close(); MessageBox.Show("生成完毕。"); } } }