3

i am fairly new in ASP.Net MVC. in my project i am not using Entity Framework rather i am using ADO.net. here is a code for my view model design. please have a look.

Action

public class WebGrid_Sample1Controller : Controller
    {
        // GET: WebGrid
        public ActionResult Show1(StudentVm oSVm)
        {
            StudentVm SVm = new StudentVm(); //.GetStudents(oSVm);
            SVm.Students= SVm.GetStudents(oSVm);
            return View(SVm);
        }
    }

My Model and View model code

public class StudentVm
    {
        public int page { get; set; }
        public int RowCount { get; set; }
        public int PageSize { get; set; }
        public int CurrentPage { get; set; }

        public string sort { get; set; }
        public string sortdir { get; set; }

        public IList<Student> Students { get; set; }

        public StudentVm()
        {
            PageSize = 5;
            sort = "ID";
            sortdir = "ASC";
            CurrentPage = 1;
        }

        public IList<Student> GetStudents(StudentVm oSVm)
        {
            int StartIndex = 0, EndIndex = 0;

            if (oSVm.page == 0)
                oSVm.page = 1;

            StartIndex = ((oSVm.page * oSVm.PageSize) - oSVm.PageSize) + 1;
            EndIndex = (oSVm.page * oSVm.PageSize);
            CurrentPage = StartIndex;

            if (string.IsNullOrEmpty(oSVm.sort))
                oSVm.sort = "ID";

            if (string.IsNullOrEmpty(oSVm.sortdir))
                oSVm.sortdir = "ASC";

            string connectionStringName = System.Configuration.ConfigurationManager.ConnectionStrings["StudentDBContext"].ConnectionString;
            IList<Student> _Student = new List<Student>();

            string strSQL = "SELECT ID, FirstName,LastName,IsActive,StateName,CityName FROM vwListStudents WHERE ID >=" + StartIndex + " AND ID <=" + EndIndex;
            strSQL += " ORDER BY " + oSVm.sort + " " + oSVm.sortdir;

            strSQL += ";SELECT COUNT(*) AS Count FROM vwListStudents";
            using (SqlConnection connection = new SqlConnection(connectionStringName))
            {
                SqlCommand command = new SqlCommand(
                  strSQL, connection);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        _Student.Add(new Student()
                        {
                            ID = Convert.ToInt32(reader["ID"].ToString()),
                            FirstName = reader["FirstName"].ToString(),
                            LastName = reader["LastName"].ToString(),
                            IsActive = Convert.ToBoolean(reader["IsActive"]),
                            StateName = reader["StateName"].ToString(),
                            CityName = reader["CityName"].ToString()
                        });
                    }
                }

                reader.NextResult();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        RowCount = Convert.ToInt32(reader["Count"].ToString());
                    }
                }

                reader.Close();
            }
            //RowCount = _Student.Count;
            return _Student;
        }
    }

Model

public class Student
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool IsActive { get; set; }
    public string StateName { get; set; }
    public string CityName { get; set; }
}

some one review my view model code and said View models should not contain implementation. View Models are container that pass data between client, controller, and View.

he gave a new partial design of vm code 

public class StudentVm
{
    public int page { get; set; }
    public int RowCount { get; set; }
    public int PageSize { get; set; }
    public int CurrentPage { get; set; }
    public string sort { get; set; }
    public string sortdir { get; set; }
    public IList<Student> Students { get; set; }
}

public class Student
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool IsActive { get; set; }
    public string StateName { get; set; }
    public string CityName { get; set; }
}

public ActionResult Index()
{
    return View();
}

[HttpPost]
public ActionResult Show1(StudentVm oSVm)
{
    return View(oSVm);
}

so my question is where to put the db interaction routine ?

i am talking about this line

public IList<Student> GetStudents(StudentVm oSVm)
        {
            int StartIndex = 0, EndIndex = 0;

            if (oSVm.page == 0)
                oSVm.page = 1;

            StartIndex = ((oSVm.page * oSVm.PageSize) - oSVm.PageSize) + 1;
            EndIndex = (oSVm.page * oSVm.PageSize);
            CurrentPage = StartIndex;

            if (string.IsNullOrEmpty(oSVm.sort))
                oSVm.sort = "ID";

            if (string.IsNullOrEmpty(oSVm.sortdir))
                oSVm.sortdir = "ASC";

            string connectionStringName = System.Configuration.ConfigurationManager.ConnectionStrings["StudentDBContext"].ConnectionString;
            IList<Student> _Student = new List<Student>();

            string strSQL = "SELECT ID, FirstName,LastName,IsActive,StateName,CityName FROM vwListStudents WHERE ID >=" + StartIndex + " AND ID <=" + EndIndex;
            strSQL += " ORDER BY " + oSVm.sort + " " + oSVm.sortdir;

            strSQL += ";SELECT COUNT(*) AS Count FROM vwListStudents";
            using (SqlConnection connection = new SqlConnection(connectionStringName))
            {
                SqlCommand command = new SqlCommand(
                  strSQL, connection);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        _Student.Add(new Student()
                        {
                            ID = Convert.ToInt32(reader["ID"].ToString()),
                            FirstName = reader["FirstName"].ToString(),
                            LastName = reader["LastName"].ToString(),
                            IsActive = Convert.ToBoolean(reader["IsActive"]),
                            StateName = reader["StateName"].ToString(),
                            CityName = reader["CityName"].ToString()
                        });
                    }
                }

                reader.NextResult();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        RowCount = Convert.ToInt32(reader["Count"].ToString());
                    }
                }

                reader.Close();
            }
            //RowCount = _Student.Count;
            return _Student;
        }

so my request is anyone can restructure my code with db interaction routine. how to design the whole code including viewmodel, model and db interaction routine. i am using ADO.Net. so far whatever sample article i read to restructure my code all project sample using EF. so please some one restructure my code with db interaction routine. thanks in advance.

Improve this question

2 Answers 2

Reset to default
2

Do not mix your data access code within your view model. That defeats the whole purpose of seperation of concern. The whole idea of introducing view models are to not mix your ORM side entities to your view layer.

Your view models should be lean-flat POCO/DTO classes. These DTO's are for transfering data between one layer to another. It should not know where to get data from.

You should have another layer/class which will give you the data and you will map the entites to your view models. You can use a Mapping library like Automapper to do that if you do not prefer to do the manual mapping.

You can design your application/layers in so many different way as per the complexity of your app. A Quick and simple implementation would be like

YourProject.Common : This project stores DTO's/POCO's in your project. If you want,you can keep your view models here as well as needed.

YourProject.Data : This project has a reference to your Common Project and returns data in the form of those DTO classes.

YourProject.Web/UI : Your Web/API project. This will refer the Data project and the Commin project and call the data access methods to get the data. You may do the property mapping(read from entity and set to view models) here.

You may add more layers to the stack, like a Business/Service layer between your UI and Data Access to do some business logic / Mapping etc..

Use layering wisely, If you blindly try to create many layers which is really not needed, you will end up with a mess.

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

6 Comments

can u please discuss with sample code or redirect me to few right article which give me knowledge how to loosely couple view model code and db interaction code with ado.net.
i am not using EF in my project rather using plain old ADO.Net
Does not matter what your Data access technology is, It can be ADO.NET or EF. You should not be mixing that in your view model
i wnat to see a live example of such. can u give me any relevant link where people use MVC and ado.net together instead of EF.
Might not be the best one, but take a look at this github repo github.com/kshyju/ProjectPlanningTool/tree/master/src
|
0

The first thing you need to do is to move the db-access code out of your view model and into a repository class. See my example below:

public class WebGridSampleController
{
    private StudentRepository _data;

    public WebGridSampleController()
    {
        _data = new StudentRepository();
    }

    public ActionResult Show1(StudentVm oSVm)
    {
        var students = _data.GetStudents(oSVm.page, oSVm.pageSize, oSVm.sort, oSVm.sortDir);
        oSVm.Students = students.ToList();
        return View(oSVm);
    }
}

public class StudentRepository
{
    public IEnumerable<Student> GetStudents(int page, int pageSize, string sort, string sortDir)
    {
        // Put the code that you have in StudentVM.GetAllStudents here
    }
}

There are other issues with the code that you posted. I think most of them comes down to improper naming of classes and variables. For example: what is the view here? It is not viewing details of a particular student, is it?

So, StudentVm (or, StudentViewModel), should probably be named something like StudentListViewModel. Also, the Controller should probably be named StudentController, as it is controlling the viewing of students. Moreover, the action Show1, should be called something along the lines of List, in my opinion.

With these things in mind, the example can be updated as follows:

public class StudentController
{
    private StudentRepository _data;

    public StudentController()
    {
        _data = new StudentRepository();
    }

    public ActionResult List(StudentListViewModel viewModel)
    {
        var students = _data.GetStudents(viewModel.Page, viewModel.PageSize, viewModel.Sort, viewModel.SortDirection);
        viewModel.Students = students.ToList();
        return View(viewModel);
    }
}

public class StudentRepository
{
    public IEnumerable<Student> GetStudents(int page, int pageSize, string sort, string sortDir)
    {
        // Put the code that you have in StudentVM.GetAllStudents here
    }
}

We could also adress the topic of Dependency Injection, but that seems like pushing it a bit too far at the moment. Also, I would not bother creating a project for each "layer" at this point. Just make sure that your model and view model does not reference any data access stuff.

Improve this answer

3 Comments

thanks for your nice answer. where to put this code StudentRepository in model folder of same project or in separate project where i will have all Repositories for student, class, staff etc ?
At first, I would put them in a new directory called 'Data'. This can then be moved to a separate project when it is needed.
i am rewriting db interaction code. i will post my full code here and would you mind to review my code and design?.