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