- AccessDataSource Web Server Control
in ASP.NET environment,This article describes you how to work with AccessDataSource Web Control.By using AccessDataSource control, we are able to working with Microsoft Access databases.The AccessDataSource control using SQL queries to manipulating the data from database to your desired application.This control is very similar to the SqlDataSource control except that you do not set the ConnectionString property.
Instead of provide the Connectionstring,you simply set the location of the Access .mdb file, using 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/DB1.mdb). This location offers additional security for data files, because they are not served if they are requested directly by the client Web browser.
Features of AccessDataSource
- 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.
Get Data Using the AccessDataSource
After including the AccessDataSource control on your page,then the Next step is to Bind the Data using AccessDataSource control.By using the AccessDataSource control,we can gathering data from a Microsoft Access database (.mdb file).Now, You can display the data in data-bound controls, such as the GridView, FormView, and DetailsView controls.
Connecting to a Database using the AccessDataSource Control
for this purpose,using the DataFile property of the AccessDataSource control that connects to the Microsoft Access database file (.mdb file) where according to specified what in the DataFile property. You can set the DataFile property to a universal naming convention (UNC) path that points to an Access database file.
The following code snippets represents the AccessDatasource connects to a DataBase.
-
<asp:AccessDataSource
id="AccessDataSource1"
DataFile="~/App_Data/DB1.mdb"
runat="server"
SelectCommand="SELECT * FROM Table1">
</asp:AccessDataSource>
-
- Select Data By AccessDataSource Control
The following code snippets helps you to retrieving Data from Database using AccessDataSource Control,
-
<html>
<asp:AccessDataSource
id="AccessDataSource1"
DataFile="~/App_Data/DB1.mdb"
runat="server"
SelectCommand="SELECT * FROM Table1" />
<asp:GridView
id="EmployeesGridView"
runat="server"
AutoGenerateColumns="True"
DataSourceid="AccessDataSource1" />
</html>
-
- Parameters Vs AccessDataSource Control
You can send the parameter values to the ASP.NET data source controls at run time, in ASP.NET environment, the AccessDataSource control accept the parameter value includes via SQL Queries. You can use parameters to supply search criteria for data retrieval; to supply values to be inserted, updated, or deleted in a data store; and to supply values for sorting, paging, and filtering. Using parameters enables you to filter data.
Similarly,another Data Souce controls such as SqlDataSource can accept the parameterized values. For example, the SqlDataSource and AccessDataSource controls allow you to specify parameter placeholders in an SQL statement, such as the SelectCommand
The following code example shows a SqlDataSource control that uses parameterized commands to query and modify data from a data-bound control. Parameters are explicitly specified in order to strongly type parameter values and to specify output parameters.
Parameter Types
- ControlParameter - Sets a parameter to the property value of a Control on an ASP.NET Web page. You specify the Control using the ControlID property. You specify the name of the property that supplies the parameter value using the ControlParameter object's PropertyName property.
- CookieParameter - Sets a parameter to the value of an HttpCookie object. You specify the name of the HttpCookie object using the CookieName property. If the specified HttpCookie object does not exist, then the value of the DefaultValue property is used as the parameter value.
- FormParameter -Sets a parameter to the value of an HTML form field. You specify the name of the HTML form field using the FormField property. If the specified HTML form field value does not exist, then the value of the DefaultValue property is used as the parameter value.
- ProfileParameter -Sets a parameter to the value of a property from the current user profile ( Profile). You specify the name of the profile property using the PropertyName property. If the specified profile property does not exist, then the value of the DefaultValue property is used as the parameter value.
- QueryStringParameter - Sets a parameter to the value of a QueryString field. You specify the name of the QueryString field using the QueryStringField property. If the specified QueryString field does not exist, then the value of the DefaultValue property is used as the parameter value.
- SessionParameter - Sets a parameter to the value of a Session object. You specify the name of the Session object using the SessionField property. If the specified Session object does not exist, then the value of the DefaultValue property is used as the parameter value.
-
in .aspx.cs page,
-
void ddl1_OnSelectedIndexChanged(Object sender, EventArgs e)
{
DV1.DataBind();
}
void DV1_ItemUpdated(Object sender, DetailsViewUpdatedEventArgs e)
{
ddl1.DataBind();
ddl1.SelectedValue = e.Keys["UserId"].ToString();
DV1.DataBind();
}
void DV1_ItemDeleted(Object sender, DetailsViewDeletedEventArgs e)
{
ddl1.DataBind();
}
void SDS1_OnInserted(Object sender, SqlDataSourceStatusEventArgs e)
{
System.Data.Common.DbCommand command = e.Command;
ddl1.DataBind();
ddl1.SelectedValue =
command.Parameters["@prmId"].Value.ToString();
DV1.DataBind();
}
-
in .aspx page,
-
<html>
<head id="Head1" runat="server">
<title>User Details</title>
</head>
<body>
<form id="form2" runat="server">
<h3>
User Details</h3>
<table cellspacing="10">
<tr>
<td valign="top">
<asp:DropDownList ID="ddl1" DataSourceID="SDS1"
DataValueField="UserId" DataTextField="UserName"
AutoPostBack="True" OnSelectedIndexChanged=
"ddl1_OnSelectedIndexChanged" runat="Server" />
</td>
<td valign="top">
<asp:DetailsView ID="DV1" DataSourceID="SDS2"
AutoGenerateRows="false" AutoGenerateInsertButton="true"
AutoGenerateEditButton="true" AutoGenerateDeleteButton=
"true" DataKeyNames="UserId"
GridLines="Both" OnItemUpdated="DV1_ItemUpdated"
OnItemDeleted="DV1_ItemDeleted"
runat="server">
<HeaderStyle BackColor="Navy" ForeColor="White" />
<RowStyle BackColor="White" />
<AlternatingRowStyle BackColor="Blue" />
<EditRowStyle BackColor="Gray" />
<Fields>
<asp:BoundField DataField="UserId"
HeaderText="User ID" InsertVisible="False" ReadOnly="true" />
<asp:BoundField DataField="UserName"
HeaderText="User Name" />
</Fields>
</asp:DetailsView>
</td>
</tr>
</table>
<asp:SqlDataSource ID="SDS1" SelectCommand="SELECT UserId, UserName FROM Table1"
ConnectionString="<%$ ConnectionStrings:connstr %>"
runat="server"></asp:SqlDataSource>
<asp:SqlDataSource ID="SDS2" SelectCommand="SELECT *
FROM Employees WHERE UserId = @prmId"
InsertCommand="INSERT INTO Table1(UserName)
VALUES (@UserName);
SELECT @prmId = SCOPE_IDENTITY()"
UpdateCommand="UPDATE Table1 SET UserName=@UserName
WHERE UserId=@UserId" DeleteCommand="DELETE Table1
WHERE UserId=@UserId"
ConnectionString="<%$ ConnectionStrings:Connstr %>"
OnInserted="SDS1_OnInserted"
runat="server">
<SelectParameters>
<asp:ControlParameter ControlID="ddl1"
PropertyName="SelectedValue" Name="prmId"
Type="Int32" DefaultValue="0" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="UserName" Type="String" />
<asp:Parameter Name="prmId" Direction="Output" Type="Int32"
DefaultValue="0" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="UserName" Type="String" />
<asp:Parameter Name="UserId" Type="Int32" DefaultValue="0" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="UserId" Type="Int32" DefaultValue="0" />
</DeleteParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
-
