摘抄的SQL Server和Access的ConnectionString

 SQL Server

• •  ODBC
    
    
    •  Standard Security:
      
      "Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
      
      
      
    •  Trusted connection:
      
      "Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
      
      
      
    •  Prompt for username and password:
      
      oConn.Properties("Prompt") = adPromptAlways
      oConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"

     

  •  OLE DB, OleDbConnection (.NET)
  
  
  
  •  Standard Security:
    
    "Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
    
    
    
  •  Trusted connection:
    
    "Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
    
    
    
  •  Prompt for username and password:
    
    oConn.Properties("Prompt") = adPromptAlways
    oConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"
    
    
    
  
  
  
  •  Standard Security:
    
    "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
    
    
    
  •  Trusted Connection:
    
    "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

    (use serverName/instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)

  •  Prompt for username and password:
    
    oConn.Provider = "sqloledb"
    oConn.Properties("Prompt") = adPromptAlways
    oConn.Open "Data Source=Aron1;Initial Catalog=pubs;"
    
    
    
  •  Connect via an IP address:
    
    "Provider=sqloledb;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))

  
  
  

 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, only SQLServer2000)

•  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))

•  Declare the SqlConnection:
  

  C#:
  using System.Data.SqlClient;
  SqlConnection oSQLConn = new SqlConnection();
  oSQLConn.ConnectionString="my connection string";
  oSQLConn.Open();

  VB.NET:
  Imports System.Data.SqlClient
  Dim oSQLConn As SqlConnection = New SqlConnection()
  oSQLConn.ConnectionString="my connection string"
  oSQLConn.Open()

  
  
  

 

 SQL Server 2005

• •  SQL Native Client ODBC Driver
  •  SQL Native Client OLE DB Provider
  •  SqlConnection (.NET)
  
  
  
  •  Standard security:
    
    "Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"
    
    
    
  •  Trusted connection:
    
    "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=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_Connection=yes;MARS_Connection=yes"

    Equivalents
    MultipleActiveResultSets=true equals MARS_Connection=yes
    
    Using MARS with SQL Native Client, by Chris Lee >>

  •  Encrypt data sent over network:
    
    "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"

    Download the SQL Native Client here >> (the package contains booth the ODBC driver and the OLE DB provider)

  
  
  
  •  Standard security:
    
    "Provider=SQLNCLI;Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"
    
    
    
  •  Trusted connection:
    
    "Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_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;Trusted_Connection=yes;MarsConn=yes"

    Equivalents
    MarsConn=yes equals MultipleActiveResultSets=true equals MARS_Connection=yes
    
    Using MARS with SQL Native Client, by Chris Lee >>

  •  Encrypt data sent over network:
    
    "Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"

    Download the SQL Native Client here >> (the package contains booth the OLE DB provider and the ODBC driver)

  
  
  
  •  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 >>

•  

•  

•  Access

• • •  ODBC
    •  OLE DB, OleDbConnection (.NET)
    
    
    
    •  Standard Security:
      
      "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/mydatabase.mdb;Uid=Admin;Pwd=;"
      
      
      
    •  Workgroup:
      
      "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/mydatabase.mdb;SystemDB=C:/mydatabase.mdw;"
      
      
      
    •  Exclusive:
      
      "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="
      
      
      
    
    
    
    •  Standard security:
      
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/somepath/mydb.mdb;User Id=admin;Password=;"
      
      
      
    •  Workgroup (system database):
      
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/somepath/mydb.mdb;Jet OLEDB:System Database=system.mdw;"
      
      
      
    •  With password:
      
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/somepath/mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"