ASP.NET - Database Connection - 改变热爱

ASP.NET - Database Connection

ADO.NET is also a part of the .NET Framework. ADO.NET is used to handle data access. With ADO.NET you can work with databases.

Examples

Database connection - Bind to a Repeater//中继器,转发器 control
ASPX Source:

<%@ 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("/db/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 bgcolor="#b0c4de">
<th>Companyname</th>
<th>Contactname</th>
<th>Address</th>
<th>City</th>
</tr>
</HeaderTemplate>

<ItemTemplate>
<tr bgcolor="#f0f0f0">
<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>

Output Result:

Companyname Contactname Address City

Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin

Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå

Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F.

Ernst Handel Roland Mendel Kirchgasse 6 Graz

FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid

Galería del gastrónomo Eduardo Saavedra Rambla de Cataluña, 23 Barcelona

Island Trading Helen Bennett Garden House Crowther Way Cowes

Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg

Laughing Bacchus Wine Cellars Yoshi Tannamuri 1900 Oak St. Vancouver

Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22 Bergamo

North/South Simon Crowther South House 300 Queensbridge London

Paris spécialités Marie Bertrand 265, boulevard Charonne Paris

Rattlesnake Canyon Grocery Paula Wilson 2817 Milton Dr. Albuquerque

Simons bistro Jytte Petersen Vinbæltet 34 København

The Big Cheese Liz Nixon 89 Jefferson Way Suite 2 Portland

Vaffeljernet Palle Ibsen Smagsløget 45 Århus

Wolski Zajazd Zbyszek Piestrzeniewicz ul. Filtrowa 68 Warszawa

Database connection - Bind to a DataList//数据 control

ASPX Source:

<%@ 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("/db/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:DataList
id="customers"
runat="server"
cellpadding="2"
cellspacing="2"
borderstyle="inset"
backcolor="#e8e8e8"
width="100%"
headerstyle-font-name="Verdana"
headerstyle-font-size="12pt"
headerstyle-horizontalalign="center"
headerstyle-font-bold="True"
itemstyle-backcolor="#778899"
itemstyle-forecolor="#ffffff"
footerstyle-font-size="9pt"
footerstyle-font-italic="True">

<HeaderTemplate>
Customers Table
</HeaderTemplate>

<ItemTemplate>
<%#Container.DataItem("companyname")%> in
<%#Container.DataItem("address")%>, <%#Container.DataItem("city")%>
</ItemTemplate>

<FooterTemplate>
Source: Northwind Database
</FooterTemplate>

</asp:DataList>
</form>

</body>
</html>

Output Result:

Customers Table

Alfreds Futterkiste in Obere Str. 57, Berlin

Berglunds snabbköp in Berguvsvägen 8, Luleå

Centro comercial Moctezuma in Sierras de Granada 9993, México D.F.

Ernst Handel in Kirchgasse 6, Graz

FISSA Fabrica Inter. Salchichas S.A. in C/ Moralzarzal, 86, Madrid

Galería del gastrónomo in Rambla de Cataluña, 23, Barcelona

Island Trading in Garden House Crowther Way, Cowes

Königlich Essen in Maubelstr. 90, Brandenburg

Laughing Bacchus Wine Cellars in 1900 Oak St., Vancouver

Magazzini Alimentari Riuniti in Via Ludovico il Moro 22, Bergamo

North/South in South House 300 Queensbridge, London

Paris spécialités in 265, boulevard Charonne, Paris

Rattlesnake Canyon Grocery in 2817 Milton Dr., Albuquerque

Simons bistro in Vinbæltet 34, København

The Big Cheese in 89 Jefferson Way Suite 2, Portland

Vaffeljernet in Smagsløget 45, Århus

Wolski Zajazd in ul. Filtrowa 68, Warszawa

Source: Northwind Database

What is ADO.NET?

ADO.NET is a part of the .NET Framework
ADO.NET consists of a set of classes used to handle data access
ADO.NET is entirely based on XML
ADO.NET has, unlike ADO, no Recordset object

Create a Database Connection

We are going to use 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 providers. We will create the connection to the database in the Page_Load subroutine. We 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. 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

To specify the records to retrieve from the database, we will create a dbcomm variable as a new OleDbCommand class. The OleDbCommand class is for issuing SQL queries against 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 a stream of records from a data source. A DataReader is 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>

Bind to a Repeater Control

Then we bind the DataReader 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>

Close the Database Connection

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

dbread.Close()
            dbconn.Close()

posted on 2007-03-26 13:29 改变热爱阅读(271) 评论(0) 收藏举报

刷新页面返回顶部

热爱生命珍惜生命