<%@ Page %>
Develop a Data Access Web User Control
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"
Create an instance of the control
You can then create an instance of the control in the web form.
In the code behind declare the variable for this control as follows:
DataAccess As DataAccess
Next, set the connection string for the control.
DataAccess.TableName = "myTable"
DataAccess.SQL = mySQL
Alternatively you can also pass the table name and query to open method,
Get the field value of the table,
To get the value for second row
To set the field
To get the record count use,
To update changes,
To insert new row,
Dim objDataRow As DataRow
objDataRow = DataAccess.NewRow()
To open another table,
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")
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
knowledge from diverse technological fields. Working in Web Development
since 3 years.