using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web;
using Ant.Data;
using System.Diagnostics;
namespace Ant.ORM
{
public class MakeMsJoinSql : SqlQuery
{
public string SelectColumn = "";
public string JoinTable = "";
private static volatile MakeMsJoinSql instance = null;
private static object lockHelper = new object();
public StringPlus StrShowSql = new StringPlus("Select "); //组装外显的SQL语句
public StringPlus StrSql = new StringPlus(); //组装SQL语句
public SqlEntity sqlmod = new SqlEntity();
public MakeMsJoinSql()
{
StrShowSql = new StringPlus("Select "); //组装外显的SQL语句
StrSql = new StringPlus(); //组装SQL语句
}
#region 赋值为了组装SQL语句
/// <summary>
/// 查询外显字段
/// </summary>
/// <param name="colume"></param>
/// <returns></returns>
public SqlQuery Select(MakeShowFields colume)
{
sqlmod.FieldColumns = colume;
return this;
}
/// <summary>
/// 查询外显字段
/// </summary>
/// <param name="colume"></param>
/// <returns></returns>
public SqlQuery Select()
{
sqlmod.FieldColumns = null;
return this;
}
/// <summary>
/// Top条数
/// </summary>
/// <param name="num"></param>
/// <returns></returns>
public SqlQuery Top(int num)
{
sqlmod.StrTop = num;
return this;
}
/// <summary>
/// 分页方法
/// </summary>
/// <param name="pagesize"></param>
/// <param name="pageno"></param>
/// <returns></returns>
public SqlQuery PageSize(int pagesize, int pageno)
{
sqlmod.PageSize = pagesize;
sqlmod.PageNo = pageno;
return this;
}
/// <summary>
/// 查询主表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strasname"></param>
/// <returns></returns>
public SqlQuery From<T>(string strasname)
{
sqlmod.StrFrom = typeof(T).Name;
sqlmod.StrAsName = strasname;
return this;
}
/// <summary>
/// 关联表
/// </summary>
/// <param name="jointable"></param>
/// <returns></returns>
public SqlQuery Join(MakeJoinTable jointable)
{
sqlmod.JoinTable = jointable;
return this;
}
/// <summary>
/// 查询条件
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public SqlQuery Where(MakeQueryWhere where)
{
sqlmod.QueryWhere = where;
return this;
}
/// <summary>
/// 排序条件
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public SqlQuery OrderBy(MakeOrderBy orderBy)
{
sqlmod.OrderBy = orderBy;
foreach (var orderByModel in orderBy.OrderByList)
{
var tableAsName = GetTableAsName(orderByModel.TableName);
foreach (var col in orderByModel.StrOrderBy)
{
sqlmod.StrOrderBy = sqlmod.StrOrderBy + " " + tableAsName + "." + col + " " + orderByModel.SortType + " ,";
}
}
return this;
}
/// <summary>
///
/// </summary>
/// <param name="strlist"></param>
/// <returns></returns>
public SqlQuery Group(params string[] strlist)
{
return this;
}
/// <summary>
/// 降序排序
/// </summary>
/// <param name="columns">The columns.</param>
/// <returns></returns>
public SqlQuery OrderDesc<T>(params string[] columns)
{
string tableAsName = GetTableAsName(typeof(T).Name);
foreach (string column in columns)
{
sqlmod.StrOrderBy = sqlmod.StrOrderBy + " " + tableAsName + "." + column + " Desc ,";
}
return this;
}
/// <summary>
/// 降序排序
/// </summary>
/// <param name="columns">The columns.</param>
/// <returns></returns>
public SqlQuery OrderAsc<T>(params string[] columns)
{
string tableAsName = GetTableAsName(typeof(T).Name);
foreach (string column in columns)
{
sqlmod.StrOrderBy = sqlmod.StrOrderBy + " " + tableAsName + " . " + column + " asc ,";
}
return this;
}
#endregion
/// <summary>
/// 获取单个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <returns></returns>
public ResponseModel GetEnSingle<T>(SqlNote sqlNote, DataAccess db = null)
{
var enty = new ResponseModel();
try
{
sqlmod.StrTop = 1;
var dt = GetDtList(null, db);
var mod = DataToModel.Dt2EnList<T>(dt.DataTable);
enty.IsSuccess = true;
enty.ResultModel = mod.FirstOrDefault();
enty.StrSql = StrShowSql.ToString();
return enty;
}
catch (Exception ex)
{
}
return enty;
}
/// <summary>
/// 获取列表
/// </summary>
/// <returns></returns>
public ResponseModel GetDtList(SqlNote sqlNote, DataAccess db = null)
{
var response = new ResponseModel();
try
{
if (sqlNote.IfNotNull())
{
if(sqlNote.NewSt.IsNull())
{
response.Message = "没有获取到方法的具体行号";
return response;
}
if (sqlNote.Author.IsEmpty())
{
response.Message = "方法作者没有赋值";
return response;
}
if(sqlNote.SqlDesc.IsEmpty())
{
response.Message = "方法的备注没有赋值";
return response;
}
var dtlist = DataTableList(sqlNote, db);
if (dtlist.IsHaveRows())
{
response.IsSuccess = true;
response.ResultModel = dtlist;
response.StrSql = StrShowSql.ToString();
response.Message = "获取数据成功";
}
}
}
catch (Exception ex)
{
}
return response;
}
/// <summary>
/// 获取列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public ResponseModel GetEnList<T>(SqlNote newSt, DataAccess db = null)
{
var listmod = new ResponseModel();
try
{
var dtlist = DataTableList(null, db);
var modlist = DataToModel.Dt2EnList<T>(dtlist);
listmod.ResultModel = modlist;
listmod.IsSuccess = modlist.Count > 0;
}
catch (Exception ex)
{
}
return listmod;
}
/// <summary>
/// 分页方法
/// </summary>
/// <param name="pageno"></param>
/// <param name="pagesize"></param>
/// <param name="db"></param>
/// <returns></returns>
public ResponseModel GetPageSite(int pageno, int pagesize, SqlNote newSt, DataAccess db = null)
{
var response = new ResponseModel();
try
{
sqlmod.PageSize = pagesize;
sqlmod.PageNo = pageno;
var dtt = PageSiteDatable(newSt,db);
if (dtt.Rows.Count > 0)
{
response.IsSuccess = true;
response.DataTable = dtt;
response.Message = "查询DataTable返回成功";
}
}
catch (Exception ex)
{
}
return response;
}
#region 组装SQL语句
/// <summary>
/// 获取分页DataTable
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
private DataTable PageSiteDatable(SqlNote newSt, DataAccess db)
{
string columnName; object columnValue; string tableName; string tableAsName = string.Empty; DbType dbtype; string overcolumnName;
StrShowSql.Append("SELECT * FROM (SELECT ROW_NUMBER() OVER (");
if (string.IsNullOrEmpty(sqlmod.StrOrderBy))
{
StrShowSql.Append(" order by ");
StrShowSql.Append(sqlmod.StrAsName + "." + sqlmod.StrOrderBy);
}
else
{
//主键
StrShowSql.Append(" order by ");
}
StrShowSql.Append(") AS rowNumber,");
var showfiled = sqlmod.FieldColumns;
#region 拼接显示字段
if (showfiled.IsNull())
{
StrShowSql.Append(" * ");
}
else
{
foreach (FieldColumnsList fieldList in showfiled.Values)
{
tableName = fieldList.TableName;
tableAsName = GetTableAsName(tableName);
foreach (FieldColumns feild in fieldList.FieldList)
{
if (!feild.FieldAsName.IsEmpty())
StrShowSql.Append(tableAsName + "." + feild.FieldColumn + " as " + feild.FieldAsName + ",");
else
StrShowSql.Append(tableAsName + "." + feild.FieldColumn + ",");
StrShowSql.AppendLine();
}
}
}
#endregion
StrShowSql.DelLastComma();
StrShowSql.Append(" from ");
StrShowSql.Append(sqlmod.StrFrom + " as " + sqlmod.StrAsName + " with (nolock) ");
#region 拼接关联表
foreach (JoinOnTableNode jion in sqlmod.JoinTable.RootList)
{
string tableAsName1 = GetTableAsName(jion.TableName); string tableAsName2 = GetTableAsName(jion.NTableName);
StrShowSql.Append(JoinSql.GetJoinTypeValue(jion.JoinType) + " " + jion.NTableName + " as " + jion.NTableAsName);
StrShowSql.Append(" " + tableAsName1 + "." + jion.JoinOn1 + "=" + tableAsName2 + "." + jion.JoinOn2);
StrShowSql.AppendLine();
}
#endregion
StrShowSql.Append(" where 1=1 ");
StrSql.Append(StrShowSql);//把外显示SQL组装带到参数的SQL中去
MakeQueryWhere where = sqlmod.QueryWhere;
#region 拼接查询条件
if (where.IfNotNull())
{
foreach (QueryWhereList queryList in where.QueryList.Values)
{
foreach (TableColumn col in queryList.QueryList)
{
columnName = col.ColumnName;
overcolumnName = (col.OverColumnName.IsEmpty()) ? columnName : col.OverColumnName;
columnValue = col.ColumnValue;
dbtype = col.ColumnType;
tableName = col.TableName;
tableAsName = GetTableAsName(tableName) + "." + columnName;
if (col.Operatstr==Operator.Like)
{
StrShowSql.Append(queryList.WhereStr + tableAsName +
QueryOperator.GetOperator(col.Operatstr) + " %" +
GetNativeType(dbtype, columnValue) + "% ");
StrSql.Append(queryList.WhereStr + tableAsName + QueryOperator.GetOperator(col.Operatstr) +
"@" + overcolumnName + " ESCAPE '!' ");
var parameter = new QueryParameter();
parameter.ParameterName = "@" + overcolumnName;
parameter.Value = string.Format("%{0}%", GetEscape(columnValue.ToString()));
parameter.DbType = dbtype;
sqlmod.Parameters.Add(parameter);
}
else
{
StrShowSql.Append(queryList.WhereStr + tableAsName + QueryOperator.GetOperator(col.Operatstr) + GetNativeType(dbtype, columnValue));
StrSql.Append(queryList.WhereStr + tableAsName + QueryOperator.GetOperator(col.Operatstr) + "@" +
overcolumnName);
var parameter = new QueryParameter();
parameter.ParameterName = "@" + overcolumnName;
parameter.Value = columnValue;
parameter.DbType = dbtype;
sqlmod.Parameters.Add(parameter);
}
}
}
}
#endregion
StrSql.AppendFormat(") AS sp WHERE rowNumber BETWEEN {0} and {1} ", (sqlmod.PageNo + 1), (sqlmod.PageNo + sqlmod.PageSize));
if (db.IsNull())
db = DataAccessFactory.GetWriteDataDefault;
StrSql.Append(GetSqlComments(newSt));
IDataReader rdr = db.ExecuteDataReader(StrSql.ToString(), sqlmod.Parameters);
DataTable dtt = DataToModel.Dr2Dt(rdr);
return dtt;
}
/// <summary>
/// 获取DataTableList
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
private DataTable DataTableList(SqlNote newSt, DataAccess db)
{
string columnName; object columnValue; string tableName; string tableAsName = string.Empty; DbType dbtype;
string overcolumnName;
if (sqlmod.StrTop > 0)
StrShowSql.AppendFormat(" top {0} ", sqlmod.StrTop);
#region 拼接显示字段
var showfiled = sqlmod.FieldColumns;
if (showfiled.IsNull())
{
StrShowSql.Append(" * ");
}
else
{
foreach (FieldColumnsList fieldList in showfiled.Values)
{
tableAsName = GetTableAsName(fieldList.TableName);
foreach (FieldColumns feild in fieldList.FieldList)
{
if (!feild.FieldAsName.IsEmpty())
StrShowSql.Append(tableAsName + "." + feild.FieldColumn + " as " + feild.FieldAsName + ",");
else
StrShowSql.Append(tableAsName + "." + feild.FieldColumn + ",");
}
StrShowSql.AppendLine();
}
}
#endregion
StrShowSql.DelLastComma();
StrShowSql.Append(" from " + sqlmod.StrFrom + " as " + sqlmod.StrAsName + " with (nolock) ");
StrShowSql.AppendLine();
#region 拼接关联表
foreach (JoinOnTableNode jion in sqlmod.JoinTable.RootList)
{
string tableAsName1 = GetTableAsName(jion.TableName); string tableAsName2 = GetTableAsName(jion.NTableName);
StrShowSql.Append(JoinSql.GetJoinTypeValue(jion.JoinType) + " " + jion.NTableName + " as " + jion.NTableAsName + " with (nolock) on ");
StrShowSql.Append(tableAsName1 + "." + jion.JoinOn1 + "=" + tableAsName2 + "." + jion.JoinOn2);
StrShowSql.AppendLine();
}
#endregion
StrShowSql.Append(" where 1=1 ");
StrSql.Append(StrShowSql);//把外显示SQL组装带到参数的SQL中去
#region 拼接查询条件
MakeQueryWhere where = sqlmod.QueryWhere;
if (where.IfNotNull())
{
foreach (QueryWhereList queryList in where.QueryList.Values)
{
if(queryList.QueryList.Count>0)
{
StrSql.Append("(");
StrShowSql.Append("(");
}
foreach (TableColumn col in queryList.QueryList)
{
columnName = col.ColumnName;
overcolumnName = (col.OverColumnName.IsEmpty()) ? columnName : col.OverColumnName;
columnValue = col.ColumnValue;
dbtype = col.ColumnType;
tableName = col.TableName;
tableAsName = GetTableAsName(tableName) + "." + columnName;
StrShowSql.Append(tableAsName + QueryOperator.GetOperator(col.Operatstr) + GetNativeType(dbtype, columnValue) + queryList.WhereStr);
StrSql.Append(tableAsName + QueryOperator.GetOperator(col.Operatstr) + "@" + overcolumnName + queryList.WhereStr);
var parameter = new QueryParameter();
parameter.ParameterName = "@" + overcolumnName;
parameter.Value = columnValue;
parameter.DbType = dbtype;
sqlmod.Parameters.Add(parameter);
}
if(queryList.QueryList.Count>0)
{
StrShowSql.DelLastChar(" and ");
StrShowSql.DelLastChar(" or ");
StrSql.DelLastChar(" and ");
StrSql.DelLastChar(" or ");
StrSql.Append(")");
StrShowSql.Append(")");
}
}
}
#endregion
if (sqlmod.StrOrderBy.IfNotNull())
{
if (sqlmod.StrOrderBy.EndsWith(","))
sqlmod.StrOrderBy = sqlmod.StrOrderBy.TrimEnd(',');
StrShowSql.Append(" order by " + sqlmod.StrOrderBy);
StrSql.Append(" order by " + sqlmod.StrOrderBy);
}
if (db.IsNull())
db = DataAccessFactory.GetWriteDataDefault;
StrSql.Append(GetSqlComments(newSt));
IDataReader rdr = db.ExecuteDataReader(StrSql.ToString(), sqlmod.Parameters);
DataTable dtt = DataToModel.Dr2Dt(rdr);
return dtt;
}
#endregion
#region 返回sql注释
/// <summary>
/// 返回sql注释
/// </summary>
/// <param name="newSt">调用方法new StackTrace(true)</param>
/// <param name="author">sql作者</param>
/// <param name="sqlDesc">sql说明</param>
/// <returns></returns>
/// <remarks>
/// added by 季健国 at 2015-3-7
/// </remarks>
public static string GetSqlComments(SqlNote sqlNote)
{
try
{
StackFrame stackFrame = sqlNote.NewSt.GetFrame(0);
StringBuilder commetBuilder = new StringBuilder();
commetBuilder.AppendFormat("/*项目名称:{0}/作者:{1}/备注:{2}/文件:///{3}/方法:{4}/行号：{5}*/","某某项目", sqlNote.Author, sqlNote.SqlDesc, stackFrame.GetFileName(), stackFrame.GetMethod().Name, stackFrame.GetFileLineNumber());
return commetBuilder.ToString();
}
catch
{
//默认许可按此处理
return "";
}
}
#endregion
/// <summary>
/// 获取别名
/// </summary>
/// <param name="tablename"></param>
/// <returns></returns>
private string GetTableAsName(string tablename)
{
string tableasname;
if (tablename == sqlmod.StrFrom)
{
tableasname = sqlmod.StrAsName;
if (tableasname.IsEmpty())
tableasname = sqlmod.StrFrom;
}
else
{
var mod = sqlmod.JoinTable.RootList.Find(p => p.NTableName.Equals(tablename));
tableasname = mod.NTableAsName;
if (tableasname.IsEmpty())
tableasname = mod.TableName;
}
return tableasname;
}
/// <summary>
/// 通配符转换
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
protected static string GetEscape(string str)
{
return str.Replace("!", "!!").Replace("%", "!%").Replace("_", "!_");
}
/// <summary>
/// Gets the type of the native.
/// </summary>
/// <param name="dbType">Type of the db.</param>
/// <returns></returns>
private object GetNativeType(DbType dbType, object obj)
{
switch (dbType)
{
case DbType.AnsiString:
case DbType.Object:
case DbType.String:
case DbType.AnsiStringFixedLength:
case DbType.StringFixedLength:
return "'" + obj + "'";
case DbType.Binary:
case DbType.Byte:
case DbType.SByte:
return "'" + obj + "'";
case DbType.Boolean:
return obj;
case DbType.Currency:
return "money";
case DbType.Date:
case DbType.DateTime:
case DbType.Time:
return "'" + obj + "'";
case DbType.Decimal:
return obj;
case DbType.Double:
return obj;
case DbType.Guid:
return "'" + obj + "'";
case DbType.Int16:
case DbType.Int32:
case DbType.UInt16:
case DbType.UInt32:
return obj;
case DbType.Int64:
case DbType.UInt64:
return obj;
case DbType.Single:
return "'" + obj + "'";
case DbType.VarNumeric:
return obj;
case DbType.Xml:
return "'" + obj + "'";
}
return obj;
}
}
}