. NET various database connection strings Daquan

Connection string for the database

Connecting databases is the first and most important step in database application development in various application development of the database.

The spelling of the database connection string, however, depends primarily on 4 conditions:

1. Types of Connected databases: SQL server,oracle,mysql,acess,mogodb,visual FoxPro (DBASE), Excel
2. Database access interface type: Odbc,ole DB
3. Connected mode: Standard connection (STD), trusted connection (Trusted connection)
4. Connection mode: Tcp/ip,named Pipes

SQL Server

· Odbc

o Standard Connection (Security):

"Driver={sql Server}; Server=aron1;database=pubs; Uid=sa; PWD=ASDASD; "

1) When the server is local, server can use (local);

"Driver={sqlserver}; server= (local);D atabase=pubs; Uid=sa; PWD=ASDASD; "

2) When connecting to a remote server, specify the address, port number, and network library

"Driver={sql Server}; server=130.120.110.001; address=130.120.110.001,1052; Network=dbmssocn;database=pubs; Uid=sa; PWD=ASDASD; "

Note: The address parameter must be an IP address and must include a port number

o Trusted connection (Trusted connection): (Microsoft Windows NT integrates security)

"Driver={sql Server}; Server=aron1;database=pubs; Trusted_connection=yes; "

Or

"Driver={sql Server}; Server=aron1;database=pubs; uid=; pwd=; "

o Enter the user name and Password dialog box when connecting:

Conn.properties ("Prompt") = adPromptAlways

Conn.Open "Driver={sql Server}; Server=aron1;database=pubs; "

· OLE DB, OleDbConnection (. NET)

o Standard Connection (Security):

"Provider=sqloledb;data source=aron1;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "

o Trust connection (Trusted connection):

"Provider=sqloledb;data source=aron1;initial catalog=pubs;integrated Security=sspi;"

(If you are connecting to a specific named SQL Server instance, use Data source=servere name\instance Name; but only for SQLServer2000) for example: "Provider=sqloledb;data Source=myservername\myinstancename;initial Catalog=mydatabasename; User Id=myusername; Password=mypassword; "

o Enter the user name and Password dialog box when connecting:

Conn.provider = "SQLOLEDB"

Conn.properties ("Prompt") = adPromptAlways

Conn.Open "Data source=aron1;initial catalog=pubs;"

o Connect via 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, the end of Data source is the port number to use (default is 1433))

· SqlConnection (. NET)

o Standard Connection (Security):

"Data source=aron1;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "

Or

"Server=aron1;database=pubs; User Id=sa; PASSWORD=ASDASD; Trusted_connection=false "

(The results of these two connection strings are the same)

o Trust connection (Trusted connection):

"Data source=aron1;initial catalog=pubs;integrated Security=sspi;"

Or

"Server=aron1;database=pubs; Trusted_connection=true; "

(The results of these two connection strings are the same)

(You can use servername\instancename instead of data Source to value a specific instance of SQL Server, but only for SQLServer2000)

o Connect via IP address:

"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, the end of Data source is the port number to use (default is 1433))

o Declaration of the SqlConnection Connection:

C#:

Using System.Data.SqlClient;

SqlConnection sqlconn = new SqlConnection ();

Sqlconn.connectionstring= "My ConnectionString";

Sqlconn.open ();

VB.net:

Imports System.Data.SqlClient

Dim sqlconn as SqlConnection = New SqlConnection ()

Sqlconn.connectionstring= "My ConnectionString"

Sqlconn.open ()

· Data Shape

o MS Data Shape

"Provider=msdatashape;data provider=sqloledb;data source=aron1;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "

· More

o How to define which 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 Protocol 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 Notes

Use the following syntax when connecting through the SQLOLEDB provider:

Network LIBRARY=DBMSSOCN

However, the following syntax is used when connecting through the MSDASQL provider:

Network=dbmssocn

o All SqlConnection connection string properties

§ The following table shows all the connection string properties of the ADO. SqlConnection object. Most of these properties are also used in ADO. All properties and descriptions are from MSDN.

Name Default Value Description

Application name of the application or the. Net SqlClient Data Provider when no application is provided

The name of the AttachDbFileName or extended properties or initial file name, including the full path of the associated database. The database name must be specified by the keyword "database".

Connect Timeout or Connection Timeout 15 the time (in seconds) to wait for a server connection before aborting the connection request, resulting in an error

Connection Lifetime 0 When a connection returns to the connection pool, the difference between the current time and the connection creation time, if the time period exceeds the specified connection lifetime, the connection is destroyed. It is used in aggregation settings to force load balancing between servers running the server and ready to go online.

Connection reset "True" determines whether to reset the database connection when the connection is moved from the connection pool. When set to ' false ' is used to avoid additional server reciprocating costs when obtaining a connection.

Current Language SQL Server language record name

The name or network address of the SQL Server instance to which the Data source or server or address or network address is to be connected (addr)

When Enlist ' true ' is true, the connection pool automatically lists connections in the current transaction context of the creation thread.

Initial catalog or database name

Integrated security or Trusted_Connection ' false ' connection is a trusted connection. Its value is ' true ', ' false ' and ' SSPI ' (equals ' true ').

Max Pool Size 100 The maximum number of connections allowed in a connection pool

Min Pool Size 0 The minimum number of connections allowed in the connection pool

Network library or net ' DBMSSOCN ' is used to establish a connection to an instance of SQL Server. Values include DBNMPNTW (Named Pipes), DBMSRPCN (Multiprotocol), Dbmsadsn (Apple talk), Dbmsgnet (VIA), DBMSIPCN (Shared memory) and DBMSSPXN (ipx/spx), and DBMSSOC N (TCP/IP). The connected system must have the appropriate dynamic link library installed. If you do not specify a network, when you use a local server (for example, "." or "(local)"), the shared memory will be used

Packet size 8192 network packet bytes that communicate with one instance of SQL Server

password-or PWD SQL Server account login password

Persist Security Info ' False ' is set to ' false ', sensitive safety information (such as a password) is not returned as part of the connection when the connection is open or has been open.

Pooling ' True ' is true, remove the SqlConnection object from the appropriate connection pool, or create the SqlConnection object when necessary and add it to the appropriate connection pool.

User ID SQL Server logged on users

Workstation ID The local computer name of the workstation that is connected to SQL Server

§ Note:

Separate each property with a semicolon

If a name appears more than two times, the last occurrence of the value in the concatenated string will be used.

If you build a connection string by using the value of the user input field in the app, you must make sure that the user does not change the connection string by inserting another value into the user's value into an additional attribute.

Access

· Odbc

o Standard Connection (Security):

"Driver={microsoft Access Driver (*.mdb)};D Bq=c:\mydatabase.mdb; Uid=admin; pwd=; "

O Group (System database) connection (Workgroup):

The two methods are: Specify the user name and password in the connection string or in the Open data set

"Driver={microsoft Access Driver (*.mdb)};D Bq=c:\mydatabase.mdb; systemdb=c:\mydatabase.mdw; "," admin "," "

Or

if (Pdb.open ("Driver={microsoft Access Driver (*.mdb)};D Bq=c:\mydatabase.mdb; Systemdb=c:\mydatabase.mdw, "," "," Databaseuser "," Databasepass "))

{dosomething ();

Pdb.close ();

}

o Exclusive Mode (Exclusive):

"Driver={microsoft Access Driver (*.mdb)};D Bq=c:\mydatabase.mdb; exclusive=1; Uid=admin; Pwd= "

· OLE DB, OleDbConnection (. NET)

o Standard Connection

Security):

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb; User id=admin; password=; "

O Group connection (System database)

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb; Jet Oledb:system database=system.mdw; "," admin "," "

o Password-connected connection:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb; Jet oledb:database Password=mydbpassword; "," admin "," "

· Oracle

· Odbc

O New version:

"Driver={microsoft ODBC for Oracle}; Server=oracleserver.world; Uid=username; PWD=ASDASD; "

o old version:

"Driver={microsoft ODBC Driver for Oracle}; Connectstring=oracleserver.world; Uid=myusername; Pwd=mypassword; "

· OLE DB, OleDbConnection (. NET)

o Standard Connection (Security):

"Provider=msdaora;data Source=myoracledb; User Id=username; PASSWORD=ASDASD; "

This is the Microsoft format, the following is the format of Oracle (provider differs)

"Provider=oraoledb.oracle;data Source=myoracledb; User Id=username; PASSWORD=ASDASD; "

Note: "Data source=" must be set to Net8 name according to the appropriate naming method. For example, for local naming, it is an alias in Tnsnames.ora, and for Oracle naming it is the NET8 Network service name

o Trust connection (Trusted Connection):

"Provider=oraoledb.oracle;data source=myoracledb;osauthent=1;"

or set the user ID to "/"

"Provider=oraoledb.oracle;data Source=myoracledb; User id=/; password=; "

· OracleConnection (. NET)

o Standard Connection:

"Data source=oracle8i;integrated Security=yes";

This is only valid for Oracle 8i Release 3 or later

o OracleConnection Statement:

C#:

Using System.Data.OracleClient;

OracleConnection oracleconn = new OracleConnection ();

oracleconn.connectionstring = "My ConnectionString";

Oracleconn.open ();

VB.net:

Imports System.Data.OracleClient

Dim oracleconn as OracleConnection = New OracleConnection ()

oracleconn.connectionstring = "My ConnectionString"

Oracleconn.open ()

· Data Shape

o MS Data Shape:

"PROVIDER=MSDATASHAPE.1; Persist Security info=false;data provider=msdaora;data source=orac;user ID=USERNAME;PASSWORD=MYPW "

· Mysql

· Odbc

o Local Database:

"Driver={mysql}; Server=mysrvname;option=16834;database=mydatabase; "

The sever parameter can also use localhost as its value

o Remote database:

"Driver={mysql}; server=data.domain.com; port=3306;option=131072; stmt=;D atabase=my-database; Uid=username; Pwd=password; "

The meaning of the option value value

1 Client cannot process MYODBC return a column of true width

2 The client cannot handle the case where MySQL returns the true value of the rows affected if this flag is set, MySQL returns ' found rows '. MySQL 3.21.14 or newer to take effect

4 Generate a debug log in C:\myodbc.log. This is with the ' AUTOEXEC. Same as set mysql_debug=d:t:o,c::\myodbc.log in BAT '

8 No package limits are set for results and parameters

16 do not eject the drive issue

32 Use or remove dynamic cursor support. This is not allowed in Myodbc 2.50.

64 ignoring the use of database names in ' Database.table.column '

128 Enforcing the use of ODBC Administrator cursors

256 removing the use of extended value (FETCH)

512 fill Char field full length

The SQLDescribeCol () function returns the name of the column that fully satisfies the condition

2048 using a compressed server/client protocol

4096 tells the server to ignore spaces (PowerBuilder required) after the function and ' ('). This will produce all the function name keywords

8192 using Named pipes to connect MYSQLD servers running on NT

16384 change the Longlong column to intl column (some apps cannot handle longlong columns)

32768 return ' user ' from SQLTables as Table_qualifier and Table_owner

66536 read ODBC group from client reading parameters from ' my.cnf '

131072 Add some additional security checks

If you need a lot of options, you should add the above logo.

· OLE DB, OleDbConnection (. NET)

o Standard Connection:

"Provider=mysqlprov;data Source=mydb; User Id=username; PASSWORD=ASDASD; "

The data source is the name of the MySQL database, or you can use the Server=localhost;db=test

· Mysqlconnection (. NET)

o Einfodesigns.dbprovider:

"Data Source=server;database=mydb; User Id=username; Password=pwd; Command Logging=false "

Applies only to Einfodesigns dbprovider, attached to. NET

o Mysqlconnection's statement:

C#:

Using EInfoDesigns.dbProvider.MySqlClient;

Mysqlconnection mysqlconn = new Mysqlconnection ();

mysqlconn.connectionstring = "My ConnectionString";

Mysqlconn.open ();

VB.net:

Imports eInfoDesigns.dbProvider.MySqlClient

Dim mysqlconn as Mysqlconnection = New mysqlconnection ()

mysqlconn.connectionstring = "My ConnectionString"

Mysqlconn.open ()

· Interbase

· ODBC, Easysoft

o Local Computer:

"Driver={easysoft IB6 ODBC}; Server=localhost;database=localhost:c:\mydatabase.gdb; Uid=username; Pwd=password "

o Remote computer:

"Driver={easysoft IB6 ODBC}; Server=computername;database=computername:c:\mydatabase.gdb; Uid=username; Pwd=password "

· ODBC, Intersolv

o Local Computer:

"Driver={intersolv InterBase ODBC Driver (*.GDB)}; Server=localhost;database=localhost:c:\mydatabase.gdb; Uid=username; Pwd=password "

o Remote computer:

"Driver={intersolv InterBase ODBC Driver (*.GDB)}; Server=computername;database=computername:c:\mydatabase.gdb; Uid=username; Pwd=password "

This drive is now provided by DataDirect Technologies (formerly provided by Intersolv)

· OLE DB, Sibprovider

o Standard Connection:

"Provider=sibprovider;location=localhost:;d ata source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password= Masterkey "

o Specify the character set:

"Provider=sibprovider;location=localhost:;d ata source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password= Masterkey;character Set=iso8859_1 "

O Specify the role:

"Provider=sibprovider;location=localhost:;d ata source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password= Masterkey;role=digitadores "

· To learn more please link to InterBase's Borland Developer Network article below http://community.borland.com/article/0,1410,27152,00.html

· IBM DB2

· OLE DB, OleDbConnection (. NET) from MS

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

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

AS400

· Odbc

"Driver={client Access ODBC Driver (32-bit)}; system=myas400; Uid=myusername; Pwd=mypassword "

· OLE DB, OleDbConnection (. NET)

"Provider=ibmda400;data source=myas400; User Id=myusername; Password=mypassword; "

· Sybase

· Odbc

o Sybase System 12 (or 12.5) Enterprise Open client Standard connection:

"Driver={sybase ASE ODBC Driver}; Srvr=aron1; Uid=username; Pwd=password "

o Sybase System 11 Standard Connection:

"Driver={sybase SYSTEM 11}; Srvr=aron1; Uid=username; Pwd=password; "

o intersolv 3.10:

"Driver={intersolv 3.10 32-bit Sybase}; Srvr=aron1; Uid=username; Pwd=password; "

o Sybase SQL Anywhere (formerly watcom SQL ODBC driver):

"ODBC; Driver=sybase SQL Anywhere 5.0; Defaultdir=c:\dbfolder\;D bf=c:\mydatabase.db; Uid=username; Pwd=password;dsn= "" "" "

Note: For the double quotation marks (VB syntax) that follow the DSN parameter referenced at the end, you must change the syntax of the reference symbol that is specified for the language you are using. It is actually very important that the DSN parameter is empty, which would result in a 7778 error if not included.

· OLE DB

o Adaptive Server Anywhere (ASA):

"Provider=asaprov;data Source=myasa"

o Adaptive Server Enterprise (ASE) with a data source of. IDS file):

"Provider=sybase ASE OLE DB Provider; Data Source=myase "

Note You must use the Data Manager to create a data source. IDS file. These. ids files are similar to ODBC DSNs.

Visual FoxPro (DBASE)

· Odbc

o DBASE:

"Driver={microsoft DBASE Driver (*.dbf)}; Driverid=277;dbq=c:\\databasepath "

Note You must specify a file name when writing an SQL statement (such as "Select Name, Address from clients.dbf").

o Visual Foxpro (with Database Container):

"Driver={microsoft Visual Foxpro Driver}; uid=; SOURCETYPE=DBC; SOURCEDB=C:\\DATABASEPATH\\MYDATABASE.DBC; Exclusive=no "

o Visual Foxpro (No Database Container):

"Driver={microsoft Visual Foxpro Driver}; uid=; SOURCETYPE=DBF; SOURCEDB=C:\\DATABASEPATH\\MYDATABASE.DBC; Exclusive=no "

· OLE DB, OleDbConnection (. NET)

o Standard Connection:

"Provider=vfpoledb;data Source=c:\\databasepath\\mydatabase.dbc;"

Excel

· Odbc

o Standard Connection (Security):

"Driver={microsoft Excel Driver (*.xls)};D riverid=790; DBQ =c:\\databasepath\\dbspreadsheet.xls;defaultdir=c:\\databasepath; "

· OLE DB Provider for Microsoft Jet

o Standard Connection (Security):

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\databasepath\\dbspreadsheet.xls; Extended properties=\ "\" Excel 8.0; Hdr=yes;\ "\"; "

Note: If "Hdr=yes", then the provider does not include the first row of the selection in the recordset, and if so, the provider will include the first row of the cell range (or already named range) in the recordset

Text

· Odbc

o Standard Connection (Security):

"Driver={microsoft Text Driver (*.txt; *.csv)}; dbq=c:\\databasepath\\; Extensions=asc,csv,tab,txt; "

If the text file uses tab as the delimiter, you must create the Schema.ini file, and you must use the format=tabdelimited option in the connection string.

Note: You must specify the file name in the SQL statement (for example, "select Name, Address from Clients.csv")

· OLE DB Provider for Microsoft Jet

o Standard Connection (Security):

"Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\\databasepath\\; Extended properties=\ "\" text; " Hdr=yes; Fmt=delimited;\ "\"; "

Note You must specify a file name when writing an SQL statement (such as "Select Name, Address from Clients.txt").

ODBC DSN

"DSN=MYDSN; Uid=myusername; Pwd=mypassword; "

OLE DB Provider for ODBC Databases

Connect to access:

"Provider=msdasql;driver={microsoft Access Driver (*.mdb)};D Bq=c:\\databasepath\\mydatabase.mdb; Uid=myusername; Pwd=mypassword; "

To connect to SQL Server:

"Provider=msdasql;driver={sql Server}; Server=myservername;database=mydatabasename; Uid=myusername; Pwd=mypassword; "

To connect using DSN:

"Provider=msdasql; Persistsecurityinfo=false; Trusted_connection=yes;data source=mydsn;catalog=mydatabase; "

OLE DB Provider for OLAP

"Provider=msolap;data source=myolapservername;initial catalog=myolapdatabasename;"

Connect via http:

This feature allows a client application to use IIS to connect to an Analysis server by specifying a URL in the data source parameter of the client application connection string. This connection method allows the PivotTable service to connect to the Analysis server through a firewall or proxy server. A special ASP page msolap.asp is connected through IIS. When connected to the server, this file must be in this directory and as part of the URL (for example, http://www.myserver.com/myolap/).

To connect using a URL:

"Provider=msolap;data source=http://myolapservername/;initial catalog=myolapdatabasename;"

Connect using SSL:

"Provider=msolap;data source=https://myolapservername/;initial catalog=myolapdatabasename;"

OLE DB Provider for Active Directory

"Provider=adsdsoobject; User Id=myusername; Password=mypassword; "

OLE DB Provider for Index Server

"Provider=msidxs;data Source=mycatalog;"

OLE DB Data Link Connections

"File name=c:\\databasepath\\databasename.udl;"

Outlook Personal mail Box

"Provider=microsoft.jet.oledb.4.0;outlook 9.0; mapilevel=;D atabase=c:\\temp\\; "

Use any temporary folders instead of C:\Temp. This will create a schema file in that folder, and when you open it, it will show all the fields you can get. An empty mapilevel indicates the top level of the folder.

Exchange Mail Box

"Provider=Microsoft.Jet.OLEDB.4.0; Exchange 4.0; Mapilevel=mailbox-pat smith|;D atabase=c:\\temp\\; "

Use any temporary folders instead of C:\Temp.

Note: You can enter a query on the mail store just like a database

Example: "SQL" select contacts.* from Contacts; "

. NET various database connection strings Daquan