c# 数据库操作之ACCESS

c#连接access数据库

注意一般要写成类MDBHelp：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        OleDbConnection dbconn;
        public Form1()
        {
            InitializeComponent();
        }

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="strConnection">数据库名字</param>
        private void linkdb(string strConnection)
        {
            dbconn = new OleDbConnection(strConnection); //dbcoon已设全局变量，
            dbconn.Open();//建立连接
        
        }


        private void button1_Click(object sender, EventArgs e)
        {
        #region
//            MessageBox.Show("Hello~~~~");
//            Class1 c1 = new Class1();
//            c1.Name = "葫芦娃";

#endregion

            linkdb("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\practice\\ACCESS\\demo1.mdb");
            string sql = "select * from  姓名表";
            string  instert_str = "insert into 姓名表(姓名,年龄,性别) values ('" + "葫芦娃" +"','"+"16"+"','"+"M"+"')";
           
            
            OleDbCommand myCommand = new OleDbCommand(instert_str, dbconn);//执行命令
            myCommand.ExecuteNonQuery();
            OleDbDataAdapter inst = new OleDbDataAdapter(sql, dbconn);
            DataSet ds = new DataSet();//临时存储
            inst.Fill(ds);//用inst 填充ds
            dataGridView1.DataSource = ds.Tables[0];//展示ds第一张表
            dbconn.Close();//关闭连接
           
          
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }


    }
}

　　

增删改查

增

            string  instert_str = "insert into 姓名表(姓名,年龄,性别) values ('" + "葫芦娃" +"','"+"16"+"','"+"M"+"')
　　
    　　　　　OleDbCommand myCommand = new OleDbCommand(Insert, dbconn);//执行命令

            myCommand.ExecuteNonQuery();//更新数据库，返回受影响行数;可通过判断其是否>0来判断操作是否成功

　　注：可以用textbox.text 作为输入值

　　　　　注意  输入语句的写法 :  '"+"str"+"'

 

 

删

string s = "'" + textBox1.Text + "'";//接受textBox1的字符串
            string Delete = "DELETE FROM student WHERE studentName = "+ s;
            //delete from 表名 where 字段名='字段值';以上代码执行后会将所有studentName为textbox中内容的行删除
            OleDbCommand myCommand = new OleDbCommand(Delete, dbconn);//执行命令
            myCommand.ExecuteNonQuery();  //更新数据库，返回受影响行数;可通过判断其是否>0来判断操作是否成功

 

 

 

改

string s = "'" + textBox1.Text + "'", x = "'" + textBox2.Text + "'"; 接受textBox的字符串
            string Update = "UPDATE student SET studentName=" + x + "WHERE studentName = " + s;
            //update 表名 set 字段名='字段值' where 字段值='字段值';上一行代码执行后将所有studentName中的s替换为x
            OleDbCommand myCommand = new OleDbCommand(Update, dbconn);//执行命令
            myCommand.ExecuteNonQuery();//更新数据库，返回受影响行数;可通过判断其是否>0来判断操作是否成功

　　

 

 

查

string s = "'" + textBox1.Text + "'";//接受textBox1的字符串
            string Select = "SELECT *FROM student WHERE studentName = " + s;
            //select *from 表名 where 字段名='字段值';*表示全表，从全表中
            OleDbDataAdapter inst = new OleDbDataAdapter(Select, dbconn);//只匹配满足条件的行
　　　　　　　inst.FILL(ds);

　　

查询后：

MessageBox.Show(ds.Tables[0].Rows[0]["studentNO"].ToString());
//这行代码可以展示ds中第一张表(Tables[0])第一行(Rows[0])["字段名"]的信息;在查找后可以用这种方式输出提示相关信息

 

语句也可以：

string sql = string.Format("INSERT INTO RuleSetInfo(guid,rsName,rsType,rsAdmin,rsPath,rsDate,rsDesc,rsNote) VALUES({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}')", guid, ruleSetName, fileType, admin, rsPath,dateNow, rsDesc, Note);

 

 

 

 

 

类的写法

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    class MDBHelp
    {
        private string _fileName;
        private string _connectionString;
        private OleDbConnection _odcConnection;
        private  int row=0;

        /// <summary>  
        /// 构建函数  
        /// </summary>  
        /// <param name="fileName">MDB文件（含完整路徑）</param>  
        public MDBHelp(string fileName)
        {
            this._fileName = fileName;
            this._connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";";
        }


        /// <summary>  
        /// 建立连接（打开数据库文件）  
        /// </summary>  
        public void Open()
        {
            try
            {
                // 建立连接  
                this._odcConnection = new OleDbConnection(this._connectionString);
                // 打开连接  
                this._odcConnection.Open();
            }
            catch (Exception)
            {
                throw new Exception("嘗試打开 " + this._fileName + " 失敗, 請確認文件是否存在！");
            }
        }


        /// <summary>  
        /// 断开连接（关闭据库文件）  
        /// </summary>  
        public void Close()
        {
            this._odcConnection.Close();
        }


        /// <summary>  
        /// 增 
        /// </summary>  
        /// <param name="sql">sql命令</param>  
        /// <returns>以DataTable形式返回数据</returns>  
        public int GetDataIns(string sql)
        {
             try
             {
                 row = 0;
                 OleDbCommand adapter = new OleDbCommand(sql, this._odcConnection);//执行命令
                 row = adapter.ExecuteNonQuery();
                 
             }
             catch (Exception)
             {
                 
             }

             return row;
          
          
        }



        /// <summary>
        /// 删
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int GetDataDel(string sql)
        {
            DataSet ds = new DataSet();
            try
            {

                row = 0;
                OleDbCommand adapter = new OleDbCommand(sql, this._odcConnection);//执行命令
                row = adapter.ExecuteNonQuery();//更新数据库，返回受影响行数;可通过判断其是否>0来判断操作是否成功
            }
            catch (Exception)
            {
                //throw new Exception("sql語句： " + sql + " 執行失敗！");
            }

            return row;
        }



        /// <summary>
        /// 改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int GetDataUpd(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                row = 0;
                OleDbCommand adapter = new OleDbCommand(sql, this._odcConnection);//执行命令
                row =  adapter.ExecuteNonQuery();

            }
            catch (Exception)
            {
                //throw new Exception("sql語句： " + sql + " 執行失敗！");
            }

            return row;
        }



        /// <summary>
        /// 查
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>

        public DataSet GetDataSel(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, this._odcConnection);
                adapter.Fill(ds);
            }
            catch (Exception)
            {
                throw new Exception("sql語句： " + sql + " 執行失敗！");
            }

            return ds;
        }  
    }
}