概述：
本文在微軟站點資源的基礎上加工整理而成，意在介紹在你的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 amples/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/ amples//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
