1.
数据库应用编程方法概述和组件架构
表4-1 不同编程方法的简单介绍
编程方法
|
简单介绍
|
WDAC
|
—主要包括ADO、ODBC和OLEDB
—无须安装，基于Windows平台，不随着SQL Server变化而变化
—对于SQL Server的一些新功能支持有所欠缺
—包含70多个DLL
|
SNAC
|
—只有一个DLL文件，同时包含ODBC和OLEDB功能
—需要安装，会随着SQL Server版本升级而推出新的版本
|
ADO.NET
|
— net
framework平台上对数据库编程访问
—借鉴了ADO的概念，但具体实现跟ADO是不一样的
|
a.
WDAC编程
WDAC（Windows Data Access
Components）其实是一个统称。之前又称为MDAC（Microsoft
Data Access Components）。发布在Windows平台上有大约70个DLL，这70多个DLL大致包括以下不同的组件，括号里面为核心DLL。
ADO (%CommonProgramFiles%\system\ado\msado15.dll)
ODBC (%SystemRoot%\system32下的odbc32.dll和sqlsrv32.dll)
OLEDB (%CommonProgramFiles%\system\Ole DB下的oledb32.dll，sqloledb.dll，msdasql.dll)
其他的DLL都是辅助DLL或属于其他小的组件。图4-1显示了WDAC的体系架构。
i. ADO
在图4-1中，ADO（主要是由msado15.dll实现）处于WDAC的最高层。应用可以用VB或VBScript，当然也可以用C++语言，虽然会比较麻烦一点。下面是一段ADO VBScript脚本，首先建立一个对数据库的连接，其次创建测试表并用命令插入数据，随后查询数据并对数据进行修改。
set conn = CreateObject("ADODB.CONNECTION")
set rs = CreateObject("ADODB.Recordset")
set cmd = CreateObject("ADODB.Command")
conn.Open "Provider=sqloledb;Server=XXX\DENALI;Trusted_Connection=Yes;"
cmd.ActiveConnection=conn
cmd.commandText = "IF EXISTS(SELECT name FROM sysobjects WHERE name = N'TESTTABLE' AND
xtype='U') DROP TABLE TESTTABLE"
cmd.execute
cmd.commandText = "CREATE TABLE TESTTABLE (ID int, NAME nvarchar(50), VACATION float)"
cmd.execute
cmd.commandText = "INSERT INTO TESTTABLE VALUES (1, 'John', 15)"
cmd.execute
rs.open "SELECT * FROM TESTTABLE WHERE ID = 1 ", conn, 1, 3
Do While Not rs.EOF
Msgbox rs.Fields("ID") & " " & rs.Fields("NAME") & " " & rs.Fields("VACATION")
rs.Fields("VACATION") = 14
rs.update
rs.movenext
Loop
rs.close
conn.close
在连接字符串中，我们也可以使用MSDASQL这个Provider，那么这个Provider会调用ODBC驱动对数据库进行连接。
conn.Open "Provider=msdasql;Driver={SQL Server};Server=XXX\DENALI;Trusted_Connection=Yes;"
ii. OLEDB Core Services
WDAC的下面一层是OLEDB Core Services （主要是由oledb32.dll实现）。这一层的主要功能就是做一些基本的规范检查，还有对连接池的实现也是在这一层完成的，真正的和数据库沟通的任务则是由更下面一层的OLEDB
Provider来完成的
iii. OLEDB Providers
不同的Provider是由不同的数据库厂商分别实现的，我们可以用Root
Enumerator来查看在我们的服务器上到底有多少个不同的Provider。
其中有一个工具 Microsoft OLE DB
RowsetViewer，运行该工具，并选择File->Root Enumerator。
我们就可以看到在这台服务器上的不同OLEDB Providers，如图4-2所示。
其中WDAC自带的Provider有SQLOLEDB（主要由sqloledb.dll实现）和MSDASQL （主要由msdasql.dll实现）。具体用哪个Provider是在连接字符串里指定的。SQLOLEDB可以直接对SQL Server访问进行编程实现。由于早些年很多数据库只提供ODBC驱动而没有相应的OLEDB Providers，因此微软实现了MSDASQL这个Provider，通过这个Provider，应用程序可以使用OLEDB方式以MSDASQL Provider为桥梁，再调用相应的ODBC 驱动来实现对数据库的OLEDB编程访问。
下面的程序是一个OLEDB程序，使用SQLOLEDB这个Provider实现跟前面的ADO代码相同的功能。我们可以看到OLEDB程序比ADO程序复杂很多，这是由于ADO组件对编程方法进行了封装，所以ADO代码相应比较简单。但从性能上讲，OLEDB程序是优于ADO程序的，因为少了中间的封装环节。
#include "stdafx.h"
#include <oledb.h>
#include <msdaguid.h>
#include <sqloledb.h>
#include <windows.h>
#include <stdio.h>
#include <string>
#define NUMLEN(p1) (sizeof(p1) / sizeof (p1[0]))
#define ROUNDUP(size, amount) (((DWORD) (size) + ((amount) - 1)) & ~((amount) - 1))
#define COLUMN_ALIGNVAL 8
int _tmain(int argc, _TCHAR* argv[])
{
using namespace std;
IDBInitialize *pIDBInitialize = NULL;
IDBCreateSession* pCreateSession = NULL;
IDBCreateCommand* pDBCreateCommand = NULL;
IUnknown *pUnkSession = NULL;
IUnknown *pUnkCommand = NULL;
IUnknown *pUnkRowset = NULL;
IRowset *pRowset = NULL;
IRowsetChange *pRowsetChange = NULL;
ICommandText *pCommandText = NULL;
ICommandProperties *pCommandProperties = NULL;
IDBProperties *pIDBProperties = NULL;
IColumnsInfo *pColumnsInfo = NULL;
IAccessor *pAccessor = NULL;
HACCESSOR hAccessor;
ITableCreation *pTableCreation = NULL;
ITableDefinition *pTableDefinition = NULL;
DBCOLUMNINFO *pDBColumnInfo = NULL;
DBBINDING *pBindings = NULL;
DBPROP InitProperties[4];
DBPROPSET rgInitPropSet[1];
int i;
HRESULT hr;
// 初始化COM库
CoInitialize(NULL);
CoCreateInstance(CLSID_SQLOLEDB, NULL, CLSCTX_INPROC_SERVER, IID_IDBInitialize, (void **)&pIDBInitialize);
// 初始化属性设定
for (i=0; i<4; i++)
{
VariantInit(&InitProperties[i].vValue);
InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[i].colid = DB_NULLID;
}
// 设定属性：在连接时不进行提示
InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT;
InitProperties[0].vValue.vt = VT_I2;
InitProperties[0].vValue.iVal = DBPROMPT_NOPROMPT;
// 设定属性：指定用户名
InitProperties[1].dwPropertyID = DBPROP_AUTH_USERID;
InitProperties[1].vValue.vt = VT_BSTR;
InitProperties[1].vValue.bstrVal = SysAllocString((LPOLESTR)L"sa");
// 设定属性：指定密码
InitProperties[2].dwPropertyID = DBPROP_AUTH_PASSWORD;
InitProperties[2].vValue.vt = VT_BSTR;
InitProperties[2].vValue.bstrVal = SysAllocString((LPOLESTR)L"xxxxxx");
// 设定属性：指定data source
InitProperties[3].dwPropertyID = DBPROP_INIT_DATASOURCE;
InitProperties[3].vValue.vt = VT_BSTR;
InitProperties[3].vValue.bstrVal = SysAllocString((LPOLESTR)L"XX\\DENALI");
rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
rgInitPropSet[0].cProperties = 4;
rgInitPropSet[0].rgProperties = InitProperties;
// 设定属性
pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
hr = pIDBProperties->SetProperties(1, rgInitPropSet);
pIDBProperties->Release();
hr = pIDBInitialize->Initialize();
if (FAILED(hr))
{
return -1;
}
// 创建session对象
pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void **) &pCreateSession);
hr = pCreateSession->CreateSession(NULL, IID_IOpenRowset, &pUnkSession);
// 获得ITableDefinition接口
pUnkSession->QueryInterface(IID_ITableDefinition, (void **) &pTableDefinition);
DBCOLUMNDESC rgColumnDescs[3];
DBID table;
table.eKind = DBKIND_NAME;
table.uName.pwszName = L"testTable";
memset(rgColumnDescs, 0, 3*sizeof(DBCOLUMNDESC));
rgColumnDescs[0].wType = DBTYPE_I4;
rgColumnDescs[0].ulColumnSize = 4;
rgColumnDescs[0].dbcid.eKind = DBKIND_NAME;
rgColumnDescs[0].dbcid.uName.pwszName = L"ID";
rgColumnDescs[1].wType = DBTYPE_WSTR;
rgColumnDescs[1].ulColumnSize = 40;
rgColumnDescs[1].dbcid.eKind = DBKIND_NAME;
rgColumnDescs[1].dbcid.uName.pwszName = L"name";
rgColumnDescs[2].wType = DBTYPE_R8;
rgColumnDescs[2].ulColumnSize = 8;
rgColumnDescs[2].dbcid.eKind = DBKIND_NAME;
rgColumnDescs[2].dbcid.uName.pwszName = L"VACATION";
// 如果已经有表的话，删除该表，不管结果如何，不对返回值做检查
hr = pTableDefinition->DropTable(&table);
// 创建表
hr = pTableDefinition->CreateTable(
NULL,
&table,
3,
rgColumnDescs,
IID_IUnknown,
0,
NULL,
NULL,
NULL
);
// 插入数据
hr = pUnkSession->QueryInterface(IID_IDBCreateCommand, (void **)&pDBCreateCommand);
hr = pDBCreateCommand->CreateCommand(NULL, IID_ICommand, &pUnkCommand);
hr = pUnkCommand->QueryInterface(IID_ICommandText, (void **)&pCommandText);
hr = pCommandText->SetCommandText(DBGUID_DEFAULT, (LPOLESTR)L"INSERT INTO testTable values (1, 'John', 15)");
hr = pCommandText->Execute(
NULL,
IID_NULL,
NULL,
NULL,
NULL
);
DBPROP CmdProperties[2];
DBPROPSET rgCmdPropSet[1];
CmdProperties[0].dwPropertyID = DBPROP_IRowsetChange;
CmdProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
CmdProperties[0].dwStatus = DBPROPSTATUS_OK;
CmdProperties[0].colid = DB_NULLID;
CmdProperties[0].vValue.vt = VT_BOOL;
CmdProperties[0].vValue.boolVal = VARIANT_TRUE;
CmdProperties[1].dwPropertyID = DBPROP_UPDATABILITY;
CmdProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;
CmdProperties[1].dwStatus = DBPROPSTATUS_OK;
CmdProperties[1].colid = DB_NULLID;
CmdProperties[1].vValue.vt = VT_I4;
CmdProperties[1].vValue.lVal = DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE ;
rgCmdPropSet[0].guidPropertySet = DBPROPSET_ROWSET;
rgCmdPropSet[0].cProperties = 2;
rgCmdPropSet[0].rgProperties = CmdProperties;
hr = pCommandText->QueryInterface(IID_ICommandProperties, (void **)&pCommand Properties);
hr = pCommandProperties->SetProperties(1, rgCmdPropSet);
hr = pCommandText->SetCommandText(DBGUID_DEFAULT, (LPOLESTR)L"SELECT * FROM testTable");
hr = pCommandText->Execute(
NULL,
IID_IUnknown,
NULL,
NULL,
&pUnkRowset
);
ULONG cColumns = 0;
DBCOLUMNINFO *rgColumnInfo = NULL;
LPWSTR pStringBuffer = NULL;
DBBINDING *rgBindings = NULL;
ULONG dwOffset = 0;
ULONG iCol = 0;
// 获得列信息
hr = pUnkRowset->QueryInterface(IID_IColumnsInfo, (void **)&pColumnsInfo);
hr = pColumnsInfo->GetColumnInfo(
&cColumns,
&rgColumnInfo,
&pStringBuffer
);
// 分配binding内存
rgBindings = (DBBINDING*) CoTaskMemAlloc(cColumns * sizeof(DBBINDING));
memset(rgBindings, 0, cColumns * sizeof(DBBINDING));
// 对每个列，设定属性
for (iCol=0; iCol<cColumns; iCol++)
{
rgBindings[iCol].iOrdinal = rgColumnInfo[iCol].iOrdinal;
rgBindings[iCol].dwPart = DBPART_VALUE | DBPART_LENGTH | DBPART_STATUS ;
rgBindings[iCol].obStatus = dwOffset;
rgBindings[iCol].obLength = dwOffset + sizeof(DBSTATUS);
rgBindings[iCol].obValue = dwOffset + sizeof(DBSTATUS) + sizeof(ULONG);
rgBindings[iCol].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
rgBindings[iCol].eParamIO = DBPARAMIO_NOTPARAM;
rgBindings[iCol].bPrecision = rgColumnInfo[iCol].bPrecision;
rgBindings[iCol].bScale = rgColumnInfo[iCol].bScale;
rgBindings[iCol].wType = DBTYPE_WSTR;
rgBindings[iCol].cbMaxLen = 0;
// 检查在buffer中，所需要的最大字节数
switch (rgColumnInfo[iCol].wType)
{
case DBTYPE_NULL:
case DBTYPE_EMPTY:
case DBTYPE_I1:
case DBTYPE_I2:
case DBTYPE_I4:
case DBTYPE_UI1:
case DBTYPE_UI2:
case DBTYPE_UI4:
case DBTYPE_R4:
case DBTYPE_BOOL:
case DBTYPE_I8:
case DBTYPE_UI8:
case DBTYPE_R8:
case DBTYPE_CY:
case DBTYPE_ERROR:
// 当为上述类型时，25个字符完全足够，再加上结尾符
rgBindings[iCol].cbMaxLen = (25 + 1) * sizeof (WCHAR);
break;
case DBTYPE_DECIMAL:
case DBTYPE_NUMERIC:
case DBTYPE_DATE:
case DBTYPE_DBDATE:
case DBTYPE_DBTIMESTAMP:
case DBTYPE_GUID:
// 当为上述类型时，50个字符完全足够，再加上结尾符
rgBindings[iCol].cbMaxLen = (50 + 1) * sizeof (WCHAR);
break;
case DBTYPE_BYTES:
// 当对上述类型转换时，每个字节变成两个字符，如：0xFF->"FF"
rgBindings[iCol].cbMaxLen = (rgColumnInfo[iCol].ulColumnSize * 2 + 1) * sizeof(WCHAR);
break;
case DBTYPE_STR:
case DBTYPE_WSTR:
case DBTYPE_BSTR:
rgBindings[iCol].cbMaxLen = (rgColumnInfo[iCol].ulColumnSize + 1) * sizeof(WCHAR);
break;
default:
rgBindings[iCol].cbMaxLen = 4096 + 1;
break;
};
if (rgColumnInfo[iCol].wType == DBTYPE_IUNKNOWN ||
rgColumnInfo[iCol].dwFlags & DBCOLUMNFLAGS_ISLONG)
{
rgBindings[iCol].wType = DBTYPE_IUNKNOWN;
rgBindings[iCol].cbMaxLen = sizeof(ISequentialStream *);
rgBindings[iCol].pObject = (DBOBJECT *)CoTaskMemAlloc(sizeof(DBOBJECT));
rgBindings[iCol].pObject->iid = IID_ISequentialStream;
rgBindings[iCol].pObject->dwFlags = STGM_READ;
}
dwOffset = rgBindings[iCol].cbMaxLen + rgBindings[iCol].obValue;
dwOffset = ROUNDUP(dwOffset, COLUMN_ALIGNVAL);
}
hr = pUnkRowset->QueryInterface(IID_IAccessor, (void **)&pAccessor);
hr = pAccessor->CreateAccessor(
DBACCESSOR_ROWDATA,
cColumns,
rgBindings,
0,
&hAccessor,
NULL
);
hr = pUnkRowset->QueryInterface(IID_IRowset, (void **)&pRowset);
DBCOUNTITEM cRowsReturned;
HROW hRow[1];
HROW* pRow = hRow;
hr = pRowset->GetNextRows(
NULL,
0,
2,
&cRowsReturned,
&pRow);
char *pData = new char[4096];
if (cRowsReturned > 0)
{
hr = pRowset->GetData(hRow[0], hAccessor, pData);
printf("%S\t\t%S\t\t%S\n", &pData[rgBindings[0].obValue], &pData[rgBindings[1].obValue], &pData[rgBindings[2].obValue]);
}
_tcscpy_s((TCHAR *)&pData[rgBindings[2].obValue], 100, TEXT("14"));
hr = pRowset->QueryInterface(IID_IRowsetChange, (void **)&pRowsetChange);
hr = pRowsetChange->SetData(hRow[0], hAccessor, pData);
if (pRowset) { pRowset->Release(); }
if (pUnkRowset) { pUnkRowset->Release(); }
if (pUnkRowset) { pUnkRowset->Release(); }
if (pAccessor) { pAccessor->Release(); }
if (pDBCreateCommand) { pDBCreateCommand->Release(); }
if (pTableDefinition) { pTableDefinition->Release(); }
if (pCreateSession) { pCreateSession->Release(); }
return 0;
}
iv. ODBC Driver Manager
应用程序可以直接利用ODBC编程实现对数据库的访问。跟OLEDB一样，不同的数据库有不同的ODBC驱动。为了统一规范，在所有的ODBC驱动上，有一层ODBC Driver Manager （主要由ODBC32.DLL实现）。ODBC Driver Manager的主要任务就是确保不同驱动所支持的ODBC
API是统一的。当然，API的具体实现则是不同的，跟具体的后台数据库有关。
v. ODBC Drivers
我们可以用ODBC 数据源管理器（odbcad32.exe）来查看服务器上有多少个不同的ODBC驱动。需要注意的是，ODBC驱动有32位和64位区分，在64位上查看32位的ODBC驱动则需要调用C:\Windows\Sys
WOW64\odbcad32.exe，如 图4-3所示。
图4-3显示的ODBC驱动中，SQL Server是WDAC自带的ODBC驱动，主要由sqlsrv32.dll实现，无须安装，操作系统本身就带有这个驱动。下面的程序片段是一个ODBC程序，完成对数据库的连接和查询功能。
#include "stdafx.h"
#include <Windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <stdio.h>
#include <ODBCSS.h>
#include <iostream>
#include <odbcinst.h>
using namespace std;
// 当错误发生时，显示错误信息
void displayErrorMessage(SQLSMALLINT handleType, SQLHANDLE handle)
{
RETCODE rc;
const int NAME_LEN = 300;
SQLTCHAR state[NAME_LEN];
SQLTCHAR errorInfo[NAME_LEN];
SQLINTEGER nativeError;
SQLSMALLINT cbLength;
TCHAR szMsg[1000];
SQLINTEGER numRecords;
rc = SQLGetDiagRec(handleType, handle, 1, state, &nativeError, errorInfo, NAME_LEN, &cbLength);
printf("the error message is %S\n", errorInfo);
SQLGetDiagField(handleType, handle, 0, SQL_DIAG_NUMBER, &numRecords, SQL_IS_INTEGER, NULL);
for (int i=1; i<=numRecords; i++)
{
rc = SQLGetDiagRec(handleType, handle, i, state, &nativeError, errorInfo, NAME_LEN, &cbLength);
printf("the error message is %S\n", errorInfo);
printf("the error state is: %S\n", state);
printf("the nativeError state is %d\n", nativeError);
}
}
int _tmain(int argc, _TCHAR* argv[])
{
RETCODE rc;
HENV henv = NULL;
HDBC hdbc = NULL;
HSTMT hstmt = NULL;
// 分配一个环境句柄
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
// 设定属性
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
// 分配一个连接句柄
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
// 使用SQLDriverConnect对数据库进行连接
rc = SQLDriverConnect(
hdbc,
NULL,
(SQLTCHAR *) TEXT("Driver={SQL Server};Server=xxx\\KATMAI;UID=sa;PWD=xxxx"),
SQL_NTS,
NULL,
0,
NULL,
SQL_DRIVER_NOPROMPT
);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
displayErrorMessage(SQL_HANDLE_DBC, hdbc);
return -1;
}
// 分配语句句柄
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
_TCHAR cmd[1000];
// 删除表
_tcscpy_s(cmd, TEXT("if exists (select * from sysobjects where id = object_id ('dbo.testTable'))"));
_tcscat_s(cmd, TEXT(" drop table dbo.testTable"));
rc = SQLExecDirect(hstmt, (SQLTCHAR*)cmd, _tcslen(cmd));
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
// 创建表
_tcscpy_s(cmd, TEXT("CREATE TABLE testTable ( "));
_tcscat_s(cmd, TEXT(" [ID] int,"));
_tcscat_s(cmd, TEXT(" [name] nvarchar(40),"));
_tcscat_s(cmd, TEXT(" [Vacation] float"));
_tcscat_s(cmd, TEXT(" )"));
rc = SQLExecDirect(hstmt, (SQLTCHAR*)cmd, _tcslen(cmd));
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
// 插入数据
_tcscpy_s(cmd, TEXT("INSERT INTO testTable VALUES (1, 'John', 15)"));
rc = SQLExecDirect(hstmt, (SQLTCHAR*)cmd, _tcslen(cmd));
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
const int NAME_LEN = 300;
TCHAR szName[NAME_LEN];
SQLINTEGER empID;
float Vacation;
SQLLEN cbEmpID, cbName, cbFirstName, cbVacation;
rc = SQLExecDirect(hstmt, (SQLTCHAR *)TEXT("SELECT * from testTable;"), SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
// bind columns
SQLBindCol(hstmt, 1, SQL_C_ULONG, &empID, 0, &cbEmpID);
SQLBindCol(hstmt, 2, SQL_C_TCHAR, szName, NAME_LEN, &cbName);
SQLBindCol(hstmt, 3, SQL_C_FLOAT, &Vacation, 0, &cbVacation);
rc = SQLFetch(hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
printf(" %d %S %f", empID, szName, Vacation);
SQLFreeStmt(hstmt, SQL_CLOSE);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}
vi. WDAC编程与TDS包
读者可以看到，光是使用WDAC进行数据库编程就有多种方法。有些方法的区别仅仅在于换一个OLEDB
Provider或ODBC驱动。不管是用何种方法，最终跟SQL
Server的通信则是基于TDS（Tabular Data
Stream）协议的。这主要是由C:\Windows\system32\dbnetlib.dll完成，ODBC驱动或OLEDB Provider最终会调用这个DLL来完成对SQL Server真正的数据传递。SQL Server不管客户端是用什么编程模式，它能识别的就是TDS数据包。要具体查看TDS数据包的内容，我们可以：
用Network Monitor工具来监控TDS数据包的内容。
或者开启trace flag 4052, 4055,
3605，那么SQL Server会把接收到的和发送的TDS数据包在SQL Server错误日志里显示。开启这些trace flag会对性能有影响，因此只能在测试环境里开启。
用如下命令行启动数据库，随后运行上述查看数据库版本的ADO VBScript脚本。
Sqlservr.exe –c –sKATMAI –T4052 –T4055 –T3605
set conn = CreateObject("ADODB.CONNECTION")
set rs = CreateObject("ADODB.Recordset")
set cmd = CreateObject("ADODB.Command")
conn.Open "Provider=SQLOLEDB;Server=xxx\KATMAI;
Trusted_Connection=Yes;Database=master"
rs.Activeconnection = conn
rs.open "SELECT @@version"
msgbox rs.fields(0)
在SQL Server的错误日志中，我们就能看到如下TDS包。这里TDS包的每一个字节都有其特定的含义。如接收包的第一字节01，代表SQL
Server收到的命令是SQL Batch命令，第二个01代表的是TDS包状态，28代表TDS包的长度。具体的每个字节的含义，读者如果有兴趣可参考TDS规范：http://msdn.microsoft.com/ en-us/library/dd304523(v=prot.13).aspx。
2012-06-03 16:20:34.
spid51
Printing receive buffer:
01 01 00 28 00 00 01 00 53 00 45 00 4C 00 45 00 ...(....S.E.L.E.
43 00 54 00 20 00 40 00 40 00 76 00 65 00 72 00 C.T. .@.@.v.e.r.
73 00 69 00 6F 00 6E 00 s.i.o.n.
2012-06-03 16:20:34.
spid51
Printing send buffer:
04 01 01 BC 00 33 01 00 81 01 00 00 00 21 00 E7 .....3.......!..
58 02 09 04 D0 00 34 00 D1 98 01 4D 00 69 00 63 X.....4....M.i.c
00 72 00 6F 00 73 00 6F 00 66 00 74 00 20 00 53 .r.o.s.o.f.t. .S
00 51 00 4C 00 20 00 53 00 65 00 72 00 76 00 65 .Q.L. .S.e.r.v.e
00 72 00 20 00 32 00 30 00 30 00 38 00 20 00 28 .r. .2.0.0.8. .(
00 53 00 50 00 33 00 29 00 20 00 2D 00 20 00 31 .S.P.3.). .-. .1
00 30 00 2E 00 30 00 2E 00 35 00 35 00 30 00 30 .0...0...5.5.0.0
00 2E 00 30 00 20 00 28 00 58 00 36 00 34 00 29 ...0. .(.X.6.4.)
00 20 00 0A 00 09 00 53 00 65 00 70 00 20 00 32 . .....S.e.p. .2
00 31 00 20 00 32 00 30 00 31 00 31 00 20 00 32 .1. .2.0.1.1. .2
00 32 00 3A 00 34 00 35 00 3A 00 34 00 35 00 20 .2.:.4.5.:.4.5.
00 0A 00 09 00 43 00 6F 00 70 00 79 00 72 00 69 .....C.o.p.y.r.i
00 67 00 68 00 74 00 20 00 28 00 63 00 29 00 20 .g.h.t. .(.c.).
00 31 00 39 00 38 00 38 00 2D 00 32 00 30 00 30 .1.9.8.8.-.2.0.0
00 38 00 20 00 4D 00 69 00 63 00 72 00 6F 00 73 .8. .M.i.c.r.o.s
00 6F 00 66 00 74 00 20 00 43 00 6F 00 72 00 70 .o.f.t. .C.o.r.p
00 6F 00 72 00 61 00 74 00 69 00 6F 00 6E 00 0A .o.r.a.t.i.o.n..
00 09 00 44 00 65 00 76 00 65 00 6C 00 6F 00 70 ...D.e.v.e.l.o.p
00 65 00 72 00 20 00 45 00 64 00 69 00 74 00 69 .e.r. .E.d.i.t.i
00 6F 00 6E 00 20 00 28 00 36 00 34 00 2D 00 62 .o.n. .(.6.4.-.b
00 69 00 74 00 29 00 20 00 6F 00 6E 00 20 00 57 .i.t.). .o.n. .W
00 69 00 6E 00 64 00 6F 00 77 00 73 00 20 00 4E .i.n.d.o.w.s. .N
00 54 00 20 00 36 00 2E 00 31 00 20 00 3C 00 58 .T. .6...1. .<.X
00 36 00 34 00 3E 00 20 00 28 00 42 00 75 00 69 .6.4.>. .(.B.u.i
00 6C 00 64 00 20 00 37 00 36 00 30 00 31 00 3A .l.d. .7.6.0.1.:
00 20 00 53 00 65 00 72 00 76 00 69 00 63 00 65 . .S.e.r.v.i.c.e
00 20 00 50 00 61 00 63 00 6B 00 20 00 31 00 29 . .P.a.c.k. .1.)
00 0A 00 FD 10 00 C1 00 01 00 00 00 ............
对于上述ADO脚本，首先通过msado15.dll对其进行转换，转换成复杂的OLEDB程序API调用（包括OLE DB Core Services和SQL OLEDB），随之通过dbnetlib.dll转化成TDS包，通过传输协议如TCP传递给SQL Server。SQL Server接到命令后，在SQL Server里对该命令进行处理，并把处理结果以TDS包的形式再次通过传输协议如TCP传递给客户端。客户端接收到TDS包后，由dbnetlib把结果存放在缓存中，OLEDB程序API调用返回，随之ADO调用返回，ADO脚本则可以访问该缓存获得数据库查询结果。所以虽然上述的ADO脚本比较简单，但是WDAC内部做了很多事情来实现这些功能，并且使得编程方法简单化。
b.
SNAC编程
SQL Server Native Client是在SQL Server
2005后引入的OLEDB和ODBC编程方法。WDAC是发布在操作系统平台上的，比较稳定，但是随着SQL
Server的新功能开发，WDAC对其支持不够，因此，就有了SQL
Server Native Client。SQL Server Native Client是跟随SQL Server发布的，不过它的代码是从WDAC分离出去的，因此绝大部分的功能都是一样的。SQL Server Native Client所支持的新功能主要有：
·
数据库镜像的支持。
·
异步操作的支持，即在不阻塞调用线程的情况下立即返回。
·
多个活动结果集（MARS）的支持。
·
对XML数据类型的支持。
·
对用户定义类型的支持。
·
对大型对象数据类型（BLOB）的支持。
·
对快照隔离的支持。
·
对表值参数的支持。
·
对FILESTREAM的支持。
·
对AlwaysOn的支持。
如果应用程序使用到这些功能，则需要使用SQL Server
Native Client。读者可以在连接字符串里指定是用WDAC还是SNAC的ODBC驱动或OLEDB Provider。
SQL Server Native Client目前版本如表4-2所示。SNAC客户端版本和SQL Server版本是相互兼容的。即可以用SNAC for SQL Server 2005连接到SQL
Server 2012，也可以用SNAC for SQL Server 2012连接到SQL Server 2005。只有在用到特定版本的新功能时才需要使用相匹配的SNAC，如对FILESTREAM的支持，则需要用SNAC for SQL
Server 2008或SNAC for SQL Server 2012。SNAC
for SQL Server 2005则不行，因为FileStream功能在SQL
Server 2008及以后的版本才有。
表4-2 SQL Server Native Client版本
SNAC名
|
ODBC驱动名
|
OLEDB Provider名
|
DLL名
|
SNAC for SQL Server
2005
|
SQL Native Client
|
SQLNCLI
|
SQLNCLI.DLL
|
SNAC for SQL Server
2008
|
SQL Server Native Client 10.0
|
SQLNCLI10
|
SQLNCLI10.DLL
|
SNAC for SQL Server
2012
|
SQL Server Native Client 11.0
|
SQLNCLI11
|
SQLNCLI11.DLL
|
c.
ADO.NET编程
WDAC和SNAC都是在native环境下的对数据库编程访问方法，随着.net
framework平台的推出，微软也在其平台上提供了ADO.NET。ADO.NET和ADO是两个不同的产品，只是一些概念非常相似，故取名为ADO.NET。ADO.NET包含如下几个重要的类。
·
Connection类：Connection帮助指明数据库服务器、数据库名、用户名和密码以及连接数据库所需要的其他参数。Connection对象会被Command对象使用，这样就能够知道是在哪个数据源上面执行命令。
·
Command类：成功建立数据库连接后，则可以用Command来执行查询、修改、插入、删除等命令。
·
DataReader类：许多数据操作要求只是读数据。我们可以在Command对象执行查询命令得到DataReader对象。从DataReader返回的数据都是快速的且只是向前的数据流，即我们只能按照一定的顺序从数据流中取出数据，不能对数据进行更改。
·
DataSet类：DataSet对象是数据在内存中的表示形式。它包含多个DataTable对象，DataTable包含行和列。
·
DataAdapter类：DataAdapter对象提供链接DataSet和数据源的桥梁。DataAdapter使用Command对象在数据源中执行SQL命令以向DataSet加载数据，并对DataSet中数据的更改协调回数据源。
下面是一段简单的ADO.NET代码，连接SQL
Server并查询信息。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace SIMPLEADONET
{
class Program
{
static void Main(string[] args)
{
SqlConnection conn;
SqlCommand cmd;
SqlDataAdapter DA;
DataSet ds;
String cmdString = null;
//int r;
cmdString = "if exists (SELECT * FROM sysobjects WHERE ID = OBJECT_ID ('dbo.testTable'))";
cmdString += " drop table dbo.testTable";
conn = new SqlConnection("Data Source=xxxx\\DENALI; Trusted_Connection=yes;");
conn.Open();
cmd = new SqlCommand(cmdString, conn);
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE testTable (";
cmd.CommandText += " [ID] int, ";
cmd.CommandText += " [name] nvarchar(40), ";
cmd.CommandText += " [Vacation] float) ";
cmd.ExecuteNonQuery();
// 插入数据
cmd.CommandText = "INSERT INTO testTable VALUES (1, 'John', 15)";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM testTable";
DA = new SqlDataAdapter(cmd);
ds = new DataSet();
DA.Fill(ds, "testTable");
for (int i = 0; i < ds.Tables["testTable"].Rows.Count; i++)
{
int id = (int)(ds.Tables["testTable"].Rows[i])["ID"];
string name = (string)(ds.Tables["testTable"].Rows[i])["name"];
double vacation = (double)(ds.Tables["testTable"].Rows[i])["Vacation"];
Console.WriteLine(id.ToString() + " " + name + " " + vacation.ToString());
}
conn.Close();
}
}
}
ADO.NET也可以调用Native的WDAC或SNAC来实现对数据库的编程访问。图4-4是综合Native或Managed方法对数据库编程的可能路径。为简单起见，SNAC模块并没有特别列出，即便如此，读者仍旧可以看到方法有很多，有时候可能感觉不知道如何选择。其实之所以有这么多可能路径，目的是为了兼容性考虑。一般情况在Native模式下，用ADO或ODBC方法居多，在Managed模式下，通过SqlConnection实现对数据库的访问居多。
2.
连接字符串
通过上面的的介绍，我们知道除了设定属性外，连接后台数据库的行为主要是由连接字符串指定的。连接字符串有其一定的规范。除了指定要连接的服务器名，认证方法以外，还可以指定其他的一些连接特性，如DSN或使用哪个ODBC驱动等。ODBC的连接字符串和OLEDB连接字符串，以及ADO.NET的连接字符串规范也是不一样的。
表4-3 重要的ODBC连接字符串属性
属 性 名
|
描 述
|
Driver
|
ODBC驱动的名字
|
DSN
|
DSN名字。DSN可以用odbcad32.exe创建，
存放在注册表中
|
PWD
|
UID的密码
|
UID
|
连接时的用户名，这里的名字是数据库的SQL登录名
|
Address
|
指定数据库实例的网络名，可以由如下格式：
Tcp:<host
name>\<instance name>
Tcp:<host
name>,<TCP/IP port number>
Np:\\<host
name>\pipe\<pipe name>
|
APP
|
指定当前的应用名字
|
Database
|
指定连接默认数据库名
|
Encrypt
|
连接是否加密
|
Network
|
指定跟数据库连接是用何种网络通信协议，常见的值有：
DBNMPNTW：指定用name pipe连接
DBMSSOCN：指定用TCP/IP协议
DBMSLPCN：指定用Shared Memory协议
|
Server
|
指定要连接的数据库服务名，如果指定了Driver
这个属性，则也必须指定Server属性
|
Trusted_Connection
|
指定是否用Windows验证，如果是No，
则必须指定UID和PWD值
|
从上面的一些键值，读者可以知道有些是重叠的，如Address和Server。如果两个键值都指定且值不一致，ODBC会根据规范指定哪一个优先。当然，最好在写连接字符串的时候不要有重叠现象。下面是一些常见的连接字符写法：
Driver=SQL Server;Server=ServerName;Database=DBName;Trusted_Connection=Yes;
Driver=SQL Server;Server=ServerName;Database=DBName;UID=sa;PWD=xxxx;
Driver=SQL Server;Server=ServerName\InstanceName;Trusted_Connection=Yes;
DSN=testDSN;UID=sa;PWD={abc;}}def}; //这里密码是abc;}def
Driver=SQL Server Native Client 11.0; Server=ServerName; Database=DBName; UID=sa; PWD=xxxx;
表4-4 重要的OLEDB连接字符串属性
属性名
|
描 述
|
Provider
|
指定使用的Provider名字
|
Encrypt Password
|
指定把密码传递给服务器端做验证时，
是否需要对密码加密
|
Integrated Security
|
验证模式，可以选用SSPI，即Windows
验证，或empty string，即SQL验证
|
Password
|
SQL登录的密码
|
User ID
|
SQL登录的用户名
|
Initial Catalog
|
连接的默认数据库名
|
Data Source
|
数据源名
|
General Timeout
|
请求发送到Data Source的timeout，以秒为单位
|
OLE DB Services
|
指定服务的属性，可以用它来控制是否使用连接池
|
Connect Timeout
|
连接完成的timeout，以秒为单位
|
Network Address
|
指定数据库实例的网络名，可以由如下格式：
Tcp:<host
name>\<instance name>
Tcp:<host
name>,<TCP/IP port number>
Np:\\<host
name>\pipe\<pipe name>
|
Network Library
|
指定跟数据库连接是用何种网络通信协议，
常见的值有：
DBNMPNTW：指定用name pipe连接
DBMSSOCN：指定用TCP/IP协议
DBMSLPCN：指定用Shared Memory协议
|
Packet Size
|
指定和服务器连接的网络包大小，默认为4 096字节
|
Use Encryption for
Data
|
指定是否要对数据进行加密
|
读者可以看到，OLEDB和ODBC虽然有些键值的名称不同，不过其意义是一样的，如SQL登录名在OLEDB为User ID，在ODBC则是UID。下面是常见的OLEDB连接字符串：
Provider=SQLOLEDB;Data Source=ServerName;Integrated Security=SSPI;
Provider=SQLOLEDB;Data Source=ServerName;User Id=UserName;Pasword=UserPassword;
Provider=SQLOLEDB;Data Source=ServerName\InstanceName;Integrated Security=SSPI;
Provider=SQLNCLI11;Data Source=ServerName\InstanceName;Integrated Security=SSPI;
表4-5 重要的SQLClient连接字符串属性
属 性 名
|
描 述
|
Connection Lifetime或Load
Balance Timeout
|
当连接被返回到连接池时，将其创建时间与当前
时间做比较，如果时间长度（以秒为单位）超出了
由Connection Lifetime指定的值，该连接就会被销毁
|
Connection Timeout或
Connect Timeout或Timeout
|
连接的超时设定（以秒为单位）
|
Database
|
跟Initial Catalog同义
|
Data Source或Address或
Addr或Network Address或
Server
|
指定数据库实例的网络名，可以由如下格式：
Tcp:<host
name>\<instance name>
Tcp:<host
name>,<TCP/IP port number>
Np:\\<host
name>\pipe\<pipe name>
|
Encrypt
|
是否加密
|
Failover Partner
|
指定数据库镜像的镜像服务器
|
Initial Catalog
|
默认数据库名
|
Integrated Security或
Trusted_Connection
|
当为false时，将在连接中指定用户名和密码，当为true
时，将使用当前的Windows账户进行身份验证。可识别
的值为true，false，yes，no以及SSPI（和true等效）
|
Min Pool Size
|
连接池允许的最小连接数
|
Max Pool Size
|
连接池允许的最大连接数
|
Packet Size
|
指定和服务器连接的网络包大小，默认为4096字节
|
Network Library
|
指定跟数据库连接是用何种网络通信协议，常见的值有：
DBNMPNTW：指定用name pipe连接
DBMSSOCN：指定用TCP/IP协议
DBMSLPCN：指定用Shared Memory协议
|
PWD或Password
|
User ID的密码
|
Pooling
|
是否使用连接池
|
UID或User或User ID
|
SQL登录名
|
读者可以看到，ADO.NET的SQLClient连接字符串同义词增加了很多，这是由于ADO.NET尽量使得连接字符串的兼容程度更高一些。常见的SQLClient连接字符串有：
Integrated Security=Yes;Data Source=MyServer; Initial Catalog=MyDatabase;
Data Source=ServerName;User ID=UserName;Password=UserPassword;
Data Source=ServerName\InstanceName;Integrated Security=Yes
3.
连接池
所谓连接池，就是当连接关闭时，其物理连接还是继续保留，连接会放到一个池中，当下一个连接需要对数据库进行连接时，可从连接池中取得某一个连接继续使用。其目的是为了减少每次建立物理连接所需要的损耗，这对于性能要求很高的系统尤其重要。
以ADO为例，下面的脚本对数据库进行1 000次连接取数据。在一个测试环境下，需要时间59秒，如果不是用连接池，即在连接字符串设定OLE
DB Services= 2，则需要时间473秒，性能差了很多。
Dim index
Dim a, b
Dim v1, v2
set conn = CreateObject("ADODB.CONNECTION")
set rs = CreateObject("ADODB.Recordset")
set cmd = CreateObject("ADODB.Command")
a = timer
For index=1 to 1000
ADO_perf
Next
b = timer
msgbox (b-a)
Sub ADO_perf
'conn.Open "Provider=sqloledb; Data Source=xxxx\KATMAI; User Id=sa;
Password=xxxxx;OLE DB Services=-2"
conn.Open "Provider=sqloledb; Data Source=xxxx\KATMAI; User Id=sa; Password=xx;"
cmd.commandType = 1
cmd.ActiveConnection = conn
cmd.commandText = "SELECT @@version"
set rs = cmd.execute
v1 = rs.Fields(0).Value
conn.close()
End Sub
下面我们来检视一下连接池在SQL Server端的行为。针对前面的ADO代码，在连接字符串设定OLE DB
Services= 2，我们在数据库端每次连接可以看到有登录（Audit Login）行为，而且EventSubClass表明该登录是用Nonpooled方式登录，如图4-5所示。
当使用连接池（默认）时，每次连接读者还是会看到有登录行为，而且EventSubClass表明该登录是用连接池方式登录。在每次登录前，有sp_reset_connection被调用。sp_reset_connection会清理当前的连接状态，如重置当前连接所申请的内存、关闭所有对数据库的连接、释放临时表等。不会重置的是当前连接的安全上下文，所以新的连接无须再次验证安全上下文，如图4-6所示。
使用Network Monitor我们会更清楚一些，我们在服务器端抓网络包，对于使用了连接池的连接，其网络包如图4-7所示。首先客户端通过三次握手协议和数据库建立连接，但是随后就不再建立新的连接了，而是在同一个连接上进行操作。
而如果没有使用连接池，则我们能看到的网络包如图4-8所示，每做一个查询都需要进行TCP三次握手协议后才能对SQL Server重建建立连接。查询结束后，则真正的断开连接。在网络包的TCP:Flags标识中，看到有F标识，即Finish，表明断开物理上的连接。下次查询时需要重新建立连接。
由于连接池能大大提高性能，因此基本上所有的客户端编程默认情况下，连接池是启用的。所以读者不需要太多的关注，不过有两点要注意：
只有连接字符串相同的连接才能被重用。如果连接字符串不同，则会放入不同的连接池中，不同连接池中的连接是不能被互相重用的。
只有被关闭的连接才会放回连接池中，所以我们要检查代码，检查连接是否被正常关闭，以防止连接泄漏。
在ADO.NET平台上使用连接池，常见的错误有：
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
在ADO.NET平台上，默认连接池大小为100。当我们要去连接池选一个连接进行重用时，发现所有的连接都处于被使用状态。在这种情况下，ADO.NET会等待Connection Timeout时间，如果在这段时间内，一直无法从连接池拿到一个可以被重用的连接，则会报上述错误。解决方法如下：
检查所有的连接是否正常开启和关闭，没有关闭的连接是不能被重用的。绝大多数上述的错误是由于这个原因导致的。
另外我们也可以修改连接字符串提高"Max Pool Size"，如设定为"Max Pool Size"=200
只有在极端情况下，我们可以修改连接字符串把连接池取消。这当然能解决问题，但是会影响性能。
对于ADO.NET，推荐使用Using语法来使用连接池。这样，能确保一旦离开语句返回，连接能够被关闭并放回连接池中。下面是简单代码：
using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=Northwind"))
{
connection.Open();
// Pool A 被创建
}
using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=pubs"))
{
connection.Open();
// 因为连接字符串不同，因此Pool B被创建。
}
using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=Northwind"))
{
connection.Open();
// 连接字符串和Pool A匹配
}
每次对数据库连接时，我们有时候会碰到连接超时或者命令超时，这两个超时是不一样的。以ADO.NET为例，当客户端和服务器端连接时，碰到的超时情况主要有下面几种：
·
当从连接池获取一个连接时，碰到超时。
·
当建立一个全新连接（而不是从连接池获取）时，碰到超时。
·
当发送一个命令（command）到SQL Server时，超时。
·
当发送命令（连接字符串带有"context connection=true"属性）到SQL Server时，超时。
·
当不是显示的发送一个命令（implicitly）到SQL Server时，碰到超时。
·
当执行异步命令时，（BeginExecute）碰到超时。
·
当从服务器端，获取行时，碰到超时。
·
当用Bulk copy方式，上传数据时，碰到超时。
这些超时主要是通过连接字符串中的Connect Timeout和SqlCommand.CommandTimeout来进行控制。前面两种是登录超时由Connection
Timeout来决定什么时候超时，后面几种是命令超时由Command Timeout来决定什么时候超时。
以下面的测试代码为例，NA是一个不存在的服务器，在连接字符串设定connect
timeout=0，则下面代码会无限等待。默认的情况下，会在15秒后（默认timeout值）抛出错误。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text;
namespace ADONET
{
class Program
{
static public SqlConnection conn;
static public SqlCommand cmd;
static public SqlDataAdapter DA;
static public DataSet ds;
static void Main(string[] args)
{
conn = new SqlConnection("Data Source=NA;User Id=sa; Password=xx;connection timeout=0");
cmd = new SqlCommand("WAITFOR DELAY ’00:01’", conn);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
连接超时的错误信息如下：
Unhandled Exception: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternal ConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo server Info, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owning Connection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outer Connection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
依照以上的代码为例，改正服务器名后执行语句，读者会发现应用程序会抛出如下命令超时错误。这是因为Command Timeout默认值是30秒，而我们的语句执行需要1分钟。如果需要延长命令的超时时间，我们要修改cmd的超时属性。
Unhandled Exception: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ADONET.Program.Main(String[] args) in D:\TEST\MDACIntroduction\Pooling\ADONET - Copy\ADONET\Program.cs:line 23
对于ADO或OLEDB，连接超时可以通过连接字符串中的Connect Timeout属性来控制。如下面的代码，NA是一个不存在的服务器，在100秒后连接会报告失败。如果不做设定，默认的连接Timeout值为15。
Dim index
Dim a, b
Dim v1, v2
set conn = CreateObject("ADODB.CONNECTION")
set rs = CreateObject("ADODB.Recordset")
set cmd = CreateObject("ADODB.Command")
a = timer
'On Error Resume Next'
conn.Open "Provider=sqloledb; Data Source=NA; User Id=sa;Password=xx;connect timeout=100"
cmd.commandType = 1
cmd.ActiveConnection = conn
cmd.commandText = "WAITFOR DELAY '00:01'"
cmd.execute
b = timer
msgbox (b-a)
其错误的信息为：
Microsoft OLE DB Provider for SQL Server: [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied
改正上述ADO代码的服务器名，则过30秒后会抛出如下命令超时错误：
Microsoft OLE DB Provider for SQL Server: Query timeout expired
如需要延长命令超时时间，则需要更改cmd的超时属性。
对于ODBC，连接的超时不能通过连接字符串来控制，必须通过修改代码里的SQL_ATTR_LOGIN_ TIMEOUT属性来控制连接超时，默认值是15秒。
#include "stdafx.h"
#include <Windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <stdio.h>
#include <ODBCSS.h>
#include <iostream>
#include <odbcinst.h>
using namespace std;
// display error message when an error occurs
void displayErrorMessage(SQLSMALLINT handleType, SQLHANDLE handle)
{
RETCODE rc;
const int NAME_LEN = 300;
SQLTCHAR state[NAME_LEN];
SQLTCHAR errorInfo[NAME_LEN];
SQLINTEGER nativeError;
SQLSMALLINT cbLength;
SQLINTEGER numRecords;
rc = SQLGetDiagRec(handleType, handle, 1, state, &nativeError, errorInfo, NAME_LEN, &cbLength);
printf("the error message is %S\n", errorInfo);
SQLGetDiagField(handleType, handle, 0, SQL_DIAG_NUMBER, &numRecords, SQL_IS_INTEGER, NULL);
for (int i=1; i<=numRecords; i++)
{
rc = SQLGetDiagRec(handleType, handle, i, state, &nativeError, errorInfo, NAME_LEN, &cbLength);
printf("the error message is %S\n", errorInfo);
printf("the error state is: %S\n", state);
printf("the nativeError state is %d\n", nativeError);
}
}
int _tmain(int argc, _TCHAR* argv[])
{
HENV henv = NULL;
HDBC hdbc = NULL;
HSTMT hstmt = NULL;
RETCODE rc;
// 分配环境句柄
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
// 设定属性
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
// 分配连接句柄
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
SQLUINTEGER value = 3;
rc = SQLSetConnectAttr(
hdbc,
SQL_ATTR_LOGIN_TIMEOUT,
(SQLUINTEGER *)value,
sizeof(value));
// 使用SQLDriverConnect对数据库进行连接
rc = SQLDriverConnect(
hdbc,
NULL,
(SQLTCHAR *) TEXT("Driver={SQL Server};Server=NA;Trusted_Connection=Yes;"),
SQL_NTS,
NULL,
0,
NULL,
SQL_DRIVER_NOPROMPT
);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
displayErrorMessage(SQL_HANDLE_DBC, hdbc);
return -1;
}
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
value = 10;
rc = SQLSetStmtAttr(hstmt, SQL_ATTR_QUERY_TIMEOUT, (SQLUINTEGER*) value, sizeof(value));
TCHAR cmd[1000];
wcscpy_s(cmd, TEXT("WAITFOR DELAY '00:01'"));
rc = SQLExecDirect(hstmt, (SQLTCHAR*)cmd, wcslen(cmd));
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
displayErrorMessage(SQL_HANDLE_STMT, hstmt);
return -1;
}
SQLFreeStmt(hstmt, SQL_CLOSE);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}
当连接超时，ODBC可能抛出的错误如下：
Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
改正前面ODBC代码的服务器名，ODBC会抛出如下的命令超时错误。ODBC默认的Command Timeout值是0，所以如果不做上述设定，查询会一直进行下去，直到返回。我们可以给SQL_ATTR_QUERY_
TIMEOUT设定一个合适的值。
[Microsoft][ODBC SQL Server Driver]Query timeout expired
表4-6显示了不同编程方式，对Connection
Timeout和Command Timeout的不同设定方法。
编程方式
|
Connection Timeout
|
Command Timeout
|
ADO.NET
|
默认值为15秒，
可在连接字符串指定
|
默认值为30秒，
可在command对象对其进行修改
|
ADO/OLEDB
|
默认值为15秒，
可在连接字符串指定
|
默认值为30秒，
可在command对象对其进行修改
|
ODBC
|
缺省值为15秒，
可通过设定
SQL_ATTR_LOGIN_
TIMEOUT属性对
其进行修改
|
默认值为0，也就
是没有超时。可通过设定
SQL_ATTR_QUERY_TIMEOUT
对其进行修改
|
5.
使用BID Tracing来跟踪检查应用程序的执行
客户端程序执行时，有时会遇到一些难以调试的错误，如偶尔SQL Server连不上。有时一周出现一次，或者三四天出现一次。错误信息很简单，如：
[Microsoft][SQL Native Client]communication link failure
或者碰到一些毫无头绪的错误信息：
Multiple-step operation generated errors. Check each status value.
at ADODB.Field.set_Value(Object pvar)
at ADODB.InternalField.set_Value(Object value)
碰到这些错误一般都是很难调试跟踪，对于这种问题似乎没有头绪。不过也不是没有办法，在很早以前，微软很多产品都有Built-In Diagnostics Tracing（BID跟踪）功能。所谓的BID跟踪功能其实很简单，就是在源代码的关键地方插入类似打印语句，平时不开启以免影响性能，只有在启用BID跟踪的时候，这些关键的信息会被打印下来。
启用BID跟踪对程序应能有一定的影响，但是还是在可控范围内，大概性能最多会有5%的影响。另外由于照顾到性能，并不是所有的跟踪都会被打印，如果BID跟踪发现对性能有影响，就会忽略一些跟踪点。
表4-7是跟踪SNAC10 OLEDB
Provider所得到的结果。这个结果非常难以阅读，主要是因为显示的信息都是SQLNCLI10源代码相关，但是从结果中，我们还是可以了解一些信息。
模 块
|
时 间 点
|
跟 踪 点
|
SQLNCLI10
|
50325
|
<TDS|ROW|TOKEN|ADV> 0000000002C97090
{BATCHCTX},
0000000002C98490 {STMT},
ROW - RowNum: 1,
ComputeRowID: 0
|
SQLNCLI10
|
50325
|
leave_02
|
SQLNCLI10
|
50325
|
ObtainIDw 646185#
<CMultipleResults::FInit|ID|OLEDB>
0000000002C99810 {.} created by 646183#{CCommand}
|
SQLNCLI10
|
50325
|
<CCommand::AddRef|REFCOUNT> RefCount 3 646183#
0000000005907C70 {.}
|
SQLNCLI10
|
50325
|
<CDBSession::AddRef|REFCOUNT>
RefCount 3 15# 0000000002C97DC0{.}
|
SQLNCLI10
|
50325
|
<CCommand::AddRef|REFCOUNT> RefCount 4 646183#
0000000005907C70 {.}
|
SQLNCLI10
|
50325
|
<CCommand::Release|REFCOUNT> RefCount 3
646183# 0000000005907C70 {.}
|
SQLNCLI10
|
50325
|
<CMultipleResults::AddRef|REFCOUNT>
RefCount 1
646185# 0000000002C99810{.}
|
SQLNCLI10
|
50325
|
<ICommand::Execute|OLEDB|RET>
00000000{HRESULT},
pParams:
0000000000000000 {DBPARAMS*}, pcRowsAffected: 000
|
SQLNCLI10
|
50325
|
leave_01
|
上述代码执行了一个如下查询SELECT TOP 1 ACCTNO FROM
ACCOUNTS OPTION(MAXDOP 1)，这个查询从开始到结束花了user mode上105毫秒时间。语句运行正常，因为最后HRESULT=0。另外，我们还可以看到用到的通信协议是Named Pipe，因为读数据时前面标识了NP。通常情况，在每个函数的开头和结尾我们都会加跟踪点，所以enter_#和leave_#一般都会匹配。但是有时候跟踪点并不会记录，所以并不是一直都是如此。
WDAC、SNAC和ADO.NET都有这个功能，但是要收集BID跟踪数据，步骤稍微有点复杂。
a. 下载BID TRACE所需要的文件
http://download.microsoft.com/download/e/d/3/ed3cdde1-0ffc-4abc-
8bc2-d62f206d1a3d/Data_Access_Tracing_2008.exe，解压缩后，文件目录结构如图4-9所示。
8bc2-d62f206d1a3d/Data_Access_Tracing_2008.exe，解压缩后，文件目录结构如图4-9所示。
b. 修改注册表
HKEY_LOCAL_MACHINE\Software\Microsoft\BidInterface\Loader,
加:Path值，如图4-10所示。
·
注意a：如果是32位应用程序跑在64位系统上，则修改相应的HKLM\Software\Wow6432Node\
Microsoft\BidInterface\Loader。
·
注意b：Path前面有一个冒号。
·
注意c：如果是跟踪ADO.NET 2.0，则注册表值为%SYSTEMROOT%\Microsoft.NET\Framework\
v2.0.50727\ADONETDiag.dll，如果是跟踪WDAC或SNAC，则值为msdadiag.dll。
c. 打开一个命令窗口，编译MOF文件。
到MOF_Files文件夹下面，执行命令：mofcomp all.mof。
d. 重新启动应用程序。
在监控之前，我们需要重新启动应用程序，否则抓不到BID Trace信息。
e. 在Control_GUID_Files文件夹下面，选定要监控的模块，如ctrl.guid.mdac，再对该文件进行修改，只选择要监控的DLL。
f. 开始监控。
Logman start MyTrace -pf ctrl.guid.mdac -ct perf -o out.etl -ets
g. 等问题重现，重现问题后，终止监控。
Logman stop Mytrace -ets
h. 这时，out.etl文件会产生，读者可以使用tracerpt命令把ETL格式的文件转为XML或CSV格式。
Tracerpt.exe out.etl
Tracerpt.exe out.etl -o logfile.csv -of CSV
也可以用LogParser工具把ETL格式转换成文本格式。由于日志文件比较大且日志非常难阅读，读者可以通过搜寻关键字的方法定位到问题点。
下面是一个使用BID TRACE跟踪问题的例子。
问题描述：应用程序偶尔报[DBNETLIB][ConnectionRead(recv().)General
Network Error. Check your network documentation。
从错误信息里，我们看到错误是在模块DBNETLIB.DLL这个模块报告出来的。抓取这个模块的BID Trace，发现在出问题的时候是由于底层报操作系统0x274c错误。
205136,DBNETLIB,2012-06-25 15:07:01,ModID=5|msgStr=<ConnectionRead|ERR> recv 0x274c {WINERR}
205137,DBNETLIB,2012-06-25 15:07:01,ModID=5|msgStr=<ConnectionRead|ERR> recv 0x274c {WINERR}
205138,DBNETLIB,2012-06-25 15:07:01,ModID=5|msgStr=<ConnectionRead|ERR> recv 0x274c {WINERR}
205139,DBNETLIB,2012-06-25 15:07:01,ModID=5|msgStr=<ConnectionRead|ERR> recv 0x274c {WINERR}
0x274c错误即10060错误，具体描述为：A connection attempt failed because the connected
party did not properly respond after a period of time, or established
connection failed because connected hos has failed to respond.
通过这个错误代码，我们定位到在客户端报GNE错误时，Server端是有性能问题，所以导致连接中断。开启Performance Monitor通过检查服务器端性能发现，出问题时服务器可用内存非常低。最后通过调整服务器的内存使用，使得服务器可用内存保持稳定，问题不再发生。
6.
小结
在这章我们具体讲述了客户端应用编程机理，包括WDAC、SNAC和ADO.NET，我们没有具体介绍每一种具体的编程方法细节，而是对其架构进行了描述，因为每一种编程方法展开来均无法在一个章节内讲述完，但是我们针对这些编程方法的一些共性，如连接字符串、连接池、连接超时和命令超时进行了介绍。最后，我们介绍了BID
Trace这个跟踪工具来发现客户端问题，BID跟踪工具使用起来比较麻烦，但是有可能是最后的救命稻草，对于调查偶尔发生的问题特别有用。
