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

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

 

2，DatabaseDesign

文本数据库Northwind.mdb

3，/App_Code

 3.1,/App_Code/DBConnection.cs

//引用Access文本数据的类
using System.Data.OleDb;

/// <summary>
/// DBConnection 的摘要说明
/// </summary>
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

/// <summary>
/// ProductsInfo 的摘要说明
/// </summary>
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;
/// <summary>
/// ProductsOper 的摘要说明
/// </summary>
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<ProductsInfo> GetProductByCateid(int cateid)
    {
        string sql = "select 产品ID,产品名称,单价,类别ID from 产品 where 类别ID=@cateid";
        IList<ProductsInfo> products = new List<ProductsInfo>();

        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,源代码下载 

 http://ylbtechdotnet.googlecode.com/files/ADO-%E6%9D%82%E9%A1%B9.zip

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