Microsoft.NET

……………………………………………….Expertise in .NET Technologies

AccessDataSource Control in ASP.NET 3.5

Posted by Ravi Varma Thumati on October 7, 2009

This control is designed to work with Microsoft Access. It uses OleDb data provider internally. OleDb data provider classes are defined in the System.Data.OleDb namespace. The AccessDataSource class is a data source control that works with Microsoft Access databases. Like its base class, SqlDataSource, the AccessDataSource control uses SQL queries to perform data retrieval.

One of the unique characteristics of the AccessDataSource control is that you do not set the ConnectionString property. All you need to do is set the location of the Access (.mdb) file in the DataFile property and the AccessDataSource takes care of the underlying connection to the database. You should place Access databases in the App_Data directory of the Web site and reference them by a relative path (for example, ~/App_Data/Test.mdb). This location offers additional security for data files, because they are not served if they are requested directly by the client Web browser. You bind data-bound controls to an AccessDataSource using the DataSourceID property of the data-bound control.

The following are the features of the AccessDataSource control.

  • Sorting – Set the DataSourceMode property to the DataSet value.
  • Filtering – Set the FilterExpression property to a filtering expression used to filter the data when the Select method is called.
  • Paging – The AccessDataSource does not support direct paging operations on an Access database. A data-bound control, such as the GridView, can page over the items returned by the AccessDataSource, if the DataSourceMode property is set to the DataSet value.
  • Updating – Set the UpdateCommand property to a SQL statement used to update data. This statement is typically parameterized.
  • Deleting – Set the DeleteCommand property to a SQL statement used to delete data. This statement is typically parameterized.
  • Inserting – Set the InsertCommand property to a SQL statement used to insert data. This statement is typically parameterized.
  • Caching – Set the DataSourceMode property to the DataSet value, the EnableCaching property to true, and the CacheDuration and CacheExpirationPolicy properties according to the caching behavior you want for your cached data.

<asp:AccessDataSource ID=”AccessDataSource1″ runat=”server”

        DataSourceMode=”DataSet”

            DataFile=”~/App_Data/Test.mdb”

            SelectCommand=”SELECT [ID], [FirstName], [State], [Country], [City], [LastName] FROM [Employers]”>

        </asp:AccessDataSource>

<asp:GridView ID=”GridView1″ runat=”server” AllowPaging=”True”

            AutoGenerateColumns=”False” BackColor=”LightGoldenrodYellow” BorderColor=”Tan”

            BorderWidth=”1px” CellPadding=”2″ DataKeyNames=”ID”

            DataSourceID=”AccessDataSource1″ ForeColor=”Black” GridLines=”None”>

            <Columns>

                <asp:BoundField DataField=”ID” HeaderText=”ID” InsertVisible=”False”

                    ReadOnly=”True” SortExpression=”ID” />

                <asp:BoundField DataField=”FirstName” HeaderText=”FirstName”

                    SortExpression=”FirstName” />

                <asp:BoundField DataField=”State” HeaderText=”State” SortExpression=”State” />

                <asp:BoundField DataField=”Country” HeaderText=”Country”

                    SortExpression=”Country” />

                <asp:BoundField DataField=”City” HeaderText=”City” SortExpression=”City” />

                <asp:BoundField DataField=”LastName” HeaderText=”LastName”

                    SortExpression=”LastName” />

            </Columns>

            <FooterStyle BackColor=”Tan” />

            <PagerStyle BackColor=”PaleGoldenrod” ForeColor=”DarkSlateBlue”

                HorizontalAlign=”Center” />

            <SelectedRowStyle BackColor=”DarkSlateBlue” ForeColor=”GhostWhite” />

            <HeaderStyle BackColor=”Tan” Font-Bold=”True” />

            <AlternatingRowStyle BackColor=”PaleGoldenrod” />

        </asp:GridView>

Let’s discuss by diagram now:

First of all drag and drop AccessDataSource control from toolbox.

Access DataSource1 

Figure 1.

And now configure AccessDataSource.

Access DataSource2 

Figure 2.

After that Configure Data Source.

Access DataSource3 

Figure 3.

And now select data by query from database.

Access DataSource4 

Figure 4.

If you want then you can test your query.

Access DataSource5 

Figure 5.

Result looks like this.

Access DataSource6 

Figure 6.

Rest all the information in attached application. Database is inside of App_Data folder. If you want then attach it and work it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: