Below is an example of a script which will run a stored procedure, passing in a “@DEPARTMENT” parameter from the Dataset the script has been attached to or upon which the Spreadsheet is operating:

using System;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Runtime.Serialization;
using System.Web.Script.Serialization;
using Newtonsoft.Json;


namespace Nathean
{

    public class ExecuteStoredProcedure
    {

        public void ProcessSavedData(DataTable dt, List<string> stringParams)
        {

            //try
            {
                string connString = "server=[SERVERNAME]\;User Id=[USERNAME];password=[PASSWORD];Persist Security Info=True;database=[INSTANCE_NAME]";


                using (SqlConnection checkDel = new SqlConnection(connString))
                {
                    using (SqlCommand cmd = checkDel.CreateCommand())
                    {
                        cmd.CommandText = "EXEC dbo.[YOUR_STORED_PROCEDURE_NAME @DEPARTMENT= @DEPARTMENT";
                        cmd.CommandType = CommandType.Text;

                        cmd.Parameters.Add("@PARAM1", SqlDbType.VarChar, 255);

                        cmd.Parameters["@PARAM1"].Value = stringParams[0];
                        //cmd.Parameters["@PARAM1"].Value = "string";

                        checkDel.Open();

                        using (SqlDataReader rdr = cmd.ExecuteReader())
                        {
                            while (rdr.Read())
                            {
                            }
                        }
                    }
                }

            }
            //catch (Exception e)
            //{
                // do something with e
            //}

            // add columns
            // if (dt.Columns["NEW_FIELD"] == null)
            // {
            //     dt.Columns.Add(new DataColumn("NEW_FIELD",typeof(int)));
            // }

            // to do

        }

    }
}

p.
Revision: 3
Last modified: Jul 24, 2020

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment