October 2017 : Online course in ASP.NET MVC / Core. Conducted by Bipin Joshi. Read more...
Registration for October 2017 batch of ASP.NET MVC / Core online course has already started. Conducted by Bipin Joshi. Book your seat today ! Click here for more details.

<%@ Page %>

Develop a Data Access Web User Control

Introduction

Microsoft has provided ADO.NET a next generation of data access technology. ADO.NET provides enhanced functionality to that of ADO. But you need to write a bit of more code to access the Data using ADO.NET. This work can be minimized using a Custom Web User Control. Data Access User Control illustrated here provides easy way of accessing MS Access (*.mdb) database, using which you can access data similar to ADO. You can easily changes the control to work with SQL database also.

Data Access Control

Using Data Access User Control you can open the table and access the table content just like recordset of ADO. You can modify the data and can update it just by calling the corresponding update command. You can add as many tables as you want to this control and can access the data from these tables easily.

List of properties of Data Access control

  • ConnectionString() As String : To set the connection string for the connection object
  • TableName() As String : To set the table name, will be useful to refer to the open tables. You can also pass the tableName as an argument to the .open method of the DataAccess control, hence setting this property is optional.
  • SQL() As String : To set the SQL Query to retrieve the data from data source. You can also pass the SQL as an argument to the .open method of the DataAccess control.
  • ReadOnly Property RecordCount([tableName as string]) As Integer : Get the number of records in the last open table or table specified by tableName argument.
  • Field(fieldName as string, [rowNum as integer]): To get or set the value of field fieldName for the first row or the row specified by rowNum argument in last open table.
  • Field(fieldname as string, [tableName as string]): To get or set the value of the field strFieldName for the first row or the row specified by rowNum argument in the table specified by tableName argument.
  • ReadOnly Property NewRow([tableName as string]) As DataRow: Get new row for the last open table or table specified by tableName argument. This will be useful to get new row object to insert new record to the datatable.
  • ReadOnly Property Table([tableName as string]) as DataTable: To get the table object for the last open table or table specified by tableName argument. List of Methods of the Data Access control
  • OpenConnection(): Will open the connection to the data source. Calling this method is optional as it will automatically open up the connection while filling the dataset. But it is efficient to open the connection explicitly when retrieving data from more than one table.
  • CloseConnection(): Will close the connection if it is opened explicitly.
  • Open([tableName as string], [Query as string]) : Method that open up table for the last set query or query passed in as an argument. The table name is set as last set table name or table name specified by tableName argument.
  • AddNewRow(objNewDataRow as DataRow, [tableName as string]): Adds the new row passed in as an argument to the last open table or to the table specified by tableName argument. (objDataRow is a DataRow object obtained from the NewRow property.)
  • UpdateInsert([tableName as string]: Will update all insert operations to the last set table or table specified in the tableName argument.
  • UpdateChanges([tableName as string]: Will update all modifications to the last set table or table specified in the tableName argument.
  • UpdateDelete([tableName as string]: Will update all deletion operations to the last set table or table specified in the tableName argument.

How to use the Data Access User Control

In order to use Data Access User Control you need to register it in the web form using @Register directive.
<%@ Register TagPrefix="UserControl"
TagName="DataAccess"
src="DataAccess.ascx"%>

Create an instance of the control

You can then create an instance of the control in the web form.
<UserControl:DataAccess
id="DataAccess" runat="server">
</UserControl:DataAccess>
In the code behind declare the variable for this control as follows:
Protected WithEvents
DataAccess As DataAccess
Next, set the connection string for the control.
DataAccess.ConnectionString="connection_string_here"
DataAccess.TableName = "myTable"
DataAccess.SQL = mySQL
DataAccess.Open()
Alternatively you can also pass the table name and query to open method,
DataAccess.Open("myTable", mySQL)

Common Tasks

Get the field value of the table,
DataAccess.Field("myFieldName")
or
DataAccess.Field("myTable", "myFieldName")
To get the value for second row
DataAccess.Field("myFieldName",,2)
or
DataAccess.Field("myTable", "myFieldName",2)
To set the field
DataAccess.Field("myFieldName")=valuetoSet
To get the record count use,
DataAccess.RecordCount
To update changes,
DataAccess.UpdateChanges()
To insert new row,
Dim objDataRow As DataRow
objDataRow = DataAccess.NewRow()
objDataRow("field1")=fieldValue1
objDataRow("field2")=fieldValue2
DataAccess.AddNewRow(objDataRow)
DataAccess.UpdateInsert()
To open another table,
DataAccess.Open("myTable1", mySQL1)
To get table object to bind to the data grid,
DataGrid1.DataSource = DataAccess.Table
Or to bind to one of the open table
DataGrid1.DataSource = DataAccess.Table("mytable")
DataGrid1.DataBind()

Bhagvan Chougule did his Post Graduation Mechanical (CAD/CAM) from Swami Ramanand Thirth Marathwada University. A motivated and self taught individual, I relish working in stimulating environments and assimilating knowledge from diverse technological fields. Working in Web Development since 3 years.

Posted On : 09 February 2003


Tags : ASP.NET Data Access Server Controls Custom Controls