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

namespace DAL
{
/// <summary>
/// 数据层 - 数据库 操作类
/// </summary>
internal class SqlHelper

{
//获得配置文件的连接字符串
public readonly static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

#region 01.查询数据表 static DataTable ExecuteDataTable(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)
{
//创建数据库连接对象
SqlConnection conn = new SqlConnection(strConn);
//创建适配器对象（sql命令，连接通道）
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
//为适配器添加参数
da.SelectCommand.Parameters.AddRange(paras);
//创建数据表对象
DataTable dt = new DataTable();
//适配器 读取数据库，并将查询的结果 装入程序的 dt里
da.Fill(dt);
//返回数据表
return dt;
}
#endregion

#region 02.执行非查询语句 int ExcuteNonQuery(string strSql, params SqlParameter[] paras)
/// <summary>
/// 执行非查询语句)
/// </summary>
/// <param name="strSql">sql语句</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();
//执行非查询SQL命令
res = cmd.ExecuteNonQuery();
}
return res;
}
#endregion

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

#region 04.执行Scalar查询单个值 public static int ExcuteScalar(string strSql, params SqlParameter[] paras)
/// <summary>
/// 执行Scalar查询单个值
/// </summary>
/// <param name="strSql">sql语句</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();
//将查询结果转换为int类型并返回
res = Convert.ToInt32(cmd.ExecuteScalar());
}
return res;
}
#endregion

#region 05.执行分页存储过程 public static DataTable GetPageList(int pageIndex, int pageSize, out int pageCount, out int rowCount)
/// <summary>
///执行特殊的分页存储过程
/// </summary>
/// <param name="userID">用户ID</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页容量</param>
/// <param name="pageCount">总页数--输出</param>
/// <param name="rowCount">总行数--输出</param>
/// <returns></returns>
public static DataTable GetPageListByProc(int userID, int pageIndex, int pageSize, out int rowCount, out int pageCount)
{
DataTable dt = new DataTable();
//创建连接通道
using (SqlConnection conn = new SqlConnection(strConn))
{
//创建适配器对象
SqlDataAdapter da = new SqlDataAdapter("GetPageList", conn);
//设置 命令类型 为存储过程
da.SelectCommand.CommandType = CommandType.StoredProcedure;
//设置 参数
da.SelectCommand.Parameters.AddWithValue("@pageIndex", pageIndex);//当前页码
da.SelectCommand.Parameters.AddWithValue("@pageSize", pageSize);//页容量
da.SelectCommand.Parameters.AddWithValue("@userID", userID); //用户ID

//在存储过程中 输出参数
da.SelectCommand.Parameters.Add(new SqlParameter("@rowCount", SqlDbType.Int));
da.SelectCommand.Parameters.Add(new SqlParameter("@pageCount", SqlDbType.Int));
//将后面两个参数 设置为 输出类型
da.SelectCommand.Parameters[3].Direction = ParameterDirection.Output;
da.SelectCommand.Parameters[4].Direction = ParameterDirection.Output;
//执行 并将查询到的 结果 赋给 数据表对象
da.Fill(dt);
//获得 存储过程 返回的 输出参数
rowCount = Convert.ToInt32(da.SelectCommand.Parameters[3].Value);
pageCount = Convert.ToInt32(da.SelectCommand.Parameters[4].Value);
}
//返回数据表
return dt;
}
#endregion

#region 06.执行存储过程 public static DataTable ExecuteProcedure(string procedure, params SqlParameter[] paras)
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procedure">存储过程名称</param>
/// <param name="param">参数数组</param>
/// <returns>数据表</returns>
public static DataTable ExecuteProcedure(string procedure, params SqlParameter[] paras)
{
//创建连接通道
SqlConnection conn = new SqlConnection(strConn);
//创建适配器对象（sql命令，连接通道）
SqlDataAdapter da = new SqlDataAdapter(procedure, conn);
//设置适配器的命令类型为存储过程
da.SelectCommand.CommandType = CommandType.StoredProcedure;
//为适配器添加参数
da.SelectCommand.Parameters.AddRange(paras);
//创建数据表对象
DataTable dt = new DataTable();
//填充数据集
da.Fill(dt);
//返回数据表
return dt;
}
#endregion

/***********************分页存储过程SQL代码***********************
CREATE procedure usp_GetPageList
@PageIndex int = 1,
@PageSize int = 10,
@PageCount int output,
@RowCount int output
as
begin
select * from (Select ROW_NUMBER() over(order by ID) as num, * from Student) as temp where num between (@pageIndex-1) * @pageSize + 1 and @pageSize + 1
select @RowCount = COUNT(*) from Student
set @PageCount = Ceiling(@RowCount/@PageSize)
end
*****************************************************************/
}