Connect Database to ASP.NET application

**Ainsley** · 12-01-2010

I am looking for the database connectivity in ASP.NET with SQLServer 2005 or MS-Access?What is the ADO.NET? How can we made the connection between Front end and Back end? What is the concept behind Front end and Back end ? Tell me something about ASP.NET applications and their uses.

**Modifier** · 12-01-2010

ADO.NET is the tool that is provided by .NET Framework. ADO.NET is used for the applications to access the data from database. With ADO.NET you can work with databases. ADO.NET provides a lot of components for creating,accessing the data-sharing applications. It is an integral part of the .NET Framework, providing access to relational data and application data. ADO.NET supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects used by applications, tools, languages or Internet browsers.

**MindSpace** · 12-01-2010

Create a Database Connection
We will use the MS-Access database in our examples with the file name northwind.mdb. You need to import the "System.Data.OleDb" namespace first . We need this namespace to work with Microsoft Access database and other OLE DB database providers. We are planning to create the connection to the database at Page_Load subroutine means when the application would open it will interact to oledb after all the library functions and namespaces are activated . To make a connection create a dbconn variable as a new OleDbConnection class with a connection string that indicates the OLE DB provider and the location of the 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>

Create Database Command
To fetch the record from the database, we will create a dbcomm variable as a new oledb command class through which we can access the database with the help of SQL queries :

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

**opaper** · 12-01-2010

If you have defined a connection variable and command variable so you should create two variables more one for data reader and data control variables which are as follows:

Create a DataReader

After creating the Database command variable, we would create a data reader which read the

command output :

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

Bind to a 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>

Closing Connections

After all that you need to close every connection which are not being used by application:

dbread.Close()
dbconn.Close()

**Reegan** · 12-01-2010

Basically the front-end is for the general users who makes a lot of queries for Example- viewers, consumers,etc. While the back-end is the storage of data which is handled by advanced users and developers.
When you are surfing on the internet, all the URLs that you see and enter into URL bar all the information are stored into the back end. We use the front end as application that can be an internet application(web application) and window applications. If you are requesting for a page on the internet ,you are treated as the client in technical word and the pages from where you are fetching currently, it's called as server.
The real application example of their direct connection is when a user needs log in. They access the web page. They see the log in through the front end. Other than entering the information required all of what happens from that point is all done in the back end. A file of data that is related to login stored on a back end server. Then that tells the information to search a database on another back end server, and verify your existence.
If you are going for windows application that is being used locally means back end and front end is handled by only one computer.You can use a simple database connectivity to use a file or database templates.