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 }
示例代码下载地址：https://files.cnblogs.com/files/dotnet261010/CSharp%E6%89%A7%E8%A1%8CSQLServer%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B.rar
