基础温习(一) ADO.NET之增删查修

回看自己学习.NET以来操作数据库的历史,从萌芽阶段的ADO.NET,自我封装的SQLhelp的懵懂,使用objectDataSource的便捷,Enterprise Library之类的选择,开源ORM的探索,再到现在LINQ和Entity Framework,一路走来深感技术就是第一生产力深刻道理.

这段日子要重新上路了,是时候温习那些风尘已久的ADO.NET代码,再次踏踏实实书写那些增删查修的代码...

今天写的Demo(数据库使用Northwind两个表 Products 和 Categories)

凌乱的界面:)

 1.后台代码:

Product.cs#region Product.cs

/**//***********************************************************************
* 文件名:            Product.cs
* 功能:            产品实体类

* 创建时间:            2007-10-14
* 创建人:            清风
* 最后修改时间:        
* 最后修改人:        
***********************************************************************/

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;

/**//// <summary>
///Product 的摘要说明
/// </summary>
public class Product
...{
    private int _pID;

    public int PID
    ...{
        get ...{ return _pID; }
        set ...{ _pID = value; }
    }
    private string _pName;

    public string PName
    ...{
        get ...{ return _pName; }
        set ...{ _pName = value; }
    }
    private double _pUnitPrice;

    public double PUnitPrice
    ...{
        get ...{ return _pUnitPrice; }
        set ...{ _pUnitPrice = value; }
    }

    public Product()
    ...{
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }
}

#endregion 

 

Category.cs#region Category.cs

/**//***********************************************************************
* 文件名:            Category.cs
* 功能:                种类实体类

* 创建时间:            2007-10-14
* 创建人:            清风
* 最后修改时间:        
* 最后修改人:        
***********************************************************************/

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;

/**//// <summary>
///Category 的摘要说明
/// </summary>
public class Category
...{
    private int _cID;

    public int CID
    ...{
        get ...{ return _cID; }
        set ...{ _cID = value; }
    }
    private string _cName;

    public string CName
    ...{
        get ...{ return _cName; }
        set ...{ _cName = value; }
    }

    public Category()
    ...{
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }
}

#endregion

 

CommonApp.cs#region CommonApp.cs

/**//***********************************************************************
* 文件名:            CommonApp.cs
* 功能:                公共应用封装

* 创建时间:            2007-10-14
* 创建人:            清风
* 最后修改时间:        
* 最后修改人:        
***********************************************************************/

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;

/**//// <summary>
///CommonApp 的摘要说明
/// </summary>
public class CommonApp
...{
    public CommonApp()
    ...{
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }

    /**//// <summary>
    /// 弹出信息提示框
    /// </summary>
    /// <param name="strMsg"></param>
    /// <returns></returns>
    public static string msg(string strMsg)
    ...{
        // 0指strMsg,1是指引号
        return String.Format(@"<script language={1}javascript{1}>alert({1}{0}{1});</script>", strMsg, """);
    }


}

#endregion

 

DBOperate.cs#region DBOperate.cs

/**//***********************************************************************
* 文件名:            DBOperate.cs
* 功能:                封装数据库相关操作

* 创建时间:            2007-10-14
* 创建人:            清风
* 最后修改时间:        2007-10-15
* 最后修改人:        清风
***********************************************************************/

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections;

/**//// <summary>
///DBOperate 的摘要说明
/// </summary>
public class DBOperate
...{
    public DBOperate()
    ...{
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }

    /**//// <summary>
    /// 建立数据库连接
    /// </summary>
    /// <returns></returns>
    public static SqlConnection createConnection()
    ...{
        return new SqlConnection(@"Data Source=.sqlexpress;Initial Catalog=Northwind;Integrated Security=True;Pooling=False;");
    }

    /**//// <summary>
    /// 列出所有产品种类
    /// </summary>
    /// <returns></returns>
    public static ArrayList listCategories()
    ...{
        SqlConnection con = DBOperate.createConnection();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = new SqlCommand("SELECT CategoryName FROM Categories;",con);
        DataSet ds = new DataSet();
        int iRowCount = da.Fill(ds, "Categorices");
        ArrayList alCategories = new ArrayList();
        for (int i = 0; i < iRowCount; i++)
        ...{
            DataRow dr = ds.Tables["Categorices"].Rows[i];
            alCategories.Add(dr["CategoryName"]);
        }

        return alCategories;
    }

    /**//// <summary>
    /// 根据种类查找产品
    /// </summary>
    /// <param name="c"></param>
    /// <returns></returns>
    public static DataTable selectProducts(Category c)
    ...{
        SqlConnection con = DBOperate.createConnection();
        SqlDataAdapter da = new SqlDataAdapter();
        string strCategoryName = c.CName;
        string strSQL = "SELECT p.ProductName,p.UnitPrice,c.CategoryName " 
                           + " FROM  Products AS p, Categories AS c "
                           + " WHERE p.CategoryID = c.CategoryID AND p.CategoryID IN ( " 
                              + "SELECT CategoryID " 
                              +  "FROM Categories " 
                              + "WHERE CategoryName like '" + strCategoryName + "%') " 
                              + "ORDER BY p.ProductName ASC;";
        da.SelectCommand = new SqlCommand(strSQL, con);

        DataSet ds = new DataSet();
        da.Fill(ds, "Products");
        return ds.Tables["Products"];
    }


    /**//// <summary>
    /// 根据产品名称查找产品
    /// </summary>
    /// <param name="p"></param>
    /// <returns></returns>
    public static DataTable findProducts(Product p)
    ...{
        SqlConnection con = DBOperate.createConnection();
        SqlDataAdapter da = new SqlDataAdapter();
        string strProductName = p.PName;
        string strSQL = "SELECT p.ProductName,p.UnitPrice,c.CategoryName "
                       + "FROM  Products AS p, Categories AS c "
                       + "WHERE p.CategoryID = c.CategoryID AND p.ProductName like '%" + strProductName + "%';";
        da.SelectCommand = new SqlCommand(strSQL, con);
        DataSet ds = new DataSet();
        da.Fill(ds, "Products");
        return ds.Tables["Products"];
    }


    /**//// <summary>
    /// 插入产品种类
    /// </summary>
    /// <param name="c"></param>
    /// <returns></returns>
    public static bool insertCategory(Category c)
    ...{
        try
        ...{

            SqlConnection con = DBOperate.createConnection();
            SqlCommand cmd = new SqlCommand("INSERT INTO Categories (CategoryName) VALUES (@cName);",con);
     
            SqlParameter paraCategoryName = new SqlParameter("@cName", SqlDbType.VarChar, 15);
            paraCategoryName.Value = c.CName;
            cmd.Parameters.Add(paraCategoryName);

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;

        }
        catch (Exception e)
        ...{
            return false;
        }
    }

    /**//// <summary>
    /// 根据种类名删除种类
    /// </summary>
    /// <param name="c"></param>
    /// <param name="bIsIncludeProducts">是否同时删除改种类的产品</param>
    /// <returns></returns>
    public static bool deleteCategory(Category c, bool bIsIncludeProducts)
    ...{
        string strSQL = "";
        if (bIsIncludeProducts)
        ...{
            strSQL = "DELETE " +
                     "FROM Products " +
                     "WHERE CategoryID IN ( " +
                     "SELECT CategoryID " +
                     "FROM Categories " +
                     "WHERE CategoryName = @cName);" +
                     "DELETE " +
                     "FROM Categories " +
                     "WHERE CategoryName = @cName; ";
        }
        else
        ...{
            strSQL = "DELETE " +
                     "FROM Categories " +
                     "WHERE CategoryName = @cName; ";
        }
        try
        ...{
            SqlConnection con = DBOperate.createConnection();
            SqlCommand cmd = new SqlCommand(strSQL, con);

            SqlParameter paraCategoryName = new SqlParameter("@cName", SqlDbType.VarChar, 15);
            paraCategoryName.Value = c.CName;
            cmd.Parameters.Add(paraCategoryName);

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        catch (Exception e)
        ...{
            return false;
        }
    }

    /**//// <summary>
    /// 更新种类名称
    /// </summary>
    /// <param name="oldc">旧种类</param>
    /// <param name="newc">新种类</param>
    /// <returns></returns>
    public static bool updateCategoryName(Category oldc,Category newc)
    ...{
        try
        ...{
            SqlConnection con = DBOperate.createConnection();
            SqlCommand cmd = new SqlCommand("UPDATE Categories SET CategoryName = @newCName WHERE CategoryName = @oldCName;", con);

            SqlParameter paraNewCategoryName = new SqlParameter("@newCName", SqlDbType.VarChar, 15);
            paraNewCategoryName.Value = newc.CName;
            cmd.Parameters.Add(paraNewCategoryName);

            SqlParameter paraOldCategoryName = new SqlParameter("@oldCName", SqlDbType.VarChar, 15);
            paraOldCategoryName.Value = oldc.CName;
            cmd.Parameters.Add(paraOldCategoryName);

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        catch (Exception e)
        ...{
            return false;
        }
     
    }
    /**//// <summary>
    /// 新增一个产品
    /// </summary>
    /// <param name="p"></param>
    /// <param name="c"></param>
    /// <returns></returns>
    public static bool insertProduct(Product p, Category c)
    ...{
        string strSQL = "DECLARE @cID INT " +
                        "SET @cID = (SELECT TOP 1 CategoryID FROM Categories WHERE CategoryName = @cName) " +
                        "INSERT INTO Products (ProductName,CategoryID,UnitPrice) " +
                        "VALUES (@pName,@cID,@pUnitPrice) ";

        try
        ...{
            SqlConnection con = DBOperate.createConnection();
            SqlCommand cmd = new SqlCommand(strSQL, con);

            SqlParameter paraCategoryName = new SqlParameter("@cName", SqlDbType.VarChar, 15);
            paraCategoryName.Value = c.CName;
            cmd.Parameters.Add(paraCategoryName);

            SqlParameter paraProductName = new SqlParameter("@pName", SqlDbType.VarChar, 40);
            paraProductName.Value = p.PName;
            cmd.Parameters.Add(paraProductName);

            SqlParameter paraUnitPrice = new SqlParameter("@pUnitPrice", SqlDbType.Money);
            paraUnitPrice.Value = p.PUnitPrice;
            cmd.Parameters.Add(paraUnitPrice);


            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        catch (Exception)
        ...{
            return false;
        }
    }

    /**//// <summary>
    /// 删除一个产品
    /// </summary>
    /// <param name="p"></param>
    /// <returns></returns>
    public static bool deleteProduct(Product p)
    ...{
        try
        ...{
            SqlConnection con = DBOperate.createConnection();
            SqlCommand cmd = new SqlCommand("DELETE FROM Products WHERE ProductName = @pName;", con);

            SqlParameter paraProductName = new SqlParameter("@pName", SqlDbType.VarChar, 40);
            paraProductName.Value = p.PName;
            cmd.Parameters.Add(paraProductName);

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        catch (Exception e)
        ...{
            return false;
        }
    }
        


        

    /**//// <summary>
    /// 动态生成GridView的Columns
    /// </summary>
    /// <param name="gv"></param>
    /// <param name="dt"></param>
    /// <returns></returns>
    public static GridView dynamicGenerateColumns(GridView gv, DataTable dt)
    ...{
        // 把GridView的自动产生列设置为false,否则会出现重复列
        gv.AutoGenerateColumns = false;

        // 清空所有的Columns
        gv.Columns.Clear();

        // 遍历DataTable 的每个Columns,然后添加到GridView中去
        foreach (DataColumn item in dt.Columns)
        ...{
            BoundField col = new BoundField();
            col.HeaderText = item.ColumnName;
            col.DataField = item.ColumnName;
            col.Visible = true;
            gv.Columns.Add(col);
        }
        return gv;
    }    
}

#endregion

2.前台代码

Default.aspx.cs#region Default.aspx.cs

/**//***********************************************************************
* 文件名:            Default.aspx.cs
* 功能:                Default.aspx上的业务逻辑相关操作

* 创建时间:            2007-10-14
* 创建人:            清风
* 最后修改时间:        2007-10-15
* 最后修改人:        清风
***********************************************************************/

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Collections;

public partial class _Default : System.Web.UI.Page 
...{
    protected void Page_Load(object sender, EventArgs e)
    ...{
        if (!Page.IsPostBack)
        ...{
            // 初始化页面显示的数据
            dbFill();
        }
        lbMessage.Text = "";
    }

    /**//// <summary>
    /// 填充页面显示的数据
    /// </summary>
    protected void dbFill()
    ...{
        // 填充现在的产品类别列表
        ddlCategoryName.Items.Clear();
        ddlCategoryName2.Items.Clear();
        ArrayList alCategories = DBOperate.listCategories();
        foreach (string item in alCategories)
        ...{
            ddlCategoryName.Items.Add(item);
            ddlCategoryName2.Items.Add(item);
        }

        //显示产品列表
        Category c = new Category();
        c.CName = ddlCategoryName.SelectedItem.Text;
        this.gvProducts = DBOperate.dynamicGenerateColumns(this.gvProducts, DBOperate.selectProducts(c));

        this.gvProducts.Columns[0].HeaderText = "产品名";
        this.gvProducts.Columns[1].HeaderText = "产品单价";
        this.gvProducts.Columns[2].HeaderText = "产品种类";

        this.gvProducts.DataSource = DBOperate.selectProducts(c);
        this.gvProducts.DataBind();

    }

    /**//// <summary>
    /// 查找产品
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void bFindProducts_Click(object sender, EventArgs e)
    ...{
        Product p = new Product();
        p.PName = this.tbProductName.Text;

        this.gvProducts = DBOperate.dynamicGenerateColumns(this.gvProducts,DBOperate.findProducts(p));

        this.gvProducts.Columns[0].HeaderText = "产品名";
        this.gvProducts.Columns[1].HeaderText = "产品单价";
        this.gvProducts.Columns[2].HeaderText = "产品种类";

        this.gvProducts.DataSource = DBOperate.findProducts(p);
        this.gvProducts.DataBind();

    }

    /**//// <summary>
    /// 列出该种类的产品
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void ddlCategoryName_SelectedIndexChanged(object sender, EventArgs e)
    ...{
        Category c = new Category();
        c.CName = this.ddlCategoryName.SelectedItem.Text;

        // 填写种类名,方便修改种类名
        tbUpdateCategoryName.Text = this.ddlCategoryName.SelectedItem.Text;

        this.gvProducts = DBOperate.dynamicGenerateColumns(this.gvProducts, DBOperate.selectProducts(c));

        this.gvProducts.Columns[0].HeaderText = "产品名";
        this.gvProducts.Columns[1].HeaderText = "产品单价";
        this.gvProducts.Columns[2].HeaderText = "产品种类";

        this.gvProducts.DataSource = DBOperate.selectProducts(c);
        this.gvProducts.DataBind();

    }

    /**//// <summary>
    /// 新增种类
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void bInsertCategory_Click(object sender, EventArgs e)
    ...{
        Category c = new Category();
        c.CName = this.tbCategoryName.Text;

        if (DBOperate.insertCategory(c))
        ...{
            dbFill();
            lbMessage.Text = CommonApp.msg("成功添加种类"+ c.CName + ".");
        }
        else
        ...{
            lbMessage.Text = CommonApp.msg("添加种类" + c.CName + "失败,请重新操作!");
        }
    }

    /**//// <summary>
    /// 删除种类
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void bDeleteCategory_Click(object sender, EventArgs e)
    ...{
        Category c = new Category();
        c.CName = this.ddlCategoryName.SelectedItem.Text;

        if(DBOperate.deleteCategory(c,this.chkIsIncludeProducts.Checked))
        ...{
            dbFill();
            lbMessage.Text = CommonApp.msg("成功删除种类" + c.CName + ".");
        }
        else
        ...{
            lbMessage.Text = CommonApp.msg("删除种类" + c.CName + "失败,请重新操作!");
        }
    }

    /**//// <summary>
    /// 新增产品
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void bInsertProduct_Click(object sender, EventArgs e)
    ...{
        Category c = new Category();
        c.CName = this.ddlCategoryName2.SelectedItem.Text;
        c.CID = this.ddlCategoryName2.SelectedIndex;

        Product p = new Product();
        p.PName = this.tbInsertProductName.Text;
        p.PUnitPrice = Convert.ToDouble(this.tbInsertProductUnitPrice.Text.ToString());

        if (DBOperate.insertProduct(p, c))
        ...{
            dbFill();
            this.ddlCategoryName.Items[0].Selected = false;
            this.ddlCategoryName.Items[c.CID].Selected = true;
            ddlCategoryName_SelectedIndexChanged(sender, e);
            lbMessage.Text = CommonApp.msg("成功添加产品" + p.PName + ".");
        }
        else
        ...{
            lbMessage.Text = CommonApp.msg("添加产品" + p.PName + "失败,请重新操作!");
        }
    }

    /**//// <summary>
    /// 删除产品
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void bDeleteProduct_Click(object sender, EventArgs e)
    ...{
        Product p = new Product();
        p.PName = this.tbProductName.Text;

        if (DBOperate.deleteProduct(p))
        ...{
            dbFill();
            lbMessage.Text = CommonApp.msg("成功删除产品" + p.PName + ".");
        }
        else
        ...{
            lbMessage.Text = CommonApp.msg("删除产品" + p.PName + "失败,请重新操作!");
        }
    }

    /**//// <summary>
    /// 更新种类名
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void bUpdateCategoryName_Click(object sender, EventArgs e)
    ...{
        Category newc = new Category();
        Category oldc = new Category();

        oldc.CName = this.ddlCategoryName.SelectedItem.Text;
        oldc.CID = this.ddlCategoryName.SelectedIndex;
        newc.CName = this.tbUpdateCategoryName.Text;

        if (DBOperate.updateCategoryName(oldc,newc))
        ...{
            dbFill();
            this.ddlCategoryName.Items[0].Selected = false;
            this.ddlCategoryName.Items[oldc.CID].Selected = true;
            ddlCategoryName_SelectedIndexChanged(sender, e);
            lbMessage.Text = CommonApp.msg("成功更新种类名.");
        }
        else
        ...{
            lbMessage.Text = CommonApp.msg("更新种类名失败,请重新操作!");
        }
    }
}

#endregion

3.前台页面:

<%...@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>ADO.NET Demo</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lbMessage" runat="server" Text="Label"></asp:Label><br />
        种类名:
        <asp:DropDownList ID="ddlCategoryName" runat="server" Height="29px" 
            Width="126px" AutoPostBack="true"  
            onselectedindexchanged="ddlCategoryName_SelectedIndexChanged" >
        </asp:DropDownList>

        &nbsp;&nbsp;&nbsp;
        <asp:Button ID="bDeleteCategory" runat="server" Text="删除该种类" 
            onclick="bDeleteCategory_Click" />
&nbsp;&nbsp;
        <asp:CheckBox ID="chkIsIncludeProducts" Text="同时删除该种类的产品" Checked="true" runat="server" />

        <br />
        <br />
        输入新的种类名:<asp:TextBox ID="tbUpdateCategoryName" runat="server"></asp:TextBox>
&nbsp;&nbsp;&nbsp;
        <asp:Button ID="bUpdateCategoryName" runat="server" Text="更新种类名" 
            onclick="bUpdateCategoryName_Click" />
        <br />
        <br />
        输入新增种类的名称:<asp:TextBox ID="tbCategoryName" runat="server"></asp:TextBox>

        <asp:Button ID="bInsertCategory" runat="server" Text="新增种类" 
            onclick="bInsertCategory_Click"  />

        <br />

        <br />

        <br />
        产品名称:
        <asp:TextBox ID="tbProductName" runat="server"></asp:TextBox>

        <asp:Button ID="bFindProducts" runat="server" Text="查找产品" 
            onclick="bFindProducts_Click" />
        &nbsp;&nbsp;
        <asp:Button ID="bDeleteProduct" runat="server" Text="删除该产品" 
            onclick="bDeleteProduct_Click" />
        
        <br />
        <br />
        <br />
        产品名:<asp:TextBox ID="tbInsertProductName" runat="server"></asp:TextBox>
        <br />
        产品单价:<asp:TextBox ID="tbInsertProductUnitPrice" runat="server"></asp:TextBox>
        <br />
        产品种类:<asp:DropDownList ID="ddlCategoryName2" runat="server" Height="29px" 
            Width="126
            px">
        </asp:DropDownList>
        <br />
        <asp:Button ID="bInsertProduct" runat="server" Text="添加产品" 
            onclick="bInsertProduct_Click" />
        <br />
        <br />
        <asp:GridView ID="gvProducts" runat="server">
        </asp:GridView>
    </div>
    </form>
</body>
</html>