Connect SQL Server and Execute Procedure [get data-set/rows/return value] from C#.NET

I will connect to SQL Server database and execute a procedure to get data.

a procedure can provide data by Return or as a Select statement.

I have discussed the full process step-by-step and also added the full C# code/class at the bottom of this post!

here is the DBO.Test_Proc , which i will call and get data & return value.
-------------------
Create Procedure dbo.Test_Proc  @param1 int = null, @param2 varchar = null
as
begin
     select @param1 as param1 , @param2 as param1 ;

     return isnull(@param1,-50);
end
------------------

now we have to call this procedure to get data from the select statement and from return statement as well.

step 0: use package System.Data.SqlClient = using System.Data.SqlClient;
step 1: First we have to connect to SQL server
step 2: Prepare the SQL command to call the procedure
step 3: Execute & Read data rows and return value.

STEP 1: Connect to SQL server

use below code to connect to SQL Server using SQL_Server user..
------------------
SqlConnection cnn;
string sqlConnectionString = "Data Source=SERVER_ADDRESS;Initial Catalog=" + "DB_NAME"
+ ";User ID=" + "USER_NAME" + ";Password=" + "PASSWORD";

try
{
cnn = new SqlConnection();
cnn.ConnectionString = sqlConnectionString;
cnn.Open();
Console.WriteLine("Connection Open");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
-----------------

STEP 2: SQL command to call the procedure

use below code to prepare the SQL Command to execute using above SQL Connection (cnn)
--------------------------
SqlCommand cmd;
string[] paramN = { "param1", "param2" };
string[] paramV = { "10","Name"};

cmd = new SqlCommand(sqlCommand, cnn);
cmd.CommandType = CommandType.StoredProcedure;

// add parameter to pass value to DB Proc
// 1st parameter = param1
if (!string.IsNullOrEmpty( paramV[0]))
cmd.Parameters.AddWithValue("@"+paramN[0], paramV[0]);
// 2nd parameter = param2
if (!string.IsNullOrEmpty(paramV[1]))
cmd.Parameters.AddWithValue("@" + paramN[1], paramV[1]);

// add a return type parameter [only if you required the return value from db proc]
var retParam = cmd.Parameters.Add("@retv", SqlDbType.Int);
retParam.Direction = ParameterDirection.ReturnValue;
--------------------------

STEP 3: Execute the SQL Command (cmd) and Read data rows & return value.

use below code to get data rows from proc and the return value also.
----------------------------
SqlDataReader dr;

// get data rows return by the select of proc
dr = cmd.ExecuteReader();
Console.WriteLine("executed");
while (dr.Read())
{
Console.WriteLine(dr[paramN[0]].ToString() + " -- " + dr[paramN[1]].ToString());
}
dr.close();

//// get return value by the retrun of proc
cmd.ExecuteNonQuery();
var retValue = retparam.Value;
Console.WriteLine(retValue);
---------------------------
---------------------------
OUTPUT
---------------------------

Connect SQL Server and Execute Stored Procedure

Please check below for the full C# class, i have written for me only :)

//--------------------------------------------------------------------------------\\
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace FTP_MONITORING_EXE
{
    class FTP_MONITORING
    {
        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataReader dr;

string sqlConnectionString = "Data Source=SERVER_ADDRESS;Initial Catalog=" + "DB_NAME" + ";User ID=" + "USER_NAME" + ";Password=" + "PASSWORD";

        static void Main(string[] args)
        {
            FTP_MONITORING fm = new FTP_MONITORING();

            int conS = fm.getSQLConnection();
            if (conS == 0) return;

            string[] paramV = { "10","Name"};
            string[] paramN = { "param1", "param2" };
            int retVal = fm.execProc("dbo.testProc", paramV, paramN);
            Console.WriteLine("return = " + retVal);
        }

        int execProc(string sqlCommand, string[] paramV, string[] paramN)
        {
            cmd = new SqlCommand(sqlCommand, cnn);
            cmd.CommandType = CommandType.StoredProcedure;

            for (int i = 0; i < paramN.Length; i++)
            {
                if (!string.IsNullOrEmpty(paramV[i]))
                    cmd.Parameters.AddWithValue("@" + paramN[i], paramV[i]);
            }

            SqlParameter retparam = cmd.Parameters.Add("@retv", SqlDbType.Int);
            retparam.Direction = ParameterDirection.ReturnValue;

            dr = cmd.ExecuteReader();
            Console.WriteLine("executed");
            while (dr.Read())
            {
                string output="";
                for (int i = 0; i < paramN.Length; i++)
                {
                    output = output + dr[paramN[i]].ToString() + " -- ";
                }
                Console.WriteLine(output);
            }
            dr.Close();

            cmd.ExecuteNonQuery();

            var retValue = retparam.Value;
            Console.WriteLine("retValue = " + retValue);
            cmd.Dispose();
            return (int)retValue;
        }

        int getSQLConnection()
        {
            try
            {
                cnn = new SqlConnection();
                cnn.ConnectionString = sqlConnectionString;
                cnn.Open();
                Console.WriteLine("Connection Open");
                return 1;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return 0;
            }
        }
    }
}

//----------------------------------------------------------------\\