ASP.NET - Database connection

ADO.NET is also a component of the .NET framework.

The function of ADO.NET is to handle data access. With ADO.NET, you can operate databases.

Example

Database connection - binding to the Repeater control
Database connection - binding to the DataList control

What is ADO.NET?

  • ADO.NET is a component of the .NET framework
  • ADO.NET consists of a series of classes used to operate data access
  • ADO.NET is completely based on XML
  • ADO.NET does not have a Recordset object, unlike ADO

Create database connection

We intend to use the Northwind database that we have used before.

First, import the namespace "System.Data.OleDb". We need this namespace to be able to operate Microsoft Access and other OLE DB data providers. We will create a connection to this database in the Page_Load subroutine. We created a dbconn variable and assigned a new OleDbConnection class to it, which has a connection string that can indicate the location of OLE DB and the database. Then we opened this database connection:

<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
sub Page_Load
dim dbconn
dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
data source=" & server.mappath("northwind.mdb")")
dbconn.Open()
end sub
</script>

Note:This connection string must be a continuous string without line breaks!

Create database command

To specify the records to be retrieved from the database, we will create a dbcomm variable and assign a new OleDbCommand to it. This OleDbCommand class is used to issue SQL queries against database tables:

<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
sub Page_Load
dim dbconn, sql, dbcomm
dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
data source=" & server.mappath("northwind.mdb")")
dbconn.Open()
sql = "SELECT * FROM customers"
dbcomm = New OleDbCommand(sql, dbconn)
end sub
</script>

Create DataReader

The OleDbDataReader class is used to read a stream of records from a data source. By calling the ExecuteReader method of the OleDbCommand object, you can create a DataReader:

<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
sub Page_Load
dim dbconn, sql, dbcomm, dbread
dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
data source=" & server.mappath("northwind.mdb")")
dbconn.Open()
sql = "SELECT * FROM customers"
dbcomm = New OleDbCommand(sql, dbconn)
dbread=dbcomm.ExecuteReader()
end sub
</script>

Bind to Repeater control

Then, we bind this DataReader to a Repeater control:

<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
sub Page_Load
dim dbconn, sql, dbcomm, dbread
dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
data source=" & server.mappath("northwind.mdb")")
dbconn.Open()
sql = "SELECT * FROM customers"
dbcomm = New OleDbCommand(sql, dbconn)
dbread=dbcomm.ExecuteReader()
customers.DataSource=dbread
customers.DataBind()
dbread.Close()
dbconn.Close()
end sub
</script>
<html>
<body>
<form runat="server">
<asp:Repeater id="customers" runat="server">
<HeaderTemplate>
<table border="1" width="100%">
<tr>
<th>Companyname</th>
<th>Contactname</th>
<th>Address</th>
<th>City</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%#Container.DataItem("companyname")%></td>
<td><%#Container.DataItem("contactname")%></td>
<td><%#Container.DataItem("address")%></td>
<td><%#Container.DataItem("city")%></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</form>
</body>
</html>

Display This Example

Close Database Connection

After accessing the database is not needed, remember to always close the DataReader and database connection:

dbread.Close()
dbconn.Close()