/// <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); }