NET数据库连接字符串.docx
《NET数据库连接字符串.docx》由会员分享，可在线阅读，更多相关《NET数据库连接字符串.docx（19页珍藏版）》请在冰豆网上搜索。
NET数据库连接字符串
.NET数据库连接字符串
1说明
ADO.NET连接字符串：
SQLServer，SQLServer2005，ACCESS，Oracle，MySQL，Interbase，IBMDB2，Sybase，Informix，Ingres，MimerSQL，Lightbase，PostgreSQL，Paradox，DNS，Firebird，Excel，Text，DBF/FoxPro，AS/400（iSeries），Exchange，VisualFoxPro，Pervasive，UDL。
2SQLServer
2.1ODBC
1、标准安全
"Driver={SQLServer};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
2、信任的连接
"Driver={SQLServer};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
3、提示输入用户名和密码
oConn.Properties（"Prompt"）=adPromptAlways
oConn.Open"Driver={SQLServer};Server=Aron1;DataBase=pubs;"
2.2OLEDB,OleDbConnection（.NET）
1、标准安全
"Provider=sqloledb;DataSource=Aron1;InitialCatalog=pubs;UserId=sa;Password=asdasd;"
2、信任的连接
"Provider=sqloledb;DataSource=Aron1;InitialCatalog=pubs;IntegratedSecurity=SSPI;"
（useserverName\instanceNameasDataSourcetouseanspecifikSQLServerinstance,onlySQLServer2000）
3、提示输入用户名和密码
oConn.Provider="sqloledb"
oConn.Properties（"Prompt"）=adPromptAlways
oConn.Open"DataSource=Aron1;InitialCatalog=pubs;"
4、IP地址连接管道
"Provider=sqloledb;DataSource=190.190.200.100,1433;NetworkLibrary=DBMSSOCN;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
（DBMSSOCN=TCP/IPinsteadofNamedPipes,attheendoftheDataSourceistheporttouse（1433isthedefault））
2.3SqlConnection（.NET）
1、标准安全
"DataSource=Aron1;InitialCatalog=pubs;UserId=sa;Password=asdasd;"
-or-
"Server=Aron1;Database=pubs;UserID=sa;Password=asdasd;Trusted_Connection=False"
（bothconnectionstringsproducesthesameresult）
2、信任的连接
"DataSource=Aron1;InitialCatalog=pubs;IntegratedSecurity=SSPI;"
-or-
"Server=Aron1;Database=pubs;Trusted_Connection=True;"
（bothconnectionstringsproducesthesameresult）
（useserverName\instanceNameasDataSourcetouseanspecifikSQLServerinstance,onlySQLServer2000）
3、IP地址连接管道
"DataSource=190.190.200.100,1433;NetworkLibrary=DBMSSOCN;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
（DBMSSOCN=TCP/IPinsteadofNamedPipes,attheendoftheDataSourceistheporttouse（1433isthedefault））
4、定义SqlConnection对象
C#:
usingSystem.Data.SqlClient;
SqlConnectionoSQLConn=newSqlConnection（）;
oSQLConn.ConnectionString="myconnectionstring";
oSQLConn.Open（）;
VB.NET:
ImportsSystem.Data.SqlClient
DimoSQLConnAsSqlConnection=NewSqlConnection（）
oSQLConn.ConnectionString="myconnectionstring"
oSQLConn.Open（）
DataShape
MSDataShape"Provider=MSDataShape;DataProvider=SQLOLEDB;DataSource=Aron1;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
3SQLServer2005
3.1SQLNativeClientODBCDriver
1、标准安全
"Driver={SQLNativeClient};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"
2、信任的连接
"Driver={SQLNativeClient};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
Equivalents
IntegratedSecurity=SSPIequalsTrusted_Connection=yes
3、提示输入用户名和密码
oConn.Properties（"Prompt"）=adPromptAlways
oConn.Open"Driver={SQLNativeClient};Server=Aron1;DataBase=pubs;"
4、授权的MARS（多重活动结果集）
"Driver={SQLNativeClient};Server=Aron1;Database=pubs;Trusted_Connection=yes;MARS_Connection=yes"
Equivalents
MultipleActiveResultSets=trueequalsMARS_Connection=yes
5、加密数据网络传输
"Driver={SQLNativeClient};Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"
6、在本地SQL服务器实例附加数据库文件
"Driver={SQLNativeClient};Server=.\SQLExpress;AttachDbFilename=c:
\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
-or-
"Driver={SQLNativeClient};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
（use|DataDirectory|whenyourdatabasefileresidesinthedatadirectory）
3.2SQLNativeClientOLEDBProvider
1、标准安全
"Provider=SQLNCLI;Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"
2、信任的连接
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;"
Equivalents
IntegratedSecurity=SSPIequalsTrusted_Connection=yes
3、提示输入用户名和密码
oConn.Properties（"Prompt"）=adPromptAlways
oConn.Open"Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"
4、授权的MARS（多重活动结果集）
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;MarsConn=yes"
Equivalents
MarsConn=yesequalsMultipleActiveResultSets=trueequalsMARS_Connection=yes
5、加密数据网络传输
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"
6、在本地SQL服务器实例附加数据库文件
"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=c:
\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
-or-
"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
（use|DataDirectory|whenyourdatabasefileresidesinthedatadirectory）
3.3SqlConnection（.NET）
1、标准安全
"DataSource=Aron1;InitialCatalog=pubs;UserId=sa;Password=asdasd;"
-or-
"Server=Aron1;Database=pubs;UserID=sa;Password=asdasd;Trusted_Connection=False"
（bothconnectionstringsproducesthesameresult）
2、信任的连接
"DataSource=Aron1;InitialCatalog=pubs;IntegratedSecurity=SSPI;"
-or-
"Server=Aron1;Database=pubs;Trusted_Connection=True;"
（bothconnectionstringsproducesthesameresult）
3、IP地址连接管道
"DataSource=190.190.200.100,1433;NetworkLibrary=DBMSSOCN;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
（DBMSSOCN=TCP/IPinsteadofNamedPipes,attheendoftheDataSourceistheporttouse（1433isthedefault））
4、授权的MARS（多重活动结果集）
"Server=Aron1;Database=pubs;Trusted_Connection=True;MultipleActiveResultSets=true"
Note!
UseADO.NET2.0forMARSfunctionality.MARSisnotsupportedinADO.NET1.0norADO.NET1.1
5、在本地SQL服务器实例附加数据库文件
"Server=.\SQLExpress;AttachDbFilename=c:
\asd\qwe\mydbfile.mdf;Database=dbname;Database=dbname;Trusted_Connection=Yes;"
-or-
"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
6、Using"UserInstance"onalocalSQLServerExpressinstance
"DataSource=.\SQLExpress;integratedsecurity=true;attachdbfilename=|DataDirectory|\mydb.mdf;userinstance=true;"
The"UserInstance"functionalitycreatesanewSQLServerinstanceontheflyduringconnect.ThisworksonlyonalocalSQLServer2005instanceandonlywhenconnectingusingwindowsauthenticationoverlocalnamedpipes.ThepurposeistobeabletocreateafullrightsSQLServerinstancetoauserwithlimitedadministrativerightsonthecomputer.Toenablethefunctionality:
sp_configure'userinstancesenabled','1'（0todisable）
UsingSQLServer2005Express?
Don'tmisstheservernamesyntax:
SERVERNAME\SQLEXPRESS（Substitute"SERVERNAME"withthenameofthecomputer）
ContextConnection-connectingto"self"fromwithinyourCLRstoredprodedure/function
3.4ContextConnection-connectingto"self"fromwithinyourCLRstoredprodedure/function
1、C#
using（SqlConnectionconnection=newSqlConnection（"contextconnection=true"））
{
connection.Open（）;
//Usetheconnection
}
2、VisualBasic
UsingconnectionasnewSqlConnection（"contextconnection=true"）
connection.Open（）
'Usetheconnection
EndUsing
3.5Readmore
WhentouseSQLNativeClient?
.Netapplications
DonotusetheSQLNativeClient.Usethe.NETFrameworkDataProviderforSQLServer（SqlConnection）.
COMapplications,allotherthen.Netapplications
COMapplications,allotherthen.Netapplications
UsetheSQLNativeClientifyouareaccessinganSQLServer2005andneedthenewfeaturesofSQLServer2005suchasMARS,encryption,XMLdatatypeetc.Continueuseyourcurrentprovider（OLEDB/ODBCthroughtheMDACpackage）ifyouarenotconnectingtoanSQLServer2005（that'squiteobviouseh..）orifyouareconnectingtoanSQLServer2005butarenotusinganyofthenewSQLServer2005features.
4ACCESS
4.1ODBC
1、标准安全
"Driver={MicrosoftAccessDriver（*.mdb）};Dbq=C:
\mydatabase.mdb;Uid=Admin;Pwd=;"
2、工作组
"Driver={MicrosoftAccessDriver（*.mdb）};Dbq=C:
\mydatabase.mdb;SystemDB=C:
\mydatabase.mdw;"
3、独占
"Driver={MicrosoftAccessDriver（*.mdb）};Dbq=C:
\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="
4.2OLEDB,OleDbConnection（.NET）
1、标准安全
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\somepath\mydb.mdb;UserId=admin;Password=;"
2、工作组
（systemdatabase）"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\somepath\mydb.mdb;JetOLEDB:
SystemDatabase=system.mdw;"
3、使用密码
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\somepath\mydb.mdb;JetOLEDB:
DatabasePassword=MyDbPassword;"
5Oracle
5.1ODBC
1、新版本
"Driver={MicrosoftODBCforOracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;"
2、旧版本
"Driver={MicrosoftODBCDriverforOracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"
5.2OLEDB,OleDbConnection（.NET）
1、标准安全
"Provider=msdaora;DataSource=MyOracleDB;UserId=UserName;Password=asdasd;"
Thisone'sfromMicrosoft,thefollowingarefromOracle
2、标准安全
"Provider=OraOLEDB.Oracle;DataSource=MyOracleDB;UserId=Username;Password=asdasd;"
3、信任的连接
"Provider=OraOLEDB.Oracle;DataSource=MyOracleDB;OSAuthent=1;"
5.3OracleConnection（.NET）
1、标准
"DataSource=MyOracleDB;IntegratedSecurity=yes;"
ThisoneworksonlywithOracle8irelease3orlater
2、指定用户名和密码
"DataSource=MyOracleDB;UserId=username;Password=passwd;IntegratedSecurity=no;"
ThisoneworksonlywithOracle8irelease3orlater
3、定义OracleConnection对象
C#:
usingSystem.Data.OracleClient;
OracleConnectionoOracleConn=newOracleConnection（）;
oOracleConn.ConnectionString="myconnectionstring";
oOracleConn.Open（）;
VB.NET:
ImportsSystem.Data.OracleClient
DimoOracleConnAsOracleConnection=NewOracleConnection（）
oOracleConn.ConnectionString="myconnectionstring"
oOracleConn.Open（）
5.4
升级会员 