Database connection string [Favorites]
In various application development of the database, the connection database is the first step in database application development, and it is also the most important step. For different databases, their connection modes are different, and the corresponding connection strings are different. Programmers may have such experiences, sometimes I don't know how to write or write the connection string to the database, causing the database that cannot be properly accessed. Of course, many programming tools can directly generate the correct connection string through visual interface, but the specific meaning of each parameter in the string is not clear, often confused.
This article lists the connection string required for different connection methods for most common databases, so that the programmer refers! SQL Server · ODBC O standard connection ("driver = {sql server}; server = aron1; database = pubs; uid = sa; pwd = asdasd;" 1) When the server is local, Server can use (local) "Driver = {SQL Server}; server = (local); database = pubs; uid = sa; pwd = asdasd;" 2) When connecting to a remote server, you need to specify an address, port number and a network library "Driver = {SQL Server}; server = 130.120.110.001; address = 130.120.110.001, 1052; network = dbmsocn; database = pubs; uid = sa; pwd = asdasd; "Note: The address parameter must be an IP address, and must include port number O trust Trusted Connection: (Microsoft Windows NT Integrate Security) "Driver = {SQL Server}; server = aron1; database = pubs; trusted_connection = yes;" or "driver = {SQL Server}; server = aron1; database = PUBS; UID =; PWD =; "O connection When I popup input username and password dialog box: conn.properties (" prompt ") = adpromptalways conn.open" driver = {sql server}; server = aron1; database = pubs; "· OLE DB, OLEDBCONNECTION (.NET) O standard connection (STANDARD, DATA SOURCE = aron1; initial catalog = pubs; user ID = sa; password = asdasd;" O Trust connection (TR Usted Connection: "Provider = SQLOLEDB; DATA SOURCE = Aron1; Initial Catalog = Pubs; Integrated Security = SSPI;" (If you connect a specific named SQLServer instance, use Data Source = Servere Name / Instance Name; but only SQLServer2000) For example: "provider = sqloledb; data source = myservername / myinstancename; initial catalog = myDatabaseName; user id = myusername;
PASSWORD = mypassword; "O connection pops up the username and password dialog box: conn.provider =" sqloledb "conn.properties (" prompt ") = adpromptalways conn.open" Data Source = aron1; initial catalog = pubs; "O IP address of the connection: "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, Data Source The end of the end is the port number (default 1433)) · SqlConnection (.NET) O standard connection (SQLCONNECTION): "data source = aron1; initial catalog = public; user ID = sa; password = asdasd;" Or "server = aron1; database = pubs; user ID = sa; password = asdasd; trusted_connection = false" o Trusted connection: "Data Source = aron1; initial catalog = Pubs; Integrated Security = SSPI; "or" Server = aron1; database = pubs; trusted_connection = true; "(The same result is the same as the result of these two connection strings) (available with ServerName / InstanceName to replace Data Source, with a specific SQLServer Example, but only for SQLServer2000) O via the IP address: "Data Source = 190.190.2 00.100, 1433; network library = dbmsSocn; initial catalog = pubs; user ID = sa; password = asdasd; "(DBMSSOCN = TCP / IP instead of NAMED PIPES, the end slogan required for Data Source is required (433) ) o SqlConnection connection statement: 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 = askASD; " name of the 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 use the following syntax when provided by SQLOLEDB who connect: network library = DBMSSOCN but uses the following syntax when connecting through the MSDasql provider: Network = dbmsoCN O All SQLConnection connection string properties § The following table shows all connection string properties of the ADO.NET SQLConnection object. Most of the properties are also used in ADO. All Attributes and descriptions come from MSDN. Name Default Value Description Application Name application name or when the application is not provided, a .NET SQLCLIENT data provider AttachDBFileName or Extended Properties or in The name of the main file name, including the full path to the associated database. The database name must be specified by keyword 'database'. Connect Timeout or Connection Timeout 15 In the abort connection request, wait for the time (in seconds) of the error before the error is generated (in seconds) Connection Lifetime 0 When a connection returns to the connection pool, the current time and the connection creation time, if the time period exceeds The specified connection survival time is destroyed. It is used to force load balancing between running servers and ready-to-line servers in aggregate settings. Connection Reset 'True' When the connection is removed from the connection pool, it is decided to reset the database connection. When set to 'false', it is used to avoid the additional server to get a connection. CURRENT LANGUAGE SQL Server Language Record Data Source or Server or Address or Addr or Network Address's name or network address enlist 'true' is true, the connection pool automatically lists the current transaction context of creating threads. connection.
Initial Catalog or Database Database Name Integrated Security or Trusted_Connection 'false' connection is a trust connection. It takes the value of 'True', 'false' and 'SSPI' (equal to 'TRUE'). Max Pool Size 100 Connecting the maximum number of connections allowed in the pool MIN POOL SIZE 0 connection pool The minimum connection number NetWork Library or Net The 'DBMSSOCN' network library is used to establish a connection with a SQL Server instance. The value includes DBNMPNTW, DBMSRPCN (Multi-Protocol), DBMsadSn (Apple Talk), DBMSGNET (VIA), DBMSIPCN (Shared Memory), and DBMSSPXN (IPX / SPX), and DBMSSOCN (TCP / IP). The corresponding dynamic link library must be installed. If you don't specify a network, when you use a local server (for example, ","), you will use the shared memory packet size 8192 network packet byte size password-or from an instance of SQL Server. The PWD SQL Server Account Login password Persist security info 'false' is set to 'false', when the connection has been turned on or in an open state, the sensitivity security information (such as password) does not return a part of the connection. Pool 'true' is true, remove the SqlConnection object from the appropriate connection pool, or create a SQLConnection object if necessary and increase it to the appropriate connection pool. User ID SQL Server Login User WorkStation ID The Local Computer Name Connecting to SQL Server Workstation Name § Note: Separating the Section Separation Every Attribute If a name is more than twice, the last occurrence of the last appearance in the connection string will be use. If you build a connection string by entering the value of the field in the app, you must ensure that the user does not insert an additional attribute through another value in the user value to change the connection string.
Access · ODBC O standard connection (STANDARD Security): "Driver = {Microsoft Access Driver (* .mdb)}; dbq = c: /mydatabase.mdb; uid = admin; pwd =;" O group (system database) connection ( Workgroup): Two methods are: in the connection string or specify the username and password "Driver = {Microsoft Access Driver (* .mdb)}; dbq = c: /mydatabase.mdb; systemdb = C: / MyDatabase . MDW; ",", ",",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, "," "" "" "" "" "" ") {Dosomething (); pdb.close ();} o exclusive:" driver = {Microsoft Access Driver (* .mdb)}; dbq = C : /mydatabase.mdb; Exclusive = 1; UID = admin; pwd = "· OLE DB, OLEDBCONNECTION (.NET) O standard connection (SAMEPATH; 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 data = system. MDW; "", "admin", "" Oscape connection: "provider = mi Crosoft.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}; connectionString = oraclesRver.World; uid = myusername; pwd = mypassword; "
· OLE DB, OLEDBCONNECTION (.NET) O Standard Security): "Provider = MSDara; Data Source = Myoracledb; user ID = Username; Password = asdasd;" This is Microsoft's format, below is Oracle format (provided Different) "provider = oraoledb.racle; data source = myoracledb; user ID = username; password = asdasd;" Note: "Data Source =" must be set to NET8 name according to the corresponding naming method.
For example, for local naming, it is the alias in tnsnames.ora, for oracle named, is a Trusted connection: "provider = oraoledb.oracle; data source = myoracledb; osfehent = 1;" or Set the User ID to "/" "" "" "" "" "Data Source = Oracle8i; Integrated Security = YES"; "Data Source = ORACLE8I; Integrated Security = YES"; integrated security = yes; only for Oracle 8i release 3 or later effective 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 = msdara; data source = orac; user ID = username; password = mypw "· mys Ql · ODBC O Local Database: "Driver = {mysql}; server = mysrvname; option = 16834; Database = MyDatabase;" Sever parameters can also use localhost as its value O remote database: "driver = {mysql}; server = data .domain.com; port = 3306; option = 131072; stmt =; database = my-database; uid = username; pwd = password; "Option value 1" Option value 1 Client Cannot handle MyoDBC Returns a column of true width 2 The client cannot handle the true value of the row of mysql returns the row of the affected row, and MySQL returns 'Found Rows'. Mysql 3.21.14 or update version can take effect 4 Generate a debug log in c: /myodbc.log.
This is the setting mysql_debug = d: t: O, C :: / myodbc.log in the `autoexec.bat 'in` Autoexec.bat'. . This is not allowed in MyodBC 2.50, which is not allowed in 'database.table.column', using 128 forced database name 128 forced using ODBC Manager Cursor 256 Removing Extension Value (Fetch) 512 full of char fields full length 1024 SQLDESCRIBECOL ) Function Returns the column name 2048 using compressed server / client protocol 4096 tells the server after the function and '(' ignore the space before ". This will generate all function names Keywords 8192 using named pipe connection Mysqld server 16384 on NT converts the LONGLONG column to an INTL column (some applications cannot handle the LONGLONG column) 32768 Return 'user' from SQLTables as Table_Qualifier and Table_owner 66536 from the client reading parameters, from `my.cnf 'read ODBC groups 131072 Add some additional security check If you need a lot of options, you should add the above flags.
· OLE DB, OLEDBCONNECTION (.NET) O Standard Connection: "Provider = mysqlProv; data source = mydb; user ID = username; password = asdasd;" Data Source is the name of the MySQL database, or use 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, appended to the statement of .NET o MySqlConnection : 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 = passw ORD "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 = computename: c: /mydatabase.gdb; uid = username; pwd = password
This drive is now available by DataDirect Technologies (provided by Intersolv) · OLE DB, SIBPROVIDER O standard connection: "provider = sibprovider; location = localhost:; data source = c: /Databases/gdbs/mygdb.gdb; user ID = SYSDBA; password = masterkey "o specify the character set:" provider = sibprovider; location = localhost:; data source = c: /databases/gdbs/mygdb.gdb; user id = SYSDBA; password = masterkey; character set = ISO8859_1 "o Specify role: "provider = sibprovider; location = localhost:; data source = c: /databases/gdbs/mygdb.gdb; user ID = sysdba; password = masterkey; role = digitadores" • Need to get more please link to Interbase The following Borland Developer Network Article http://community.borland.com/Article/0,1410,27152,00.html · IBM DB2 · OLE DB, OLEDBCONNECTION (.NET) from MS O TCP / IP: "Provider = DB2OLDB ; 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 al ias = 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):" ODBC; Driver = Sybase SQL Anywhere 5.0; defaultdir = c: / dbfolder /; dbf = c : /mydatabase.db; uid = username; pwd = password; dsn = "" "" Note: For dual quotes (VB syntax) referenced at the end, you must change the language that meets you The specified reference symbol syndrome.DSN parameter is actually very important, and if not included, the 7778 error will be caused.
· OLE DB O Adaptive Server Anywhere (ASA): "Provider = asaprov; data source = myasa" o Adaptive Server Enterprise (ASE): "Provider = Sybase ASE OLE DB Provider; data source = "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 the file name (such as" Select Name, Address from Clom Clients.dbf "). O Visual FoxPro (Database Passer):" Driver = { Microsoft Visual Foxpro Driver}; UID =; SourceType = DBC; SourceDB = C: //DatabasePath//MyDatabase.dbc; Exclusive = no "o Visual Foxpro (no database containers):" 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 (STANDARD Security):" driver = {micro Soft excel driver (* .xls)}; driverid = 790; dbq = c: //databasepath//dbspreadsheet.xls; defaultdir = c: // DatabasePath; "OLE DB Provider for Microsoft Jet O standard connection (Standard security) (STANDARD Security) : "Provider = microsoft.jet.OLEDB.4.0; Data Source = C: //DatabasePath//dbspreadsheet.xls; extended proties = /" / "excel 8.0; hdr = yes; /" / ";" Note: If " HDR = YES, then the provider does not include the first line of the selected, if, the provider will include the unit range (or have a range) in the recordset
Standard security: "driver = {microsoft text driver (* .txt; * .csv)}; dbq = c: // DatabasePath //; extensions = ASC, CSV, Tab , txt; "If the text file uses Tab as a separator, you must create a schema.ini file, you must use the format = tabdelimited option in the connection string. Note: You must specify a file name in the SQL statement (such as "SELECT NAME, ADDRESS.CSV") · OLE DB Provider for Microsoft Jet O standard connection (STANDARD Security): "provider = microsoft.jet.OleDb.4.0; Data Source = C: // DatabasePath //; Extended Properties = / "/" text; "HDR = YES; FMT = DELIMITED; /" / ";" Note You must specify the file name when writing SQL statements (eg "SELECT NAME , Address From Clients.txt ") ODBC DSN." DSN = MyDSN; Uid = MyUsername; Pwd = MyPassword; "OLE DB Provider for ODBC Databases connection Access:" Provider = MSDASQL; Driver = {Microsoft Access Driver (* .mdb) }; Dbq = c: //DatabasePath//MyDatabase.mdb; Uid = MyUsername; Pwd = MyPassword; "connected to SQL Server:" Provider = MSDASQL; Driver = {SQL Server}; Server = myServerName; Database = MyDatabaseName; Uid = MyUsername; Pwd = MyPassword; "using DSN connection:" Provider = MSDASQL; PersistSecurityInfo = False; Trusted_Connection = Yes; Data Source = MyDSN; catalog = MyDatabase; "OLE DB Provider for OLAP" Provider = MSOLAP; Data Source = MyOLAPServerName; Initial catalog = myolapdatabase; "" "" "passed HTT p Connection: This feature allows the client application to specify a URL in the Data Source parameter of the client application connection string and connect to an analytical server using IIS. This connection method allows the PivotTable service to connect to the analysis server via a firewall or proxy server. A special ASP page MSOLAP.ASP is connected via IIS.
