How Can I Display, Update Delete items in three tier architecture using single stored procedure? [on hold]

Question

0.00/5 (No votes)

See more:

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.NET

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

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; }
        }

    }

**Data layer**

C#

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

C#

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);

        }
}

Posted 25-Nov-13 18:46pm

Naveen K Tiwari

Updated 25-Nov-13 19:01pm

walterhevedeich

v2

Add a Solution

Comments

King Fisher 26-Nov-13 1:02am

do you wanna keep the same DAL,BAL format..

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

thatraja · Answer 1 · 2013-11-25T20:16:00

Solution 2

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.

Posted 25-Nov-13 20:16pm

thatraja

sankarsan parida · Answer 2 · 2013-11-25T19:01:00

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>

Rithik · Answer 3 · 2013-11-25T21:15:00

You Can Use Like This

SQL

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

King Fisher · Answer 4 · 2013-11-25T20:26:00

create procedure procedurename
@branchid bigint,
@user_id nvarchar(max),
@type bigint //it must be integer
as begin
if(@type=1)
select *from tbl_1
else if(@type=2)
select *from tbl_2
else if(@type=3)
select *from tbl_3
else if(@type=4)
select *from tbl_4
else if(@type=5)
end

just pass your type value from your .Cs form

How Can I Display, Update Delete items in three tier architecture using single stored procedure? [on hold]

4 solutions

Solution 2

Solution 1

Solution 4

Solution 3

Add your solution here

Preview 0