Where should i write database fetching line of codes ? directly writing in controller or by creating another data access layers?

Question

Where should i write database fetching line of codes ? directly writing in controller or by creating another data access layers ? //sample code

string cmdString = "select id from testdata where id=@val1 and name=@val2";
        string connString = @"";

        using (SqlConnection conn = new SqlConnection(connString))
        {
            using (SqlCommand comm = new SqlCommand())
            {
                comm.Connection = conn;
                comm.CommandText = cmdString;
                comm.Parameters.AddWithValue("@val1", c2.id);
                comm.Parameters.AddWithValue("@val2",c2.name);
                conn.Open();

            }
       }

Short answer: inside controller action method. Model classes represent data model & viewmodel (data model properties are ORM-based, you should use ORM like EF/Dapper instead `SqlConnection` to retrieve data). — Tetsuya Yamamoto, Oct 19 '17 at 04:58
I would like to suggest that the best approach in your scenario is create another project which will implement all database related tasks and access it from controller and bind data with Models. — mmushtaq, Oct 19 '17 at 05:22
@mmushtaq can you please elaborate this idea or give me a tutorial ? — Midhul, Oct 19 '17 at 05:51

Jeremy Thompson · Answer 1 · 2017-10-24T04:20:33.313

A few things;

a) This is an AdHoc query not a Stored Procedure.

b) It's more common to pass the Conn to the SqlCommand(Conn) on instantiation

c) Don't use AddWithValue as per https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

d) This is a SQL SELECT but you're not populating a data reader, adapter or etc.

To answer your question:

write database connectivity line of codes? is it in model or in controller?

Typically you put the Data Access Layer in a Repository and using Dependency Injection (when creating controllers) you can mock the database calls in your tests.

It's the way WebAPI & etc do it. Otherwise if you're using Entity Framework, just put the DAL in the Controller.

score 0 · Answer 2 · answered Oct 19 '17 at 05:00

If you had to choose between model or controller, it is better to have them in the model. Controllers are meant to just have the website related codes. Models are meant to manage the data. Having them in the models and having the model as a separate project also helps in a lot of code reuse in case you are planing on more websites or a separate web api.

However generally the structure used includes a core project (basic class structure), a dataAccess project (the connection to the database), business project with contains the business logic. And finally the models can extend the business classes.

Even if you don't plan on using these layers, I would still recommend you use another library to create the classes that fetch data from the db and then extend them to create your models. This will allow you to have different models with different validations when needed.

Thank you for answer.Most of the Tutorials on internet using Entity Framework .since I'm leaning mvc from scratch (using webforms for last 3 years) should i use EF over this approach ? — Midhul, Oct 19 '17 at 05:35
i have personally never used it. it does make life easier, however whenever i looked into EF I found it would restrict the flexibility of my structure. For instance using the above structure, I can share the core project with xamarin. I can not do this if i use EF and have to extend classes for creating all objects. I have also heard of complaints such as being heavy. One of the preferred approaches is also creating stored procedures in the database. — Neville Nazerane, Oct 19 '17 at 05:40

Where should i write database fetching line of codes ? directly writing in controller or by creating another data access layers?

2 Answers2