ADO.NET用法示例

 1.后台代码:

#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
 

 

#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

 

#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

 

#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.前台代码

#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>



posted @ 2007-10-17 13:17  yangjun  阅读(390)  评论(0编辑  收藏  举报