<% Import Namespace="System. Data"%> <% Import Namespace="System. Data ADO"%> <script language="VB" RunAt="Server> Dim cn As ADOConnection cn= New ADOConnection("provider=MicrosoftJet OLEDB 4.0; " Data Source=C: \ Program Files Microsoft OfficelOfficelSamples Northwind mdb, " cn.Open <script> 下面的代码建立了到 MS SQL Server数据库的连接: %( Import Namespace="System. Data"%> <% Import Namespace="System. Data ADO"%> <script language="VB" RunAt="Server> Dim cn As ADOConnection cn New ADOConnection("Provider=SQLOLEDB. 1; Data Source=(local); uid=sa pwd= Initial Catalog=pubs) ADO NET目前支持下面的几个 OLEDB OLEDB驱动程序 提供者 SQLOLEDB OL OLE DB Provider MSDAORA Oracle OLE DB Provider JOLt Jet OLE DB Provider MSDASQU/SQLServer ODBC SQL Server ODbC Driver via OLE DB for ODBC Provider MSDASQLJet OdbC Jet odbc driver via ole db provider for ODBC Provider 据 来 源 http://msdn.microsoftcom/ibrary/defaultasp?url=/libRary/DotnEt/cpguide/cpconaccessingdatay thado. htm 33221一个完整的例子 <%a Page Language=vb"%> <%( Import Namespace="System. Data"%> %( Import Namespace ="System. Data ADO"%> <script runat==server>
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.ADO" %> <script language=”VB” RunAt=”Server”> … Dim cn As ADOConnection cn = New ADOConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;") cn.Open() … </script> 下面的代码建立了到 MS SQL Server 数据库的连接： <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.ADO" %> <script language=”VB” RunAt=”Server”> … Dim cn As ADOConnection cn = New ADOConnection("Provider=SQLOLEDB.1;Data Source=(local);uid=sa;pwd=;Initial Catalog=pubs”) cn.Open() … </script> ADO.NET 目前支持下面的几个 OLEDB： OLEDB 驱动程序 提供者 SQLOLEDB SQL OLE DB Provider MSDAORA Oracle OLE DB Provider JOLT Jet OLE DB Provider MSDASQL/SQLServer ODBC SQL Server ODBC Driver via OLE DB for ODBC Provider MSDASQL/Jet ODBC Jet ODBC Driver via OLE DB Provider for ODBC Provider ( 数据来源： http://msdn.microsoft.com/library/default.asp?URL=/library/dotnet/cpguide/cpconaccessingdatawi thado.htm ) 3.3.2.2.1 一个完整的例子 <%@ Page Language="vb" %> <%@ Import Namespace = "System.Data" %> <%@ Import Namespace = "System.Data.ADO" %> <html> <head> <script runat=server>
Sub Page Load( By Val Sender As Object, By Val e As EventArgs) On error resume next Dim cn As ADOConnectio ew ADOConnection("provider=Microsoft Jet OLEDB 4.0; Data Source=C: \ Program Files\Microsoft OfficelOfficelSamples Northwind mdb Ifcn State=1 Then IblReturn Code. Text = "The Connection State is: " cn State " Connection Succeeded Else IblReturn Code. Text = "The Connection State is. " cn State " Connection Failed End If End sub <asp: Label id="lbl Return Code"Runat=server A> </html 3.3.2.3 SQL Managed Provider FH Microsoft SQLServer 通过 SQL Managed Provider建立到 MS SQL Server的连接很简单 <% Import Namespace="System. Data"%0> <%(@ Import Namespace="System. Data. SQL"%> <script language="VB"RunAt="Server"> Dim obj Conn SQLConnection N ADOConnection("server=localhost; uid=sa pwd= database=pubs; " obj Conn. Open(·打开数据链路 请注意几个地方 1、 Import语句的不同。在 ADO. NET Managed Provider里面,我们 Import的是 System Data. ADO;而这里需要 System Data. SQL 2、连接对象也不同。在 ADO. NET Managed Provider中,所有的对象以ADO打头;而这里 需要以SQL打头
Sub Page_Load(ByVal Sender As Object, ByVal e As EventArgs) On Error Resume Next Dim cn As ADOConnection cn = New ADOConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;") cn.Open() If cn.State = 1 Then lblReturnCode.Text = "The Connection State is: " & cn.State & " - Connection Succeeded" Else lblReturnCode.Text = "The Connection State is: " & cn.State & " - Connection Failed" End If End Sub </script> </head> <body> <asp:Label id="lblReturnCode" Runat=server /> </body> </html> 3.3.2.3 SQL Managed Provider 和 Microsoft SQL Server 通过 SQL Managed Provider 建立到 MS SQL Server 的连接很简单： <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQL" %> <script language=”VB” RunAt=”Server”> … Dim objConn as SQLConnection = New ADOConnection("server=localhost;uid=sa;pwd=;database=pubs;") objConn.Open() '打开数据链路 … </script> 请注意几个地方： 1、 Import 语 句的 不同 。在 ADO.NET Managed Provider 里面 ，我 们 Import 的 是 System.Data.ADO；而这里需要 System.Data.SQL； 2、 连接对象也不同。在 ADO.NET Managed Provider 中，所有的对象以 ADO 打头；而这里 需要以 SQL 打头
下面的表格归纳了这些不同 ADO. NET Managed Provider ADO. NET SQL Managed Provider Connection对象| ADOConnection SOLConnection Command对象| ADODatasetCommand SQLDataset Command 对象 DataReader ADODataReader SQLDataReader 连接数据库例子 String cOnnectionsTring= String Provider= SQLOLEDB. 1 server=localhost; uid=sa pwd= database Initial SQLConnection Catalog=pub SQLConnection(cOnnectionsTring) con. Open ADOConnection con new ADOConnection(sConnectionStr 执行SQL语句例 ADOCommand cmd= new SQLCommand ADOCommand("SELECT* SQLCommand(("SELECT FROM FROM Authors" con) Authors", con) ADODataReader d new SQLDataReader cmd. Execute(out dr) cmd. Execute(out dr) 使用存储过程例| ADOCommand cmd new SQLCommand cmd new ADOCommand SQLCommand( "sp Get Author BylD ("spGet Author BylD", con); cmd Command Type Command Type StoredProcedure; Command Type Stored Procedure ADOParameter prmID= new SQLParameter prmID ADOParameter("AuthID SQLParameter("@AuthID ADODatalype. VarChar, 11) SQLData lype VarChar, 11) prmID. Value="111-11-1111 prmID. Value=11l-11-111 cmd. Select Command Parameters. Add(pr cmd SelectCommand Parameters. mID SQLDataReader dr ADODataReader dr. cmd. Execute(out dr 33231一个完整的例子 <%a Page Language=vb"%> %o@ Import Namespace="System. Data"%> %( Import Namespace ="System. Data, SQL"%> Sub Page Load(By Val Sender As Object, By Val e As EventArgs) On error resume next Dim cn As SQLConnection
下面的表格归纳了这些不同： ADO.NET Managed Provider ADO.NET SQL Managed Provider 需要引入的 Namespace System.Data.ADO System.Data.SQL Connection 对象 ADOConnection SQLConnection Command 对象 ADODatasetCommand SQLDatasetCommand Dataset 对象 Dataset Dataset DataReader ADODataReader SQLDataReader 连接数据库例子 String sConnectionString = "Provider= SQLOLEDB.1; Data Source=localhost; uid=sa; pwd=; Initial Catalog=pubs"; ADOConnection con = new ADOConnection(sConnectionStr ing); con.Open(); String sConnectionString = "server=localhost;uid=sa;pwd=;database =pubs"; SQLConnection con = new SQLConnection(sConnectionString); con.Open(); 执行SQL语句例 子 ADOCommand cmd = new ADOCommand("SELECT * FROM Authors", con); ADODataReader dr = new ADODataReader(); cmd.Execute(out dr); SQLCommand cmd = new SQLCommand(("SELECT * FROM Authors", con); SQLDataReader dr = new SQLDataReader(); cmd.Execute(out dr); 使用存储过程例 子 ADOCommand cmd = new ADOCommand ("spGetAuthorByID", con); cmd.CommandType = CommandType.StoredProcedure; ADOParameter prmID = new ADOParameter("AuthID", ADODataType.VarChar, 11); prmID.Value = "111-11-1111"; cmd.SelectCommand.Parameters. Add(prmID); ADODataReader dr; cmd.Execute (out dr); SQLCommand cmd = new SQLCommand("spGetAuthorByID", con); cmd.CommandType = CommandType.StoredProcedure; SQLParameter prmID = new SQLParameter("@AuthID", SQLDataType.VarChar,11); prmID.Value = "111-11-1111" cmd.SelectCommand.Parameters.Add(pr mID); SQLDataReader dr; cmd.Execute(out dr); 3.3.2.3.1 一个完整的例子 <%@ Page Language="vb" %> <%@ Import Namespace = "System.Data" %> <%@ Import Namespace = "System.Data.SQL" %> <html> <head> <script runat=server> Sub Page_Load(ByVal Sender As Object, ByVal e As EventArgs) 'On Error Resume Next Dim cn As SQLConnection
cn= New SQLConnection("server=localhost; uid=sa, pwd=, database=pubs:, " If cn. State= 1 Then IblReturn Code Text ="The Connection State is: " cn State " Connection Else IblReturn Code. Text = "The Connection State is " cn State # Connection Failed End If End Sub head> <asp: Label id="lblReturn Code"Runat=server / 3324三种方法的对比 般来说,这三种存取数据库的方法中, SQL Managed Provider效率最高,其次是 ADO NET Managed provider+ OLEDB,最差的是 ADO. NET Managed Provider+ODBC。下面是在普通 PI微机上,对于 Access2000和 MS SQL Server2000上的测试结果 数据库连接类型 页面显示所需时间(秒) [ ADO.NET Managed Provider+ODBC.831195 LADO.NET Managed Provider+OLEDB. 100144 SQL Managed Provider 0.060086 口ADO.NET Provider+ODB 0.6 B ADO. NET Provider+OLE 口 SQL Managed ovel 从图上可以看出, SQL Managed Provider要优于ADO. NET Managed Provider,而从ODBC 和 OLEDB的对比来看, OLEDB要优于ODBC。 下面列示了测试用的源程序,仅供参考
cn = New SQLConnection("server=localhost;uid=sa;pwd=;database=pubs;") cn.Open() If cn.State = 1 Then lblReturnCode.Text = "The Connection State is: " & cn.State & " - Connection Succeeded" Else lblReturnCode.Text = "The Connection State is: " & cn.State & " - Connection Failed" End If End Sub </script> </head> <body> <asp:Label id="lblReturnCode" Runat=server /> </body> </html> 3.3.2.4 三种方法的对比 一般来说，这三种存取数据库的方法中，SQL Managed Provider 效率最高，其次是 ADO.NET Managed Provider+OLEDB，最差的是 ADO.NET Managed Provider+ODBC。下面是在普通 PIII 微机上，对于 Access 2000 和 MS SQL Server 2000 上的测试结果： 数据库连接类型 页面显示所需时间(秒) ADO.NET Managed Provider+ODBC 0.831195 ADO.NET Managed Provider+OLEDB 0.100144 SQL Managed Provider 0.060086 0 0.2 0.4 0.6 0.8 1 ADO.NET Managed Provider+ODB C ADO.NET Managed Provider+OLE DB SQL Managed Provider 从图上可以看出，SQL Managed Provider 要优于 ADO.NET Managed Provider，而从 ODBC 和 OLEDB 的对比来看，OLEDB 要优于 ODBC。 下面列示了测试用的源程序，仅供参考
3325测试程序 (122303.aspx) <%Q Page EnableSession State="False"%> %( Import Namespace="System. Data"%> % Import Namespace="System. Data ADO"%> <% Import Namespace="System. Data. SQL runa Sub Refresh( By Val sender As System Object, By Val e As System. EventArgs) Dim d1 d2 As Date Time Dim strConn if page Is valid the Dim iChoice As Integer=CInt( Choices. Selectedltem Value) select case iChoice case 1 ADOBindData( strConn) case str Conn="Provider=SQLOLEDB. 1; Data Source=(local); uid=sa pwd=, Initial Catalog=pub ADOBind Data(str Conn) server=localhost uid=sa pwd=; database=northwind SQLBind Data(str Conn) Case else d select result. text="用时(Tcks):"&d2. Ticks-d1Ticks End Sub Sub adoBind data( strConn 设置连接串 创建对象 ADOConnection
3.3.2.5 测试程序 (122303.aspx) <%@ Page EnableSessionState="False" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.ADO" %> <%@ Import Namespace="System.Data.SQL" %> <script language="VB" runat="server"> Sub Refresh(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim d1,d2 As DateTime Dim strConn if Page.IsValid then d1=Now() Dim iChoice As Integer=CInt(Choices.SelectedItem.Value) select case iChoice case 1 strConn="DSN=pubs;" ADOBindData(strConn) case 2 strConn="Provider=SQLOLEDB.1;Data Source=(local);uid=sa;pwd=;Initial Catalog=pubs" ADOBindData(strConn) case 3 strConn="server=localhost;uid=sa;pwd=;Database=pubs" '"server=localhost;uid=sa;pwd=;database=northwind;" SQLBindData(strConn) Case Else end select d2=Now() result.Text = "用时(Ticks)："&d2.Ticks-d1.Ticks end if End Sub Sub ADOBindData(strConn) '设置连接串... '创建对象 ADOConnection