DataReader转Dictionary数据类型之妙用

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

输出

 

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

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