C#存储过程操作

转载注明出处:http://www.sortol.com/projects/2007-10-20/article3380.shtml

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

public class SqlClass
{
    protected SqlConnection Connection;
    protected string connectionstring;

    public SqlClass()
    {
        connectionstring = System.Configuration.ConfigurationSettings.AppSettings["connString"];
        Connection = new SqlConnection(connectionstring);
    }

    public SqlClass(string connstring)
    {
        connectionstring = connstring;
        Connection = new SqlConnection(connectionstring);
    }

    /*执行返回结果和不返回结果的存储过程方法*/
    /// <summary>
    /// 根据存储过程名称和参数生成对应的SQL命令对象
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <returns></returns>
    private SqlCommand BuilderQueryCommand(string storedProcName, SqlParameter[] parameters)
    {
        SqlCommand command = new SqlCommand();
        command.Connection = Connection;
        command.CommandText = storedProcName.Trim();
        command.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
        {
            foreach (SqlParameter p in parameters)
            {
                command.Parameters.Add(p);
            }
        }
        return command;
    }
    /// <summary>
    /// 返回结果的存储过程
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">参数值</param>
    /// <returns></returns>
    public SqlDataReader GetDataReader(string storedProcName, SqlParameter[] parameters)
    {
        SqlDataReader reader;
        SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return reader;
    }
    /// <summary>
    /// 不返回结果的存储过程
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">参数值</param>
    /// <returns></returns>
    public int GetEffect(string storedProcName, SqlParameter[] parameters)
    {
        int result = 0;
        try
        {
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
            SqlTransaction trans = Connection.BeginTransaction();
            try
            {
                SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
                cmd.Transaction = trans;
                result = cmd.ExecuteNonQuery();
                trans.Commit();
                Connection.Close();
                return result;
            }
            catch (Exception ex)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    return result;
                }
                throw ex;

            }
            finally
            {
                if (trans != null)
                {
                    trans.Dispose();
                }
                Connection.Close();
            }
        }
        catch (Exception ex1)
        {
            return 0;
            //  throw new Exception(ex1.Message);
        }
    }
    /// <summary>
    /// 返回dateSet
    /// </summary>
    /// <param name="ProcName"></param>
    /// <param name="parameters"></param>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public DataSet GetDataSet(string ProcName, SqlParameter[] parameters, string tableName)
    {
        try
        {

            DataSet ds = new DataSet();
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }

            SqlDataAdapter myDa = new SqlDataAdapter();
            myDa.SelectCommand = BuilderQueryCommand(ProcName, parameters);
            myDa.Fill(ds, tableName);
            return ds;
        }
        catch
        {
            return null;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 自定义分页
    /// </summary>
    /// <param name="ProcName">存储过程名</param>
    /// <param name="parameters">参数名</param>
    /// <param name="start">起始页</param>
    /// <param name="maxRecord">记录数</param>
    /// <param name="tableName">表名</param>
    /// <returns></returns>
    public DataSet GetDataSet(string ProcName, SqlParameter[] parameters, int start, int maxRecord, string tableName)
    {
        try
        {
            DataSet ds = new DataSet();
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
            Connection.Open();
            SqlDataAdapter myDa = new SqlDataAdapter();
            myDa.SelectCommand = BuilderQueryCommand(ProcName, parameters);
            myDa.Fill(ds, start, maxRecord, tableName);
            return ds;
        }
        catch
        {
            Connection.Close();
            return null;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 返回object类型　比如首行首列
    /// </summary>
    /// <param name="storedProcName">存储过程名称</param>
    /// <param name="parameters">参数列表</param>
    /// <returns></returns>
    public object GetObject(string storedProcName, SqlParameter[] parameters)
    {
        object result = null;

        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        //   SqlTransaction trans = Connection.BeginTransaction();
        try
        {
            SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
            //  cmd.Transaction = trans;
            result = cmd.ExecuteScalar();
            //  trans.Commit();
            Connection.Close();
            return result;
        }
        catch
        {
            //  trans.Rollback();
            return result;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 通过ＩＤ号操作某条记录,比如删除ＩＤ号的记录
    /// </summary>
    /// <param name="ProcName">存储过程名</param>
    /// <param name="id">值</param>
    /// <param name="paraname">参数名如：@ID</param>
    /// <returns></returns>
    public int RunProcByID(string ProcName, int id, string paraname)
    {
        try
        {
            SqlParameter[] p ={ new SqlParameter(paraname, SqlDbType.Int) };
            p[0].Value = id;
            return GetEffect(ProcName, p);
        }
        catch
        {
            Connection.Close();
            return 0;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 修改ＩＤ号为keyValue的记录的ParaDieldName字段的值为FiledValue
    /// </summary>
    /// <param name="ProcName">相信过程名</param>
    /// <param name="keyValue">ID号的值</param>
    /// <param name="paraKeyName">ID号对应的字段名</param>
    /// <param name="paraFieldName">要修改的字段名</param>
    /// <param name="FieldValue">要修改字段的值</param>
    /// <param name="length">字段的长度</param>
    /// <returns></returns>
    public int UpdateByID(string ProcName, int keyValue, string paraKeyName, string paraFieldName, string FieldValue, int length)
    {
        try
        {
            SqlParameter[] p ={ new SqlParameter(paraFieldName,SqlDbType.VarChar,length),
                                    new SqlParameter(paraFieldName, SqlDbType.Int)
                                   };
            p[0].Value = FieldValue;
            p[1].Value = keyValue;
            return GetEffect(ProcName, p);
        }
        catch
        {
            Connection.Close();
            return 0;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 执行ＳＱＬ语句返回DataSet
    /// </summary>
    /// <param name="strSql">ＳＱＬ语句</param>
    /// <param name="tableName">表名</param>
    /// <returns></returns>
    public DataSet RunSql(string strSql, string tableName)
    {
        try
        {

            DataSet ds = new DataSet();
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
            SqlDataAdapter myDa = new SqlDataAdapter();
            myDa.SelectCommand = new SqlCommand(strSql, Connection);
            myDa.Fill(ds, tableName);
            return ds;
        }
        catch
        {
            return null;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 执行ＳＱＬ语句返回影响行数
    /// </summary>
    /// <param name="strSql">sql语句</param>
    /// <param name="effect">输出行数</param>
    /// <returns></returns>
    public int RunSql(string strSql, out int effect)
    {
        effect = 0;
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        SqlTransaction trans = Connection.BeginTransaction();
        try
        {
            SqlCommand cmd = new SqlCommand(strSql, Connection);
            cmd.Transaction = trans;
            effect = cmd.ExecuteNonQuery();
            trans.Commit();
            return effect;
        }
        catch
        {
            trans.Rollback();
            Connection.Close();
            return effect;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 执行ＳＱＬ语句返回SqlDataReader
    /// </summary>
    /// <param name="strsql">sql语句</param>
    /// <returns></returns>
    public SqlDataReader RunSql(string strsql)
    {
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        try
        {
            SqlCommand cmd = new SqlCommand(strsql, Connection);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            Connection.Close();
            return null;
        }
      
    }
    /// <summary>
    /// 执行ＳＱＬ语句返回object
    /// </summary>
    /// <param name="strsql">sql语句</param>
    /// <returns></returns>
    public object GetObjectByRunSQL(string strsql)
    {
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        object obj = null;
        try
        {
            SqlCommand cmd = new SqlCommand(strsql, Connection);
            obj = cmd.ExecuteScalar();
            Connection.Close();
            return obj;
        }
        catch
        {
            Connection.Close();
            return null;
        }
        finally
        {
            Connection.Close();
        }
    }
}