SQL Server 2008连接字符串写法大全
Sql-Server配置远程连接以及C#连接字符串
Sql-Server配置远程连接以及C#连接字符串SQLServer2008设置开启INTERNET远程连接(“转”有现成的⾃⼰就不写了)SQL Server 2008默认是不允许远程连接的，sa帐户默认禁⽤的，如果想要在本地⽤SSMS连接远程服务器上的SQL Server 2008，需要做两个部分的配置：使⽤⽤sa账户登录SQL Server Management Studio（简写SSMS）如果安装时，不是使⽤SQL Server⾝份验证模式的，请使⽤windows⾝份连接数据库，登录后，右键选择“属性”“安全性”，选中右侧的“SQL Server 和 Windows ⾝份验证模式”以启⽤混合登录模式选择“连接”，勾选“允许远程连接此服务器”，建议将“最⼤并发连接数”按实际情况，进⾏设置，然后点“确定”展开“安全性”->“登录名”->“sa”，右键选择“属性”左侧选择“常规”，右侧选择“SQL Server ⾝份验证”，并设置密码选择“状态”，选中“启⽤”，点击“确定”右击数据库选择“⽅⾯”在“⽅⾯”下接列表框中，选择“服务器配置”，将“RemoteAccessEnabled”属性和”RemotoDacEnabled”设为“True”，点“确定”⾄此SSMS已设置完毕，先退出，再⽤sa登录，成功即表⽰sa帐户已经启⽤。
下⾯开始配置Sql Server Configuration Manager (SSCM)，选中左侧的“SQL Server服务”，确保右侧的“SQL Server”以及“SQL ServerBrowser”正在运⾏打开左侧“SqlServer⽹络配置”，打开“你⾃⼰数据库实例名的协议”，查看右侧的TCP/IP默认是“已禁⽤”，将其修改为“已启⽤”双击打开“TCP/IP”查看“TCP/IP属性”下“协议”选项卡中的“全部侦听”和“已启⽤”项，是否都是设置成“是”选择“IP Addersses”选项卡，IP1、IP2、IPAll设置TCP端⼝为“1433”，TCP动态端⼝为空值，已启⽤为“是”如图将"客户端协议"的"TCP/IP"也修改为“已启⽤”双击打开右侧“TCP/IP”，打开“TCP/IP属性”，将默认端⼝设为“1433”，已启⽤为“是”配置完成，重新启动SQL Server 2008。
SQL数据库连接字符串大全
SQL ServerODBCStandard 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") = adPromptAlwaysoConn.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") = adPromptAlwaysoConn.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)Standard 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"(both connection strings produces the same result)Trusted Connection:"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"- or -"Server=Aron1;Database=pubs;Trusted_Connection=True;"(both connection strings produces the same result)(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)Connect via an 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, at the end of the Data Source is the port to use (1433 is the default))Declare the SqlConnection:C#:using System.Data.SqlClient;SqlConnection oSQLConn = new SqlConnection();oSQLConn.ConnectionString="my connection string";oSQLConn.Open();:Imports System.Data.SqlClientDim oSQLConn As SqlConnection = New SqlConnection()oSQLConn.ConnectionString="my connection string"oSQLConn.Open()Data ShapeMS 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 moreHow to define which network protocol to useExample:"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"Name Network librarydbnmpntw Win32 Named Pipesdbmssocn Win32 Winsock TCP/IPdbmsspxn Win32 SPX/IPXdbmsvinn Win32 Banyan Vinesdbmsrpcn 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=dbmssocnAll SqlConnection connection string propertiesThis table shows all connection string properties for the SqlConnection object. Most of the properties are also used in ADO. All properties and descriptions is from msdn.Name Default Descriptionapplication 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.NoteUse ; to separate each property.If a name occurs more than once, the value from the last one in the connection string will be used. If you are building your connection string in your app using values from user input fields, make sure the user can't change the connection string by inserting an additional property with another value within the user value.SQL Server 2005SQL Native Client ODBC DriverStandard security:"Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"Trusted connection:"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;" EquivalentsIntegrated Security=SSPI equals Trusted_Connection=yesPrompt for username and password:oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Driver={SQL Native Client};Server=Aron1;DataBase=pubs;"Enabling MARS (multiple active result sets):"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;MARS_Connection=yes" EquivalentsMultipleActiveResultSets=true equals MARS_Connection=yesUsing MARS with SQL Native Client, by Chris Lee >>Encrypt data sent over network:"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"Attach a database file on connect to a local SQL Server Express instance:"Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Tr usted_Connection=Yes;"- or -"Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbnam e;Trusted_Connection=Yes;"(use |DataDirectory| when your database file resides in the data directory)Why is the "Database" parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection). Download the SQL Native Client here >> (the package contains booth the ODBC driver and the OLE DB provider)Using SQL Server 2005 Express? Don't miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer) SQL Native Client OLE DB ProviderStandard security:"Provider=SQLNCLI;Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"Trusted connection:"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;"EquivalentsIntegrated Security=SSPI equals Trusted_Connection=yesPrompt for username and password:oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"Enabling MARS (multiple active result sets):"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;MarsConn=yes" EquivalentsMarsConn=yes equals MultipleActiveResultSets=true equals MARS_Connection=yesUsing MARS 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 local SQL Server Express instance:"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Databa se=dbname;Trusted_Connection=Yes;"- or -"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Dat abase=dbname;Trusted_Connection=Yes;"(use |DataDirectory| when your database file resides in the data directory)Why is the "Database" parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection). Download the SQL Native Client here >> (the package contains booth the ODBC driver and the OLE DB provider)Using SQL Server 2005 Express? Don't miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer) SqlConnection (.NET)Standard 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"(both connection strings produces the same result)Trusted Connection:"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"- or -"Server=Aron1;Database=pubs;Trusted_Connection=True;"(both connection strings produces the same result)(use serverName\instanceName as Data Source to use an specifik SQLServer instance)Connect via an 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, at the end of the Data Source is the port to use (1433 is the default))Enabling MARS (multiple active result sets):"Server=Aron1;Database=pubs;Trusted_Connection=True;MultipleActiveResultSets=true"Note! Use 2.0 for MARS functionality. MARS is not supported in 1.0 nor 1.1Streamline your Data Connections by Moving to MARS, by Laurence Moroney, >> Attach a database file on connect to a local SQL 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;Trust ed_Connection=Yes;"(use |DataDirectory| when your database file resides in the data directory)Why is the "Database" parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection).Using "User Instance" on a local SQL Server Express instance:"Data Source=.\SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|\mydb.mdf;user instance=true;"The "User Instance" functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer. To enable the functionality: sp_configure 'user instances enabled','1' (0 to disable)Using SQL Server 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/functionC#:using(SqlConnection connection = new SqlConnection("context connection=true")){connection.Open();// Use the connection}Visual Basic:Using connection as new SqlConnection("context connection=true")connection.Open()' Use the connectionEnd UsingThe context connection lets you execute Transact-SQL statements in the same context (connection) that your code was invoked in the first place.Read moreWhen to use SQL Native Client?.Net applicationsDo not use the SQL Native Client. Use the .NET Framework Data Provider for SQL Server(SqlConnection).COM applications, all other then .Net applicationsUse the SQL Native Client if you are accessing an SQL Server 2005 and need the new features of SQL Server 2005 such as MARS, encryption, xml data type etc. Continue use your current provider (OLE DB / ODBC through the MDAC package) if you are not connecting to an SQL Server 2005 (that's quite obvious eh..) or if you are connecting to an SQL Server 2005 but are not using any of the new SQL Server 2005 features.For more details on the differences between MDAC and SQL Native Client, read this msdn article >>AccessODBCStandard 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;"OracleODBCNew version:"Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;"Old version:"Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"OLE DB, OleDbConnection (.NET)Standard security:"Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;"This one's from Microsoft, the following are from OracleStandard Security:"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;"Trusted Connection:"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;" OracleConnection (.NET)Standard:"Data Source=MyOracleDB;Integrated Security=yes;"This one works only with Oracle 8i release 3 or laterSpecifying username and password:"Data Source=MyOracleDB;User Id=username;Password=passwd;Integrated Security=no;"This one works only with Oracle 8i release 3 or laterDeclare the OracleConnection:C#:using System.Data.OracleClient;OracleConnection oOracleConn = new OracleConnection();oOracleConn.ConnectionString = "my connection string";oOracleConn.Open();:Imports System.Data.OracleClientDim oOracleConn As OracleConnection = New OracleConnection()oOracleConn.ConnectionString = "my connection string"oOracleConn.Open()Missing the System.Data.OracleClient namespace? Download .NET Managed Provider for Oracle >>Great article! "Features of Oracle Data Provider for .NET" by Rama Mohan G. at C# Corner 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 ShapeMS 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 >> MySQLMyODBCMyODBC 2.50 Local database:"Driver={mySQL};Server=localhost;Option=16834;Database=mydatabase;"MyODBC 2.50 Remote database:"Driver={mySQL};Server=;Port=3306;Option=131072;Stmt=;Database=my-dat abase;Uid=username;Pwd=password;"MyODBC 3.51 Local database:"DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DA TABASE=myDatabase;USER=myUsername;PASSWORD=myPa ssword;OPTION=3;"MyODBC 3.51 Remote database:"DRIVER={MySQL ODBC 3.51 Driver};SERVER=;PORT=3306;DA TABASE=myDatabase;USER=myUsername;PASSWORD=myPassword;OPTION=3;"OLE DB, OleDbConnection (.NET)Standard:"Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=asdasd;"Connector/Net 1.0 (.NET)Standard:"Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;"Download the driver at MySQL Developer Zone.Specifying port:"Server=Server;Port=1234;Database=Test;Uid=UserName;Pwd=asdasd;"Default port is 3306. Enter value -1 to use a named pipe connection.Declare the MySqlClient connection:C#:using MySql.Data.MySqlClient;MySqlConnection oMySqlConn = new MySqlConnection();oMySqlConn.ConnectionString = "Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;"; oMySqlConn.Open();:Imports MySql.Data.MySqlClientDim oMySqlConn As MySqlConnection = New MySqlConnection()oMySqlConn.ConnectionString = "Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;" oMySqlConn.Open()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 .NETDeclare the MySqlConnection:C#:using eInfoDesigns.dbProvider.MySqlClient;MySqlConnection oMySqlConn = new MySqlConnection();oMySqlConn.ConnectionString = "my connection string";oMySqlConn.Open();:Imports eInfoDesigns.dbProvider.MySqlClientDim oMySqlConn 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 a freeware data provider from SevenObjectsCore 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.InterbaseODBC, EasysoftLocal computer:"Driver={Easysoft IB6 ODBC};Server=localhost;Database=localhost:C:\mydatabase.gdb;Uid=username;Pwd=password"Remote Computer:"Driver={Easysoft IB6 ODBC};Server=ComputerName;Database=ComputerName:C:\mydatabase.gdb;Uid=username;P wd=password"Read more about this driver: Easysoft ODBC-Interbase driver >>ODBC, IntersolvLocal computer:"Driver={INTERSOLV InterBase ODBC Driver (*.gdb)};Server=localhost;Database=localhost:C:\mydatabase.gdb;Uid=username;Pwd=password"Remote Computer:"Driver={INTERSOLV InterBase ODBC Driver (*.gdb)};Server=ComputerName;Database=ComputerName:C:\mydatabase.gdb;Uid=username;P wd=password"This driver are provided by DataDirect Technologies >> (formerly Intersolv)OLE DB, SIBPROviderStandard:"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 /article/0,1410,27152,00.htmlIBM DB2OLE DB, OleDbConnection (.NET) from msTCP/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"IBM's OLE DB Provider (shipped with IBM DB2 UDB v7 or above)TCP/IP:Provider=IBMDADB2;Database=sample;HOSTNAME=db2host;PROTOCOL=TCPIP;PORT=50 000;uid=myUserName;pwd=myPwd;ODBCStandard:"driver={IBM DB2 ODBC DRIVER};Database=myDbName;hostname=myServerName;port=myPortNum;protocol=TCPIP; uid=myUserName; pwd=myPwd"SybaseODBCStandard Sybase System 12 (or 12.5) Enterprise Open Client:"Driver={SYBASE ASE ODBC Driver};Srvr=Aron1;Uid=username;Pwd=password"Standard Sybase System 11:"Driver={SYBASE SYSTEM 11};Srvr=Aron1;Uid=username;Pwd=password;Database=mydb" For more information check out the Adaptive Server Enterprise Document SetsIntersolv 3.10:"Driver={INTERSOLV 3.10 32-BIT Sybase};Srvr=Aron1;Uid=username;Pwd=password;"Sybase SQL Anywhere (former Watcom SQL ODBC driver):"ODBC; Driver=Sybase SQL Anywhere 5.0; DefaultDir=c:\dbfolder\;Dbf=c:\mydatabase.db;Uid=username;Pwd=password;Dsn="""""Note! The two double quota following the DSN parameter at the end are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax. The empty DSN parameter is indeed critical as not including it will result in error 7778.Read more in the Sybase SQL Anywhere User Guide (see part 3, chapter 13) >>OLE DBAdaptive Server Anywhere (ASA):"Provider=ASAProv;Data source=myASA"Read more in the ASA User Guide (part 1, chapter 2) >>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;UserId=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)Standard:"DataSource='myASEserver';Port=5000;Database='myDBname';UID='username';PWD='password';"Declare the AseConnection:C#:using Sybase.Data.AseClient;AseConnection oCon = new AseConnection();oCon.ConnectionString="my connection string";oCon.Open();:Imports System.Data.AseClientDim oCon As AseConnection = New AseConnection()oCon.ConnectionString="my connection string"oCon.Open()Read more! Adaptive Server Enterprise Data Provider Documentation >>InformixODBCInformix 3.30:"Dsn='';Driver={INFORMIX 3.30 32 BIT};Host=hostname;Server=myserver;Service=service-name;Protocol=olsoctcp;Database=mydb ;UID=username;PWD=myPwdInformix-CLI 2.5:"Driver={Informix-CLI 2.5 (32 Bit)};Server=myserver;Database=mydb;Uid=username;Pwd=myPwd"OLE DBIBM Informix OLE DB Provider:"Provider=Ifxoledbc.2;password=myPw;User ID=myUser;Data Source=dbName@serverName;Persist Security Info=true"IngresODBCDSN-less"Provider=MSDASQL.1;DRIVER=Ingres;SRVR=xxxxx;DB=xxxxx;Persist Security Info=False;uid=xxxx;pwd=xxxxx;SELECTLOOPS=N;ExtendedProperties="""SERVER=xxxxx;DATABASE=xxxxx;SERVERTYPE=INGRES""Mimer SQLODBCStandard Security:"Driver={MIMER};Database=mydb;Uid=myuser;Pwd=mypw;"Prompt for username and password:"Driver={MIMER};Database=mydb;"LightbaseStandardStandard:"user=USERLOGIN;password=PASSWORD;UDB=USERBASE;server=SERVERNAME"PostgreSQLCore 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.PostgreSQL driverStandard:"DRIVER={PostgreSQL};SERVER=ipaddress;port=5432;DATABASE=dbname;UID=username; PWD=password;"Npgsql by pgFoundry (.NET)SSL activated:"Server=127.0.0.1;Port=5432;Userid=myuserid;password=mypw;Protocol=3;SSL=true;Pooling=t rue;MinPoolSize=3;MaxPoolSize=20;Encoding=UNICODE;Timeout=20;SslMode=Require"Without SSL:"Server=127.0.0.1;Port=5432;Userid=myuserid;password=mypw;Protocol=3;SSL=false;Pooling= true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable" Read more in the Npgsql: User's Manual and on the pgFoundry website.ParadoxODBC5.X:Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;DefaultDir=c:\pathToDb\;Dbq=c:\pathToDb\;CollatingSequence=ASCII"7.X:"Provider=MSDASQL.1;Persist Security Info=False;Mode=Read;Extended Properties='DSN=Paradox;DBQ=C:\myDb;DefaultDir=C:\myDb;DriverId=538;FIL=Paradox7.X;MaxBufferSize=2048;PageTimeout=600;';Initial Catalog=C:\myDb"OleDbConnection (.NET)Standard"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myDb;Extended Properties=Paradox 5.x;" MS kb-article: How to use Paradox data with Access and Jet >>DSN。
SQLServer连接字符串的总结
SQLServer连接字符串的总结SQL Server连接字符串的总结以下的文章主要是介绍SQL Server连接字符串的总结，你如果是SQL Server连接字符串的疯狂一族的话，此的文章，你一定不要错过。
以下就是文章的主要内容的详细描述，望大家在浏览之后会对其有更深的了解。
SQL Server的连接字符串总结1 SQL Server的连接字符串总结转至学网页网下面是连接到SQL Server连接字符串.(站长注:主要是用于sql server2000)ODBC方式标准安全链接语法：1.Driver={SQLServer};Server=myServerAddress;Database=myDataBase;Uid=m yUsername;Pwd=myPassword;信任连接语法：1.Driver={SQLServer};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;若提示用户名和密码,这里有一个小技巧.首先你要设置连接对象的Prompt 属性为adPromptAlways.然后用下面的SQL Server连接字符串连接数据库. 以下是语法格式：1.oConn.Properties("Prompt") = adPromptAlways2.Driver={SQLServer};Server=myServerAddress;Database=myDataBase;3.OLE DB, OleDbConnection (.NET)标准安全链接语法：1.Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;信任连接语法：1.Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;用服务器名\实例名来连接一个服务器实例,需要注意很多实例连接参数仅对2000版本有效,以前其他的版本是不可以用的.连接到sql server实例. 以下是语法格式：1.Provider=sqloledb;DataSource=myServerName\theInstanceName;Initial Catalog=myDa taBase;Integrated Security=SSPI;若提示用户名和密码,也需要一点小技巧.首先设置连接对象的Provider属性为sqloledb,接着设置连接对象的Prompt属性为adPromptAlways.最后用SQL Server连接字符串连接数据库就可以啦.代码如下: 以下是示例代码：1.oConn.Provider = "sqloledb"2.oConn.Properties("Prompt") = adPromptAlways3.Data Source=myServerAddress;Initial Catalog=myDataBase;通过IP地址连接以下是语法格式：1.Provider=sqloledb;DataSource=190.190.200.100,1433;Network Library=DBMSSOCN;Ini tial Catalog=myDataBase;User ID=myUsername;Password=myPassword;DBMSSOCN=TCP/IP.是告诉连接对象用ip连接而不是命名管道的方式.DataSource最后的那个1433端口是SQL server的默认端口(站长注:与sql server2005的不相同)1.SqlConnection (.NET)标准连接语法：1.Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;标准连接语法(另一种)：1.Server=myServerAddress;Database=myDataBase;UserID=myUsername;Password=myPassword;Trusted_Connection=False;第二种连接字符串与前一个是效果相同的.之所以包含到这里是为了指出很多SQL Server连接字符串的关键词有相同的效果.信任连接语法：1.Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;信任连接的语法(另一种)：1.Server=myServerAddress;Database=myDataBase;Trusted_ Connection=True;用服务器名\实例名来连接一个服务器实例,需要注意很多实例连接参数仅对2000版本有效,以前其他的版本是不可以用的. 连接到Sql Server实例：1.Server=myServerName\theInstanceName;Database=myD ataBase;Trusted_Connection=True;CE设备的信任连接通常一个windows CE设置是被验证过并且登陆到域中的,用SSPI 或信任/验证过的连接连接数据库用下面的SQL Server连接字符串: 以下是语法格式：1.Data Source=myServerAddress;InitialCatalog=myDataBase;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;注意:仅在CE设备上有效.通过IP地址连接以下是语法格式：1.Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;DBMSSOCN=TCP/IP.是告诉连接对象用ip连接而不是命名管道的方式.DataSource最后的那个1433端口是SQL server的默认端口(站长注:与sql server2005的不相同)特殊的包大小以下是语法格式：1.Server=myServerAddress;Database=myDataBase;UserID=myUsername;Password=myPassword;Trusted_Connection=False;Packet Size=4096;默认的,微软的.net框架中的SQL Server数据库Provider 设置的默认网络包的大小为8192字节,这可能不是最优的设置,你可以试着设置为4096字节.8192字节可能引发" Failed to reserve contiguous memory" 错误.以下内容不常用到,不翻译啦.Data Shape以下是语法格式：1.Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=myServerAddress;InitialCatalog=myDataBase;UserID=myUsername;Password=myPassword;以上的相关内容就是对SQL Server连接字符串的介绍，望你能有所收获。
ADO连接SQL Server 2008 Express方法总结
ADO连接SQL Server 2008 Express方法总结使用ADO连接微软SQL Server数据库，根据连接的类型有多种连接字符串，并且有些字符串的效果是一样的。
目前已经经过VS2008测试成功的连接字符串说明如下：（1）第一种：指定SQL Server 2008 Express安装的计算机名称“Provider=SQLNCLI10;Server=Hostname\\SQLEXPRESS;Database=DBName; Uid=UserName;Pwd=Password”其中：SQLNCLI10也可以替换为SQLOLEDB。
Hostname为安装了SQL Server 2008 Express的计算机名称，\\SQLEXPRESS 是安装SQL Server 2008 Express的实例名称，数据库安装时将用SQLEXPRESS 作为缺省的实例名称，如果指定了其它实例名，则需要做相应的替换。
DBName为要连接的数据库名称。
UserName和Password分别为连接数据库的用户名和密码。
如果指定信任连接，则可以在最后增加字符串“;Integrated Security=SSPI”或者“;Trusted_Connection=yes”。
（2）第二种：指定SQL Server 2008 Express安装的计算机IP地址直接将上面字符串中的Hostname替换为对应的IP地址（如192.168.1.5）即可，如果IP地址为127.0.0.1，则连接本地的SQL Serve 2008且实例名为SQLEXPRESS的数据库服务器。
需要特别注意的是，此种情况只在SQL Server Browser服务启动时有效。
需要注意的几点：（1）可能由于版本为EXPRESS的缘故，在连接字符串中必须指定形式为HostName\\实例名或者IPAddress\\实例名的形式，否则连接不成功，这可能区别于其它版本的数据库连接方式。
sql_server_分组后一列字段拼接成字符串方法
sql server 分組后一列字段拼接成字符串方法1. 引言1.1 概述：在SQL Server数据库中，有时我们需要将分组后的一列字段拼接成一个字符串。
这种操作在实际应用中非常常见，例如将某一类别下的所有产品名称以逗号分隔的形式显示出来。
为了实现这个需求，我们可采用多种方法和技术。
1.2 文章结构：本文将介绍两种常用的SQL Server函数和技术来实现分组后一列字段的拼接。
首先，我们会详细讲解COALESCE函数的用法并展示如何在分组查询中使用该函数实现字段拼接。
然后，我们会介绍FOR XML PATH('')技术，并演示如何利用它来进行字段拼接操作。
最后，我们会对这两种方法进行性能比较，并对它们的优缺点和适用场景进行总结和讨论。
1.3 目的：本文旨在帮助读者理解SQL Server中实现分组后一列字段拼接的方法和技术。
读者将学习到如何使用COALESCE函数和FOR XML PATH('')技术来处理此类需求，并能根据具体情况选择最合适的方法。
通过本文的阅读与学习，读者将提升自己在SQL Server数据库开发中的技术水平，并能更加高效地完成相关任务。
2. 分组后一列字段拼接成字符串方法2.1 什么是分组后字段拼接在SQL Server中，当我们进行分组查询时，有时候需要将每个分组内的某一列字段合并为一个字符串。
这种需求经常出现在需要生成报表或者统计数据时，其中需要将某一列的多个值拼接在一起作为汇总信息。
例如，我们有一个订单表，每个订单包含了许多产品编号，现在我们希望按照订单进行分组，并将每个订单内的产品编号拼接成一个字符串。
2.2 常用的SQL Server函数和技术在SQL Server中，有几种常用的函数和技术可以帮助我们实现字段拼接：- COALESCE函数：COALESCE函数可以返回参数列表中第一个非空值。
在分组查询中，我们可以使用COALESCE函数来合并同一组内的字段值。
数据库连接字符串大全
1.SQL Server(1)ODBC"Driver={SQL Server};Server=Chris;Database=pubs;Uid=sa;Pwd=sa""Driver={SQL Server};Server=Chris;Database=pubs;Trusted_Connection=yes;"oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Driver={SQL Server};Server=Chris;DataBase=pubs;"(2)OLEDB,OLEDBConnection(.net)"Provider=sqloledb;Data Source=Chris;Initial Catalog=pubs;User Id=sa;Password=sa;""Provider=sqloledb;Data Source=Chris;Initial Catalog=pubs;Integrated Security=SSPI;"(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)oConn.Provider = "sqloledb"oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Data Source=Chris;Initial Catalog=pubs;""Provider=sqloledb;Data Source=192.168.0.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=sa;"(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))(3) SqlConnection (.NET)"server=.;database=pubs;uid=sa;pwd=123456" –用户名密码方式"Data Source=.;Initial Catalog=MySchool;User ID=sa;Pwd=sa""Data Source=.;Initial Catalog=pubs;Integrated Security=SSPI;" – SSPI方式"Data Source=Chris;Initial Catalog=pubs;User Id=sa;Password=sa;""Data Source=Chris;Initial Catalog=pubs;Integrated Security=SSPI;"(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)"Data Source=192.168.0.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=sa;"(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))2.Access(1)ODBC"Driver={Microsoft Access Driver (*.mdb)};Dbq=\somepath\mydb.mdb;Uid=Admin;Pwd=asdasd;""Driver={Microsoft Access Driver (*.mdb)};Dbq=\somepath\mydb.mdb;SystemDB=\somepath\mydb.mdw;","admin", ""(2)OLEDB, OleDbConnection (.NET)"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=asdasd;""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:System Database=system.mdw;","admin", "" "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;","admin", "" 3.Oracle(1)ODBC"Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;""Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"(2)OLEDB, OleDbConnection (.NET)"Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;""Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;""Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;"(3)OracleConnection (.NET)"Data Source=Oracle8i;Integrated Security=yes";4.MySql(1)ODBC"Driver={mySQL};Server=mySrvName;Option=16834;Database=mydatabase;""Driver={mySQL};Server=;Port=3306;Option=131072;Stmt=;Database=my-database;Uid=username;Pwd=password;" (2)OLEDB, OleDbConnection (.NET)"Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=asdasd;"(3)MySqlConnection (.NET)"Data Source=server;Database=mydb;User ID=username;Password=pwd;Command Logging=false"5.IBM DB2(1)OLEDB, OleDbConnection (.NET) from ms"Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;PackageCollection=MyPkgCol;Default Schema=Schema;User ID=MyUser;Password=MyPW""Provider=DB2OLEDB;APPC Local LU Alias=MyAlias;APPC Remote LU Alias=MyRemote;Initial Catalog=MyCtlg;PackageCollection=MyPkgCol;Default Schema=Schema;User ID=MyUser;Password=MyPW"6.SyBase(1)ODBC"Driver={SYBASE SYSTEM 11};Srvr=Aron1;Uid=username;Pwd=password;""Driver={INTERSOLV 3.10 32-BIT Sybase};Srvr=Aron1;Uid=username;Pwd=password;"7. Informix(1)ODBC2.Driver={INFORMIX3.30 32BIT};Host=hostname;Server=myserver;Service=service-name;Protocol=olsoctcp;Database=mydb;UID=username;PWD=myPwd"Driver={Informix-CLI 2.5 (32 Bit)};Server=myserver;Database=mydb;Uid=username;Pwd=myPwd"8.DSN(1)ODBC"DSN=myDsn;Uid=username;Pwd=;""FILEDSN=c:\myData.dsn;Uid=username;Pwd=;"9.Excel(1)ODBC"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;"(2)OLE DB"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=Excel 8.0;HDR=Yes;""HDR=Yes;"。
SqlServer数据库链接字符串参数说明
SqlServer数据库链接字符串参数说明DataSource,//要连接到的 SQL Server 实例的名称或⽹络地址FailoverPartner,//在主服务器停机时要连接到的伙伴服务器的名称或地址AttachDBFilename,//包含主数据⽂件名称的字符串。
该字符串包括可附加数据库的完整路径名InitialCatalog,//与该连接关联的数据库的名称IntegratedSecurity,//⼀个布尔值，该值指⽰是否在连接中指定⽤户 ID 和密码（值为 false 时），或者是否使⽤当前的 Windows 帐户凭据进⾏⾝份验证（值为 true 时） PersistSecurityInfo,//⼀个布尔值，该值指⽰如果连接是打开的或者⼀直处于打开状态，那么安全敏感信息（如密码）是否将不作为连接的⼀部分返回UserID,//连接到 SQL Server 时要使⽤的⽤户 IDPassword,//SQL Server 帐户的密码Enlist,//⼀个布尔值，该值指⽰ SQL Server 连接池程序是否在创建线程的当前事务上下⽂中⾃动登记连接Pooling,//⼀个布尔值，该值指⽰每次请求连接时该连接是汇⼊连接池还是显式打开MinPoolSize,//针对此特定连接字符串连接池中所允许的最⼩连接数MaxPoolSize,//针对此特定连接字符串连接池中所允许的最⼤连接数AsynchronousProcessing,//⼀个布尔值，该值指定使⽤此连接字符串创建的连接是否允许异步处理ConnectionReset,//⼀个布尔值，该值指⽰在从连接池中提取连接时是否重置连接(已过时)MultipleActiveResultSets,//⼀个布尔值，该值指⽰多活动结果集是否可与关联的连接相关联Replication,//⼀个布尔值，该值指⽰是否使⽤连接来⽀持复制ConnectTimeout,//在终⽌尝试并产⽣错误之前，等待与服务器连接的时间长度（以秒为单位）Encrypt,//⼀个布尔值，该值指⽰在服务器安装了证书的情况下，SQL Server 是否为客户端和服务器之间发送的所有数据使⽤ SSL 加密TrustServerCertificate,//该值指⽰在跳过⽤于验证信任的证书链遍历时是否加密信道LoadBalanceTimeout,//连接被销毁前在连接池中存活的最短时间（以秒为单位）NetworkLibrary,//⼀个字符串，该字符串包含⽤于建⽴与 SQL Server 的连接的⽹络库的名称PacketSize,//⽤来与 SQL Server 的实例通信的⽹络数据包的⼤⼩（以字节为单位）TypeSystemVersion,//⼀个字符串值，该值指⽰应⽤程序所需的类型系统ApplicationName,//与连接字符串关联的应⽤程序的名称,如果未提供名称，则为“.NET SqlClient Data Provider”CurrentLanguage,//获取或设置 SQL Server 语⾔记录名称WorkstationID,//连接到 SQL Server 的⼯作站的名称UserInstance,//该值指⽰是否将连接从默认的 SQL Server Express 实例重定向到在调⽤⽅帐户之下运⾏并且在运⾏时启动的实例ContextConnection,//该值指⽰应建⽴与 SQL Server 的客户端/服务器连接还是进程内连接TransactionBinding,//⼀个字符串值，该值指⽰该连接如何保持与登记 System.Transactions 事务的关联ApplicationIntent,MultiSubnetFailover,KeywordsCount。
sql server2008字符连接函数
sql server2008字符连接函数SQL Server 2008字符连接函数在SQL Server 2008中，字符连接函数是一种非常有用的功能，用于将多个字符串连接成一个字符串。
本文将介绍SQL Server 2008中常用的字符连接函数，包括CONCAT、+运算符和字符串聚合函数。
1. CONCAT函数CONCAT函数是SQL Server 2012引入的，但在SQL Server 2008中也可以使用。
它可以将多个字符串连接在一起，并返回一个合并后的字符串。
它的用法很简单，只需将要连接的字符串作为参数传递给函数即可。
例如：SELECT CONCAT('Hello', ' ', 'World') AS Result;这将返回"Hello World"。
2. +运算符在SQL Server中，我们可以使用+运算符将两个字符串连接在一起。
例如：SELECT 'Hello' + ' ' + 'World' AS Result;这也将返回"Hello World"。
需要注意的是，如果其中一个操作数为NULL，那么结果也将为NULL。
要避免这种情况，可以使用ISNULL函数或COALESCE函数来处理NULL值。
3. 字符串聚合函数在SQL Server中，我们还可以使用字符串聚合函数将多个字符串连接在一起。
其中最常用的函数是STUFF和FOR XML PATH。
STUFF函数用于从一个字符串中删除指定位置的字符，并插入一个新的字符串。
通过将空字符串作为第二个参数传递给STUFF函数，可以实现字符串的连接。
例如：SELECT STUFF('Hello', 3, 0, ' World') AS Result;这将返回"Hello World"。
sqlserver 中group by 字符串拼接
标题：SQL Server中使用GROUP BY进行字符串拼接的方法一、介绍在SQL Server中，我们经常会遇到需要对数据进行分组并进行字符串拼接的情况。
比如我们需要将某个字段按照相同的某个字段进行分组，并将这个字段的数据进行拼接。
这个时候，我们就需要使用GROUP BY进行字符串拼接了。
二、使用STUFF和FOR XML PATH方法进行字符串拼接在SQL Server中，我们可以使用STUFF和FOR XML PATH方法来进行字符串的拼接。
具体步骤如下：1. 在SELECT语句中使用STUFF和FOR XML PATH方法进行字符串拼接```sqlSELECT column1,STUFF((SELECT ', ' + column2FROM table1 t2WHERE t1.column1 = t2.column1FOR XML PATH('')), 1, 2, '') AS concatenated_column2FROM table1 t1GROUP BY column1;```在这个例子中，我们首先在SELECT语句中使用STUFF和FOR XML PATH方法来对column2进行字符串拼接。
然后使用GROUP BY对column1进行分组。
2. 解释- `STUFF`函数用来替换字符串的子字符串。
它的语法是`STUFF(string_expression, start, length, replacement_characters)`。
在这个例子中，我们将`start`设为1，`length`设为2，`replacement_characters`设为空字符串，表示替换从字符串的第一个字符开始的两个字符为一个空字符串。
- `FOR XML PATH('')`表示将查询结果转换为XML，并且将每一行的数据合并为一个字符串。
.NETSQLServer连接字符串句法（详解）
.NETSQLServer连接字符串句法（详解）.NET SQL Server连接字符串句法（详解）2008-11-12 上午 10:15.NET SQL Server连接字符串句法数据库的连接性已经发展成为应用程序开发的一个标准方面。
数据库连接字符串现在已经成为每个项目的标准必备条件。
我发现自己为了找到所需要的句法，经常要从另外一个应用程序中复制连接字符串或者进行一次搜索。
这个在与SQL Server交互操作时更是如此，因为它有太多的连接字符串选项。
现在就让我们来研究一下连接字符串的众多方面。
连接字符串在对象实例化或者建立期间，数据库连接字符串通过属性或方法被传递到必要的对象。
连接字符串的格式是一个以分号为界，划分键／值参数对的列表。
列表A中包括了一个C#中的例子，说明了怎样用创建SqlConnection对象的方法连接到SQL Server（实际的连接字符串是通过对象的ConnectionString属性分配的）。
列表B中包括的是的版本。
引用内容：string cString = "Data Source=server;Initial Catalog=db;User ID=test;Password=test;";SqlConnectionconn = new SqlConnection();conn.ConnectionString = cString;conn.Open();引用内容：Dim cString As StringcString = "Data Source=server;Initial Catalog=db;UserID=test;Password=test;"Dim conn As SqlConnection = New SqlConnection()conn.ConnectionString = cStringconn.Open()连接字符串会指定数据库服务器和数据库，以及访问数据库必需的用户名和密码。
sql server 2008数据库连接字符串大全
一、.NET Framework Data Provider for SQL Server类型：.NET Framework类库使用：System.Data.SqlClient.SqlConnection厂商：Microsoft1.标准安全连接复制代码代码如下:Data Source = myServerAddress;Initial Catalog = myDataBase;User Id = myUsername;Password = myPassword;使用服务器名\实例名作为连接指定SQL Server实例的数据源。
如果你使用的是SQL Server 2008 Express版，实例名为SQLEXPRESS。
2.可替代的标准安全连接复制代码代码如下:Server = myServerAddress;Database = myDataBase;User ID = myUsername;Password = myPassword;Trusted_Connection = False;这条连接字符串跟上一条效果一样。
把这条写出来只是想说，其实很多连接字符串的关键字有多种写法。
3.信任连接复制代码代码如下:Data Source = myServerAddress;Initial Catalog = myDataBase;Integrated Security = SSPI;可替代的信任连接复制代码代码如下:Server = myServerAddress;Database = myDataBase;Trusted_Connection = True;4.连接Windows CE设备的信任连接通常一台Windows CE设备在一个域里是不能被认证和登录的。
为了让一台CE设备使用SSPI或信任连接和认证，可以使用下面的连接字符串：复制代码代码如下:Data Source = myServerAddress;Initial Catalog = myDataBase;Integrated Security = SSPI;User ID = myDomain\myUsername;Password = myPassword;说明一下，这条语句只能在CE设备上用。
数据库连接字符串大全
数据库连接字符串大全转自：/∙ SQL Server∙ ODBCo Standard Security:"Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"o Trusted connection:"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"o PR ompt for username and pass Word:oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"∙ OLE DB, OleDbConnection (.NET)o Standard Security:"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"o Trusted Connection:"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"(useserverName\instanceName as Data Source to use an specifikSQLServer instance, onlySQLServer2000)o Prompt for username and password:oConn.Provider = "sqloledb"oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Data Source=Aron1;Initial Catalog=pubs;"o Connect via an ip address:"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;InitialCatalog=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)o Standard 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"(both connection strings produces the same result)o Trusted Connection:"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"- or -"Server=Aron1;Database=pubs;Trusted_Connection=True;"(both connection strings produces the same result)(useserverName\instanceName as Data Source to use an specifikSQLServer instance, onlySQLServer2000)o Connect via an IP address:"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;UserID=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))o Declare the SqlConnection:C#:using System.Data.SqlClient;SqlConnectionoSQLConn = new SqlConnection();oSQLConn.ConnectionString="my connection string";oSQLConn.Open();:Imports System.Data.SqlClientDim oSQLConn As SqlConnection = New SqlConnection()oSQLConn.ConnectionString="my connection string"oSQLConn.Open()∙ Data Shapeo MS Data Shape"Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=Aron1;InitialCatalog=pubs;User ID=sa;Password=asdasd;"Want to learn data shaping? Check out 4GuyfFromRolla's great article about Data Shaping >>∙ Read moreo How to define which network protocol to use▪Example:"Provider=sqloledb;Data Source=190.190.200.100,1433;NetworkLibrary=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"Name Network librarydbnmpntw Win32 Named Pipesdbmssocn Win32 Winsock TCP/IPdbmsspxn Win32 SPX/IPXdbmsvinn Win32 Banyan Vinesdbmsrpcn Win32 Multi-Protocol (Windows RPC)▪Important note!When connecting through the SQLOLEDB provider use the syntax NetworkLibrary=dbmssocnand when connecting through MSDASQL provider use the syntax Network=dbmssocn o All SqlConnection connection string properties▪This table shows all connection string properties for the SqlConnection object.Most of the properties are also used in ADO. All properties and descriptions is frommsdn.NoteUse ; to separate each property.If a name occurs more than once, the value from the last one in the connection string willbe used.If you are building your connection string in your app using values from user input fields,make sure the user can't change the connection string by inserting an additional propertywith another value within the user value.∙ SQL Server 2005∙ SQL Native Client ODBC Drivero Standard security:"Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"o Trusted connection:"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;"EquivalentsIntegrated Security=SSPI equals Trusted_Connection=yeso Prompt for username and password:oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Driver={SQL Native Client};Server=Aron1;DataBase=pubs;"o Enabling MARS (multiple active result sets):"Driver={SQL NativeClient};Server=Aron1;Database=pubs;Trusted_Connection=yes;MARS_Connection=yes"EquivalentsMultipleActiveResultSets=true equals MARS_Connection=yesUsing MARS with SQL Native Client, by Chris Lee >>o Encrypt data sent over network:"Driver={SQL NativeClient};Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"o Attach a database file on connect to a local SQL Server Express instance:"Driver={SQL NativeClient};Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"- or -"Driver={SQL NativeClient};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"(use |DataDirectory| when your database file resides in the data directory)Why is the "Database" parameter needed? Answer: If the database was previouslyattached, SQL Server does not reattach it (it uses the attached database as the default forthe connection).Download the SQL Native Client here >> (the package contains booth the ODBC driverand the OLE DB provider)Using SQL Server 2005 Express? Don't miss the server name syntax:SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of thecomputer)SQL Native Client OLE DB Providero Standard security:"Provider=SQLNCLI;Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"o Trusted connection:"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;"EquivalentsIntegrated Security=SSPI equals Trusted_Connection=yeso Prompt for username and password:oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"o Enabling MARS (multiple active result sets):"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;MarsConn=yes"EquivalentsMarsConn=yes equals MultipleActiveResultSets=true equals MARS_Connection=yesUsing MARS with SQL Native Client, by Chris Lee >>o Encrypt data sent over network:"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"o Attach a database file on connect to a local SQL Server Express instance: "Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Databa se=dbname;Trusted_Connection=Yes;"- or -"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Dat abase=dbname;Trusted_Connection=Yes;"(use |DataDirectory| when your database file resides in the data directory)Why is the "Database" parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection).Download the SQL Native Client here >> (the package contains booth the ODBC driverand the OLE DB provider)Using SQL Server 2005 Express? Don't miss the server name syntax:SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of thecomputer)SqlConnection (.NET)o Standard 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"(both connection strings produces the same result)o Trusted Connection:"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"- or -"Server=Aron1;Database=pubs;Trusted_Connection=True;"(both connection strings produces the same result)(useserverName\instanceName as Data Source to use an specifikSQLServer instance) o Connect via an IP address:"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;UserID=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))o Enabling MARS (multiple active result sets):"Server=Aron1;Database=pubs;Trusted_Connection=True;MultipleActiveResultSets=true"Note! Use 2.0 for MARS functionality. MARS is not supported in 1.0 nor 1.1Streamline your Data Connections by Moving to MARS, by Laurence Moroney, >>o Attach a database file on connect to a local SQL 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 file resides in the data directory)Why is the "Database" parameter needed? Answer: If the database was previously attached, SQLServer does not reattach it (it uses the attached database as the default for the connection).o Using "User Instance" on a local SQL Server Express instance:"Data Source=.\SQLExpress;integratedsecurity=true;attachdbfilename=|DataDirectory|\mydb.mdf;user instance=true;"The "User Instance" functionality creates a new SQL Server instance on the fly during connect.This works only on a local SQL Server 2005 instance and only when connecting using windowsauthentication over local named pipes. The purpose is to be able to create a full rights SQL Serverinstance to a user with limited administrative rights on the computer. To enable the functionality:sp_configure 'user instances enabled','1' (0 to disable)Using SQL Server 2005 Express? Don't miss the server name syntax:SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of thecomputer)∙ Context Connection - connecting to "self" from within your CLR stored prodedure/function o C#:using(SqlConnection connection = new SqlConnection("context connection=true")){connection.Open();// Use the connection}o Visual Basic:Using connection as new SqlConnection("context connection=true")connection.Open()' Use the connectionEnd UsingThe context connection lets you execute Transact-SQL statements in the same context(connection) that your code was invoked in the first place.∙ Read moreo When to use SQL Native Client?.Net applicationsDo not use the SQL Native Client. Use the .NET Framework Data Provider for SQLServer (SqlConnection).COM applications, all other then .Net applicationsUse the SQL Native Client if you are access ing an SQL Server 2005 and need the newfeatures of SQL Server 2005 such as MARS, encryption, xml data type etc. Continue useyour current provider (OLE DB / ODBC through the MDAC package) if you are notconnecting to an SQL Server 2005 (that's quite obvious eh..) or if you are connecting toan SQL Server 2005 but are not using any of the new SQL Server 2005 features.For more details on the differences between MDAC and SQL Native Client, read thismsdn article >>∙ Access∙ ODBCo Standard Security:"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"o Workgroup:"Driver={Microsoft Access Driver(*.mdb)};Dbq=C:\mydatabase.mdb;SystemDB=C:\mydatabase.mdw;"o Exclusive:"Driver={Microsoft Access Driver(*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="∙ OLE DB, OleDbConnection (.NET)o Standard security:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;UserId=admin;Password=;"o Workgroup (system database):"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;JetOLEDB:SystemDatabase=system.mdw;"o With password:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;JetOLEDB:DatabasePassword=MyDbPassword;"∙Oracle∙ ODBCo New version:"Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;"o Old version:"Driver={Microsoft ODBC Driver forOracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"∙ OLE DB, OleDbConnection (.NET)o Standard security:"Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;"This one's from Microsoft, the following are from Oracleo Standard Security:"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;"o Trusted Connection:"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;"∙ OracleConnection (.NET)o Standard:"Data Source=MyOracleDB;Integrated Security=yes;"This one works only with Oracle 8i release 3 or latero Specifying username and password:"Data Source=MyOracleDB;User Id=username;Password=passwd;Integrated Security=no;"This one works only with Oracle 8i release 3 or latero Declare the OracleConnection:C#:using System.Data.OracleClient;OracleConnectionoOracleConn = new OracleConnection();oOracleConn.ConnectionString = "my connection string";oOracleConn.Open();:Imports System.Data.OracleClientDim oOracleConn As OracleConnection = New OracleConnection()oOracleConn.ConnectionString = "my connection string"oOracleConn.Open()Missing the System.Data.OracleClient namespace? Download .NET Managed Providerfor Oracle >>Great article! "Features of Oracle Data Provider for .NET" by Rama Mohan G. at C#Corner∙ Core Labs OraDirect (.NET)o 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 Shapeo MS Data Shape:"Provider=MSDataShape.1;Persist Security Info=False;Data Provider=MSDAORA;DataSource=orac;user id=username;password=mypw"Want to learn data shaping? Check out 4GuyfFromRolla's great article about Data Shaping >>∙MySQL∙ MyODBCo MyODBC 2.50 Local database:"Driver={mySQL};Server=localhost;Option=16834;Database=mydatabase;"o MyODBC 2.50 Remote database:"Driver={mySQL};Server=;Port=3306;Option=131072;Stmt=;Database=my-database;Uid=username;Pwd=password;"o MyODBC 3.51 Local database:"DRIVER={MySQL ODBC 3.51Driver};SERVER=localhost;DATABASE=myDatabase;USER=myUsername;PASSWORD=myPassword;OPTION=3;"o MyODBC 3.51 Remote database:"DRIVER={MySQL ODBC 3.51Driver};SERVER=;PORT=3306;DATABASE=myDatabase;USER=myUsername;PASSWORD=myPassword;OPTION=3;"∙ OLE DB, OleDbConnection (.NET)o Standard:"Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=asdasd;"∙ Connector/Net 1.0 (.NET)o Standard:"Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;"Download the driver at MySQL Developer Zone.o Specifying port:"Server=Server;Port=1234;Database=Test;Uid=UserName;Pwd=asdasd;"Default port is 3306. Enter value -1 to use a named pipe connection.o Declare the MySqlClient connection:C#:using MySql.Data.MySqlClient;MySqlConnectionoMySqlConn = new MySqlConnection();oMySqlConn.ConnectionString ="Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;";oMySqlConn.Open();:Imports MySql.Data.MySqlClientDim oMySqlConn As MySqlConnection = New MySqlConnection()oMySqlConn.ConnectionString ="Server=Server;Database=Test;Uid=UserName;Pwd=asdasd;"oMySqlConn.Open()MySqlConnection (.NET)o eInfoDesigns.dbProvider:"Data Source=server;Database=mydb;User ID=username;Password=pwd;CommandLogging=false"This one is used with eInfoDesigns dbProvider, an add-on to .NETo Declare the MySqlConnection:C#:using eInfoDesigns.dbProvider.MySqlClient;MySqlConnectionoMySqlConn = new MySqlConnection();oMySqlConn.ConnectionString = "my connection string";oMySqlConn.Open();:Imports eInfoDesigns.dbProvider.MySqlClientDim oMySqlConn As MySqlConnection = New MySqlConnection()oMySqlConn.ConnectionString = "my connection string"oMySqlConn.Open()∙ SevenObjectsMySqlClient (.NET)o Standard:"Host=server; UserName=myusername; Password=mypassword;Database=mydb;"This is a freeware data provider from SevenObjects∙ Core Labs MySQLDirect (.NET)o 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; ConnectionLifetime=0"Read more at Core Lab and the product page.∙ Interbase∙ ODBC, Easysofto Local computer:"Driver={Easysoft IB6ODBC};Server=localhost;Database=localhost:C:\mydatabase.gdb;Uid=username;Pwd=password"o Remote Computer:"Driver={Easysoft IB6ODBC};Server=ComputerName;Database=ComputerName:C:\mydatabase.gdb;Uid=username;Pwd=password"Read more about this driver: Easysoft ODBC-Interbase driver >>∙ ODBC, Intersolvo 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 driver are provided by DataDirect Technologies >> (formerly Intersolv) ∙ OLE DB, SIBPROvidero Standard:"provider=sibprovider;location=localhost:;data source=c:\databases\gdbs\mygdb.gdb;userid=SYSDBA;password=masterkey"o Specifying character set:"provider=sibprovider;location=localhost:;data source=c:\databases\gdbs\mygdb.gdb;userid=SYSDBA;password=masterkey;character set=ISO8859_1"o Specifying role:"provider=sibprovider;location=localhost:;data source=c:\databases\gdbs\mygdb.gdb;userid=SYSDBA;password=masterkey;role=DIGITADORES"Read more about SIBPROvider>>∙Read more about connecting to Interbase in this Borland Developer Network article/article/0,1410,27152,00.html∙ IBM DB2∙ OLE DB, OleDbConnection (.NET) from mso TCP/IP:"Provider=DB2OLEDB;Network Transport Library=TCPIP;NetworkAddress=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;DefaultSchema=Schema;User ID=MyUser;Password=MyPW"o APPC:"Provider=DB2OLEDB;APPC Local LU Alias=MyAlias;APPC Remote LUAlias=MyRemote;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;DefaultSchema=Schema;User ID=MyUser;Password=MyPW"∙ IBM's OLE DB Provider (shipped with IBM DB2 UDB v7 or above)o TCP/IP:Provider=IBMDADB2;Database=sample;HOSTNAME=db2host;PROTOCOL=TCPIP;PORT=50000;uid=myUserName;pwd=myPwd;∙ ODBCo Standard:"driver={IBM DB2 ODBCDRIVER};Database=myDbName;hostname=myServerName;port=myPortNum;protocol=TCPIP;uid=myUserName; pwd=myPwd"∙ Sybase∙ ODBCo Standard Sybase System 12 (or 12.5) Enterprise Open Client:"Driver={SYBASE ASE ODBC Driver};Srvr=Aron1;Uid=username;Pwd=password"o Standard Sybase System 11:"Driver={SYBASE SYSTEM 11};Srvr=Aron1;Uid=username;Pwd=password;Database=mydb"For more information check out the Adaptive Server Enterprise Document Sets o Intersolv 3.10:"Driver={INTERSOLV 3.10 32-BIT Sybase};Srvr=Aron1;Uid=username;Pwd=password;"o Sybase SQL Anywhere (former Watcom SQL ODBC driver):"ODBC; Driver=Sybase SQL Anywhere 5.0;DefaultDir=c:\dbfolder\;Dbf=c:\mydatabase.db;Uid=username;Pwd=password;Dsn="""""Note! The two double quota following the DSN parameter at the end are escaped quotas(VB syntax), you may have to change this to your language specific escape syntax. Theempty DSN parameter is indeed critical as not including it will result in error 7778.Read more in the Sybase SQL Anywhere User Guide (see part 3, chapter 13) >>OLE DBo Adaptive Server Anywhere (ASA):"Provider=ASAProv;Data source=myASA"Read more in the ASA User Guide (part 1, chapter 2) >>o 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.o Adaptive Server Enterprise (ASE):"Provider=Sybase.A SEO LEDBProvider;Srvr=myASEserver,5000;Catalog=myDBname;UserId=username;Password=password"- some reports on problem using the above one, try the following as an alternative -"Provider=Sybase.ASEOLEDBProvider;Server Name=myASEserver,5000;InitialCatalog=myDBname;User Id=username;Password=password"This one works only from Open Client 12.5 where the server port number feature works,?allowingfully qualified connection strings to be used without defining?any .IDS Data Source files.∙ AseConnection (.NET)o Standard:"DataSource='myASEserver';Port=5000;Database='myDBname';UID='username';PWD='password';"o Declare the AseConnection:C#:using Sybase.Data.AseClient;AseConnectionoCon = new AseConnection();oCon.ConnectionString="my connection string";oCon.Open();:Imports System.Data.AseClientDim oCon As AseConnection = New AseConnection()oCon.ConnectionString="my connection string"oCon.Open()Read more! Adaptive Server Enterprise Data Provider Documentation >>∙ Informix∙ ODBCo Informix 3.30:"Dsn='';Driver={INFORMIX 3.30 32 BIT};Host=hostname;Server=myserver;Service=service-name;Protocol=olsoctcp;Database=mydb;UID=username;PWD=myPwdo Informix-CLI 2.5:"Driver={Informix-CLI 2.5 (32Bit)};Server=myserver;Database=mydb;Uid=username;Pwd=myPwd"∙ OLE DBo IBM Informix OLE DB Provider:"Provider=Ifxoledbc.2;password=myPw;User ID=myUser;DataSource=dbName@serverName;Persist Security Info=true"∙ Ingres∙ ODBCo DSN-less"Provider=MSDASQL.1;DRIVER=Ingres;SRVR=xxxxx;DB=xxxxx;Persist SecurityInfo=False;uid=xxxx;pwd=xxxxx;SELECTLOOPS=N;ExtendedProperties="""SERVER=xxxxx;DATABASE=xxxxx;SERVERTYPE=INGRES""∙ Mimer SQL∙ ODBCo Standard Security:"Driver={MIMER};Database=mydb;Uid=myuser;Pwd=mypw;"o Prompt for username and password:"Driver={MIMER};Database=mydb;"∙ Lightbase∙ Standardo Standard:"user=USERLOGIN;password=PASSWORD;UDB=USERBASE;server=SERVERNAME" ∙ PostgreSQL∙ Core Labs PostgreSQLDirect (.NET)o Standard:"User ID=root; Password=pwd; Host=localhost; Port=5432; Database=testdb;Pooling=true; MinPool Size=0; Max Pool Size=100; Connection Lifetime=0"Read more at Core Lab and the product page.∙ PostgreSQL drivero Standard:"DRIVER={PostgreSQL};SERVER=ipaddress;port=5432;DATABASE=dbname;UID=username;PWD=password;"∙ Npgsql by pgFoundry (.NET)o SSL activated:"Server=127.0.0.1;Port=5432;Userid=myuserid;password=mypw;Protocol=3;SSL=true;Pooling=true;MinPoolSize=3;MaxPoolSize=20;Encoding=UNICODE;Timeout=20;SslMode=Require"Without SSL:"Server=127.0.0.1;Port=5432;Userid=myuserid;password=mypw;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable"Read more in the Npgsql: User's Manual and on the pgFoundry website.∙ Paradox∙ ODBCo 5.X:Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox5.X;DefaultDir=c:\pathToDb\;Dbq=c:\pathToDb\;CollatingSequence=ASCII"o 7.X:"Provider=MSDASQL.1;Persist Security Info=False;Mode=Read;ExtendedProperties='DSN=Paradox;DBQ=C:\myDb;DefaultDir=C:\myDb;DriverId=538;FIL=Paradox7.X;MaxBufferSize=2048;PageTimeout=600;';Initial Catalog=C:\myDb"∙ OleDbConnection (.NET)o Standard"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myDb;Extended Properties=Paradox 5.x;"MS kb-article: How to use Paradox data with Access and Jet >>∙ DSN∙ ODBCo DSN:"DSN=myDsn;Uid=username;Pwd=;"o File DSN:"FILEDSN=c:\myData.dsn;Uid=username;Pwd=;"∙ Firebird∙ ODBC - IBPhoenix Open Sourceo Standard:"DRIVER=Firebird/InterBase(r)driver;UID=SYSDBA;PWD=masterkey;DBNAME=D:\FIREBIRD\examples\TEST.FDB"IBPhoenix ODBC; More info, download etc>>∙ .NET - Firebird .Net Data Providero Standard:"User=SYSDBA;Password=masterkey;Database=SampleDatabase.fdb;DataSource=localhost;Port=3050;Dialect=3;Charset=NONE;Role=;Connectionlifetime=15;Pooling=true;MinPoolSize=0;MaxPoolSize=50;Packet Size=8192;ServerType=0"Firebird project >>Firebird downloads >>∙Excel∙ ODBCo Standard:"Driver={Microsoft Excel Driver(*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;"TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$"and wrapped in "[" "]" brackets.∙ OLE DBo Standard:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel8.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 textTIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$"and wrapped in "[" "]" brackets.∙ Text∙ ODBCo Standard:"Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;"∙ OLE DBo Standard:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;ExtendedProperties=""text;HDR=Yes;FMT=Delimited""""HDR=Yes;" indicates that the first row contains columnnames, not data∙ DBF / FoxPro∙ ODBCo standard:"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\mydbpath;"∙ OLE DB, OleDbConnection (.NET)o standard:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASEIV;UserID=Admin;Password="∙ AS/400 (iSeries)∙ OLE DB, OleDbConnection (.NET)o IBM Client Access OLE DB provider:"PROVIDER=IBMDA400; DATA SOURCE=MY_SYSTEM_NAME;USERID=myUserName;PASSWORD=myPwd"Where MY_SYSTEM_NAME is the name given to the system connection inOpera tionsNavigatoro IBM Client Access OLE DB provider:"PROVIDER=IBMDA400; DATA SOURCE=MY_SYSTEM_NAME;USERID=myUserName;PASSWORD=myPwd;DEFAULT COLLECTION=MY_LIBRARY;"。
数据库连接-连接字符串
一.数据库的连接字符串string connectionstr = "Data Source=PC201305072142\\SQLEXPRESS;Da taBase=db_stu;User ID=sa;pwd=123456";Data Source后加你的连接服务器，DataBase后加连接的数据库ID=sa;pwd=123456ID:用户名Pwd:密码二．C#语言实现SQL SERVER、access连接SQL SERVER：添加 using System.Data.SqlClient;SqlConnection con =new SqlConnection("server=.;database=数据库名;uid=sa;pwd=密码");//密码是你数据库sa的登录的密码或者是 SqlConnection con = new SqlConnection("Data source=你的数据库服务器;Initial Catalog=要连接的数据库名;trusetd_connection=sspi");//c是window身份认证的方式access:添加using System.Data.OleDb;OleDbConnection omd=newOleDbConnection("Provider=Microsoft.jet.oldb.4.0;database=数据库的相对路径");//路径是想对你程序运行的路径三.问：如何手动设置数据库连接（数据库的IP、登录密码、用户名称以让用户自己输入一次，然后把它们保存到配置文件中，以后就不需要再输了，直接从配置文件中读取就行了）答：1、在解决方案中添加一个新的类，命名CL_Conn，用于连接数据库。
完整代码如下：using System;using System.Collections.Generic;using ponentModel;using System.Text;using System.Windows.Forms;using Microsoft.Data.ConnectionUI;namespace CL_Conn{public class Conn{/// <summary>/// 获取 自带的数据库连接对话框的数据库连接信息/// </summary>/// <returns>数据库连接</returns>public string GetDatabaseConnectionString(){string connString = String.Empty;Microsoft.Data.ConnectionUI.DataConnectionDialog connDialog = new Microsoft.Data.ConnectionUI.DataConnectionDialog();// 添加数据源列表，可以向窗口中添加自己程序所需要的数据源类型必须增加以下几项中任一一项connDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.AccessDataSo urce); // AccessconnDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.OdbcDataSou rce); // ODBCconnDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.OracleDataSo urce); // OracleconnDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.SqlDataSourc e); // Sql ServerconnDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.SqlFileDataSo urce); // Sql Server File// 初始化connDialog.SelectedDataSource =Microsoft.Data.ConnectionUI.DataSource.SqlDataSource;connDialog.SelectedDataProvider =Microsoft.Data.ConnectionUI.DataProvider.SqlDataProvider;//只能够通过DataConnectionDialog类的静态方法Show出对话框//不同使用dialog.Show()或dialog.ShowDialog()来呈现对话框if(Microsoft.Data.ConnectionUI.DataConnectionDialog.Show(connDialog) == DialogResult.OK){connString = connDialog.ConnectionString;}return connString;}}}2、编译。
SQL Server连接字符串
连接字符串中常用的声明有：服务器声明Data Source、Server和Addr等。
数据库声明Initial Catalog和DataBase等。
集成Windows账号的安全性声明Integrated Security和Trusted_Connection等。
使用数据库账号的安全性声明User ID和Password等。
对于访问数据库的账号来说，通常我们在一些参考资料上看到的字符串连接往往有如下写法：复制代码代码如下:string ConnStr = "server = localhost;user id = sa; password = xxx; database = northwind";对于集成Windows安全性的账号来说，其连接字符串写法一般如下：复制代码代码如下:string ConnStr = "server = localhost;integrated security = sspi; database = northwind";或string ConnStr = "server = localhost;trusted_connection = yes; database = northwind";使用Windows集成的安全性验证在访问数据库时具有很多优势：安全性更高、访问速度更快、减少重新设计安全架构的工作、可以硬编码连接字符串等，还是很值得使用的。
SQL Native Client ODBC Driver标准安全连接复制代码代码如下:Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;您是否在使用SQL Server 2005 Express？请在“Server”选项使用连接表达式“主机名称\SQLEXPRESS”。
数据库连接字符串大全
数据库连接字符串大全转自：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)Standard 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"(both connection strings produces the same result)Trusted Connection："Data Source=Aron1；Initial Catalog=pubs；IntegratedSecurity=SSPI；"-or-"Server=Aron1；Database=pubs；Trusted_Connection=True；"(both connection strings produces the same result)(use serverName\instanceName as Data Source to use an specifik SQLServer instance,only SQLServer2000)Connect via an 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,at the end of the Data Source is the port to use(1433 is the default))Declare the SqlConnection：C#：using System.Data.SqlClient；SqlConnection oSQLConn=new SqlConnection()；oSQLConn.ConnectionString="my connection string"；oSQLConn.Open()；：Imports System.Data.SqlClient Dim oSQLConn As SqlConnection=New SqlConnection()oSQLConn.ConnectionString="my connection string"oSQLConn.Open()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 greatarticle about Data Shaping Read more How to define which 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 connection string properties This table shows all connection string properties for the 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 aconnection to the server before terminating the attempt and generating an error.Connection Lifetime 0When aconnection is returned to the pool,its creation time is com pared with the current time,and the connection is destroyed if that time span(in seconds)exceeds the value specified by connection eful in clustered configurations to force load balancing between arunning server and aserver 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 aconnection,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 asecure 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 0The minimum number of connections allowed in the pool.Network Library-or-Net'dbmssocn'The network library used to establish aconnection to an instance of SQL Server.Supported values include dbnmpntw(Named Pipes),dbmsrpcn(Multiprotocol),dbmsadsn(AppleTalk),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 anetwork and you use alocal 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 inf ormation,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 aname occurs more than once,the value from the last one in the connection string will be used.If you are building your connection string in your app using values from user input fields,make sure the user can't change the connection string by inserting an additional property with another value within the user value.SQL Server 2005 SQL Native Client ODBC Driver Standard security："Driver={SQL Native Client}；Server=Aron1；Database=pubs；UID=sa；PWD=asdasd；"Trusted connection："Driver={SQL Native Client}；Server=Aron1；Database=pubs；Trusted_Connection=yes；"Equivalents Integrated Security=SSPI equalsTrusted_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={SQL Native Client}；Server=Aron1；Database=pubs；Trusted_Connection=yes；MARS_Connection=yes"Equivalents MultipleActiveResultSets=true equalsMARS_Connection=yes Using MARS with SQL Native Client,by Chris Lee Encrypt data sent over network："Driver={SQL Native Client}；Server=Aron1；Database=pubs；Trusted_Connection=yes；Encrypt=yes"Attach adatabase file on connect to alocal SQL Server Express instance："Driver={SQL Native Client}；Server=.\SQLExpress；AttachDbFilename=c：\asd\qwe\mydbfile.mdf；Database=dbname；Trusted_Connection=Yes；"-or-"Driver={SQL Native Client}；Server=.\SQLExpress；AttachDbFilename=|DataDirectory|mydbfile.mdf；Database=dbname；Trusted_Connection=Yes；"(use|DataDirectory|when your database file resides in the data directory)Why is the"Database"parameter needed?Answer：If the database was previously attached,SQL Server does not reattach it(it uses the attached database as the default for the connection).Download the SQL Native Client here(the package contains booth the ODBC driver and the OLE DB provider)Using SQL Server 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 Integrated Security=SSPI equalsTrusted_Connection=yes Prompt for username and password：oConn.Properties("Prompt")=adPromptAlwaysoConn.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=yes equals MultipleActiveResultSets=true equals MARS_Connection=yes Using MARS with SQL Native Client,by Chris Lee Encrypt data sent over network："Provider=SQLNCLI；Server=Aron1；Database=pubs；Trusted_Connection=yes；Encrypt=yes"Attach adatabase file on connect to alocal SQL 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 file resides in the data directory)Why is the"Database"parameter needed?Answer：If the database was previously attached,SQL Server does not reattach it(it uses the attached database as the default for the connection).Download the SQL Native Client here(the package contains booth the ODBC driver and the OLE DB provider)Using SQL Server 2005 Express?Don't miss the server name syntax：SERVERNAME\SQLEXPRESS(Substitute"SERVERNAME"with the name of the computer)SqlConnection(.NET)Standard 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"(both connection strings produces the same result)Trusted Connection："Data Source=Aron1；Initial Catalog=pubs；IntegratedSecurity=SSPI；"-or-"Server=Aron1；Database=pubs；Trusted_Connection=True；"(both connection strings produces the same result)(use serverName\instanceName as Data Source to use an specifik SQLServer instance)Connect via an 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,at the end of the Data Source is the port to use(1433 is the default))Enabling MARS(multiple active result sets)："Server=Aron1；Database=pubs；Trusted_Connection=True；MultipleActiveResultSets=true"Note！Use 2.0 for MARS functionality.MARS is not supported in 1.0 nor 1.1 Streamline your Data Connections by Moving to MARS,by Laurence Moroney, Attach adatabase file on connect to alocal SQL 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 file resides in the data directory)Why is the"Database"parameter needed?Answer：If the database was previously attached,SQL Server does not reattach it(it uses the attached database as the default for the connection).Using"User Instance"on alocal SQL Server Express instance："Data Source=.\SQLExpress；integrated security=true；attachdbfilename=|DataDirectory|\mydb.mdf；user instance=true；"The"User Instance"functionality creates anew SQL Server instance on the fly during connect.This works only on alocal SQL Server 2005 instance and only when connecting using windows authentication over local named pipes.The purpose is to be able to create afull rights SQL Server instance to auser with limited administrative rights on the computer.To enable the functionality：sp_configure'user instances enabled','1'(0 to disable)Using SQL Server 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(SqlConnection connection=new SqlConnection("context connection=true")){connection.Open()；//Use the connection}Visual Basic：Using connection as new SqlConnection("context connection=true")connection.Open()'Use the connection End Using The context connection lets you execute Transact-SQL statements in the same context(connection)that your code was invoked in the first place.Read more When to use SQL Native Client?.Net applications Do not use the SQL Native e Framework Data Provider for SQL Server(SqlConnection).COM applications,all other applications Use the SQL Native Client if you are accessing an SQL Server 2005 and need the new features of SQL Server 2005 such as MARS,encryption,xml data type etc.Continue use your current provider(OLE DB/ODBC through the MDAC package)if you are not connecting to an SQL Server 2005(that's quite obvious eh.)or if you are connecting to an SQL Server 2005 but arenot using any of the new SQL Server 2005 features.For more details on the differences between MDAC and SQL Native Client,read this msdn article 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 New version："Driver={Microsoft ODBC for Oracle}；Server=OracleServer.world；Uid=Username；Pwd=asdasd；"Old version："Driver={Microsoft ODBC Driver for Oracle}；ConnectString=OracleServer.world；Uid=myUsername；Pwd=myPassword；"OLE DB,OleDbConnection(.NET)Standard security："Provider=msdaora；Data Source=MyOracleDB；User Id=UserName；Password=asdasd；"This one's from Microsoft,the following are from Oracle Standard Security："Provider=OraOLEDB.Oracle；Data Source=MyOracleDB；UserId=Username；Password=asdasd；"Trusted Connection："Provider=OraOLEDB.Oracle；Data Source=MyOracleDB；OSAuthent=1；"OracleConnection(.NET)Standard："Data Source=MyOracleDB；Integrated Security=yes；"This one works only with Oracle 8i release 3or later Specifying username and password："Data Source=MyOracleDB；User Id=username；Password=passwd；Integrated Security=no；"This one works only with Oracle 8i release 3or later Declare the OracleConnection：C#：using System.Data.OracleClient；OracleConnection oOracleConn=new OracleConnection()；oOracleConn.ConnectionString="my connection string"；oOracleConn.Open()；：Imports System.Data.OracleClient Dim oOracleConn As OracleConnection=New OracleConnection()oOracleConn.ConnectionString="my connection string"oOracleConn.Open()Missing the System.Data.OracleClient namespace? Managed Provider for Oracle Great article！"Features of Oracle Data Provider "by Rama Mohan G.at C#Corner Core Labs OraDirect(.NET)"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 greatarticle about Data Shaping MySQL MyODBC MyODBC 2.50 Local database："Driver={mySQL}；Server=localhost；Option=16834；Database=mydatabase；"MyODBC 2.50 Remote database："Driver={mySQL}；Server=；Port=3306；Option=131072；Stmt=；Database=my-database；Uid=username；Pwd=password；"MyODBC 3.51 Local database："DRIVER={MySQL ODBC 3.51 Driver}；SERVER=localhost；DATABASE=myDatabase；USER=myUsername；PASSWORD=myPassword；OPTION=3；"MyODBC 3.51 Remote database："DRIVER={MySQL ODBC 3.51 Driver}；SERVER=；PORT=3306；DATABASE=myDatabase；USER=myUsername；PASSWORD=myPassword；OPTION=3；"OLE DB,OleDbConnection(.NET)"Provider=MySQLProv；Data Source=mydb；User Id=UserName；Password=asdasd；"Connector/Net 1.0(.NET)Standard："Server=Server；Database=Test；Uid=UserName；Pwd=asdasd；"Download the driver at MySQL Developer Zone.Specifying port："Server=Server；Port=1234；Database=Test；Uid=UserName；Pwd=asdasd；"Default port is 3306.Enter value-1 to use anamed pipe connection.Declare the MySqlClient connection：C#：using MySql.Data.MySqlClient；MySqlConnection oMySqlConn=new MySqlConnection()；oMySqlConn.ConnectionString="Server=Server；Database=Test；Uid=UserName；Pwd=asdasd；"；oMySqlConn.Open()；：Imports MySql.Data.MySqlClient Dim oMySqlConn As MySqlConnection=New MySqlConnection()oMySqlConn.ConnectionString="Server=Server；Database=Test；Uid=UserName；Pwd=asdasd；"oMySqlConn.Open()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 Declare the MySqlConnection：C#：using eInfoDesigns.dbProvider.MySqlClient；MySqlConnection oMySqlConn=new MySqlConnection()；oMySqlConn.ConnectionString="my connection string"；oMySqlConn.Open()；：Imports eInfoDesigns.dbProvider.MySqlClient Dim oMySqlConn 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 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；ConnectionLifetime=0"Read more at Core Lab and the product page.Interbase ODBC,Easysoft Local computer："Driver={Easysoft IB6 ODBC}；Server=localhost；Database=localhost：C：\mydatabase.gdb；Uid=username；Pwd=password"Remote Computer："Driver={Easysoft IB6 ODBC}；Server=ComputerName；Database=ComputerName：C：\mydatabase.gdb；Uid=username；Pwd=password"Read more about this driver：Easysoft ODBC-Interbase driver ODBC,Intersolv Local computer："Driver={INTERSOLV InterBase ODBC Driver(*.gdb)}；Server=localhost；Database=localhost：C：\mydatabase.gdb；Uid=username；Pwd=password"Remote Computer："Driver={INTERSOLV InterBase ODBC Driver(*.gdb)}；Server=ComputerName；Database=ComputerName：C：\mydatabase.gdb；Uid=username；Pwd=password"This driver are provided by DataDirect Technologies(formerly 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 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"IBM's OLE DB Provider(shipped with IBM DB2 UDB v7 or above)TCP/IP：Provider=IBMDADB2；Database=sample；HOSTNAME=db2host；PROTOCOL=TCPIP；PORT=50000；uid=myUserName；pwd=myPwd；ODBC Standard："driver={IBM DB2 ODBC DRIVER}；Database=myDbName；hostname=myServerName；port=myPortNum；protocol=TCPIP；uid=myUserName；pwd=myPwd"Sybase ODBC Standard Sybase System 12(or 12.5)Enterprise Open Client："Driver={SYBASE ASE ODBC Driver}；Srvr=Aron1；Uid=username；Pwd=password"Standard Sybase System 11："Driver={SYBASE SYSTEM 11}；Srvr=Aron1；Uid=username；Pwd=password；Database=mydb"For more information check out the Adaptive Server Enterprise Document Sets Intersolv 3.10："Driver={INTERSOLV 3.10 32-BIT Sybase}；Srvr=Aron1；Uid=username；Pwd=password；"Sybase SQL Anywhere(former Watcom SQL ODBC driver)："ODBC；Driver=Sybase SQL Anywhere 5.0；DefaultDir=c：\dbfolder\；Dbf=c：\mydatabase.db；Uid=username；Pwd=password；Dsn="""""Note！The two double quota following the DSN parameter at the end are escaped quotas(VB syntax),you may have to change this to your language specific escape syntax.The empty DSN parameter is indeed critical as not including it will result in error 7778.Read more in the Sybase SQL Anywhere User Guide(see part3,chapter 13)OLE DB Adaptive Server Anywhere(ASA)："Provider=ASAProv；Data source=myASA"Read more in the ASA User Guide(part 1,chapter 2)Adaptive Server Enterprise(ASE)with Data Source.IDS file："Provider=Sybase ASE OLE DB Provider；Data source=myASE"Note that you must create aData 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)Standard："Data Source='myASEserver'；Port=5000；Database='myDBname'；UID='username'；PWD='password'；"Declare the AseConnection：C#：using Sybase.Data.AseClient；AseConnection oCon=new AseConnection()；oCon.ConnectionString="my connection string"；oCon.Open()；：Imports System.Data.AseClient Dim oCon As AseConnection=New AseConnection()oCon.ConnectionString="my connection string"oCon.Open()Read more！Adaptive Server Enterprise Data Provider Documentation 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 IBM Informix OLE DB Provider："Provider=Ifxoledbc.2；password=myPw；User ID=myUser；Data[email=Source=dbName@serverName；Persist]Source=dbName@serverName；Persist[/email]Security Info=true"Ingres ODBC DSN-less"Provider=MSDASQL.1；DRIVER=Ingres；SRVR=xxxxx；DB=xxxxx；Persist Security Info=False；uid=xxxx；pwd=xxxxx；SELECTLOOPS=N；Extended Properties="""SERVER=xxxxx；DATABASE=xxxxx；SERVERTYPE=INGRES""Mimer SQL ODBC Standard Security："Driver={MIMER}；Database=mydb；Uid=myuser；Pwd=mypw；"Prompt for username and password："Driver={MIMER}；Database=mydb；"Lightbase Standard Standard："user=USERLOGIN；password=PASSWORD；UDB=USERBASE；server=SERVERNAME"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.PostgreSQL driver Standard："DRIVER={PostgreSQL}；SERVER=ipaddress；port=5432；DATABASE=dbname；UID=username；PWD=password；"Npgsql by pgFoundry(.NET)SSL activated："Server=127.0.0.1；Port=5432；Userid=myuserid；password=mypw；Protocol=3；SSL=true；Pooling=true；MinPoolSize=3；MaxPoolSize=20；Encoding=UNICODE；Timeout=20；SslMode=Require"Without SSL："Server=127.0.0.1；Port=5432；Userid=myuserid；password=mypw；Protocol=3；SSL=false；Pooling=true；MinPoolSize=1；MaxPoolSize=20；Encoding=UNICODE；Timeout=15；SslMode=Disable"Read more in the Npgsql：User's Manual and on the pgFoundry website.Paradox ODBC 5.X：Driver={Microsoft Paradox Driver(*.db)}；DriverID=538；Fil=Paradox 5.X；DefaultDir=c：\pathToDb\；Dbq=c：\pathToDb\；CollatingSequence=ASCII"7.X："Provider=MSDASQL.1；Persist Security Info=False；Mode=Read；Extended Properties='DSN=Paradox；DBQ=C：\myDb；DefaultDir=C：\myDb；DriverId=538；FIL=Paradox 7.X；MaxBufferSize=2048；PageTimeout=600；'；Initial Catalog=C：\myDb"OleDbConnection(.NET)Standard"Provider=Microsoft.Jet.OLEDB.4.0；Data Source=c：\myDb；Extended Properties=Paradox 5.x；"MS kb-article：How to use Paradox data with Access and Jet DSN ODBC DSN："DSN=myDsn；Uid=username；Pwd=；"File DSN："FILEDSN=c：\myData.dsn；Uid=username；Pwd=；"Firebird ODBC-IBPhoenix Open Source Standard："DRIVER=Firebird/InterBase(r)driver；UID=SYSDBA；PWD=masterkey；DBNAME=D：\FIREBIRD\examples\TEST.FDB"IBPhoenix ODBC；More info,download etc Data Provider Standard："User=SYSDBA；Password=masterkey；Database=SampleDatabase.fdb；DataSource=localhost；Port=3050；Dialect=3；Charset=NONE；Role=；Connection lifetime=15；Pooling=true；MinPoolSize=0；MaxPoolSize=50；Packet Size=8192；ServerType=0"。
SQLServer数据库连接字符串的声明
SQLServer数据库连接字符串的声明
连接字符串中常⽤的声明有：
服务器声明 Data Source、Server和Addr等。
数据库声明 Initial Catalog和DataBase等。
集成Windows账号的安全性声明 Integrated Security和Trusted_Connection等。
使⽤数据库账号的安全性声明 User ID和Password等。
对于访问数据库的账号来说，通常我们在⼀些参考资料上看到的字符串连接往往有如下写法：
string ConnStr = "server = localhost; user id = sa; password = xxx; database = northwind";
对于集成Windows安全性的账号来说，其连接字符串写法⼀般如下：
string ConnStr = "server = localhost; integrated security = sspi; database = northwind";
或string ConnStr = "server = localhost; trusted_connection = yes; database = northwind";
使⽤Windows集成的安全性验证在访问数据库时具有很多优势：安全性更⾼、访问速度更快、减少重新设计安全架构的⼯作、可以硬编码连接字符串等，还是很值得使⽤的。
连接字符串
配置文件：<connectionStrings><add name="connStr"connectionString="Data Source=.;Initial Catalog=数据库名;Integrated Security=true"providerName="System.Data.SqlClient"/></connectionStrings>连接字符串：string connStr =ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;sqlserver连接字符串string ConnStr = "server=localhost;uid=sa;pwd=123;database=Student" Access连接字符串：string ConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(@"\Student.mdb");清除表中数据：using (SqlConnection conn = new SqlConnection(connStr)){conn.Open();using(SqlCommand comm=conn.CreateCommand()){mandText = "delete from 表";comm.ExecuteNonQuery();}}插入:using (SqlConnection conn = new SqlConnection(connStr)){conn.Open();using (SqlCommand comm = conn.CreateCommand()){mandText = "Insert into 表(字段1,字段2,字段3)values(@字段1,@字段2,@字段3)";foreach (string file in files){string asd =Path.GetFileNameWithoutExtension(file);//不具有扩展名的文件名string[] lines = File.ReadAllLines(file, Encoding.Default);foreach (string line in lines){string []strs=line.Split("_");string a1=strs[0];string a2=strs[1];string a3=strs[2];comm.Parameters.Add("字段1", a1);comm.Parameters.Add("字段2", a2);comm.Parameters.Add("字段3", a3);}}}}查询：using (SqlConnection conn = new SqlConnection(connStr)){conn.Open();using (SqlCommand comm = conn.CreateCommand()){mandText = "select * from 表 where 字段1=123";comm.Parameters.Add(new SqlParameter(字段2,textBox1.Text));using(SqlDataReader dr=comm.ExecuteReader()){if (dr.Read()){string name =dr.GetString(dr.GetOrdinal("Name"));MessageBox.Show("姓名:" + name);}else{MessageBox.Show("查询失败！");}}}}通过代码绑定数据源：if (!Page.IsPostBack)//判断是否是首次加载页面{//与数据库连接字符串string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//创建SqlConnection对象SqlConnection conn = new SqlConnection(connStr);conn.Open();//打开与数据库的连接//创建SqlDataAdapter对象SqlDataAdapter ad = new SqlDataAdapter("Select * from StudentInfo", conn);//创建DataSet对象DataSet ds = new DataSet();ad.Fill(ds);//填充DataSet对象//对GridView对象绑定数据源this.GridView1.DataSource = ds;//此句必须有，否则页面上显示不出数据this.GridView1.DataBind();if (conn.State == ConnectionState.Open){//判断连接状态，若连接就关闭连接。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
SQL Server 2008连接字符串写法大全一、.NET Framework Data Provider for SQL Server类型：.NET Framework类库使用：System.Data.SqlClient.SqlConnection厂商：Microsoft标准安全连接Data Source = myServerAddress;Initial Catalog = myDataBase;User Id = myUsername;Password = myPassword;使用服务器名\实例名作为连接指定SQL Server实例的数据源。
如果你使用的是SQL Server 2008 Express版，实例名为SQLEXPRESS。
可替代的标准安全连接Server = myServerAddress;Database = myDataBase;User ID = myUsername;Password = myPassword;Trusted_Connection = False;这条连接字符串跟上一条效果一样。
把这条写出来只是想说，其实很多连接字符串的关键字有多种写法。
信任连接Data Source = myServerAddress;Initial Catalog = myDataBase;Integrated Security = SSPI;可替代的信任连接Server = myServerAddress;Database = myDataBase;Trusted_Connection = True;连接Windows CE设备的信任连接通常一台Windows CE设备在一个域里是不能被认证和登录的。
为了让一台CE设备使用SSPI 或信任连接和认证，可以使用下面的连接字符串：Data Source = myServerAddress;Initial Catalog = myDataBase;Integrated Security = SSPI;User ID = myDomain\myUsername;Password = myPassword;说明一下，这条语句只能在CE设备上用。
使用IP地址的连接Data Source = 190.168.1.100,1433;Network Library = DBMSSOCN;Initial Catalog = myDataBase;UserID = myUsername;Password = myPassword;这条语句用TCP/IP地址替代了命名管道。
在Data Source字段最后的是使用的端口。
SQL Server默认使用的端口是1433。
开启MARS功能(multiple active result sets)Server = myServerAddress;Database = myDataBase;Trusted_Connection = True; MultipleActiveResultSets = true;MARS不支持 1.0和 1.1。
在连接到SQL Server Express实例时附加一个数据库文件Server = .\SQLExpress;AttachDbFilename = c:\asd\qwe\mydbfile.mdf;Database = dbname; Trusted_Connection = Yes;这里为什么还需要Database字段呢？因为如果指定的数据库文件已经被附加，那么SQL Server不会再附加它，而使用已附加的数据库作为默认数据库。
在连接到SQL Server Express实例时，从数据目录下附加一个数据库文件Server = .\SQLExpress;AttachDbFilename = |DataDirectory|mydbfile.mdf; Database = dbname;Trusted_Connection = Yes;在本地SQL Server Express实例上使用用户实例用户实例这个功能会在连接的过程中，新建一个SQL Server实例。
该功能只有在本地SQL Server实例上，且连接时使用通过本地命名通道的Windows认证。
这样做的好处是，可以给一个在本地计算机上只有相当有限的管理员权限的用户，新建一个拥有全部权限的SQL Server 实例。
Data Source = .\SQLExpress;Integrated Security = true; AttachDbFilename =|DataDirectory|\mydb.mdf;User Instance = true;若想使用用户实例这个功能，需要先在SQL Server将其开启。
开启命令为：sp_configure 'user instances enabled', '1'取消命令为：sp_configure 'user instances enabled', '0'注：这两条命令只在SQL Server Express中有效。
数据库镜像如果你用或者SQL Native Client去连接一个数据库镜像,当这个数据库镜像进行故障切换的时候,你的应用程序可以利用驱动器的特性去自动重定向连接。
当然，你必须在连接字段里指定初始的主服务器和数据库，以及用于故障切换的镜像服务器。
Data Source = myServerAddress;Failover Partner = myMirrorServerAddress;Initial Catalog = myDataBase;IntegratedSecurity = True;上面的例子只是介绍了一下如何使用数据库镜像，你可以将Failover Partner字段跟其他连接字符串功能组合使用。
异步处理Server = myServerAddress;Database = myDataBase;Integrated Security =True;Asynchronous Processing = True;二、SQL Server Native Client 10.0 OLE DB Provider类型：OLE DB Provider使用：Provider=SQLNCLI10厂商：Microsoft标准安全连接Provider = SQLNCLI10;Server = myServerAddress;Database = myDataBase;Uid = myUsername; Pwd = myPassword;信任连接Provider = SQLNCLI10;Server = myServerAddress;Database = myDataBase;Trusted_Connection = yes;"Integrated Security=SSPI" 和"Trusted_Connection=yes"是等价的。
连接一个SQL Server实例Provider = SQLNCLI10;Server = myServerName\theInstanceName;Database = myDataBase; Trusted_Connection = yes;用户名、密码提示oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Provider = SQLNCLI10;Server = myServerAddress;DataBase = myDataBase;开启MARS功能(multiple active result sets)Provider = SQLNCLI10;Server = myServerAddress;Database = myDataBase; Trusted_Connection = yes;MARS Connection = True;加密模式Provider = SQLNCLI10;Server = myServerAddress;Database = myDataBase; Trusted_Connection = yes;Encrypt = yes;在连接到SQL Server Express实例时附加一个数据库文件Provider = SQLNCLI10;Server = .\SQLExpress;AttachDbFilename =c:\asd\qwe\mydbfile.mdf; Database = dbname;Trusted_Connection = Yes;在连接到SQL Server Express实例时，从数据目录下附加一个数据库文件Provider = SQLNCLI10;Server = .\SQLExpress;AttachDbFilename =|DataDirectory|mydbfile.mdf;Database = dbname;Trusted_Connection = Yes;数据库镜像Provider = SQLNCLI10;Data Source = myServerAddress;Failover Partner = myMirrorServerAddress;InitialCatalog = myDataBase;Integrated Security = True;三、.NET Framework Data Provider for OLE DB类型：.NET Framework Wrapper Class Library使用：System.Data.OleDb.OleDbConnection厂商：Microsoft桥接到SQL Native Client OLE DBProvider = SQLNCLI10;Server = myServerAddress;Database = myDataBase;Uid = myUsername; Pwd = myPassword;四、SQL Server Native Client 10.0 ODBC Driver类型：ODBC Driver使用：Driver={SQL Server Native Client 10.0}厂商：Microsoft标准安全连接Driver = {SQL Server Native Client 10.0};Server = myServerAddress;Database = myDataBase;Uid = myUsername;Pwd = myPassword;信任连接Driver = {SQL Server Native Client 10.0};Server = myServerAddress;Database = myDataBase;Trusted_Connection = yes;连接一个SQL Server实例Driver = {SQL Server Native Client 10.0};Server = myServerName\theInstanceName; Database = myDataBase;Trusted_Connection = yes;用户名、密码提示oConn.Properties("Prompt") = adPromptAlwaysDriver = {SQL Server Native Client 10.0};Server = myServerAddress;Database = myDataBase;开启MARS功能(multiple active result sets)Driver = {SQL Server Native Client 10.0};Server = myServerAddress;Database = myDataBase;Trusted_Connection = yes;MARS_Connection = yes;加密模式Driver = {SQL Server Native Client 10.0};Server = myServerAddress;Database = myDataBase;Trusted_Connection = yes;Encrypt = yes;在连接到SQL Server Express实例时附加一个数据库文件Driver = {SQL Server Native Client 10.0};Server = .\SQLExpress; AttachDbFilename = c:\asd\qwe\mydbfile.mdf;Database = dbname;Trusted_Connection = Yes;在连接到SQL Server Express实例时，从数据目录下附加一个数据库文件Driver={SQL Server Native Client10.0};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;数据库镜像Driver = {SQL Server Native Client10.0};Server = myServerAddress;Failover_Partner =myMirrorServerAddress;Database = myDataBase; Trusted_Connection = yes;五、.NET Framework Data Provider for ODBC类型：.NET Framework Wrapper Class Library使用：System.Data.Odbc.OdbcConnection厂商：Microsoft桥接到SQL Native Client 10.0 ODBC Driver下面的语句只是一条例子，不同厂商的ODBC驱动不同。