ASP.NET 连接 SQL Server 2008 数据库
SQL Native Client ODBC Driver
标准连接
Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
如果你在使用 SQL Server 2008 Express，请不要忘记服务器名称的是 Servername\SQLEXPRESS。
信任连接
Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
可用相同意义的键值替换："Integrated Security=SSPI" 等价于 "Trusted_Connection=yes"
连接到一个 SQL Server 实例
The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.
Driver={SQL Server Native Client 10.0};Server=myServerName\theInstanceName; Database=myDataBase;Trusted_Connection=yes;
提示输入用户名密码
This one is a bit tricky. First you need to set the connection 对象的 Prompt property to adPromptAlways. Then use the connection string to connect to the database.
oConn.Properties("Prompt") = adPromptAlways
Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;
Enabling MARS (multiple active result sets)
Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes; MARS_Connection=yes;
Equivalent key-value pair: "MultipleActiveResultSets=true" equals "MARS_Connection=yes"
Use ADO.NET for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.
Using MARS with SQL Native Client, by Chris Lee >>
Encrypt data sent over network
Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;
附件一个数据库文件连接到本地 SQL Server Express 实例
Driver={SQL Server Native Client 10.0};Server=.\SQLExpress; AttachDbFilename=c:\asd\qwe\mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;
为什么需要数据库参数呢？如果数据库的名称已经附加，SQL Server 不会再附加它。它会使用附件的数据库作为默认的连接。
Attach a database file, located in the data directory, on connect to a local SQL Server Express instance
Driver={SQL Server Native Client 10.0};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.
数据库镜像
If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.
Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial Catalog=myDataBase;Integrated Security=True;
There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.
SQL Native Client OLE DB Provider
标准连接
Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;
如果你在使用 SQL Server 2008 Express，请不要忘记服务器名称的是 Servername\SQLEXPRESS。
信任连接
Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;
Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"
连接到一个 SQL Server 实例
在语法中指定数据库实例的键值，和其他的SQL Server连接字符串是一样的。
Provider=SQLNCLI10;Server=myServerName\theInstanceName;Database=myDataBase; Trusted_Connection=yes;
提示输入用户名密码
首先你需要设置连接对象的Provider属性为"sqloledb"。然后再设置连接对象的Prompt属性为"adPromptAlways"。然后使用连接字符串连接到数据库。
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Provider=SQLNCLI10;Server=myServerAddress;DataBase=myDataBase;
Enabling MARS (multiple active result sets)
Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;MarsConn=yes;
Equivalent key-value pair: "MultipleActiveResultSets=true" equals "MARS_Connection=yes"
Use ADO.NET for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.
Using MARS with SQL Native Client, by Chris Lee >>
Encrypt data sent over network
Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;
连接到本地的 SQL Server 数据库文件
Provider=SQLNCLI10;Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf; Database=dbname; Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.
连接到本地的数据目录内的 SQL Server 数据库文件
Provider=SQLNCLI10;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.
数据库镜像
If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.
Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial Catalog=myDataBase;Integrated Security=True;
There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.
SqlConnection (.NET)
标准连接
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
使用“服务器名\数据库实例名”作为数据源，连接到指定的SQL Server 实例。
你是否正在使用 SQL Server 2008 Express (开发者版)? 请不要忘记使用 Servername\SQLEXPRESS 作为数据源，Servername为SQL Server Express 所在的电脑的计算机名。
标准连接的另一种写法
与刚才的连接效果一样。原因是连接字符串里很多的关键字是相同的。
Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
信任连接
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
信任连接的另一种写法
与上一个连接字符的效果一样。原因是连接字符串里很多的关键字是相同的。
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
连接到一个 SQL Server 实例
在语法中指定数据库实例的键值，和其他的SQL Server连接字符串是一样的。
Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;
在 Windows CE 设备上的信任连接
通常一个Windows CE设备不会自动地登录到一个域。要在CE设备上使用SSPI或者信任连接，需要使用这样的连接字符串：
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;
请注意这个连接字符串只会在 CE 设备上有效。
这里有更多关于在CE设备连接SQL Server的资料 >>
http://msdn2.microsoft.com/en-us/library/aa275613(SQL.80).aspx
Connect via an IP address
Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
DBMSSOCN=TCP/IP，这是为什么使用 TCP/IP 代替通道名。数据源后面的所需要使用的端口。1433是SQL Server默认的端口.
Enabling MARS (multiple active result sets)
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True; MultipleActiveResultSets=true;
Use ADO.NET 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
连接到本地的 SQL Server 数据库文件
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;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.
连接到本地的数据目录内的 SQL Server 数据库文件
(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;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.
使用一个用户实例在本地的 SQL Server Express 实例
The User Instance functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 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.
Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;
整理中
数据库镜像
If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.
Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial Catalog=myDataBase;Integrated Security=True;
There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.
异步处理
A connection to SQL Server that allows for the issuing of async requests through ADO.NET objects.
Server=myServerAddress;Database=myDataBase;Integrated Security=True;Asynchronous Processing=True;
SQLXMLOLEDB
Using SQL Server Native Client provider
Provider=SQLXMLOLEDB.4.0;Data Provider=SQLNCLI10;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
SQL Server 2008 specials
Context Connection
When to use SQL Native Client? >>
List of all SqlConnection connection string properties >>