WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

ASP.NET Web Forms - Database Connection


ADO.NET is additionally a piece of the .NET Framework. ADO.NET is utilized to deal with information get to. With ADO.NET you can work with databases.


What is ADO.NET?

  • ADO.NET is a piece of the .NET Framework
  • ADO.NET comprises of a lot of classes used to deal with information access
  • ADO.NET is altogether founded on XML
  • ADO.NET has, in contrast to ADO, no Recordset object

Create a Database Connection

We are going to utilize the Northwind database in our examples.

First, import the "System.Data.OleDb" namespace. We need this namespace to work with Microsoft Access and other OLE DB database suppliers. We will make the association with the database in the Page_Load subroutine. We make a dbconn variable as another OleDbConnection class with an association string which distinguishes the OLE DB supplier and the area of the database. At that point we open the database connection:

<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
sub Page_Load
diminish dbconn
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
information source=" & server.mappath("northwind.mdb"))
dbconn.Open()
end sub
</script>

Note: The association string must be a persistent string without a line break!


Create a Database Command

To indicate the records to recover from the database, we will make a dbcomm variable as another OleDbCommand class. The OleDbCommand class is for issuing SQL questions against database tables:

<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
sub Page_Load
diminish dbconn,sql,dbcomm
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
information 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 utilized to peruse a flood of records from an information source. A DataReader is made by calling the ExecuteReader technique for the OleDbCommand object:

<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
sub Page_Load
diminish dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
information source=" & server.mappath("northwind.mdb"))
dbconn.Open()
sql="SELECT * FROM customers"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
end sub
</script>

Bind to a Repeater Control

Then we tie the DataReader to a Repeater control:

Example

<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
sub Page_Load
diminish dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
information 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>

Close the Database Connection

Always close both the DataReader and database association after access to the database is no longer required:

dbread.Close()
dbconn.Close()