using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DataOperator
{
/// <summary>
/// 数据库操作基类
/// 实现对SQL数据库的各种操作
/// 创建者：王海利
/// 创建时间：2006-12-9
/// </summary>
public class SQLHelper
{
//获取Web.Config数据库连接字符串
private readonly string sqlConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
private SqlConnection conn; //创建SQL连接
private SqlDataAdapter adapter; //创建SQL数据适配器
private SqlDataReader dr; //创建SQL数据读取器
private SqlCommand cmd; //创建SQL命令对象
private SqlParameter param; //创建SQL参数
private DataSet ds; //创建数据集
private DataView dv; //创建视图
/// <summary>
/// 打开数据库连接
/// </summary>
public void Open()
{
#region
conn = new SqlConnection(sqlConnectionString);
conn.Open();
#endregion
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
#region
if (conn != null)
{
conn.Close();
conn.Dispose();
}
#endregion
}
/// <summary>
/// 返回DataSet数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
public DataSet GetDs(string strSql)
{
#region
Open();
adapter = new SqlDataAdapter(strSql, conn);
ds = new DataSet();
adapter.Fill(ds);
Close();
return ds;
#endregion
}
/// <summary>
/// 添加DataSet表
/// </summary>
/// <param name="ds">DataSet对象</param>
/// <param name="strSql">SQL语句</param>
/// <param name="strTableName">表名</param>
public void GetDs(DataSet ds, string strSql, string strTableName)
{
#region
Open();
adapter = new SqlDataAdapter(strSql, conn);
adapter.Fill(ds, strTableName);
Close();
#endregion
}
/// <summary>
/// 返回DataView数据视图
/// </summary>
/// <param name="strSql">SQL语句</param>
public DataView GetDv(string strSql)
{
#region
dv = GetDs(strSql).Tables[0].DefaultView;
return dv;
#endregion
}
/// <summary>
/// 获得DataTable对象
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public DataTable GetTable(string strSql)
{
#region
return GetDs(strSql).Tables[0];
#endregion
}
/// <summary>
/// 获得SqlDataReader对象 使用完须关闭DataReader,关闭数据库连接
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public SqlDataReader GetDataReader(string strSql)
{
#region
Open();
cmd = new SqlCommand(strSql, conn);
dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return dr;
#endregion
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="strSql"></param>
public void RunSql(string strSql)
{
#region
Open();
cmd = new SqlCommand(strSql, conn);
cmd.ExecuteNonQuery();
Close();
#endregion
}
/// <summary>
/// 执行SQL语句，并返回第一行第一列结果
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public string RunSqlReturn(string strSql)
{
#region
string strReturn = "";
Open();
try
{
cmd = new SqlCommand(strSql, conn);
strReturn = cmd.ExecuteScalar().ToString();
}
catch { }
Close();
return strReturn;
#endregion
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <returns>返回存储过程返回值</returns>
public int RunProc(string procName)
{
#region
cmd = CreateCommand(procName, null);
cmd.ExecuteNonQuery();
Close();
return (int)cmd.Parameters["ReturnValue"].Value;
#endregion
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <returns>返回存储过程返回值</returns>
public int RunProc(string procName, SqlParameter[] prams)
{
#region
cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
Close();
return (int)cmd.Parameters["ReturnValue"].Value;
#endregion
}
/// <summary>
/// 执行存储过程返回DataReader对象
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="dataReader">DataReader对象</param>
public void RunProc(string procName, SqlDataReader dataReader)
{
#region
cmd = CreateCommand(procName, null);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
#endregion
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <param name="dataReader">DataReader对象</param>
public void RunProc(string procName, SqlParameter[] prams, SqlDataReader dataReader)
{
#region
cmd = CreateCommand(procName, prams);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
#endregion
}
/// <summary>
/// 创建一个SqlCommand对象以此来执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <returns>返回SqlCommand对象</returns>
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
#region
// 确认打开连接
Open();
cmd = new SqlCommand(procName, conn);
cmd.CommandType = CommandType.StoredProcedure;
// 依次把参数传入存储过程
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// 加入返回参数
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return cmd;
#endregion
}
/// <summary>
/// 传入输入参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param></param>
/// <param name="Size">参数大小</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
#region
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
#endregion
}
/// <summary>
/// 传入返回值参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
#region
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
#endregion
}
/// <summary>
/// 生成存储过程参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <param name="Direction">参数方向</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
#region
if (Size > 0)
param = new SqlParameter(ParamName, DbType, Size);
else
param = new SqlParameter(ParamName, DbType);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
#endregion
}
}
}