数据库连接字符串大全.docx
《数据库连接字符串大全.docx》由会员分享，可在线阅读，更多相关《数据库连接字符串大全.docx（24页珍藏版）》请在冰豆网上搜索。
数据库连接字符串大全
数据库连接字符串大全
转自：
SQLServer
∙ ODBC
o StandardSecurity:
"Driver={SQLServer};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
o Trustedconnection:
"Driver={SQLServer};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
o PRomptforusernameandpassWord:
oConn.Properties（"Prompt"）=adPromptAlways
oConn.Open"Driver={SQLServer};Server=Aron1;DataBase=pubs;"
∙ OLEDB,OleDbConnection（.NET）
o StandardSecurity:
"Provider=sqloledb;DataSource=Aron1;InitialCatalog=pubs;UserId=sa;Password=asdasd;"
o TrustedConnection:
"Provider=sqloledb;DataSource=Aron1;InitialCatalog=pubs;IntegratedSecurity=SSPI;"
（useserverName\instanceNameasDataSourcetouseanspecifikSQLServerinstance,onlySQLServer2000）
o Promptforusernameandpassword:
oConn.Provider="sqloledb"
oConn.Properties（"Prompt"）=adPromptAlways
oConn.Open"DataSource=Aron1;InitialCatalog=pubs;"
o Connectviaanipaddress:
"Provider=sqloledb;DataSource=190.190.200.100,1433;NetworkLibrary=DBMSSOCN;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
（DBMSSOCN=TCP/IPinsteadofNamedPipes,attheendoftheDataSourceistheporttouse（1433isthedefault））
∙ SqlConnection（.NET）
o StandardSecurity:
"DataSource=Aron1;InitialCatalog=pubs;UserId=sa;Password=asdasd;"
- or -
"Server=Aron1;Database=pubs;UserID=sa;Password=asdasd;Trusted_Connection=False"
（bothconnectionstringsproducesthesameresult）
o TrustedConnection:
"DataSource=Aron1;InitialCatalog=pubs;IntegratedSecurity=SSPI;"
- or -
"Server=Aron1;Database=pubs;Trusted_Connection=True;"
（bothconnectionstringsproducesthesameresult）
（useserverName\instanceNameasDataSourcetouseanspecifikSQLServerinstance,onlySQLServer2000）
o ConnectviaanIPaddress:
"DataSource=190.190.200.100,1433;NetworkLibrary=DBMSSOCN;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
（DBMSSOCN=TCP/IPinsteadofNamedPipes,attheendoftheDataSourceistheporttouse（1433isthedefault））
o DeclaretheSqlConnection:
C#:
usingSystem.Data.SqlClient;
SqlConnectionoSQLConn=newSqlConnection（）;
oSQLConn.ConnectionString="myconnectionstring";
oSQLConn.Open（）;
VB.NET:
ImportsSystem.Data.SqlClient
DimoSQLConnAsSqlConnection=NewSqlConnection（）
oSQLConn.ConnectionString="myconnectionstring"
oSQLConn.Open（）
∙ DataShape
o MSDataShape
"Provider=MSDataShape;DataProvider=SQLOLEDB;DataSource=Aron1;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
Wanttolearndatashaping?
Checkout4GuyfFromRolla'sgreatarticleaboutDataShaping>>
∙ Readmore
o Howtodefinewhichnetworkprotocoltouse
▪Example:
"Provider=sqloledb;DataSource=190.190.200.100,1433;NetworkLibrary=DBMSSOCN;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
Name
Networklibrary
dbnmpntw
Win32NamedPipes
dbmssocn
Win32WinsockTCP/IP
dbmsspxn
Win32SPX/IPX
dbmsvinn
Win32BanyanVines
dbmsrpcn
Win32Multi-Protocol（WindowsRPC）
▪
Importantnote!
WhenconnectingthroughtheSQLOLEDBproviderusethesyntaxNetworkLibrary=dbmssocn
andwhenconnectingthroughMSDASQLproviderusethesyntaxNetwork=dbmssocn
o AllSqlConnectionconnectionstringproperties
▪ThistableshowsallconnectionstringpropertiesfortheADO.NETSqlConnectionobject.MostofthepropertiesarealsousedinADO.Allpropertiesanddescriptionsisfrommsdn.
Name
Default
Description
applicationName
Thenameoftheapplication,or'.NetSqlClientDataProvider'ifnoapplicationnameisprovided.
AttachDBFilename
-or-
extended properties
-or-
InitialFileName
Thenameoftheprimaryfile,includingthefullpathname,ofanattachabledatabase.Thedatabasenamemustbespecifiedwiththekeyword'database'.
ConnectTimeout
-or-
ConnectionTimeout
15
Thelengthoftime（inseconds）towaitforaconnectiontotheserverbeforeterminatingtheattemptandgeneratinganerror.
ConnectionLifetime
0
Whenaconnectionisreturnedtothepool,itscreationtimeiscomparedwiththecurrenttime,andtheconnectionisdestroyedifthattimespan（inseconds）exceedsthevaluespecifiedbyconnectionlifetime.Usefulinclusteredconfigurationstoforceloadbalancingbetweenarunningserverandaserverjustbroughton-line.
ConnectionReset
'true'
Determineswhetherthedatabaseconnectionisresetwhenbeingremovedfromthepool.Settingto'false'avoidsmakinganadditionalserverround-tripwhenobtainingaconnection,buttheprogrammermustbeawarethattheconnectionstateisnotbeingreset.
CurrentLanguage
TheSQLServerLanguagerecordname.
DataSource
-or-
Server
-or-
Address
-or-
Addr
-or-
NetworkAddress
ThenameornetworkaddressoftheinstanceofSQLServertowhichtoconnect.
Enlist
'true'
Whentrue,thepoolerautomaticallyenliststheconnectioninthecreationthread'scurrenttransactioncontext.
InitialCatalog
-or-
Database
Thenameofthedatabase.
IntegratedSecurity
-or-
Trusted_Connection
'false'
Whethertheconnectionistobeasecureconnectionornot.Recognizedvaluesare'true','false',and'sspi',whichisequivalentto'true'.
MaxPoolSize
100
Themaximumnumberofconnectionsallowedinthepool.
MinPoolSize
0
Theminimumnumberofconnectionsallowedinthepool.
NetworkLibrary
-or-
Net
'dbmssocn'
ThenetworklibraryusedtoestablishaconnectiontoaninstanceofSQLServer.Supportedvaluesincludedbnmpntw（NamedPipes）,dbmsrpcn（Multiprotocol）,dbmsadsn（AppleTalk）,dbmsgnet（VIA）,dbmsipcn（SharedMemory）anddbmsspxn（IPX/SPX）,anddbmssocn（TCP/IP）.
ThecorrespondingnetworkDLLmustbeinstalledonthesystemtowhichyouconnect.Ifyoudonotspecifyanetworkandyouusealocalserver（forexample,"."or"（local）"）,sharedmemoryisused.
PacketSize
8192
SizeinbytesofthenetworkpacketsusedtocommunicatewithaninstanceofSQLServer.
Password
-or-
Pwd
ThepasswordfortheSQLServeraccountloggingon.
PersistSecurityInfo
'false'
Whensetto'false',security-sensitiveinformation,suchasthepassword,isnotreturnedaspartoftheconnectioniftheconnectionisopenorhaseverbeeninanopenstate.Resettingtheconnectionstringresetsallconnectionstringvaluesincludingthepassword.
Pooling
'true'
Whentrue,theSQLConnectionobjectisdrawnfromtheappropriatepool,orifnecessary,iscreatedandaddedtotheappropriatepool.
UserID
TheSQLServerloginaccount.
WorkstationID
thelocalcomputername
ThenameoftheworkstationconnectingtoSQLServer.
▪
Note
Use;toseparateeachproperty.
Ifanameoccursmorethanonce,thevaluefromthelastoneintheconnectionstringwillbeused.
Ifyouarebuildingyourconnectionstringinyourappusingvaluesfromuserinputfields,makesuretheusercan'tchangetheconnectionstringbyinsertinganadditionalpropertywithanothervaluewithintheuservalue.
SQLServer2005
∙ SQLNativeClientODBCDriver
o Standardsecurity:
"Driver={SQLNativeClient};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"
o Trustedconnection:
"Driver={SQLNativeClient};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
Equivalents
IntegratedSecurity=SSPIequalsTrusted_Connection=yes
o Promptforusernameandpassword:
oConn.Properties（"Prompt"）=adPromptAlways
oConn.Open"Driver={SQLNativeClient};Server=Aron1;DataBase=pubs;"
o EnablingMARS（multipleactiveresultsets）:
"Driver={SQLNativeClient};Server=Aron1;Database=pubs;Trusted_Connection=yes;MARS_Connection=yes"
Equivalents
MultipleActiveResultSets=trueequalsMARS_Connection=yes
UsingMARSwithSQLNativeClient,byChrisLee>>
o Encryptdatasentovernetwork:
"Driver={SQLNativeClient};Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"
o AttachadatabasefileonconnecttoalocalSQLServerExpressinstance:
"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）
Whyisthe"Database"parameterneeded?
Answer:
Ifthedatabasewaspreviouslyattached,SQLServerdoesnotreattachit（itusestheattacheddatabaseasthedefaultfortheconnection）.
DownloadtheSQLNativeClienthere>>（thepackagecontainsbooththeODBCdriverandtheOLEDBprovider）
UsingSQLServer2005Express?
Don'tmisstheservernamesyntax:
SERVERNAME\SQLEXPRESS（Substitute"SERVERNAME"withthenameofthecomputer）
∙ SQLNativeClientOLEDBProvider
o Standardsecurity:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"
o Trustedconnection:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;"
Equivalents
IntegratedSecurity=SSPIequalsTrusted_Connection=yes
o Promptforusernameandpassword:
oConn.Properties（"Prompt"）=adPromptAlways
oConn.Open"Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"
o EnablingMARS（multipleactiveresultsets）:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;MarsConn=yes"
Equivalents
MarsConn=yesequalsMultipleActiveResultSets=trueequalsMARS_Connection=yes
UsingMARSwithSQLNativeClient,byChrisLee>>
o Encryptdatasentovernetwork:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"
o AttachadatabasefileonconnecttoalocalSQLServerExpressinstance:
"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=c:
\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
- or -
"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilen
升级会员 