Web Forms database connection
ASP.NET Web Forms - Database Connection
ADO.NET is also part of the .NET Framework. ADO.NET is used to handle data access. ADO.NET allows you to manipulate databases.
Try it - Example
Database connection-Bind to the DataList control
Database connection-Bind to the Repeater control
What is ADO.NET?
- ADO.NET is an integral part 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 does not have a Recordset object, which is different from ADO
Creating a database connection
In our example, we will use Northwind database.
First, import the "System.Data.OleDb" namespace. We need this namespace to operate Microsoft Access and other OLE DB database providers. We will create the connection to this database in the Page_Load subroutine. We create a dbconn variable and assign it a new OleDbConnection class with 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
In order to specify the records to be retrieved from the database, we will create a dbcomm variable and assign it a new OleDbCommand class. The 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 the record stream from the 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 the Repeater control
Then, we bind the DataReader to the Repeater control:
Instance
<%@ 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("/db/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>
<!DOCTYPE html>
<html>
<body>
<form runat="server">
<asp:Repeater id="customers" runat="server">
<HeaderTemplate>
<table border="1" width="100%">
<tr bgcolor="#b0c4de">
<th>Companyname</th>
<th>Contactname</th>
<th>Address</th>
<th>City</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr bgcolor="#f0f0f0">
<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>Run Instance»
Click the "Run Instance" button to view the online instance
Close the database connection
If you no longer need to access the database, please remember to close the DataReader and database connection:
dbconn.Close( )
