Use RAD with Visual Studio WebForms to Programmatically Query a SQLDataSource Control from Custom Code

Rapid Application Development (RAD) offers huge advantages to developers under time constraints looking to get an application released as quickly as possible. Visual Studio 2010’s WebForms is a great environment to use when looking at possible RAD solutions.

In this article I will cover the issue of using RAD with custom code to speed up writing database connections and queries. This is part of my series of articles investigating ASP.NET and Visual Studio 2010.

Why is RAD so useful with Custom Code?

Developers often end up hand-coding every step of a database connection, which includes processing the results and populating whatever widget they are working with. Given a large number of controls on a form that need to get populated at various steps in the form’s life-cycle, populating and maintaining each control can become a tedious process that eats up precious development time.

The really big advantage of RAD is that RAD controls can be used to accelerate writing custom code without the developer needing to create or use custom helper objects.

So how to create and integrate custom code with RAD controls?

Let’s say that you need to build a custom function such as an access control function. This will naturally involve custom code where you will allow some users read-only access to your Web form, while others have update/read access, and administrative users have full control.

Really what you need here is to pass a unique user id and a page id to the database to check what level of access the user has to that page. There are a huge number of options for how to do this, and I’m sure everyone has their own favorite methods for doing so.

However, the RAD way is quite fast and simple:

1. To use the RAD method, use Visual Studio to drag and drop a DataSource control onto your Web form.
2. Then use the DataSource wizard to connect the control to the database and to set up your query to check for the access level of the user on the page.
3. Make sure to set the Select Parameters of your DataSource control in the easiest way for you to manage. In the example below we will set both parameters that we want to pass via the values stored in the session state.
4. Then switch to the codebehind of your WebForm and locate the code area where you would like to execute your query.
5. You can use the .NET Select( command to activate your DataSource control and populate a storage mechanism such as a SQLDataReader

So that’s pretty much all there is to it. You need a properly set up DataSource control on your page, and then you can tell the control to talk to your database using the SQLDataSource.Select( command.

An Example of Using RAD to Check the Database for User Permissions

So to put theory into practice, here is an example of a simple SQLDataSource that does a Select-only query to an imaginary database access-control list table via a stored procedure. In our example we will check if the user has administrative privileges based on the user’s Id, and the page being accessed.

Here is our SQLDataSource that we add to our WebForm via the Visual Studio UI:

<asp:SqlDataSource ID="SqlDSUserACL" 
runat="server" 
ConnectionString="<%$ 
ConnectionStrings:ConnStr %>" 
ProviderName="<%$ ConnectionStrings:ConnStr.ProviderName %>" EnableCaching="false" 
SelectCommandType="StoredProcedure" 
SelectCommand="AccessCheckQ" 
DataSourceMode="DataReader">
<SelectParameters>
<asp:SessionParameter Name="UserId" SessionField="userident" DefaultValue="0" />
<asp:SessionParameter Name="PageName" SessionField="QuerySelected" DefaultValue="TestPage" />
</SelectParameters>
</asp:SqlDataSource>

Then we add some code in the Page Load event that will execute the stored procedure via the SQLDataSource on the page. The custom code will then check the returned information and make a decision as to whether or not the user should see a special administrative function on the page by enabling or disabling a div tag:

Dim strACL As String = String.Empty
Dim myDVReader As SqlDataReader = CType(SqlDSUserACL.Select(DataSourceSelectArguments.Empty), SqlDataReader)
While myDVReader.Read
strACL = CStr(myDVReader(0))
End While
If Not String.IsNullOrEmpty(strACL) Then
divAdminButton.Style.Remove("display")
divAdminButton.Style.Add("display", "inline")
Else
divAdminButton.Style.Remove("display")
divAdminButton.Style.Add("display", "none")
End If

In the code above, the line that calls the SQLDataSource and gets the data returned from it is:

Dim myDVReader As SqlDataReader = CType(SqlDSUserACL.Select(DataSourceSelectArguments.Empty), SqlDataReader)

What does the trick is the Select( command of the SQLDataSource control. Calling this method and passing the option DataSourceSelectArguments.Empty tells the SQLDataSource control to call the database using the parameters specified at design-time, which will populate a SQLDataReader with which the programmer can do as they wish.

As you can see this is pretty straightforward and the select arguments are left to the default ones specified in the DataSource. So because the SQLDataSource will check the session values before it calls the database, the programmer has the option to update the Session values right before calling the Select( command of the SQLDataSource control.

The select parameters in this example are set using values derived from Session parameters, but naturally the values could be set and obtained in the same manner as SQLDataSource parameters are typically set via hidden fields for example.

So to Sum Things Up

As you can see, using RAD you can significantly increase your speed of development.

In this case we have an incredibly simple and versatile way to set up and activate a Database connection using a SQLDataSource control. If we need to work with the database, we can access the the control programmatically and populate (for example) a SQLDataReader variable with the results of the query. Similar syntax can be used to programmatically Update, Insert, or Delete from the database via a SQLDataSource control.