c#连接数据库代码集合.docx
《c#连接数据库代码集合.docx》由会员分享，可在线阅读，更多相关《c#连接数据库代码集合.docx（21页珍藏版）》请在冰豆网上搜索。
c#连接数据库代码集合
SQLServer
ODBC
standardSecurity:
"Driver={SQLServer};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
trustedconnection:
"Driver={SQLServer};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
promptforusernameandpassword:
oConn.Properties（"Prompt"）=adPromptAlways
oConn.Open"Driver={SQLServer};Server=Aron1;DataBase=pubs;"
OLEDB,OleDbConnection（.NET）
standardSecurity:
"Provider=sqloledb;DataSource=Aron1;InitialCatalog=pubs;UserId=sa;Password=asdasd;"
trustedConnection:
"Provider=sqloledb;DataSource=Aron1;InitialCatalog=pubs;IntegratedSecurity=SSPI;"
（useserverName\instanceNameasDataSourcetouseanspecifikSQLServerinstance,onlySQLServer2000）
promptforusernameandpassword:
oConn.Provider="sqloledb"
oConn.Properties（"Prompt"）=adPromptAlways
oConn.Open"DataSource=Aron1;InitialCatalog=pubs;"
connectviaanIPaddress:
"Provider=sqloledb;DataSource=190.190.200.100,1433;NetworkLibrary=DBMSSOCN;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
（dbmssocn=tcp/ipinsteadofNamedPipes,attheendoftheDataSourceistheporttouse（1433isthedefault））
SqlConnection（.NET）
standardSecurity:
"DataSource=Aron1;InitialCatalog=pubs;UserId=sa;Password=asdasd;"
-or-
"Server=Aron1;Database=pubs;UserID=sa;Password=asdasd;Trusted_Connection=False"
（boothconnectionstringsproducesthesameresult）
trustedConnection:
"DataSource=Aron1;InitialCatalog=pubs;IntegratedSecurity=SSPI;"
-or-
"Server=Aron1;Database=pubs;Trusted_Connection=True;"
（boothconnectionstringsproducesthesameresult）
（useserverName\instanceNameasDataSourcetouseanspecifikSQLServerinstance,onlySQLServer2000）
connectviaanIPaddress:
"DataSource=190.190.200.100,1433;NetworkLibrary=DBMSSOCN;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
（dbmssocn=tcp/ipinsteadofNamedPipes,attheendoftheDataSourceistheporttouse（1433isthedefault））
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
msDataShape
"Provider=MSDataShape;DataProvider=SQLOLEDB;DataSource=Aron1;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
wanttolearndatashaping?
Checkout4GuyfFromRolla'sgreatarticleaboutDataShaping>>
Readmore
Howtodefinewhichnetworkprotocoltouse
Example:
"Provider=sqloledb;DataSource=190.190.200.100,1433;NetworkLibrary=DBMSSOCN;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
NameNetworklibrary
dbnmpntwWin32NamedPipes
dbmssocnWin32WinsockTCP/IP
dbmsspxnWin32SPX/IPX
dbmsvinnWin32BanyanVines
dbmsrpcnWin32Multi-Protocol（WindowsRPC）
importantnote!
WhenconnectingthroughtheSQLOLEDBproviderusethesyntaxNetworkLibrary=dbmssocn
andwhenconnectingthroughMSDASQLproviderusethesyntaxNetwork=dbmssocn
AllSqlConnectionconnectionstringproperties
ThistableshowsallconnectionstringpropertiesfortheADO.NETSqlConnectionobject.MostofthepropertiesarealsousedinADO.Allpropertiesanddescriptionsisfrommsdn.
NameDefaultDescription
ApplicationName Thenameoftheapplication,or'.NetSqlClientDataProvider'ifnoapplicationnameisprovided.
AttachDBFilename
-or-
extendedproperties
-or-
InitialFileName Thenameoftheprimaryfile,includingthefullpathname,ofanattachabledatabase.Thedatabasenamemustbespecifiedwiththekeyword'database'.
ConnectTimeout
-or-
ConnectionTimeout15Thelengthoftime（inseconds）towaitforaconnectiontotheserverbeforeterminatingtheattemptandgeneratinganerror.
ConnectionLifetime0Whenaconnectionisreturnedtothepool,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'.
MaxPoolSize100Themaximumnumberofconnectionsallowedinthepool.
MinPoolSize0Theminimumnumberofconnectionsallowedinthepool.
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.
PacketSize8192SizeinbytesofthenetworkpacketsusedtocommunicatewithaninstanceofSQLServer.
Password
-or-
Pwd ThepasswordfortheSQLServeraccountloggingon.
PersistSecurityInfo'false'Whensetto'false',security-sensitiveinformation,suchasthepassword,isnotreturnedaspartoftheconnectioniftheconnectionisopenorhaseverbeeninanopenstate.Resettingtheconnectionstringresetsallconnectionstringvaluesincludingthepassword.
Pooling'true'Whentrue,theSQLConnectionobjectisdrawnfromtheappropriatepool,orifnecessary,iscreatedandaddedtotheappropriatepool.
UserID TheSQLServerloginaccount.
WorkstationIDthelocalcomputernameThenameoftheworkstationconnectingtoSQLServer.
note
Use;toseparateeachproperty.
Ifanameoccursmorethanonce,thevaluefromthelastoneintheconnectionstringwillbeused.
Ifyouarebuildingyourconnectionstringinyourappusingvaluesfromuserinputfields,makesuretheusercan'tchangetheconnectionstringbyinsertinganadditionalpropertywithanothervaluewithintheuservalue.
Access
ODBC
standardSecurity:
"Driver={MicrosoftAccessDriver（*.mdb）};Dbq=C:
\mydatabase.mdb;Uid=Admin;Pwd=;"
workgroup:
"Driver={MicrosoftAccessDriver（*.mdb）};Dbq=C:
\mydatabase.mdb;SystemDB=C:
\mydatabase.mdw;"
exclusive:
"Driver={MicrosoftAccessDriver（*.mdb）};Dbq=C:
\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="
OLEDB,OleDbConnection（.NET）
standardsecurity:
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\somepath\mydb.mdb;UserId=admin;Password=;"
workgroup（systemdatabase）:
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\somepath\mydb.mdb;JetOLEDB:
SystemDatabase=system.mdw;"
withpassword:
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\somepath\mydb.mdb;JetOLEDB:
DatabasePassword=MyDbPassword;"
Oracle
ODBC
newversion:
"Driver={MicrosoftODBCforOracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;"
oldversion:
"Driver={MicrosoftODBCDriverforOracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"
OLEDB,OleDbConnection（.NET）
standardsecurity:
"Provider=msdaora;DataSource=MyOracleDB;UserId=UserName;Password=asdasd;"
thisone'sfromMicrosoft,thefollowingarefromOracle
standardSecurity:
"Provider=OraOLEDB.Oracle;DataSource=MyOracleDB;UserId=Username;Password=asdasd;"
trustedConnection:
"Provider=OraOLEDB.Oracle;DataSource=MyOracleDB;OSAuthent=1;"
OracleConnection（.NET）
standard:
"DataSource=MyOracleDB;IntegratedSecurity=yes;"
thisoneworksonlywithOracle8irelease3orlater
specifyingusernameandpassword:
"DataSource=MyOracleDB;UserId=username;Password=passwd;IntegratedSecurity=no;"
thisoneworksonlywithOracle8irelease3orlater
declaretheOracleConnection:
C#:
usingSystem.Data.OracleClient;
OracleConnectionoOracleConn=newOracleConnection（）;
oOracleConn.ConnectionString="myconnectionstring";
oOracleConn.Open（）;
VB.NET:
ImportsSystem.Data.OracleClient
DimoOracleConnAsOracleConnection=NewOracleConnection（）
oOracleConn.ConnectionString="myconnectionstring"
oOracleConn.Open（）
missingtheSystem.Data.OracleClientnamespace?
Download.NETManagedProviderforOracle»
greatarticle!
"FeaturesofOracleDataProviderfor.NET"byRamaMohanG.atC#Corner
CoreLabsOraDirect（.NET）
standard:
"UserID=scott;Password=tiger;Host=ora;Pooling=true;MinPoolSize=0;MaxPoolSize=100;ConnectionLifetime=0"
ReadmoreatCoreLabandtheproductpage.
DataShape
msDataShape:
"Provider=MSDataShape.1;PersistSecurityInfo=False;DataProvider=MSDAORA;DataSource=orac;userid=username;password=mypw"
wanttolearndatashaping?
Checkout4GuyfFromRolla'sgreatarticleaboutDataShaping>>
MySQL
ODBC
odbc2.50Localdatabase:
"Driver={mySQL};Server=localhost;Option=16834;Database=mydatabase;"
odbc2.50Remotedatabase:
"Driver={mySQL};Server=;Port=3306;Option=131072;Stmt=;Database
升级会员 