数据库访问通用类（封装）示例3

最新推荐文章于 2019-03-08 11:30:27 发布

转载最新推荐文章于 2019-03-08 11:30:27 发布 · 841 阅读

文章标签：

#数据库 #dataset #string #存储 #cmd #sql

DataBase 专栏收录该内容

16 篇文章

订阅专栏

本文介绍了一个SQL数据提供者类，该类封装了常见的SQL操作，包括执行存储过程、SQL语句等，并提供了创建参数的方法。

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
}
}