ADO.NET封装T-SQL存储过程操作类:ProcedureCommand

 

ADO.NET封装T-SQL存储过程操作类:ProcedureCommand

           最近公司项目要频繁操作数据库,需要一个能够执行带参数的存储过程并返回结果的类,于是翻了点资料就写了 ProcedureCommand类来执行存储过程.

         ProcedureCommand类为了较好的移植,因此类中不包括SqlConnection的实现,封装了一个SqlCommand对象,接收SqlConnection当作参数传入,使用起来就像一个SqlCommand一样.

         ProcedureCommand类中包含四个方法,如图:

ExecuteWithOutPara  :不带参数执行存储过程Procedure,返回受影响的行数

注:如果是Select *之类的语句,将返回-1

ExecuteWithPara : 带参数执行存储过程Procedure,返回受影响的行数

ExecuteDataSetWithOutPara:不带参数执行存储过程Procedure,返回一个DataSet

ExecuteDataSetWithPara: 带参数执行存储过程Procedure,返回一个DataSet,

实现代码如下:

 

using System;
using System.Data;
using System.Data.SqlClient;

namespace System.Data.ProcedureCommand
...{
    /**//// <summary>
    /// 数据库存储过程使用类
    /// 封装了一个SqlCommand
    /// 
    /// Build: 2006.10.26 
    /// @Author: Red_angelX
    /// </summary>
    public class ProcedureCommand
    ...{
        //全局SqlCommand对象实体
        SqlCommand _sqlCmd;

        //全局SqlDataAdapter对象
        SqlDataAdapter _sqladapter;

        /**//// <summary>
        /// 执行不带参数的存储过程,并返回受影响的行数
        /// </summary>
        /// <param name="sqlconn">SqlConnection实例</param>
        /// <param name="ProcedureName">存储过程名称</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteWithOutPara(SqlConnection sqlconn, string ProcedureName)
        ...{
            _sqlCmd = new SqlCommand(ProcedureName, sqlconn);
            //设置使用存储过程
            _sqlCmd.CommandType = CommandType.StoredProcedure;

            if (sqlconn.State != ConnectionState.Open)
            ...{
                sqlconn.Open();
            }
            int ret = -1;
            try
            ...{
                ret = _sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            ...{
                throw new Exception(ex.ToString());
            }
            finally
            ...{
                //关闭连接
                sqlconn.Close();
            }
            return ret;
        }

        /**//// <summary>
        /// 执行带参数的存储过程,并返回受影响的行数
        /// </summary>
        /// <param name="sqlconn">SqlConnection实例</param>
        /// <param name="ProcedureName">存储过程名称</param>
        /// <param name="Parameters">参数集</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteWithPara(SqlConnection sqlconn, string ProcedureName, SqlParameter[] Parameters)
        ...{
            _sqlCmd = new SqlCommand(ProcedureName, sqlconn);
            //设置使用存储过程
            _sqlCmd.CommandType = CommandType.StoredProcedure;
            //先清楚参数
            _sqlCmd.Parameters.Clear();

            _sqlCmd.Parameters.AddRange(Parameters);

            if (sqlconn.State != ConnectionState.Open)
            ...{
                sqlconn.Open();
            }
            int ret = -1;
            try
            ...{
                ret = _sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            ...{
                throw new Exception(ex.ToString());
            }
            finally
            ...{
                //关闭连接
                sqlconn.Close();
            }
            return ret;
        }

        /**//// <summary>
        /// 执行不带参数的存储过程查询,并返回查询结果
        /// </summary>
        /// <param name="sqlconn">SqlConnection实例</param>
        /// <param name="ProcedureName">存储过程名称</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSetWithOutPara(SqlConnection sqlconn, string ProcedureName)
        ...{
            _sqladapter = new SqlDataAdapter();
            DataSet dataset = new DataSet();
            _sqlCmd = new SqlCommand(ProcedureName, sqlconn);
            //设置使用存储过程
            _sqlCmd.CommandType = CommandType.StoredProcedure;

            if (sqlconn.State != ConnectionState.Open)
            ...{
                sqlconn.Open();
            }
            try
            ...{
                _sqladapter.SelectCommand = _sqlCmd;
                _sqladapter.Fill(dataset);
            }
            catch (Exception ex)
            ...{
                throw new Exception(ex.ToString());
            }
            finally
            ...{
                //关闭连接
                sqlconn.Close();
            }
            return dataset;
        }

        /**//// <summary>
        /// 执行带参数的存储过程查询,并返回查询结果
        /// </summary>
        /// <param name="sqlconn">SqlConnection实例</param>
        /// <param name="ProcedureName">存储过程名称</param>
        /// <param name="Parameters">参数集</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSetWithPara(SqlConnection sqlconn, string ProcedureName, SqlParameter[] Parameters)
        ...{
            _sqladapter = new SqlDataAdapter();
            DataSet dataset = new DataSet();
            _sqlCmd = new SqlCommand(ProcedureName, sqlconn);
            //设置使用存储过程
            _sqlCmd.CommandType = CommandType.StoredProcedure;
            //先清楚参数
            _sqlCmd.Parameters.Clear();

            _sqlCmd.Parameters.AddRange(Parameters);

            if (sqlconn.State != ConnectionState.Open)
            ...{
                sqlconn.Open();
            }
            try
            ...{
                _sqladapter.SelectCommand = _sqlCmd;
                _sqladapter.Fill(dataset);
            }
            catch (Exception ex)
            ...{
                throw new Exception(ex.ToString());
            }
            finally
            ...{
                //关闭连接
                sqlconn.Close();
            }
            return dataset;
        }


    }
}

 

客户端调用代码简单演示:

 

using System.Data.SqlClient;

namespace ProcedureCallDemoCSharp
...{
    public partial class Form1 : Form
    ...{
        SqlConnection sqlconn;
        public Form1()
        ...{
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        ...{
            String connStr = "server=localhost;uid=sa;pwd=;database=Procedure";
            sqlconn = new SqlConnection(connStr);
            sqlconn.Open();
            ProcedureCommand pcmd = new ProcedureCommand();
            int result = pcmd.ExecuteWithOutPara(sqlconn, "UpdateProducts");
            MessageBox.Show(result.ToString());
        }

        private void button2_Click(object sender, EventArgs e)
        ...{
            String connStr = "server=localhost;uid=sa;pwd=r;database=Procedure";
            sqlconn = new SqlConnection(connStr);
            ProcedureCommand pcmd = new ProcedureCommand();
            SqlParameter[] Parameters = new SqlParameter[]...{
                new SqlParameter("@ProductID", 1)
            };
            pcmd.ExecuteWithPara(sqlconn, "ProductInfo", Parameters);

        }
    }
}

其中UpdateProducts过程为

 

CREATE PROCEDURE UpdateProducts
AS
Update
 Products set name='red'

GO

 

ProductInfo过程为:

 

CREATE PROCEDURE ProductInfo
@ProductID int
AS
Update Products set name='Test'
WHERE id=@ProductID


GO

表的结构很简单,自己构造了

使用起来是不是很方便:)