Data Access Control - SqlDataSourceControl: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
Data Access Control - SqlDataSourceControl

page 1 of 1

Published: 01 Nov 2003

Unedited - Community Contributed

Abstract
In Web Matrix, there is a new data access control - SqlDataSourceControl and we can use it to manipulate the datasource and databind to any data web controls, e.g. DropDownList, DataGrid, DataList, Repeater, etc. The beauty is that you can create and configurate the SqlDataSourceControl once easily, and then you can apply it to different data web controls immediately

by Colt Kwong
Feedback

Average Rating:

Views (Total / Last 10 Days): 7853/ 14

Data Access Control - SqlDataSourceControl

Data Access Control - SqlDataSourceControl

Generally speaking, you have to write a couple line of code if you want to retrieve data from datasource and then bind to a data display control.

For example:

Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

Dim myDataAdapter As New SqlDataAdapter("SELECT * FROM Authors", myConnection)

Dim myDataSet As New DataSet()

myDataAdapter.Fill(myDataSet)
myConnection.Close()

DataGrid1.DataSourece = myDataSet
DataGrid1.DataBind()

It’s pretty straightforward, but long and hard to read/write especially when you repeatedly use it for multiple data display control.

By the way, if you have Visual Studio .NET, you can open your Server Explorer and then drag and drop your interested data / source onto the designer pane, and then do a couple of steps in the wizard-guided configuration, drag and drop a data display control, e.g. DataGrid, and then write the code for databinding.

This is a visual and easy way to build a data report, but it is not a time-saving way, and I’m sure most of experienced page developers would prefer the first approach, i.e. inline coding directly.

SqlDataSourceControl provide a fast and easy way to connect and define the command for data access. A new control come with Web Matrix (Version 0.5 Build 464) – SqlDataSourceControl, and the usage of this control and another new control – MxDataGrid – are closely related, which is also be described in the Web Matrix Guided Tour
(http://asp.net/webmatrix/tour/section3/binddatagrid.aspx)

When you connect to a data source and then drag and drop a Database table onto the designer pane of Web Matrix, a MxDataGrid will be used and the SqlDataSourceControl will be configured correctly and bind to the MxDataGrid, so a fully functional data report will be generated immediately.

Procedure:

1. Connect to your data source:

2. Drag your interested Database Table onto the Designer Pane of Web Matrix:

3. Completed: A SqlDataSourceControl and a MxDataGrid were generated, filled and binded automatically.

Data binding to other data display control with SqlDataSourceControl

Actually, the SqlDataSourceControl is NOT limited to the use of MxDataGrid ONLY. The SqlDataSourceControl provide a simple data source connection mechanism, we can only have to define the Select or Delete CommandText, and all of its properties can be found at the property window:

As you can see that the ConnectionString, SelectCommand or UpdateCommand can be modified in the property window at design time, or you may have another question right now: If I store the connection string in Web.Config file, or I want to dynamically change the SQL Select Statement, how can I do that?

The SqlDataSourceControl is basically a Server Control, though it encapsulates a lot of functions and codes, we are able to programmatically create an instance of it or change its properties in runtime.

E.g.

Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

SqlDataSourceControl1.ConnectionString = ConfigurationSettings.AppSettings("connectionString")

End Sub

The nice thing of using SqlDataSourceControl is that you can create such control for data access once, and then you can use it repeatedly for other data display controls on the web form.

The following example showing the use of such control and data binding to different controls, like DataGrid, DropDownList, DataList and Repeater control. Finally, this SqlDataSourceControl is a very friendly control that saves us developers’ lots of time and code, compared with inline coding or even SqlHelper class. What we have to do is just drag and configure a SqlDataSourceControl, and then put it into any data binding control as:

<asp:Repeater id="Repeater1" runat="server"
DataMember="Table"
DataSource="<%# SqlDataSourceControl1 %>"
>
...
</asp:Repeater>

Download

User Comments

Title: Thanks!
Name: Jon
Date: 2005-02-25 5:31:56 PM
Comment:
Great info!

Product Spotlight