/// <summary>
/// 根据SQL语句获取实体类的字符串
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="className"></param>
/// <returns></returns>
public string SqlToClass(SqlSugarClient db, string sql, string className)
{
var dt = db.GetDataTable(sql);
var reval = DataTableToClass(dt, className);
return(reval);
}
/// <summary>
/// 根据表名获取实体类的字符串
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="className"></param>
/// <returns></returns>
public string TableNameToClass(SqlSugarClient db, string tableName)
{
var dt = db.GetDataTable(string.Format("select top 1 * from {0}", tableName));
var reval = DataTableToClass(dt, tableName);
return(reval);
}
/// <summary>
///根据表名获取自添列 keyTableName Value columnName
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
internal static List <KeyValue> GetIdentitiesKeyByTableName(SqlSugarClient db, string tableName)
{
if (DataBaseConfig.IsManualConfiguration)
{ //手动配置
if (DataBaseConfig.IdentityKeys == null)
{
return(null);
}
return(DataBaseConfig.IdentityKeys.Where(it => it.Key.ToLower() == tableName.ToLower()).ToList());
}
string key = "GetIdentityKeyByTableName" + tableName;
var cm = CacheManager <List <KeyValue> > .GetInstance();
List <KeyValue> identityInfo = null;
if (cm.ContainsKey(key))
{
identityInfo = cm[key];
return(identityInfo);
}
else
{
string sql = string.Format(@"
declare @Table_name varchar(60)
set @Table_name = '{0}';
Select so.name tableName, --表名字
sc.name keyName, --自增字段名字
ident_current(so.name) curr_value, --自增字段当前值
ident_incr(so.name) incr_value, --自增字段增长值
ident_seed(so.name) seed_value --自增字段种子值
from sysobjects so
Inner Join syscolumns sc
on so.id = sc.id
and columnproperty(sc.id, sc.name, 'IsIdentity') = 1
Where upper(so.name) = upper(@Table_name)
", tableName);
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
var dt = db.GetDataTable(sql);
db.IsEnableLogEvent = isLog;
identityInfo = new List <KeyValue>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
identityInfo.Add(new KeyValue()
{
Key = dr["tableName"].ToString().ToLower(), Value = dr["keyName"].ToString()
});
}
}
cm.Add(key, identityInfo, cm.Day);
return(identityInfo);
}
}
/// <summary>
/// 根据表名获取实体类的字符串
/// </summary>
/// <param name="db"></param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public string TableNameToClass(SqlSugarClient db, string tableName)
{
var dt = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(), tableName.GetTranslationSqlName()));
var tableColumns = GetTableColumns(db, tableName);
var reval = DataTableToClass(dt, tableName, null, tableColumns);
return(reval);
}
/// <summary>
/// 创建SQL实体文件,指定表名
/// </summary>
public void CreateClassFilesByTableNames(SqlSugarClient db, string fileDirectory, string nameSpace, params string[] tableNames)
{
var tables = db.GetDataTable("select name from sysobjects where xtype='U'");
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["name"].ToString().ToLower();
if (tableNames.Any(it => it.ToLower() == tableName))
{
var currentTable = db.GetDataTable(string.Format("select top 1 * from {0}", tableName));
var classCode = DataTableToClass(currentTable, tableName, nameSpace);
FileSugar.WriteText(fileDirectory.TrimEnd('\\') + "\\" + tableName + ".cs", classCode);
}
}
}
}
/// <summary>
/// 根据SQL语句获取实体类的字符串
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="className"></param>
/// <returns></returns>
public string SqlToClass(SqlSugarClient db, string sql, string className)
{
using (SqlConnection conn = new SqlConnection(db.ConnectionString))
{
var dt = db.GetDataTable(sql);
var reval = DataTableToClass(dt, className);
return(reval);
}
}
/// <summary>
/// 根据表获取主键
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
internal static List <string> GetPrimaryKeyByTableNames(SqlSugarClient db, string tableName)
{
if (DataBaseConfig.IsManualConfiguration) //手动配置
{
if (DataBaseConfig.PrimaryKeys == null)
{
return(null);
}
return(DataBaseConfig.PrimaryKeys.Where(it => it.Key.ToLower() == tableName.ToLower()).Select(it => it.Value).ToList());
}
string key = "GetPrimaryKeyByTableName" + tableName;
tableName = tableName.ToLower();
var cm = CacheManager <List <KeyValue> > .GetInstance();
List <KeyValue> primaryInfo = null;
//获取主键信息
if (cm.ContainsKey(key))
{
primaryInfo = cm[key];
}
else
{
string sql = @" SELECT a.name as keyName ,d.name as tableName
FROM syscolumns a
inner join sysobjects d on a.id=d.id
where exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
)))";
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
var dt = db.GetDataTable(sql);
db.IsEnableLogEvent = isLog;
primaryInfo = new List <KeyValue>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
primaryInfo.Add(new KeyValue()
{
Key = dr["tableName"].ToString().ToLower(), Value = dr["keyName"].ToString()
});
}
}
cm.Add(key, primaryInfo, cm.Day);
}
//反回主键
if (!primaryInfo.Any(it => it.Key == tableName))
{
return(null);
}
return(primaryInfo.Where(it => it.Key == tableName).Select(it => it.Value).ToList());
}
/// <summary>
/// 创建SQL实体文件
/// </summary>
/// <param name="db"></param>
/// <param name="tableOrView">是生成视图文件还是表文件,null生成表和视图，true生成表，false生成视图(默认为：null)</param>
/// <param name="callBack">回调函数</param>
public void CreateClassFilesInterface(SqlSugarClient db, bool?tableOrView, Action <DataTable, string, string> callBack)
{
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
string sql = SqlSugarTool.GetCreateClassSql(tableOrView);
var tables = db.GetDataTable(sql);
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["name"].ToString();
var currentTable = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(), GetTableNameWithSchema(db, tableName).GetTranslationSqlName()));
string className = db.GetClassTypeByTableName(tableName);
callBack(tables, className, tableName);
}
}
db.IsEnableLogEvent = isLog;
}
/// <summary>
/// 多线程请求所有数据库节点，同步汇总结果
/// </summary>
/// <typeparam name="T">支持DataTable、实体类和值类型</typeparam>
/// <param name="sql"></param>
/// <param name="connectionStringList">连接字符串数组</param>
/// <param name="whereObj">参数 例如: new { id="1",name="张三"}</param>
/// <returns></returns>
public Taskable <T> Taskable <T>(string sql, List <string> connectionStringList, object whereObj = null)
{
Taskable <T> reval = new Taskable <T>();
reval.Sql = sql;
reval.WhereObj = whereObj;
var tasks = new Task <CloudSearchResult <T> > [connectionStringList.Count];
for (int i = 0; i < tasks.Length; i++)
{
CloudPubMethod.TaskFactory <CloudSearchResult <T> >(ti =>
{
string innerSql = sql;
var connString = connectionStringList[ti];
var db = new SqlSugarClient(connString);
SettingConnection(db);
CloudSearchResult <T> itemReval = new CloudSearchResult <T>();
var isDataTable = typeof(T) == typeof(DataTable);
var isClass = typeof(T).IsClass;
if (innerSql.Contains("$:->connectionString<-:$"))
{
innerSql = innerSql.Replace("$:->connectionString<-:$", connString);
}
if (isDataTable)
{
itemReval.DataTable = db.GetDataTable(innerSql, whereObj);
}
else if (isClass)
{
itemReval.Entities = db.SqlQuery <T>(innerSql, whereObj);
}
else
{
var obj = db.GetScalar(sql, whereObj);
if (obj == DBNull.Value)
{
itemReval.Value = default(T);
}
else
{
obj = Convert.ChangeType(obj, typeof(T));
itemReval.Value = (T)obj;
}
}
itemReval.ConnectionString = connString;
return(itemReval);
}, tasks, i);
}
Task.WaitAll(tasks);
reval.Tasks = tasks;
return(reval);
}
/// <summary>
/// 创建SQL实体文件
/// </summary>
/// <param name="db"></param>
/// <param name="fileDirectory"></param>
/// <param name="nameSpace">命名空间（默认：null）</param>
/// <param name="tableOrView">是生成视图文件还是表文件,null生成表和视图，true生成表，false生成视图(默认为：null)</param>
public void CreateClassFiles(SqlSugarClient db, string fileDirectory, string nameSpace = null, bool?tableOrView = null, Action <string> callBack = null)
{
var tables = db.GetDataTable("select name from sysobjects where xtype in ('U','V') ");
if (tableOrView != null)
{
if (tableOrView == true)
{
tables = db.GetDataTable("select name from sysobjects where xtype in ('U') ");
}
else
{
tables = db.GetDataTable("select name from sysobjects where xtype in ('V') ");
}
}
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["name"].ToString();
var currentTable = db.GetDataTable(string.Format("select top 1 * from {0}", tableName));
if (callBack != null)
{
var tableColumns = GetTableColumns(db, tableName);
var classCode = DataTableToClass(currentTable, tableName, nameSpace, tableColumns);
string className = db.GetClassTypeByTableName(tableName);
classCode = classCode.Replace("class " + tableName, "class " + className);
FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + className + ".cs", classCode, Encoding.UTF8);
callBack(className);
}
else
{
var tableColumns = GetTableColumns(db, tableName);
var classCode = DataTableToClass(currentTable, tableName, nameSpace, tableColumns);
FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + tableName + ".cs", classCode, Encoding.UTF8);
}
}
}
}
/// <summary>
/// 创建实体文件
/// </summary>
/// <param name="db"></param>
/// <param name="fileDirectory"></param>
/// <param name="nameSpace">命名空间（默认：system）</param>
/// <param name="tableOrView">是生成视图文件还是表文件,null生成表和视图，true生成表，false生成视图(默认为：null)</param>
/// <param name="callBack">生成文件后的处理，参数string为实体名</param>
/// <param name="callBack">生成文件前的处理，参数string为表名</param>
public void CreateClassFiles(SqlSugarClient db, string fileDirectory, string nameSpace = null, bool?tableOrView = null, Action <string> callBack = null, Action <string> preAction = null)
{
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
string sql = SqlSugarTool.GetCreateClassSql(tableOrView);
var tables = db.GetDataTable(sql);
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["name"].ToString();
if (preAction != null)
{
preAction(tableName);
}
var currentTable = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(), GetTableNameWithSchema(db, tableName).GetTranslationSqlName()));
if (callBack != null)
{
var tableColumns = GetTableColumns(db, tableName);
var classCode = DataTableToClass(currentTable, tableName, nameSpace, tableColumns);
string className = db.GetClassTypeByTableName(tableName);
classCode = classCode.Replace("class " + tableName, "class " + className);
FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + className + ".cs", classCode, Encoding.UTF8);
callBack(className);
}
else
{
var tableColumns = GetTableColumns(db, tableName);
string className = db.GetClassTypeByTableName(tableName);
var classCode = DataTableToClass(currentTable, className, nameSpace, tableColumns);
FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + className + ".cs", classCode, Encoding.UTF8);
}
}
}
db.IsEnableLogEvent = isLog;
}
/// <summary>
/// 根据表获取主键
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
internal static string GetPrimaryKeyByTableName(SqlSugarClient db, string tableName)
{
string key = "GetPrimaryKeyByTableName";
tableName = tableName.ToLower();
var cm = CacheManager <List <KeyValue> > .GetInstance();
List <KeyValue> primaryInfo = null;
//获取主键信息
if (cm.ContainsKey(key))
{
primaryInfo = cm[key];
}
else
{
string sql = @" SELECT a.name as keyName ,d.name as tableName
FROM syscolumns a
inner join sysobjects d on a.id=d.id
where exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
)))";
var dt = db.GetDataTable(sql);
primaryInfo = new List <KeyValue>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
primaryInfo.Add(new KeyValue()
{
Key = dr["tableName"].ToString().ToLower(), Value = dr["keyName"].ToString()
});
}
}
cm.Add(key, primaryInfo, cm.Day);
}
//反回主键
if (!primaryInfo.Any(it => it.Key == tableName))
{
return(null);
}
return(primaryInfo.First(it => it.Key == tableName).Value);
}
/// <summary>
/// 根据表获取主键
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
internal static string GetPrimaryKeyByTableName(SqlSugarClient db, string tableName)
{
string key = "GetPrimaryKeyByTableName";
tableName = tableName.ToLower();
var cm = CacheManager <List <KeyValue> > .GetInstance();
List <KeyValue> primaryInfo = null;
//获取主键信息
if (cm.ContainsKey(key))
{
primaryInfo = cm[key];
}
else
{
string sql = @" with tb as(
select * from sysobjects where xtype='U'
),
pk as(
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
)
select pk.COLUMN_NAME as keyName,tb.name as tableName from tb inner join pk on tb.name=pk.TABLE_NAME";
var dt = db.GetDataTable(sql);
primaryInfo = new List <KeyValue>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
primaryInfo.Add(new KeyValue()
{
Key = dr["tableName"].ToString().ToLower(), Value = dr["keyName"].ToString()
});
}
}
cm.Add(key, primaryInfo, cm.Day);
}
//反回主键
if (!primaryInfo.Any(it => it.Key == tableName))
{
return(null);
}
return(primaryInfo.First(it => it.Key == tableName).Value);
}
/// <summary>
/// 多线程请求所有数据库节点，同步汇总结果
/// </summary>
/// <typeparam name="T">支持DataTable、实体类和值类型</typeparam>
/// <param name="sqlSelect">sql from之前（例如： "select count(*)" ）</param>
/// <param name="sqlEnd">sql from之后（例如： "from table where id=1" </param>
/// <param name="whereObj">参数 例如: new { id="1",name="张三"}</param>
/// <returns></returns>
public TaskableWithCount <T> TaskableWithCount <T>(string sqlSelect, string sqlEnd, object whereObj = null)
{
TaskableWithCount <T> reval = new TaskableWithCount <T>();
reval.Sql = sqlSelect + sqlEnd;
reval.WhereObj = whereObj;
var tasks = new Task <CloudSearchResult <T> > [configList.Count];
for (int i = 0; i < tasks.Length; i++)
{
CloudPubMethod.TaskFactory <CloudSearchResult <T> >(ti =>
{
var connString = configList[ti].ConnectionString;
var db = new SqlSugarClient(connString);
SettingConnection(db);
CloudSearchResult <T> itemReval = new CloudSearchResult <T>();
var isDataTable = typeof(T) == typeof(DataTable);
var isClass = typeof(T).IsClass;
if (isClass)
{
itemReval.Entities = db.SqlQuery <T>(reval.Sql, whereObj);
}
else if (isDataTable)
{
itemReval.DataTable = db.GetDataTable(reval.Sql, whereObj);
}
else
{
var obj = db.GetScalar(reval.Sql, whereObj);
obj = Convert.ChangeType(obj, typeof(T));
itemReval.Value = (T)obj;
}
itemReval.Count = db.GetInt("SELECT COUNT(1)" + sqlEnd);;
itemReval.ConnectionString = connString;
return(itemReval);
}, tasks, i);
}
Task.WaitAll(tasks);
reval.Tasks = tasks;
return(reval);
}