ASP.NET Web Forms - Database Connection
ADO.NET is also a component of the .NET framework. ADO.NET is used to handle data access. Through ADO.NET, you can operate on databases.
Try it - Example
Database Connection - Bind to DataList Control
Database Connection - Bind to Repeater Control
What is ADO.NET?
- ADO.NET is a component of the .NET framework
- ADO.NET consists of a series of classes used to handle data access
- ADO.NET is completely based on XML
- ADO.NET has no Recordset object, which is different from ADO
Create Database Connection
In our example, we will use the Northwind database.
First, import the "System.Data.OleDb" namespace. We need this namespace to operate with Microsoft Access and other OLE DB database providers. We will create the database connection in the Page_Load subroutine. We create a dbconn variable and assign it a new OleDbConnection class, which has a connection string indicating the OLE DB provider and database location. Then we open the database connection:
<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 it a new OleDbCommand class. This OleDbCommand class is used to issue SQL queries against database tables:
<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. The DataReader is created by calling the ExecuteReader method of the OleDbCommand object:
<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 the DataReader to the Repeater control:
Example
<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>
Demo Example »
Close Database Connection
If you no longer need to access the database, remember to close the DataReader and the database connection:
dbconn.Close()
Other Extensions