mardi 27 décembre 2016

c# Entity Framework when should I use an new dbContext?

I was wondering for some time what is the proper way to make a new instance of an dbcontext? I got some problem with it because when I make up change in my database through SQL Server my context doesn't update the data. Let me explain how my website work.

We are doing an appointment website for our customer to take appointment obviously. we will hosting all database on our server. How it work is the application made up 2 connection:

first connection

this connection connect all the time to the same database let's call it master. It'll redirect the user to the good database with the url code in it example: www.example.com/foo the server will check for the code where here is foo So it'll lookup in the table to matchup the code and then take the good database name where it should redirect and it's here that my second connection come's up

Second connection

This one will make the connection to the correct database according to the data the master has return. From here all seems to work well except for the DBContext that actually never update because I don't instantiate it correctly and I don't have an large experience with it. Here's the code i did with my coworker:

using System;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;
using System.Web;
using System.Web.Routing;
using WebRV.Models.Entities;

namespace WebRV.RouteDb
{

    public class DbConnection
    {

        private static DbConnection instance;
        private Cliniciel_WebRV_Entities db;
        private String connectionString;
        private readonly Cliniciel_WebRV_MasterEntities masterDb = new Cliniciel_WebRV_MasterEntities();
        private Int32 idCie;
        private static readonly object myLock = new object();


        private DbConnection() {
            var context = new HttpContextWrapper(System.Web.HttpContext.Current);
            var routeData = RouteTable.Routes.GetRouteData(context);
            // Use RouteData directly:
            String code = routeData.Values["code"].ToString();
            //String  code = Thread.CurrentContext. .RequestContext.RouteData.Values["code"].ToString();
            var response = masterDb.SYS_tbCustDBLocation.Where(p => p.CustDBLocationCode == code).ToList();

            if (response.Count == 1)
            {
                try
                {
                    db = CreateConnection(response.FirstOrDefault());
                    idCie = (db.SYS_vwCie.Where(p => p.ClinicielWebName == code).FirstOrDefault()).IdCie;
                }
                catch (Exception e)
                {
                    throw e;
                }

            }
            else {
                throw new FormatException();
            }
        }

        private Cliniciel_WebRV_Entities CreateConnection(SYS_tbCustDBLocation data)
        {

            connectionString = *****

            db = new Cliniciel_WebRV_Entities();

            db.Database.Connection.ConnectionString = connectionString;

            db.Database.Connection.Open();

            return db;
        }

        private static void CreateInstance() {
            instance = new DbConnection();
        }

        public static DbConnection GetInstance() {
            lock (myLock)
            {
                if (instance == null)
                {
                    CreateInstance();
                }

            }

            return instance;
        }

        public String GetConnectionString()
        {
            return connectionString;
        }

        public Cliniciel_WebRV_Entities GetConnection()
        {
            return db;
        }

        public Int32 GetIdCie()
        {
            //hard code 1 but test
            //return idCie;
            return 1;
        }

    }
}

and here's an example of how I use it:

  //[CompanyCodeFilter]
    public class HomeController : AppointementController 
    {
        //public static Cliniciel_WebRV_Entities entityDB = DbConnection.GetConnection();

        public HomeController()
        {
            base.Refresh();
        }

 public JsonResult GetConsultationDescription(Int32 idService)
        {
            //base.Refresh();
            entityDB.Set<SYS_vwService>().AsNoTracking();
            var motifDescription = entityDB.SYS_vwService.Where(s => s.IDLang == cultureName && s.IdService == idService && s.IdCie == idCie).FirstOrDefault();
            var base64 = Convert.ToBase64String(motifDescription.ServiceImage);
            var imgSrc = String.Format("data:image/gif;base64,{0}", base64);
            var imageDecode = imgSrc;
            if (base64 == "AA==")
            {
                imageDecode = "";
            }
            var result = new { motifDescription, imageDecode };


            return Json(result, JsonRequestBehavior.AllowGet);
        }
  }

Here base.refresh() call this:

using System;
using System.Linq;
using WebRV.Filters;
using WebRV.Localization;
using WebRV.Models.Entities;
using WebRV.RouteDb;

namespace WebRV.Controllers
{
    //[CompanyCodeFilter]
    public class AppointementController : BaseController
    {
        protected Cliniciel_WebRV_Entities entityDB;
        protected Int32 idCie;
        protected String cultureName;

        //public AppointementController() {
        //    Refresh();
        //}

        //Permet de bien vérifier quel DB utilisé, quel idCie et quel cultureName.
        protected void Refresh() {
            entityDB = DbConnection.GetInstance().GetConnection();
            idCie= DbConnection.GetInstance().GetIdCie();
            cultureName = CultureLocalization.GetCulture();
            //cultureName = "en-ca";
        }

    }
}

If someone can help me to instantiate the connection properly it'll be appreciate thank you

Aucun commentaire:

Enregistrer un commentaire