Introduction
This article will explore various methods of accessing SQL Server from inside Microsoft Expression Web. Microsoft Expression Web is a graphical development tool for creating web pages and is part of the new Expression Studio. The Studio itself consists of four components, Expression Web, Expression Design, Expression Blend, and Expression Media. Expression Media is an asset management tool, complete with version checks. Expression Design is a graphical design and illustration tool. Expression Blend allows various art and design elements to be mixed into a unified user interface. Lastly, Expression Web, the focus of this article, creates web pages. The web pages can be simple or use advanced Expression Web features such as CSS, XML, XPath expressions, and will conform to the XHTML 1.0 Transitional standard. The following URL gives an overview of Microsoft Expression Studio; www.microsoft.com/expression/expression-studio/overview.aspx .
Expression Web
To begin, a trail version of the Expression Web tool can be downloaded from the following URL; http://www.microsoft.com/products/expression/en/expression-web/free-trial.mspx . Once the product is installed, start Expression Web. To create a new web project, from the top menu select File, New, Web Site. Next, select “empty web site” and give the site a name, then click OK. For this first example, a web page will display information stored in the SQL 2005 AdventureWorks database. Next, we need to create the actual web page. From the top menu, select File, New, and ASPX. This will create a blank ASPX page and bring it into the foreground of Expression Web. Click the Save button. Give the page a name and then click “Save”. As shown below, there are three page options to choose from, HTML, ASPX, and CSS.
CSS stands for Cascading Style sheet; they are used to define font and display characteristics. HTML pages are used to display static, non-changing information. If a web page is going to do “work” (work meaning something other than just displaying static information, such as pulling data from a database), then there must be some type of processing language involved. A few language examples include Pearl, Java, and ASPX. ASPX will be used in this article.
SQL Adventure Works
The examples in this article are based on SQL 2005 with the Microsoft test database AdventureWorks. If the test database isn’t installed on your machine, it can be downloaded from Microsoft at the following location; http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en . AdventureWorks is designed as an Online Transaction Processing system and includes a wide variety of tables, data, and procedures.
Gridview to SQL
Return to Expression Web and the ASPX created above. In this example, we’ll use a graphical interface to make a connection to SQL. First, split the ASPX window so both the WSIWIG and code panes are visible by clicking the “Split” button located on the bottom left of the window as shown below.
Next, insure the “Toolbox” pane is visible. If not, click “Task Panes” from the top menu, then Toolbox. Click the GridView control, located under the APS NET Controls Data section.
Drag the GridView onto the blank APSX page WSIWIG section. A GridView is used to display data in a tabular format. The wizard for this control will walk us through making a SQL connection. The “Common Gridview Tasks” box should be visible as pictured below. If not, right click the grid and select “Show Common Control Tasks”.
Click the “Choose Data Source” drop down box and change the selection from “None” to “New Data Source”. On the next screen, “Choose a Data Source Type”, select “Database”. Change the ID to some other name if desired and click OK.
Next, the drop down for “Choose your data Connection” should be blank. Click the “New Connection” button.
For the Data Source, select “Microsoft SQL Server” and keep the default Data Provider. Click OK.
On the “Connection Properties” screen, enter the SQL Server name and the log on security type to use. Select the database name “AdventureWorks” then click the “Test Connection” button. A “test connection succeeded” should appear. Click OK.
Now, “Custom” should be filled in for the “Choose Your Data Connection. Click Next.
Accept the default of saving the connection and click Next.
The “Configure the Select Statement” screen should appear next. From here, a SQL Stored Procedure can be specified or SQL Statement can be created. For this example, select the table “DatabaseLog”. A list of available columns should appear as shown below.
The Advanced button offers a couple of great options. One is to create Insert, Update, and Delete statements for the table you selected. The other is to use optimistic concurrency to check if the underlying table has had row changes since being loaded into the grid. Back on the “Configure the Select Statement” screen, select a couple of columns and click Next.
Now click the “Test Query” button. A few rows with the columns you selected should be returned. The auto-created SQL generated from our GUI selections will also be displayed. In this example, the following SQL statement is returned: SELECT [PostTime], [Event] FROM [DatabaseLog]. Click Finish.
We should now be back to the main Expression Web screen. To test the page and database connection, click save, and then File, Preview in Browser. A web page should open to display records from SQL Server.
The SQL statement created is saved out over a few different places inside the Expression Web project. Column choices are saved with GridView, visible from the “Code” pane. In our example, the columns “PostTime” and “Event” were selected, as shown below.
<asp:GridView runat=”server” id=”GridView1″ AutoGenerateColumns=”False” DataSourceID=”SqlDataSource1″>
<Columns>
<asp:boundfield DataField=”PostTime” SortExpression=”PostTime” HeaderText=”PostTime”>
</asp:boundfield>
<asp:boundfield DataField=”Event” SortExpression=”Event” HeaderText=”Event”>
</asp:boundfield>
</Columns>
</asp:GridView>
The Data Source information is saved as this separate string.
<asp:SqlDataSource runat=”server” ID=”SqlDataSource1″
ConnectionString=”<%$ ConnectionStrings:AdventureWorksConnectionString %>”
SelectCommand=”SELECT [PostTime], [Event] FROM [DatabaseLog]”>
</asp:SqlDataSource>
The actual database connection information is not stored on the page just created. Instead, it’s found in the page “web.config”. The SQL connection information for this example is shown below.
<configuration>
<connectionStrings>
<add name=”AdventureWorksConnectionString” connectionString=”Data Source=dons;Initial Catalog=AdventureWorks;Integrated Security=True”
providerName=”System.Data.SqlClient” />
</connectionStrings>
</configuration>
Schema Names
One note, if the selected SQL 2005 table uses a Schema-Table name convention, such as “People.Sales”, the SQL statement will not create correctly through the GUI. The workaround is to create a SQL statement by hand on the “Configure the Select Statement” screen by clicking the “Specify a custom SQL Statement”.
Conclusion
If you need to quickly display SQL data via a browser, Expression Web provides a quick and simple method.
