Bài này giới thiệu với bạn các để thiết lập chuỗi kết nối (Connection Strings) với cơ sở dữ liệu SQL Server
SQL Native Client ODBC Driver:
Standard security:
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Trusted Connection:
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
Ghi chú: “Integrated Security=SSPI” tương tự như “Trusted_Connection=yes”Connecting to an SQL Server instance:
Driver={SQL Native Client};Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=yes;Prompt for username and password:
oConn.Properties(“Prompt”) = adPromptAlways
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Enabling MARS (multiple active result sets):
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;MARS_Connection=yes;Ghi chú:
“MultipleActiveResultSets=true” tương tự như “MARS_Connection=yes”
Sử dụng ADO.NET 2.0 cho MARS. MARS không hỗ trợ ADO.NET 1.0 và ADO.NET 1.1.
Encrypt data sent over network:
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;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;Trusted_Connection=Yes;Attach a database file, located in the data directory, on connect to a local SQL Server Express instance:
Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;Database mirroring:
Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial Catalog=myDataBase;Integrated Security=True;
SQL Native Client OLE DB Provider:
Standard security:
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Trusted connection:
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Connecting to an SQL Server instance:
Provider=SQLNCLI;Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=yes;Prompt for username and password:
oConn.Properties(“Prompt”) = adPromptAlways
oConn.Open “Provider=SQLNCLI;Server=myServerAddress;DataBase=myDataBase;Enabling MARS (multiple active result sets):
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;MarsConn=yes;Ghi chú:
“MultipleActiveResultSets=true” tương tự như “MARS_Connection=yes”
Sử dụng ADO.NET 2.0 cho MARS. MARS không hỗ trợ ADO.NET 1.0 và ADO.NET 1.1.
Encrypt data sent over network:
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;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; Database=dbname;Trusted_Connection=Yes;Attach a database file, located in the data directory, on connect to a local SQL Server Express instance:
Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;Database mirroring:
Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial Catalog=myDataBase;Integrated Security=True;
SqlConnection (.NET):
Standard Security:
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;hoặc:
Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
Trusted Connection:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;hoặc:
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
Connecting to an SQL Server instance:
Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;Trusted Connection from a CE device:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;Connect via an IP address:
Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;Ghi chú: 1433 là cổng mặc định của SQL Server.
Enabling MARS (multiple active result sets):
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;MultipleActiveResultSets=true;Ghi chú:
“MultipleActiveResultSets=true” tương tự như “MARS_Connection=yes”
Sử dụng ADO.NET 2.0 cho MARS. MARS không hỗ trợ ADO.NET 1.0 và ADO.NET 1.1.
Attach a database file on connect to a local SQL Server Express instance:
Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;Attach a database file, located in the data directory, on connect to a local SQL Server Express instance:
Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;Using an User Instance on a local SQL Server Express instance:
Data Source=.\SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;Database mirroring:
Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial Catalog=myDataBase;Integrated Security=True;Asynchronous processing:
erver=myServerAddress;Database=myDataBase;Integrated Security=True;Asynchronous Processing=True;
SQL Server 2005 special:
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
Theo asp.net.vn