ASP.NET web Forms
- database connection
In this section, we covered
A
SP.NET database connections in the database are implemented.
In the following, you will be exposed to ADO.NET.
ADO.NET is also part of the .NET framework. A
DO.NET is used to process data access.
With ADO.NET, you can manipulate the database.
Try it - instance
Database connection - bound to the DataList control
Database connection - bound to the Repeater control
What is ADO.NET?
-
ADO.NET is part of the .NET framework
-
ADO.NET consists of a series of classes that handle data access
-
ADO.NET is based entirely on XML
-
ADO.NET does not have a Recordset object, unlike ADO
Create a database connection
In our case, we'll use the Northwind database.
First, import the "System.Data.OleDb" namespace. W
e need this namespace to operate Microsoft Access and other OLE DB database providers. W
e'Page_Load the connection to this database in the new sub-routine. W
e create a dbconn variable and assign it a new OleDbConnection class with a connection string indicating the location of the OLE DB provider and database.
Then we open the 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 with no folds!
Create a database command
In order to specify the records that need to be taken back 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 for 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 a DataReader
The OleDbDataReader class is used to read the record stream from the data source.
DataReader was created by calling the ExecuteReader method of the OleDbCommand object:
<%@ 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>
Binding to the Repeater control
Then we bind DataReader to the 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>
Demo Examples . . .
Close the database connection
If you no longer need access to the database, remember to close the DataReader and database connections:
dbread.Close()
dbconn.Close()
Related articles
ASP.NET ADO.NET