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);
}
public IJoinQuery <T> OrderByDescing <TProperty>(Expression <Func <T, TProperty> > field)
{
if (field != null)
{
OrderbyList.Add(new OrderCriteria {
OrderBy = EOrderBy.Desc, Field = field
});
}
return(this);
}
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();
}
}
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));
}
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));
}
public IJoinQuery<T> OrderBy<TProperty>(Expression<Func<T, TProperty>> field)
{
if (field != null)
OrderbyList.Add(new OrderCriteria { OrderBy = EOrderBy.Asc, Field = field });
return this;
}