How to create a connection string from Web.config file using stored procedure?

This post will illustrate how to create a database connection in C# using web.config file in MVC along with the reusable function for executing the stored procedure.

1. Create a connection string in web.config file or copy the code given below. Put this code in your <configuration> </configuration> part.

NOTE:- Don't forget to replace data source, initial catalog, user id, and password with your own.

<connectionStrings>
    <add name="DataEntities" connectionString="data source=YourServerName;initial catalog=DataBaseName;user                id=userId;integrated security=false;password=password;" providerName="System.Data.SqlClient"  />
</connectionStrings>

2. Create a Sql Connection using the connection string created in web.config file. This is done as:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DataEntities"].ConnectionString);

3. Create a function that returns a DataTable, name it as ExecuteSp. This function will take two arguments: 1. SqlCommand object i.e., the command that is to be executed. 2. DataSet object i.e., an object used to fill data adapter in disconnected manner. Proper care must be taken to provide correct stored procedure name and correct number of parameters and their values. This function will close the connection when the procedure is executed. The function is as follows: 

     
         public DataTable ExecuteSp(SqlCommand cmd, DataSet ds)
        {
            DataTable dt;
            try
            {
                SqlDataAdapter ad = new SqlDataAdapter();
                con.Open();
                ad.SelectCommand = cmd;
                ad.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch
            {
                con.Close();
                con.Dispose();
                SqlDataAdapter ad = new SqlDataAdapter();
                con.Open();
                ad.SelectCommand = cmd;
                ad.Fill(ds);
                dt = ds.Tables[1];
                return dt;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

4. Now its time to execute the above function by properly providing the stored procedure name and parameters as: proper care must be taken to mention the return type of the function as DataTable since the function ExecuteSp will return DataTable.

         public DataTable GetAllProgrammes(string param1, string param2)
        {
            //Pass Procedure Name and Connection String in the command object
            SqlCommand cmd = new SqlCommand("Procedure Name", con);

            //Specify the command type as StoredProcedure
            cmd.CommandType = CommandType.StoredProcedure;

            //Pass the values of all parameters which are specified in the stored procedure
            cmd.Parameters.AddWithValue("@ProcedureParam1", param1);
            cmd.Parameters.AddWithValue("@ProcedureParam2", param2);

            //call the method ExecuteSp()
            return ExecuteSp(cmd, ds);
        }

5. Finally, code is as:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using GraphicsScheduling.Entities;

namespace GraphicsScheduling.Data
{
    public class GraphicsClipMap
    {
        GraphicsEntities context = new GraphicsEntities();
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["GraphicsEntities1"].ConnectionString);
        public DataTable GetAllProgrammes(string channelId, string date)
        {
            DateTime fpcdate = Convert.ToDateTime(date);
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand("sp_getprogrammes", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@ccode", channelId);
            cmd.Parameters.AddWithValue("@fdate", fpcdate);
            return ExecuteSp(cmd, ds);
        }

 public DataTable ExecuteSp(SqlCommand cmd, DataSet ds)

        {

            DataTable dt;

            try

            {

                SqlDataAdapter ad = new SqlDataAdapter();

                con.Open();

                ad.SelectCommand = cmd;

                ad.Fill(ds);

                dt = ds.Tables[0];

                return dt;

            }

            catch

            {

                con.Close();

                con.Dispose();

                SqlDataAdapter ad = new SqlDataAdapter();

                con.Open();

                ad.SelectCommand = cmd;

                ad.Fill(ds);

                dt = ds.Tables[1];

                return dt;

                //return result;

            }

            finally

            {

                con.Close();

                con.Dispose();

            }

        }

    }

}

CONCLUSION

In this post we have seen how to create connection string in Web.config file and access it in controller. The advantage of having connection string in Web.config file is we can change it in later future if our data base or server changes without affecting our code. That reduces our effort.

FEEDBACK

Hope the post will be useful for you in your programming career. What I need from you is to comment below and share with me your doubts and/or suggestions. Thank you :) Have a wonderful programming.