Execute SQL Server stored procedure in C#

Question

I'm helping to make a chatbot in C# but I have never worked with C# before so please forgive my ignorance. I'm using this link as a reference: https://jd-bots.com/2020/10/15/connect-bot-framework-to-db-part-3-read-data-from-azure-sql-database/

I want to switch out the select query for a stored procedure which accomplishes the same thing (we need to do some joins that make more sense to do within SQL Server).

The code below is based on the example from the link:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using EchoAzureDBBot.Models;
using Microsoft.Bot.Builder;
using Microsoft.Bot.Schema;
 
namespace EchoAzureDBBot.Bots
{
    public class EchoBot : ActivityHandler
    {
        EmployeeDBContext context;
        public EmployeeDBContext Context { get { return context; } }

        public EchoBot()
        {
            context = new EmployeeDBContext();
        }

        public Employee FetchEmployeeName(string no)
        {
            Employee employee;

            try
            {
                employee = (from e in Context.Employee
                            where e.Empid == no
                            select e).FirstOrDefault();//Query for employee details with id
            }
            catch (Exception)
            {
                throw;
            }

            return employee;
        }

        protected override async Task OnMessageActivityAsync(ITurnContext<IMessageActivity> turnContext, CancellationToken cancellationToken)
        {
            var empNumber = turnContext.Activity.Text;
            Employee employee = FetchEmployeeName(empNumber);
            var replyText = employee.Empid + ": " + employee.Empname;
            await turnContext.SendActivityAsync(MessageFactory.Text(replyText, replyText), cancellationToken);
        }

        protected override async Task OnMembersAddedAsync(IList<ChannelAccount> membersAdded, ITurnContext<IConversationUpdateActivity> turnContext, CancellationToken cancellationToken)
        {
            var welcomeText = "Hello and welcome! I have connected this bot to Azure DB."; //welcome message

            foreach (var member in membersAdded)
            {
                if (member.Id != turnContext.Activity.Recipient.Id)
                {
                    await turnContext.SendActivityAsync(MessageFactory.Text(welcomeText, welcomeText), cancellationToken);
                }
            }
        }
    }
}

What we want to do is have a stored procedure execute the query. So basically instead of:

employee = (from e in Context.Employee
            where e.Empid == emp_id //the id we pass in as a variable earlier 
            select e).FirstOrDefault();//Query for employee details with id

We want the stored procedure which does the same thing to only return the EmployeeName from the query:

SqlCommand cmd  = new SqlCommand("GetEmployee", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", emp_id))

using (SqlDataReader rdr = cmd.ExecuteReader()) 
{
    employee = [EmployeeName] 
}

Would this be how to go about it? Is there a simple way to just pass in the @EmployeeID param and then do the following to get it to work? If so, where would this block of code go in the original code block, where the select query is now?

EXEC GetEmployee @EmployeeID = emp_id;

Thank you for your help!

Answer 1

You can use this stored procedure that only returns the Employee name By giving her the the connected employee ID.

 CREATE PROCEDURE [GetEmployee]
        (@EmployeeID int)
        AS
        BEGIN
         
        select EMPNAME from EMPLOYEE where EMPID=@EmployeeID
         
        END

Example :

EXEC GetEmployee @EmployeeID = 1;

Answer 2

Inside of your using statement, you'll need to get the value from the SqlDataReader like so:

employee = rdr["EmployeeName"];