连表查询Left join的实现

2015-04-06 08:58:59 访问(1257) 赞(0) 踩(0)

生成和输出的SQL语句

select top 10 t.ID, t.TheName, t.ClassId, 
t.CreateTime, t.UpdateTime, 
tClass.ID as C__ID,tClass.TheName as C__TheName,tClass.CreateTime as C__CreateTime,
tClass.UpdateTime as C__UpdateTime from UTB_EXAM_STUDENT t left join 
UTB_EXAM_CLASS tClass 
on t.ClassId = tClass.ID where tClass.TheName = '一班'

@tClassp2 = '一班'


select top 10 t.ID, t.TheName, t.ClassId, 
t.CreateTime, t.UpdateTime, 
tClass.ID as C__ID,tClass.TheName as C__TheName,tClass.CreateTime as C__CreateTime,
tClass.UpdateTime as C__UpdateTime from UTB_EXAM_STUDENT t left join 
UTB_EXAM_CLASS tClass 
on t.ClassId = tClass.ID where tClass.TheName = @tClassp2

@tClassp2 	 一班 	 String 	 Input 	 SqlParameter

关键代码

IFromTableDriver ifrom = entity;
            ifrom.AddIFromTableItemWithBaseEntity
                (
                    "tClass", 
                    entityClass, 
                    SlowX.Core.Enums.SqlJoin.EmSqlJoin.LeftJoin,
                    entity._ClassId,
                    entityClass._ID
                );

代码写法

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using SlowX.DAL.Helpers;
using SlowX.Core.ICoreClasses;
using System.Data;
using SlowX.Functions.Functions;
using SlowX.Core.CoreClasses;

public partial class ExamSQL : System.Web.UI.Page
{
    protected string strResult = "";

    protected void Page_Load(object sender, EventArgs e)
    {
        DataBindTheContorls(null);
    }

    #region 操作

    /// <summary>
    /// 
    /// </summary>
    /// <param name="xdbHelper"></param>
    protected void DataBindTheContorls(DBHelper xdbHelper)
    {
        string theResult = null;
        bool bIsCreate = true;

        if (xdbHelper == null)
        {
            xdbHelper = SlowX.DAL.Helpers.DBHelper.CreateDBHelper();
        }
        else
        {
            // 没有打开链接 //
            bIsCreate = xdbHelper.IsNotOpen();
        }

        try
        {
            if (bIsCreate)
                xdbHelper.OpenDBHelper();

            SlowX.ExamLib.Business.UTB_EXAM_STUDENT
             bll
             =
             SlowX.ExamLib.Business.UTB_EXAM_STUDENT.instance;

            SlowX.ExamLib.Entity.UTB_EXAM_STUDENT
                entity
                =
                new SlowX.ExamLib.Entity.UTB_EXAM_STUDENT();

            SlowX.ExamLib.Entity.UTB_EXAM_CLASS
                entityClass
                =
                new SlowX.ExamLib.Entity.UTB_EXAM_CLASS();

            IFromTableDriver ifrom = entity;
            ifrom.AddIFromTableItemWithBaseEntity
                (
                    "tClass", 
                    entityClass, 
                    SlowX.Core.Enums.SqlJoin.EmSqlJoin.LeftJoin,
                    entity._ClassId,
                    entityClass._ID
                );

            IQueryDriver iq = entity;
            iq.AddIQueryItemWithEntityFieldInfo(entityClass._TheName, "一班");

            ISelectDriver iselect = entity;
            iselect.AddISelectItemWithBaseEntity(entity);

            entityClass.selectPreName = "C__";

            iselect.AddISelectItemWithBaseEntity(entityClass);

            DataSet ds = bll.PageList(0, 10, entity, xdbHelper);

            theResult =
                @"<textarea name=""TextBox1"" rows=""2"" cols=""20"" id=""TextBox1"" style=""height:400px;width:99%;"">"
                + bll.BuildSqlPageList(0, 10, entity, xdbHelper)
                + "</textarea>";

            theResult += "<br /><br />";

            theResult += DataSetSlowXFunctions.DataSetToHtmlTable(ds, true);


            if (bIsCreate)
                xdbHelper.EndDBHelper();

        }
        catch (Exception err)
        {
            if (bIsCreate)
                xdbHelper.TranDBHelper();

            throw err;
        }
        finally
        {
            if (bIsCreate)
                xdbHelper.FinallyDBHelper();
        }

        strResult = theResult;
    }

    #endregion 操作

}