1，ADO.NET访问Access(文本数据库)数据操作(CRUD)

 

2，DatabaseDesign

文本数据库Northwind.mdb

3，/App_Code

 3.1,/App_Code/DBConnection.cs

//引用Access文本数据的类using System.Data.OleDb;/// 
/// DBConnection 的摘要说明/// 
public class DBConnection{    OleDbConnection con = null;       public DBConnection()    {                con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\\northwind.mdb;");    }    public OleDbConnection Con    {        get { return con; }        set { con = value; }    }}

3.2,/App_Code/ProductsInfo.cs

/// 
/// ProductsInfo 的摘要说明/// 
public class ProductsInfo{    //字段    int productid;    string productname;        decimal unitprice;           int categoryid;        public ProductsInfo()    {        //        // TODO: 在此处添加构造函数逻辑        //    }    //插入商品    public ProductsInfo(string productname, decimal unitprice, int categoryid)    {        this.productname = productname;        this.unitprice = unitprice;        this.categoryid = categoryid;    }    //查看全部    //插入商品    public ProductsInfo(int productid, string productname, decimal unitprice, int categoryid)    {        this.productid = productid;        this.productname = productname;        this.unitprice = unitprice;        this.categoryid = categoryid;    }    //封装    public int Productid    {        get { return productid; }        set { productid = value; }    }    public string Productname    {        get { return productname; }        set { productname = value; }    }    public decimal Unitprice    {      get { return unitprice; }      set { unitprice = value; }    }    public int Categoryid    {        get { return categoryid; }        set { categoryid = value; }    }}

3.3,/App_Code/ProductsOper.cs  (注意在做删除做作时，请在delete之后添加关键词“from”)

using System.Data.OleDb;using System.Collections.Generic;/// 
/// ProductsOper 的摘要说明/// 
public class ProductsOper{    public ProductsOper()    {        //        // TODO: 在此处添加构造函数逻辑        //    }    //插入商品    public static void Insert(ProductsInfo product)    {        string sql = "insert into 产品(产品名称,单价,类别ID) values(@productname,@unitprice,@categoryid)";        OleDbConnection con = new DBConnection().Con;        OleDbCommand com = new OleDbCommand();        com = con.CreateCommand();        com.CommandText = sql;        //配参        com.Parameters.Add(new OleDbParameter("@productname", product.Productname));        com.Parameters.Add(new OleDbParameter("@unitprice", product.Unitprice));        com.Parameters.Add(new OleDbParameter("@categoryid", product.Categoryid));        con.Open();        com.ExecuteNonQuery();        con.Close();    }    //查所有产品    public static IList
      
        GetProductByCateid(int cateid)    {        string sql = "select 产品ID,产品名称,单价,类别ID from 产品 where 类别ID=@cateid";        IList
       
         products = new List
        
         ();        OleDbConnection con = new DBConnection().Con;        OleDbCommand com = new OleDbCommand();        com = con.CreateCommand();        com.Parameters.Add(new OleDbParameter("@cateid", cateid));        com.CommandText = sql;        con.Open();        OleDbDataReader oddr = com.ExecuteReader();        while (oddr.Read())        {            ProductsInfo product = new ProductsInfo(oddr.GetInt32(0), oddr.GetString(1), oddr.GetDecimal(2), oddr.GetInt32(3));            products.Add(product);        }        con.Close();        return products;    }    //根据productid删除    public static void Delete(int productid)    {        string sql = "delete from 产品 where 产品ID=@productid";        OleDbConnection con=new DBConnection().Con;        OleDbCommand com=new OleDbCommand();        com=con.CreateCommand();        //配参        com.Parameters.Add(new OleDbParameter("@productid", productid));        com.CommandText=sql;        con.Open();        com.ExecuteNonQuery();        con.Close();    }    ////根据productid更新数据    public static void Update(ProductsInfo prod)    {        string sql = "update 产品 set 产品名称=@productname,单价=@unitprice,类别ID=@categoryid "            +" where 产品ID=@productid";        OleDbConnection con = new DBConnection().Con;        OleDbCommand com = new OleDbCommand();        com = con.CreateCommand();        com.CommandText = sql;        //配参        com.Parameters.Add(new OleDbParameter("@productname", prod.Productname));        com.Parameters.Add(new OleDbParameter("@unitprice", prod.Unitprice));        com.Parameters.Add(new OleDbParameter("@categoryid", prod.Categoryid));        com.Parameters.Add(new OleDbParameter("@productid", prod.Productid));        con.Open();        com.ExecuteNonQuery();        con.Close();    }}
        
       
      

4，功能截图

5,源代码下载 

 

作者： 出处： 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。