Na dica desta semana listo algumas strings de conexão que você pode utilizar para se conectar ao SQL Server. Os métodos estão divididos entre ODBC (MSDASQL), OLEDB ou OledbConnection.NET, SqlConnection.NET e SQL Native Client (SQL Server 2005).
1) MSDASQL (ODBC): este método do ADO existe com o objetivo de manter a compatibilidade de versões do MDAC. Com este método você abre uma conexão com o SQL Server baseado nas funções do ODBC API SQLDriveConnect. O ideal é que você deixe de usar este tipo de conexão e passe a utilizar o OLEDB.
String usando SQL Authentication:
Dim conn As New ADODB.Connection
conn.Open “Driver={SQL Server};Server=SQL_Server;Database=Northwind;User ID=sql_user;Password=sql_password;”
– ou –
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Provider=MSDASQL;Data Source=Nome_DSN;User ID=sql_user;Password=sql_password;”
String usando Windows Authentication (Trusted Connection):
Dim conn As New ADODB.Connection
conn.Open “Driver={SQL Server};Server=SQL_Server;Database=Northwind;Integrated Security=SSPI;”
Nota: Integrated Security=SSPI é o mesmo que Trusted_Connection=yes
String de conexão que solicita usuário/senha:
Dim conn As New ADODB.Connection
conn.Properties(“Prompt”) = adPromptAlways
conn.Open “Driver={SQL Server};Server=SQL_Server;DataBase=Northwind;”
SQL Server 2005 (SQL Native Client ODBC Driver)
String usando SQL Authentication:
Dim conn As New ADODB.Connection
conn.Open “Driver={SQL Native Client};Server=SQL_Server;Database=AdventureWorks;UID=sql_user;PWD=sql_password;”
String usando Windows Authentication:
Dim conn As New ADODB.Connection
Conn.Open “Driver={SQL Native Client};Server=SQL_Server;Database=AdventureWorks;Integrated Security=SSPI;”
Nota: Integrated Security=SSPI é o mesmo que Trusted_Connection=yes
String de conexão que solicita usuário/senha:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Properties(“Prompt”) = adPromptAlways
Conn.Open “Driver={SQL Native Client};Server=SQL_Server;Database=AdventureWorks;”
Ativa o MARS (multiple active result sets):
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Driver={SQL Native Client};Server=SQL_Server;Database=AdventureWorks;Integrated Security=SSPI;MultipleActiveResultSets=true”
Nota: MultipleActiveResultSets=true é o mesmo que MARS_Connection=yes
Para saber como usar MARS com o SQL Server 2005,visite o link http://blogs.msdn.com/dataaccess/archive/2005/08/02/446894.aspx
Faz um Attach do banco de dados durante a conexão (apenas para conexão local do SQL Server Express):
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Driver={SQL Native Client};Server=.SQLExpress;AttachDbFilename=c:SQLEXPRESSDatadatabase_name.mdf;Database=dbname;Integrated Security=SSPI;”
2) SQLOLEDB (OLE DB), OleDbConnection (.Net): é nativo do SQL Server 2000 sendo o método de conexão recomendado.
String usando SQL Authentication:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Provider=sqloledb;Data Source=SQL_Server;Initial Catalog=Northwind;User Id=sql_user;Password=sql_passowrd;”
String usando Windows Authentication (Trusted Connection):
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Provider=sqloledb;Data Source=SQL_Server;Initial Catalog=Nothwind;Integrated Security=SSPI;”
Nota: Use SQL_ServerinstanceName como Data Source se o SQL Server está instalado como instance named.
String de conexão que solicita usuário/senha:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Provider = “sqloledb”
conn.Properties(“Prompt”) = adPromptAlways
conn.Open “Data Source=SQL_Server;Initial Catalog=Nothwind;”
String de conexão usando o IP do servidor SQL Server:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Provider=sqloledb;Data Source=192.168.10.25,1433;Initial Catalog=Nothwind;User ID=sql_user;Password=sql_password;”
Nota: O ,1433 refere-se à porta que deve ser utilizada (1433 é a default). Neste caso, mesmo que você esteja usando uma instância nomeada não é necessário informar o nome da instância.
SQL Server 2005 (SQL Native Client OLE DB Provider)
String usando SQL Authentication:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Provider=SQLNCLI;Server=SQL_Server;Database=AdventureWorks;UID=sql_user;PWD=sql_password;”
String usando Windows Authentication (Trusted connection):
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Provider=SQLNCLI;Server=SQL_Server;Database=AdventureWorks;Integrated Security=SSPI;”
String de conexão que solicita usuário/senha:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Properties(“Prompt”) = adPromptAlways
conn.Open “Provider=SQLNCLI;Server=SQL_Server;DataBase=AdventureWorks;”
Ativa o MARS (multiple active result sets):
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Provider=SQLNCLI;Server=SQL_Server;Database=AdventureWorks;Integrated Security=SSPI;MarsConn=yes”
Nota: MarsConn=yes é o mesmo que MultipleActiveResultSets=true, que também é o mesmo que MARS_Connection=yes
Faz um Attach do banco de dados durante a conexão (apenas para conexão local do SQL Server Express):
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Provider=SQLNCLI;Server=.SQLExpress;AttachDbFilename=C:SQLEXPRESSDatadatabase_name.mdf;Database=dbname;Integrated Security=SSPI;”
3) SqlDbConnection (.NET): método recomendado para aplicações escritas em.NET
String usando SQL Authentication:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Data Source=SQL_Server;Initial Catalog=Nothwind;User Id=sql_user;Password=sql_password;”
String usando Windows Authentication (Trusted Connection):
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Data Source=SQL_Server;Initial Catalog=Northwind;Integrated Security=SSPI;”
String de conexão usando o IP do servidor SQL Server:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Data Source=192.168.10.25,1433;Initial Catalog=Nothwind;User ID=sql_user;Password=sql_password;”
Nota: O ,1433 refere-se à porta que deve ser utilizada (1433 é a default).
Declaração do SqlConnection:
C#:
using System.Data.SqlClient;
SqlConnection SQLConn = new SqlConnection();
SQLConn.ConnectionString=”connection string”;
SQLConn.Open();
VB.NET:
Imports System.Data.SqlClient
Dim SQLConn As SqlConnection = New SqlConnection()
SQLConn.ConnectionString=”connection string”
SQLConn.Open()
SQL Server 2005 (SQL Native Client OLE DB Provider)
String usando SQL Authentication:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Data Source=SQL_Server;Initial Catalog=AdventureWorks;User Id=sql_user;Password=sql_password;”
String usando Windows Authentication (Trusted Connection):
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Data Source=SQL_Server;Initial Catalog=AdventureWorks;Integrated Security=SSPI;”
String de conexão usando o IP do servidor SQL Server:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Data Source=192.168.10.25,1433;Initial Catalog=AdventureWorks;User ID=sql_user;Password=sql_password;”
Ativa o MARS (multiple active result sets):
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Server=SQL_Server;Database=AdventureWorks;Trusted_Connection=True;MultipleActiveResultSets=true”
Note: MARS requer ADO.NET 2.0 ou superior.
Faz um Attach do banco de dados durante a conexão (apenas para conexão local do SQL Server Express):
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open “Server=.SQLExpress;AttachDbFilename=C:SQLEXPRESSDatadatabase_name.mdf;Database=dbname;Integrated Security=SSPI;”
Usando User Instance (apenas para conexão local do SQL Server Express):
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Openn “Data Source=.SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|database_name.mdf;user instance=true;”