Using Parameters with the SQL Datasource Control

In the previous article we saw how we could use the DataSource control in two ways, declaratively, where the control is set up explicitly in a web page as an <asp: /> control at design time, and programatically, where the DataSource control is set up and manipulated in ASP.NET code at run time.

We used the simple SELECT statement in the SQLDataSource as an example of what could be done.

We’re now going to continue by looking at using parameters programatically and declaratively, and then look at other database operations, starting with INSERT. We'll be working from the database structure and set up from the previous article.

Declarative Parameters

Let’s start with a piece of code that uses a Select parameter declaratively. In the following example, type text in to the text box and press return; the records with a LastName field beginning with those letters will be displayed.

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>

<br />
<asp:TextBox ID="TextBox1" runat="server"
Width="147px"></asp:TextBox>
<br />
<asp:SqlDataSource ID="dsNames" runat="server"
ConnectionString="<%$ ConnectionStrings:Demo-SQL %>"
SelectCommand="SELECT [ID], [FirstName], [LastName] FROM [names] WHERE LastName Like @Lname+'%'" >
<selectparameters>
<asp:controlparameter name="Lname" controlid="TextBox1" propertyname="Text"/>
</selectparameters>
</asp:SqlDataSource>

<asp:GridView id="grdNames" RunAt="Server" DataSourceID="dsNames" AutoGenerateColumns="False">
<columns>
<asp:BoundField HeaderText="ID" DataField="ID" />
<asp:BoundField HeaderText="First Name" DataField="FirstName" />
<asp:BoundField HeaderText="Last Name" DataField="LastName" />
</columns>
</asp:GridView>
</div>
</form>
</body>
</html>

Adding Parameters Programmatically

In the following example, we repeat what we did above but by adding the parameter through code. The only difference is how we configure the initial SELECT statement to NOT include a parameter so that when the page loads up for the first time all records are shown.

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> Dim dsNames As SqlDataSource Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init dsNames = New SqlDataSource ' This routine runs when the Page is initialised and has access to the asp: namespace controls ' on the page. ' First of all get the connection string from the web.config file and assign it to the SQLDataSource Dim sConnString As String sConnString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("Demo-SQL").ConnectionString dsNames.ConnectionString = sConnString dsNames.ID = "dsNames" ' Now configure the SELECT statement. dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] " ' Now add the created SQLDataSource to the page Me.Controls.Add(dsNames) End Sub ' Following fires off when you press ENTER on the TextBox Protected Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) ' Now re-write the SELECT statement to include a WHERE clause dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] WHERE LastName Like @Lname+'%'" ' Clear the existing parameter list - this is vital as otherwise parameters simply ' build up dsNames.SelectParameters.Clear() ' Now add the parameter to the SelectParameters collection and off we go dsNames.SelectParameters.Add("Lname", TextBox1.Text.ToString) End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <br /> <asp:TextBox ID="TextBox1" runat="server" Width="147px" ontextchanged="TextBox1_TextChanged"></asp:TextBox> <asp:GridView id="grdNames" RunAt="Server" DataSourceID="dsNames" AutoGenerateColumns="False"> <columns> <asp:BoundField HeaderText="ID" DataField="ID" /> <asp:BoundField HeaderText="First Name" DataField="FirstName" /> <asp:BoundField HeaderText="Last Name" DataField="LastName" /> </columns> </asp:GridView> </div> </form> </body> </html>

INSERT and UPDATE

Let’s now take a look at adding INSERT and UPDATE capabilities to the SQLDataSource. It’s important to bear in mind that the DataSource control only looks after the data side of things; the User Interface (UI) side of things is handled by the other controls on the page.

In this next example, the GridView is configured to handle the UPDATE UI, and the INSERT UI is provided by text boxes and a button. It would have been possible to do both via the GridView but in this example it shows how code outside the GridView can be used to access the SQLDataSource.

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Dim dsNames As SqlDataSource

Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

dsNames = New SqlDataSource

' This routine runs when the Page is initialised and has access to the asp: namespace controls
' on the page.

' First of all get the connection string from the web.config file and assign it to the SQLDataSource
Dim sConnString As String
sConnString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("Demo-SQL").ConnectionString
dsNames.ConnectionString = sConnString
dsNames.ID = "dsNames"

' Now configure the SELECT statement.
dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] "

' Now configure the UPDATE statement. We have to declare the Parameters here though at this stage they are empty
dsNames.UpdateCommand = "UPDATE [names] SET [FirstName]=@Fname, [LastName]=@Lname WHERE [ID]=@ID"
dsNames.UpdateParameters.Add("@Fname", "")
dsNames.UpdateParameters.Add("@Lname", "")
dsNames.UpdateParameters.Add("@ID", "")

' Now configure the INSERT statement. We have to declare the Parameters here
dsNames.InsertCommand = "INSERT INTO [names] ([FirstName],[LastName]) VALUES(@Fname, @Lname)"
dsNames.InsertParameters.Add("@Fname", "")
dsNames.InsertParameters.Add("@Lname", "")


' Now add the created SQLDataSource to the page
Me.Controls.Add(dsNames)

End Sub

' Following fires off when you press ENTER on the TextBox
Protected Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs)
' Now re-write the SELECT statement to include a WHERE clause
dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] WHERE LastName Like @Lname+'%'"
' Clear the existing parameter list - this is vital as otherwise parameters simply
' build up 
dsNames.SelectParameters.Clear()
' Now add the parameter to the SelectParameters collection and off we go
dsNames.SelectParameters.Add("@Lname", TextBox1.Text.ToString)
End Sub

' This routine is fired off by the OnUpdate event of the gridview - when you click the
' Update link after Editing a row.
Protected Sub grdNames_RowUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs)
'clear update parameters
dsNames.UpdateParameters.Clear()

' We set the parameters to values passed in from the Gridview in the EventArgs object
' The values we want to set are in the NewValues collection
dsNames.UpdateParameters.Add("@Fname", e.NewValues("FirstName"))
dsNames.UpdateParameters.Add("@Lname", e.NewValues("LastName"))
dsNames.UpdateParameters.Add("@ID", e.NewValues("ID"))

' Now we've set the parameters, do the actual update command to write the
' data to the database.
dsNames.Update()

End Sub
' The following code is triggered when the Insert button is pressed.
Protected Sub bInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' Clear insert Parameters
dsNames.InsertParameters.Clear()
' Add parameters with values form Text boxes
dsNames.InsertParameters.Add("@Fname", tFirstName.Text.ToString)
dsNames.InsertParameters.Add("@Lname", tLastName.Text.ToString)
' Do the INSERT command
dsNames.Insert()
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"
Width="147px" ontextchanged="TextBox1_TextChanged"></asp:TextBox>
 
<br />
 
<asp:GridView id="grdNames" RunAt="Server" DataSourceID="dsNames" AutoGenerateColumns="False"
AutoGenerateEditButton="true" DataKeyNames="ID" OnRowUpdated="grdNames_RowUpdated">
<columns>
<asp:BoundField HeaderText="ID" DataField="ID" />
<asp:BoundField HeaderText="First Name" DataField="FirstName" />
<asp:BoundField HeaderText="Last Name" DataField="LastName" />
</columns>
</asp:GridView>
<br />
<p>
<asp:Label ID="Label1" runat="server" Text="New First Name : "></asp:Label>
<asp:TextBox ID="tFirstName" runat="server"></asp:TextBox>
</p>
<p>
<asp:Label ID="Label2" runat="server" Text="New Last name : "></asp:Label>
<asp:TextBox ID="tLastName" runat="server"></asp:TextBox>
</p>
<p>
<asp:Button ID="bInsert" runat="server" onclick="bInsert_Click"
Text="Insert Data" />
</p>
 
</div>
</form>
</body>
</html>

Addition of DELETE is done in a way that is similar to the UPDATE – configure the GridView to show a delete button, a DeleteCommand in the SQLDataSource, and an Event Handler for the link in the GridView that populates the DeleteParameters collection of the SQLDataSource then calls the SQLDataSource’s Delete Method. Take a look at the grdNames_RowUpdated Event Handler above for how to get the ID field back.

Why parameters?

You might think that within the Event Handlers we could dispense with the parameters and simply re-define the InsertCommand or UpdateCommand property of the SQLDataSource with a SQL command string put together by just concatenating string values from the different controls. Indeed you could, but using parameters makes for ‘cleaner’ code and is also more secure.

Get the best ASP hosting with DiscountASP.NET - great value, money back guarantee.

Advertisements



MembersPro

MembersPro PayPal - ASP Membership software

Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.

Global ASP.NET Hosting Leader - Click Here