using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace ItcastSIM.DAL
{
class SQLHelper
{
//读取配置文件中的连接字符串
private static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
/// <summary>
/// 执行查询 返回datatable
/// </summary>
/// <param name="sql">sql语句或存储过程</param>
/// <param name="ct">类型</param>
/// <param name="param">参数</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql, CommandType ct, params SqlParameter[] param)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sql,conn))
{
//设置cmd执行sql语句 还是存储过程
cmd.CommandType = ct;
//
cmd.Parameters.AddRange(param);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
}
return dt;
}
/// <summary>
/// 执行增删改
/// </summary>
/// <param name="sql">sql语句或存储过程</param>
/// <param name="ct">类型</param>
/// <param name="param">参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, CommandType ct, params SqlParameter[] param)
{
int r = -1;
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = ct;
cmd.Parameters.AddRange(param);
conn.Open();
r = cmd.ExecuteNonQuery();
}
}
return r;
}
/// <summary>
/// 执行查询 返回首行首列
/// </summary>
/// <param name="sql">sql语句或存储过程</param>
/// <param name="ct">类型</param>
/// <param name="param">参数</param>
/// <returns></returns>
public static object ExecuteScalar(string sql, CommandType ct, params SqlParameter[] param)
{
object o = null;
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = ct;
cmd.Parameters.AddRange(param);
conn.Open();
o = cmd.ExecuteScalar();
}
}
return o;
}
/// <summary>
/// 执行查询 返回DataReader
/// </summary>
/// <param name="sql">sql语句或存储过程</param>
/// <param name="ct">类型</param>
/// <param name="param">参数</param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string sql, CommandType ct, params SqlParameter[] param)
{
SqlConnection conn = new SqlConnection(connStr);
using (SqlCommand cmd = new SqlCommand(sql,conn))
{
cmd.CommandType = ct;
cmd.Parameters.AddRange(param);
conn.Open();
//当datareader关闭,对应的连接也就关闭
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
}
}
|