.NET中各种数据库连接大全(1)

·SQL Server

• ODBC
  • Standard Security:

              "Driver={SQLServer};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"

  • Trusted connection:

              "Driver={SQLServer};Server=Aron1;Database=pubs;Trusted_Connection=yes;"

  • Prompt for username and password:
    oConn.Properties("Prompt") = adPromptAlways
    oConn.Open "Driver= {SQLServer};Server=Aron1;DataBase=pubs;"
• OLEDB, OleDbConnection (.NET) 
  
  
  • Standard Security:
    "Provider=sqloledb;Data Source=Aron1;Initial Catalog= pubs;UserId=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;UserID=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;UserId=sa;Password=asdasd;"
  • Trusted Connection:
    "Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

(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;UserID=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 connectionstring";
oSQLConn.Open(); 

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

• Data Shape 
  
  
  • MS Data Shape
    "Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=Aron1;Initial Catalog= pubs;UserID=sa;Password=asdasd;"
• Read more

• • How to define wich network protocol to use

• • Example:
    "Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog= pubs;UserID=sa;Password=asdasd;"

Name Network library dbnmpntw Win32 Named Pipes dbmssocn Win32 Winsock TCP/IP dbmsspxn Win32 SPX/IPX dbmsvinn Win32 Banyan Vines dbmsrpcn Win32 Multi-Protocol (Windows RPC)

• • 
    Important note! 
    When connecting through the SQLOLEDB provider use the syntax Network Library=dbmssocn
    and when connecting through MSDASQL provider use the syntax Network=dbmssocn

               All SqlConnection connectionstring properties

• This table shows all connectionstring properties for the ADO.NET SqlConnection object. Most of the properties are also used in ADO. All properties and descriptions is from msdn.   

2009-09-04                   

注：源自原百度博客“至美心”