using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SqlHelper
{
/// <summary>
/// 封装Sql连接和查询方法
/// </summary>
public static class SqlHelper
{
//1.从app.config中获取连接字符串
private static readonly string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
//2.ExecuteNonQuery
/// <summary>
/// 执行增、删、改用到的方法
/// </summary>
/// <param name="sql">带参数的Sql语句</param>
/// <param name="cmdType">SqlCommand执行类型,CommandType.Text为SQL语句执行类型, CommandType.StoredProcedure为调用存储过程</param>
/// <param name="pms">可变的Sql参数数组</param>
/// <returns>返回影响行数</returns>
public static int ExecuteNonQuery(string sql,CommandType cmdType, params SqlParameter[] pms)
{
//2.1 创建SqlConnection 连接对象
using (SqlConnection conn = new SqlConnection(constr))
{
//2.2创建SqlCommand Sql执行对象
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
//设置执行类型
cmd.CommandType = cmdType;
//2.3加入参数
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
//2.4打开连接
conn.Open();
//2.5执行sql语句并返回值--影响行数
return cmd.ExecuteNonQuery();
}
}
}
//3.ExecuteScalar
/// <summary>
/// 执行查,返回单个值的方法
/// </summary>
/// <param name="sql">带参数的Sql语句</param>
/// <param name="cmdType">SqlCommand执行类型,CommandType.Text为SQL语句执行类型, CommandType.StoredProcedure为调用存储过程</param>
/// <param name="pms">可变的Sql参数数组</param>
/// <returns>返回单个值</returns>
public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] pms)
{
//3.1创建连接对象SqlConnection
using (SqlConnection conn = new SqlConnection(constr))
{
//3.2创建Sql执行对象SqlCommand
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
//设置执行类型
cmd.CommandType = cmdType;
//3.3加入参数
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
//3.4打开连接
conn.Open();
//3.5执行Sql语句并返回单个值
return cmd.ExecuteScalar();
}
}
}
//4.ExecuteReader
/// <summary>
/// 执行插,返回SqlDataReader,可以逐条读取数据
/// </summary>
/// <param name="sql">带参数的Sql语句</param>
/// <param name="cmdType">SqlCommand执行类型,CommandType.Text为SQL语句执行类型, CommandType.StoredProcedure为调用存储过程</param>
/// <param name="pms">可变的Sql参数数组</param>
/// <returns>返回结果集</returns>
public static SqlDataReader ExecuteReader(String sql, CommandType cmdType, params SqlParameter[] pms)
{
//3.1创建不自动关闭的连接对象
SqlConnection conn = new SqlConnection(constr);
//3.2创建自动关闭的SqlCommand
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
//设置执行类型
cmd.CommandType = cmdType;
//3.3加入参数
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
//3.4打开连接
conn.Open();
//3.5执行Sql语句并返回集合
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//数据库连接随着SqlDataReader的关闭而关闭
}
}
//5.ExecuteAdapter
/// <summary>
/// 加载结果集到内存中
/// </summary>
/// <param name="sql">带参数的Sql语句</param>
/// <param name="cmdType">SqlCommand执行类型,CommandType.Text为SQL语句执行类型, CommandType.StoredProcedure为调用存储过程</param>
/// <param name="pms">可变的Sql参数数组</param>
/// <returns>返回数据表DataTable</returns>
public static DataTable ExecuteAdapter(string sql, CommandType cmdType, params SqlParameter[] pms)
{
//创建DataTable
DataTable dt = new DataTable();
//5.1执行ExecuteAdapter
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))
{
//设置执行类型
adapter.SelectCommand.CommandType = cmdType;
//加入参数
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
//填充数据表DataTable
adapter.Fill(dt);
}
return dt;
}
}
}
简单SqlHelper
最新推荐文章于 2021-11-27 14:50:53 发布