/// <summary>
/// 获取库下全部表
/// </summary>
/// <param name="context"></param>
public void GetTableList(HttpContext context)
{
var id = context.Request["id"];
var label = context.Request["label"];
var tableList = DbClient.Query <TableBaseInfo>($@"USE {label}
SELECT TableName = CASE WHEN a.colorder=1 THEN d.name
ELSE ''
END,TableDescribe = CASE WHEN a.colorder=1 THEN ISNULL(f.value,'')
ELSE ''
END
FROM syscolumns a
LEFT JOIN systypes b
ON a.xusertype=b.xusertype
INNER JOIN sysobjects d
ON a.id=d.id
AND d.xtype='U'
AND d.name<>'dtproperties'
LEFT JOIN syscomments e
ON a.cdefault=e.id
LEFT JOIN sys.extended_properties g
ON a.id=g.major_id
AND a.colid=g.minor_id
LEFT JOIN sys.extended_properties f
ON d.id=f.major_id
AND f.minor_id=0
ORDER BY a.id,a.colorder;").ToList();
var nodeList = tableList.Where(x => !string.IsNullOrEmpty(x.TableName.Trim())).Select(x => new Node
{
id = Convert.ToInt32(id + tableList.IndexOf(x)),
label = $"{x.TableName}{(string.IsNullOrEmpty(x.TableDescribe) ? "" : $"({x.TableDescribe})")}",
children = Operate
});
private string GetIdentityKey()
{
var properties = _properties.GetProperties();
var identityKey = properties.FirstOrDefault(x => x.Name == "IdentityKey");
if (identityKey != null)
{
return(identityKey.Name);
}
var field = string.Empty;
var strSql =
$@"USE {ConfigurationManager.ConnectionStrings["DATABASE"].ConnectionString};
SELECT ( CASE WHEN COLUMNPROPERTY(id, name, 'IsIdentity') = 1
THEN '1'
ELSE '0'
END ) as identityKey,
name
FROM syscolumns
WHERE id = OBJECT_ID ('{_tableName}') ";
try
{
var list = DbClient.Query <dynamic>(strSql);
var model = list.FirstOrDefault(x => x.identityKey.ToString() == "1");
if (model != null)
{
field = (model.name ?? "").ToString();
}
return(field);
}
catch (Exception)
{
return("");
}
}
/// <summary>
/// 查询数据 (需要分页请先配置PageConfig)
/// </summary>
/// <returns></returns>
public IEnumerable <T> Select()
{
var totlaSql = "";
var isPage = PageConfig.PageIndex > 0 && PageConfig.PageSize > 0;
SqlString = new StringBuilder();
if (isPage)
{
var pageSortField = IsNullOrEmpty(PageConfig.PageSortSql)
? $" {PageConfig.PageSortField} {GetDescription(PageConfig.SortEnum)} "
: PageConfig.PageSortSql;
SqlString.Append("SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY " +
$" {pageSortField} " +
$" ) AS ROWNUMBER ,{GetShowString()} FROM {_tableName} ");
totlaSql += $"SELECT COUNT(1) FROM {_tableName} ";
}
else
{
var top = Top > 0 ? $"TOP({Top})" : "";
SqlString.Append($"SELECT {top} {GetShowString()} FROM {_tableName} ");
}
var join = GetJoinString();
if (!IsNullOrEmpty(join.Trim()))
{
if (IsNullOrEmpty(Alia.Trim()))
{
throw new Exception(GetDescription(ErrorEnum.E1003));
}
SqlString.Append(join);
totlaSql += join;
}
var para = new DynamicParameters();
var sp = GetWhereString();
para.AddDynamicParams(sp.Parameter);
SqlString.Append(" WHERE 1=1 " + sp.SqlStr);
totlaSql += " WHERE 1=1 " + sp.SqlStr;
var group = GetGroupString();
if (!IsNullOrEmpty(group.Trim()))
{
SqlString.Append(" GROUP BY " + group);
totlaSql += " GROUP BY " + group;
}
var sort = GetSortString();
if (!IsNullOrEmpty(sort.Trim()))
{
SqlString.Append(" ORDER BY " + sort);
}
if (isPage)
{
SqlString.Append($") A WHERE ROWNUMBER BETWEEN {(PageConfig.PageIndex - 1) * PageConfig.PageSize + 1} AND {PageConfig.PageIndex * PageConfig.PageSize} ");
Total = DbClient.Query <int>(totlaSql, para).FirstOrDefault();
}
return(DbClient.Query <T>(SqlString.ToString(), para));
}
private string GetPrimaryKey()
{
var properties = _properties.GetProperties();
var primaryKey = properties.FirstOrDefault(x => x.Name == "PrimaryKey");
if (primaryKey != null)
{
return(primaryKey.Name);
}
var strSql = $"USE {ConfigurationManager.ConnectionStrings["DATABASE"].ConnectionString};EXEC sp_pkeys @table_name='{RemoveStrModel(_properties.UnderlyingSystemType.Name)}'";
try
{
return(DbClient.Query <dynamic>(strSql).FirstOrDefault()?.COLUMN_NAME.ToString());
}
catch (Exception)
{
return("");
}
}
/// <summary>
/// 获取全部的库
/// </summary>
/// <param name="context"></param>
public void GetDbList(HttpContext context)
{
var dbList = DbClient.Query <string>(@"SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ( 'master', 'tempdb', 'model', 'msdb' )
ORDER BY name;").ToList();
var nodeList = dbList.Select(x => new Node
{
id = dbList.IndexOf(x),
label = x,
children = new List <Node> {
new Node
{
children = new List <Node>(),
id = Convert.ToInt32(dbList.IndexOf(x).ToString() + "1"),
label = "加载中..."
}
}
});
context.Response.Write(JsonConvert.SerializeObject(nodeList));
context.Response.End();
}
/// <summary>
/// 插入一条数据 (数据来自初始化传入的对象实体)
/// </summary>
/// <returns></returns>
public int Insert()
{
SqlString = new StringBuilder();
var properties = _properties.GetProperties();
var fields = string.Empty;
var values = string.Empty;
var para = new DynamicParameters();
foreach (var t in properties)
{
if (IsNullOrEmpty(IdentityKey) || IdentityKey != t.Name)
{
fields += $"{t.Name},";
values += $"@{t.Name},";
para.Add("@" + t.Name, t.GetValue(_model, null));
}
}
SqlString.Append($" INSERT INTO {_tableName} ({fields.TrimEnd(',')}) VALUES ({values.TrimEnd(',')});");
if (!IsNullOrEmpty(IdentityKey))
{
SqlString.Append(" SELECT @@IDENTITY; ");
}
return(DbClient.Query <int>(SqlString.ToString(), para).FirstOrDefault());
}
/// <summary>
/// 获取匿名对象
/// </summary>
/// <param name="where">条件(以 AND 开头)</param>
/// <param name="show">显示字段</param>
/// <returns></returns>
public IEnumerable <dynamic> GetDynamic(string where, string show = "")
{
GetSelectSql(where, show);
return(DbClient.Query <dynamic>(SqlString.ToString()));
}
/// <summary> /// 直接执行一条SQL /// </summary> /// <param name="sql"></param> /// <returns></returns> public IEnumerable <T> GetModelListBySql(string sql) => DbClient.Query <T>(sql);
/// <summary>
/// 获取一条数据
/// </summary>
/// <param name="key">主键OR自增键</param>
/// <param name="value">值</param>
/// <param name="show">显示字段</param>
/// <returns></returns>
public T GetModel(string key, object value, string show = "")
{
GetSelectSql(key, value, show);
return(DbClient.Query <T>(SqlString.ToString()).FirstOrDefault());
}
/// <summary>
/// 简单查询 (单个条件)
/// </summary>
/// <param name="field">筛选字段</param>
/// <param name="value">筛选值</param>
/// <param name="show">显示字段</param>
/// <returns></returns>
public IEnumerable <T> GetModelList(string field, object value, string show = "")
{
GetSelectSql(field, value, show);
return(DbClient.Query <T>(SqlString.ToString()));
}
