بابک زواری
شنبه 27 اسفند 1384, 16:31 عصر
SQL Native Client ODBC Driver
Standard security:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asda sd;"
Trusted connection:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connect ion=yes;"
Equivalents
Integrated Security=SSPI equals Trusted_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_Connect ion=yes;MARS_Connection=yes"
Equivalents
MultipleActiveResultSets=true equals MARS_Connection=yes
Using MARS with SQL Native Client, by Chris Lee >> (http://blogs.msdn.com/dataaccess/comments/446894.aspx)
Encrypt data sent over network:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connect ion=yes;Encrypt=yes"
Attach a database file on connect to a local SQL Server Express instance:
"Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\as d\qwe\mydbfile.mdf;Database=dbname;Trusted_Connect ion=Yes;"
- or -
"Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|Data Directory|mydbfile.mdf;Database=dbname;Trusted_Con nection=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 SQL Server 2005 Express? Don't miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)
Download the SQL Native Client here >> (http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/sqlncli.msi) (the package contains booth the ODBC driver and the OLE DB provider)
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;Truste d_Connection=yes;"
Equivalents
Integrated Security=SSPI equals Trusted_Connection=yes
Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"
Enabling MARS (multiple active result sets):
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Truste d_Connection=yes;MarsConn=yes"
Equivalents
MarsConn=yes equals MultipleActiveResultSets=true equals MARS_Connection=yes
Using MARS with SQL Native Client, by Chris Lee >> (http://blogs.msdn.com/dataaccess/comments/446894.aspx)
Encrypt data sent over network:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Truste d_Connection=yes;Encrypt=yes"
Attach a database file on connect to a local SQL Server Express instance:
"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilen ame=c:\asd\qwe\mydbfile.mdf;Database=dbname;Truste d_Connection=Yes;"
- or -
"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilen ame=|DataDirectory|mydbfile.mdf;Database=dbname;Tr usted_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 >> (http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/sqlncli.msi) (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 ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1
Streamline your Data Connections by Moving to MARS, by Laurence Moroney, DevX.com >> (http://www.devx.com/dbzone/Article/30132)
Attach a database file on connect to a local SQL Server Express instance:
"Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\my dbfile.mdf;Database=dbname;Database=dbname;Trusted _Connection=Yes;"
- or -
"Server=.\SQLExpress;AttachDbFilename=|DataDirector y|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 a local SQL Server Express instance:
"Data Source=.\SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|\myd b.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/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
Standard security:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asda sd;"
Trusted connection:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connect ion=yes;"
Equivalents
Integrated Security=SSPI equals Trusted_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_Connect ion=yes;MARS_Connection=yes"
Equivalents
MultipleActiveResultSets=true equals MARS_Connection=yes
Using MARS with SQL Native Client, by Chris Lee >> (http://blogs.msdn.com/dataaccess/comments/446894.aspx)
Encrypt data sent over network:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connect ion=yes;Encrypt=yes"
Attach a database file on connect to a local SQL Server Express instance:
"Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\as d\qwe\mydbfile.mdf;Database=dbname;Trusted_Connect ion=Yes;"
- or -
"Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|Data Directory|mydbfile.mdf;Database=dbname;Trusted_Con nection=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 SQL Server 2005 Express? Don't miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)
Download the SQL Native Client here >> (http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/sqlncli.msi) (the package contains booth the ODBC driver and the OLE DB provider)
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;Truste d_Connection=yes;"
Equivalents
Integrated Security=SSPI equals Trusted_Connection=yes
Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"
Enabling MARS (multiple active result sets):
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Truste d_Connection=yes;MarsConn=yes"
Equivalents
MarsConn=yes equals MultipleActiveResultSets=true equals MARS_Connection=yes
Using MARS with SQL Native Client, by Chris Lee >> (http://blogs.msdn.com/dataaccess/comments/446894.aspx)
Encrypt data sent over network:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Truste d_Connection=yes;Encrypt=yes"
Attach a database file on connect to a local SQL Server Express instance:
"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilen ame=c:\asd\qwe\mydbfile.mdf;Database=dbname;Truste d_Connection=Yes;"
- or -
"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilen ame=|DataDirectory|mydbfile.mdf;Database=dbname;Tr usted_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 >> (http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/sqlncli.msi) (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 ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1
Streamline your Data Connections by Moving to MARS, by Laurence Moroney, DevX.com >> (http://www.devx.com/dbzone/Article/30132)
Attach a database file on connect to a local SQL Server Express instance:
"Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\my dbfile.mdf;Database=dbname;Database=dbname;Trusted _Connection=Yes;"
- or -
"Server=.\SQLExpress;AttachDbFilename=|DataDirector y|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 a local SQL Server Express instance:
"Data Source=.\SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|\myd b.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/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