数据库连接字符串大全

来源：互联网发布：创意文案知乎编辑：程序博客网时间：2024/05/03 12:24

数据库连接字符串大全

Oracle

ODBC

New version:

"Driver={MicrosoftODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;"

Old version:

"Driver={MicrosoftODBC Driver for Oracle}; ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"

OLE DB, OleDbConnection (.NET)

Standard security:

"Provider=msdaora;DataSource=MyOracleDB;User Id=UserName;Password=asdasd;"

Thisone's from Microsoft, the following are from Oracle

Standard Security:

"Provider=OraOLEDB.Oracle;DataSource=MyOracleDB;User Id=Username;Password=asdasd;"

Trusted Connection:

"Provider=OraOLEDB.Oracle;DataSource=MyOracleDB;OSAuthent=1;"

OracleConnection (.NET)

Standard:

"DataSource=MyOracleDB;Integrated Security=yes;"

This oneworks only with Oracle 8i release 3 or later

Specifying username and password:

"DataSource=MyOracleDB;User Id=username;Password=passwd;IntegratedSecurity=no;"

This oneworks only with Oracle 8i release 3 or later

Declare the OracleConnection:

C#:

usingSystem.Data.OracleClient;

OracleConnectionoOracleConn = new OracleConnection();

oOracleConn.ConnectionString= "my connection string";

oOracleConn.Open();

VB.NET:

ImportsSystem.Data.OracleClient

DimoOracleConn As OracleConnection = New OracleConnection()

oOracleConn.ConnectionString= "my connection string"

oOracleConn.Open()

Missingthe System.Data.OracleClient namespace? Download .NET Managed Provider forOracle >>

Greatarticle! "Features of Oracle Data Provider for .NET" by Rama Mohan G.at C# Corner

Core Labs OraDirect (.NET)

Standard:

"UserID=scott; Password=tiger; Host=ora; Pooling=true; Min Pool Size=0;Max PoolSize=100; Connection Lifetime=0"

Read moreat Core Lab and the product page.

Data Shape

MS Data Shape:

"Provider=MSDataShape.1;PersistSecurity Info=False;Data Provider=MSDAORA;Data Source=orac;userid=username;password=mypw"

Want tolearn data shaping? Check out 4GuyfFromRolla's great article about Data Shaping>>

SQL Server

ODBC

Standard Security:

"Driver={SQLServer};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"

Trusted connection:

"Driver={SQLServer};Server=Aron1;Database=pubs;Trusted_Connection=yes;"

PRompt for username and passWord:

oConn.Properties("Prompt")= adPromptAlways

oConn.Open"Driver={SQL Server};Server=Aron1;DataBase=pubs;"

OLE DB, OleDbConnection (.NET)

Standard Security:

"Provider=sqloledb;DataSource=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"

Trusted Connection:

"Provider=sqloledb;DataSource=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

(useserverName/instanceName as Data Source to use an specifik SQLServer instance,only SQLServer2000)

Prompt for username and password:

oConn.Provider= "sqloledb"

oConn.Properties("Prompt")= adPromptAlways

oConn.Open"Data Source=Aron1;Initial Catalog=pubs;"

Connect via an ip address:

"Provider=sqloledb;DataSource=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;UserID=sa;Password=asdasd;"

(DBMSSOCN=TCP/IPinstead of Named Pipes, at the end of the Data Source is the port to use (1433is the default))

SqlConnection (.NET)

Standard Security:

"DataSource=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"

- or -

"Server=Aron1;Database=pubs;UserID=sa;Password=asdasd;Trusted_Connection=False"

(both connection strings produces the sameresult)

Trusted Connection:

"DataSource=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

- or -

"Server=Aron1;Database=pubs;Trusted_Connection=True;"

(both connection strings produces the sameresult)

(useserverName/instanceName as Data Source to use an specifik SQLServer instance,only SQLServer2000)

Connect via an IP address:

"DataSource=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;UserID=sa;Password=asdasd;"

(DBMSSOCN=TCP/IPinstead of Named Pipes, at the end of the Data Source is the port to use (1433is the default))

Declare the SqlConnection:

C#:

usingSystem.Data.SqlClient;

SqlConnectionoSQLConn = new SqlConnection();

oSQLConn.ConnectionString="myconnection string";

oSQLConn.Open();

VB.NET:

ImportsSystem.Data.SqlClient

DimoSQLConn As SqlConnection = New SqlConnection()

oSQLConn.ConnectionString="myconnection string"

oSQLConn.Open()

Data Shape

MS Data Shape

"Provider=MSDataShape;DataProvider=SQLOLEDB;Data Source=Aron1;Initial Catalog=pubs;UserID=sa;Password=asdasd;"

Want tolearn data shaping? Check out 4GuyfFromRolla's great article about Data Shaping>>

How to define which network protocol to use

Example:

"Provider=sqloledb;DataSource=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;UserID=sa;Password=asdasd;"

NameNetwork library

dbnmpntwWin32 Named Pipes

dbmssocnWin32 Winsock TCP/IP

dbmsspxnWin32 SPX/IPX

dbmsvinnWin32 Banyan Vines

dbmsrpcnWin32 Multi-Protocol (Windows RPC)

Importantnote!

Whenconnecting through the SQLOLEDB provider use the syntax NetworkLibrary=dbmssocn

and whenconnecting through MSDASQL provider use the syntax Network=dbmssocn

All SqlConnection connection string properties

Thistable shows all connection string properties for the ADO.NET SqlConnectionobject. Most of the properties are also used in ADO. All properties and descriptions is frommsdn.

NameDefault Description

applicationName The name of the application, or'.Net SqlClient Data Provider' if no application name is provided.

AttachDBFilename

-or-

extendedproperties

-or-

InitialFile Name The name of the primary file,including the full path name, of an attachable database. The database name mustbe specified with the keyword 'database'.

ConnectTimeout

-or-

ConnectionTimeout 15 The length of time (in seconds) to wait for a connection to theserver before terminating the attempt and generating an error.

ConnectionLifetime 0 When a connection is returned to the pool, its creation time iscompared with the current time, and the connection is destroyed if that timespan (in seconds) exceeds the value specified by connection lifetime. Useful inclustered configurations to force load balancing between a running server and aserver just brought on-line.

ConnectionReset 'true' Determines whether the database connection is reset when beingremoved from the pool. Setting to 'false' avoids making an additional serverround-trip when obtaining a connection, but the programmer must be aware thatthe connection state is not being reset.

CurrentLanguage The SQL Server Language recordname.

DataSource

-or-

Server

-or-

Address

-or-

Addr

-or-

NetworkAddress The name or network address ofthe instance of SQL Server to which to connect.

Enlist'true' When true, the pooler automatically enlists the connection in thecreation thread's current transaction context.

InitialCatalog

-or-

Database The name of the database.

IntegratedSecurity

-or-

Trusted_Connection'false' Whether the connection is to be a secure connection or not. Recognizedvalues are 'true', 'false', and 'sspi', which is equivalent to 'true'.

Max PoolSize 100 The maximum number of connections allowed in the pool.

Min PoolSize 0 The minimum number of connections allowed in the pool.

NetworkLibrary

-or-

Net'dbmssocn' The network library used to establish a connection to an instance ofSQL Server. Supported values include dbnmpntw (Named Pipes), dbmsrpcn(Multiprotocol), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmsipcn (SharedMemory) and dbmsspxn (IPX/SPX), and dbmssocn (TCP/IP).

Thecorresponding network DLL must be installed on the system to which you connect.If you do not specify a network and you use a local server (for example,"." or "(local)"), shared memory is used.

PacketSize 8192 Size in bytes of the network packets used to communicate with aninstance of SQL Server.

Password

-or-

Pwd The password for the SQL Server accountlogging on.

PersistSecurity Info 'false' When set to 'false', security-sensitive information, suchas the password, is not returned as part of the connection if the connection isopen or has ever been in an open state. Resetting the connection string resetsall connection string values including the password.

Pooling'true' When true, the SQLConnection object is drawn from the appropriate pool,or if necessary, is created and added to the appropriate pool.

UserID The SQL Server login account.

WorkstationID the local computer name The name of the workstation connecting to SQLServer.

Note

Use ; toseparate each property.

If a nameoccurs more than once, the value from the last one in the connection stringwill be used.

If youare building your connection string in your app using values from user inputfields, make sure the user can't change the connection string by inserting anadditional property with another value within the user value.

SQL Server 2005

SQL Native Client ODBC Driver

Standard security:

"Driver={SQLNative Client};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"

Trusted connection:

"Driver={SQLNative Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;"

Equivalents

IntegratedSecurity=SSPI equals Trusted_Connection=yes

Prompt for username and password:

oConn.Properties("Prompt")= adPromptAlways

oConn.Open"Driver={SQL Native Client};Server=Aron1;DataBase=pubs;"

Enabling MARS (multiple active result sets):

"Driver={SQLNativeClient};Server=Aron1;Database=pubs;Trusted_Connection=yes;MARS_Connection=yes"

Equivalents

MultipleActiveResultSets=trueequals MARS_Connection=yes

UsingMARS with SQL Native Client, by Chris Lee >>

Encrypt data sent over network:

"Driver={SQLNativeClient};Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"

Attach a database file on connect to a localSQL Server Express instance:

"Driver={SQLNative Client};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| when your database fileresides in the data directory)

Why isthe "Database" parameter needed? Answer: If the database waspreviously attached, SQL Server does not reattach it (it uses the attacheddatabase as the default for the connection).

Downloadthe SQL Native Client here >> (the package contains booth the ODBC driverand the OLE DB provider)

Using SQLServer 2005 Express? Don't miss the server name syntax: SERVERNAME/SQLEXPRESS(Substitute "SERVERNAME" with the name of the computer)

SQL Native Client OLE DB Provider

Standard security:

"Provider=SQLNCLI;Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"

Trusted connection:

"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;"

Equivalents

IntegratedSecurity=SSPI equals Trusted_Connection=yes

Prompt for username and password:

oConn.Properties("Prompt")= adPromptAlways

oConn.Open"Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"

Enabling MARS (multiple active result sets):

"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;MarsConn=yes"

Equivalents

MarsConn=yesequals MultipleActiveResultSets=true equals MARS_Connection=yes

UsingMARS with SQL Native Client, by Chris Lee >>

Encrypt data sent over network:

"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"

Attach a database file on connect to a localSQL Server Express instance:

"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| when your database fileresides in the data directory)

Why isthe "Database" parameter needed? Answer: If the database waspreviously attached, SQL Server does not reattach it (it uses the attacheddatabase as the default for the connection).

Downloadthe SQL Native Client here >> (the package contains booth the ODBC driverand the OLE DB provider)

Using SQLServer 2005 Express? Don't miss the server name syntax: SERVERNAME/SQLEXPRESS(Substitute "SERVERNAME" with the name of the computer)

SqlConnection (.NET)

Standard Security:

"DataSource=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"

- or -

"Server=Aron1;Database=pubs;UserID=sa;Password=asdasd;Trusted_Connection=False"

(both connection strings produces the sameresult)

Trusted Connection:

"DataSource=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

- or -

"Server=Aron1;Database=pubs;Trusted_Connection=True;"

(both connection strings produces the sameresult)

(useserverName/instanceName as Data Source to use an specifik SQLServer instance)

Connect via an IP address:

"DataSource=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;UserID=sa;Password=asdasd;"

(DBMSSOCN=TCP/IPinstead of Named Pipes, at the end of the Data Source is the port to use (1433is the default))

Enabling MARS (multiple active result sets):

"Server=Aron1;Database=pubs;Trusted_Connection=True;MultipleActiveResultSets=true"

Note! UseADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 norADO.NET 1.1

Streamlineyour Data Connections by Moving to MARS, by Laurence Moroney, DevX.com >>

Attach a database file on connect to a localSQL Server Express instance:

"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;"

(use |DataDirectory| when your database fileresides in the data directory)

Why isthe "Database" parameter needed? Answer: If the database waspreviously attached, SQL Server does not reattach it (it uses the attacheddatabase as the default for the connection).

Using "User Instance" on a local SQLServer Express instance:

"DataSource=./SQLExpress;integratedsecurity=true;attachdbfilename=|DataDirectory|/mydb.mdf;userinstance=true;"

The"User Instance" functionality creates a new SQL Server instance onthe fly during connect. This works only on a local SQL Server 2005 instance andonly when connecting using windows authentication over local named pipes. Thepurpose is to be able to create a full rights SQL Server instance to a userwith limited administrative rights on the computer. To enable thefunctionality: sp_configure 'user instances enabled','1' (0 to disable)

Using SQLServer 2005 Express? Don't miss the server name syntax: SERVERNAME/SQLEXPRESS(Substitute "SERVERNAME" with the name of the computer)

Context Connection - connecting to"self" from within your CLR stored prodedure/function

C#:

using(SqlConnectionconnection = new SqlConnection("context connection=true"))

{

connection.Open();

// Use the connection

}

Visual Basic:

Usingconnection as new SqlConnection("context connection=true")

connection.Open()

' Use the connection

End Using

Thecontext connection lets you execute Transact-SQL statements in the same context(connection) that your code was invoked in the first place.

When to use SQL Native Client?

.Net applications

Do notuse the SQL Native Client. Use the .NET Framework Data Provider for SQL Server(SqlConnection).

COM applications, all other then .Netapplications

Use theSQL Native Client if you are accessing an SQL Server 2005 and need the newfeatures of SQL Server 2005 such as MARS, encryption, xml data type etc.Continue use your current provider (OLE DB / ODBC through the MDAC package) ifyou are not connecting to an SQL Server 2005 (that's quite obvious eh..) or ifyou are connecting to an SQL Server 2005 but are not using any of the new SQLServer 2005 features.

For moredetails on the differences between MDAC and SQL Native Client, read this msdnarticle >>

Access

ODBC

Standard Security:

"Driver={MicrosoftAccess Driver (*.mdb)};Dbq=C:/mydatabase.mdb;Uid=Admin;Pwd=;"

Workgroup:

"Driver={MicrosoftAccess Driver (*.mdb)};Dbq=C:/mydatabase.mdb;SystemDB=C:/mydatabase.mdw;"

Exclusive:

"Driver={MicrosoftAccess Driver (*.mdb)};Dbq=C:/mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="

OLE DB, OleDbConnection (.NET)

Standard security:

"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=/somepath/mydb.mdb;User Id=admin;Password=;"

Workgroup (system database):

"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=/somepath/mydb.mdb;Jet OLEDB:System Database=system.mdw;"

With password:

"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=/somepath/mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"

MySQL

MyODBC

MyODBC 2.50 Local database:

"Driver={mySQL};Server=localhost;Option=16834;Database=mydatabase;"

MyODBC 2.50 Remote database:

"Driver={mySQL};Server=data.domain.com;Port=3306;Option=131072;Stmt=;Database=my-database;Uid=username;Pwd=password;"

MyODBC 3.51 Local database:

"DRIVER={MySQLODBC 3.51 Driver};SERVER=localhost;DATABASE=myDatabase;USER=myUsername;PASSWORD=myPassword;OPTION=3;"

MyODBC 3.51 Remote database:

"DRIVER={MySQLODBC 3.51 Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUsername;PASSWORD=myPassword;OPTION=3;"

OLE DB, OleDbConnection (.NET)

Standard:

"Provider=MySQLProv;DataSource=mydb;User Id=UserName;Password=asdasd;"

Connector/Net 1.0 (.NET)

Standard:

"Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;"

Downloadthe driver at MySQL Developer Zone.

Specifying port:

"Server=Server;Port=1234;Database=Test;Uid=UserName;Pwd=asdasd;"

Defaultport is 3306. Enter value -1 to use a named pipe connection.

Declare the MySqlClient connection:

C#:

usingMySql.Data.MySqlClient;

MySqlConnectionoMySqlConn = new MySqlConnection();

oMySqlConn.ConnectionString= "Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;";

oMySqlConn.Open();

VB.NET:

ImportsMySql.Data.MySqlClient

DimoMySqlConn As MySqlConnection = New MySqlConnection()

oMySqlConn.ConnectionString= "Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;"

oMySqlConn.Open()

MySqlConnection (.NET)

eInfoDesigns.dbProvider:

"DataSource=server;Database=mydb;User ID=username;Password=pwd;CommandLogging=false"

This oneis used with eInfoDesigns dbProvider, an add-on to .NET

Declare the MySqlConnection:

C#:

usingeInfoDesigns.dbProvider.MySqlClient;

MySqlConnectionoMySqlConn = new MySqlConnection();

oMySqlConn.ConnectionString= "my connection string";

oMySqlConn.Open();

VB.NET:

ImportseInfoDesigns.dbProvider.MySqlClient

DimoMySqlConn As MySqlConnection = New MySqlConnection()

oMySqlConn.ConnectionString= "my connection string"

oMySqlConn.Open()

SevenObjects MySqlClient (.NET)

Standard:

"Host=server;UserName=myusername; Password=mypassword;Database=mydb;"

This is afreeware ADO.Net data provider from SevenObjects

Core Labs MySQLDirect (.NET)

Standard:

"UserID=root; Password=pwd; Host=localhost; Port=3306; Database=test;Direct=true;Protocol=TCP; Compress=false; Pooling=true; Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0"

Read moreat Core Lab and the product page.

Interbase

ODBC, Easysoft

Local computer:

"Driver={EasysoftIB6ODBC};Server=localhost;Database=localhost:C:/mydatabase.gdb;Uid=username;Pwd=password"

Remote Computer:

"Driver={EasysoftIB6ODBC};Server=ComputerName;Database=ComputerName:C:/mydatabase.gdb;Uid=username;Pwd=password"

Read moreabout this driver: Easysoft ODBC-Interbase driver >>

ODBC, Intersolv

Local computer:

"Driver={INTERSOLVInterBase ODBC Driver(*.gdb)};Server=localhost;Database=localhost:C:/mydatabase.gdb;Uid=username;Pwd=password"

Remote Computer:

"Driver={INTERSOLVInterBase ODBC Driver(*.gdb)};Server=ComputerName;Database=ComputerName:C:/mydatabase.gdb;Uid=username;Pwd=password"

Thisdriver are provided by DataDirect Technologies >> (formerly Intersolv)

OLE DB, SIBPROvider

Standard:

"provider=sibprovider;location=localhost:;datasource=c:/databases/gdbs/mygdb.gdb;user id=SYSDBA;password=masterkey"

Specifying character set:

"provider=sibprovider;location=localhost:;datasource=c:/databases/gdbs/mygdb.gdb;user id=SYSDBA;password=masterkey;characterset=ISO8859_1"

Specifying role:

"provider=sibprovider;location=localhost:;datasource=c:/databases/gdbs/mygdb.gdb;userid=SYSDBA;password=masterkey;role=DIGITADORES"