c#配合sqlserver2014实现调用有输入参数，有返回输出的存储过程

数据库部分：
定义一个表Cfg_Department，里面有一列ID，作为主键，并且是标识。
还有一列Department是我们的部门数据
如下图：

然后写一个存储过程，并执行以下，如下：

USE [我的数据库名]
GO

if exists (select * from sysobjects where id = object_id(N'PROC_OperateDepartment') and type in (N'P', N'PC'))
drop procedure PROC_OperateDepartment
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Bruce
-- Create date: 2020-06-06
-- Description:	获取部门信息
-- =============================================
CREATE PROCEDURE PROC_OperateDepartment(
	@OperType int --操作类型
)
AS
BEGIN
	--使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息
	SET NOCOUNT ON;
	if(@@TRANCOUNT <> 0)
		return 1
	--如果 Transact-SQL 语句产生运行时错误，整个事务将终止并回滚
	set xact_abort on 
	--开始事务
	begin tran maintran	
	if(@OperType=1)
	begin
	select * from Cfg_Department
		if(@@ERROR <> 0)
			begin
			rollback tran maintran
			return 1
			end	
		commit tran miantran
		return 0
	end
END
GO

c#代码部分：
定义一个sqlhelper类用来连接和访问操作数据库，代码如下：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestSqlServerProcedure
{
    public class Dept
    {
        public string deptName;
    }
    class SqlHelper
    {
        private string _connectionStr = "";
        private string _dbName = "";
        //打开数据库
        public bool OpenSqlServerConnection(string strServerName, string strDbName, string strUser, string strPwd)
        {
            if (IsConnected)
            {
                return true;
            }
            string strConnection = "";
            if (strUser == "" && strPwd == "")
            {
                strConnection = "Persist Security Info=false;Integrated Security=sspi;Connection Timeout=5;Data Source=" + strServerName +
                                ";Initial Catalog=" + strDbName;
            }
            else
            {
                strConnection = "Persist Security Info=false;Integrated Security=false;Connection Timeout=5;Data Source=" + strServerName +
                                                ";Initial Catalog=" + strDbName + ";User ID=" + strUser + ";Password=" + strPwd;
            }
            try
            {
                m_Conn = new SqlConnection(strConnection);
                m_Conn.Open();
                _connectionStr = strConnection;
                _dbName = strDbName;
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
                m_Conn = null;
            }

            return m_Conn == null ? false : true;
        }
        public bool ReOpenSqlServer()//重连数据库
        {
            try
            {
                m_Conn = new SqlConnection(_connectionStr);
                m_Conn.Open();
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
                m_Conn = null;
            }

            return m_Conn == null ? false : true;
        }
        private SqlConnection m_Conn = null;
        public bool IsConnected//判断当前是否连接数据库
        {
            get
            {
                return m_Conn == null ? false : m_Conn.State == ConnectionState.Open;
            }
        }
        private readonly object sqlLock = new object();
        /// <summary>
        /// 使用存储过程执行
        /// </summary>
        /// <param name="strProcName">存储过程名称</param>
        /// <param name="procParam">存储过程的参数</param>
        /// <param name="dataReader">输出</param>
        /// <returns>执行结果</returns>
        public int ExecuteProc(string strProcName, SqlParameter[] procParam, out SqlDataReader dataReader)
        {
            dataReader = null;
            if (!IsConnected)
                return -1;
            lock (sqlLock)//加锁，防止多线程调用引起数据库访问失败
            {
                SqlCommand cmd = new SqlCommand(strProcName, m_Conn);
                cmd.CommandTimeout = 15;
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Clear();
                // 依次把参数传入存储过程 
                if (procParam != null)
                {
                    foreach (SqlParameter param in procParam)
                        cmd.Parameters.Add((SqlParameter)((ICloneable)param).Clone());
                }
                SqlParameter retValParam = new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, true,
                                                                                   0, 0, string.Empty, DataRowVersion.Default, null);
                // 加入返回参数 
                cmd.Parameters.Add(retValParam);
                dataReader = cmd.ExecuteReader();
                if (cmd.Parameters["ReturnValue"].Value != null)
                    return (int)cmd.Parameters["ReturnValue"].Value;
                else
                    return 1;
            }
        }

        /// <summary>
        /// 使用SQL语句执行,无输入参数无数据集输出
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns>执行结果</returns>
        public int ExecuteCommand(string strSql)
        {
            if (!IsConnected)
                return -1;
            lock (sqlLock)
            {
                SqlCommand cmd = new SqlCommand(strSql, m_Conn);
                cmd.CommandTimeout = 15;
                cmd.CommandType = CommandType.Text;
                SqlParameter retValParam = new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, true,
                                                                                            0, 0, string.Empty, DataRowVersion.Default, null);
                // 加入返回参数 
                cmd.Parameters.Clear();
                cmd.Parameters.Add(retValParam);
                cmd.ExecuteNonQuery();
                if (cmd.Parameters["ReturnValue"].Value != null)
                    return (int)cmd.Parameters["ReturnValue"].Value;
                else
                    return 1;
            }
        }
    }
}

然后在form类中连接数据库，并调用存储过程，主要代码如下：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace TestSqlServerProcedure
{

    public partial class Form1 : Form
    {
        SqlHelper sqlHelp = new SqlHelper();
        public Form1()
        {
            InitializeComponent();
        }

        private void buttonConnectSql_Click(object sender, EventArgs e)
        {
            if (sqlHelp.OpenSqlServerConnection("192.168.43.193数据库所在电脑IP", "", "数据库名", "数据库密码"))
            {
                textBoxMsg.Text = "连接数据库成功！"; 
            }
            else
            {
                textBoxMsg.Text = "连接数据库失败！";
            }
        }

        private void button1_Click(object sender, EventArgs e)//执行存储过程
        {
            List<Dept> lstdept = GetAllDept(1);
            if (lstdept == null)
                return;
            foreach(Dept dt in lstdept)
            {
                textBoxMsg.AppendText(dt.deptName+"\n");
            }
        }
        /// <summary>
        /// 参数组装
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">类型</param>
        /// <param name="Size">字节数</param>
        /// <param name="Direction">参数方向(输入/输出)</param>
        /// <param name="Value">参数值</param>
        /// <returns>执行结果</returns>
        public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            SqlParameter param;
            if (Size > 0)
                param = new SqlParameter(ParamName, DbType, Size);
            else
                param = new SqlParameter(ParamName, DbType);

            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;
            return param;
        }
        public List<Dept> GetAllDept(int nOperate)//调用存储过程
        {
            List<Dept> lstRet = null;
            SqlDataReader dataReader = null;
            try
            {
                SqlParameter[] parSql = new SqlParameter[1];
                parSql[0] = MakeParam("OperType", SqlDbType.Int, 0, ParameterDirection.Input, nOperate);

                sqlHelp.ExecuteProc("[Lead3C].[dbo].[PROC_OperateDepartment]", parSql, out dataReader);
                if (dataReader != null)
                {
                    if (dataReader.HasRows)
                    {
                        lstRet = new List<Dept>();
                        while (dataReader.Read())
                        {
                            Dept dept = new Dept();
                            if(!dataReader.IsDBNull(dataReader.GetOrdinal("Department")))
                            {
                                dept.deptName = (string)dataReader["Department"];
                            }
                            lstRet.Add(dept);
                        }
                    }
                    dataReader.Close();
                }
            }
            catch (Exception ex)
            {
                if (dataReader != null)
                    dataReader.Close();
                Trace.WriteLine(ex.Message);
                return null;
            }
            return lstRet;
        }

        private void button2_Click(object sender, EventArgs e)//插入数据
        {
            int nRet = sqlHelp.ExecuteCommand("insert into [Lead3C].[dbo].[Cfg_Department] values('测试部门')");
            textBoxMsg.AppendText(nRet.ToString());
        }
    }
}

执行结果如下：