ASP.NET - Database Connection

At first, import the "System.Data.OleDb" namespace.To work with Microsoft Access and other OLE DB database providers,we need this namespace. We will create the connection to the database in the Page_Load subroutine and then create a dbconn variable as a new OleDbConnection class with a connection string which identifies the OLE DB provider and the location of the database and 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: The connection string must be a continuous string without a line break!

Create a Database Command

We will create a dbcomm variable as a new OleDbCommand class to specify the records to retrieve from the database.For issuing SQL queries against database tables,the OleDbCommand class is used:

<%@ 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

to read a stream of records from a data source,the OleDbDataReader class is used. By calling the ExecuteReader method of the OleDbCommand object,a DataReader is created:

Bind to a Repeater Control

Then we bind the DataReader to a Repeater control as follows:

<%@ 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>

Close the Database Connection

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

dbread.Close()
dbconn.Close()

Share And Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.

Keywords: ASP.NET using Database Connection, how to database, vb net database, microsoft access database, ms access database, visual basic database, asp net database, vb net using, sql server database, visual basic using, using sql server, connecting to database, connect to database, ado.net database, sql server connection, using source code, data source connection, asp net using, php database, xml database, database tutorial, odbc database, c# database, c# using, vb database, dsn connection, connection jdbc, access database, db database, database forms, mysql database, sql database, odbc connection, client database, oracle database, sql using, database server

HTML Quizes

XML Quizes