asp.net --数据访问通用类

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

using System.Reflection;
using System.Data;

namespace CommonDBAccess.library
{
    public class DataAccess
    {
        public static string connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
        private static SqlConnection conn = null;
        private static SqlCommand cmd = null;
        private static SqlDataReader reader = null;
        private static SqlDataAdapter adapter = null;

        //==============================================================生成连接属性
        public static SqlConnection Conn
        {
            get
            {
                if (conn == null)
                {
                    conn = new SqlConnection(connectionString);
                    conn.Open();
                }
                else if (conn.State == System.Data.ConnectionState.Closed)
                {
                    conn = new SqlConnection(connectionString);
                    conn.Open();
                }
                else if (conn.State == System.Data.ConnectionState.Broken)
                {
                    conn.Close();
                    conn = new SqlConnection(connectionString);
                    conn.Open();

                }
                return conn;

            }
        }//

        //============================================================
        //以下为一组ado.net扣作数据库的方法
        /// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql)
        {
            cmd = new SqlCommand(sql, Conn);
            int rows = cmd.ExecuteNonQuery();
            return rows;

        }

        /// <summary>
        /// 返回第一行第一列的值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql)
        {
            cmd = new SqlCommand(sql, Conn);
            object obj = cmd.ExecuteScalar();
            return obj;

        }
        /// <summary>
        /// 返回一个reader,调用此函数之后，一定要记得关闭reader
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql)
        {
            cmd = new SqlCommand(sql, Conn);
            SqlDataReader reader = null;
            cmd.ExecuteReader();

            return reader;
        }

        public static DataSet FillDataSet(string sql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Conn);
            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);
            return ds;
        }



        //===================================================================
        //以下为一组由实体操作数据库的主法

        //生成sql查询条件
        public string CreateSqlCondition<T>(T model) where T : class,new()
        {
            string sql = "";

            T t = new T();
            //第一步：得到实体类型中的所有属性
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties();

            //第二步：遍历实体类型中的所有属性
            foreach (PropertyInfo p in properties)
            {
                //
                object obj = p.GetValue(model, null);
                if (obj == null) continue;


                //如果属性类型为int
                if (p.PropertyType.Equals(typeof(int)))
                {
                    if (Convert.ToInt32(obj) == 0) continue;
                    sql += " and " + p.Name + "=" + p.GetValue(model, null).ToString();
                }



                //如果属性类型为string
                if (p.PropertyType.Equals(typeof(string)))
                {
                    if (p.GetValue(model, null).ToString().Length > 0)
                    {
                        sql += " and " + p.Name + " like '%" + p.GetValue(model, null).ToString() + "%'";
                    }
                }

                //如果属性类型为DateTime
                if (p.PropertyType.Equals(typeof(DateTime)))
                {
                    continue;

                }

            }
            return sql;
        }

        //==================================================================================
        /// <summary>
        /// 生成sql查询语句,假如users{id,userName,userPass},得到字符串select id,userName,userPass from users;
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        public static string CreateSelectSql<T>() where T : class,new()
        {
            string sql = "select ";

            T t = new T();

            //=====================================================得到表名
            string s = t.ToString();
            int begin = t.ToString().LastIndexOf(".") + 1;
            int len = t.ToString().Length;
            string table_name = t.ToString().Substring(begin);


            //第一步：得到实体类型中的所有属性
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties();



            //第二步：遍历实体类型中的所有属性
            foreach (PropertyInfo p in properties)
            {

                //如果属性类型为int
                if (p.PropertyType.Equals(typeof(int)))
                {
                    sql += p.Name;
                    sql += ",";

                }


                //如果属性类型为string
                if (p.PropertyType.Equals(typeof(string)))
                {
                    sql += p.Name;
                    sql += ",";
                }

                //如果属性类型为DateTime
                if (p.PropertyType.Equals(typeof(DateTime)))
                {
                    sql += p.Name;
                    sql += ",";
                }

            }
            sql = sql.Substring(0, sql.Length - 1);//把最后一个逗号去掉
            sql += " from " + table_name;



            return sql;
        }

        public static string CreateSelectSql<T>(int top,string strWhere,string fieldOrder) where T : class,new()
        {
            string sql = "select top "+top+" ";

            T t = new T();

            //=====================================================得到表名
            string s = t.ToString();
            int begin = t.ToString().LastIndexOf(".") + 1;
            int len = t.ToString().Length;
            string table_name = t.ToString().Substring(begin);


            //第一步：得到实体类型中的所有属性
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties();



            //第二步：遍历实体类型中的所有属性
            foreach (PropertyInfo p in properties)
            {

                //如果属性类型为int
                if (p.PropertyType.Equals(typeof(int)))
                {
                    sql += p.Name;
                    sql += ",";

                }


                //如果属性类型为string
                if (p.PropertyType.Equals(typeof(string)))
                {
                    sql += p.Name;
                    sql += ",";
                }

                //如果属性类型为DateTime
                if (p.PropertyType.Equals(typeof(DateTime)))
                {
                    sql += p.Name;
                    sql += ",";
                }

            }
            sql = sql.Substring(0, sql.Length - 1);//把最后一个逗号去掉
            sql += " from " + table_name;
            if(strWhere!="")sql += strWhere;
            if(fieldOrder!="")sql += fieldOrder;

            return sql;
        }
        //===================================================================生成删除sql
        public static string CreateDeleteSql<T>(T model) where T : class,new()
        {
            string sql = "delete from ";

            T t = new T();

            //----------------------------------------------------得到表名
            string s = t.ToString();
            int begin = t.ToString().LastIndexOf(".") + 1;
            int len = t.ToString().Length;
            string table_name = t.ToString().Substring(begin);
            sql += table_name;

            //---------------------------------------------------
            //第一步：得到实体类型中的所有属性
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties();



            //第二步：遍历实体类型中的所有属性
            string strWhere = "";
            foreach (PropertyInfo p in properties)
            {
                object obj = p.GetValue(model, null);
                if (obj == null) continue;


                //如果属性类型为int
                if (p.PropertyType.Equals(typeof(int)))
                {
                    if (Convert.ToInt32(obj) == 0) continue;
                    if (strWhere == "")
                    {
                        strWhere += p.Name + "=" + p.GetValue(model, null).ToString();
                    }
                    else
                    {
                        strWhere += " and " + p.Name + "=" + p.GetValue(model, null).ToString();
                    }

                }



                //如果属性类型为string
                if (p.PropertyType.Equals(typeof(string)))
                {
                    if (p.GetValue(model, null).ToString().Length > 0)
                    {
                        if (strWhere == "")
                        {
                            strWhere += p.Name + " like '%" + p.GetValue(model, null).ToString() + "%'";
                        }
                        else
                        {
                            strWhere += " and " + p.Name + " like '%" + p.GetValue(model, null).ToString() + "%'";

                        }
                    }
                }

                //如果属性类型为DateTime
                if (p.PropertyType.Equals(typeof(DateTime)))
                {

                    if (strWhere == "")
                    {
                        strWhere += p.Name + "=" + p.GetValue(model, null).ToString();
                    }
                    else
                    {
                        strWhere += " and " + p.Name + "=" + p.GetValue(model, null).ToString();

                    }
                }

            }

            //---------------------
            if (strWhere != "")
            {
                sql += " where " + strWhere;
            }



            return sql;
        }


        //==================================================================生成更新sql
        public static string CreateUpdateSql<T>(T model, string strWhere) where T : class,new()
        {
            string update_sql = "update ";


            T t = new T();

            //----------------------------------------------------得到表名
            string s = t.ToString();
            int begin = t.ToString().LastIndexOf(".") + 1;
            int len = t.ToString().Length;
            string table_name = t.ToString().Substring(begin);
            update_sql += table_name;
            update_sql += " set ";//到此为止，生成的sql为:update users set

            //-----------------------------------------------------
            //第一步：得到实体类型中的所有属性
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties();



            //第二步：遍历实体类型中的所有属性
            string str = "";
            foreach (PropertyInfo p in properties)
            {
                object obj = p.GetValue(model, null);
                if (obj == null) continue;


                //如果属性类型为int
                if (p.PropertyType.Equals(typeof(int)))
                {
                    if (Convert.ToInt32(obj) == 0) continue;
                    if (str == "")
                    {
                        str += p.Name + "=" + p.GetValue(model, null).ToString();
                    }
                    else
                    {
                        str += " , " + p.Name + "=" + p.GetValue(model, null).ToString();
                    }

                }



                //如果属性类型为string
                if (p.PropertyType.Equals(typeof(string)))
                {
                    if (p.GetValue(model, null).ToString().Length > 0)
                    {
                        if (str == "")
                        {
                            str += p.Name + "='" + p.GetValue(model, null).ToString() + "'";
                        }
                        else
                        {
                            str += " , " + p.Name + "='" + p.GetValue(model, null).ToString() + "'";

                        }
                    }
                }

                //如果属性类型为DateTime
                if (p.PropertyType.Equals(typeof(DateTime)))
                {

                    if (str == "")
                    {
                        str += p.Name + "=" + p.GetValue(model, null).ToString();
                    }
                    else
                    {
                        str += " , " + p.Name + "=" + p.GetValue(model, null).ToString();

                    }
                }

            }

            //---------------------
            update_sql += str;


            update_sql += " where " + strWhere;


            return update_sql;
        }


        //==================================================================生成插入sql
        /// <summary>
        /// 生成插入sql,注意不要把自增字段传入到model中,否则会出错
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public static string CreateInsertSql<T>(T model) where T : class,new()
        {
            string insert_sql = "insert into ";
            T t = new T();
            //----------------------------------------------------得到表名
            string s = t.ToString();
            int begin = t.ToString().LastIndexOf(".") + 1;
            int len = t.ToString().Length;
            string table_name = t.ToString().Substring(begin);
            insert_sql += table_name;//到此为止，生成的sql为:insert into users


            //-----------------------------------------------------
            //第一步：得到实体类型中的所有属性

            string str_key = "";
            string str_value = "";
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties();
            foreach (PropertyInfo p in properties)
            {
                object obj = p.GetValue(model, null);
                if (obj == null) continue;

                //如果属性类型为int
                if (p.PropertyType.Equals(typeof(int)))
                {
                    if (Convert.ToInt32(obj) > 0)
                    {
                        if (str_key == "")
                        {
                            str_key += p.Name;
                            str_value += Convert.ToInt32(obj);
                        }
                        else
                        {
                            str_key += "," + p.Name;
                            str_value += "," + Convert.ToInt32(obj);
                        }

                    }
                }


                //如果属性类型为string
                if (p.PropertyType.Equals(typeof(string)))
                {
                    if (obj.ToString().Length > 0)
                    {
                        if (str_key == "")
                        {
                            str_key += p.Name;
                            str_value += "'" + obj.ToString() + "'";
                        }
                        else
                        {
                            str_key += "," + p.Name;
                            str_value += "," + "'" + obj.ToString() + "'";
                        }
                    }

                }

                //如果属性类型为DateTime
                if (p.PropertyType.Equals(typeof(DateTime)))
                {
                    if (obj.ToString().Length > 0)
                    {
                        if (str_key == "")
                        {
                            str_key += p.Name;
                            str_value += obj.ToString();
                        }
                        else
                        {
                            str_key += "," + p.Name;
                            str_value += "'," + obj.ToString() + "'";
                        }
                    }
                }

            }

            insert_sql += " ( " + str_key + " )";
            insert_sql += " values ";
            insert_sql += " (" + str_value + " )";
            insert_sql += ";select @@IDENTITY";


            return insert_sql;
        }
        //===================================================================


        //===================================================================
        //以下为数据库的通用操作

        public static DataSet GetDataSet<T>() where T : class,new()
        {
            DataSet ds = null;
            string sql = CreateSelectSql<T>();
            ds = FillDataSet(sql);
            return ds;
        }

        public static DataSet GetDataSet<T>(int top,string strWhere,string fieldOrder) where T : class,new()
        {
            DataSet ds = null;
            string sql = CreateSelectSql<T>(top,strWhere,fieldOrder);
            ds = FillDataSet(sql);
            return ds;
        }


        public static int Delete<T>(T model) where T : class,new()
        {
            string delete_sql = CreateDeleteSql<T>(model);
            int rows = ExecuteCommand(delete_sql);
            return rows;
        }

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="int_pk">主键（整型)</param>
        /// <returns></returns>
        public static int Update<T>(T model, string strWhere) where T : class,new()
        {
            int rows = 0;
            string update_sql = CreateUpdateSql<T>(model, strWhere);
            rows = ExecuteCommand(update_sql);
            return rows;
        }


        public static int Add<T>(T model) where T : class,new()
        {
            int id = 0;
            string insert_sql = CreateInsertSql<T>(model);
            id = Convert.ToInt32(ExecuteScalar(insert_sql));
            return id;
        }

        //===========================================================
        public static List<T> DataTableToList<T>(DataTable table) where T : class,new()
        {
            T t = new T();


            List<T> model_list = new List<T>();
            int rowsCount = table.Rows.Count;
            if (rowsCount > 0)
            {
                T model;
                for (int i = 0; i < rowsCount; i++)
                {
                    //model = new T();
                    model = CreateModel<T>(table.Rows[i]);//这个很关键
                    model_list.Add(model);
                }
            }
            return model_list;

        }//

        public static List<T> GetAllList<T>() where T : class,new()
        {
            DataSet ds = GetDataSet<T>();
            List<T> list = DataTableToList<T>(ds.Tables[0]);

            return list;
        }

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="top">前几行</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="fieldOrder">查询排序字段</param>
        /// <returns></returns>
        public static List<T> GetList<T>(int top,string strWhere,string fieldOrder) where T : class,new()
        {
            DataSet ds = GetDataSet<T>(top,strWhere,fieldOrder);
            List<T> list = DataTableToList<T>(ds.Tables[0]);

            return list;
        }

        /// <summary>
        /// 得到分页数据列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="strWhere"></param>
        /// <param name="fieldOrder"></param>
        /// <returns></returns>
        public static List<T> GetPageList<T>(int pageIndex,int pageSize, string strWhere, string fieldOrder) where T : class,new()
        {//没有实现出来，不知道表中主键，一般还是要以参数的形式传出来
            //select top 10 * from users where id not in (select top ((1-1))*10 id from users)
            

            DataSet ds = GetDataSet<T>(pageSize, strWhere, fieldOrder);
            List<T> list = DataTableToList<T>(ds.Tables[0]);

            return list;
        }


        public static T CreateModel<T>(DataRow row) where T : class,new()
        {
            //第一步：根据模型反射，得到字段名
            T t = new T();
            
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties();
            foreach (PropertyInfo p in properties)
            {
                p.SetValue(t, DBFieldValueToObject(row[p.Name]), null);
            }
            return t;
        }//

        private static object DBFieldValueToObject(object obj)
        {
            return obj == System.DBNull.Value ? null : obj;
        }

      



    }
}