Building a multi-tier data-driven web application using the SqlDataSource control.

Author: Luis Ramirez.
March 8, 2007.

The ASP.NET 2.0 framework has a very good control, the SqlDataSource control. Without write a single line of code you can insert, update, delete and select data. The bad point is that it mixes the presentation layer and the data access layer. It means, the SqlDataSource control embeds the SQL code into your presentation layer. That is the reason that makes many people to choose another options such as: ObjectDataSource control or create their own classes for the data access layer. Here there is a discussion about the advantage and disadvantage of using the SqlDataSource control.

In the following article you will see how to use the SqlDataSource control to create a web application that has two layers: the presentation layer and the data access layer. It means, you won’t mix your SQL code with your presentation layer. Before continue I need to tell you that I have had a grammar mistake, the control that we will use to create our web application is not the SqlDataSource control but the SqlStoreDataSource control. You can download it from the www.sqlnetframework.com web site.

The SqlStoreDataSource control works in the same way that the SqlDataSource control does. The main differences is that it doesn’t embed the SQL code within the aspx file. All the SQL code used by the SqlStoreDataSource control is stored in a SQL repository. For our example we will use a XML file to store the SQL code, an access database and the OleDb data provider to execute the SQL statements..

The first thing that we will make is to create a SqlStoreDataSource instance and then use it to fill a GridView control with data.

<form id="form1" runat="server">
  <div>
    <SqlNetFramework:SqlStoreDataSource ID="dsClients" runat="server" 
          ConnectionID="DemoDb" 
          SelectStatementID="1"/>        
    
    <asp:GridView runat="server" ID="gvClients" DataSourceID="dsClients"></asp:GridView>    
  </div>
</form>

The SqlStoreDataSource’s ConnectionID property is used to get the connection string used to connect to the database server, to get the database factory used to execute the SQL statements and to get the SQL repository. The values associated to the "DemoDb" connection identifier are configured in the web.config file.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connections>
   <connection
    id="DemoDb" 
    connectionString="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE='C:\DemoDb.mdb';" 
    dbFactoryId="OleDbFactory" 
    sqlDataStoreId="DemoDbXmlDataStore"/>
  </connections>
  <sqlDataStores>
    <sqlDataStore 
        id="DemoDbXmlDataStore" 
        sqlDataStoreManager="XmlSqlDataStoreManager" 
        connectionSettings="File=C:\Querys.xml|ResourceLocation=File|ConfigurationMode=Debug" 
        sqlDataCacheMode="All"/>
  </sqlDataStores>
</configuration>

The SqlStoreDataSource’s SelectStatementID property makes references to the SQL statement used to get the data. The identifier specified in the SelectStatementId property is used to find the SQL code within the SQL repository.

<XmlSqlDataStore xmlns="http://tempuri.org/XmlSqlDataStore.xsd">
  <SqlStatement>
    <SqlStatementId>1</SqlStatementId>
    <SqlStatementName>All clients</SqlStatementName>
    <Description />
    <CommandText><![CDATA[SELECT * FROM Clients]]></CommandText>
    <SqlStatementType>Text</SqlStatementType>
    <LastModification>2006-12-05T00:00:00-06:00</LastModification>
  </SqlStatement>
</XmlSqlDataStore>

Finally when you run the web project you have the following result in your browser:

As you can see the SqlStoreDataSource control works as the SqlDataSource control does but it allows you to create multi-tier data driven applications. You can use the SqlStoreDataSource control if you want a control easy to use and also if you want to develop your application with good patterns.

For more information about the SqlStoreDataSource control you can visit www.sqlnetframework.com, take a look to see how the SqlNetFramework can help you to work with SQL in your applications. Download the SqlNetFramework to see a live demo.

$ 75 USD