|
|
SQL
Server
- ODBC
- Standard Security:
"Driver={SQL
Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
- Trusted connection:
"Driver={SQL
Server};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;Data Source=Aron1;Initial
Catalog=pubs;User Id=sa;Password=asdasd;"
- Trusted Connection:
"Provider=sqloledb;Data Source=Aron1;Initial
Catalog=pubs;Integrated Security=SSPI;"
(use serverName\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;Data
Source=190.190.200.100,1433;Network
Library=DBMSSOCN;Initial Catalog=pubs;User
ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes,
at the end of the Data Source is the port to use (1433
is the
default))
- SqlConnection
(.NET)
- Data
Shape
- MS Data
Shape
"Provider=MSDataShape;Data
Provider=SQLOLEDB;Data Source=Aron1;Initial
Catalog=pubs;User ID=sa;Password=asdasd;"
Want to learn data shaping? Check out
4GuyfFromRolla's great
article about Data Shaping
>>
- Read
more
- How
to define wich network protocol to use
-
Example: "Provider=sqloledb;Data
Source=190.190.200.100,1433; Network
Library=DBMSSOCN;Initial Catalog=pubs;User
ID=sa;Password=asdasd;"
Name |
Network
library |
dbnmpntw |
Win32 Named Pipes |
dbmssocn |
Win32 Winsock TCP/IP |
dbmsspxn |
Win32 SPX/IPX |
dbmsvinn |
Win32 Banyan Vines |
dbmsrpcn |
Win32 Multi-Protocol (Windows
RPC) |
Important note! When
connecting through the SQLOLEDB provider use the syntax
Network Library=dbmssocn and when
connecting through MSDASQL provider use the syntax
Network=dbmssocn
- All
SqlConnection connectionstring
properties
-
This table shows all connectionstring properties
for the ADO.NET SqlConnection object. Most of the
properties are also used in ADO. All properties and
descriptions is from msdn.
Name |
Default |
Description |
Application Name |
|
The name of the application, or '.Net
SqlClient Data Provider' if no application name is
provided. |
AttachDBFilename -or- extended properties -or- Initial
File Name |
|
The name of the primary file, including the
full path name, of an attachable database. The
database name must be specified with the keyword
'database'. |
Connect Timeout -or- Connection
Timeout |
15 |
The length of time (in seconds) to wait for a
connection to the server before terminating the
attempt and generating an error. |
Connection Lifetime |
0 |
When a connection is returned to the pool, its
creation time is compared with the current time,
and the connection is destroyed if that time span
(in seconds) exceeds the value specified by
connection lifetime. Useful in clustered
configurations to force load balancing between a
running server and a server just brought
on-line. |
Connection Reset |
'true' |
Determines whether the database connection is
reset when being removed from the pool. Setting to
'false' avoids making an additional server
round-trip when obtaining a connection, but the
programmer must be aware that the connection state
is not being reset. |
Current Language |
|
The SQL Server Language record name. |
Data
Source -or- Server -or- Address -or- Addr -or- Network
Address |
|
The name or network address of the instance of
SQL Server to which to connect. |
Enlist |
'true' |
When true, the pooler automatically enlists
the connection in the creation thread's current
transaction context. |
Initial Catalog -or- Database |
|
The name of the database. |
Integrated
Security -or- Trusted_Connection |
'false' |
Whether the connection is to be a secure
connection or not. Recognized values are 'true',
'false', and 'sspi', which is equivalent to
'true'. |
Max Pool Size |
100 |
The maximum number of connections allowed in
the pool. |
Min Pool Size |
0 |
The minimum number of connections allowed in
the pool. |
Network Library -or- Net |
'dbmssocn' |
The network library used to establish a
connection to an instance of SQL Server. Supported
values include dbnmpntw (Named Pipes), dbmsrpcn
(Multiprotocol), dbmsadsn (Apple Talk), dbmsgnet
(VIA), dbmsipcn (Shared Memory) and dbmsspxn
(IPX/SPX), and dbmssocn (TCP/IP). The
corresponding 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. |
Packet Size |
8192 |
Size in bytes of the network packets used to
communicate with an instance of SQL Server. |
Password -or- Pwd |
|
The password for the SQL Server account
logging on. |
Persist Security Info |
'false' |
When set to 'false', security-sensitive
information, such as the password, is not returned
as part of the connection if the connection is
open or has ever been in an open state. Resetting
the connection string resets all 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. |
User ID |
|
The SQL Server login account. |
Workstation ID |
the local computer name |
The name of the workstation connecting to SQL
Server. |
Note Use ; to separate each
property. If a name occurs more than once, the value
from the last one in the connectionstring will be
used. If you are building your connectionstring in
your app using values from user input fields, make sure
the user can't change the connectionstring by inserting
an additional property with another value within the
user value.
Access
- ODBC
- Standard Security:
"Driver={Microsoft Access Driver
(*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"
- Workgroup:
"Driver={Microsoft Access Driver
(*.mdb)};Dbq=C:\mydatabase.mdb;SystemDB=C:\mydatabase.mdw;"
- Exclusive:
"Driver={Microsoft Access Driver
(*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="
- OLE
DB, OleDbConnection (.NET)
- Standard security:
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\somepath\mydb.mdb;User Id=admin;Password=;"
- Workgroup (system database):
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\somepath\mydb.mdb;Jet OLEDB:System
Database=system.mdw;"
- With password:
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\somepath\mydb.mdb;Jet OLEDB:Database
Password=MyDbPassword;"
Oracle
- ODBC
- OLE
DB, OleDbConnection (.NET)
- OracleConnection
(.NET)
- Standard:
"Data Source=Oracle8i;Integrated Security=yes;"
This one works only with Oracle 8i
release 3 or later
- Specifying username and password:
"Data Source=Oracle8i;User
Id=username;Password=passwd;Integrated Security=no;"
This one works only with Oracle 8i
release 3 or later
- Declare the OracleConnection:
C#: using
System.Data.OracleClient; OracleConnection
oOracleConn = new
OracleConnection(); oOracleConn.ConnectionString =
"my connectionstring"; oOracleConn.Open();
VB.NET: Imports
System.Data.OracleClient Dim oOracleConn As
OracleConnection = New
OracleConnection() oOracleConn.ConnectionString = "my
connectionstring" oOracleConn.Open()
- Core
Labs OraDirect (.NET)
- Standard:
"User ID=scott;
Password=tiger; Host=ora; Pooling=true; Min Pool
Size=0;Max Pool Size=100; Connection Lifetime=0"
Read more at Core Lab and the product
page.
- Data
Shape
- MS Data
Shape:
"Provider=MSDataShape.1;Persist Security
Info=False;Data Provider=MSDAORA;Data Source=orac;user
id=username;password=mypw"
Want to learn data shaping? Check out
4GuyfFromRolla's great
article about Data Shaping
>>
MySQL
- ODBC
- OLE
DB, OleDbConnection (.NET)
- MySqlConnection
(.NET)
- eInfoDesigns.dbProvider:
"Data Source=server;Database=mydb;User
ID=username;Password=pwd;Command Logging=false"
This one is used with eInfoDesigns
dbProvider, an add-on to .NET
- Declare the MySqlConnection:
C#: using
eInfoDesigns.dbProvider.MySqlClient; MySqlConnection
oMySqlConn = new
MySqlConnection(); oMySqlConn.ConnectionString = "my
connectionstring"; oMySqlConn.Open();
VB.NET: Imports
eInfoDesigns.dbProvider.MySqlClient Dim oMySqlConn As
MySqlConnection = New
MySqlConnection() oMySqlConn.ConnectionString = "my
connectionstring" oMySqlConn.Open()
- SevenObjects
MySqlClient (.NET)
- Standard:
"Host=server; UserName=myusername;
Password=mypassword;Database=mydb;"
This is a freeware ADO.Net data provider
from SevenObjects
- Core
Labs MySQLDirect (.NET)
- Standard:
"User ID=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 more at Core Lab and the product
page.
Interbase
- ODBC,
Easysoft
- ODBC,
Intersolv
- OLE
DB, SIBPROvider
- Standard:
"provider=sibprovider;location=localhost:;data
source=c:\databases\gdbs\mygdb.gdb;user
id=SYSDBA;password=masterkey"
- Specifying character set:
"provider=sibprovider;location=localhost:;data
source=c:\databases\gdbs\mygdb.gdb;user
id=SYSDBA;password=masterkey;character set=ISO8859_1"
- Specifying role:
"provider=sibprovider;location=localhost:;data
source=c:\databases\gdbs\mygdb.gdb;user
id=SYSDBA;password=masterkey;role=DIGITADORES"
Read
more about SIBPROvider >>
Read
more about connecting to Interbase in this Borland Developer
Network article http://community.borland.com/article/0,1410,27152,00.html
IBM
DB2
- OLE
DB, OleDbConnection (.NET) from ms
- TCP/IP:
"Provider=DB2OLEDB;Network Transport
Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial
Catalog=MyCtlg;Package Collection=MyPkgCol;Default
Schema=Schema;User ID=MyUser;Password=MyPW"
- APPC:
"Provider=DB2OLEDB;APPC Local LU Alias=MyAlias;APPC
Remote LU Alias=MyRemote;Initial Catalog=MyCtlg;Package
Collection=MyPkgCol;Default Schema=Schema;User
ID=MyUser;Password=MyPW"
- ODBC
Sybase
- ODBC
- OLE
DB
- Adaptive Server Anywhere (ASA):
- Adaptive Server Enterprise (ASE) with Data
Source .IDS file:
"Provider=Sybase ASE OLE DB Provider; Data
source=myASE"
Note that you must create a Data Source
.IDS file using the Sybase Data Administrator. These
.IDS files resemble ODBC DSNs.
- Adaptive Server Enterprise (ASE):
"Provider=Sybase.ASEOLEDBProvider;Srvr=myASEserver,5000;Catalog=myDBname;User
Id=username;Password=password" -
some reports on problem using the above one, try the
following as an alternative
-"Provider=Sybase.ASEOLEDBProvider;Server
Name=myASEserver,5000;Initial Catalog=myDBname;User
Id=username;Password=password"
This one works only from Open Client 12.5
where the server port number feature works, allowing
fully qualified connection strings to be used without
defining any .IDS Data Source files.
- AseConnection
(.NET)
Informix
- ODBC
- Informix 3.30:
"Dsn='';Driver={INFORMIX 3.30 32
BIT};Host=hostname;Server=myserver;Service=service-name;Protocol=olsoctcp;Database=mydb;UID=username;PWD=myPwd
- Informix-CLI 2.5:
"Driver={Informix-CLI 2.5 (32
Bit)};Server=myserver;Database=mydb;Uid=username;Pwd=myPwd"
- OLE
DB
Mimer
SQL
PostgreSQL
- Core
Labs PostgreSQLDirect (.NET)
- Standard:
"User ID=root; Password=pwd; Host=localhost;
Port=5432; Database=testdb;Pooling=true; Min Pool Size=0;
Max Pool Size=100; Connection Lifetime=0"
Read more at Core Lab and the product
page.
DSN
Excel
- ODBC
- OLE
DB
- Standard:
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\MyExcel.xls;Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row
contains columnnames, not data
"IMEX=1;" tells the driver to always read
"intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM
[sheet1$]" - i.e. worksheet name followed by a "$" and
wrapped in "[" "]"
brackets.
Text
- ODBC
- OLE
DB
- Standard:
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\txtFilesFolder\;Extended
Properties=""text;HDR=Yes;FMT=Delimited"""
"HDR=Yes;" indicates that the first row
contains columnnames, not
data
DBF
/ FoxPro
- ODBC
- OLE
DB, OleDbConnection (.NET)
Visual
FoxPro
- OLE
DB, OleDbConnection (.NET)
- Database container (.DBC):
"Provider=vfpoledb.1;Data
Source=C:\MyDbFolder\MyDbContainer.dbc;Password=MyPassWord;Collating
Sequence=machine"
- Free table directory:
"Provider=vfpoledb.1;Data
Source=C:\MyDataDirectory\;Password=MyPassWord;Collating
Sequence=general"
Read
more (Microsoft msdn)
>>
- ODBC
Microsoft
Visual Foxpro site: http://msdn.microsoft.com/vfoxpro
Pervasive
UDL
|
Expand all Collapse all
|
| |
|
|
|