1、使用ADO.NET来连接SQL Server数据库
创建一个SqlConnection对象,在它的ConnectionString属性中指定要使用的数据库,然后调用它的Open方法
1
SqlConnection dataConnection = new SqlConnection();
2
string userID = "sa";
3
string password = "test";
4
try
5
{
6
dataConnection.ConnectionString = String.Format("User ID={0};Password={1};Persist Security Info=True;Initial Catalog=Northwind;Data Source=TXC-NOTEBOOK", userID, password);
7
dataConnection.Open(); 2、使用ADO.NET来创建并执行查询
创建一个SqlCommand对象,将它的Connection属性设为一个有效的SqlConnection对象,将它的CommandText设为一个有效的SQL SELECT语句。调用ExecuteReader方法来运行查询并创建一个SqlDataReader对象
1
Console.Write("please enter a customer ID(5 characters):");
2
string customerId = Console.ReadLine();
3
4
SqlCommand dataCommand = new SqlCommand();
5
dataCommand.Connection = dataConnection;
6
dataCommand.CommandText = "select OrderID,OrderDate,ShippedDate,ShipName,ShipAddress,ShipCity,ShipCountry " +
7
"from Orders where CustomerID='" + customerId + "'";
8
Console.WriteLine("About to execute:{0}\n\n", dataCommand.CommandText);
9
10
SqlDataReader dataReader = dataCommand.ExecuteReader(); 3、使用ADO.NET的SqlDataReader对象来获取数据
使用IsDBNull方法确保数据不是null。如果数据不是null,就用恰当的GetXXX方法(例如GetString和GetInt32等)来获取数据
1
while (dataReader.Read())
2
{
3
int orderId = dataReader.GetInt32(0);
4
if (dataReader.IsDBNull(2))
5
{
6
Console.WriteLine("Order {0} not yet shipped\n\n", orderId);
7
}
8
else
9
{
10
11
DateTime orderDate = dataReader.GetDateTime(1);
12
DateTime shipDate = dataReader.GetDateTime(2);
13
string shipName = dataReader.GetString(3);
14
string shipAddress = dataReader.GetString(4);
15
string shipCity = dataReader.GetString(5);
16
string shipCountry = dataReader.GetString(6);
17
18
Console.WriteLine("order:{0}\nPlaced:{1}\nShipped:{2}\n" +
19
"To Address:{3}\n{4}\n{5}\n{6}\n\n", orderId, orderDate, shipDate, shipName, shipAddress, shipCity, shipCountry);
20
21
}
22
23
}
24
dataReader.Close(); 4、定义实体类
定义一个类,在类中为每个列都定义一个public property。在类定义的前一行,附加一个Table attribute。在Table attribute中,指定底层数据库中的表名。在每个public property定义的前一行,都附加一个Column attribute。在Column attribute中,用参数来指定数据库中对应的列的名称、类型以及是否允许为空
1
[Table(Name="Orders")]
2
public class Order
3
{
4
[Column(IsPrimaryKey=true,CanBeNull=false)]
5
public int OrderID
{ get; set; }
6
7
[Column]
8
public string CustomerID
{ get; set; }
9
10
[Column]
11
public DateTime? OrderDate
{ get; set; }
12
13
[Column]
14
public DateTime? ShippedDate
{ get; set; }
15
16
[Column]
17
public string ShipName
{ get; set; }
18
19
[Column]
20
public string ShipAddress
{ get; set; }
21
22
[Column]
23
public string ShipCity
{ get; set; }
24
25
[Column]
26
public string ShipCountry
{ get; set; }
27
} 5、使用DLINQ创建并执行查询
创建一个新的DataContext变量,指定一个连接字符串来连接数据库。创建一个Table集合变量,它所基于的实体类必须和你要查询的表对应。定义一个DLINQ查询,它标识要从数据库中获取的数据,并返回实体的一个可枚举的集合。遍历可枚举的集合,即可获取每一行的数据,并对结果进行处理。
1
using System;
2
using System.Collections.Generic;
3
using System.Linq;
4
using System.Text;
5
using System.Data.Linq;
6
using DLINQOrders.model;
7
8
namespace DLINQOrders.dao
9

{
10
public class Northwind:DataContext
11
{
12
public Table<Order> Orders;
13
public Northwind(string connectionInfo)
14
: base(connectionInfo)
15
{
16
17
}
18
}
19
}
20
1
using System;
2
using System.Collections.Generic;
3
using System.Linq;
4
using System.Text;
5
6
using System.Data.Linq;
7
using System.Data.Linq.Mapping;
8
using System.Data.SqlClient;
9
10
namespace DLINQOrders.model
11

{
12
[Table(Name="Orders")]
13
public class Order
14
{
15
[Column(IsPrimaryKey=true,CanBeNull=false)]
16
public int OrderID
{ get; set; }
17
18
[Column]
19
public string CustomerID
{ get; set; }
20
21
[Column]
22
public DateTime? OrderDate
{ get; set; }
23
24
[Column]
25
public DateTime? ShippedDate
{ get; set; }
26
27
[Column]
28
public string ShipName
{ get; set; }
29
30
[Column]
31
public string ShipAddress
{ get; set; }
32
33
[Column]
34
public string ShipCity
{ get; set; }
35
36
[Column]
37
public string ShipCountry
{ get; set; }
38
}
39
}
40
1
using System;
2
using System.Collections.Generic;
3
using System.Linq;
4
using System.Text;
5
using DLINQOrders.dao;
6
using System.Data.SqlClient;
7
8
namespace DLINQOrders
9

{
10
class DLINQReport
11
{
12
static void Main(string[] args)
13
{
14
Northwind northwindDB = new Northwind("Data Source=TXC-NOTEBOOK;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=test");
15
try
16
{
17
Console.WriteLine("Please enter a customer ID(5 characters):");
18
string customerId = Console.ReadLine();
19
20
var ordersQuery = from o in northwindDB.Orders
21
where String.Equals(o.CustomerID, customerId)
22
select o;
23
foreach (var order in ordersQuery)
24
{
25
if (order.ShippedDate == null)
26
{
27
Console.WriteLine("Order {0} not yet shipped\n\n", order.OrderID);
28
}
29
else
30
{
31
Console.WriteLine("Order:{0}\nPlaced:{1}\nShipped:{2}\n" +
32
"To Address:{3}\n{4}\n{5}\n{6}\n\n", order.OrderID,
33
order.OrderDate, order.ShippedDate, order.ShipName, order.ShipAddress, order.ShipCity,
34
order.ShipCountry);
35
}
36
37
}
38
}
39
catch (SqlException e)
40
{
41
Console.WriteLine("Error accessing the database:{0}", e.Message);
42
}
43
}
44
}
45
}
46