概述:
本文在微软站点资源的基础上加工整理而成,意在介绍在你的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