using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace TMDAL
{
static class DBHelper
{
//创建连接对象
public static SqlConnection CreateConn()
{
//读取app.config文件内的链接字符串
string strConn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
SqlConnection conn = new SqlConnection(strConn);
return conn;
}
//增、删、改
public static int ExecuteNonQuery(string sql,SqlParameter[] pars)
{
using(SqlConnection conn = CreateConn())
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
int count = cmd.ExecuteNonQuery();
conn.Close();
return count;
}
}
//单值查询
public static object ExecuteScalar(string sql,SqlParameter[] pars)
{
using (SqlConnection conn = CreateConn())
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
object obj = cmd.ExecuteScalar();
conn.Close();
return obj;
}
}
//多值查询:返回一行数据
public static SqlDataReader ExecuteReader(string sql,SqlParameter[] pars)
{
SqlConnection conn = CreateConn();
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
//判断参数数组是否为空
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
//多值查询:返回多行数据
public static DataSet QueryAll(string sql, SqlParameter[] pars)
{
using (SqlConnection conn = CreateConn())
{
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
if (pars != null)
{
sda.SelectCommand.Parameters.AddRange(pars);
}
DataSet ds = new DataSet();
sda.Fill(ds, "temp");
return ds;
}
}
//执行存储过程
public static SqlDataReader ExecuteProc(string procName,SqlParameter[] pars)
{
SqlConnection conn = CreateConn();
conn.Open();
SqlCommand cmd = new SqlCommand(procName,conn);
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader sdr = cmd.ExecuteReader();
return sdr;
}
}
}