コード例 #1
0
ファイル: QueryInfo.cs プロジェクト: ywscr/Dapper.LnskyDB
        public ISelectResult <TResult> Select <TResult>(Expression <Func <T, TResult> > sel)
        {
            var dynamicParameters = new DynamicParameters();
            var selExp            = new JoinSelectExpression(sel, new Dictionary <string, string> {
                { "", $"{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}jtmp{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote}" }
            }, dynamicParameters, DBModel.GetDBModel_SqlProvider());
            var selSql   = string.Join(",", selExp.QueryColumns).Replace($"{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}jtmp{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote}.", "");
            var sql      = new StringBuilder($"SELECT {selSql} FROM {this.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(this.DBModel)}{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} {DBTool.GetTableWith(this)} WHERE 1=1");
            var countSql = new StringBuilder($"SELECT COUNT(1) FROM {DBTool.GetTableName(this.DBModel)} {DBTool.GetTableWith(this)} WHERE 1=1");

            var whereExp = new WhereExpression(WhereExpression, "", dynamicParameters, DBModel.GetDBModel_SqlProvider());

            sql.Append(whereExp.SqlCmd);
            countSql.Append(whereExp.SqlCmd);

            if (OrderbyList.Count > 0)
            {
                sql.Append($" ORDER BY ");
                var olst = OrderbyList.Select(m =>
                {
                    var order = new JoinOrderExpression(m.Field, new Dictionary <string, string> {
                        { "", "" }
                    }, dynamicParameters, DBModel.GetDBModel_SqlProvider());
                    return(order.SqlCmd + " " + m.OrderBy);
                });
                sql.Append(string.Join(",", olst) + " ");
            }

            sql.Append(DBModel.GetDBModel_SqlProvider().GetLimit(StarSize, Rows));

            var sr = new SelectResult <TResult>(sql.ToString(), countSql.ToString(), dynamicParameters);

            sr.DBModel = DBModel;
            return(sr);
        }
コード例 #2
0
ファイル: JoinQueryInfo.cs プロジェクト: xiong0317/LnskyDB
 public IJoinQuery <T> OrderByDescing <TProperty>(Expression <Func <T, TProperty> > field)
 {
     if (field != null)
     {
         OrderbyList.Add(new OrderCriteria {
             OrderBy = EOrderBy.Desc, Field = field
         });
     }
     return(this);
 }
コード例 #3
0
    protected void CmbTable_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (cmbTable.SelectedIndex != 0)
        {
            try
            {
                selectTable = cmbTable.SelectedItem.ToString();
                Session["selected_table"] = selectTable;
                ViewState["selectedTbl"]  = null;
                selectedTbl.Clear();
                selectedTbl.Add(selectTable);
                ViewState["selectedTbl"] = selectedTbl;

                string        sqlQuery = "Select * from " + selectTable;
                SqlCommand    command  = new SqlCommand(sqlQuery);
                SqlConnection sqlConn  = new SqlConnection((Session["Connection_String"]).ToString());
                sqlConn.Open();
                command.Connection = sqlConn;
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataTable      dt      = new DataTable();
                adapter.Fill(dt);
                sqlConn.Close();
                List <string> columnNames = new List <string>();
                List <string> numerticCol = new List <string>();

                foreach (DataColumn column in dt.Columns)
                {
                    columnNames.Add(column.ColumnName);
                    if (column.DataType == System.Type.GetType("System.Int32"))
                    {
                        numerticCol.Add(column.ColumnName);
                    }
                }
                FieldNameList.DataSource = columnNames;
                FieldNameList.DataBind();
                ckSelectAllBase.Checked = false;
                ckSelectAllBase.Visible = true;
                LoadSelectedTable();

                OrderbyList.DataSource = columnNames;
                OrderbyList.DataBind();
            }
            catch (Exception ex)
            {
                lblError.Text = ex.ToString();
            }
        }
        else
        {
            ckSelectAllBase.Visible = false;
            FieldNameList.Items.Clear();
            joinSelectedTable.Items.Clear();
            OrderbyList.Items.Clear();
        }
    }
コード例 #4
0
ファイル: JoinQueryInfo.cs プロジェクト: xiong0317/LnskyDB
        public ISelectResult <TResult> Select <TResult>(Expression <Func <T, TResult> > select, bool firstTableSelectAll = false) where TResult : new()
        {
            var p = new DynamicParameters();

            p.AddDynamicParams(Param);
            var selectResult = new JoinSelectExpression(select, Map, p);
            var selSql       = string.Join(",", selectResult.QueryColumns);

            if (firstTableSelectAll)
            {
                if (!string.IsNullOrEmpty(selSql))
                {
                    selSql = "t1.*," + selSql;
                }
                else
                {
                    selSql = "t1.*";
                }
            }
            var isDBModel = typeof(BaseDBModel).IsAssignableFrom(typeof(TResult));

            if (isDBModel)
            {
                selSql = "0 as DBModel_IsBeginChange," + selSql;
            }
            var whereExpression = new JoinWhereExpression(WhereExpression, Map, p);
            var sql             = new StringBuilder($"SELECT {selSql} FROM {JoinStr} WHERE 1=1 {Where} {whereExpression.SqlCmd} ");
            var countSql        = new StringBuilder($"SELECT count(1) FROM {JoinStr} WHERE 1=1 {Where} {whereExpression.SqlCmd} ");

            if (OrderbyList.Count > 0)
            {
                sql.Append($" ORDER BY ");
                var olst = OrderbyList.Select(m =>
                {
                    var order = new JoinOrderExpression(m.Field, Map, p);
                    return(order.SqlCmd + " " + m.OrderBy);
                });
                sql.Append(string.Join(",", olst) + " ");
            }

            if (StarSize != 0 || Rows != 0)
            {
                sql.Append($" OFFSET {StarSize } ROWS ");
                if (Rows != 0)
                {
                    sql.Append($"  FETCH NEXT {Rows} ROWS ONLY ");
                }
            }

            return(new SelectResult <TResult>(sql.ToString(), countSql.ToString(), p));
        }
コード例 #5
0
ファイル: QueryInfo.cs プロジェクト: xiong0317/LnskyDB
        public ISelectResult <TResult> Select <TResult>(Expression <Func <T, TResult> > sel) where TResult : new()
        {
            var dynamicParameters = new DynamicParameters();
            var selExp            = new JoinSelectExpression(sel, new Dictionary <string, string> {
                { "", "[jtmp]" }
            }, dynamicParameters);
            var selSql   = string.Join(",", selExp.QueryColumns).Replace("[jtmp].", "");
            var sql      = new StringBuilder($"SELECT {selSql} FROM {DBTool.GetTableName(this.DBModel)} WHERE 1=1");
            var countSql = new StringBuilder($"SELECT COUNT(1) FROM {DBTool.GetTableName(this.DBModel)} WHERE 1=1");

            var whereExp = new WhereExpression(WhereExpression, "", dynamicParameters);

            sql.Append(whereExp.SqlCmd);
            countSql.Append(whereExp.SqlCmd);

            if (OrderbyList.Count > 0)
            {
                sql.Append($" ORDER BY ");
                var olst = OrderbyList.Select(m =>
                {
                    var order = new JoinOrderExpression(m.Field, new Dictionary <string, string> {
                        { "", "" }
                    }, dynamicParameters);
                    return(order.SqlCmd + " " + m.OrderBy);
                });
                sql.Append(string.Join(",", olst) + " ");
            }

            if (StarSize != 0 || Rows != 0)
            {
                sql.Append($" OFFSET {StarSize } ROWS ");
                if (Rows != 0)
                {
                    sql.Append($"  FETCH NEXT {Rows} ROWS ONLY ");
                }
            }
            return(new SelectResult <TResult>(sql.ToString(), countSql.ToString(), dynamicParameters));
        }
コード例 #6
0
ファイル: JoinQueryInfo.cs プロジェクト: zdlian/LnskyDB
 public IJoinQuery<T> OrderBy<TProperty>(Expression<Func<T, TProperty>> field)
 {
     if (field != null)
         OrderbyList.Add(new OrderCriteria { OrderBy = EOrderBy.Asc, Field = field });
     return this;
 }