using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace Farm.DB
{
/// <summary>
/// 数据库访问类 DB
/// 无需实例化,静态访问
/// </summary>
public static class SqlHelper
{
//获取数据库连接字符串,其属于静态变量且只读,项目中所有文档可以直接使用,但不能修改
private static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnectionStrings"].ConnectionString;
private static SqlConnection conn = new SqlConnection();
private static int throwError = -1; //-1:提示错误 0:屏蔽错误 1:扔出错误
static SqlHelper()
{
throwError = 1;
}
/// <summary>
/// 错误处理
/// -1:提示错误
/// 0:屏蔽错误
/// 1:抛出错误
/// </summary>
public static int ThrowError
{
get { return throwError; }
set { throwError = value; }
}
/// <summary>
/// 打开数据库
/// </summary>
/// <param name="connectionString">连接字符串</param>
public static void Open(string connectionString)
{
if (conn.State != ConnectionState.Open)
{
try
{
conn.ConnectionString = connectionString;
conn.Open();
}
catch (SqlException exp)
{
if (throwError == -1)
{
System.Web.HttpContext.Current.Response.Write("错误,错误信息如下:/n" + exp.Message);
System.Web.HttpContext.Current.Response.End();
}
else if (throwError == 1)
{
throw exp;
}
}
}
}
/// <summary>
/// 打开数据库
/// </summary>
public static void Open()
{
Open(connectionString);
}
/// <summary>
/// 关闭数据库
/// </summary>
public static void Close()
{
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
catch (SqlException exp)
{
if (throwError == -1)
{
System.Web.HttpContext.Current.Response.Write("错误,错误信息如下:/n" + exp.Message);
System.Web.HttpContext.Current.Response.End();
}
else if (throwError == 1)
{
throw exp;
}
}
}
/// <summary>
/// 获取数DataTable数据表
/// </summary>
/// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="cmdParms">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>返回DataTable数据表</returns>
public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] cmdParms)
{
DataTable dt = new DataTable();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
catch (SqlException exp)
{
if (throwError == -1)
{
System.Web.HttpContext.Current.Response.Write("发生错误:" + exp.Message);
System.Web.HttpContext.Current.Response.End();
}
else if (throwError == 1)
{
throw exp;
}
}
return dt;
}
public static DataTable ExecuteDataTable(string cmdText,CommandType cmdType, params SqlParameter[] cmdParms)
{
DataTable dt = new DataTable();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
catch (SqlException exp)
{
if (throwError == -1)
{
System.Web.HttpContext.Current.Response.Write("发生错误:" + exp.Message);
System.Web.HttpContext.Current.Response.End();
}
else if (throwError == 1)
{
throw exp;
}
}
return dt;
}
/// <summary>
/// 调用视图
/// </summary>
/// <param name="cmdText">查询视图语句</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string cmdText)
{
DataTable dt = new DataTable();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
catch (SqlException exp)
{
if (throwError == -1)
{
System.Web.HttpContext.Current.Response.Write("发生错误:" + exp.Message);
System.Web.HttpContext.Current.Response.End();
}
else if (throwError == 1)
{
throw exp;
}
}
return dt;
}
/// <summary>
/// 通过sql语句获取SqlDataReader对象
/// </summary>
/// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="cmdParms">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>SqlDataReader对象</returns>
public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] cmdParms)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}
catch (SqlException exp)
{
if (throwError == -1)
{
System.Web.HttpContext.Current.Response.Write("发生错误:" + exp.Message);
System.Web.HttpContext.Current.Response.End();
}
else if (throwError == 1)
{
throw exp;
}
return null;
}
}
//add by hry
public static SqlDataReader ExecuteReader(string cmdText,CommandType cmdType, params SqlParameter[] cmdParms)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}
catch (SqlException exp)
{
if (throwError == -1)
{
System.Web.HttpContext.Current.Response.Write("发生错误:" + exp.Message);
System.Web.HttpContext.Current.Response.End();
}
else if (throwError == 1)
{
throw exp;
}
return null;
}
}
/// <summary>
/// 通过sql语句返回第一行第一列
/// </summary>
/// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="cmdParms">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>返回的列</returns>
public static object ExecuteScalar(string cmdText, params SqlParameter[] cmdParms)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
return cmd.ExecuteScalar();
}
catch (SqlException exp)
{
if (throwError == -1)
{
System.Web.HttpContext.Current.Response.Write("发生错误:" + exp.Message);
System.Web.HttpContext.Current.Response.End();
}
else if (throwError == 1)
{
throw exp;
}
return 0;
}
}
//add By hanglyang
public static object ExecuteScalar(string cmdText,CommandType cmdType, params SqlParameter[] cmdParms)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
return cmd.ExecuteScalar();
}
catch (SqlException exp)
{
if (throwError == -1)
{
System.Web.HttpContext.Current.Response.Write("发生错误:" + exp.Message);
System.Web.HttpContext.Current.Response.End();
}
else if (throwError == 1)
{
throw exp;
}
return 0;
}
}
/// <summary>
/// 通过sql语句执行增、删、改命令
/// </summary>
/// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="cmdParms">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(string cmdText, params SqlParameter[] cmdParms)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
int ret = cmd.ExecuteNonQuery();
return ret;
}
catch (SqlException exp)
{
if (throwError == -1)
{
System.Web.HttpContext.Current.Response.Write("发生错误:" + exp.Message);
System.Web.HttpContext.Current.Response.End();
}
else if (throwError == 1)
{
throw exp;
}
return 0;
}
}
//add lyang
public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
int ret = cmd.ExecuteNonQuery();
return ret;
}
catch (SqlException exp)
{
if (throwError == -1)
{
System.Web.HttpContext.Current.Response.Write("发生错误:" + exp.Message);
System.Web.HttpContext.Current.Response.End();
}
else if (throwError == 1)
{
throw exp;
}
return 0;
}
}
}
}