using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;
namespace Tskill.Helper
{
public class SqlHelper
{
private SqlConnection conn = null;
private SqlCommand com = null;
private SqlDataReader sdr = null;
/// <summary>
/// 重构SqlHelper
/// </summary>
public SqlHelper()
{
string connStr = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
if (conn == null)
{
conn = new SqlConnection(connStr); //初始化SqlConnection
}
}
/// <summary>
/// 获取数据库连接:当数据库连接关闭时,打开连接;再返回连接
/// </summary>
/// <returns>返回SqlConnection</returns>
private SqlConnection GetConn()
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn;
}
//打开数据库
public void Open()
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
//关闭数据库
public void Close()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
/// <summary>
/// 该方法执行传入的SQL增、删、改语句
/// </summary>
/// <param name="sqlString">要执行的SQL语句</param>
/// <returns>返回更新的记录数</returns>
public int ExecuteNonQuery(string sqlString)
{
int res = 0;
using (com = new SqlCommand(sqlString, GetConn()))
{
res = com.ExecuteNonQuery();
conn.Close();
}
return res;
}
/// <summary>
/// 该方法执行传入的SQL增删改语句
/// </summary>
/// <param name="sqlString">要执行的SQL语句</param>
/// <returns>返回更新的记录数</returns>
public int ExecuteNonQuery(string sqlString, List<SqlParameter> listPar)
{
int res; //如果定义为0,后面判断就要注意了
using (com = new SqlCommand(sqlString, GetConn()))
{
com.Parameters.AddRange(listPar.ToArray());
res = com.ExecuteNonQuery();
conn.Close();
}
return res;
}
/// <summary>
/// 该方法执行传入的SQL查询语句
/// </summary>
/// <param name="sqlString">SQL查询语句</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader ExecuteReader(string sqlString)
{
using (com = new SqlCommand(sqlString, GetConn()))
{
sdr = com.ExecuteReader();
}
return sdr;
}
/// <summary>
/// 该方法执行传入的SQL查询语句
/// </summary>
/// <param name="sqlString">SQL查询语句</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader ExecuteReader(string sqlString, List<SqlParameter> listPar)
{
using (com = new SqlCommand(sqlString, GetConn()))
{
com.Parameters.AddRange(listPar.ToArray());
sdr = com.ExecuteReader();
}
return sdr;
}
/// <summary>
/// 该方法执行传入的SQL查询语句
/// </summary>
/// <param name="sqlString">SQL查询语句</param>
/// <returns>DataTable</returns>
public DataTable DtReader(string sqlString)
{
DataTable dt = new DataTable();
com = new SqlCommand(sqlString, GetConn());
using (sdr = com.ExecuteReader(CommandBehavior.CloseConnection)) //如果关闭sdr连接,关联的conn连接也将关闭
{
dt.Load(sdr);
}
return dt;
}
/// <summary>
/// 该方法执行传入的SQL查询语句
/// </summary>
/// <param name="sqlString">SQL查询语句</param>
/// <returns>DataTable</returns>
public DataSet dsReader(string sql)
{
DataSet ds = new DataSet();
using (SqlDataAdapter sqldap = new SqlDataAdapter(sql, GetConn()))//数据适配器 就是一个类 ado.net 提供的 我们把.net 平台连库的方式称之为 ado.net "他可以动态添加 参数"
{
sqldap.Fill(ds);
Close();
}
return ds;
}
}
}