ASP.NET database connection
ADO.NET is an integral part of the .NET framework. ADO.NET for handling data access. By ADO.NET, you can operate the database.
Try - Example
Database Connection - bound to the DataList control
Database Connection - bound to the Repeater control
What is ADO.NET?
- ADO.NET is an integral part of the .NET Framework
- By a series of classes for working with ADO.NET data access composition
- ADO.NET based entirely on XML
- ADO.NET no Recordset object, which is different with ADO
Create a database connection
In our example, we will use the Northwind database.
First, import the "System.Data.OleDb" namespace. We need this namespace to manipulate Microsoft Access and other OLE DB database providers. We will create the connection to the database in the Page_Load subroutine. Dbconn we create a variable and assign a new OleDbConnection class that indicates the location of the database and OLE DB provider connection string with. 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: The connection string must be no continuous string off line!
Create Database command
To specify the need to retrieve from the database records, we will create a dbcomm variable, and assign a new OleDbCommand class. The OleDbCommand class is used to issue SQL queries against the 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 a DataReader
OleDbDataReader class is used to read a stream of records from a data source. DataReader by calling OleDbCommand object's ExecuteReader method to create:
<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>
Bound to the Repeater control
Then we bind the DataReader to a Repeater control:
Examples
<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>
The demonstration >>
Close the database connection
If you no longer need to access the database, please remember to close the DataReader and database connections:
dbconn.Close()