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對象
1Console.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等）來獲取數據
1while (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查詢，它標識要從數據庫中獲取的數據，并返回實體的一個可枚舉的集合。遍歷可枚舉的集合，即可獲取每一行的數據，并對結果進行處理。
1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5using System.Data.Linq;
6using DLINQOrders.model;
7
8namespace 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
1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5
6using System.Data.Linq;
7using System.Data.Linq.Mapping;
8using System.Data.SqlClient;
9
10namespace 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
1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5using DLINQOrders.dao;
6using System.Data.SqlClient;
7
8namespace 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