C# 调用存储过程

好记性不如烂笔头。

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

/// <summary>
/// 数据库工具
/// </summary>
public class DataBaseUtil
{
    /// <summary>
    /// 数据库连接语句
    /// </summary>
    private const string strConn = "Server=47.103.91.61,5300;Initial Catalog=IntelligentParkingSys;User ID=syq;Password=syq123456;pooling=true;min pool size=1;max pool size=1000;connect timeout = 20;";



    /// <summary>
    /// 保存数据
    /// </summary>
    /// <param name="procName"></param>
    /// <param name="parirsDic"></param>
    public static int SaveData(string procName, Dictionary<string, object> parirsDic)
    {
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection(strConn);
            //conn.Open();
            //打开数据库连接
            //conn.Open();
            Console.WriteLine("数据库连接成功！");

            List<SqlParameter> parasList = new List<SqlParameter>();

            if (parirsDic != null && parirsDic.Count > 0)
            {
                Dictionary<string, object>.Enumerator it = parirsDic.GetEnumerator();
                while (it.MoveNext())
                {
                    parasList.Add(new SqlParameter(it.Current.Key, it.Current.Value));
                }
            }

            //设置错误代码返回值
            int errorId = 1000;
            SqlParameter returnPara = new SqlParameter("@return", errorId);
            returnPara.Direction = ParameterDirection.ReturnValue;
            parasList.Add(returnPara);


            SqlCommand cmd = new SqlCommand(procName, conn);
            for (int i = 0; i < parasList.Count; i++)
            {
                cmd.Parameters.Add(parasList[i]);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dap = new SqlDataAdapter(cmd);
            DataTable ds = new DataTable();
            dap.Fill(ds);

            errorId = Convert.ToInt32(parasList[parasList.Count - 1].Value);
            return errorId;
        }
        catch (Exception ex)
        {
            Console.WriteLine("数据库连接失败！" + ex.Message);
            return 0;
        }
        finally
        {
            if (conn != null)
            {
                //关闭数据库连接
                conn.Close();
            }
        }
    }



    /// <summary>
    /// 获取数据
    /// </summary>
    /// <param name="procName">存储过程名字</param>
    /// <param name="parirsDic">参数列表</param>
    /// <returns></returns>
    public static DataTable GetData(string procName, Dictionary<string, object> parirsDic)
    {
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection(strConn);
            conn.Open();
            //打开数据库连接
            //conn.Open();
            Console.WriteLine("数据库连接成功！");

            List<SqlParameter> parasList = new List<SqlParameter>();

            if (parirsDic != null && parirsDic.Count > 0)
            {
                Dictionary<string, object>.Enumerator it = parirsDic.GetEnumerator();
                while (it.MoveNext())
                {
                    parasList.Add(new SqlParameter(it.Current.Key, it.Current.Value));
                }
            }


            SqlCommand cmd = new SqlCommand(procName, conn);
            for (int i = 0; i < parasList.Count; i++)
            {
                cmd.Parameters.Add(parasList[i]);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dap = new SqlDataAdapter(cmd);
            DataTable ds = new DataTable();
            dap.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            Console.WriteLine("数据库连接失败！" + ex.Message);
            return null;
        }
        finally
        {
            if (conn != null)
            {
                //关闭数据库连接
                conn.Close();
            }
        }
    }
}