How to refactor the connectionString in my Entity Framework & ASP.NET MVC project?

Question

I have a large number of stored procedures to work with and I have to work with Entity Framework.

I got for example this controller where I'm just calling the database to show my table:

public class CarguioController : Controller
{
    public ActionResult Index(int? page)
    {
        string cs = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        using (SqlConnection conn = new SqlConnection(cs))
        {
            // establece conneción
            SqlParameter param1 = new SqlParameter();
            param1.ParameterName = "@MODO";
            param1.SqlDbType = SqlDbType.Int;
            param1.Value = 2;

            SqlCommand cmdProcedure = new SqlCommand(@"Almacen.[PRC_Carguio]", conn);
            cmdProcedure.Parameters.Add(param1);

            conn.Open();

            cmdProcedure.CommandType = CommandType.StoredProcedure;

            SqlDataReader dr = cmdProcedure.ExecuteReader();
            List<CarguioViewModel> lst = new List<CarguioViewModel>();

            int pageNumber = page ?? 1;
            int pageSize = 8;

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    lst.Add(new CarguioViewModel
                    {
                        Carguio_ID = dr.GetInt32(0),
                        Vehiculos_ID = dr.GetInt32(1),
                        ManifiestoCarga_ID = dr.GetInt32(2),
                        Guia_ID = dr.GetInt32(3),
                        Programaciones_ID = dr.GetInt32(4),
                        Numero = dr.GetInt32(5),
                        NroMobil = dr.GetString(6),
                        Fecha = dr.GetDateTime(7),
                        Usuarios_ID = dr.GetInt32(8),
                        Sucursales_IS = dr.GetInt32(9)
                    });
                    //display retrieved record
                }

                return View(lst.ToPagedList(pageNumber, pageSize));
            }
            else
            {
                Console.WriteLine("No data found.");
            }

            dr.Close();
            conn.Close();
        }

        return View();
    }
}

As you can see, I have to connect with the SQL Server database many times. Maybe you have done a similar job with ASP.NET MVC projects or have any idea to refactor my code?

I have more than 30 tables and everyone has more a Crud and other functions.

I've been searching for this but there is just the same example.

string cs = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

Answer 1

You can get create in General Utility class to read the connection, so that It is stay in one place in the code and read connection value from the Genral Utility class wherever you need it.

void Main()
{
    string cn = GeneralUtility.getConnectionString();
}

public class GeneralUtility
{
    public static string getConnectionString()
    {
        string cs = "";
        try
        {
            cs = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        }
        catch (Exception ex)
        {   
            throw new Exception("Connection String Error " + ex.Message.ToString());
        }
        return cs;
    }
}

Answer 2

I added a new element called ADO.Net Entity Data Model, where I retrieve all my Stored Procedures, It is helpful

I added a new element called ADO.Net Entity Data Model

Well, now my code is shorter than before:

public ActionResult Index(int? page)
    {
        List<CarguioModel> lst = new List<CarguioModel>();

        int pageNumber = page ?? 1;
        int pageSize = 8;

        using (MarviBKPEntities prcAlm = new MarviBKPEntities())
        { 
            List<PRC_Carguio_Result> prc = prcAlm.PRC_Carguio(2, null, null, null, null, null, null, null, null, null, null).ToList();
            return View(prc.ToPagedList(pageNumber, pageSize));
        }

            
        return View();
    }

Whta do you think? Could it cause some bad performance?