我想把数据库操作封装成一个类，以这种方式写，那不是很不好设计类库了吗？请教。

封装这种存储过程有多参数的可以把参数做为参数数组传入
public string SpExeFor(string m_A,string m_B)
         {
                //存储过程的参数声明
              OracleParameter[] parameters={
                                 new OracleParameter("paramin",OracleType.VarChar,20),
                                new OracleParameter("paramout",OracleType.VarChar,20),
                                 new OracleParameter("paraminout",OracleType.VarChar,20)
                                                };
              parameters[0].Value=m_A;
              parameters[2].Value=m_B;
              parameters[0].Direction=ParameterDirection.Input;
              parameters[1].Direction=ParameterDirection.Output;
              parameters[2].Direction=ParameterDirection.InputOutput;
              try
              {
                   RunProcedure("proce_test",parameters);
                  return parameters[1].Value.ToString();
              }
              catch(Exception e)
              {
                   throw e;
              }
         }         private void RunProcedure(string storedProcName,OracleParameter[] parameters)
         {
              cmd.CommandText=storedProcName;//声明存储过程名
              cmd.CommandType=CommandType.StoredProcedure;
              foreach(OracleParameter parameter in parameters)
              {
                   cmd.Parameters.Add(parameter);
              }
              cmd.ExecuteNonQuery();//执行存储过程
         }
这是Oracle的，你先看看

仿照微软 sqlhelper 类的实现

到网上找个SqlHelper.cs下来看看就行了

太长，分开贴SqlDataProvider.cs
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="startPage">开始页</param>
/// <param name="endPage">结束页</param>
/// <returns>返回分页DATASET</returns>
public DataSet RunProcDataSet(string procedureName, int startPage, int endPage)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateCommand(sqlConnection,procedureName,null);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
da.Fill(dataSet, startPage, endPage, "tmpDs");
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>
/// 运行存储过程返回DataSet
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameter">存储过程参数</param>
/// <param name="startPage">开始页</param>
/// <param name="endPage">结束页</param>
/// <returns>返回DataSet</returns>
public DataSet RunProcDataSet(string procedureName, SqlParameter[] parameter, int startPage, int endPage)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateCommand(sqlConnection,procedureName,parameter);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
da.Fill(dataSet, startPage, endPage, "tmpDs");
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="startPage">开始记录</param>
/// <param name="endPage">结束记录</param>
/// <returns>返回分页DataSet</returns>
public DataSet RunSqlDataSet(string strSql, int startPage, int endPage)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,null);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
da.Fill(dataSet, startPage, endPage, "tmpDs");
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 语句返回分页DataSet
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parameter">SQL语句参数</param>
/// <param name="startPage">开始记录</param>
/// <param name="endPage">结束记录</param>
/// <returns>返回分页DataSet</returns>
public DataSet RunSqlDataSet(string strSql, SqlParameter[] parameter,  int startPage, int endPage)
{
using ( SqlConnection sqlConnection = GetSqlConnection() )
{
SqlCommand cmd = CreateSqlCommand(sqlConnection,strSql,parameter);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
da.Fill(dataSet, startPage, endPage, "tmpDs");
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
}
}

说实话,如果你想做一个广泛引用的封装类,那就没有必要了,目前的ado已经很方便了.
如果只是针对特定操作(比如只是调用procedure).那倒是很好,当然这样也就很简单,不存在结构设计上的问题

调试易

我想把数据库操作封装成一个类，以这种方式写，那不是很不好设计类库了吗？请教。

解决方案 »