C#执行存储过程通用类
网上看到的,整理整齐有用的童鞋拿去.using System.Data.SqlClient;
using System.Collections;
using System.Data;
namespace RunStoreProcedure
{
/// <summary>
/// 调用存储过程通用类
/// </summary>
public class StoreProcedure
{
private string _name; // 存储过程名称。
private string _conStr; // 数据库连接字符串。
/// <summary>
/// 构造函数
/// </summary>
/// <param name="sprocName">存储过程名称</param>
/// <param name="conStr">数据库连接字符串</param>
public StoreProcedure(string sprocName, string conStr)
{
_conStr = conStr;
_name = sprocName;
}
/// <summary>
/// 执行存储过程，不返回值
/// </summary>
/// <param name="paraValues"> 参数值列表</param>
public void ExecuteNoQuery(params object[] paraValues)
{
using (SqlConnection con = new SqlConnection(_conStr))
{
SqlCommand comm = new SqlCommand(_name, con);
comm.CommandType = CommandType.StoredProcedure;
AddInParaValues(comm, paraValues);
con.Open();
comm.ExecuteNonQuery();
con.Close();
}
}
/// <summary>
/// 执行存储过程,返回表
/// </summary>
/// <param name="paraValues">参数列表</param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(params object[] paraValues)
{
SqlCommand comm = new SqlCommand(_name, new SqlConnection(_conStr));
comm.CommandType = CommandType.StoredProcedure;
AddInParaValues(comm, paraValues);
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
/// <summary>
/// 执行存储过程,返回SqlDataReader
/// </summary>
/// <param name="paraValues">参数列表</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader ExecuteDataReader(params object[] paraValues)
{
SqlConnection con = new SqlConnection(_conStr);
SqlCommand comm = new SqlCommand(_name, con);
comm.CommandType = CommandType.StoredProcedure;
AddInParaValues(comm, paraValues);
con.Open();
return comm.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 添加参数及赋值.
/// </summary>
/// <param name="comm">SqlCommand</param>
/// <param name="paraValues">参数列表</param>
private void AddInParaValues(SqlCommand comm, params object[] paraValues)
{
comm.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int));
comm.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
if (paraValues != null)
{
ArrayList al = GetParas();
for (int i = 0; i < paraValues.Length; i++)
{
comm.Parameters.AddWithValue(al.ToString(), paraValues);
}
}
}
/// <summary>
///获取存储过程的参数列表
/// </summary>
/// <returns>ArrayList</returns>
private ArrayList GetParas()
{
SqlCommand comm = new SqlCommand("dbo.sp_sproc_columns_90", new SqlConnection(_conStr));
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@procedure_name", (object) _name);
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataTable dt = new DataTable();
sda.Fill(dt);
ArrayList al = new ArrayList();
for (int i = 0; i < dt.Rows.Count; i++)
{
al.Add(dt.Rows.ToString());
}
return al;
}
}
}
页:
[1]