ADO.NET最佳实践(上)

类别:数据库 点击:0 评论:0 推荐:

    概述:

    本文在微软站点资源的基础上加工整理而成,意在介绍在你的ADO.NET应用程序中执行和完成性能优化、稳定性和功能性方面提供最佳的解决方案;同时也包含在ADO.NET中运用已有的数据对象进行开发的最佳实践和帮助你怎样设计ADO.NET应用程序提供建议。

    本文包含以下内容:

    1..NET框架中的data providers;

    2.对照DataSet和DataReader,分别介绍他们的最佳用途;

    3.如何使用DataSet、Commands和Connections;

    4.结合XML;

    5.如果你是ADO程序员,也不妨看看ADO.NET与ADO的区别和联系;

    6.结合一些FAQ,更深一步讨论ADO.NET观点和使用技巧。

    介绍:

    A..NET框架中的data providers

        Data providers在应用程序和数据库之间扮演一个桥梁的角色,它使得你可以从一个数据库返回查询结果、执行命令以及对数据集的更新等。

    B.几种data provider的介绍

        下面表格中数据表明各种data provider以及最佳适用数据库对象

提供者

描述

SQL Server.NET Data Provider

在.NET框架中使用System.Data.SqlClient命名空间;

建议在中间层应用程序中使用SQL Server7.0或以后版本;

建议在独立的应用程序中使用MSDE或SQL Server7.0或更高版本;

SQL Server6.5或更早版本,必须使用OLE DB.NET Data Provider中的OLE DB Provider For SQL Server。

OLE DB.NET Data Provider

在.NET框架中使用System.Data.OleDb命名空间;

建议在中间层应用程序中使用SQL Server6.5或以前版本,或者任何在.NET框架SDK中指出的支持OLE DB接口清单的OLE DB Provider,OLE DB接口清单将在后面列出;

建议在独立的应用程序中使用Access,中间层应用程序不建议使用Access;

不再支持为ODBC的OLE DB Provider,要访问ODBC,使用ODBC.NET Data Provider。

ODBC.NET Data Provider

在.NET框架中使用System.Data.Odbc命名空间;

提供对使用ODBC驱动连接的数据库的访问;

.NET Data Provider For Oracle

在.NET框架中使用System.Data.OracleClient命名空间;

提供对Oracle数据库的访问。

Custom.NET Data Provider

提供一套接口,让你可以自定义一个Data Provider;

SQLXML Managed Classes

包含SQLXML Managed Classes的最新版SQLXML3.0,使得你可以访问SQL Server2000或以后版本的XML功能性扩展,比如执行XML模板文件、执行XPath查询和使用Updategrams或Diffgrams更新数据等;在SQLXML 3.0中存储过程和XML模板将会通过SOAP作为一种WEB服务。

        表格中提到的OLE DB接口清单,在这里把它列出

OLE DB 对象

接口

OLE DB Services

IdataInitilize

DataSource

IDBInitialize
IDBCreateSession
IDBProperties
IPersist
IDBInfo*

Session

ISessionProperties
IOpenRowset
IDBSchemaRowset*
ITransactionLocal*
IDBCreateCommand*

Command

IcommandText
ICommandProperties
ICommandWithParameters*
IAccessor (only required if ICommandWithParameters is supported)
ICommandPrepare*

MultipleResults

ImultipleResults

RowSet

Irowset
IAccessor
IColumnsInfo
IColumnsRowset*
IRowsetInfo (only required if DBTYPE_HCHAPTER is supported)

Row

IRow*

Error

IerrorInfo
IErrorRecords
ISQLErrorInfo*

    C.连接SQL Server7.0或更高版本

        使用SQL Server.NET Data Provider连接SQL Server7.0或更高版本是最好的方式,在于它建立与SQL Server的直接连接而中间不需要任何的技术层衔接。如下图一展示了各种访问SQL Server7.0或更高版本的技术比较:

图一(连接访问SQL Server7.0或更高版本的各种技术比较)

        以下例子演示怎样创建和打开一个到SQL Server7.0或更高版本数据库的连接:

‘Visual Basic

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _                                                   "Initial Catalog=northwind")

nwindConn.Open()

‘C#

SqlConnection nwindConn = new SqlConnection("Data Source=localhost; Integrated Security=SSPI;" +

"Initial Catalog=northwind");

nwindConn.Open();

    D.连接ODBC数据源

        ODBC.NET Data Provider,使用System.Data.Odbc命名空间,拥有为SQL Server和OLE DB的.NET Data Porvider一样的结构,使用ODBC前缀(比如OdbcConnetion)和标准的ODBC连接字符。下面例子演示怎样创建和打开一个到ODBC数据源的连接:

‘Visual Basic

Dim nwindConn As OdbcConnection = New OdbcConnection("Driver={SQL Server};Server=localhost;" & _                                                     "Trusted_Connection=yes;Database=northwind")

nwindConn.Open()

‘C#

OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;" +

"Trusted_Connection=yes;Database=northwind");

nwindConn.Open();

    E.使用DataReaders、DataSets、DataAdapters和DataViews

        ADO.NET使用DataSet和DataReader对象读取数据并存储。DataSet就好比是数据库的直系亲属,拥有数据库的所有表、顺序和数据库的约束(比如表间关系)。DataReader则从数据库读取快速的、只进的的和只读的数据流。使用DataSet,你将会经常使用DataAdapter(或者CommandBuilder)与你的数据库打交道,同时,你也许会使用DataView去排序和过滤数据,DataSet还允许你可以创建一个继承于DataSet的子对象来表现数据中的表、行和列。下面图二显示DataSet对象模型:

图二(DataSet对象模型)

下面将要介绍在什么时候使用DataSet或DataReader最恰当,同时也将说明如何使用DataAdapter(包括CommandBuilder)和DataView最优化对数据的访问。

    F.DataSet和DataReader的比较

        在设计你的应用程序时决定究竟使用DataSet还是使用DataReader,主要看在你的应用程序中要实现的功能性级别。

        使用DataSet可以在你的应用程序中做以下事情:

        I.在多个离散的结果表之间导航;

            一个DataSet可以包含多个结果表,这些结果表是不连续的。你可以分开处理这些表,也可以把这些表当作父子关系进行处理。

        II.操作多个数据源(比如从XML文件和电子数据表等不只一个数据库得到的混合数据);

        下面的例子演示从SQL Server2000的Northwind数据库读取一个customers表的清单和从Access2000的Northwind数据库读取一个orders表的清单,然后使用DataRelation在两个表之间建立一个对应关系:

‘Visual Basic

Dim custConn As SqlConnection= New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _

"Initial Catalog=northwind;")

Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers", custConn)

Dim orderConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _                                                       "Data Source=c:\Program Files\Microsoft Office\" & _                                                       "Office\Samples\northwind.mdb;")

Dim orderDA As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM Orders", orderConn)

custConn.Open()

orderConn.Open()

Dim custDS As DataSet = New DataSet()

custDA.Fill(custDS, "Customers")

orderDA.Fill(custDS, "Orders")

custConn.Close()

orderConn.Close()

Dim custOrderRel As DataRelation = custDS.Relations.Add("CustOrders", _                                     custDS.Tables("Customers").Columns("CustomerID"), _                                     custDS.Tables("Orders").Columns("CustomerID"))

Dim pRow, cRow As DataRow

For Each pRow In custDS.Tables("Customers").Rows

  Console.WriteLine(pRow("CustomerID").ToString())

  For Each cRow In pRow.GetChildRows(custOrderRel)

    Console.WriteLine(vbTab & cRow("OrderID").ToString())

  Next

Next

‘C#

SqlConnection custConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;");

SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", custConn);

OleDbConnection orderConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +                                                "Data Source=c:\\Program Files\\Microsoft Office\\Office\\Samples\\northwind.mdb;");

OleDbDataAdapter orderDA = new OleDbDataAdapter("SELECT * FROM Orders", orderConn);

custConn.Open();

orderConn.Open();

DataSet custDS = new DataSet();

custDA.Fill(custDS, "Customers");

orderDA.Fill(custDS, "Orders");

custConn.Close();

orderConn.Close();

DataRelation custOrderRel = custDS.Relations.Add("CustOrders",                              custDS.Tables["Customers"].Columns["CustomerID"],                              custDS.Tables["Orders"].Columns["CustomerID"]);

foreach (DataRow pRow in custDS.Tables["Customers"].Rows)

{

  Console.WriteLine(pRow["CustomerID"]);

   foreach (DataRow cRow in pRow.GetChildRows(custOrderRel))

    Console.WriteLine("\t" + cRow["OrderID"]);

}

        III.层中交换数据或者使用一个XML WEB服务,与DataReader不一样的是DataSet可以被传递给一个远程的客户端;

            下面的例子演示如何创建一个XML WEB服务,其中使用GetCustomers取数据库中customers表数据,使用UpdateCustomers更新数据库中数据:

1.     ‘Visual Basic

2.     <% @ WebService Language = "VB" Class = "Sample" %>

3.     Imports System

4.     Imports System.Data

5.     Imports System.Data.SqlClient

6.     Imports System.Web.Services

7.     <WebService(Namespace:="http://microsoft.com/webservices/")> _

8.     Public Class Sample

9.       Public nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

10.    <WebMethod( Description := "Returns Northwind Customers", EnableSession := False )> _

11.    Public Function GetCustomers() As DataSet

12.      Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn)

13.      Dim custDS As DataSet = New DataSet()

14.      custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey

15.      custDA.Fill(custDS, "Customers")

16.      GetCustomers = custDS

17.    End Function

18.    <WebMethod( Description := "Updates Northwind Customers", EnableSession := False )> _

19.    Public Function UpdateCustomers(custDS As DataSet) As DataSet

20.      Dim custDA As SqlDataAdapter = New SqlDataAdapter()

21.      custDA.InsertCommand = New SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " & _                                          "Values(@CustomerID, @CompanyName)", nwindConn)

22.      custDA.InsertCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")

23.      custDA.InsertCommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName")

24.      custDA.UpdateCommand = New SqlCommand("UPDATE Customers Set CustomerID = @CustomerID, " & _

25.  "CompanyName = @CompanyName WHERE CustomerID = @OldCustomerID", nwindConn)

26.      custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")

27.      custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName")

28.      Dim myParm As SqlParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID")

29.      myParm.SourceVersion = DataRowVersion.Original

30.      custDA.DeleteCommand = New SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", nwindConn)

31.      myParm = custDA.DeleteCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")

32.      myParm.SourceVersion = DataRowVersion.Original

33.      custDA.Update(custDS, "Customers")

34.      UpdateCustomers = custDS

35.    End Function

36.  End Class

37.   

38.  ‘C#

39.  <% @ WebService Language = "C#" Class = "Sample" %>

40.  using System;

41.  using System.Data;

42.  using System.Data.SqlClient;

43.  using System.Web.Services;

44.  [WebService(Namespace="http://microsoft.com/webservices/")]

45.  public class Sample

46.  {

47.    public SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

48.    [WebMethod( Description = "Returns Northwind Customers", EnableSession = false )]

49.    public DataSet GetCustomers()

50.    {

51.      SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn);

52.      DataSet custDS = new DataSet();

53.      custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;

54.      custDA.Fill(custDS, "Customers");

55.      return custDS;

56.    }

57.    [WebMethod( Description = "Updates Northwind Customers", EnableSession = false )]

58.    public DataSet UpdateCustomers(DataSet custDS)

59.    {

60.      SqlDataAdapter custDA = new SqlDataAdapter();

61.      custDA.InsertCommand = new SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " +                                          "Values(@CustomerID, @CompanyName)", nwindConn);

62.      custDA.InsertCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");

63.      custDA.InsertCommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName");

64.      custDA.UpdateCommand = new SqlCommand("UPDATE Customers Set CustomerID = @CustomerID, " + "CompanyName = @CompanyName WHERE CustomerID = @OldCustomerID", nwindConn);

65.      custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");

66.      custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName");

67.      SqlParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID");

68.      myParm.SourceVersion = DataRowVersion.Original;

69.      custDA.DeleteCommand = new SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", nwindConn);

70.      myParm = custDA.DeleteCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");

71.      myParm.SourceVersion = DataRowVersion.Original;

72.      custDA.Update(custDS, "Customers");

73.      return custDS;

74.    }

}

        IV.数据的再使用(比如排序、搜索或过滤数据);

        V.执行每行的大容量数据处理,处理DataReader挂起的连接服务已不再需要、影响性能的每一行;

        VI.使用诸如XSLT转换或者XPath查询等XML操作的多重数据。

            下面的例子介绍如何使用XmlDataDocument同步DataSet数据和如何使用XSLT样式文件在HTML文件中包含DataSet数据,首先是XSLT样式文件:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:template match="CustomerOrders">

  <HTML>

  <STYLE>

  BODY {font-family:verdana;font-size:9pt}

  TD   {font-size:8pt}

  </STYLE>

    <BODY>

    <TABLE BORDER="1">

      <xsl:apply-templates select="Customers"/>

    </TABLE>

    </BODY>

  </HTML>

</xsl:template>

<xsl:template match="Customers">

    <TR><TD>

      <xsl:value-of select="ContactName"/>, <xsl:value-of select="Phone"/><BR/>

    </TD></TR>

      <xsl:apply-templates select="Orders"/>

</xsl:template>

<xsl:template match="Orders">

  <TABLE BORDER="1">

    <TR><TD valign="top"><B>Order:</B></TD><TD valign="top"><xsl:value-of select="OrderID"/></TD></TR>

    <TR><TD valign="top"><B>Date:</B></TD><TD valign="top"><xsl:value-of select="OrderDate"/></TD></TR>

    <TR><TD valign="top"><B>Ship To:</B></TD>

        <TD valign="top"><xsl:value-of select="ShipName"/><BR/>

        <xsl:value-of select="ShipAddress"/><BR/>

        <xsl:value-of select="ShipCity"/>, <xsl:value-of select="ShipRegion"/>  <xsl:value-of select="ShipPostalCode"/><BR/>

        <xsl:value-of select="ShipCountry"/></TD></TR>

  </TABLE>

</xsl:template>

</xsl:stylesheet>

            接着下面的代码演示如何填充DataSet的数据和运用XSLT样式:

‘Visual Basic

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Xml

Imports System.Xml.Xsl

Public Class Sample

  Public Shared Sub Main()

    Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=northwind;Integrated Security=SSPI")

    nwindConn.Open()

    Dim myDataSet As DataSet = New DataSet("CustomerOrders")

    Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers", nwindConn)

    custDA.Fill(myDataSet, "Customers")

    Dim ordersDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Orders", nwindConn)

    ordersDA.Fill(myDataSet, "Orders")

    nwindConn.Close()

    myDataSet.Relations.Add("CustOrders",_                            myDataSet.Tables("Customers").Columns("CustomerID"),_                            myDataSet.Tables("Orders").Columns("CustomerID")).Nested = true

    Dim xmlDoc As XmlDataDocument = New XmlDataDocument(myDataSet)

    Dim xslTran As XslTransform = New XslTransform

    xslTran.Load("transform.xsl")

    Dim writer As XmlTextWriter = New XmlTextWriter("xslt_output.html", System.Text.Encoding.UTF8)

    xslTran.Transform(xmlDoc, Nothing, writer)

    writer.Close()

  End Sub

End Class

‘C#

using System;

using System.Data;

using System.Data.SqlClient;

using System.Xml;

using System.Xml.Xsl;

public class Sample

{

  public static void Main()

  {

    SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Initial Catalog=northwind;Integrated Security=SSPI;");

    nwindConn.Open();

    DataSet custDS = new DataSet("CustomerDataSet");

    SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", nwindConn);

    custDA.Fill(custDS, "Customers");

    SqlDataAdapter ordersDA = new SqlDataAdapter("SELECT * FROM Orders", nwindConn);

    ordersDA.Fill(custDS, "Orders");

    nwindConn.Close();

    custDS.Relations.Add("CustOrders",

                         custDS.Tables["Customers"].Columns["CustomerID"],

                         custDS.Tables["Orders"].Columns["CustomerID"]).Nested = true;

    XmlDataDocument xmlDoc = new XmlDataDocument(custDS);

    XslTransform xslTran = new XslTransform();

    xslTran.Load("transform.xsl");

    XmlTextWriter writer = new XmlTextWriter("xslt_output.html", System.Text.Encoding.UTF8);

    xslTran.Transform(xmlDoc, null, writer);

    writer.Close();

  }

}

ADO.NET最佳实践(中)

http://www.csdn.net/Develop/read_article.asp?id=22663

本文地址:http://com.8s8s.com/it/it21062.htm