EF实现多表连接查询

1. 建立数据库(BOOK)
2. 建立数据表(Articles,Category) 

 表 Articles

字段名	类型	约束
ID	int	主键 自增 非空
Title	nvarchar(50)	非空
Author	nvarchar(50)	非空
PushDate	datetime	获取当前时间
Conent	nvarchar(200)	非空
Type	int	外键约束ID

表 Category

字段名	类型	约束
ID	int	主键 自增 非空
Name	nvarchar(50)	非空

3. 创建Web窗体和类库

实体层 ASP.NET Web

数据访问层 类库DAL

业务逻辑层 类库BLL

模型层 类库Model 使用实体数据模型创建

 将Model类的App.Config中的以下代码

<connectionStrings>
		<add name="BOOKEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=BOOK;persist security info=True;user id=sa;password=123456;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
	</connectionStrings>

剪切并复制到Web窗体中的Web.config，主要目的是为了在Web窗体找到BOOKEntities。

将Model类中的程序集EntityFramework、程序集EntityFramework.SqlServer引用到Web窗体和DAL类中。

4.编写DAL,添加ArticlesDAO类和CategoryDAO类

ArticlesDAO类

//引用
using Model;

public class ArticlesDAO
    {
        //实例化BOOKEntities
        BOOKEntities db = new BOOKEntities();
        /// <summary>
        /// 多表查询
        /// </summary>
        /// <returns></returns>
        public object GetArticles()
        {
            var result = (from a in db.Articles
                         join c in db.Category on a.Type equals c.ID
                         select new { a.Title, a.Author, a.PushDate, a.Conent, Type= c.Name }).ToList();
            return result;
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="articles"></param>
        /// <returns></returns>
        public int Add(Articles articles)
        {
            db.Articles.Add(articles);
            return db.SaveChanges();
        }
    }

CategoryDAO类

//引用
using Model;

public  class CategoryDAO
    {
        //实例化
        BOOKEntities db = new BOOKEntities();
        
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns></returns>
        public object GetCategory()
        {
            var result = (from c in db.Category
                         select c).ToList();
            return result;
        }
    }

5.编写BLL层，添加ArticlesService类和CategoryService类

ArticlesService类

//引用
using DAL;
using Model;

public class ArticlesService
    {
        ArticlesDAO articlesDAO = new ArticlesDAO();
        /// <summary>
        /// 查询
        /// </summary>
        /// <returns></returns>
        public object GetArticles()
        {
            return articlesDAO.GetArticles();
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="articles"></param>
        /// <returns></returns>
        public int Add(Articles articles)
        {
            return articlesDAO.Add(articles);
        }
    }

CategoryService类

//引用
using DAL;
using Model;

 public  class CategoryService
    {
        CategoryDAO categoryDAO = new CategoryDAO();
        /// <summary>
        /// 查询
        /// </summary>
        /// <returns></returns>
        public object GetCategory()
        {
            return categoryDAO.GetCategory();
        }
    }

6.添加Web窗体

Defult.aspx和Add.aspx

使用GridView绑定数据源 选择对象类型

使用LinkButton控件 进行跳转到添加页面

Defult.aspx窗体

        <div>
            <asp:LinkButton ID="LinkButton1" runat="server" PostBackUrl="~/Add.aspx">添加</asp:LinkButton>
            <asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1">
            </asp:GridView>
            <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetArticles" TypeName="BLL.ArticlesService"></asp:ObjectDataSource>
        </div>

 

Add.aspx窗体

 

        <div>
            标题：<asp:TextBox ID="txtTitle" runat="server"></asp:TextBox><br />
            作者：<asp:TextBox ID="txtAuthor" runat="server"></asp:TextBox><br />
            内容：<asp:TextBox ID="txtConent" runat="server"  TextMode="MultiLine"></asp:TextBox><br />
            //下拉框控件进行绑定类别
            类别：<asp:DropDownList ID="dlType" runat="server" DataTextField="Name" DataValueField="ID">
                
               </asp:DropDownList><br />
            <asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" />
        </div>

Add.aspx.cs编写代码

//引用
using BLL;
using Model;

    public partial class Add : System.Web.UI.Page
    {
        //实例化ArticlesService，CategoryService
        ArticlesService articlesService = new ArticlesService();
        CategoryService categoryService = new CategoryService();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                //DropDownList绑定Category里的类别值 调用GetCategory方法
                this.dlType.DataSource = categoryService.GetCategory();
                this.dlType.DataBind();
            }
        }

        protected void btnAdd_Click(object sender, EventArgs e)
        {
            Articles articles = new Articles();
            articles.Title = txtTitle.Text;
            articles.Author = txtAuthor.Text;
            //获取当前时间
            articles.PushDate = DateTime.Now;
            articles.Conent = txtConent.Text;
            articles.Type =int.Parse( dlType.SelectedValue);
            int count = articlesService.Add(articles);
            if (count>0)
            {
                Response.Write("<script>alert('添加成功')</script> ");
            }
        }
    }

根据类别查询

使用DropDownList下拉框 Repeater

        <div>
            <table>
                <caption>
                    <label>类别</label>
                    <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"
                    //绑定要显示的文本值 和ID
                    DataTextField="Name" DataValueField="ID"></asp:DropDownList>
                </caption>
                <tr>
                    <td>标题</td>
                    <td>作者</td>
                    <td>时间</td>
                    <td>内容</td>
                    <td>类别</td>
                    <td>操作</td>
                </tr>
                <asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand">
                    <ItemTemplate>
                    //绑定显示的值
                        <tr>
                            <td><%# Eval("Title") %></td>
                            <td><%# Eval("Author") %></td>
                            <td><%# Eval("PushDate") %></td>
                            <td><%# Eval("Conent") %></td>
                            <td><%# Eval("Type") %></td>
                            <td>
                                <asp:LinkButton ID="LinkButton1" runat="server" CommandArgument='<%# Eval("ID") %>' CommandName="More">查看详情</asp:LinkButton>
                            </td>
                        </tr>
                    </ItemTemplate>
                </asp:Repeater>
            </table>
        </div>

在此编写代码

 public partial class Defult : System.Web.UI.Page
    {
        
    }

public partial class Defult : System.Web.UI.Page
    {
        //实例化
        ArticlesService articlesService = new ArticlesService();
        CategoryService categoryService = new CategoryService();
        
        protected void Page_Load(object sender, EventArgs e)
        {
            //回传
            if (!IsPostBack)
            {
                this.Repeater1.DataSource = articlesService.GetArticles();
                this.Repeater1.DataBind();
                
                //绑定类别值
                this.DropDownList1.DataSource = categoryService.GetCategory();
                this.DropDownList1.DataBind();
            }
        }

       
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            int type =int.Parse( DropDownList1.SelectedValue);

            this.Repeater1.DataSource = articlesService.SelectByType(type);
            
            this.Repeater1.DataBind();
        }

        protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
        {
            //查看详情 传值
            int id = int.Parse(e.CommandArgument.ToString());
            //判断
            if (e.CommandName=="More")
            {
                Response.Redirect("/Datail.aspx?id="+id);
            }
        }
    }