框架使用-Sql拼接

 Sql语句拼写：

• 查询
  • DQueryDom
  • DmoQuery（返回的整个对象）
• 更新
  • DQUpdateDom
• 删除
  • DQDeleteDom
• 条件
  • dom.Where.Conditions.Add(DQCondition.Or(DQCondition.Like("Name","检修部"), DQCondition.Like("Spell", "检修部")));
• 排序
  • dom.OrderBy.Expressions.Add(DQOrderByExpression.Create("OrderId",false));
• 分组
  • dom.GroupBy.Expressions.Add(DQExpression.Field("Name"));
• 自定义查询字段
  • dom.Columns.Add(DQSelectColumn.Field("ID"));

       

  • dom.Columns.Add(DQSelectColumn.Count());

       

• 连接查询

  var user = new JoinAlias(typeof(WpfUser));

  var bindInfo = new JoinAlias(typeof(UserBindInfo));

  var dom = new DQueryDom(user);

  dom.From.AddJoin(JoinType.Left, new DQDmoSource(bindInfo), DQCondition.EQ(user, "ID", bindInfo, "User_ID"));

     

  dom.Columns.Add(DQSelectColumn.Field("ID"));

  dom.Columns.Add(DQSelectColumn.Field("Name"));

  dom.GroupBy.Expressions.Add(DQExpression.Field("ID"));

  dom.GroupBy.Expressions.Add(DQExpression.Field("Name"));

• Session
  • using (var context = new TransactionContext())

    {

    context.Session.ExecuteNonQuery(update);

    context.Session.Commit();

    }

  using (IDmoSession session = Dmo.NewSession()) //好想要引用或者继承什么，否则session为null

  {

  var list = session.ExecuteList(query);

  return list.Cast<WXDept>().ToList();

  }

     

• 执行方法
  • DQUpdateDom提交 ExecuteNonQuery
    context.Session.ExecuteNonQuery(update);

    context.Session.Commit();

  • DQDeleteDom提交 ExecuteNonQuery

    Session.ExecuteNonQuery(del);

  • DomQuery提交 ExecuteScalar

    var result = (WXAgent)Session.ExecuteScalar(dmo); //DomQuery

    return (long?)context.Session.ExecuteScalar(dom); //DQueryDom

       

     

  • ExecuteList

    using (var context = new TransactionContext())

    {

    var list = context.Session.ExecuteList(dmo);

    var userList = list.Cast<UserBindInfo>().ToList();

    UserBindInfo bindInfo = null;

    if (userList.Count > 0)

    {

    bindInfo = userList.First();

    }

    return bindInfo;

    }

  • ExecuteReader

    using (var context = new TransactionContext())

    {

    using (var reader = context.Session.ExecuteReader(dom))

    {

    while (reader.Read())

    {

    yield return new WordPair(reader[0].ToString(), TreeUtil.GetTreePrefix(Convert.ToInt32(reader[2])) + reader[1]);

    }

    }

    }

       

       

       

  using (IDmoSession session = Dmo.NewSession())

  {

  dtWeiXin = session.ExecuteSql(WxSql()).Tables[0];

  }

  using (IDmoSession session = Dmo.NewSession(ConfigurationManager.AppSettings["cfdBus"]))

  {

  //session.ExecuteSqlNonQuery(CfdBusSql(dtWeiXin));

  session.ExecuteSqlNonQuery(CfdBusSql(dtWeiXin));

     

  session.Commit();自定义sql要提交一下子

  }