当前位置 : 主页 > 编程语言 > c语言 >

C#操作Access的查询、添加、删除、修改源程序

来源:互联网 收集:自由互联 发布时间:2021-06-25
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

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>[email protected]", 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();
        }
    }
}
 
View Code

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
 
View Code

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");
 
View Code
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:\c\WindowsFormsApplication7\aspxWeb.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", "[email protected]");
      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("生成完毕。");
    }
  }
}
View Code
网友评论