SQL 基本语言使用

namespace DAL
{
    /// <summary>
    /// 数据层 - 数据库 操作类
    /// </summary>
    internal class DbHelperSQL
    {
        //获得配置文件的连接字符串
        public static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;


        #region 01.查询数据表 +static DataTable GetTabel(string strSql, params SqlParameter[] paras)
        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paras">参数数组</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string strSql, params SqlParameter[] paras)
        {
            DataTable dt = null;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                //创建 适配器对象（sql命令，连接通道）
                SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
                //添加参数
                da.SelectCommand.Parameters.AddRange(paras);
                //创建数据表对象
                dt = new DataTable(); 
                //适配器 读取数据库，并将查询的结果 装入程序的 dt里
                da.Fill(dt);
            }
            return dt;
        } 
        #endregion


        #region 02.执行 增删改 (非查询语句) +int ExcuteNonQuery(string strSql, params SqlParameter[] paras)
        /// <summary>
        /// 执行 增删改 (非查询语句)
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExcuteNonQuery(string strSql, params SqlParameter[] paras)
        {
            int res = -1;
            //创建连接通道
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                //创建命令对象（sql语句，连接通道）
                SqlCommand cmd = new SqlCommand(strSql, conn);
                //添加参数
                cmd.Parameters.AddRange(paras);
                conn.Open();
                res = cmd.ExecuteNonQuery();


                return res;
            }
        } 
        #endregion
        
        #region 02a.执行 多条增删改 (非查询语句) +int ExcuteNonQuery(string strSql, params SqlParameter[] paras)
        /// <summary>
        /// 执行 多条增删改 (非查询语句)
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExcuteNonQuerys(string[] strSqls, SqlParameter[][] paras2Arr)
        {
            int res = 0;
            //创建连接通道
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                //创建 事务
                SqlTransaction tran = conn.BeginTransaction();
                //创建命令对象
                SqlCommand cmd = new SqlCommand();
                //为命令对象指定连接通道
                cmd.Connection = conn;
                //为命令对象指定事务
                cmd.Transaction = tran;
                try
                {
                    //循环执行sql语句
                    for (int i = 0; i < strSqls.Length; i++)
                    {
                        //获得要执行的sql语句
                        string strSql = strSqls[i];
                        //为命令对象指定 此次执行的 sql语句
                        cmd.CommandText = strSql;
                        //添加参数
                        if (paras2Arr.Length > i)//如果 参数2维数组的长度大于当前循环的下标
                        {
                            cmd.Parameters.AddRange(paras2Arr[i]);//将 交错数组 的第一个元素（其实也是一个数组，添加到参数集合中）
                        }
                        res += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    tran.Commit();//提交事务
                }
                catch (Exception ex)
                {
                    res = 0;
                    tran.Rollback();//回滚事务
                    throw ex;
                }
            }
            return res;
        }
        #endregion


        #region 02.执行 查询单个值 +int ExcuteScalar(string strSql, params SqlParameter[] paras)
        /// <summary>
        /// 执行 增删改 (非查询语句)
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExcuteScalar(string strSql, params SqlParameter[] paras)
        {
            int res = -1;
            //创建连接通道
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                //创建命令对象（sql语句，连接通道）
                SqlCommand cmd = new SqlCommand(strSql, conn);
                //添加参数
                cmd.Parameters.AddRange(paras);
                conn.Open();
                res = Convert.ToInt32(cmd.ExecuteScalar());
            }
            return res;
        }
        #endregion


        #region 04.执行 特殊的 分页存储过程 +DataTable GetPageListByProc(int pageIndex, int pageSize,out int pageCount,out int rowCount)
        /// <summary>
        ///04.执行 特殊的 分页存储过程
        /// </summary>
        /// <param name="proName">存储过程名称</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="pageCount">总页数--输出</param>
        /// <param name="rowCount">总行数--输出</param>
        /// <returns></returns>
        public static DataTable GetPageListByProc(string proName,int pageIndex, int pageSize,bool isDel, out int pageCount, out int rowCount)
        {
            DataTable dt = new DataTable();
            //创建连接通道
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                //创建适配器对象
                SqlDataAdapter da = new SqlDataAdapter(proName, conn);
                //设置 命令类型 为存储过程
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                //设置 参数
                da.SelectCommand.Parameters.AddWithValue("@pageIndex", pageIndex);//当前页码
                da.SelectCommand.Parameters.AddWithValue("@pageSize", pageSize);//页容量
                da.SelectCommand.Parameters.AddWithValue("@isDel", isDel);//页容量
                
                //在存储过程中 输出参数
                da.SelectCommand.Parameters.Add(new SqlParameter("@pageCount", SqlDbType.Int));//总页数
                da.SelectCommand.Parameters.Add(new SqlParameter("@rowCount", SqlDbType.Int));//总行数
                //将后面两个参数 设置为 输出类型
                da.SelectCommand.Parameters[3].Direction = ParameterDirection.Output;
                da.SelectCommand.Parameters[4].Direction = ParameterDirection.Output;


                //执行 并将查询到的 结果 赋给 数据表对象
                da.Fill(dt);
                //获得 存储过程 返回的 输出参数
                pageCount = Convert.ToInt32(da.SelectCommand.Parameters[3].Value);
                rowCount = Convert.ToInt32(da.SelectCommand.Parameters[4].Value);
            }
            //返回数据表
            return dt;
        } 
        #endregion


        #region 04.执行 特殊的 分页存储过程 +DataTable GetPageListByProc(int pageIndex, int pageSize,out int pageCount,out int rowCount)
        /// <summary>
        ///04.执行 特殊的 分页存储过程
        /// </summary>
        /// <param name="proName">存储过程名称</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="userId">用户id</param>
        /// <param name="pageCount">总页数--输出</param>
        /// <param name="rowCount">总行数--输出</param>
        /// <returns></returns>
        public static int ExcuteNonQueryWithProc(string proName, params SqlParameter[] paras)
        {
            DataTable dt = new DataTable();
            //创建连接通道
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand(proName,conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(paras);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }
        #endregion


        #region 01.3为 对象数据源控件 提供 分页数据
        /// <summary>
        /// 01.3为 对象数据源控件 提供 分页数据
        /// </summary>
        /// <param name="pageSize">页容量</param>
        /// <param name="startRowIndex">起始行下标</param>
        /// <returns></returns>
        public static DataTable GetPagedListForObjectDataSource(int pageSize, int startRowIndex)
        {
            string strSql = "select * from(select Row_Number() over(order by cid) as num, * from Classes)as temp where num>@startRowIndex and num<=@size";
            SqlParameter[] paras = { 
                                   new SqlParameter("@startRowIndex",startRowIndex),
                                   new SqlParameter("@size",pageSize+startRowIndex)
                                   };
            return GetDataTable(strSql, paras);
        } 
        #endregion


        #region 3.执行查询多行语句 - 返回数据读取器  +static SqlDataReader ExcuteDataReader(string strSelectCmd, params SqlParameter[] paras)
        /// <summary>
        /// 执行查询多行语句 - 返回数据读取器
        /// </summary>
        /// <param name="strSelectCmd"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static SqlDataReader ExcuteDataReader(string strSelectCmd, params SqlParameter[] paras)
        {
            SqlConnection conn = null;
            try
            {
                //1.创建连接通道
                conn = new SqlConnection(strConn);
                //2.创建命令对象
                SqlCommand cmd = new SqlCommand(strSelectCmd, conn);
                //3.添加命令参数
                cmd.Parameters.AddRange(paras);
                //4.打开连接
                conn.Open();
                //5.创建读取器（当关闭此读取器时，会自动关闭连接通道）
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//当关闭此读取器时，会自动关闭连接通道
                //6.返回读取器
                return dr;
            }
            catch (Exception ex)
            {
                conn.Dispose();
                throw ex;
            }
        }
        #endregion


        #region  2.0升级泛型版 ------ 执行查询多行语句 - 返回数据表
        /// <summary>
        /// 2.0升级泛型版 ------ 执行查询多行语句 - 返回数据表
        /// </summary>
        /// <typeparam name="T2">泛型类型</typeparam>
        /// <param name="strSelectCmd">查询sql语句</param>
        /// <param name="paras">查询参数</param>
        /// <returns>泛型集合</returns>
        public static List<T2> ExcuteList<T2>(string strSelectCmd, params SqlParameter[] paras)
        {
            //1.创建连接通道
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                //2.创建适配器
                SqlDataAdapter da = new SqlDataAdapter(strSelectCmd, conn);
                //2.1设置查询命令的参数
                da.SelectCommand.Parameters.AddRange(paras);
                //3.数据表
                DataTable dt = new DataTable();
                //4.将数据查询并填充到数据表中
                da.Fill(dt);
                //5.将DataTable转成泛型集合List<T2>
                if (dt.Rows.Count > 0)
                {
                    //6.创建泛型集合对象
                    List<T2> list = new List<T2>();
                    //7.遍历数据行，将行数据存入 实体对象中，并添加到 泛型集合中list
                    foreach (DataRow row in dt.Rows)
                    {
                        //留言：等学完反射后再讲~~~~！
                        //7.1先获得泛型的类型(里面包含该类的所有信息---有什么属性啊，有什么方法啊，什么字段啊....................)
                        Type t = typeof(T2);
                        //7.2根据类型创建该类型的对象
                        T2 model = (T2)Activator.CreateInstance(t);// new MODEL.Classes()
                        //7.3根据类型 获得 该类型的 所有属性定义
                        PropertyInfo[] properties = t.GetProperties();
                        //7.4遍历属性数组
                        foreach (PropertyInfo p in properties)
                        {
                            //7.4.1获得属性名，作为列名
                            string colName = p.Name;
                            //7.4.2根据列名 获得当前循环行对应列的值
                            object colValue = row[colName];
                            //7.4.3将 列值 赋给 model对象的p属性
                            //model.ID=colValue;
                            p.SetValue(model, colValue, null);
                        }
                        //7.5将装好 了行数据的 实体对象 添加到 泛型集合中 O了！！！
                        list.Add(model);
                    }
                    return list;
                }
            }
            return null;
        }
        #endregion


        #region 6.查询结果集里的第一个单元格的值（单个值）-- 泛型版本 + static T ExcuteScalar<T>(string strSelectCmd, params SqlParameter[] paras)
        /// <summary>
        /// 查询结果集里的第一个单元格的值（单个值）-- 泛型版本
        /// </summary>
        /// <typeparam name="T">类型参数</typeparam>
        /// <param name="strSelectCmd"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static T ExcuteScalar<T>(string strSelectCmd, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand(strSelectCmd, conn);
                cmd.Parameters.AddRange(paras);
                conn.Open();
                object o = cmd.ExecuteScalar();
                return (T)Convert.ChangeType(o, typeof(T));
            }
        }
        #endregion


        #region 将数据表 转成对应 T2 类型的泛型集合对象
        /// <summary>
        /// 将数据表 转成对应 T2 类型的泛型集合对象
        /// </summary>
        /// <typeparam name="T2">泛型类型</typeparam>
        /// <returns>泛型集合</returns>
        public static List<T2> Table2List<T2>(DataTable dt)
        {
            //5.将DataTable转成泛型集合List<T2>
            if (dt.Rows.Count > 0)
            {
                //6.创建泛型集合对象
                List<T2> list = new List<T2>();
                //7.遍历数据行，将行数据存入 实体对象中，并添加到 泛型集合中list
                foreach (DataRow row in dt.Rows)
                {
                    //留言：等学完反射后再讲~~~~！
                    //7.1先获得泛型的类型(里面包含该类的所有信息---有什么属性啊，有什么方法啊，什么字段啊....................)
                    Type t = typeof(T2);
                    //7.2根据类型创建该类型的对象
                    T2 model = (T2)Activator.CreateInstance(t);// new MODEL.Classes()
                    //7.3根据类型 获得 该类型的 所有属性定义
                    PropertyInfo[] properties = t.GetProperties();
                    //7.4遍历属性数组
                    foreach (PropertyInfo p in properties)
                    {
                        //7.4.1获得属性名，作为列名
                        string colName = p.Name;
                        //7.4.2根据列名 获得当前循环行对应列的值
                        object colValue = row[colName];
                        //7.4.3将 列值 赋给 model对象的p属性
                        //model.ID=colValue;
                        p.SetValue(model, colValue, null);
                    }
                    //7.5将装好 了行数据的 实体对象 添加到 泛型集合中 O了！！！
                    list.Add(model);
                }
                return list;
            }
            return null;
        }
        #endregion


        public static void PrepareCommand(SqlCommand cmd, SqlConnection conn,SqlTransaction trans, CommandType type, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = type;
            if (trans != null) cmd.Transaction = trans;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }


        #region 7.0 通用的分页方法 +GetPagedList(string tbName, string keyName, int pageIndex, int pageSize, ref int rowCount, string where)
        /// <summary>
        /// 通用的分页方法
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="keyName">主键列名</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="rowCount">输出总行数</param>
        /// <param name="pageCount">输出总页数</param>
        /// <param name="where">查询条件</param>
        /// <returns></returns>
        public static DataTable GetPagedList(string tbName, string keyName, int pageIndex, int pageSize, ref int rowCount, ref int pageCount, string where = "", string innerJoin="")
        {
            pageIndex = pageIndex == 0 ? 1 : pageIndex;
            pageSize = pageSize == 0 ? 1 : pageSize;
            rowCount = rowCount == 0 ? 1 : rowCount;


            //1.创建连接通道
            SqlConnection conn = new SqlConnection(strConn);
            //2.创建命令对象，指定 执行通用 存储过程up_GetPagedData
            SqlCommand cmd = new SqlCommand("up_GetPagedData", conn);
            //2.1设置命令为存储过程类型
            cmd.CommandType = CommandType.StoredProcedure;
            //2.2为命令添加参数
            cmd.Parameters.Add(new SqlParameter("@tableName", tbName));
            cmd.Parameters.Add(new SqlParameter("@idName", keyName));
            cmd.Parameters.Add(new SqlParameter("@pageIndex", pageIndex));
            cmd.Parameters.Add(new SqlParameter("@pageSize", pageSize));


            cmd.Parameters.Add(new SqlParameter("@rowCount", rowCount));
            cmd.Parameters[4].Direction = ParameterDirection.Output;//因为rowCount在存储过程中会被输出，所以需要指定输出方向


            cmd.Parameters.Add(new SqlParameter("@where", where));
            cmd.Parameters.Add(new SqlParameter("@innerJoin", innerJoin));
            //3.创建 适配器（传入命令对象）
            //cmd.ExecuteReader
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //4.创建数据表对象
            DataTable dt = new DataTable();
            //5.适配器查询并状态数据表
            da.Fill(dt);
            //5.1将输出参数 赋值给 rowCount
            rowCount = Convert.ToInt32(cmd.Parameters[4].Value);
            //6.计算总页数
            pageCount = (int)Math.Ceiling(Convert.ToDouble(rowCount) / Convert.ToDouble(pageSize));


            //7.返回
            return dt;
        } 
        #endregion


        /*
         * @tableName nvarchar(100),--表名
            @idName nvarchar(50),--主键列名
            @pageIndex int = 1,--页码
            @pageSize int =10,--页容量
            @rowCount int output,--总行数
            @where varchar(200)--where条件
         */
    }
}