DataReader转Dictionary数据类型之妙用

时间:2022-10-10 20:47:57

datareader转dictionary有很多用处,可以输出表中部分字段转实体字段,以前需要全部字段输出或者再建一个实体模型才行,这样就可以减少数据库的输出量了,特别是某些接口的格式化输出很方便。

先看底层代码部分

        /// <summary>
/// DataReader转Dictionary<string, object>数据类型/// </summary>
/// <param name="dataReader"></param>
/// <param name="close"></param>
/// <returns></returns>
public static List<Dictionary<string, object>> GetListDictionary(IDataReader dataReader, bool close = true)
{
List
<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
if (close)
{
using (dataReader)
{
while (dataReader.Read())
{
list.Add(DataFillDictionary(dataReader));
}
}

}
else
{
while (dataReader.Read())
{
list.Add(DataFillDictionary(dataReader));
}
}
return list;
}

/// <summary>
/// 将DataReader数据转为Dictionary<string, object>对象/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public static Dictionary<string, object> DataFillDictionary(IDataReader reader)
{
Dictionary
<string, object> dict = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
try
{
dict.Add(reader.GetName(i), reader.GetValue(i));
}
catch
{
dict.Add(reader.GetName(i),
null);
}
}
return dict;
}

调用

        private void GetWebUserList(HttpContext context)
{
string jsonResult = string.Empty;
var modifiedOn = context.Request["modifiedOn"];
var list = new List<Dictionary<string, object>>();

using (var dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.UserCenterDbType, BaseSystemInfo.UserCenterDbConnection))
{
try
{

using (var dr = new BaseUserManager(dbHelper).GetWebUserList(modifiedOn))
{
if (dr != null)
{
list
= DbLogic.GetListDictionary(dr);
}
}
var timeConverter = new IsoDateTimeConverter { DateTimeFormat = BaseSystemInfo.DateTimeFormat };
jsonResult
= JsonConvert.SerializeObject(list, Formatting.Indented, timeConverter);
}
catch (Exception ex)
{
NLogHelper.Warn(ex,
"GetWebUserList异常");
}
finally
{
dbHelper.Close();
}
}
}

获取数据的方法

        /// <summary>
/// 向官网同步用户信息
/// 只传部分字段
/// </summary>
/// <returns></returns>
public IDataReader GetWebUserList(string modifiedOn)
{
string commandText = "SELECT A." + BaseUserEntity.FieldId
+ " ,A." + BaseUserEntity.FieldUserName
+ " ,A." + BaseUserEntity.FieldRealName
+ " ,A." + BaseUserEntity.FieldCode
+ " ,A." + BaseUserEntity.FieldCompanyId
+ " ,A." + BaseUserEntity.FieldCompanyName
+ " ,A." + BaseUserEntity.FieldCompanyCode
+ " ,B." + BaseUserContactEntity.FieldMobile
+ " ,B." + BaseUserContactEntity.FieldModifiedOn
+ " FROM " + BaseUserEntity.TableName + " A "
+ " LEFT JOIN " + BaseUserContactEntity.TableName + " B "
+ " ON A." + BaseUserEntity.FieldId + " = B." + BaseUserContactEntity.FieldId;

// 不传时间 一条记录也不要给
if (!string.IsNullOrWhiteSpace(modifiedOn))
{
var dbParameters = new List<KeyValuePair<string, object>>(); //查询条件参数集合
commandText += " WHERE B." + BaseUserContactEntity.FieldModifiedOn + " > TO_DATE(" + this.DbHelper.GetParameter(BaseUserContactEntity.FieldModifiedOn + ",'yyyy-MM-dd HH24:mi:ss')");
// songbiao 做一下限制
commandText += " AND ROWNUM <= 300";
dbParameters.Add(
new KeyValuePair<string, object>(BaseUserContactEntity.FieldModifiedOn, modifiedOn));
return this.DbHelper.ExecuteReader(commandText, dbHelper.MakeParameters(dbParameters));
}

return null;
}

输出

DataReader转Dictionary数据类型之妙用

 

上面输出的是数据库字段,我们只需改下查询语句(field as modelfield),也可以输出实体属性。

可以有选择输出表中部分字段,这个方法加上好处很多。