Getting an Insert Identity from a SQL Data Source
Prerequisites
This is part of the Category Codes Case Study. Follow these video’s in order at Case Study – Category Codes
Summary
Demonstrates using a SQLDataSource to insert data into a stored procedure and retrieve the identity of an insert in that stored procedure. Technique can be used to get any value as an output parameter of the stored procedure. The value is then passed to a second form that uses it.
Video
Video for this lecture has not been produced yet.
Code
| InsertCategory.aspx |
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="InsertCategory.aspx.cs" Inherits="TraumaFlow.CategoryManagement.InsertCategory" %>
<!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>Enter Category</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Enter Category</h1>
<br />
Every field in the form is a Category. A Category can have codes if the user is limited
to specific choices they can make, or it can have a user entered value. Choices are
displayed in drop down lists, where user enterd values are entered through a text box.
<br /><br />
<table><tr>
<td>Enter Category Name</td><td>
<asp:TextBox ID="tbCategory" runat="server"></asp:TextBox></td>
</tr></table>
<asp:Button ID="btnSubmit" runat="server" Text="Submit and Enter Another Category"
onclick="btnSubmit_Click" />
<asp:Button ID="btnSubmitCodes" runat="server"
Text="Submit and Enter Codes" onclick="btnSubmitCodes_Click" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>"
SelectCommand="sp_InsertCategory"
SelectCommandType="StoredProcedure" OnSelected="SqlDataSource1_OnSelected">
<SelectParameters>
<asp:ControlParameter ControlID="tbCategory" Name="DisplayText"
PropertyName="Text" Type="String" />
<asp:Parameter Direction="Output" Name="Identity" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<br />
</div>
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="id" DataSourceID="SqlDataSource2" CellPadding="4"
ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False"
ReadOnly="True" SortExpression="id" />
<asp:BoundField DataField="NamespaceID" HeaderText="NamespaceID"
SortExpression="NamespaceID" />
<asp:BoundField DataField="DisplayText" HeaderText="DisplayText"
SortExpression="DisplayText" />
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>"
SelectCommand="SELECT * FROM [Categories]"></asp:SqlDataSource>
</form>
</body>
</html>| InsertCategory.aspx.cs |
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace TraumaFlow.CategoryManagement
{
public partial class InsertCategory : System.Web.UI.Page
{
private string CategoryID;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
tbCategory.Text = String.Empty;
GridView1.DataBind();
}
protected void SqlDataSource1_OnSelected(object sender, SqlDataSourceStatusEventArgs e)
{
System.Data.Common.DbCommand command = e.Command;
Session.Add("CategoryID", command.Parameters["@Identity"].Value.ToString());
CategoryID = command.Parameters["@Identity"].Value.ToString();
}
protected void btnSubmitCodes_Click(object sender, EventArgs e)
{
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
Response.Redirect("InsertCode.aspx?CategoryID="+CategoryID);
}
}
}| InsertCode.aspx |
<%@ Page Language="C#" Title="Insert/Update Codes" AutoEventWireup="true" CodeBehind="InsertCode.aspx.cs" Inherits="TraumaFlow.CategoryManagement.InsertCode" %>
<!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>Insert Code</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1 style="text-align:center"></h1>
<table><tr>
<td>Select Category</td>
<td>
<asp:DropDownList ID="ddlSelectCategory" runat="server"
DataSourceID="SqlDataSource1" DataTextField="DisplayText"
DataValueField="id" AutoPostBack="True"
onselectedindexchanged="ddlSelectCategory_SelectedIndexChanged">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>"
SelectCommand="SELECT * FROM [Categories]"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td>Enter Code</td><td>
<asp:TextBox ID="tbCode" runat="server"></asp:TextBox></td>
</tr>
</table>
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click" />
<asp:Button ID="btnUpdate" runat="server" Text="Update"
onclick="btnUpdate_Click" Visible="False" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>"
SelectCommand="sp_InsertCode" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="ddlSelectCategory" Name="CategoryID"
PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="tbCode" Name="DisplayText" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource4" runat="server"
ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>"
SelectCommand="sp_UpdateCode" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:SessionParameter Name="CodeID" SessionField="CodeID" Type="Int32" />
<asp:ControlParameter ControlID="tbCode" Name="DisplayText" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<hr />
<asp:GridView ID="gvCodes" runat="server" AutoGenerateColumns="False"
CellPadding="4" DataKeyNames="CategoryID,CodeID" DataSourceID="SqlDataSource3"
EnableModelValidation="True" ForeColor="#333333" GridLines="None" OnRowCommand="gvCodes_RowCommand">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="CategoryID" HeaderText="CategoryID"
InsertVisible="False" ReadOnly="True" SortExpression="CategoryID" />
<asp:BoundField DataField="CodeID" HeaderText="CodeID" InsertVisible="False"
ReadOnly="True" SortExpression="CodeID" />
<asp:BoundField DataField="Category" HeaderText="Category"
SortExpression="Category" />
<asp:BoundField DataField="Code" HeaderText="Code" SortExpression="Code" />
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton runat="server" ID="lbEdit" Text="Edit This Code" CommandName="editinform"
CommandArgument="<%# ((GridViewRow) Container).RowIndex %>"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" SelectCommand="SELECT Categories.id AS 'CategoryID', Codes.id AS 'CodeID', Categories.DisplayText AS 'Category', Codes.DisplayText AS 'Code' FROM Categories INNER JOIN Codes ON Categories.id = Codes.CategoryID
WHERE Categories.id = @CategoryID">
<SelectParameters>
<asp:ControlParameter ControlID="ddlSelectCategory" Name="CategoryID"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>| InsertCode.aspx.cs |
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace TraumaFlow.CategoryManagement
{
public partial class InsertCode : System.Web.UI.Page
{
public int CodeID()
{
if (Session["CodeID"] != null)
{
return Convert.ToInt32(Session["CodeID"]);
}
else
{ return 0; }
}
public int CategoryID()
{
if (Request["CategoryID"] != null)
{
return Convert.ToInt32(Request["CategoryID"]);
}
else
{ return 0; }
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (CategoryID() != 0)
{
ddlSelectCategory.SelectedValue = Convert.ToString(CategoryID());
}
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlDataSource2.Select(DataSourceSelectArguments.Empty);
tbCode.Text = string.Empty;
gvCodes.DataBind();
}
protected void ddlSelectCategory_SelectedIndexChanged(object sender, EventArgs e)
{
tbCode.Text = string.Empty;
gvCodes.DataBind();
}
protected void gvCodes_RowCommand(object sender, GridViewCommandEventArgs e)
{
int index = Convert.ToInt32(e.CommandArgument);
if (e.CommandName == "editinform")
{
GridViewRow row = gvCodes.Rows[index];
tbCode.Text = row.Cells[3].Text;
Session.Add("CodeID", row.Cells[1].Text);
btnSubmit.Visible = false;
btnUpdate.Visible = true;
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
SqlDataSource4.Select(DataSourceSelectArguments.Empty);
tbCode.Text = string.Empty;
gvCodes.DataBind();
btnSubmit.Visible = true;
btnUpdate.Visible = false;
Session.Remove("CodeID");
}
}
}Stored Procedure sp_InsertCategory
ALTER PROCEDURE [dbo].[sp_InsertCategory] (
@DisplayText varchar(200),
@Identity INT OUTPUT
)
AS
BEGIN
DECLARE @N INT
SELECT @N = COUNT(*) FROM Categories
WHERE NamespaceID = 1
AND DisplayText = @DisplayText
IF @N = 0
BEGIN
INSERT INTO Categories
(NamespaceID, DisplayText)
VALUES
(1, @DisplayText)
END
SELECT @Identity = @@IDENTITY
END Reference Materials
The OnSelected event handler for a SqlDataSource – http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selected.aspx
All events associated with a SqlDataSource (used in this video) – http://msdn.microsoft.com/en-us/library/5b6ksy3z
It is also good to know and understand the DbCommand object (used in this video) – http://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.aspx