C# calls stored procedures with parameters in SQL Server

1. How to use SqlParameter

Code:

using System;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;

namespace ExecuteProcBySQLServer
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btn_LoadData_Click(object sender, EventArgs e)
        {
            // Stored procedure name            string strProcName = "usp_yngr_getInfectionCard_test";

            //Define the parameter array of stored procedures            SqlParameter[] paraValues = {
                                        new SqlParameter("@BeginTime",),
                                        new SqlParameter("@EndTime",),
                                        new SqlParameter("@DateType",),
                                        new SqlParameter("@PtName",),
                                        new SqlParameter("@PtChartNo",),
                                        new SqlParameter("@DeptCode",),
                                        new SqlParameter("@CheckedStatus",)
                                        };
            // Assign values to stored procedure parameter array            paraValues[0].Value = "2017-06-01";
            paraValues[1].Value = "2017-07-01";
            paraValues[2].Value = 1;
            paraValues[3].Value = "";
            paraValues[4].Value = "";
            paraValues[5].Value = "";
            paraValues[6].Value = 1;

            this.dgv_Demo.DataSource = LoadData(strProcName, paraValues);

        }

        /// &lt;summary&gt;
        /// Get data through stored procedures        /// &lt;/summary&gt;
        /// <param name="strProcName">Stored procedure name</param>        /// <param name="paraValues">Variable parameter array The number of arrays can be 0 or multiple</param>        /// &lt;returns&gt;&lt;/returns&gt;
        private DataTable LoadData(string strProcName, params object[] paraValues)
        {
            DataTable dt = new DataTable();
            string strConn = ["HealthHospInfection"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                     = strProcName;
                    // Set the type of CommandType                     = ;
                     = conn;
                    ();

                    if (paraValues != null)
                    {
                        //Add parameters                        (paraValues);
                    }

                    // Get data                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        (dt);
                    }
                }
                catch (Exception ex)
                {
                    ("mistake:" +  + "/r/n tracking:" + );
                }
                finally
                {
                    ();
                }
            }
            return dt;
        }
    }
}

2. Use SqlCommandBuilder

In the above example, after obtaining a SqlCommand, you have to set the parameters one by one, which is very troublesome. Fortunately, SqlCommandBuilder has a static method:

public static void DeriveParameters(SqlCommand command);

There are two limitations to using this method:

1. The parameter must be SqlCommand.
2. This method can only be used when calling stored procedures.

Also note that when using the database connection, the database connection must be open.
The following example shows how to set parameters of stored procedures using this method:

using System;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;

namespace ExecuteProcBySQLServer
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        private void btn_LoadData_Click(object sender, EventArgs e)
        {
            // Stored procedure name            string strProcName = "usp_yngr_getInfectionCard_test";
            // Define parameter class            object objParams = new
            {
                BeginTime = "2017-06-01",
                EndTime = "2017-07-01",
                DateType = 1,
                PtName = "",
                PtChartNo = "",
                DeptCode = "",
                CheckedStatus = 1
            };

            this.dgv_Demo.DataSource = LoadData(strProcName,objParams);
        }

        private DataTable LoadData(string strProcName,object objParams)
        {
            DataTable dtInit = new DataTable();
            string strConn = ["HealthHospInfection"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                     = strProcName;
                    // Set the type of CommandType                     = ;
                     = conn;
                    ();

                    // Add parameters                    foreach (var item in GetParameters(cmd, objParams))
                    {
                        (item);
                    }

                    // Get data                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        (dtInit);
                    }
                }
                catch (Exception ex)
                {
                    ("mistake:" +  + "/r/n tracking:" + );
                }
                finally
                {
                    ();
                }
            }
            return dtInit;
        }

        private Collection&lt;SqlParameter&gt; GetParameters(SqlCommand command, object objParam)
        {
            Collection&lt;SqlParameter&gt; collection = new Collection&lt;SqlParameter&gt;();
            if (objParam != null)
            {
                // Use reflection to get attributes                PropertyInfo[] properties = ().GetProperties();
                (command);
                //int index = 0;
                foreach (SqlParameter parameter in )
                {
                    foreach (PropertyInfo property in properties)
                    {
                        if (("@" + ()).Equals(()))
                        {
                             = (objParam, null);
                            (parameter);
                        }
                    }
                }

                // Clear all parameter objects                ();
            }

            return collection;
        }
    }
}

Sample code download address:Click here to download

This is all about this article about C# calling parameter stored procedures in SQL Server. I hope it will be helpful to everyone's learning and I hope everyone will support me more.