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），也可以输出实体属性。

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