AspdotnetCodes.com
Search
Articles
Books
Resources
Asp.Net News
Downloads
Free Tech Magazines
Asp.Net Web Hosting
Archives
Softwares
Newsletter
Suggest Us
Link to Us
Feeds Subscription
Articles
Questions & Answers
Tips & Tricks
 Simple Edit, Update and Delete of multiple tables from SQLDataSource using Stored Procedure
Posted by Moderator1 on 8/29/2009 1:57:33 AM Category: ADO.NET
Total Views : 30366
Add to my favorites
Email to friend
  
Introduction
We, the asp.net developers, knew the importance of using data binding controls, sometimes it’s a very challenging task, to use it to update or delete or manipulate data from two or more tables which is joined together with or without foreign key references. So this article takes the pride of explaining the concept by using SQLDataSource and a Stored Procedure that edits, updates and deletes data in two tables at a time. Besides that, this article speaks little bit about the Stored Procedure with optional parameters, and binding a DropDownList control that contains some static items with the value from the database.
Sample Scenario 

For demonstration purpose of this article, we display some customer information along with their choice of interest. The customer information is fetched from the Customer table and their interest is kept stored in another table known as CustomerInterest. The Customer and CustomerInterest tables are joined, updated and deleted together using single Stored Procedure called “Proc_UpdateTwoTable” that is called from the SQLDataSource control. From here we go directly to the working arena.


Create the Stored Procedure

In your MS SQL Server database, create the Stored Procedure “Proc_UpdateTwoTable”. The complete code is given below
 
CREATE Proc [Proc_UpdateTwoTable](@ExecFor Int, @Cus_Code Int, @Cus_Email VarChar(50)=null, @Cus_Interest VarChar(50)=null)
As
Begin

IF (@ExecFor=1)
  Select A.*, B.Cus_Interest from Customers A
  Inner Join
  CustomerInterest B No A.Cus_Code=B.Cus_Code

IF (@ExecFor=2)
Begin
  Update Customers Set Cus_Email=@Cus_Email
  Where Cus_Code=@Cus_Code
  Update CustomerInterest Set
  Cus_Interest=@Cus_Interest
  Where Cus_Code=@Cus_Code
End

IF (@ExecFor=3)
Begin
  Delete Customers Where Cus_Code=@Cus_Code
  Delete CustomerInterest
  Where Cus_Code=@Cus_Code
End

End
The above Stored Procedure takes 4 parameters such as @ExecFor, @Cus_Code, @Cus_Email and @Cus_Interest. A parameter to a Stored Procedure can be optional, which means, while calling the Stored Procedure, it is not mandatory to pass the value for that parameter. An optional parameter is created by assigning a null value to it in the parameter declaration section of the Stored Procedure. The optional parameters in the above Stored Procedure are highlighted below,


@Cus_Email VarChar(50)=null, @Cus_Interest VarChar(50)=null


The @ExecFor parameter is like the Execution ID, instructs the Stored Procedure about which section of command to execute. So when we pass the @ExecFor as 1, then Select statement will work and this will return data to fill the GridView control. When @ExecFor is 2, the Update statements will work, which will update the customer email id in Customers table and customers interest in the CustomerInterest table. Finally, when @ExecFor is 3, the delete statements will work. Now let us see how we are going to use the Stored Procedure in the SQLDataSource control to manipulate multiple tables. In your aspx page, drag and drop a GridView control and an SQLDataSource control, and the name will be GridView1 and SqlDataSource1 respectively.


Setup GridView control
Set the DataSourceId of the GridView control as “SqlDataSource1”, set its AutoGenerateColumns property as “false” and set its DataKeyNames as “Cus_Code”. The customer code [Cus_Code] is the primary unique key column in the Customer Table. We are going to manipulate the data based on this field. Next create some databound columns in the GridView control to display data, for this article purpose; we have created data bound columns such as Customer Code [Cus_Code], Customer Name [Cus_Name], Customer Age [Cus_Age], Customer Email [Cus_Email] and Customer Interest [Cus_Interest]. The last Customer Interest column is a TemplateField column, so place a Label control in the ItemTemplate section and place a DropDownList control in the EditItemTemplate section. Set the Text property of the Label control to display the Customer Interest value, sometimes, the value in table might contain a null or empty value, so if null value exists, we have to replace that with “Nothing” like below,
<asp:Label ID="Label1" runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"Cus_Interest") == DBNull.Value ? "Nothing" : DataBinder.Eval(Container.DataItem,"Cus_Interest") %>'></asp:Label>

Next add some values in the DropDownList control in the EditItemTemplate section and bind its SelectedValue property as “Cus_Interest” as follows,
<asp:DropDownList ID="ddlCusInterest" runat="server" SelectedValue='<%# Bind("Cus_Interest") %>'>
  <asp:ListItem Value="">Nothing</asp:ListItem> 
  <asp:ListItem Value="Reading ">Reading</asp:ListItem> 
  <asp:ListItem Value="Music”>Music</asp:ListItem> 
  <asp:ListItem Value="Games”>Games</asp:ListItem> 
  <asp:ListItem Value="Surfing”>Surfing</asp:ListItem>
</asp:DropDownList>

Then add two CommandField columns for Edit and Delete purpose. The complete source code of the GridView control is given below,

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" AutoGenerateColumns="False" DataKeyNames="Cus_Code">
<Columns>
  <asp:BoundField DataField="Cus_Code" HeaderText="Code" ReadOnly="True" /> 
  <asp:BoundField DataField="Cus_Name" HeaderText="Name" ReadOnly="True" /> 
  <asp:BoundField DataField="Cus_Age" HeaderText="Age" ReadOnly="True" /> 
  <asp:BoundField DataField="Cus_Email" HeaderText="Email" /> 
  <asp:TemplateField HeaderText="Interest" ConvertEmptyStringToNull="False"> 
  <ItemTemplate> 
    <asp:Label ID="Label1" runat="server" 
    Text='<%# DataBinder.Eval(Container.DataItem,"Cus_Interest") == DBNull.Value ? "Nothing" : 
    DataBinder.Eval(Container.DataItem,"Cus_Interest")
%>'></asp:Label> 
  </ItemTemplate> 
  <EditItemTemplate> 
  <asp:DropDownList ID="ddlCusInterest" runat="server" SelectedValue='<%# Bind("Cus_Interest") %>'> 
  <asp:ListItem Value="">Nothing</asp:ListItem> 
  <asp:ListItem Value="Reading ">Reading</asp:ListItem> 
  <asp:ListItem Value="Music”>Music</asp:ListItem> 
  <asp:ListItem Value="Games”>Games</asp:ListItem> 
  <asp:ListItem Value="Surfing”>Surfing</asp:ListItem> 
  </asp:DropDownList> 
  <asp:CommandField ShowEditButton="True" /> 
  <asp:CommandField ShowDeleteButton="True" />
</Columns>
</asp:GridView>

Setup SQLDataSource control

In the SQLDataSource control, set the ConnectionString property to point your database. The best way is to place your connection string in the web.config file and refer it like below.
In web.config file
<connectionStrings> <add name="ConnectionString" connectionString="Initial Catalog=YourDatabase;Data Source=ServerName;Uid=YourUserId;Pwd=YourPassword;"/> </connectionStrings>
In SQLDataSource control,
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
Next in the SQLDataSource control, we are going to set up the Select, Update and Delete commands as the Stored Procedure name and its command types as “StoredProcedure” as below,

SelectCommand="Proc_UpdateTwoTable" SelectCommandType="StoredProcedure"
UpdateCommand="Proc_UpdateTwoTable" UpdateCommandType="StoredProcedure"
DeleteCommand="Proc_UpdateTwoTable" DeleteCommandType="StoredProcedure"


Finally, we are going to set the parameters for each command and set the ExecFor parameter with default value as follows

Select Command Parameter

<SelectParameters>
  <asp:Parameter Name="ExecFor" DefaultValue="1" />
  <asp:Parameter DefaultValue="0" Name="Cus_Code" />
</SelectParameters>


Update Command Parameter

<UpdateParameters>
  <asp:Parameter Name="ExecFor" DefaultValue="2" />
  <asp:Parameter Name="Cus_Code" />
  <asp:Parameter Name="Cus_Email" />
  <asp:Parameter Name="Cus_Interest" />
</UpdateParameters>

Delete Command Parameter

<DeleteParameters>
  <asp:Parameter Name="ExecFor" DefaultValue="3" />
  <asp:Parameter DefaultValue="0" Name="Cus_Code" />
</DeleteParameters>


The complete html source of the SQLDataSource control is given below,

<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="Proc_UpdateTwoTable"
SelectCommandType="StoredProcedure"
UpdateCommand="Proc_UpdateTwoTable"
UpdateCommandType="StoredProcedure"
DeleteCommand="Proc_UpdateTwoTable"
DeleteCommandType="StoredProcedure" >

<SelectParameters>
  <asp:Parameter Name="ExecFor" DefaultValue="1" />
  <asp:Parameter DefaultValue="0" Name="Cus_Code" />
</SelectParameters>

<UpdateParameters>
  <asp:Parameter Name="ExecFor" DefaultValue="2" />
  <asp:Parameter Name="Cus_Code" />
  <asp:Parameter Name="Cus_Email" />
  <asp:Parameter Name="Cus_Interest" />
</UpdateParameters>

<DeleteParameters>
  <asp:Parameter Name="ExecFor" DefaultValue="3" />
  <asp:Parameter DefaultValue="0" Name="Cus_Code" />
</DeleteParameters>

</asp:SqlDataSource>
Featured Resources:
No Nonsense XML Web Development With PHP - Free 146 Page Preview!
Learn how to put XML to practical use on your Website.
The JavaScript Anthology: 101 Essential Tips, Tricks & Hacks - Free 158 Page Preview
Get the most out of this complete question-and-answer book on JavaScript.
100% Free Subscription until Stock lost.View complete list.


Points to Remember:

1. While specifying the Name of the Parameter use the same name as specified in the Stored Procedure.
2. If a parameter in Stored Procedure is optional, then you must not include in the commands unnecessarily.
3. Make sure the number of parameters in Stored Procedure and SQLDataSource controls is same.

That’s it. Save all and browse this page. By default the SQLDataSource control will execute the SelectCommand to fill the GridView control. So you can edit and delete any rows and see it is affecting both the tables.
You need to Login or Register to download Source Code.
 
Viewer's Comments
Posted by Samoys on 10/3/2009 9:56:26 AM
lot about you
 
Posted by Roberto on 10/4/2009 4:36:43 AM
cool blog
 
Posted by Md. Shohel Rana on 12/30/2009 12:49:05 AM
That's good code for Freasher Programmer, Thank You very much.
 
Posted by srilakshmi on 3/14/2010 1:32:52 PM
Very helpful example. Thank you so much
 
Posted by Srilakshmi on 3/17/2010 4:28:09 AM
helpful blog
 
Posted by ishita on 4/7/2010 4:03:44 PM
helpful and useful article
 
Posted by HMHKH on 2/23/2011 1:59:27 AM
Wonderful post Thanks very much
 
Posted by Vo~ Tro?ng Ðai on 4/4/2011 10:44:32 PM
Thanks a lot ! it's very Useful
 
Posted by djokotingkir009 on 5/22/2011 11:43:26 PM
download sourcecode = error have another links?
 
Posted by osc mosc on 11/8/2011 10:17:44 AM
1 - thanks for the articles 2 - the link to download the code (http://www.aspdotnetcodes.com/Download/GridViewSPSQLDataSource.zip) does not work please email me the code or let me know when the link is fixed. 3- a nice small user management and an order entry application would be appriciated. SMALL ONE to get ideas. Thanks again
 
Posted by s on 2/10/2012 11:00:19 AM
thanks man, this is what i was looking for whole week :))) very nice :)
 
Posted by Mohan Das Sonber on 2/2/2013 1:27:04 PM
Usful Blog and Thanks a Lot..............:)
 
 Rating & Comments
A word 'Excellent' means lot to the author of this article. You can give comments about this article but not the author.
Rate this Article:
Name:
Email Id:  
We never display your email id anywhere.
Comment/Question: Max. 500 letters
Fifteen plus fifty Equals To
 
Sponsored by
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
No Nonsense XML Web Development With PHP - Free 146 Page Preview!
Washington Technology