using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
public class SqlHelper
{
//定义字段
private string connectionString;
/// <summary>
/// 设置数据库连接字符串
/// </summary>
public string ConnectionString
{
set { connectionString = value; }
}
/// <summary>
/// 空参的构造函数
/// </summary>
public SqlHelper()
{
//注意:须在web.config中配置"conn"的数据库连接字符串
connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
public SqlHelper(string connectionString)
{
this.connectionString = connectionString;//后面的是构造函数的参数
}
/// <summary>
/// 执行一个查询,并返回结果集
/// </summary>
/// <param name="sql">要执行的查询SQL文本命令</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql)
{
return ExecuteDataTable(sql, CommandType.Text, null);
}
/// <summary>
/// 执行一个查询,并返回查询结果
/// </summary>
/// <param name="sql">要执行的查询SQL语句</param>
/// <param name="type">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, CommandType type)
{
return ExecuteDataTable(sql, type, null);
}
/// <summary>
/// 执行一个查询,并返回结果集
/// </summary>
/// <param name="sql">要执行的查询SQL文本命令</param>
/// <param name="type">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <param name="param">Transact-SQL语句或存储过程的参数数组</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, CommandType type, SqlParameter[] param)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = type;
if (param != null)
{
foreach (SqlParameter par in param)
{
cmd.Parameters.Add(par);
}
}
//通过包含查询SQL的SqlCommand的实例来实例化SqlDataAdapter
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);//填充DataTable
}
}
return dt;
}
/// <summary>
/// 返回一个SqlDataReader对象的实例
/// </summary>
/// <param name="sql">要执行的查询SQL语句</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql)
{
return ExecuteReader(sql, CommandType.Text, null);
}
/// <summary>
/// 返回一个SqlDataReader对象的实例
/// </summary>
/// <param name="sql">要执行的查询SQL语句</param>
/// <param name="type">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql, CommandType type)
{
return ExecuteReader(sql, type, null);
}
/// <summary>
/// 返回一个SqlDataReader对象的实例
/// </summary>
/// <param name="sql">要执行的查询SQL文本命令</param>
/// <param name="type">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <param name="param">Transact-SQL语句或存储过程的参数数组</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql, CommandType type, SqlParameter[] param)
{
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(sql, conn);
if (param != null)
{
foreach (SqlParameter par in param)
{
cmd.Parameters.Add(par);
}
}
conn.Open();
//CommandBehavior.CloseConnection //参数提示关闭Reader对象时关闭与其关联的Connection对象
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 执行一个查询,返回查询结果集的第一行第一列,忽略其他行和列
/// </summary>
/// <param name="sql">要执行的查询SQL语句</param>
/// <returns></returns>
public Object ExecuteScalar(string sql)
{
return ExecuteScalar(sql, CommandType.Text, null);
}
/// <summary>
/// 执行一个查询,返回查询结果集的第一行第一列,忽略其他行和列
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <returns></returns>
public Object ExecuteScalar(string sql, CommandType type)
{
return ExecuteScalar(sql, type, null);
}
/// <summary>
/// 执行一个查询,返回查询结果集的第一行第一列,忽略其他行和列
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <param name="param"></param>
/// <returns></returns>
public Object ExecuteScalar(string sql, CommandType type, SqlParameter[] param)
{
object result = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = type;
if (param != null)
{
foreach (SqlParameter par in param)
{
cmd.Parameters.Add(par);
}
}
conn.Open();
result = cmd.ExecuteScalar();
}
}
return result;
}
/// <summary>
/// 对数据库执行增删改操作
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql, CommandType.Text, null);
}
/// <summary>
/// 对数据库执行增删改操作
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <returns></returns>
public int ExecteNonQuery(string sql, CommandType type)
{
return ExecuteNonQuery(sql, type, null);
}
/// <summary>
/// 对数据库执行增删改操作
/// </summary>
/// <param name="sql"></param>
/// <param name="type">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <param name="param">Transact-sql语句或者存储过程的参数数组</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType type, SqlParameter[] param)
{
int count = 0;
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = type;//设置cmd的CommandType为指定的type
if (param != null)
{
foreach (SqlParameter par in param)
{
cmd.Parameters.Add(par);
}
}
conn.Open();//打开数据库连接
count = cmd.ExecuteNonQuery();
}
}
return count;
}
/// <summary>
/// 返回当前连接的数据库所有由用户创建的数据库
/// </summary>
/// <returns></returns>
public DataTable GetTables()
{
DataTable dt = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
dt = conn.GetSchema("Tables");
}
return dt;
}
}
}