Microsoft Expression Web - SQL Datasource


Advertisements


Expression Web has many tools for working with static HTML pages, but it also has tools for creating more dynamic pages. Dynamic pages often retrieve data or content from a source of data. In this chapter, we will learn how to create a SQL DataSource to be used in dynamic pages.

Example

Let’s take a look at a simple example in which we create a dropdown list and fill the list by retrieving data from the database using SQL DataSource.

Step 1 − Start by creating a new empty website.

Empty Website

Let’s call it SQLDataSource. Click OK.

Step 2 − You will see that expression web has created a folder for us. However, since we chose to create an empty project, so we don't have any files yet.

Empty Project

Expression Web doesn't have to be used just to create static HTML sites; we can create dynamic sites using pages and controls with much ease.

Step 3 − Expression web also allows us to create a lot of dynamic content without necessarily having to be a developer. So let’s add an ASPX file from the File → New Page menu option. Select ASPX in the middle model and click OK.

New Pages

Step 4 − Here we will be creating a control in our form that needs to access data from a database to create a dynamic website.

In this particular case, we will create a SQL DataSource which will retrieve data from the database.

SQL DataSource

Step 5 − Go to the Toolbox and drag the DropDownList and drop it inside the <form> tag in Code View or you can also drop it in the form section in Design View. You will see that the code is added by Expression Web.

Here, we want to connect some data from our database and store that in our dropdown list. So, the first thing we need is the database.

DropDownList

Step 6 − Create a new Folder in your Project folder by going to the New → Folder menu option.

New Folder

Step 7 − Call this folder App_Data.

App_Data

Step 8 − We need to import a database in our project by using the File → Import → File… menu option.

Import File

Step 9 − It will open the Import dialog, as shown below. Click on the Add File… button which will open the open file dialog box.

Import dialog

Step 10 − Browse to the database (*.mdf file) which you want to include in the website and click Open.

Database

Step 11 − Open the MyTestDatabase.mdf. It will display the following dialog. Click OK.

MyTestDatabase

Step 12 − Now you can see that the database file is added in the App_Data folder. When you have a control like a dropdown list and Expression Web allows you to bind the data to it, then you will see a little arrow at the top right corner in the design view.

App_Data Folder

Step 13 − This is the current context menu that can allow us to do a very specific task for that particular control and one of those is to choose a data source. So let’s click on Choose Data Source… and that will open the data source configuration wizard.

Choose Data Source

Step 14 − Currently, we don’t have a data source. Let’s select the New data source option from the menu.

New Data Source

Here, we will import an SQL database. Let’s select the database and click OK.

Step 15 − Now, we need to specify the connection string. Let’s click the New Connection button.

New Connection

Step 16 − Select the Microsoft SQL Server Database File and click OK.

Microsoft SQL Server

Step 17 − Click the Browse button to locate the database file.

Locate the Database

Step 18 − Select the database file and click the Open button or double-click the database file.

Database File

Step 19 − To test the connection, click the Test Connection button.

Test Connection

It will display the following screen. Click the OK button.

Display Screen

Step 20 − Now, we will configure the data source. Click Next on the following screen.

Configure Datasource

Check the checkbox and click Next again.

Check Box

Step 21 − Here you will see all the tables from your database. Let’s select the Student table.

Student Table

Step 22 − And you will see the all the columns in the list box. Select ID and LastName. At the bottom, you can see that it is actually creating a query. You can also you use the Where or ORDER BY clauses. Once you are done with the query, click Next.

Order by Clauses

Step 23 − Click the Test Query button. It will display the query result as shown below.

Query Result

Step 24 − In the following dialog box, the data source is selected by default. The “Select a data field to display in the DropDownList” is the field that is actually going to be displayed. Let’s select LastName and select the ID from the “Select a data field for the value of the DropDownList” and click Ok.

DataSource Configuration Wizard

As you can see in the following screenshot, DataSource is added in design view.

Datasource Added

Following is the complete code in ASPX file, which is created by Expression Web.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<%@ Page Language = "C#" %> 
<html dir = "ltr" xmlns = "http://www.w3.org/1999/xhtml">  
   <head runat = "server"> 
      <meta content = "text/html; charset = utf-8" http-equiv = "Content-Type" /> 
      <title>Untitled 1</title> 
   </head>  

   <body>  
      <form id = "form1" runat = "server"> 
         <asp:DropDownList id = "listID" runat = "server" DataSourceID = "SqlDataSource1"  
            DataTextField = "LastName" DataValueField = "ID"> 
         </asp:DropDownList> 

         <asp:SqlDataSource ID = "SqlDataSource1" runat = "server"   
            ConnectionString = "<%$ ConnectionStrings:MyTestDatabaseConnectionString %>"  
               SelectCommand = "SELECT [ID], [LastName] FROM [Student]"> 
         </asp:SqlDataSource> 
      </form>  
   </body>  
</html>

Step 25 − Let’s save the webpage by pressing Ctrl + S.

Save the Webpage

Call this page SQLDatasource.aspx and click Save.

Step 26 − Let’s preview this page in a browser. You will see a dropdown list which contains the last name of the students from the Student table.

Student Name

Advertisements