|
|
|
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 :
7537 |
|
Adding to your Favorites....
|
|
|
 |
|
|
|
|
|
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> |
|
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.
|
|
|
|
|
|
|
|
|
|
|
|