MSSQL编程：数据库毗连集锦
MySQL的海豚标志的名字叫“sakila”，它是由MySQLAB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者AmbroseTwebaze提供。数据|数据库|数据库毗连iExpandall
CollapseallSQLServerODBC
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;"(useserverNameinstanceNameasDataSourcetouseanspecifikSQLServerinstance,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)
(useserverNameinstanceNameasDataSourcetouseanspecifikSQLServerinstance,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;"
Readmore
Howtodefinewichnetworkprotocoltouse
Example:
"Provider=sqloledb;DataSource=190.190.200.100,1433;NetworkLibrary=DBMSSOCN;InitialCatalog=pubs;UserID=sa;Password=asdasd;"
NameNetworklibrarydbnmpntwWin32NamedPipesdbmssocnWin32WinsockTCP/IPdbmsspxnWin32SPX/IPXdbmsvinnWin32BanyanVinesdbmsrpcnWin32Multi-Protocol(WindowsRPC)
Importantnote!
WhenconnectingthroughtheSQLOLEDBproviderusethesyntaxNetworkLibrary=dbmssocn
andwhenconnectingthroughMSDASQLproviderusethesyntaxNetwork=dbmssocn
AllSqlConnectionconnectionstringproperties
ThistableshowsallconnectionstringpropertiesfortheADO.NETSqlConnectionobject.MostofthepropertiesarealsousedinADO.Allpropertiesanddescriptionsisfrommsdn.
NameDefaultDescriptionApplicationNameThenameoftheapplication,or.NetSqlClientDataProviderifnoapplicationnameisprovided.AttachDBFilename
-or-
extendedproperties
-or-
InitialFileNameThenameoftheprimaryfile,includingthefullpathname,ofanattachabledatabase.Thedatabasenamemustbespecifiedwiththekeyworddatabase.ConnectTimeout
-or-
ConnectionTimeout15Thelengthoftime(inseconds)towaitforaconnectiontotheserverbeforeterminatingtheattemptandgeneratinganerror.ConnectionLifetime0Whenaconnectionisreturnedtothepool,itscreationtimeiscomparedwiththecurrenttime,andtheconnectionisdestroyedifthattimespan(inseconds)exceedsthevaluespecifiedbyconnectionlifetime.Usefulinclusteredconfigurationstoforceloadbalancingbetweenarunningserverandaserverjustbroughton-line.ConnectionResettrueDetermineswhetherthedatabaseconnectionisresetwhenbeingremovedfromthepool.Settingtofalseavoidsmakinganadditionalserverround-tripwhenobtainingaconnection,buttheprogrammermustbeawarethattheconnectionstateisnotbeingreset.CurrentLanguageTheSQLServerLanguagerecordname.DataSource
-or-
Server
-or-
Address
-or-
Addr
-or-
NetworkAddressThenameornetworkaddressoftheinstanceofSQLServertowhichtoconnect.EnlisttrueWhentrue,thepoolerautomaticallyenliststheconnectioninthecreationthreadscurrenttransactioncontext.InitialCatalog
-or-
DatabaseThenameofthedatabase.IntegratedSecurity
-or-
Trusted_ConnectionfalseWhethertheconnectionistobeasecureconnectionornot.Recognizedvaluesaretrue,false,andsspi,whichisequivalenttotrue.MaxPoolSize100Themaximumnumberofconnectionsallowedinthepool.MinPoolSize0Theminimumnumberofconnectionsallowedinthepool.NetworkLibrary
-or-
NetdbmssocnThenetworklibraryusedtoestablishaconnectiontoaninstanceofSQLServer.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-
PwdThepasswordfortheSQLServeraccountloggingon.PersistSecurityInfofalseWhensettofalse,security-sensitiveinformation,suchasthepassword,isnotreturnedaspartoftheconnectioniftheconnectionisopenorhaseverbeeninanopenstate.Resettingtheconnectionstringresetsallconnectionstringvaluesincludingthepassword.PoolingtrueWhentrue,theSQLConnectionobjectisdrawnfromtheappropriatepool,orifnecessary,iscreatedandaddedtotheappropriatepool.UserIDTheSQLServerloginaccount.WorkstationIDthelocalcomputernameThenameoftheworkstationconnectingtoSQLServer.
Note
Use;toseparateeachproperty.
Ifanameoccursmorethanonce,thevaluefromthelastoneintheconnectionstringwillbeused.
Ifyouarebuildingyourconnectionstringinyourappusingvaluesfromuserinputfields,makesuretheusercantchangetheconnectionstringbyinsertinganadditionalpropertywithanothervaluewithintheuservalue.AccessODBC
StandardSecurity:
"Driver={MicrosoftAccessDriver(*.mdb)};Dbq=C:mydatabase.mdb;Uid=Admin;Pwd=;"
Workgroup:
"Driver={MicrosoftAccessDriver(*.mdb)};Dbq=C:mydatabase.mdb;SystemDB=C:mydatabase.mdw;","admin",""
Exclusive:
"Driver={MicrosoftAccessDriver(*.mdb)};Dbq=C:mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="
OLEDB,OleDbConnection(.NET)
Standardsecurity:
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=omepathmydb.mdb;UserId=admin;Password=;"
Workgroup(systemdatabase):
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=omepathmydb.mdb;JetOLEDB:SystemDatabase=system.mdw;","admin",""
Withpassword:
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=omepathmydb.mdb;JetOLEDB:DatabasePassword=MyDbPassword;","admin",""
OracleODBC
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;"ThisonesfromMicrosoft,thefollowingarefromOracleStandardSecurity:
"Provider=OraOLEDB.Oracle;DataSource=MyOracleDB;UserId=Username;Password=asdasd;"
TrustedConnection:
"Provider=OraOLEDB.Oracle;DataSource=MyOracleDB;OSAuthent=1;"
OracleConnection(.NET)
Standard:
"DataSource=Oracle8i;IntegratedSecurity=yes";ThisoneworksonlywithOracle8irelease3orlaterDeclaretheOracleConnection:
C#:
usingSystem.Data.OracleClient;
OracleConnectionoOracleConn=newOracleConnection();
oOracleConn.ConnectionString="myconnectionstring";
oOracleConn.Open();
VB.NET:
ImportsSystem.Data.OracleClient
DimoOracleConnAsOracleConnection=NewOracleConnection()
oOracleConn.ConnectionString="myconnectionstring"
oOracleConn.Open()
DataShape
MSDataShape:
"Provider=MSDataShape.1;PersistSecurityInfo=False;DataProvider=MSDAORA;DataSource=orac;userid=username;password=mypw"
MySQLODBC
Localdatabase:
"Driver={mySQL};Server=mySrvName;Option=16834;Database=mydatabase;"
Remotedatabase:
"Driver={mySQL};Server=data.domain.com;Port=3306;Option=131072;Stmt=;Database=my-database;Uid=username;Pwd=password;"
OLEDB,OleDbConnection(.NET)
Standard:
"Provider=MySQLProv;DataSource=mydb;UserId=UserName;Password=asdasd;"MySqlConnection(.NET)
eInfoDesigns.dbProvider:
"DataSource=server;Database=mydb;UserID=username;Password=pwd;CommandLogging=false"ThisoneisusedwitheInfoDesignsdbProvider,anadd-onto.NETDeclaretheMySqlConnection:
C#:
usingeInfoDesigns.dbProvider.MySqlClient;
MySqlConnectionoMySqlConn=newMySqlConnection();
oMySqlConn.ConnectionString="myconnectionstring";
oMySqlConn.Open();
VB.NET:
ImportseInfoDesigns.dbProvider.MySqlClient
DimoMySqlConnAsMySqlConnection=NewMySqlConnection()
oMySqlConn.ConnectionString="myconnectionstring"
oMySqlConn.Open()
InterbaseODBC,Easysoft
Localcomputer:
"Driver={EasysoftIB6ODBC};Server=localhost;Database=localhost:C:mydatabase.gdb;Uid=username;Pwd=password"
RemoteComputer:
"Driver={EasysoftIB6ODBC};Server=ComputerName;Database=ComputerName:C:mydatabase.gdb;Uid=username;Pwd=password"Readmoreaboutthisdriver:EasysoftODBC-Interbasedriver>>
ODBC,Intersolv
Localcomputer:
"Driver={INTERSOLVInterBaseODBCDriver(*.gdb)};Server=localhost;Database=localhost:C:mydatabase.gdb;Uid=username;Pwd=password"
RemoteComputer:
"Driver={INTERSOLVInterBaseODBCDriver(*.gdb)};Server=ComputerName;Database=ComputerName:C:mydatabase.gdb;Uid=username;Pwd=password"ThisdriverareprovidedbyDataDirectTechnologies>>(formerlyIntersolv)
ReadmoreaboutconnectingtoInterbaseinthisBorlandDeveloperNetworkarticlehttp://community.borland.com/article/0,1410,27152,00.html
IBMDB2OLEDB,OleDbConnection(.NET)fromms
TCP/IP:
"Provider=DB2OLEDB;NetworkTransportLibrary=TCPIP;NetworkAddress=XXX.XXX.XXX.XXX;InitialCatalog=MyCtlg;PackageCollection=MyPkgCol;DefaultSchema=Schema;UserID=MyUser;Password=MyPW"
APPC:
"Provider=DB2OLEDB;APPCLocalLUAlias=MyAlias;APPCRemoteLUAlias=MyRemote;InitialCatalog=MyCtlg;PackageCollection=MyPkgCol;DefaultSchema=Schema;UserID=MyUser;Password=MyPW"
SybaseODBC
Standard:
"Driver={SYBASESYSTEM11};Srvr=Aron1;Uid=username;Pwd=password;"
Intersolv3.10:
"Driver={INTERSOLV3.1032-BITSybase};Srvr=Aron1;Uid=username;Pwd=password;"
InformixODBC
Informix3.30:
"Dsn=;Driver={INFORMIX3.3032BIT};Host=hostname;Server=myserver;Service=service-name;Protocol=olsoctcp;Database=mydb;UID=username;PWD=myPwd
Informix-CLI2.5:
"Driver={Informix-CLI2.5(32Bit)};Server=myserver;Database=mydb;Uid=username;Pwd=myPwd"
OLEDB
IBMInformixOLEDBProvider:
"Provider=Ifxoledbc.2;password=myPw;UserID=myUser;DataSource=dbName@serverName;PersistSecurityInfo=true"
MimerSQLODBC
StandardSecurity:
"Driver={MIMER};Database=mydb;Uid=myuser;Pwd=mypw;"
Promptforusernameandpassword:
"Driver={MIMER};Database=mydb;"
DSNODBC
DSN:
"DSN=myDsn;Uid=username;Pwd=;"
FileDSN:
"FILEDSN=c:myData.dsn;Uid=username;Pwd=;"
ExcelODBC
Standard:
"Driver={MicrosoftExcelDriver(*.xls)};DriverId=790;Dbq=C:MyExcel.xls;DefaultDir=c:mypath;"
OLEDB
Standard:
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:MyExcel.xls;ExtendedProperties=""Excel8.0;HDR=Yes;IMEX=1""""HDR=Yes;"indicatesthatthefirstrowcontainscolumnnames,notdata"IMEX=1;"tellsthedrivertoalwaysread"intermixed"datacolumnsastextTextODBC
Standard:
"Driver={MicrosoftTextDriver(*.txt;*.csv)};Dbq=c: xtFilesFolder;Extensions=asc,csv,tab,txt;"
OLEDB
Standard:
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c: xtFilesFolder;ExtendedProperties=""text;HDR=Yes;FMT=Delimited""""HDR=Yes;"indicatesthatthefirstrowcontainscolumnnames,notdataDBF/FoxProODBC
standard:
"Driver={MicrosoftdBASEDriver(*.dbf)};DriverID=277;Dbq=c:mydbpath;"
OLEDB,OleDbConnection(.NET)
standard:
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:folder;ExtendedProperties=dBASEIV;UserID=Admin;Password="
VisualFoxProOLEDB,OleDbConnection(.NET)
standard:
"Provider=vfpoledb.1;DataSource=C: estvfp.dbc"Readmore>>ODBC
VisualFoxprodatabase(.DBC):
"Driver={MicrosoftVisualFoxProDriver};SourceType=DBC;SourceDB=c:myvfpdb.dbc;Exclusive=No;Collate=Machine;"
FreeTabledirectory:
"Driver={MicrosoftVisualFoxProDriver};SourceType=DBF;SourceDB=c:myvfpdbfolder;Exclusive=No;Collate=Machine;""Collate=Machine"isthedefaultsetting,forothersettingscheckthelistofsupportedcollatingsequences>>PervasiveODBC
Standard:
"Driver={PervasiveODBCClientInterface};ServerName=srvname;dbq=@dbname"PervasiveODBCinfo>>OLEDB
Standard:
"Provider=PervasiveOLEDB;DataSource=C:path"PervasiveOLEDBinfo>>UDLUDL
UDL:
"FileName=c:myDataLink.udl;"
mysql使用内部操作字符集gbk来进行操作，即执行"SELECT*FROMtestWHEREname=xxxor1=1/*LIMIT1";从而注入成功 可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。 对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。 SP4是一个累积性的ServicePack，包含自以前的ServicePack发布以来所有的修补程序（包括MS03-031安全公告）。 财务软件要用SQL也只是后台的数据库而已,软件都是成品的,当然多学东西肯定是有好处的.. 所以你总能得到相应的升级版本，来满足你的需求。 理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识 但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。
页:
[1]