回看自己学习.NET以来操作数据库的历史,从萌芽阶段的ADO.NET,自我封装的SQLhelp的懵懂,使用objectDataSource的便捷,Enterprise Library之类的选择,开源ORM的探索,再到现在LINQ和Entity Framework,一路走来深感技术就是第一生产力深刻道理.
这段日子要重新上路了,是时候温习那些风尘已久的ADO.NET代码,再次踏踏实实书写那些增删查修的代码...
今天写的Demo(数据库使用Northwind两个表 Products 和 Categories)
凌乱的界面:)
![](https://p-blog.csdn.net/images/p_blog_csdn_net/linqingfeng/ADO_DEMO.jpg)
1.后台代码:
Product.cs
#region Product.cs
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//***********************************************************************
* 文件名: Product.cs
* 功能: 产品实体类
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
* 创建时间: 2007-10-14
* 创建人: 清风
* 最后修改时间:
* 最后修改人:
***********************************************************************/
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
///Product 的摘要说明
/// </summary>
public class Product
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
private int _pID;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
public int PID
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
get ...{ return _pID; }
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
set ...{ _pID = value; }
}
private string _pName;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
public string PName
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
get ...{ return _pName; }
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
set ...{ _pName = value; }
}
private double _pUnitPrice;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
public double PUnitPrice
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
get ...{ return _pUnitPrice; }
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
set ...{ _pUnitPrice = value; }
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
public Product()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
//
//TODO: 在此处添加构造函数逻辑
//
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#endregion
Category.cs
#region Category.cs
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//***********************************************************************
* 文件名: Category.cs
* 功能: 种类实体类
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
* 创建时间: 2007-10-14
* 创建人: 清风
* 最后修改时间:
* 最后修改人:
***********************************************************************/
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
///Category 的摘要说明
/// </summary>
public class Category
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
private int _cID;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
public int CID
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
get ...{ return _cID; }
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
set ...{ _cID = value; }
}
private string _cName;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
public string CName
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
get ...{ return _cName; }
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
set ...{ _cName = value; }
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
public Category()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
//
//TODO: 在此处添加构造函数逻辑
//
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#endregion
CommonApp.cs
#region CommonApp.cs
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//***********************************************************************
* 文件名: CommonApp.cs
* 功能: 公共应用封装
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
* 创建时间: 2007-10-14
* 创建人: 清风
* 最后修改时间:
* 最后修改人:
***********************************************************************/
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
///CommonApp 的摘要说明
/// </summary>
public class CommonApp
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
public CommonApp()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
//
//TODO: 在此处添加构造函数逻辑
//
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 弹出信息提示框
/// </summary>
/// <param name="strMsg"></param>
/// <returns></returns>
public static string msg(string strMsg)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
// 0指strMsg,1是指引号
return String.Format(@"<script language={1}javascript{1}>alert({1}{0}{1});</script>", strMsg, """);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#endregion
DBOperate.cs
#region DBOperate.cs
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//***********************************************************************
* 文件名: DBOperate.cs
* 功能: 封装数据库相关操作
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
* 创建时间: 2007-10-14
* 创建人: 清风
* 最后修改时间: 2007-10-15
* 最后修改人: 清风
***********************************************************************/
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
///DBOperate 的摘要说明
/// </summary>
public class DBOperate
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
public DBOperate()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
//
//TODO: 在此处添加构造函数逻辑
//
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 建立数据库连接
/// </summary>
/// <returns></returns>
public static SqlConnection createConnection()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
return new SqlConnection(@"Data Source=.sqlexpress;Initial Catalog=Northwind;Integrated Security=True;Pooling=False;");
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 列出所有产品种类
/// </summary>
/// <returns></returns>
public static ArrayList listCategories()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
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++)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
DataRow dr = ds.Tables["Categorices"].Rows[i];
alCategories.Add(dr["CategoryName"]);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
return alCategories;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 根据种类查找产品
/// </summary>
/// <param name="c"></param>
/// <returns></returns>
public static DataTable selectProducts(Category c)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
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);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
DataSet ds = new DataSet();
da.Fill(ds, "Products");
return ds.Tables["Products"];
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 根据产品名称查找产品
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
public static DataTable findProducts(Product p)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
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"];
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 插入产品种类
/// </summary>
/// <param name="c"></param>
/// <returns></returns>
public static bool insertCategory(Category c)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
try
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
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);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
con.Open();
cmd.ExecuteNonQuery();
con.Close();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
return true;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
catch (Exception e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
return false;
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 根据种类名删除种类
/// </summary>
/// <param name="c"></param>
/// <param name="bIsIncludeProducts">是否同时删除改种类的产品</param>
/// <returns></returns>
public static bool deleteCategory(Category c, bool bIsIncludeProducts)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
string strSQL = "";
if (bIsIncludeProducts)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
strSQL = "DELETE " +
"FROM Products " +
"WHERE CategoryID IN ( " +
"SELECT CategoryID " +
"FROM Categories " +
"WHERE CategoryName = @cName);" +
"DELETE " +
"FROM Categories " +
"WHERE CategoryName = @cName; ";
}
else
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
strSQL = "DELETE " +
"FROM Categories " +
"WHERE CategoryName = @cName; ";
}
try
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
SqlConnection con = DBOperate.createConnection();
SqlCommand cmd = new SqlCommand(strSQL, con);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
SqlParameter paraCategoryName = new SqlParameter("@cName", SqlDbType.VarChar, 15);
paraCategoryName.Value = c.CName;
cmd.Parameters.Add(paraCategoryName);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
con.Open();
cmd.ExecuteNonQuery();
con.Close();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
return true;
}
catch (Exception e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
return false;
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 更新种类名称
/// </summary>
/// <param name="oldc">旧种类</param>
/// <param name="newc">新种类</param>
/// <returns></returns>
public static bool updateCategoryName(Category oldc,Category newc)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
try
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
SqlConnection con = DBOperate.createConnection();
SqlCommand cmd = new SqlCommand("UPDATE Categories SET CategoryName = @newCName WHERE CategoryName = @oldCName;", con);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
SqlParameter paraNewCategoryName = new SqlParameter("@newCName", SqlDbType.VarChar, 15);
paraNewCategoryName.Value = newc.CName;
cmd.Parameters.Add(paraNewCategoryName);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
SqlParameter paraOldCategoryName = new SqlParameter("@oldCName", SqlDbType.VarChar, 15);
paraOldCategoryName.Value = oldc.CName;
cmd.Parameters.Add(paraOldCategoryName);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
con.Open();
cmd.ExecuteNonQuery();
con.Close();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
return true;
}
catch (Exception e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
return false;
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 新增一个产品
/// </summary>
/// <param name="p"></param>
/// <param name="c"></param>
/// <returns></returns>
public static bool insertProduct(Product p, Category c)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
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) ";
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
try
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
SqlConnection con = DBOperate.createConnection();
SqlCommand cmd = new SqlCommand(strSQL, con);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
SqlParameter paraCategoryName = new SqlParameter("@cName", SqlDbType.VarChar, 15);
paraCategoryName.Value = c.CName;
cmd.Parameters.Add(paraCategoryName);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
SqlParameter paraProductName = new SqlParameter("@pName", SqlDbType.VarChar, 40);
paraProductName.Value = p.PName;
cmd.Parameters.Add(paraProductName);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
SqlParameter paraUnitPrice = new SqlParameter("@pUnitPrice", SqlDbType.Money);
paraUnitPrice.Value = p.PUnitPrice;
cmd.Parameters.Add(paraUnitPrice);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
con.Open();
cmd.ExecuteNonQuery();
con.Close();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
return true;
}
catch (Exception)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
return false;
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 删除一个产品
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
public static bool deleteProduct(Product p)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
try
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
SqlConnection con = DBOperate.createConnection();
SqlCommand cmd = new SqlCommand("DELETE FROM Products WHERE ProductName = @pName;", con);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
SqlParameter paraProductName = new SqlParameter("@pName", SqlDbType.VarChar, 40);
paraProductName.Value = p.PName;
cmd.Parameters.Add(paraProductName);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
con.Open();
cmd.ExecuteNonQuery();
con.Close();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
return true;
}
catch (Exception e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
return false;
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 动态生成GridView的Columns
/// </summary>
/// <param name="gv"></param>
/// <param name="dt"></param>
/// <returns></returns>
public static GridView dynamicGenerateColumns(GridView gv, DataTable dt)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
// 把GridView的自动产生列设置为false,否则会出现重复列
gv.AutoGenerateColumns = false;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
// 清空所有的Columns
gv.Columns.Clear();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
// 遍历DataTable 的每个Columns,然后添加到GridView中去
foreach (DataColumn item in dt.Columns)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
BoundField col = new BoundField();
col.HeaderText = item.ColumnName;
col.DataField = item.ColumnName;
col.Visible = true;
gv.Columns.Add(col);
}
return gv;
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#endregion
2.前台代码
Default.aspx.cs
#region Default.aspx.cs
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//***********************************************************************
* 文件名: Default.aspx.cs
* 功能: Default.aspx上的业务逻辑相关操作
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
* 创建时间: 2007-10-14
* 创建人: 清风
* 最后修改时间: 2007-10-15
* 最后修改人: 清风
***********************************************************************/
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
public partial class _Default : System.Web.UI.Page
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
protected void Page_Load(object sender, EventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
if (!Page.IsPostBack)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
// 初始化页面显示的数据
dbFill();
}
lbMessage.Text = "";
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 填充页面显示的数据
/// </summary>
protected void dbFill()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
// 填充现在的产品类别列表
ddlCategoryName.Items.Clear();
ddlCategoryName2.Items.Clear();
ArrayList alCategories = DBOperate.listCategories();
foreach (string item in alCategories)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
ddlCategoryName.Items.Add(item);
ddlCategoryName2.Items.Add(item);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
//显示产品列表
Category c = new Category();
c.CName = ddlCategoryName.SelectedItem.Text;
this.gvProducts = DBOperate.dynamicGenerateColumns(this.gvProducts, DBOperate.selectProducts(c));
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
this.gvProducts.Columns[0].HeaderText = "产品名";
this.gvProducts.Columns[1].HeaderText = "产品单价";
this.gvProducts.Columns[2].HeaderText = "产品种类";
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
this.gvProducts.DataSource = DBOperate.selectProducts(c);
this.gvProducts.DataBind();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 查找产品
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void bFindProducts_Click(object sender, EventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Product p = new Product();
p.PName = this.tbProductName.Text;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
this.gvProducts = DBOperate.dynamicGenerateColumns(this.gvProducts,DBOperate.findProducts(p));
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
this.gvProducts.Columns[0].HeaderText = "产品名";
this.gvProducts.Columns[1].HeaderText = "产品单价";
this.gvProducts.Columns[2].HeaderText = "产品种类";
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
this.gvProducts.DataSource = DBOperate.findProducts(p);
this.gvProducts.DataBind();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 列出该种类的产品
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ddlCategoryName_SelectedIndexChanged(object sender, EventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Category c = new Category();
c.CName = this.ddlCategoryName.SelectedItem.Text;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
// 填写种类名,方便修改种类名
tbUpdateCategoryName.Text = this.ddlCategoryName.SelectedItem.Text;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
this.gvProducts = DBOperate.dynamicGenerateColumns(this.gvProducts, DBOperate.selectProducts(c));
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
this.gvProducts.Columns[0].HeaderText = "产品名";
this.gvProducts.Columns[1].HeaderText = "产品单价";
this.gvProducts.Columns[2].HeaderText = "产品种类";
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
this.gvProducts.DataSource = DBOperate.selectProducts(c);
this.gvProducts.DataBind();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 新增种类
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void bInsertCategory_Click(object sender, EventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Category c = new Category();
c.CName = this.tbCategoryName.Text;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
if (DBOperate.insertCategory(c))
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
dbFill();
lbMessage.Text = CommonApp.msg("成功添加种类"+ c.CName + ".");
}
else
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
lbMessage.Text = CommonApp.msg("添加种类" + c.CName + "失败,请重新操作!");
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 删除种类
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void bDeleteCategory_Click(object sender, EventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Category c = new Category();
c.CName = this.ddlCategoryName.SelectedItem.Text;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
if(DBOperate.deleteCategory(c,this.chkIsIncludeProducts.Checked))
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
dbFill();
lbMessage.Text = CommonApp.msg("成功删除种类" + c.CName + ".");
}
else
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
lbMessage.Text = CommonApp.msg("删除种类" + c.CName + "失败,请重新操作!");
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 新增产品
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void bInsertProduct_Click(object sender, EventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Category c = new Category();
c.CName = this.ddlCategoryName2.SelectedItem.Text;
c.CID = this.ddlCategoryName2.SelectedIndex;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
Product p = new Product();
p.PName = this.tbInsertProductName.Text;
p.PUnitPrice = Convert.ToDouble(this.tbInsertProductUnitPrice.Text.ToString());
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
if (DBOperate.insertProduct(p, c))
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
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
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
lbMessage.Text = CommonApp.msg("添加产品" + p.PName + "失败,请重新操作!");
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 删除产品
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void bDeleteProduct_Click(object sender, EventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Product p = new Product();
p.PName = this.tbProductName.Text;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
if (DBOperate.deleteProduct(p))
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
dbFill();
lbMessage.Text = CommonApp.msg("成功删除产品" + p.PName + ".");
}
else
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
lbMessage.Text = CommonApp.msg("删除产品" + p.PName + "失败,请重新操作!");
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//// <summary>
/// 更新种类名
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void bUpdateCategoryName_Click(object sender, EventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Category newc = new Category();
Category oldc = new Category();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
oldc.CName = this.ddlCategoryName.SelectedItem.Text;
oldc.CID = this.ddlCategoryName.SelectedIndex;
newc.CName = this.tbUpdateCategoryName.Text;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
if (DBOperate.updateCategoryName(oldc,newc))
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
dbFill();
this.ddlCategoryName.Items[0].Selected = false;
this.ddlCategoryName.Items[oldc.CID].Selected = true;
ddlCategoryName_SelectedIndexChanged(sender, e);
lbMessage.Text = CommonApp.msg("成功更新种类名.");
}
else
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
lbMessage.Text = CommonApp.msg("更新种类名失败,请重新操作!");
}
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#endregion
3.前台页面:
<%
...
@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default"
%>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<!
DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
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
>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
asp:Button
ID
="bDeleteCategory"
runat
="server"
Text
="删除该种类"
onclick
="bDeleteCategory_Click"
/>
<
asp:CheckBox
ID
="chkIsIncludeProducts"
Text
="同时删除该种类的产品"
Checked
="true"
runat
="server"
/>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
br
/>
<
br
/>
输入新的种类名:
<
asp:TextBox
ID
="tbUpdateCategoryName"
runat
="server"
></
asp:TextBox
>
<
asp:Button
ID
="bUpdateCategoryName"
runat
="server"
Text
="更新种类名"
onclick
="bUpdateCategoryName_Click"
/>
<
br
/>
<
br
/>
输入新增种类的名称:
<
asp:TextBox
ID
="tbCategoryName"
runat
="server"
></
asp:TextBox
>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
asp:Button
ID
="bInsertCategory"
runat
="server"
Text
="新增种类"
onclick
="bInsertCategory_Click"
/>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
br
/>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
br
/>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
br
/>
产品名称:
<
asp:TextBox
ID
="tbProductName"
runat
="server"
></
asp:TextBox
>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
asp:Button
ID
="bFindProducts"
runat
="server"
Text
="查找产品"
onclick
="bFindProducts_Click"
/>
<
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
>