DataReader读取返回list

最新推荐文章于 2023-02-13 18:30:53 发布

转载最新推荐文章于 2023-02-13 18:30:53 发布 · 318 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/tanzhen/p/4494103.html

文章标签：

#python #c#

本文详细阐述了使用C#语言结合SQL语句进行数据库数据查询，并通过自定义方法将查询结果转换为特定数据结构的过程。

    public ActionResult Index()
        {
            string ConStr = ConfigurationManager.AppSettings["constr"];
            string sql = "select * from UserInfo";

            SqlConnection conn = new SqlConnection(ConStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            // IList<UserInfo> list = ToList<UserInfo>(reader);
            IList<UserInfo> list = new List<UserInfo>();
            //获取UserInfo的类型
            Type type = typeof(UserInfo);
            if (type.IsValueType || type == typeof(string))
            {
                //如果是值类型
                while (reader.Read())
                {
                    UserInfo local = (UserInfo)Convert.ChangeType(reader[0], type, null);
                    list.Add(local);
                }
            }
            else
            {
                //获取UserInfo的公共属性
                PropertyInfo[] properties = type.GetProperties();
                string name = string.Empty;
                while (reader.Read())
                {
                    //创建UserInfo实例
                    UserInfo local = Activator.CreateInstance<UserInfo>();
                    //如果reader的列名和属性名相等，则把它的值赋给该属性
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        name = reader.GetName(i);
                        foreach (PropertyInfo info in properties)
                        {
                            if (name.Equals(info.Name))
                            {
                                info.SetValue(local, Convert.ChangeType(reader[info
                                    .Name], info.PropertyType), null);
                                break;
                            }
                        }
                    }
                    //添加到list集合
                    list.Add(local);
                }
            }
            conn.Close();
            return View(list);
        }

        private IList<T> ToList<T>(SqlDataReader reader)
        {
            Type type = typeof(T);
            IList<T> list = null;
            if (type.IsValueType || type == typeof(string))
                list = CreateValue<T>(reader, type);
            else
                list = CreateObject<T>(reader, type);
            reader.Dispose();
            reader.Close();
            return list;
        }

        private IList<T> CreateObject<T>(SqlDataReader reader, Type type)
        {
            IList<T> list = new List<T>();
            PropertyInfo[] properties = type.GetProperties();
            string name = string.Empty;
            while (reader.Read())
            {
                T local = Activator.CreateInstance<T>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    name = reader.GetName(i);
                    foreach (PropertyInfo info in properties)
                    {
                        if (name.Equals(info.Name)) { info.SetValue(local, Convert.ChangeType(reader[info.Name], info.PropertyType), null); break; }
                    }
                }
                list.Add(local);
            }
            return list;
        }

        private IList<T> CreateValue<T>(SqlDataReader reader, Type type)
        {
            IList<T> list = new List<T>();
            while (reader.Read())
            {
                T local = (T)Convert.ChangeType(reader[0], type, null);
                list.Add(local);
            }
            return list;
        }

    }