回顾：Linq To SQL语法 - 实体类

浏览数：19 / 时间：2015年06月12日

第一篇博客，还望各位大神勿喷

小弟在此代码奉上........

借用NorthWind数据库，实现一个商品展示的小功能。上代码：

添加对Linq的引用

1 using System.Data.Linq;//添加对Linq的引用
2 using System.Data.Linq.Mapping;//配置对象和映射关系的命名空间

View Code

由于图简单，所以写了很少的字段，继续代码

    [Table(Name = "Products")]
    public class Product
    {
        [Column(Name = "ProductID", DbType = "int identity(1,1) not null", IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert, CanBeNull = true)]
        public int ProductID { get; set; }
        [Column(Name = "ProductName", DbType = "nvarchar(40) not null")]
        public string ProductName { get; set; }
        [Column(Name = "CategoryID", DbType = "int", CanBeNull = true)]
        public int CategoryID { get; set; }
        [Column(Name = "UnitPrice", DbType = "money")]
        public decimal? Price { get; set; }
    }

View Code

 1     [Table(Name = "Categories")]
 2     public class Categoty
 3     {
 4         [Column(Name = "CategoryID", DbType = "int identity(1,1) not null", IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = true, AutoSync = AutoSync.OnInsert)]
 5         public int ID { get; set; }
 6         [Column(Name = "CategoryName", DbType = "nvarchar(15) not null")]
 7         public string CategoryName { get; set; }
 8         [Column(Name = "Description", DbType = "ntext null")]
 9         public string Description { get; set; }
10 
11         private EntitySet<Products> _products;
12         [Association(Name = "C_P", IsForeignKey = false, OtherKey = "CategoryID", ThisKey = "ID", Storage = "_products")]
13         public IList<Products> Products
14         {
15             get { return _products; } //上级接受下级参数，直接返回，上级：Category  下级：Product
16             set { _products.Assign(value); }//下级接受上级参数，使用Assign方法进行向上转换
17         }
18     }

View Code

上面分别是两个类：商品类Product与商品类别Category，下面继续创建上下文对象

 1  public class NorthwindDBContext : DataContext
 2     {
 3         private static readonly string conn = ConfigurationManager.ConnectionStrings["Myconn"].ConnectionString;//数据库的连接
 4 

 5         public NorthwindDBContext()
 6             : base(conn) { }
 7 
 8         public NorthwindDBContext(string connection)
 9             : base(connection) { }
10 
11         public Table<Categoty> Category;
12 
13         public Table<Products> Products;
14     }

View Code

接下来就是展示页面了，创建一个aspx页面吧，扔两个控件上去

 1 <body>
 2     <form id="form1" runat="server">
 3     <div>
 4         <asp:DropDownList ID="DropDownListCategory" runat="server">
 5         </asp:DropDownList>
 6         <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
 7         <asp:GridView ID="GridView1" runat="server">
 8         </asp:GridView>
 9     </div>
10     </form>
11 </body>

View Code

页面现在也有了，后台代码：

 1 public partial class ProductsList : System.Web.UI.Page
 2     {
 3         Model.NorthwindDBContext db = new Model.NorthwindDBContext();
 4         protected void Page_Load(object sender, EventArgs e)
 5         {
 6             if (!IsPostBack)
 7             {
 8                 var query = db.Products.Count();
 9                 BandCategory();
10             }
11         }
12 
13         private void BandCategory()
14         {
15 
16             var allCate = from c in db.Catrgorys select c;
17             this.DropDownListCategory.DataValueField = "ID";
18             this.DropDownListCategory.DataTextField = "Name";
19             this.DropDownListCategory.DataSource = allCate;
20             this.DropDownListCategory.DataBind();
21         }
22 
23         protected void Button1_Click(object sender, EventArgs e)
24         {
25             BandProduct();
26         }
27 
28         private void BandProduct()
29         {
30 
31             //生成日志文件，方便程序出错查看生成的SQL语句
32             db.Log = new StreamWriter(Server.MapPath("~/log.txt"), true);
33             int cid = int.Parse(this.DropDownListCategory.SelectedValue);
34             List<Model.Product> lst = db.Products.ToList();
35             var Products = db.Products.Where(x => x.CategoryID == cid);
36             //拿到类别对象
37             //var cate = db.Category.Where(x => x.CategoryID == cid).First();
38             this.GridView1.DataSource = Products;//cate.Product;两种方法都行
39             this.GridView1.DataBind();
40             db.Log.Flush();
41             db.Log.Close();
42           
43         }