public List <T> GetKeyValueData <T>(string dataCategory) where T : class, new() { List <KeyValueDataAccessSetting> settingList = KeyValueDataAccessSettingManager.GetAllSettings(); KeyValueDataAccessSetting setting = settingList.Find(f => f.DataCategory.ToUpper().Trim() == dataCategory.Trim().ToUpper()); if (setting == null) { throw new NullReferenceException("没有找到" + dataCategory + "在KeyValueDataAccessSetting.config中的配置!"); } string sql = string.Empty; if (string.IsNullOrEmpty(setting.DataCategoryPath)) { sql = @"SELECT [SysNo] ,[DataCategory] ,[BizKey] ,[BizValue] ,[Status] ,[InDate] ,[LastEditDate] FROM [WebKeyValueData].[dbo].[DefaultKVD] WITH(NOLOCK) WHERE DataCategory='{0}'"; sql = string.Format(sql, dataCategory.Replace("'", "''")); } else { sql = @"SELECT [SysNo] ,[DataCategory] ,[BizKey] ,[BizValue] ,[Status] ,[InDate] ,[LastEditDate] FROM {0} WITH(NOLOCK) WHERE DataCategory='{1}'"; sql = string.Format(sql, setting.DataCategoryPath, dataCategory.Replace("'", "''")); } string dbConnectionName = SQLDBProvider.DBConnectionName; CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromSql(sql, dbConnectionName); DataTable dt = cmd.ExecuteDataTable(); List <T> list = new List <T>(); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { string bizValue = row["BizValue"] != null ? row["BizValue"].ToString().Trim() : null; if (!string.IsNullOrEmpty(bizValue) && bizValue.Length >= 4) { T t = SerializationUtility.XmlDeserialize <T>(bizValue); list.Add(t); } } } return(list); }
/// <summary> /// 查询数据并分页 /// </summary> /// <typeparam name="Q"></typeparam> /// <typeparam name="T"></typeparam> /// <param name="command"></param> /// <param name="filter"></param> /// <param name="needRealTime"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="sortField"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <T> QueryData <Q, T>(CustomDataCommand command, Q filter, string configName, PagingInfoEntity pagingInfo, out int totalCount) where T : class, new() { pagingInfo.SortField = "[SOMaster.SOSysNo]"; if (string.IsNullOrEmpty(pagingInfo.SortField)) { throw new ApplicationException("You must specified one sort field at least."); } var config = ConfigHelper.GetRealTimeConfig(configName); string xmlFields; List <string> columns; BuildColumns(config.ReturnFields, out xmlFields, out columns); var cmd = DataCommandManager.CreateCustomDataCommandFromSql(loadPagingDataSql, command.DatabaseAliasName); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd, pagingInfo, pagingInfo.SortField)) { BuilCondition <Q>(filter, config.DataType, config.FilterFields, sqlBuilder); cmd.CommandText = sqlBuilder.BuildQuerySql(); //把传入的参数添加到组合后的DataCommand中 //command.DbParameterList.ForEach(p => //{ // var param = cmd.DbParameterList.FirstOrDefault(k => k.ParameterName.Trim().ToUpper() == p.ParameterName.Trim().ToUpper()); // if (param == null) // { // cmd.AddInputParameter(p.ParameterName, p.DbType, p.Value); // } //}); cmd.CommandText = cmd.CommandText.Replace("#Columns#", columns.Join(",")); cmd.CommandText = cmd.CommandText.Replace("#XmlFields#", xmlFields.ToString()); cmd.CommandText = cmd.CommandText.Replace("#InputSql#", command.CommandText); var list = cmd.ExecuteEntityList <T>(); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(list); } }
/// <summary> /// 查询数据不分页 /// </summary> /// <typeparam name="Q"></typeparam> /// <typeparam name="T"></typeparam> /// <param name="command"></param> /// <param name="filter"></param> /// <returns></returns> public static List <T> QueryData <Q, T>(CustomDataCommand command, Q filter, string configName) where T : class, new() { var config = ConfigHelper.GetRealTimeConfig(configName); string xmlFields; List <string> columns; BuildColumns(config.ReturnFields, out xmlFields, out columns); var cmd = DataCommandManager.CreateCustomDataCommandFromSql(loadDataSql, command.DatabaseAliasName); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd, "SysNo desc")) { BuilCondition <Q>(filter, config.DataType, config.FilterFields, sqlBuilder); cmd.CommandText = sqlBuilder.BuildQuerySql(); cmd.CommandText = cmd.CommandText.Replace("#XmlFields#", xmlFields.ToString()); cmd.CommandText = cmd.CommandText.Replace("#InputSql#", command.CommandText); var list = cmd.ExecuteEntityList <T>(); return(list); } }