关于DropDownList ， GridView的一道测试题

最新推荐文章于 2021-01-03 17:32:55 发布

eclipseluo

最新推荐文章于 2021-01-03 17:32:55 发布

阅读量574

点赞数

CC 4.0 BY-SA版权

分类专栏： ASP.NET 文章标签：测试 dataset string exception sql object

本文链接：https://blog.youkuaiyun.com/eclipseluo/article/details/4791014

ASP.NET 专栏收录该内容

90 篇文章

订阅专栏

本文介绍了一个商品管理系统的设计与实现，包括添加、显示、修改和删除商品等功能。系统采用ASP.NET技术，利用GridView控件展示商品信息，并通过存储过程进行商品单价的更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、数据库

提供数据库db_Shop，其中有两张表：一是商品类别表T_GoodsType；二是商品信息表T_GoodsInfo,两张表的结构为：

T_GoodsType：

字段名称	类型	备注
TypeID	Int	主键，类别id
TypeName	Varchar（50）	类别名称

T_ GoodsInfo：

字段名称	类型	备注
GoodsID	Int	主键，商品id
TypeID	Int	商品类别id
GoodsName	Varchar（50）	商品名称
GoodsPrice	Float	商品价格

二、需要实现的功能

1、添加商品（15分）

2、使用GridView显示所有商品列表（15分）

3、可以按商品类别显示商品列表（15分）

4、修改商品单价（15分）

5、删除一件商品（10分）

三、考试要求：

1、对页面样式无要求；

2、封装数据库访问类（10分）

3、其中【修改商品单价】要求使用存储过程实现；（10分）

4、良好的编码规范，包括：命名、注释等；（10分）

5、总分100分，每部分占得分数已在后面标出；

6、做完之后打包QQ发给我，压缩包命名方式：“ADO.NET测试_张三”

四、功能截图

显示所有商品：

显示显示器类商品：

修改商品单价：

可以删除某个商品：

添加商品：

<%@ 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>Shop</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="lbShowShop" runat="server" Text="商品类别:" ForeColor="red"></asp:Label> <asp:DropDownList ID="DdlSelect" runat="server" AutoPostBack="true" onselectedindexchanged="DdlSelect_SelectedIndexChanged" AppendDataBoundItems="true" > </asp:DropDownList> <asp:Button ID="btShowGoods" runat="server" Text="搜索" onclick="btShowGoods_Click" /> <br /> <asp:GridView ID="Gv_Show" runat="server" BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4" style="width: 500px" Width="211px" Height="168px" DataKeyNames="GoodsID" AutoGenerateColumns="False" onrowcancelingedit="Gv_Show_RowCancelingEdit" onrowediting="Gv_Show_RowEditing" onrowupdating="Gv_Show_RowUpdating" onrowcommand="Gv_Show_RowCommand" > <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" /> <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" /> <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" /> <RowStyle BackColor="White" ForeColor="#330099" /> <Columns> <asp:BoundField HeaderText="商品类别" DataField="TypeName" ReadOnly="true"/> <asp:BoundField DataField="GoodsName" HeaderText="商品名称" ReadOnly="true" /> <asp:BoundField DataField="GoodsPrice" HeaderText="商品价格" /> <asp:CommandField ShowEditButton="true" HeaderText="编辑"/> <asp:TemplateField HeaderText="删除"> <ItemTemplate> <asp:LinkButton ID="lbdel" runat="server" CommandName="del" CommandArgument='<%#Eval("GoodsID") %>'>Delete</asp:LinkButton> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <br /> <br /> <asp:Label ID="lbAdd" runat="server" Text="添加商品:" ForeColor="red"></asp:Label> <br /> 商品类别: <asp:DropDownList ID="DdlSelect1" runat="server" AutoPostBack="true"> </asp:DropDownList><hr/> <br /> 商品名称: <asp:TextBox ID="tbGoodsName" runat="server"></asp:TextBox><hr /> <br /> 商品单价: <asp:TextBox ID="tbGoodsPrice" runat="server"></asp:TextBox> <br /> <div align="left" style=" padding-left:150px" mce_style=" padding-left:150px"><asp:Button ID="btAddGoods" runat="server" Text="确定" onclick="btAddGoods_Click" /></div> <hr/> </span> </div> </form> </body> </html>

后台代码：

using System; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DdlBind(); DdlBindForSelect1(); GvBind(); } } /// <summary> /// 定义数据库连接方法 /// </summary> /// <returns></returns> public SqlHelper OpenConnection() { SqlHelper helper = new SqlHelper(@"Data Source=A102-35/SQLEXPRESS;Initial Catalog=db_Shop;Integrated Security=True"); return helper; } /// <summary> /// 搜索下拉框的绑定方法 /// </summary> public void DdlBind() { SqlHelper helper = OpenConnection(); //创建查询字符串 string strQry = @"select * from T_GoodsType"; //返回数据表 DdlSelect.DataSource = helper.RunProcDataTable(strQry); DdlSelect.Items.Add(new ListItem("显示全部","all")); //绑定文本域 DdlSelect.DataTextField = "TypeName"; //绑定主键域 DdlSelect.DataValueField = "TypeID"; //绑定下拉框控件 DdlSelect.DataBind( ); } /// <summary> /// 添加商品下拉框的数据绑定 /// </summary> public void DdlBindForSelect1() { SqlHelper helper = OpenConnection(); //创建查询字符串 string strQry = @"select * from T_GoodsType"; //返回数据表 DdlSelect1.DataSource = helper.RunProcDataTable(strQry); //绑定文本域 DdlSelect1.DataTextField = "TypeName"; //绑定主键域 DdlSelect1.DataValueField = "TypeID"; //绑定下拉框控件 DdlSelect1.DataBind(); } /// <summary> /// 数据列表的绑定方法 /// </summary> public void GvBind() { SqlHelper helper = OpenConnection(); //创建查询字符串 string strQry = @"select * from T_GoodsInfo,T_GoodsType where T_GoodsInfo.TypeID=T_GoodsType.TypeID Order By T_GoodsType.TypeName"; //返回数据表 Gv_Show.DataSource = helper.RunProcDataTable(strQry); Gv_Show.DataBind(); } /// <summary> /// 获得TypeID的数据列表绑定 /// </summary> /// <param name="str"></param> public void GvBind(string str) { SqlHelper helper = OpenConnection(); string strVar = str; //创建查询字符串 string strQry = @"select * from T_GoodsInfo,T_GoodsType where T_GoodsInfo.TypeID=T_GoodsType.TypeID and T_GoodsInfo.TypeID='" + strVar + "'"; //返回数据表 Gv_Show.DataSource = helper.RunProcDataTable(strQry); Gv_Show.DataBind(); } ///// <summary> ///// 下拉框处理事件 ///// </summary> ///// <param name="sender"></param> ///// <param name="e"></param> protected void DdlSelect_SelectedIndexChanged(object sender, EventArgs e) { //如果为新表，则直接退出事件 if (DdlSelect.SelectedValue == null) { return; } //选择查看全部商品，否则为选择某件商品查看 if (DdlSelect.SelectedValue == "all") { GvBind(); } else { GvBind(DdlSelect.SelectedValue); } } /// <summary> /// 数据列表行编辑 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Gv_Show_RowEditing(object sender, GridViewEditEventArgs e) { Gv_Show.EditIndex = e.NewEditIndex; //该属性获取或设置所编辑的行的索引 //选择查看全部商品，否则为选择某件商品查看 if (DdlSelect.SelectedValue == "all") { GvBind(); } else { GvBind(DdlSelect.SelectedValue); } } /// <summary> /// 数据列表行编辑更新操作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Gv_Show_RowUpdating(object sender, GridViewUpdateEventArgs e) { SqlHelper helper = OpenConnection(); string price = ((TextBox)(Gv_Show.Rows[e.RowIndex].Cells[2].Controls[0])).Text; //获得更新行 int goodsid = Convert.ToInt32(Gv_Show.DataKeys[e.RowIndex].Value); //获得主键 SqlParameter[] para ={new SqlParameter ("@price",SqlDbType.Float), //添加命令变量 new SqlParameter("@goodsid",SqlDbType.Int) }; para[0].Value = float.Parse(price); para[1].Value = goodsid; string sqlStr = "update T_GoodsInfo set GoodsPrice = @price where GoodsID= @goodsid"; //跟新语句 //执行跟新操作 helper.RunSQL(sqlStr, para); if (DdlSelect.SelectedValue == "all") { GvBind(); } else { GvBind(DdlSelect.SelectedValue); } } /// <summary> /// 数据列表行编辑取消操作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Gv_Show_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { Gv_Show.EditIndex = -1; if (DdlSelect.SelectedValue == "all") { GvBind(); } else { GvBind(DdlSelect.SelectedValue); } } /// <summary> /// 数据列表行删除操作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Gv_Show_RowCommand(object sender, GridViewCommandEventArgs e) { //删除指定行的商品 if (e.CommandName == "del") { SqlHelper helper = OpenConnection(); string delStr = @"delete from T_GoodsInfo where GoodsID='" + e.CommandArgument.ToString() + "'"; //执行删除操作 helper.RunSQL(delStr); //选择查看全部商品，否则为选择某件商品查看 if (DdlSelect.SelectedValue == "all") { GvBind(); } else { GvBind(DdlSelect.SelectedValue); } } } /// <summary> /// 搜索商品事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btShowGoods_Click(object sender, EventArgs e) { //如果为新表，则直接退出 if (DdlSelect.SelectedValue == null) { return; } //选择查看全部商品，否则为选择某件商品查看 if (DdlSelect.SelectedValue == "all") { GvBind(); } else { GvBind(DdlSelect.SelectedValue); } } /// <summary> /// 添加商品事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btAddGoods_Click(object sender, EventArgs e) { SqlHelper helper = OpenConnection(); //添加命令参数 SqlParameter[] para ={ new SqlParameter("@typeid",SqlDbType.Int), new SqlParameter("@goodsname",SqlDbType.VarChar,50), new SqlParameter("@goodsprice",SqlDbType.Float) }; para[0].Value = DdlSelect1.SelectedValue; para[0].Direction = ParameterDirection.Input; para[1].Value = tbGoodsName.Text.Trim(); para[1].Direction = ParameterDirection.Input; para[2].Value = tbGoodsPrice.Text.Trim(); para[2].Direction = ParameterDirection.Input; //如果返回受影响的行数，则添加成功，否则添加失败 if ((int)(helper.RunSQL("proc_AddGoods", CommandType.StoredProcedure, para)) == 1) { Response.Write("<mce:script type="text/javascript"></mce:script>"); tbGoodsName.Text = ""; tbGoodsPrice.Text = ""; } else { Response.Write("<mce:script type="text/javascript"></mce:script>"); } } }

另一种较简洁的做法：

using System; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DdlBind(); DdlBindForSelect1(); GvBind(); } } /// <summary> /// 定义数据库连接方法 /// </summary> /// <returns></returns> public SqlHelper OpenConnection() { SqlHelper helper = new SqlHelper(@"Data Source=A102-35/SQLEXPRESS;Initial Catalog=db_Shop;Integrated Security=True"); return helper; } /// <summary> /// 搜索下拉框的绑定方法 /// </summary> public void DdlBind() { SqlHelper helper = OpenConnection(); //创建查询字符串 string strQry = @"select * from T_GoodsType"; //返回数据表 DdlSelect.DataSource = helper.RunProcDataTable(strQry); DdlSelect.Items.Add(new ListItem("显示全部", "all")); //绑定文本域 DdlSelect.DataTextField = "TypeName"; //绑定主键域 DdlSelect.DataValueField = "TypeID"; //绑定下拉框控件 DdlSelect.DataBind(); } /// <summary> /// 添加商品下拉框的数据绑定 /// </summary> public void DdlBindForSelect1() { SqlHelper helper = OpenConnection(); //创建查询字符串 string strQry = @"select * from T_GoodsType"; //返回数据表 DdlSelect1.DataSource = helper.RunProcDataTable(strQry); //绑定文本域 DdlSelect1.DataTextField = "TypeName"; //绑定主键域 DdlSelect1.DataValueField = "TypeID"; //绑定下拉框控件 DdlSelect1.DataBind(); } /// <summary> /// 数据列表的绑定方法 /// </summary> public void GvBind() { SqlHelper helper = OpenConnection(); //创建查询字符串 string strQry = @"select * from T_GoodsInfo,T_GoodsType where T_GoodsInfo.TypeID=T_GoodsType.TypeID "; int DdlgetValue = Convert.ToInt32(DdlSelect.SelectedValue); //判断直接写在一个绑定方法里 if (DdlSelect.SelectedValue != "all") { strQry += "and T_GoodsInfo.TypeID='" + DdlgetValue; } //返回数据表 Gv_Show.DataSource = helper.RunProcDataTable(strQry); Gv_Show.DataBind(); } ///// <summary> ///// 下拉框处理事件 ///// </summary> ///// <param name="sender"></param> ///// <param name="e"></param> protected void DdlSelect_SelectedIndexChanged(object sender, EventArgs e) { //如果为新表，则直接退出事件 if (DdlSelect.SelectedValue == null) { return; } //绑定列表 GvBind(); } /// <summary> /// 数据列表行编辑 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Gv_Show_RowEditing(object sender, GridViewEditEventArgs e) { Gv_Show.EditIndex = e.NewEditIndex; //该属性获取或设置所编辑的行的索引 GvBind(); } /// <summary> /// 数据列表行编辑更新操作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Gv_Show_RowUpdating(object sender, GridViewUpdateEventArgs e) { SqlHelper helper = OpenConnection(); string price = ((TextBox)(Gv_Show.Rows[e.RowIndex].Cells[2].Controls[0])).Text; //获得更新行 int goodsid = Convert.ToInt32(Gv_Show.DataKeys[e.RowIndex].Value); //获得主键 SqlParameter[] para ={new SqlParameter ("@price",SqlDbType.Float), //添加命令变量 new SqlParameter("@goodsid",SqlDbType.Int) }; para[0].Value = float.Parse(price); para[1].Value = goodsid; string sqlStr = "update T_GoodsInfo set GoodsPrice = @price where GoodsID= @goodsid"; //跟新语句 //执行跟新操作 helper.RunSQL(sqlStr, para); GvBind(); } /// <summary> /// 数据列表行编辑取消操作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Gv_Show_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { Gv_Show.EditIndex = -1; GvBind(); } /// <summary> /// 数据列表行删除操作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Gv_Show_RowCommand(object sender, GridViewCommandEventArgs e) { //删除指定行的商品 if (e.CommandName == "del") { SqlHelper helper = OpenConnection(); string delStr = @"delete from T_GoodsInfo where GoodsID='" + e.CommandArgument.ToString() + "'"; //执行删除操作 helper.RunSQL(delStr); GvBind(); } } /// <summary> /// 搜索商品事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btShowGoods_Click(object sender, EventArgs e) { //如果为新表，则直接退出 if (DdlSelect.SelectedValue == null) { return; } GvBind(); } /// <summary> /// 添加商品事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btAddGoods_Click(object sender, EventArgs e) { SqlHelper helper = OpenConnection(); //添加命令参数 SqlParameter[] para ={ new SqlParameter("@typeid",SqlDbType.Int), new SqlParameter("@goodsname",SqlDbType.VarChar,50), new SqlParameter("@goodsprice",SqlDbType.Float) }; para[0].Value = DdlSelect1.SelectedValue; para[0].Direction = ParameterDirection.Input; para[1].Value = tbGoodsName.Text.Trim(); para[1].Direction = ParameterDirection.Input; para[2].Value = tbGoodsPrice.Text.Trim(); para[2].Direction = ParameterDirection.Input; //如果返回受影响的行数，则添加成功，否则添加失败 if ((int)(helper.RunSQL("proc_AddGoods", CommandType.StoredProcedure, para)) == 1) { Response.Write("<mce:script type="text/javascript"></mce:script>"); tbGoodsName.Text = ""; tbGoodsPrice.Text = ""; } else { Response.Write("<mce:script type="text/javascript"></mce:script>"); } } }

数据库封装类：

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.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; /// <summary> ///Sql 的摘要说明 /// </summary> public class SqlHelper { private string ConnStr = null; private SqlConnection Conn = null; /// <summary> /// 无参构造函数 /// </summary> public SqlHelper() { try { ConnStr = ConfigurationManager.ConnectionStrings["db_Shop"].ConnectionString; Conn = new SqlConnection(ConnStr); Conn.Open(); } catch (Exception ex) { throw ex; } } /// <summary> /// 带连接字符串构造函数 /// </summary> /// <param name="Str"></param> public SqlHelper(string Str) { try { this.ConnStr = Str; Conn = new SqlConnection(ConnStr); Conn.Open(); } catch (Exception ex) { throw ex; } } /// <summary> /// 打开数据库连接 /// </summary> public void OpenConn() { if (Conn == null) { Conn.Open(); } } /// <summary> /// 关闭连接 /// </summary> public void Dispose() { if (Conn != null) { Conn.Close(); Conn.Dispose(); } GC.Collect(); } /// <summary> /// 生成Command对象 /// </summary> /// <param name="SQL">SQL语句</param> /// <returns>SqlCommand对象</returns> public SqlCommand CreateCmd(string SQL) { SqlCommand Cmd; Cmd = new SqlCommand(SQL, Conn); return Cmd; } /// <summary> /// 调用存储过程的Command对象，用于查询 /// </summary> /// <param name="procname">存储过程</param> /// <param name="i">CommandType.StoredProcedure属性值</param> /// <returns>SqlCommand对象</returns> public SqlCommand CreateCmd(string procname, CommandType i) { SqlCommand Cmd= new SqlCommand(); try { Cmd.Connection = Conn; Cmd.CommandType = i; Cmd.CommandText = procname; } catch(Exception Err) { throw Err; } return Cmd; } /// <summary> /// 运行SQL语句执行插入，删除，跟新操作 /// </summary> /// <param name="SQL">SQL语句</param> public int RunSQL(string SQL) { SqlCommand Cmd; Cmd = CreateCmd(SQL); int result; try { result=Cmd.ExecuteNonQuery(); } catch { throw new Exception(SQL); } Dispose(); return result; } /// <summary> /// 运行SQL语句执行插入，删除，跟新操作 /// </summary> /// <param name="SQL">SQL语句</param> /// <param name="pa">命令变量元素</param> public int RunSQL(string SQL, SqlParameter[] pa) { SqlCommand Cmd; Cmd = CreateCmd(SQL); int result; for (int i = 0; i < pa.Length; i++) { Cmd.Parameters.Add(pa[i]); } try { result=Cmd.ExecuteNonQuery(); } catch { throw new Exception(SQL); } Dispose(); return result; } /// <summary> /// 调用存储过程的SQL语句 /// </summary> /// <param name="procname">存储过程</param> /// <param name="i">CommandType.StoredProcedure属性值</param> /// <param name="pa">命令变量元素</param> public int RunSQL(string procname, CommandType i, SqlParameter[] pa) { SqlCommand Cmd; Cmd = CreateCmd(procname,i); int result; for (int j = 0; j < pa.Length; j++) { Cmd.Parameters.Add(pa[j]); } try { result=Cmd.ExecuteNonQuery(); } catch { throw new Exception(procname); } Dispose(); return result; } /// <summary> /// 运行SQL语句返回DataReader /// </summary> /// <param name="SQL">SQL语句</param> /// <returns>SqlDataReader对象.</returns> public SqlDataReader RunProcGetReader(string SQL) { SqlCommand Cmd; Cmd = CreateCmd(SQL); SqlDataReader Dr; try { Dr = Cmd.ExecuteReader(); } catch { throw new Exception(SQL); } //Dispose(Conn); return Dr; } /// <summary> /// 调用存储过程返回DataReader /// </summary> /// <param name="procname">存储过程</param> /// <param name="i">CommandType.StoredProcedure属性值</param> /// <returns>SqlDataReader对象</returns> public SqlDataReader RunProcGetReader(string procname, CommandType i) { SqlCommand Cmd; Cmd = CreateCmd(procname,i); SqlDataReader Dr; try { Dr = Cmd.ExecuteReader(); } catch { throw new Exception(procname); } //Dispose(Conn); return Dr; } /// <summary> /// 返回adapter对象 /// </summary> /// <param name="SQL">SQL语句</param> /// <returns>SqlDataAdapter对象</returns> public SqlDataAdapter CreateAdapter(string SQL) { SqlDataAdapter Da; Da = new SqlDataAdapter(CreateCmd(SQL)); return Da; } /// <summary> /// 调用存储过程返回adapter对象 /// </summary> /// <param name="procname">存储过程</param> /// <param name="i">CommandType.StoredProcedure属性值</param> /// <returns>SqlDataAdapter对象</returns> public SqlDataAdapter CreateAdapter(string procname, CommandType i) { SqlDataAdapter Da; Da = new SqlDataAdapter(CreateCmd(procname, i)); return Da; } /// <summary> /// 填充数据集,，返回dataset对象 /// </summary> /// <param name="SQL">SQL语句</param> /// <returns>DataSet对象</returns> public DataSet RunProcDataSet(string SQL) { SqlDataAdapter Da; Da = CreateAdapter(SQL); DataSet Ds = new DataSet(); try { Da.Fill(Ds); } catch (Exception Err) { throw Err; } Dispose(); return Ds; } /// <summary> /// 填充数据集,，返回dataset对象 /// </summary> /// <param name="SQL">SQL语句</param> /// <param name="tablename">表名</param> /// <returns>DataSet对象</returns> public DataSet RunProcDataSet(string SQL, string tablename) { SqlDataAdapter Da; Da = CreateAdapter(SQL); DataSet Ds = new DataSet(); try { Da.Fill(Ds, tablename); } catch (Exception Err) { throw Err; } Dispose(); return Ds; } /// <summary> /// 调用存储过程填充数据集,返回DataSet对象 /// </summary> /// <param name="procname">存储过程</param> /// <param name="i">CommandType.StoredProcedure属性值</param> /// <param name="tablename">表名</param> /// <returns>DataSet对象</returns> public DataSet RunProcDataSet(string procname, CommandType i, string tablename) { SqlDataAdapter Da; Da = CreateAdapter(procname ,i); DataSet Ds = new DataSet(); try { Da.Fill(Ds, tablename); } catch (Exception Err) { throw Err; } Dispose(); return Ds; } /// <summary> /// 返回DataTable对象 /// </summary> /// <param name="SQL">SQL语句</param> /// <returns>DataTable对象</returns> public DataTable RunProcDataTable(string SQL) { DataTable dt = RunProcDataSet(SQL).Tables[0]; return dt; } /// <summary> /// 返回DataTable对象 /// </summary> /// <param name="SQL">SQL语句</param> /// <param name="tablename">表名</param> /// <returns>DataTable对象</returns> public DataTable RunProcDataTable(string SQL, string tablename) { DataTable dt = RunProcDataSet(SQL, tablename).Tables[tablename]; return dt; } /// <summary> /// 调用存储过程返回DataTable对象 /// </summary> /// <param name="procname">存储过程</param> /// <param name="i">CommandType.StoredProcedure属性值</param> /// <param name="tablename">表名</param> /// <returns>DataSet对象</returns> public DataTable RunProcDataTable(string procname, CommandType i, string tablename) { DataTable dt = RunProcDataSet(procname,i, tablename).Tables[tablename]; return dt; } }

存储过程：

create proc dbo.proc_AddGoods ( @typeid int, @goodsname varchar(50), @goodsprice float ) as --添加商品 insert into T_GoodsInfo(TypeID,GoodsName,GoodsPrice) values(@typeid,@goodsname,@goodsprice) go