I. NET Framework Data Provider for SQL Server
Type:. NET Framework Class Library
Use: System.Data.SqlClient.SqlConnection
Manufacturer: Microsoft
1. Standard secure connection
Copy Code code as follows:
Data Source = myserveraddress;initial Catalog = myDataBase; User Id = MyUserName; Password = mypassword;
Use the server name \ Instance name as the connection to specify the data source for the SQL Server instance. If you are using the SQL Server 2008 Express Edition, the instance name is SQLExpress.
2. Alternative Standard secure connection
Copy Code code as follows:
Server = Myserveraddress;database = MyDataBase; User ID = MyUserName; Password = mypassword; Trusted_Connection = False;
This connection string has the same effect as the previous one. Write this out just to say, in fact, many of the keywords of the connection string are written in many ways.
3. Trust Connection
Copy Code code as follows:
Data Source = myserveraddress;initial Catalog = mydatabase;integrated Security = SSPI;
Alternative Trust connections
Copy Code code as follows:
Server = Myserveraddress;database = MyDataBase; Trusted_Connection = True;
4. Connect a trusted connection to a Windows CE device
Usually a Windows CE device cannot be authenticated and logged in a domain. In order for a CE device to use SSPI or trusted connections and authentication, you can use the following connection string:
Copy Code code as follows:
Data Source = myserveraddress;initial Catalog = mydatabase;integrated Security = SSPI; User ID = MyDomain
\myusername; Password = mypassword;
To illustrate, this statement can only be used on the CE device.
5. Connect using IP Address
Copy Code code as follows:
Data Source = 190.168.1.100,1433; Network Library = dbmssocn;initial Catalog = myDataBase; User
ID = MyUserName; Password = mypassword;
This statement replaces the named pipe with a TCP/IP address. At the end of the data source field is the port used. The default port used by SQL Server is 1433.
6. Turn on Mars function (multiple active result sets)
Copy Code code as follows:
Server = Myserveraddress;database = MyDataBase; Trusted_Connection = True; Multipleactiveresultsets = true;
Mars does not support Ado.net 1.0 and Ado.net 1.1.
7. Attach a database file when connecting to the SQL Server Express instance
Copy Code code as follows:
Server =. \SQLExpress; AttachDbFileName = C:\asd\qwe\mydbfile.mdf;database = dbname; Trusted_Connection = Yes;
Why do I need a database field here? Because if the specified database file is already attached, SQL Server will not append it and use the attached database as the default database.
8. Attach a database file from the data directory when connecting to the SQL Server Express instance
Copy Code code as follows:
Server =. \SQLExpress; AttachDbFileName = | Datadirectory|mydbfile.mdf; Database = dbname; Trusted_Connection = Yes;
9. Using a user instance on a local SQL Server Express instance
User instance this feature creates a new instance of SQL Server during the connection process. This feature is only available on a local SQL Server instance and is connected using Windows authentication with a local named channel. The advantage of this is that you can create a new instance of SQL Server with full permissions to a user who has only fairly limited administrator rights on the local computer.
Copy Code code as follows:
Data Source =. \sqlexpress;integrated Security = true; AttachDbFileName = | Datadirectory|\mydb.mdf; User Instance = true;
If you want to use the user instance feature, you need to open it in SQL Server first. The Open command is:
Copy Code code as follows:
sp_configure ' user instances enabled ', ' 1 '
The cancellation command is:
Copy Code code as follows:
sp_configure ' user instances enabled ', ' 0 '
Note: These two commands are valid only in SQL Server Express.
10. Database Mirroring
If you use ado.net or SQL Native client to connect to a database image, your application can automatically redirect the connection using the drive's features when the database image fails over. Of course, you must specify the initial primary server and database in the Connection field, and the mirror server for failover.
Copy Code code as follows:
Data Source = myserveraddress; Failover Partner = mymirrorserveraddress;initial Catalog = mydatabase;integrated
Security = True;
The above example only describes how to use database mirroring, you can use the Failover partner field with other connection string functions.
11. Asynchronous Processing
Copy Code code as follows:
Server = Myserveraddress;database = mydatabase;integrated Security = True; asynchronous processing = True;
Second, SQL Server Native Client 10.0 OLE DB Provider
Type: OLE DB Provider
Use: provider=sqlncli10
Manufacturer: Microsoft
1. Standard secure connection
Copy Code code as follows:
Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase; Uid = MyUserName; PWD = mypassword;
2. Trust connection
Copy Code code as follows:
Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase; Trusted_Connection = yes;
"Integrated Security=sspi" and "Trusted_connection=yes" are equivalent.
3. Connect an instance of SQL Server
Copy Code code as follows:
Provider = SQLNCLI10; Server = Myservername\theinstancename;database = MyDataBase; Trusted_Connection = yes;
4. User name, password hint
Copy Code code as follows:
Oconn.properties ("Prompt") = adPromptAlways
oConn.Open "Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase;
5. Turn on Mars function (multiple active result sets)
Copy Code code as follows:
Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase; Trusted_Connection = yes; MARS Connection = True;
6. Encryption mode
Copy Code code as follows:
Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase; Trusted_Connection = yes; Encrypt = yes;
7. Attach a database file when connecting to the SQL Server Express instance
Copy Code code as follows:
Provider = SQLNCLI10; Server =. \SQLExpress; AttachDbFileName = C:\asd\qwe\mydbfile.mdf; Database = dbname;
Trusted_Connection = Yes;
8. Attach a database file from the data directory when connecting to the SQL Server Express instance
Copy Code code as follows:
Provider = SQLNCLI10; Server =. \SQLExpress; AttachDbFileName = | Datadirectory|mydbfile.mdf;
Database = dbname; Trusted_Connection = Yes;
9. Database Mirroring
Copy Code code as follows:
Provider = Sqlncli10;data Source = myserveraddress; Failover Partner = mymirrorserveraddress;initial
Catalog = mydatabase;integrated Security = True;
Iii. NET Framework Data Provider for OLE DB
Type:. NET Framework Wrapper Class Library
Use: System.Data.OleDb.OleDbConnection
Manufacturer: Microsoft
1. Bridging to SQL Native Client OLE DB
Copy Code code as follows:
Provider = SQLNCLI10; Server = Myserveraddress;database = MyDataBase; Uid = MyUserName; PWD = mypassword;
Iv. SQL Server Native Client 10.0 ODBC Driver
Type: ODBC Driver
Use: Driver={sql Server Native Client 10.0}
Manufacturer: Microsoft
1. Standard secure connection
Copy Code code as follows:
Driver = {SQL Server Native Client 10.0}; Server = Myserveraddress;database = MyDataBase; Uid = MyUserName; PWD = mypassword;
2. Trust connection
Copy Code code as follows:
Driver = {SQL Server Native Client 10.0}; Server = Myserveraddress;database = MyDataBase; Trusted_Connection = yes;
3. Connect an instance of SQL Server
Copy Code code as follows:
Driver = {SQL Server Native Client 10.0}; Server = Myservername\theinstancename;
Database = MyDataBase; Trusted_Connection = yes;
4. User name, password hint
Copy Code code as follows:
Conn.properties ("Prompt") = adPromptAlways
Driver = {SQL Server Native Client 10.0}; Server = Myserveraddress;database = MyDataBase;
5. Turn on Mars function (multiple active result sets)
Copy Code code as follows:
Driver = {SQL Server Native Client 10.0}; Server = Myserveraddress;database = MyDataBase; Trusted_Connection = yes;
Mars_connection = yes;
6. Encryption mode
Copy Code code as follows:
Driver = {SQL Server Native Client 10.0}; Server = Myserveraddress;database = MyDataBase;
Trusted_Connection = yes; Encrypt = yes;
7. Attach a database file when connecting to the SQL Server Express instance
Copy Code code as follows:
Driver = {SQL Server Native Client 10.0}; Server =. \SQLExpress; AttachDbFileName = C:\asd\qwe\mydbfile.mdf;
Database = dbname; Trusted_Connection = Yes;
8. Attach a database file from the data directory when connecting to the SQL Server Express instance
Copy Code code as follows:
Driver={sql Server Native Client 10.0}; server=.\sqlexpress; attachdbfilename=| Datadirectory|mydbfile.mdf;
Database=dbname; Trusted_connection=yes;
9. Database Mirroring
Copy Code code as follows:
Driver = {SQL Server Native Client
10.0}; Server = myserveraddress; Failover_partner = Mymirrorserveraddress;database = MyDataBase; Trusted_Connection = yes;
V. NET Framework Data Provider for ODBC
Type:. NET Framework Wrapper Class Library
Use: System.Data.Odbc.OdbcConnection
Manufacturer: Microsoft
Bridging to SQL Native Client 10.0 ODBC Driver
The following statement is just an example of different vendor ODBC drivers.
Copy Code code as follows:
Driver={sql Server Native Client 10.0}; Server=myserveraddress;database=mydatabase; Uid=myusername; Pwd=mypassword;
VI. SQLXML 4.0 OLE DB Provider
Type: OLE DB Provider
Use: Provider=sqlxmloledb.4.0;data provider=providername
Manufacturer: Microsoft
Copy Code code as follows:
Provider=sqlxmloledb.4.0;data provider=sqlncli10;data source=myserveraddress;initial Catalog=myDataBase; User
Id=myusername; Password=mypassword;
Seven, context Connection
Type:. NET Framework Class Library
Use:
Manufacturer: Microsoft
Connect to "Self" through stored procedures/functions in the current CLR. A context connection allows you to execute a T-SQL statement in the context (connection) where your code was first invoked.
C#:
Copy Code code as follows:
using (SqlConnection connection = new SqlConnection ("Context connection=true"))
{
Connection. Open ();
Use the connection
}
vb.net
Using Connection as New SqlConnection ("Context Connection=true")
Connection. Open ()
' Use the connection
End Using
