My last exam is today — Data Structures — so I won’t write anything of my own. Reposting an article by Yin Likun. Original link: http://www.jackspace.cn/html/824105341.html I. .NET Framework Data Provider for SQL Server Type: .NET Framework class library Usage: System.Data.SqlClient.SqlConnection Vendor: Microsoft
Standard Security Connection
Data Source = myServerAddress;Initial Catalog = myDataBase;User Id = myUsername;Password = myPassword;
Uses the server name\instance name as the data source to specify the SQL Server instance. If you are using SQL Server 2008 Express, the instance name is SQLEXPRESS.
Alternative Standard Security Connection
Server = myServerAddress;Database = myDataBase;User ID = myUsername;Password = myPassword;Trusted_Connection = False;
This connection string has the same effect as the previous one. The only reason to write it out is to show that many connection string keywords have multiple spellings.
Trusted Connection
Data Source = myServerAddress;Initial Catalog = myDataBase;Integrated Security = SSPI;
Alternative Trusted Connection
Server = myServerAddress;Database = myDataBase;Trusted_Connection = True;
Trusted Connection to a Windows CE Device
Typically a Windows CE device cannot be authenticated or logged on within a domain. To let a CE device use SSPI or a trusted connection for authentication, use the following connection string:
Data Source = myServerAddress;Initial Catalog = myDataBase;Integrated Security = SSPI;User ID = myDomain myUsername;Password = myPassword;
Note that this statement only works on CE devices.
Connection Using an IP Address
Data Source = 190.168.1.100,1433;Network Library = DBMSSOCN;Initial Catalog = myDataBase;User ID = myUsername;Password = myPassword;
This statement uses a TCP/IP address instead of named pipes. The last part of the Data Source field is the port used. The default port for SQL Server is 1433.
Enabling MARS (Multiple Active Result Sets)
Server = myServerAddress;Database = myDataBase;Trusted_Connection = True; MultipleActiveResultSets = true;
MARS is not supported in ADO.NET 1.0 and ADO.NET 1.1.
Attaching a Database File When Connecting to a SQL Server Express Instance
Server = .SQLExpress;AttachDbFilename = c:asdqwemydbfile.mdf;Database = dbname; Trusted_Connection = Yes;
Why is the Database field still needed here? Because if the specified database file is already attached, SQL Server won’t attach it again, and will instead use the already-attached database as the default database.
Attaching a Database File from the Data Directory When Connecting to a SQL Server Express Instance
Server = .SQLExpress;AttachDbFilename = |DataDirectory|mydbfile.mdf; Database = dbname;Trusted_Connection = Yes;
Using a User Instance on a Local SQL Server Express Instance
The user instance feature creates a new SQL Server instance during the connection process. This feature only works on a local SQL Server instance and when the connection uses Windows authentication through a local named pipe. The benefit is that it lets a user with only very limited administrator rights on the local machine create a SQL Server instance with full permissions.
Data Source = .SQLExpress;Integrated Security = true; AttachDbFilename = |DataDirectory|mydb.mdf;User Instance = true;
To use the user instance feature, you must first enable it in SQL Server. The enable command is: sp_configure “user instances enabled”, “1” and the disable command is: sp_configure “user instances enabled”, “0”. Note: these two commands only work in SQL Server Express.
Database Mirroring
If you use ADO.NET or SQL Native Client to connect to a database mirror, when the mirror fails over, your application can use the driver’s feature to automatically redirect the connection. Of course, you must specify the initial principal server and database, as well as the mirror server for failover, in the connection fields.
Data Source = myServerAddress;Failover Partner = myMirrorServerAddress;Initial Catalog = myDataBase;Integrated Security = True;
The example above only introduces how to use database mirroring; you can combine the Failover Partner field with other connection string features.
Asynchronous Processing
Server = myServerAddress;Database = myDataBase;Integrated Security = True;Asynchronous Processing = True;
II. SQL Server Native Client 10.0 OLE DB Provider Type: OLE DB Provider Usage: Provider=SQLNCLI10 Vendor: Microsoft
Standard Security Connection
Provider = SQLNCLI10;Server = myServerAddress;Database = myDataBase;Uid = myUsername; Pwd = myPassword;
Trusted Connection
Provider = SQLNCLI10;Server = myServerAddress;Database = myDataBase; Trusted_Connection = yes;
“Integrated Security=SSPI” and “Trusted_Connection=yes” are equivalent.
Connecting to a SQL Server Instance
Provider = SQLNCLI10;Server = myServerNametheInstanceName;Database = myDataBase; Trusted_Connection = yes;
Username/Password Prompt
oConn.Properties(“Prompt”) = adPromptAlways oConn.Open “Provider = SQLNCLI10;Server = myServerAddress;DataBase = myDataBase;
Enabling MARS (Multiple Active Result Sets)
Provider = SQLNCLI10;Server = myServerAddress;Database = myDataBase; Trusted_Connection = yes;MARS Connection = True;
Encryption Mode
Provider = SQLNCLI10;Server = myServerAddress;Database = myDataBase; Trusted_Connection = yes;Encrypt = yes;
Attaching a Database File When Connecting to a SQL Server Express Instance
Provider = SQLNCLI10;Server = .SQLExpress;AttachDbFilename = c:asdqwemydbfile.mdf; Database = dbname; Trusted_Connection = Yes;
Attaching a Database File from the Data Directory When Connecting to a SQL Server Express Instance
Provider = SQLNCLI10;Server = .SQLExpress;AttachDbFilename = |DataDirectory|mydbfile.mdf;Database = dbname;Trusted_Connection = Yes;
Database Mirroring
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 Usage: System.Data.OleDb.OleDbConnection Vendor: Microsoft
Bridging to SQL Native Client OLE DB
Provider = SQLNCLI10;Server = myServerAddress;Database = myDataBase;Uid = myUsername; Pwd = myPassword;
IV. SQL Server Native Client 10.0 ODBC Driver Type: ODBC Driver Usage: Driver={SQL Server Native Client 10.0} Vendor: Microsoft
Standard Security Connection
Driver = {SQL Server Native Client 10.0};Server = myServerAddress;Database = myDataBase;Uid = myUsername;Pwd = myPassword;
Trusted Connection
Driver = {SQL Server Native Client 10.0};Server = myServerAddress;Database = myDataBase;Trusted_Connection = yes;
Connecting to a SQL Server Instance
Driver = {SQL Server Native Client 10.0};Server = myServerNametheInstanceName; Database = myDataBase;Trusted_Connection = yes;
Username/Password Prompt
oConn.Properties(“Prompt”) = adPromptAlways Driver = {SQL Server Native Client 10.0};Server = myServerAddress;Database = myDataBase;
Enabling MARS (Multiple Active Result Sets)
Driver = {SQL Server Native Client 10.0};Server = myServerAddress;Database = myDataBase;Trusted_Connection = yes; MARS_Connection = yes;
Encryption Mode
Driver = {SQL Server Native Client 10.0};Server = myServerAddress;Database = myDataBase;Trusted_Connection = yes;Encrypt = yes;
Attaching a Database File When Connecting to a SQL Server Express Instance
Driver = {SQL Server Native Client 10.0};Server = .SQLExpress; AttachDbFilename = c:asdqwemydbfile.mdf; Database = dbname;Trusted_Connection = Yes;
Attaching a Database File from the Data Directory When Connecting to a SQL Server Express Instance
Driver={SQL Server Native Client10.0};Server=.SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
Database Mirroring
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 Usage: System.Data.Odbc.OdbcConnection Vendor: Microsoft
Bridging to SQL Native Client 10.0 ODBC Driver
The statement below is only an example; ODBC drivers differ by vendor.
Driver={SQL Server Native Client10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
VI. SQLXML 4.0 OLEDB Provider Type: OLE DB Provider Usage: Provider=SQLXMLOLEDB.4.0;Data Provider=providername Vendor: Microsoft
Provider=SQLXMLOLEDB.4.0;Data Provider=SQLNCLI10;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
VII. Context Connection Type: .NET Framework Class Library Usage: Vendor: Microsoft Connects to “itself” through a stored procedure/function in the current CLR. The context connection lets you execute T-SQL statements in the context (connection) where your code was first invoked. [code language=“sql”]C# 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[/code]