using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Specialized;
using System.Configuration;
namespace IRain.Rheleven.Components
{
/// <summary>
/// SqlDabaProvider 的摘要说明。
/// </summary>
public class SqlDataProvider : IDisposable
{
public SqlDataProvider()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
#region Sql 连接
private string connectionString = ConfigurationSettings.AppSettings["ConnectionString"] ;
public string ConnectionString
{
get { return connectionString; }
set { connectionString = value; }
}
private SqlConnection GetSqlConnection()
{
try
{ return new SqlConnection(connectionString); }
catch
{
throw new ArgumentNullException( "The SqlServer Is Not Valid" );
}
}
#endregion
#region 运行存储过程
/// <summary>
/// 运行存储过程 返回存储过程返回值
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <returns>存储过程返回值</returns>
public int RunProcNonQueryReturn(string procedureName)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateCommand(sqlConnection, procedureName, null);
cmd.ExecuteNonQuery() ;
sqlConnection.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
}
}
/// <summary>
/// 运行存储过程 返回存储过程返回值
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="parameter">存储过程参数</param>
/// <returns>存储过程返回值</returns>
public int RunProcNonQueryReturn(string procedureName, SqlParameter[] parameter)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateCommand(sqlConnection, procedureName, parameter);
cmd.ExecuteNonQuery() ;
sqlConnection.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
}
}
public void RunProcNonQuery(string procedureName)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateCommand(sqlConnection, procedureName, null);
cmd.ExecuteNonQuery() ;
sqlConnection.Close();
}
}
/// <summary>
/// 运行存储过程 获取存储过程输出参数值
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="parameter">存储过程参数</param>
public void RunProcNonQuery(string procedureName, SqlParameter[] parameter)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateCommand(sqlConnection, procedureName, parameter);
cmd.ExecuteNonQuery() ;
sqlConnection.Close();
}
}
/// <summary>
/// 运行存储过程
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <returns>返回第一条记录第一个</returns>
public int RunProcScalar(string procedureName)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateCommand(sqlConnection,procedureName,null);
int tmp;
tmp = (int)cmd.ExecuteScalar();
sqlConnection.Close();
return tmp;
}
}
/// <summary>
/// 运行存储过程
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="parameter">存储过程参数</param>
/// <returns>返回第一条记录第一个</returns>
public int RunProcScalar(string procedureName, SqlParameter[] parameter)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateCommand(sqlConnection,procedureName,parameter);
int tmp;
tmp = (int)cmd.ExecuteScalar();
sqlConnection.Close();
return tmp;
}
}
/// <summary>
/// 运行存储过程并返回 DataReader
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <returns>返回一个新的 SqlDataReader 对象</returns>
public SqlDataReader RunProcReader(string procedureName)
{
//using ( SqlConnection sqlConnection = GetSqlConnection() )
//{
SqlConnection sqlConnection = GetSqlConnection();
SqlCommand cmd = CreateCommand(sqlConnection, procedureName, null);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//}
}
/// <summary>
/// 运行存储过程并返回 DataReader
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="parameter">存储过程参数</param>
/// <returns>返回一个新的 SqlDataReader 对象</returns>
public SqlDataReader RunProcReader(string procedureName, SqlParameter[] parameter)
{
//using ( SqlConnection sqlConnection = GetSqlConnection() )
//{
SqlConnection sqlConnection = GetSqlConnection();
SqlCommand cmd = CreateCommand(sqlConnection, procedureName, parameter);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//}
}
/// <summary>
/// 运行存储过程返回DataSet
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <returns>返回DataSet</returns>
public DataSet RunProcDataSet(string procedureName)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateCommand(sqlConnection,procedureName,null);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
da.Fill(dataSet);
sqlConnection.Close();
return dataSet;
}
}
/// <summary>
/// 运行存储过程返回DataSet
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameter">存储过程参数</param>
/// <returns>返回DataSet</returns>
public DataSet RunProcDataSet(string procedureName, SqlParameter[] parameter)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateCommand(sqlConnection,procedureName,parameter);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
da.Fill(dataSet);
sqlConnection.Close();
return dataSet;
}
}
/// <summary>
/// 创建一个SqlCommand对象以此来执行存储过程
/// </summary>
/// <param name="sqlConnection">sql链接</param>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameter">存储过程参数</param>
/// <returns>返回SqlCommand对象</returns>
private SqlCommand CreateCommand(SqlConnection sqlConnection, string procedureName, SqlParameter[] parameter)
{
if ( sqlConnection.State == System.Data.ConnectionState.Closed )
sqlConnection.Open();
SqlCommand command = new SqlCommand(procedureName,sqlConnection);
command.CommandType = CommandType.StoredProcedure;
if ( parameter != null )
{
foreach( SqlParameter param in parameter)
{
command.Parameters.Add( param );
}
}
/// 加入返回参数
command.Parameters.Add( new SqlParameter("ReturnValue",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,String.Empty,DataRowVersion.Default,null) );
return command ;
}
#endregion
#region 生成存储过程参数
/// 示例
/// SqlDataProvider myData = new SqlDataProvider();
/// SqlParameter[] prams = { myData.CreateInParam("@ID",SqlDbType.Int,4,1),
/// myData.CreateOutParam("@OutParam",SqlDbType.Int,4)
/// }
/// <summary>
/// 生成存储过程参数
/// </summary>
/// <param name="parameterName">存储过程名称</param>
/// <param name="dataType">参数类型</param>
/// <param name="size">参数大小</param>
/// <param name="parameterDirection">参数方向</param>
/// <param name="parameterValue">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter CreateParam(string parameterName, SqlDbType dataType, Int32 size, ParameterDirection parameterDirection, object parameterValue)
{
SqlParameter parameter ;
if ( size > 0 )
parameter = new SqlParameter(parameterName, dataType, size);
else
parameter = new SqlParameter(parameterName, dataType);
parameter.Direction = parameterDirection ;
if ( !( parameterDirection == ParameterDirection.Output && parameterValue == null ) )
parameter.Value = parameterValue ;
return parameter ;
}
/// <summary>
/// 传入输入参数
/// </summary>
/// <param name="parameterName">存储过程名称</param>
/// <param name="dataType">参数类型</param>
/// <param name="size">参数大小</param>
/// <param name="parameterValue">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter CreateInParam(string parameterName, SqlDbType dataType, Int32 size, object parameterValue)
{
return CreateParam(parameterName, dataType, size, ParameterDirection.Input, parameterValue);
}
/// <summary>
/// 传入返回值参数
/// </summary>
/// <param name="parameterName">存储过程名称</param>
/// <param name="dataType">参数类型</param>
/// <param name="size">参数大小</param>
/// <param name="parameterValue">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter CreateOutParam(string parameterName, SqlDbType dataType, Int32 size)
{
return CreateParam(parameterName, dataType, size, ParameterDirection.Output, null);
}
#endregion
#region 运行 SQL 语句
/// <summary>
/// 运行 SQL 语句 无返回值
/// </summary>
/// <param name="strSql">SQL语句</param>
public void RunSqlNonQuery(string strSql)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateSqlCommand(sqlConnection, strSql, null);
cmd.ExecuteNonQuery();
sqlConnection.Close();
}
}
/// <summary>
/// 运行 SQL 语句 无返回值
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parameter">构建SQL语句参数</param>
public void RunSqlNonQuery(string strSql, SqlParameter[] parameter)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateSqlCommand(sqlConnection, strSql, parameter);
cmd.ExecuteNonQuery();
sqlConnection.Close();
}
}
/// <summary>
/// 运行 SQL 语句
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>返回int</returns>
public int RunSqlScalar(string strSql)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,null);
int tmp = (int)cmd.ExecuteScalar();
sqlConnection.Close();
return tmp;
}
}
/// <summary>
/// 运行 SQL 语句
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parameter">构建SQL语句参数</param>
/// <returns>返回int</returns>
public int RunSqlScalar(string strSql, SqlParameter[] parameter)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,parameter);
int tmp = (int)cmd.ExecuteScalar();
sqlConnection.Close();
return tmp;
}
}
/// <summary>
/// 运行 SQL 语句 返回DataReader
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>返回一个SqlDataReader 对象</returns>
public SqlDataReader RunSqlReader(string strSql)
{
//using ( SqlConnection sqlConnection = GetSqlConnection() )
//{
SqlConnection sqlConnection = GetSqlConnection();
SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,null);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//}
}
/// <summary>
/// 运行 SQL 语句 返回DataReader
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parameter">构建SQL语句参数</param>
/// <returns>返回一个SqlDataReader 对象</returns>
public SqlDataReader RunSqlReader(string strSql, SqlParameter[] parameter)
{
//using ( SqlConnection sqlConnection = GetSqlConnection() )
//{
SqlConnection sqlConnection = GetSqlConnection();
SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,parameter);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//}
}
/// <summary>
/// 运行 SQL 语句 返回DataSet
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>返回DataSet</returns>
public DataSet RunSqlDataSet(string strSql)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,null);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
da.Fill(dataSet);
sqlConnection.Close();
return dataSet;
}
}
/// <summary>
/// 运行 SQL 语句 返回DataSet
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parameter">构建SQL语句参数</param>
/// <returns>返回DataSet</returns>
public DataSet RunSqlDataSet(string strSql, SqlParameter[] parameter)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,parameter);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
da.Fill(dataSet);
sqlConnection.Close();
return dataSet;
}
}
/// <summary>
/// 根据SQL语句生成 SqlCommand 对象
/// </summary>
/// <param name="sqlConnection">SqlConnection 连接</param>
/// <param name="strSql">SQL语句</param>
/// <param name="parameter">构造SQL语句参数</param>
/// <returns>返回一个新的 SqlCommand 对象</returns>
public SqlCommand CreateSqlCommand(SqlConnection sqlConnection, string strSql, SqlParameter[] parameter)
{
if ( sqlConnection.State == System.Data.ConnectionState.Closed )
sqlConnection.Open();
SqlCommand command = new SqlCommand(strSql,sqlConnection);
if ( parameter != null )
{
foreach( SqlParameter param in parameter)
{
command.Parameters.Add( param );
}
}
return command ;
}
#endregion
#region 生成 构建SQL语句 参数
/// <summary>
/// 根据SQL语句生成SqlParameter对象
/// </summary>
/// <param name="parameterName">SqlParameter名</param>
/// <param name="dbType">SqlParameter DbType</param>
/// <param name="size">SqlParameter 大小</param>
/// <param name="parameterValue">SqlParameter 值</param>
/// <returns>返回SqlParameter对象</returns>
public SqlParameter CreateSqlParam(string parameterName, SqlDbType dbType, Int32 size, object parameterValue)
{
SqlParameter parameter ;
parameter = new SqlParameter(parameterName,dbType,size);
parameter.Value = parameterValue ;
return parameter ;
}
#endregion
#region IDisposable 成员
public void Dispose()
{
// TODO: 添加 SqlDabaProvider.Dispose 实现
}
#endregion
}
}