C#调用SQL Server有参的存储过程

一、使用SqlParameter的方式

代码：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.ComponentModel;
 4 using System.Data;
 5 using System.Data.SqlClient;
 6 using System.Drawing;
 7 using System.Linq;
 8 using System.Text;
 9 using System.Threading.Tasks;
10 using System.Windows.Forms;
11 using System.Configuration;
12 using System.Collections.ObjectModel;
13 using System.Reflection;
14 
15 namespace ExecuteProcBySQLServer
16 {
17     public partial class Form1 : Form
18     {
19         public Form1()
20         {
21             InitializeComponent();
22         }
23 
24         private void btn_LoadData_Click(object sender, EventArgs e)
25         {
26             // 存储过程名称
27             string strProcName = "usp_yngr_getInfectionCard_test";
28 
29             //定义存储过程的参数数组
30             SqlParameter[] paraValues = { 
31                                         new SqlParameter("@BeginTime",SqlDbType.VarChar),
32                                         new SqlParameter("@EndTime",SqlDbType.VarChar),
33                                         new SqlParameter("@DateType",SqlDbType.Int),
34                                         new SqlParameter("@PtName",SqlDbType.VarChar),
35                                         new SqlParameter("@PtChartNo",SqlDbType.VarChar),
36                                         new SqlParameter("@DeptCode",SqlDbType.VarChar),
37                                         new SqlParameter("@CheckedStatus",SqlDbType.Int)
38                                         };
39             // 给存储过程参数数组赋值
40             paraValues[0].Value = "2017-06-01";
41             paraValues[1].Value = "2017-07-01";
42             paraValues[2].Value = 1;
43             paraValues[3].Value = "";
44             paraValues[4].Value = "";
45             paraValues[5].Value = "";
46             paraValues[6].Value = 1;
47 
48             this.dgv_Demo.DataSource = LoadData(strProcName, paraValues);
49 
50         }
51 
52         /// <summary>
53         /// 通过存储过程获取数据
54         /// </summary>
55         /// <param name="strProcName">存储过程名称</param>
56         /// <param name="paraValues">可变的参数数组 数组的个数可以为0，也可以为多个</param>
57         /// <returns></returns>
58         private DataTable LoadData(string strProcName, params object[] paraValues)
59         {
60             DataTable dt = new DataTable();
61             string strConn = ConfigurationManager.ConnectionStrings["HealthHospInfection"].ConnectionString;
62             using (SqlConnection conn = new SqlConnection(strConn))
63             {
64                 try
65                 {
66                     SqlCommand cmd = new SqlCommand();
67                     cmd.CommandText = strProcName;
68                     // 设置CommandType的类型
69                     cmd.CommandType = CommandType.StoredProcedure;
70                     cmd.Connection = conn;
71                     conn.Open();
72 
73                     if (paraValues != null)
74                     {
75                         //添加参数
76                         cmd.Parameters.AddRange(paraValues);
77                     }
78 
79                     // 取数据
80                     using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
81                     {
82                         adapter.Fill(dt);
83                     }
84                 }
85                 catch (Exception ex)
86                 {
87                     MessageBox.Show("错误:" + ex.Message + "/r/n跟踪:" + ex.StackTrace);
88                 }
89                 finally
90                 {
91                     conn.Close();
92                 }
93             }
94             return dt;
95         }      
96     }
97 }

二、使用SqlCommandBuilder

在上面的例子中，得到一个SqlCommand之后要一个一个地去设置参数，这样很麻烦，幸好SqlCommandBuilder有一个静态的方法：

1 public static void DeriveParameters(SqlCommand command);

使用这个方法有两个局限性：
1、参数必须是SqlCommand。
2、该方法只能在调用存储过程的时候使用。
同时还要注意到：在使用的时候，数据库连接必须是打开的。
下面的例子演示如何使用这个方法设置存储过程的参数:

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Collections.ObjectModel;
  4 using System.ComponentModel;
  5 using System.Configuration;
  6 using System.Data;
  7 using System.Data.SqlClient;
  8 using System.Drawing;
  9 using System.Linq;
 10 using System.Reflection;
 11 using System.Text;
 12 using System.Windows.Forms;
 13 
 14 namespace ExecuteProcBySQLServer
 15 {
 16     public partial class Form2 : Form
 17     {
 18         public Form2()
 19         {
 20             InitializeComponent();
 21         }
 22 
 23         private void btn_LoadData_Click(object sender, EventArgs e)
 24         {
 25             // 存储过程名称
 26             string strProcName = "usp_yngr_getInfectionCard_test";
 27             // 定义参数类
 28             object objParams = new
 29             {
 30                 BeginTime = "2017-06-01",
 31                 EndTime = "2017-07-01",
 32                 DateType = 1,
 33                 PtName = "",
 34                 PtChartNo = "",
 35                 DeptCode = "",
 36                 CheckedStatus = 1
 37             };
 38 
 39             this.dgv_Demo.DataSource = LoadData(strProcName,objParams);
 40         }
 41 
 42         private DataTable LoadData(string strProcName,object objParams)
 43         {
 44             DataTable dtInit = new DataTable();
 45             string strConn = ConfigurationManager.ConnectionStrings["HealthHospInfection"].ConnectionString;
 46             using (SqlConnection conn = new SqlConnection(strConn))
 47             {
 48                 try
 49                 {
 50                     SqlCommand cmd = new SqlCommand();
 51                     cmd.CommandText = strProcName;
 52                     // 设置CommandType的类型
 53                     cmd.CommandType = CommandType.StoredProcedure;
 54                     cmd.Connection = conn;
 55                     conn.Open();
 56 
 57                     // 添加参数
 58                     foreach (var item in GetParameters(cmd, objParams))
 59                     {
 60                         cmd.Parameters.Add(item);
 61                     }
 62 
 63                     // 取数据
 64                     using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
 65                     {
 66                         adapter.Fill(dtInit);
 67                     }
 68                 }
 69                 catch (Exception ex)
 70                 {
 71                     MessageBox.Show("错误:" + ex.Message + "/r/n跟踪:" + ex.StackTrace);
 72                 }
 73                 finally
 74                 {
 75                     conn.Close();
 76                 }
 77             }
 78             return dtInit;
 79         }
 80 
 81         private Collection<SqlParameter> GetParameters(SqlCommand command, object objParam)
 82         {
 83             Collection<SqlParameter> collection = new Collection<SqlParameter>();
 84             if (objParam != null)
 85             {
 86                 // 使用反射获取属性
 87                 PropertyInfo[] properties = objParam.GetType().GetProperties();
 88                 SqlCommandBuilder.DeriveParameters(command);
 89                 //int index = 0;
 90                 foreach (SqlParameter parameter in command.Parameters)
 91                 {
 92                     foreach (PropertyInfo property in properties)
 93                     {
 94                         if (("@" + property.Name.ToLower()).Equals(parameter.ParameterName.ToLower()))
 95                         {
 96                             parameter.Value = property.GetValue(objParam, null);
 97                             collection.Add(parameter);
 98                         }
 99                     }
100                 }
101 
102                 // 清空所有参数对象
103                 command.Parameters.Clear();
104             }
105 
106             return collection;
107         }
108     }
109 }