using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
public class SQLhepler
{
private SqlConnection connection = null;
private SqlCommand comm = null;
private SqlDataReader der = null;
public SQLhepler()
{
string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
connection = new SqlConnection(connstr);
}
private SqlConnection getconn()
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
return connection;
}
/// <summary>
/// 执行不带参数的sql增删改语句或存储过程
/// </summary>
/// <param name="cmdtext">增删改sql或存储过程</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public int ExecuteNonQuery(string cmdtext, CommandType ct)
{
int ints;
try
{
comm = new SqlCommand(cmdtext, getconn());
comm.CommandType = ct;
ints = comm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return ints;
}
/// <summary>
/// 执行不带参数的sql查询语言或存储过程
/// </summary>
/// <param name="cmdtext">sql查询语言或存储过程</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public DataTable ExecuteQuery(string cmdtext, CommandType ct)
{
DataTable dt = new DataTable();
comm = new SqlCommand(cmdtext, getconn());
comm.CommandType = ct;
using (der = comm.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(der);
}
return dt;
}
/// <summary>
/// 执行带参数的sql增删改语句或存储过程
/// </summary>
/// <param name="cmdtext">sql增删改语句或存储过程</param>
/// <param name="para">参数集合</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public int ExecuteNonQuery(string cmdtext, SqlParameter[] para, CommandType ct)
{
int ints;
using (comm = new SqlCommand(cmdtext, getconn()))
{
comm.CommandType = ct;
comm.Parameters.AddRange(para);
ints = comm.ExecuteNonQuery();
}
return ints;
}
/// <summary>
/// 执行带参数的sql查询语句或存储过程;
/// </summary>
/// <param name="cmdtext"></param>
/// <param name="para"></param>
/// <param name="ct"></param>
/// <returns></returns>
public DataTable ExecuteQuery(string cmdtext, SqlParameter[] para, CommandType ct)
{
DataTable dt = new DataTable();
comm = new SqlCommand(cmdtext, getconn());
comm.CommandType = ct;
comm.Parameters.AddRange(para);
using (der = comm.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(der);
}
return dt;
}
}
}