如何显示，更新使用单个存储过程删除三层体系结构中的项目？ [等候接听] [英] How Can I Display, Update Delete items in three tier architecture using single stored procedure? [on hold]
本文介绍了如何显示，更新使用单个存储过程删除三层体系结构中的项目？ [等候接听]的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用网格视图对于显示数据项我想使用单个存储过程在网格视图中显示，编辑，更新，删除和取消项目。
现在我正在执行所有这些任务单独的存储过程。
我将显示，更新和删除存储过程分开，但现在我想使用单个存储过程执行。
我有一个数据库管理器类。
**以下是我的代码请修改此代码我该怎么做。**
**。aspx代码**
< asp： GridView ID = grd_category 宽度 = 100％ runat = server AutoGenerateColumns = false
EmptyDataRowStyle-CssClass = grid_error EmptyDataText = 无可用数据 GridLines = 无 OnRowDeleting = grddata_RowDeleting OnRowEditing = grddata_RowEditing OnRowCommand = grddata_RowCommand
OnRowDataBound = grddata_RowDataBound DataKeyNames = category_id
onrowcancelingedit = grd_category_RowCancelingEdit
onrowupdating = grd_category_RowUpdating ShowHeaderWhenEmpty = True
< span class =code-attribute> AllowPaging = True onpageindexchanging = grd_category_PageIndexChanging
PageSize = 25 >
< 列 >
< span class =code-keyword>< asp：TemplateField HeaderText = 类别名称 HeaderStyle-CssClass = field_title_wraper
< span class =code-attribute> ItemStyle-CssClass = value_wraper ItemStyle-Width = 20％ >
< ItemTemplate >
<％ #Eval（ txt_category_name）％>
< / ItemTemplate >
< EditItemTemplate >
< div style = float：left; >
< asp： TextBox ID = txt_category_name runat = server 文字 =' <％ #Eval（ txt_category_name）％>' > < / asp：TextBox >
< / div >
< / EditItemTemplate >
< / asp：TemplateField >
<％ - < asp：TemplateField HeaderText =TitleHead erStyle-CssClass =field_title_wraperItemStyle-CssClass =value_wraperItemStyle-Width =40％>
< ItemTemplate>
<％＃Eval（txt_title）％>
< / ItemTemplate>
< EditItemTemplate>
< div style =float：left;>
< asp：TextBox ID =txt_titlerunat =serverText ='<％＃Eval（txt_title）％>'>< / asp：TextBox>
< / div>
< / EditItemTemplate>
< / asp：TemplateField> - ％>
<％ - < asp：TemplateField HeaderText =描述HeaderStyle-CssClass =field_title_wraper
ItemStyle-CssClass = value_wraperItemStyle-Width =25％>
< ItemTemplate>
<％＃Eval（txt_description）％>
< / ItemTemplate>
< EditItemTemplate>
< div style =float：left>
< asp：TextBox ID =txt_descriptionrunat =serverText ='<％＃Eval（txt_description）％>'>< / asp：TextBox>
< / div>
< / EditItemTemplate>
< / asp：TemplateField> - ％>
< asp：TemplateField HeaderText = 操作 HeaderStyle-CssClass = field_title_rightbdr_none_wraper < span class =code-attribute> >
ItemStyle-CssClass =value_wraperItemStyle-Width =5％>
< ItemTemplate >
< div class = city_icon_wraper >
< div class = action_img_view_icon >
< asp：ImageButton ID = imgbtnedit runat = 服务器 ImageUrl = images / edit_icon.png CommandName = 编辑 / >
< / div < span class =code-keyword>>
< div class = action_bdr >
< img width = 2 < span class =code-attribute> height = 34 src = images / action_bdr.jpg alt = > < / div >
< ; div class = action_icon >
< asp：LinkButton ID = lnkBtnDelete runat = server 工具提示 = 单击以删除 CommandName = 删除 < span class =code-attribute> class = 询问 CommandArgument =' <％＃DataBinder.Eval（Container.DataItem， category_id）％>' >
< img src = images / delete_icon.png alt = >
< / asp：LinkButton >
< / div >
< / div >
< / ItemTemplate < span class =code-keyword>>
< ItemStyle HorizontalAlign = 中心 / >
< EditItemTemplate >
< asp：ImageButton ID = imgbtnupdate runat = server ImageUrl = 〜/ Admin / images / approved_icon - Copy.png CommandName = update / >
< asp：ImageButton ID = imgbtncancel runat = server ImageUrl = 〜/ Admin / images / close_btn .png CommandName = 取消 / >
< / EditItemTemplate < span class =code-keyword>>
<％ - < ItemStyle HorizontalAlign =Center/>
< EditItemTemplate>
< asp：ImageButton ID =imgbtnupdaterunat =serverImageUrl =〜/ img / update.icoCommandName =updateHeight =30Width =30/>
< asp：ImageButton ID =imgbtncancelrunat =serverImageUrl =〜/ img / cancel.icoCommandName =cancelHeight =30Width =30/>
< / EditItemTemplate> - ％>
< / asp：TemplateField >
< /列 >
< / asp：GridView >
**物业层**
<前lang =c＃> public class PropertyCategory
{
private Int64 _category_id;
private string _txt_category_name;
public Int64 id
{
get { return _category_id; }
set {_category_id = value ; }
}
public string txt_category_name
{
get { return _txt_category_name; }
set {_txt_category_name = value ; }
}
}
**数据层**
public static int DataAddCategory（PropertyCategory objcat）
{
return DBManager.ExecuteNonQuery（ < span class =code-string> spAddCategory，objcat.txt_category_name）;
}
public static DataSet datafillcategory（）
{
return DBManager.ExecuteDataset（ spFillCategory）;
}
public static int DataDeleteCategory（PropertyCategory objcat）
{
return DBManager.ExecuteNonQuery（ spDeleteCategory，objcat.id）;
}
public static int DataUpdateCategory（PropertyCategory objcat）
{
// return DBManager .ExecuteNonQuery（spUpdateCategory，objcat.id，objcat.txt_category_name，objcat.txt_title，objcat.txt_description）;
return DBManager.ExecuteNonQuery （ spUpdateCategory，objcat.id，objcat.txt_category_name）;
}
**业务层**
public class BusinessCategory
{
public static int BusinessAddCategory（PropertyCategory objcat）
{
return DataCategoryTable.DataAddCategory（objcat）;
}
public static DataSet BusinessFillCategory（）
{
return DataCategoryTable.datafillcategory（）;
}
public static int BusinessDeleteCategory（PropertyCategory objcat）
{
return DataCategoryTable.DataDeleteCategory（objcat）;
}
public static int BusinessUpdateCategory（PropertyCategory objcat）
{
return DataCategoryTable.DataUpdateCategory（objcat）;
}
}
解决方案
检查此提示/技巧
将插入/更新合并到一个程序 [< a href =http://www.codeproject.com/Tips/176355/Combining-Insert-Update-to-one-Proceduretarget =_ blanktitle =New Window> ^ ]
但我不推荐这种方式。为每个操作创建单独的存储过程，这将提供更快的速度。
为此您必须采用@Action Varchar（30）参数，
写单个storeprocedure，你将使用动作类型insert，update，delete
声明所有类型的参数，可以为nullable类型
并将你的查询写成@Action类型
Ex。 create procedure usp_Category（
@Action varchar（30），
@id int = 0，
@txt_category_name varchar（50）
）
AS BEGIN
如果@Action ='插入'开始
插入tblCategory（CategoryName）值（@txt_category_name）
结束
如果@Action ='选择'开始
从tblCategory中选择category_i，txt_category_name
结束
如果@Action ='删除开始
...所以在
结束
如果@Action ='编辑'开始
....所以在
结束
结束
< pre>
你可以像这样使用
ALTER 程序 [dbo] 。[SpPolicyReqMaster]
@ Task int = 0 ，
@ ID int = 0 Out，
@ PolicyRequest varchar （max）= null ，
@IsActive 位</ span> = ' '
AS
BEGIN
IF （ @ Task = 1 ）
begin
SELECT * FROM PolicyRequest < span class =code-keyword> ORDER BY PolicyRequest.RequestPolicesID
end
IF （ @ Task = 2 ）
开始
INSERT INTO PolicyRequest（RequestPolicy，IsActive） VALUES （ @ PolicyRequest ， @ IsActive ）
end
IF （ @ Task = 3 ）
begin
UPDATE PolicyRequest SET RequestPolicy = @ PolicyRequest ，IsActive = @ IsActive WHERE PolicyRequest.RequestPolicesID = @ ID
end
IF （ @ Task = 4 ）
开始
DELETE PolicyRequest WHERE PolicyRequest.RequestPolicesID = @ ID
end
IF （ @Task = 5 ）
开始
SELECT * FROM PolicyRequest WHERE PolicyRequest.RequestPolicy = < span class =code-sdkkeyword> @ PolicyRequest
end
IF （ @ Task = 6 ）
begin
SELECT * FROM PolicyRequest WHERE PolicyRequest.RequestPolices ID = @ ID
end
END
I am using Grid view For Display Data items I want to show, edit, update, delete and cancel items in grid view using single stored procedure.
Right now I am performing all these tasks with separate stored procedure.
I am making Display, update and Delete stored procedure separate but now i want to perform using single stored procedure.
I have a db manager class.
**Below is my code please modify this code how can I do that.**
**.aspx code**
<asp:GridView ID="grd_category" Width="100%" runat="server" AutoGenerateColumns="false"
EmptyDataRowStyle-CssClass="grid_error" EmptyDataText="No data available" GridLines="None" OnRowDeleting="grddata_RowDeleting" OnRowEditing="grddata_RowEditing" OnRowCommand="grddata_RowCommand"
OnRowDataBound="grddata_RowDataBound" DataKeyNames="category_id"
onrowcancelingedit="grd_category_RowCancelingEdit"
onrowupdating="grd_category_RowUpdating" ShowHeaderWhenEmpty="True"
AllowPaging="True" onpageindexchanging="grd_category_PageIndexChanging"
PageSize="25">
<Columns>
<asp:TemplateField HeaderText="Category Name" HeaderStyle-CssClass="field_title_wraper"
ItemStyle-CssClass="value_wraper" ItemStyle-Width="20%">
<ItemTemplate>
<%#Eval("txt_category_name")%>
</ItemTemplate>
<EditItemTemplate>
<div style="float: left;">
<asp:TextBox ID="txt_category_name" runat="server" Text='<%#Eval("txt_category_name") %>'></asp:TextBox>
</div>
</EditItemTemplate>
</asp:TemplateField>
<%-- <asp:TemplateField HeaderText="Title" HeaderStyle-CssClass="field_title_wraper" ItemStyle-CssClass="value_wraper" ItemStyle-Width="40%">
<ItemTemplate>
<%#Eval("txt_title")%>
</ItemTemplate>
<EditItemTemplate>
<div style="float: left;">
<asp:TextBox ID="txt_title" runat="server" Text='<%#Eval("txt_title") %>'></asp:TextBox>
</div>
</EditItemTemplate>
</asp:TemplateField>--%>
<%-- <asp:TemplateField HeaderText="Description" HeaderStyle-CssClass="field_title_wraper"
ItemStyle-CssClass="value_wraper" ItemStyle-Width="25%">
<ItemTemplate>
<%#Eval("txt_description")%>
</ItemTemplate>
<EditItemTemplate>
<div style="float: left">
<asp:TextBox ID="txt_description" runat="server" Text='<%#Eval("txt_description") %>'></asp:TextBox>
</div>
</EditItemTemplate>
</asp:TemplateField>--%>
<asp:TemplateField HeaderText="Action" HeaderStyle-CssClass="field_title_rightbdr_none_wraper" >
ItemStyle-CssClass="value_wraper" ItemStyle-Width="5%">
<ItemTemplate>
<div class="city_icon_wraper">
<div class="action_img_view_icon">
<asp:ImageButton ID="imgbtnedit" runat="server" ImageUrl="images/edit_icon.png" CommandName="edit"/>
</div>
<div class="action_bdr">
<img width="2" height="34" src="images/action_bdr.jpg" alt=""></div>
<div class="action_icon">
<asp:LinkButton ID="lnkBtnDelete" runat="server" ToolTip="Click To Delete" CommandName="Delete" class="ask" CommandArgument='<%#DataBinder.Eval(Container.DataItem, "category_id")%>'>
<img src="images/delete_icon.png" alt="" >
</asp:LinkButton>
</div>
</div>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" />
<EditItemTemplate>
<asp:ImageButton ID="imgbtnupdate" runat="server" ImageUrl="~/Admin/images/approved_icon - Copy.png" CommandName="update" />
<asp:ImageButton ID="imgbtncancel" runat="server" ImageUrl="~/Admin/images/close_btn.png" CommandName="cancel" />
</EditItemTemplate>
<%-- <ItemStyle HorizontalAlign="Center" />
<EditItemTemplate>
<asp:ImageButton ID="imgbtnupdate" runat="server" ImageUrl="~/img/update.ico" CommandName="update" Height="30" Width="30" />
<asp:ImageButton ID="imgbtncancel" runat="server" ImageUrl="~/img/cancel.ico" CommandName="cancel" Height="30" Width="30" />
</EditItemTemplate>--%>
</asp:TemplateField>
</Columns>
</asp:GridView>
**Property Layer**
public class PropertyCategory
{
private Int64 _category_id;
private string _txt_category_name;
public Int64 id
{
get { return _category_id; }
set { _category_id = value; }
}
public string txt_category_name
{
get { return _txt_category_name; }
set { _txt_category_name = value; }
}
}
**Data layer**
public static int DataAddCategory(PropertyCategory objcat)
{
return DBManager.ExecuteNonQuery("spAddCategory", objcat.txt_category_name);
}
public static DataSet datafillcategory()
{
return DBManager.ExecuteDataset("spFillCategory");
}
public static int DataDeleteCategory(PropertyCategory objcat)
{
return DBManager.ExecuteNonQuery("spDeleteCategory", objcat.id);
}
public static int DataUpdateCategory(PropertyCategory objcat)
{
//return DBManager.ExecuteNonQuery("spUpdateCategory", objcat.id, objcat.txt_category_name, objcat.txt_title, objcat.txt_description);
return DBManager.ExecuteNonQuery("spUpdateCategory", objcat.id, objcat.txt_category_name);
}
**Business Layer**
public class BusinessCategory
{
public static int BusinessAddCategory(PropertyCategory objcat)
{
return DataCategoryTable.DataAddCategory(objcat);
}
public static DataSet BusinessFillCategory()
{
return DataCategoryTable.datafillcategory();
}
public static int BusinessDeleteCategory(PropertyCategory objcat)
{
return DataCategoryTable.DataDeleteCategory(objcat);
}
public static int BusinessUpdateCategory(PropertyCategory objcat)
{
return DataCategoryTable.DataUpdateCategory(objcat);
}
}
解决方案
Check this Tip/Trick
Combining Insert/Update to one Procedure[^]
But I won't recommend this way. Create seperate stored procedures for each operation which'll give some more speed.
For this You have to take a @Action Varchar(30) parameter,
Write a single storeprocedure where you will use action type insert,update, delete
declare all types of paramter with a nullable type
and write your query as @Action type
Ex. create procedure usp_Category( @Action varchar(30), @id int=0, @txt_category_name varchar(50) ) AS BEGIN if @Action ='Insert' begin Insert into tblCategory(CategoryName)values(@txt_category_name) end if @Action ='Select' begin Select category_i,txt_category_name from tblCategory end if @Action ='Delete begin ...so on end if @Action='Edit' begin .... so on end END <pre>
You Can Use Like This
ALTER PROCEDURE [dbo].[SpPolicyReqMaster] @Task int = 0, @ID int = 0 Out, @PolicyRequest varchar(max) = null, @IsActive bit = '' AS BEGIN IF(@Task = 1) begin SELECT * FROM PolicyRequest ORDER BY PolicyRequest.RequestPolicesID end IF(@Task = 2) begin INSERT INTO PolicyRequest(RequestPolicy,IsActive) VALUES (@PolicyRequest,@IsActive) end IF(@Task = 3) begin UPDATE PolicyRequest SET RequestPolicy = @PolicyRequest,IsActive = @IsActive WHERE PolicyRequest.RequestPolicesID = @ID end IF(@Task = 4) begin DELETE PolicyRequest WHERE PolicyRequest.RequestPolicesID = @ID end IF(@Task = 5) begin SELECT * FROM PolicyRequest WHERE PolicyRequest.RequestPolicy = @PolicyRequest end IF(@Task = 6) begin SELECT * FROM PolicyRequest WHERE PolicyRequest.RequestPolicesID = @ID end END
这篇关于如何显示，更新使用单个存储过程删除三层体系结构中的项目？ [等候接听]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
