在ADO.NET 中, 使用 Connection 对象来连接到制定的数据源。
1. 使用 SQL Server.NET 数据提供程序连接到 SQL Server
(1)SQL Server 集成安全方式连接
SqlConnection conn = new SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=myDB");
conn.Open();
(2)SQL Server 信任机制方式连接
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "user id=; password=; Initial Catalog=myDB; data source=local; Connect Timeout=30";
conn.Open();
可以发现集成安全的连接方式,相对更加安全,不会在字符串中暴露用户名和密码。
2、使用连接事件
Connection 对象有两个事件,它们可用于从数据源中检索信息性消息或者确定Connection的状态是否被更改。
事件 | 说明 |
InfoMessage | 当从数据源中返回信息性消息时发生。信息性消息是数据源中不会引发异常的消息 |
StateChage | 当 Connection 的状态出现更改时发生 |
using System;
using System.Data;
using System.Data.SqlClient;
namespace _1_2SqlConnection
{
class Program
{
public static void Main()
{
SqlConnection conn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=master");
conn.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
conn.StateChange += new StateChangeEventHandler(OnStateChange);
conn.Open();
// to do some jobs. such as serch data
conn.Close();
}
protected static void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
{
foreach (SqlError err in args.Errors)
{
Console.WriteLine("The {0} has received a severity {1}, state{2}error number {3}\n"
+ "on line {4} of procedure {5} on server {6}:\n {7}", err.Source, err.Class, err.State,
err.Number, err.LineNumber, err.Procedure, err.Server, err.Message);
}
}
protected static void OnStateChange(object sender, StateChangeEventArgs args)
{
Console.WriteLine("The current Connection state has changed from {0} to {1}.", args.OriginalState, args.CurrentState);
}
}
}