為了在連接到Microsoft SQL Server 7.0或更高版本時(shí)獲得最佳性能，請(qǐng)使用SQL Server .NET數(shù)據(jù)提供程序。SQL Server .NET數(shù)據(jù)提供程序的設(shè)計(jì)目的就在于不通過(guò)任何附加技術(shù)層就可以直接訪問(wèn)SQL Server。
'Visual Basic Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection) Dim ds As DataSet = New DataSet() da.Fill(ds, "Customers")
//C# SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection); DataSet ds = new DataSet(); da.Fill(ds, "Customers"); 來(lái)自Customers表的數(shù)據(jù)放在名為“Customers”的DataTable中。來(lái)自O(shè)rders表的數(shù)據(jù)放在名為“Customers1”的DataTable中。
'Visual Basic Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection) da.TableMappings.Add("Customers1", "Orders") Dim ds As DataSet = New DataSet() da.Fill(ds, "Customers")
//C# SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection); da.TableMappings.Add("Customers1", "Orders"); DataSet ds = new DataSet(); da.Fill(ds, "Customers"); 使用DataReader
對(duì)于重復(fù)作用于數(shù)據(jù)源的參數(shù)化命令，Command.Prepare方法能提高性能。Prepare指示數(shù)據(jù)源為多次調(diào)用優(yōu)化指定的命令。要想有效利用Prepare，需要徹底理解數(shù)據(jù)源是如何響應(yīng)Prepare調(diào)用的。對(duì)于一些數(shù)據(jù)源（例如SQL Server 2000），命令是隱式優(yōu)化的，不必調(diào)用Prepare。對(duì)于其他（例如SQL Server 7.0）數(shù)據(jù)源，Prepare會(huì)比較有效。 顯式指定架構(gòu)和元數(shù)據(jù)
'Visual Basic Public Sub RunSqlTransaction(da As SqlDataAdapter, myConnection As SqlConnection, ds As DataSet) myConnection.Open() Dim myTrans As SqlTransaction = myConnection.BeginTransaction() myCommand.Transaction = myTrans
Try da.Update(ds) myTrans.Commit() Console.WriteLine("Update sUCcessful.") Catch e As Exception Try myTrans.Rollback() Catch ex As SqlException If Not myTrans.Connection Is Nothing Then Console.WriteLine("An exception of type " & ex.GetType().ToString() & " was encountered while attempting to roll back the transaction.") End If End Try
Console.WriteLine("An exception of type " & e.GetType().ToString() & " was encountered.") Console.WriteLine("Update failed.") End Try myConnection.Close() End Sub
//C# public void RunSqlTransaction(SqlDataAdapter da, SqlConnection myConnection, DataSet ds) { myConnection.Open(); SqlTransaction myTrans = myConnection.BeginTransaction(); myCommand.Transaction = myTrans;
try { da.Update(ds); myCommand.Transaction.Commit(); Console.WriteLine("Update successful."); } catch(Exception e) { try { myTrans.Rollback(); } catch (SqlException ex) { if (myTrans.Connection != null) { Console.WriteLine("An exception of type " + ex.GetType() +" was encountered while attempting to roll back the transaction."); } }
假如正從SQL Server 2000 FOR XML返回查詢結(jié)果，可以讓用于SQL Server的.NET框架數(shù)據(jù)提供程序使用SqlCommand.ExecuteXmlReader方法直接創(chuàng)建一個(gè)XmlReader。
SQLXML托管類(lèi)
.NET框架中有一些類(lèi)，公開(kāi)用于SQL Server 2000的XML的功能。這些類(lèi)可在Microsoft.Data.SqlXml命名空間中找到，它們添加了執(zhí)行XPath查詢和XML模板文件以及將XSLT轉(zhuǎn)換應(yīng)用到數(shù)據(jù)的能力。
SQLXML托管類(lèi)包含在用于Microsoft SQL Server 2000的XML (SQLXML 2.0)發(fā)行版中，可通過(guò)鏈接X(jué)ML for Microsoft SQL Server 2000 Web Release 2 (SQLXML 2.0) 更多有用的技巧
