Implement data access layer best practices in .net Project MVC

Question

I would like to improve my .NET project by adding another layer when accessing the database. This is my code:

namespace Company.Models
{
public static class AgencyBean
{
[WebMethod]
    [ScriptMethod(UseHttpGet = true)]
    public static String createGUID(string name)
    {
       DataAccess dataAccess = new DataAccess();
       bool exists = dataAccess.checkIfExists(Id);
       if(exist)
       {
           dataAccess.delete(Id);
       }
       retur "ok";
    }
 }
}

I placed DataAccess class in a separate folder called "Helpers" and it contains most of my queries:

public class DataAccess
 {    
public bool checkIfExists(String Id)
    {
        try
        {
            SqlConnection cnn = new SqlConnection(dataConnection);
            cnn.Open();
            SqlCommand check_Id = new SqlCommand("SELECT COUNT(*) FROM TABLE_GUID WHERE ([USER_ID] = @Id)", cnn);
            check_Id.Parameters.AddWithValue("@Id", Id);
            int UserExist = (int)check_Id.ExecuteScalar();

            if (UserExist > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (SqlException ex)
        {
            Debug.WriteLine("SQL Exception " + ex);
            DisplaySqlErrors(ex);
            throw ex;
        }
    }
 }

public class AgentBeanController : Controller
{
    // GET: AgentBean
    public ActionResult Index(string name)
    {
        return View();
    }

    [AllowAnonymous]
    [WebMethod]              
    public string AgentURL()  //here we create Agent URL and return it to the view
    {
        string var = Models.AgentBean.createGUID("TODO");            
        return var;           
    }


}

I'm accessing the database pretty much in very direct way. How would it be with a better technique, so this access can be more secure, like accessing thru a service layer? I'm connecting to a existing sql database in some server and working with MVC architecture in my project.

You are on the right track. If you Google the term "Data Repositories" and "Dependency Injection", it may take you a step further. The general idea of a repository is that you have separate classes in your model layer to access the data (like you do now,) but they conform to interfaces. When you instantiate your AgencyBean class, you would accept an object that implements that interface as an argument. The benefit is that you can have a 2nd implementation of the interface that doesn't touch the database that is used only for unit testing. A DI tool like Ninject makes this easier to code. — Dave Smash
– Dave Smash, Commented Oct 17, 2016 at 20:28
Now I understand it better, I also wanted to implement unit testing and I see that I need DI. thanks for the advise! — Rolando F
– Rolando F, Commented Oct 18, 2016 at 14:35

dvsoukup · Accepted Answer · 2016-10-17 20:56:09Z

1

So here is what I have done in the past.

First, that is your "models" namespace... models should never have database connectivity. Instead you have a seperate class, such as a controller, that hydrates some model.

Second, I've had a "service" class, which hooks up to a "repository" class. The repository class implements an interface to identify the exact "type" of database you're using.. but if that's not a part of your requirements you probably don't need to go that far.

Third, look up dependency injection (aka, DI). There are several frameworks out there. Personally I've used Autofac, but others exist as well to get the job done easier.

Fourth, on your your "controllers", "services" and "respository" classes, implement dependency injection, as well as any interfaces as needed to form a contract.

Fifth, I would use an actual controller namespace and not be working out of your models namespace to be pushing http calls band and forth.... Instead, create an action in your controller class, and instantiate an instance of your "agencyBean", hydrate it with data, and return that model out to your view.

Basically, in a scenario like this you're trying to keep each component doing what it is designated to do... breaking down responsibilities into smaller pieces and focusing on that. Your controller should just "fetch" your model and maybe do some transformations on it as needed or any other business-type logic.
Your service should handle the communication between your controller and your database layer.
Your data access layer (ie, in this case, some "repository" class...) would do all of those new data connections and/or setting up calls to stored procedures or queries.

Doing things this way has a lot of benefit. Some of the big ones are maintainability, readability, code re-use. Sure it makes your project a bit more complicated in terms of files sitting wherever... but that can be a good thing. It's so much better than slamming everything into one single class and have it do everything :)

But, just FYI, this is from an implementation I've done in the past... I'm sure there are better ways but this setup worked quite well for my team and I.

Here is a small example using some of your code you posted. I DID NOT check this for typos and it wouldn't compile, but should help give a general idea of what I'm talking about....

namespace Company.Models
{
    public class AgencyBean
    {
        public AgencyName{get;set;}
        public AgencyId{get;set;}
        // other properties...
    }
}




namespace Company.Controllers
{
    public class MyController : Controller
    {
        private readonly IMyService myService;


        public MyController(IMyService myService) // <-- this is your dependency injection here...
        {
            this.myService = myService; 
        }

        [WebMethod]
        [ScriptMethod(UseHttpGet = true)]
        public static String createGUID(string name)
        {
            var model = new AgencyBean();
            model.AgencyId = 1;
            model = myService.getAgency(agencyBean);            
            return model;
        }
    }
}




namespace Company.Services
{
    public class MyService
    {
        private readonly IMyRepository myRepository;


        public MyService(IMyRepository myRepository) // <-- this is your dependency injection here...
        {
            this.myRepository = myRepository; 
        }

        public AgencyBean getAgency(AgencyBean model){
            var dataTable = myRepository.getAgencyData(model.AgencyId);
            // fill other properties of your model you have...
            // ...
            // ...
            return model;
        }
    }
}



namespace Company.Repositories
{
    public class MyRepository : IDatabaseCommon  // <-- some interface you would use to ensure that all repo type objects get connection strings or run other necessary database-like setup methods...
    { 
        private readonly String connectionString{get;set;}

        public MyRepository()
        {
            this.connectionString = //get your connection string from web.config or somewhere else...; 
        }

        public DataTable getAgencyData(int id){
            var dataTable = new DataTable();

            // perform data access and fill up a data table

            return dataTable;
        }
    }
}

edited Oct 17, 2016 at 20:56

answered Oct 17, 2016 at 20:38

dvsoukup

1,60616 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rolando F Over a year ago

I read somewhere that my business logic should be in the model and controller should be as simple as possible, that's why I started coding this way.. then I found a similar post where people recommended to have a service layer as you pointed me. So, in your example, IMyservice is an interface located in a new file which contains my queries, right? I'm just kinda confused on where to locate certain files since I don't want to "break" this MVC pattern...

dvsoukup Over a year ago

IMyService is an interface you create in another file yes. But it's NOT where you would write your queries and what not. If you're wanting to write a bunch of SQL commands, I'd recommend another "model" class, such as "QueryModel", and it would be a bunch of constants that return your sql strings. But really, I don't personally do that. Rather I use stored procedures/functions at the database level, and call on those from the application code. The db stored procs/functions will contains all your SQL.

Rolando F Over a year ago

Now I undestand your point, I send the model as parameter, populate it in MyService using myRepository with DI and then return the model. One more question, I have installed Unity DI from Nugget, but getting parameterless error, should I add something manually to my Application_Start() or it is something that should be added by the installer?

dvsoukup Over a year ago

Usually for those DI frameworks you need to add a new reference to your project... at least I know you do for Autofac. I've never personally used the Unity DI, but I'm sure they have some sort of "getting started" guide. I believe that is the "built in" di framework that Microsoft provides. Here is a good walkthrough of using Unity. geekswithblogs.net/danielggarcia/archive/2014/01/23/…

Collectives™ on Stack Overflow

Implement data access layer best practices in .net Project MVC

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related