ASP.NET Web Forms - Database Connection
In this section, we explain How database connections are implemented in ASP.NET. In the following content, you will come into contact with ADO.NET.ADO.NET is also part of the .NET Framework. ADO.NET is used to handle data access. Through ADO.NET, you can operate the database.
Example
Database connection - bind to DataList control
Database connection - bound to Repeater control
ADO.NET is part of the .NET Framework
ADO.NET consists of a series of classes that handle data access
ADO.NET is completely based on XML
ADO.NET does not have a Recordset object, unlike ADO
In our example, we will use the 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:
<%@ Import Namespace="System.Data.OleDb" %><script runat="server">sub Page_Loaddim dbconndbconn=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!
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:
<%@ Import Namespace="System.Data.OleDb" %><script runat="server">sub Page_Loaddim dbconn,sql,dbcommdbconn=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>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:
<%@ Import Namespace="System.Data.OleDb" %><script runat="server">sub Page_Loaddim dbconn,sql,dbcomm,dbreaddbconn=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>Then, we bind the DataReader to the Repeater control:
<%@ Import Namespace="System.Data.OleDb" %><script runat="server">sub Page_Loaddim dbconn,sql,dbcomm,dbreaddbconn=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=dbreadcustomers.DataBind()dbread.Close()dbconn.Close()end sub</script><html><body> <form runat="server"><asp:Repeater id="customers" runat="server"><HeaderTemplate><table><tr><th>Companyname</th><th>Contactname</th><th>Address</th><th>City</th></tr ></HeaderTemplate><ItemTemplate><tr><td><%#Container.DataItem("companyname")%></td><td><%#Contain er.DataItem("contactname")%></td><td><%#Container.DataItem("address")%></td><td><%#Container.DataItem("city")%> </td></tr></ItemTemplate><FooterTemplate></table></FooterTemplate></asp:Repeater></form></body></html>If you no longer need to access the database, remember to close the DataReader and the database connection:
dbread.Close()dbconn.Close()