Skip Navigation LinksASP.Net 2.0 Training : ASP.Net:SQLDataSource Control
Banner for ASP.Net 2.0 training classes in Visual Basic 2005 

Skip Navigation Links
Visit my other site:

certification training

Free Flash tutorials and training

Free Dreamweaver Video Tutorials

Free PHP Training

Visual Basic Windows Training




If you like this site please link to it


SQLDataSource Control


The SQL Server Data Source Control gives you the ability to quickly and easily use a SQL Server database in a website.

The control is build for working with SQL Server 7 or newer. You can use it for the following databases:
Microsoft SQL Server
Microsoft SQL Server Express
Microsoft Access
Oracle
DB2
MySQL
Most other databases

If you want to use a database other than SQL Server you have to reconfigure the control

It is a non-visual control. You use it with other controls.

Important parts of the SQLDataSource Control

  • Id                    
      • name of the control
  • SelectCommand 
      • Select statement to use to connect to the database
  • ConnectionString
      • Source of the database

  • Drag a SQLDataSource control to a webform

  • Click the SmartTag

  • Select Configure Data Source
  • Click New Connection


If you want another dataProvider click on change

If you are using SQL Server express select Microsoft SQL Server Database File


You can connect to other databases besides SQL Server
        You can select Oracle, Access or ODBC for other databases.

  • Click Browse and select the SQL Server Express file
    • It will be located in the App_Data directory

  • Click Test Connection to check it
  • Click OK
  • Click Next


Click Next again to save the connection in your web.config file

Storing connection strings in the web.config is more secure than storing them on each of your web pages. By storing your data in the web.config you can easily change it for all pages at once and allow connection pooling for better performance.

  • Select Specify columns from a table or view
  • Select your table
  • Click * to select all columns
  • Click the Advanced button

  • Click the checkbox to Generate INSERT, UPDATE and DELETE statements

  • Click OK
  • Click Next
  • Click Finish


Here is the code it generated:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:myDatabaseConnectionString %>"
DeleteCommand="DELETE FROM [Contacts] WHERE [Contact_id] = @Contact_id"
InsertCommand="INSERT INTO [Contacts] ([Username], [email]) VALUES (@Username, @email)"
SelectCommand="SELECT * FROM [Contacts]"
UpdateCommand="UPDATE [Contacts] SET [Username] = @Username, [email] = @email WHERE [Contact_id] = @Contact_id">
            <DeleteParameters>
                <asp:Parameter Name="Contact_id" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="Username" Type="String" />
                <asp:Parameter Name="email" Type="String" />
                <asp:Parameter Name="Contact_id" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="Username" Type="String" />
                <asp:Parameter Name="email" Type="String" />
            </InsertParameters>
</asp:SqlDataSource>

Here is the code in the web.config:
    <connectionStrings>
        <add name="myDatabaseConnectionString"
             connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=E:\Storage\XASPWebSites\Week2\App_Data\myDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
You can modify it so the attachDbFilename is relative instead of  an absolute one

I changed
AttachDbFilename=E:\Storage\XASPWebSites\Week2\App_Data\myDatabase.mdf
To
AttachDbFilename=|DataDirectory|\myDatabase.mdf

This will automatically look in the App_Data directory

ConnectionString="<%$ ConnectionStrings:myDatabaseConnectionString %>"
Is the expression in the sqldatasource control that represents the string in the web.config

  • Add a gridView by dragging it to the webform

  • Select your SQLDataSource control from the dropdown list under Choose Data Source
  • Right click on the webform and select View in Browser



Database Commands

The SQLDataSource control can represent four types of SQL commands

  1. SelectCommand
  2. InsertCommand
  3. UpdateCommand
  4. DeleteCommand

 

Here are the commands automatically created:
DeleteCommand="DELETE FROM [Contacts] WHERE [Contact_id] = @Contact_id"
InsertCommand="INSERT INTO [Contacts] ([Username], [email]) VALUES (@Username, @email)"
SelectCommand="SELECT * FROM [Contacts]"
UpdateCommand="UPDATE [Contacts] SET [Username] = @Username, [email] = @email WHERE [Contact_id] = @Contact_id">

You can modify these if you like


Parameters

SQLDataSource supports the following parameter objects


ControlParameter

Represents the value of a control or the value of a page property

CookieParameter

Value of a browser cookie

FormParameter

Value of an HTML form field

ProfileParameter

Value of a profile property

QuerystringParameter

Value of a querystring field

SessionParameter

Value of an item stored in a session

SQLDataSource control can have five collections of parameters

  • SelectParameters
  • InsertParameters
  • DeleteParameters
  • UpdateParameters
  • Filterparameters

Look at the code from our example:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:myDatabaseConnectionString %>"
DeleteCommand="DELETE FROM [Contacts] WHERE [Contact_id] = @Contact_id"
InsertCommand="INSERT INTO [Contacts] ([Username], [email]) VALUES (@Username, @email)"
SelectCommand="SELECT * FROM [Contacts]"
UpdateCommand="UPDATE [Contacts] SET [Username] = @Username, [email] = @email WHERE [Contact_id] = @Contact_id">
            <DeleteParameters>
                <asp:Parameter Name="Contact_id" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="Username" Type="String" />
                <asp:Parameter Name="email" Type="String" />
                <asp:Parameter Name="Contact_id" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="Username" Type="String" />
                <asp:Parameter Name="email" Type="String" />
            </InsertParameters>
</asp:SqlDataSource>

 

After the SQL commands there are DeleteParameters, UpdateParameters and InsertParameters tags

These allow the user to enter dynamic data into the SQL statements
Look at the Delete statement:
DeleteCommand="DELETE FROM [Contacts] WHERE [Contact_id] = @Contact_id"

@Contact_id is a parameter

<DeleteParameters>
   <asp:Parameter Name="Contact_id" Type="Int32" />
</DeleteParameters>

Creates a parameter of type Int32 named Contact_id that is used in the delete statement

Update Statement:
UpdateCommand="UPDATE [Contacts] SET [Username] = @Username, [email] = @email WHERE [Contact_id] = @Contact_id">

Update Parameter:
<UpdateParameters>
   <asp:Parameter Name="Username" Type="String" />
   <asp:Parameter Name="email" Type="String" />
   <asp:Parameter Name="Contact_id" Type="Int32" />
</UpdateParameters>

Notice a parameter for each one in the SQL statement

Insert statement:
InsertCommand="INSERT INTO [Contacts] ([Username], [email]) VALUES (@Username, @email)"

Insert Parameter:
<InsertParameters>
   <asp:Parameter Name="Username" Type="String" />
   <asp:Parameter Name="email" Type="String" />
</InsertParameters>

Notice that Contact_id is not listed as a parameter. That is because it is set to auto increment. It would give you an error if you tried to add to this column.


ControlParameter

The controlParameter object allows you to retrieve a value from another control. The other control has to be on the same page as the SQLDataSource control.
It includes two additional properties:
ControlID           -       control to retrieve data from
PropertyName     -       name of the property in the control to
retrieve from


Using the ControlParameter

  • Add another webform
  • Drag a SQLDataSource control to the page and set it to your database

You can select the connectionstring you created earlier to quickly connect to your database. Select all columns and generate insert,update and delete.

  • Add a dropDownList control and set autoPostback to true
  • Click the smarttag and confige the data source

  • Select your SQLDataSource control for the data source
  • Select Username column to display and Contact_id as the value
  • Add another SQLDataSource control

 

  • Configure your data source by selecting the connection string and Select all columns.
  • This time click on the Where button

  • Select Contact_id as the column
  • Select Control as the source
  • Select your dropDownList as your controlID
  • Then click on Add

  • Click Next
  • Click Finish
  • Add a detailsView control and set the data source  to the second SQLDataSource control

  • Run it, now when you change the dropdownlist your detailsView data will change

Here is the code:
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:myDatabaseConnectionString %>"
SelectCommand="SELECT * FROM [Contacts] WHERE ([Contact_id] = @Contact_id)" >
<SelectParameters>
   <asp:ControlParameter ControlID="DropDownList1" Name="Contact_id"   
      PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>


QueryStringParameter Object

Querystrings are often used when you want to create Master/Detail pages. A hyperlink on a Master page is used to take the user to a more detailed page.

Using the QuerystringParameter Object to create Master/Detail pages

  • Add a new webform
  • Add a SQLDataSource control and configure it to your database.
  • Add a gridView and configure it to your SqlDataSource control
  • On the gridView click on Edit Columns

  • Select the email column and click the delete button
  • Do the same for the contact_id column and the username column
  • Click on the hyperlink field and click add

  • Click on the dataTextField and select username
  • Click on dataNavigateURLField and select contact_id
  • Add this line to the dataNavigateURLFormatString:

detailsPage.aspx?id={0}

  • test it

Now if you clicked on one of the hyperlinks it would send you to details.aspx and also send a querystring

Here is the code created for the gridView:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Contact_id" DataSourceID="SqlDataSource1">
<Columns>
   <asp:HyperLinkField DataNavigateUrlFields="contact_id"  
   DataNavigateUrlFormatString="detailsPage.aspx?id={0}"
   DataTextField="Username" HeaderText="Select" />
</Columns>
</asp:GridView>

Next we must create the details.aspx page

  • Add another webform and name it detailsPage.aspx
  • Add a SQLDataSource control and configure it to your connectionstring
  • Add all the columns
  • Click on the where button

  • Select contact_id as the column
  • Select Querystring as the Source
  • The querystring field is contact_id
  • Click on Add


  • Click OK
  • Click Next
  • Click Finish
  • Add a detailsView control and configure it to your SQLDataSource
  • Save it and test your previous page with the hyperlinks again

This time click one of the hyperlinks


Programming SQL Commands

These events fire AFTER a SQLDataSource command

  • Deleted     -       fires after its delete command
  • Inserted     -       fires after its insert command
  • Selected    -       fires after its select command
  • Updated     -       fires after its update command

You can add code to run when these events fire
These events fire BEFORE a SQLDataSource command

  • Deleting     -       fires before the delete command is executed
  • Inserting    -       fires before the insert command is executed
  • Selecting    -       fires before the select command is executed
  • Updating    -       fires before the update command is executed
  • Filtering     -       fires before the SQLDataSource filters its data

You can add your own parameters in code before inserting, updating or deleting


Creating Parameters in Code

  • Open the server Explorer
  • Right click on the Contacts table
  • Select Open Table Definition

  • Add the following column information

  • Add another webform
  • Add a SQLDataSource control to the webform and configure it to your database
  • Select all column and click advanced and generate your SQL statements
  • Add a detailsView control and configure it to your SQLDataSource control
  • Check the enable Inserting checkbox in the smarttag
  • Change the DefaultMode property to Insert


Here is the current parameters created for you:
<InsertParameters>
   <asp:Parameter Name="Username" Type="String" />
   <asp:Parameter Name="email" Type="String" />
   <asp:Parameter Name="dateEntered" Type="DateTime" />
</InsertParameters>

Look at the code for the detailsView:
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="Contact_id"
DataSourceID="SqlDataSource1" DefaultMode="Insert" Height="50px" Width="125px" OnItemInserting="DetailsView1_ItemInserting">
<Fields>
<asp:BoundField DataField="Contact_id" HeaderText="Contact_id" InsertVisible="False"
ReadOnly="True" SortExpression="Contact_id" />
<asp:BoundField DataField="Username" HeaderText="Username" SortExpression="Username" />
<asp:BoundField DataField="email" HeaderText="email" SortExpression="email" />
<asp:BoundField DataField="dateEntered" HeaderText="dateEntered" SortExpression="dateEntered" />
<asp:CommandField ShowInsertButton="True" />
</Fields>
</asp:DetailsView>

  • Delete the asp:BoundField named dateEntered.

We are going to programmatically add the value to this parameter. This will remove it from the visual end of the control.

We want to add this code before the data is entered so we are going to use the Inserting event

  • In the properties window click on the events button


We want ItemInserting

  • Double click on the box beside it

It adds a code block for you:

Here we can add the code to give the dateEntered parameter a value of the current date and time:
SQLDataSource1.InsertParameters.Item("dateEntered").DefaultValue = dateTime.Now()

Try it
Add some data
Open your table and you will see that your data has been entered and the current time and date are also entered


Execute Select, Insert, update or delete command in code
The SQLDataSource control has 4 method that execute SQL commands

  1. Select
  2. Insert
  3. Update
  4. Delete

Each executes the appropriate SQL command

  • Create a new webform
  • Add 2 textboxes
  • Name them txtUsername and txtEmail
  • Add labels to identify the textboxes
  • Add a button, change the text to Add
  • Add a SQLDataSource control and configure it to your database
  • Make sure to generate the SQL statements with the advanced button
  • You can delete the update and delete command and parameters if you want they are not needed
  • Add a gridView and configure it to your SQLDataSource control
  • Double-click the button and add the following code:

SQLDataSource1.InsertParameters(“username”).DefaultValue=txtusername.text
SQLDataSource1.InsertParameters(“email”).DefaultValue=txtemail.text
SQLDataSource1.InsertParameters(“dateEntered”).DefaultValue=datetime.Now()
SQLDataSource1.Insert( )

This code will create your parameters from the information passed in the textboxes when the user clicks the button

Add a label and name it lblMessage

Click on the SQLDataSource event button and double-click inserting

Add this code:
lblMessage.text=”Inserting Data”

Add another label and name it txtMessage2
Click on the SQLDataSource event button and double-click inserted

Add this code:
lblMessage2.text=”Data Inserted”


Data Source Mode

SQLDataSource control retrieves data in 2 ways

  • Dataset (DataView)
  • DataReader

The dataset is used by default

A Datareader is faster and more efficient.
It is a forward-only representation of the data

If you want to just get a fast retrieval of data
Erase the InsertCommand, DeleteCommand and UpdateCommands in the SQLDataSource control code

Delete the insertParameters, UpdateParameters and DeleteParameters tags

Add the following line:
DataSourceMode="DataReader"

The complete code should now look like this:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
DataSourceMode="DataReader"
ConnectionString="<%$ ConnectionStrings:myDatabaseConnectionString %>"
SelectCommand="SELECT * FROM [Contacts]" >    
</asp:SqlDataSource>

This technique is good for fast data retrieval when all you need is to view data

Make sure the gridView is set to your SQLDataSource control


Filtering Rows

You can filter the data returned by the SQLDataSource control

  • Add a new webform
  • Add a textbox, button, SQLDataSource and gridView control
  • Name the textbox control txtFilter

  • Configure the SQLDataSource control for your connection string
  • Select all columns
  • Configure the gridView to the SQLDataSource
  • Look at the code for the SQLDataSource control:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myDatabaseConnectionString %>"
SelectCommand="SELECT * FROM [Contacts]">
</asp:SqlDataSource>
Add a filter expression:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myDatabaseConnectionString %>"
FilterExpression=”Username like ‘{0}%’ ”
SelectCommand="SELECT * FROM [Contacts]">
</asp:SqlDataSource>

Then add a FilterParameter tag between the SQLDataSource tags:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myDatabaseConnectionString %>"
FilterExpression=”Username like ‘{0}%’ ”
SelectCommand="SELECT * FROM [Contacts]">
<FilterParameters>
   <asp:controlParameter Name=”username” controlID=”txtFilter” />
</FilterParameters>
</asp:SqlDataSource>


Stored procedures

You can assign the value StoredProcedure in any of the following properties

  1. SelectCommandType
  2. InsertCommandType
  3. UpdateCommandType
  4. DeleteCommandType

Creating a stored procedure in Visual Studio

  • Open the server explorer
  • Expand the database until you see Stored Procedures

  • Right-click on stored procedures and select Add New Stored Procedure


From here you can add a stored procedure

Type:
CREATE PROCEDURE myStoredProcedure

AS
     
Select Count(*) as NumUsers from Contacts

  • Add a new webform
  • Add a SQLDataSOurce control and configure it to your connectionstring
  • Select Specify a custom SQL statement or stored procedure
  • Click Next

  • Select the Stored Procedure option
  • Select your stored procedure from the dropdown list

  • Click Next
  • Click Finish
  • Test it


      ASP.Net 2.0 tutorials & training