relation.aspx.cs

数据库连接查询

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.IO;
using System.Data.SqlClient;
using System.Data.Common;
using System.Collections.Generic;

[Table(Name = "Categories")]
public class BoardCategory
{
    [Column(Name = "CategoryID", DbType = "int identity", IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false)]
    public int CategoryID { get; set; }

    [Column(Name = "CategoryName", DbType = "varchar(50)", CanBeNull = false)]
    public string CategoryName { get; set; }

    private EntitySet<Board> _Boards;

    [Association(OtherKey = "BoardCategory", Storage = "_Boards")]
    public EntitySet<Board> Boards
    {
        get { return this._Boards; }
        set { this._Boards.Assign(value); }
    }

    public BoardCategory()
    {
        this._Boards = new EntitySet<Board>();
    }
}

[Table(Name = "Boards")]
public class Board
{
    [Column(Name = "BoardID", DbType = "int identity", IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false)]
    public int BoardID { get; set; }

    [Column(Name = "BoardName", DbType = "varchar(50)", CanBeNull = false)]
    public string BoardName { get; set; }

    [Column(Name = "BoardCategory", DbType = "int", CanBeNull = false)]
    public int BoardCategory { get; set; }

    private EntityRef<BoardCategory> _Category;

    [Association(ThisKey = "BoardCategory", Storage = "_Category")]
    public BoardCategory Category
    {
        get { return this._Category.Entity; }
        set
        { 
            this._Category.Entity = value;
            value.Boards.Add(this);
        }
    }
}

[Table(Name = "Topics")]
[InheritanceMapping(Code = 0, Type = typeof(NewTopic), IsDefault = true)]
[InheritanceMapping(Code = 1, Type = typeof(Reply))]
public class Topic
{
    [Column(Name = "TopicID", DbType = "int identity", IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false)]
    public int TopicID { get; set; }

    [Column(Name = "TopicType", DbType = "tinyint", CanBeNull = false, IsDiscriminator = true)]
    public int TopicType { get; set; }

    [Column(Name = "TopicTitle", DbType = "varchar(50)", CanBeNull = false)]
    public string TopicTitle { get; set; }

    [Column(Name = "TopicContent", DbType = "varchar(max)", CanBeNull = false)]
    public string TopicContent { get; set; }
}

public class NewTopic : Topic
{
    public NewTopic()
    {
        base.TopicType = 0;
    }
}

public class Reply : Topic
{
    public Reply()
    {
        base.TopicType = 1;
    }

    [Column(Name = "ParentTopic", DbType = "int", CanBeNull = false)]
    public int ParentTopic { get; set; }
}

public partial class BBSContext : DataContext
{
    public Table<BoardCategory> BoardCategories;
    public Table<Board> Boards;
    public Table<Topic> Topics;
    public BBSContext(string connection) : base(connection) { }
}

public partial class relation : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        BBSContext ctx = new BBSContext("server=srv-devdbhost;database=BBS;uid=sa;pwd=Abcd1234");

        StreamWriter sw = new StreamWriter(Server.MapPath("relation.txt"), false);
        ctx.Log = sw;

        //var query = from t in ctx.Topics select t;
        //foreach (Topic topic in query)
        //{
        //    if (topic is NewTopic)
        //    {
        //        NewTopic newtopic = topic as NewTopic;
        //        Response.Write("标题：" + newtopic.TopicTitle + " 类型：" + newtopic.TopicType + "<br/>");
        //    }
        //    else if (topic is Reply)
        //    {
        //        Reply reply = topic as Reply;
        //        Response.Write("标题：" + reply.TopicTitle + " 类型：" + reply.TopicType + " 隶属主题：" + reply.ParentTopic + "<br/>");
        //    }
        //}

        //NewTopic nt = new NewTopic() { TopicTitle = "还是新主题", TopicContent = "还是新主题" };
        //Reply rpl = new Reply() { TopicTitle = "还是新回复", TopicContent = "还是新回复", ParentTopic = 4 };
        //ctx.Topics.Add(nt);
        //ctx.Topics.Add(rpl);
        //ctx.SubmitChanges();
        //rpl = ctx.Topics.OfType<Reply>().Single(reply => reply.TopicID == 8);
        //ctx.Topics.Remove(rpl);
        //ctx.SubmitChanges();

        //IEnumerable newtopiclist = (from t in ctx.Topics.OfType<NewTopic>() select t).ToList();
        //newtopics.DataSource = newtopiclist;
        //IEnumerable replylist = (from t in ctx.Topics.OfType<Reply>() select t).ToList();
        //replies.DataSource = replylist;
        //Page.DataBind();

        //Response.Write("-------------查询分类为1的版块-------------<br/>");
        //var query1 = from b in ctx.Boards where b.Category.CategoryID == 1 select b;
        //foreach (Board b in query1)
        //    Response.Write(b.BoardID + " " + b.BoardName + "<br/>");
        DataLoadOptions options = new DataLoadOptions();
        options.LoadWith<BoardCategory>(c => c.Boards);
        ctx.LoadOptions = options;
        Response.Write("-------------查询版块大于2个的分类-------------<br/>");
        var query2 = from c in ctx.BoardCategories where c.Boards.Count > 2 select c;
        foreach (BoardCategory c in query2)
            Response.Write(c.CategoryID + " " + c.CategoryName + " " + c.Boards.Count + "<br/>");
        //BoardCategory dbcat = new BoardCategory() { CategoryName = "Database" };
        //Board oracle = new Board() { BoardName = "Oracle", Category = dbcat };
        //ctx.BoardCategories.Add(dbcat);
        //ctx.SubmitChanges();
        sw.Close();


    }
}