Basic App Design - SQL Server / MVC 4 / No EF RRS feed

  • Question

  • User815882678 posted

    I have a simple application that returns SQL Server data, but feel like I have used many bad practices. Can someone please review my code and give me some input on what I can improve? My code is below.


    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    namespace AllanTest01.Models
        public class LoginHistory
            public Int32 LoginHistoryID { set; get; }
            public Decimal LoginDuration { set; get; }
            public string LoginApp { set; get; }
            public DateTime LoginDateTime { set; get; }
            public string LoginUser { set; get; }
            public string LoginLocation { set; get; }
            public string LoginEnvironment { set; get; }


    using AllanTest01.Models;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    namespace AllanTest01.Controllers
        public class HomeController : Controller
            // GET: /Home/
            public ActionResult Index()
                var items = GetLoginHistory();
                var newItems = (from p in items
                                select new LoginHistory
                                    LoginHistoryID = p.Field<Int32>("LoginHistoryID"),
                                    LoginDuration = p.Field<Decimal>("LoginDuration"),
                                    LoginApp = p.Field<String>("LoginApp"),
                                    LoginDateTime = p.Field<DateTime>("LoginDateTime"),
                                    LoginUser = p.Field<String>("LoginUser"),
                                    LoginLocation = p.Field<String>("LoginLocation")
                return View(newItems);
            private List<DataRow> GetLoginHistory()
                List<DataRow> list = null;
                string srtQry = @"Select LoginHistoryID, LoginDuration, LoginApp, LoginDateTime, LoginUser, LoginLocation, LoginEnvironment
                                  From LoginHistories";
                string connString = "Data Source=.\\sqlexpress;Initial Catalog=AllanTest;Integrated Security=True";
                using (SqlConnection conn = new SqlConnection(connString))
                    using (SqlCommand objCommand = new SqlCommand(srtQry, conn))
                        objCommand.CommandType = CommandType.Text;
                        DataTable dt = new DataTable();
                        SqlDataAdapter adp = new SqlDataAdapter(objCommand);
                        if (dt != null)
                            list = dt.AsEnumerable().ToList();
                return list;


    @model IEnumerable<AllanTest01.Models.LoginHistory>
        Layout = null;
    <!DOCTYPE html>
        <meta name="viewport" content="width=device-width" />
        <!-- Kendo References -->
        <link rel="stylesheet" href="~/Content/kendo.common.min.css">
        <link rel="stylesheet" href="~/Content/kendo.default.min.css">
        <link href="~/Content/kendo.dataviz.min.css" rel="stylesheet" type="text/css" />
        <script src="~/Scripts/jquery.min.js"></script>
        <script src="~/Scripts/kendo.all.min.js"></script>
        <script src="~/Scripts/kendo.aspnetmvc.min.js"></script>
            Login History Charts 01
            <br /><br />
            <br />
            Date Range: @(Html.Kendo().DateTimePicker().Name("StartDateTime")) - @(Html.Kendo().DateTimePicker().Name("EndDateTime"))
            <br /><br />
            <table style="width:100%;">
                        @* loop thru the apps *@
                        @foreach (var item in Model)
                                                .Name(item.LoginApp.ToString().Replace(" ", "_"))
                                                .Legend(legend => legend
                                                .Series(series =>
                                                    series.Column(new double[] { 15.7, 16.7, 20, 23.5, 26.6 }).Name("World");
                                                    series.Column(new double[] { 67.96, 68.93, 75, 74, 78 }).Name("United States");
                                                .CategoryAxis(axis => axis
                                                    .Categories("2005", "2006", "2007", "2008", "2009")
                                                .ValueAxis(axis => axis
                                                    .Numeric().Labels(labels => labels.Format("{0}%"))
                                                .Tooltip(tooltip => tooltip
                                                .HtmlAttributes(new { style = "width:300px;" })
            @foreach (var item in Model)
       <p>@item.LoginHistoryID | @item.LoginDuration | @item.LoginApp | @item.LoginDateTime | @item.LoginUser | @item.LoginLocation</p>

    Monday, January 28, 2013 11:04 AM

All replies

  • User-525215917 posted

    Most important thing to start with is separating data access code from user interface (read: separate Data Access Layer from Presentation Layer). You can find some reading here: http://blog.lowendahl.net/?p=249. 

    I still suggest you to check out Entity Framework as it works as mapper between your database and class model. It makes a lot of dorty and very complex work for you and it's free.

    If you are left with just ADO.NET then you have to write all those components by yourself and to avoid mess and solve different problems related to data access... well... it needs years of experience. I suggest you to start with implementing repositories but implement only couple of these. Then try to use them and see if you run into problems or not. You can actually go pretty far with your own code but be careful - the more you have done the harder is to change the existing code.

    Monday, January 28, 2013 1:48 PM
  • User815882678 posted

    Thanks for the reply. I have tried EF on this application, but it seems to be causing more harm than good. One issue is that the SQL DB already exists. And, the tutorials for the EF with an existing database can not be followed since there is a bug with the Reverse Engineering Power Tools.

    Monday, January 28, 2013 1:52 PM
  • User-525215917 posted

    Well... EF is sometimes fuzzy to get started with. Actually I have never used these power tools although these tools have been here for some time already. You can always add new entity data model to your application and let it generate classes from database. In model editor you can change class and property names if you need. If you don't like automatically generated classes you can always use your own classes. Just make sure you make properties as virtual or lazy loading doesn't work.

    My personal favorite in mappers world is NHibernate. Yes, it has longer learning curve than EF and it is more complex to use but this far I have lived with it conveniently.

    Actually it doesn't matter which one of them you start using - it takes a little experimenting to find out how mapper works and how convenient or understandable it is for you. Some people live better with EF and others with NHibernate. :)

    Monday, January 28, 2013 2:03 PM
  • User815882678 posted

    Fuzzy is an understatement.

    Today, if I could just get a simple KendoUI column chart to work with MVC4 and SQL Server I would be happy.

    I have a cshtml page with some data and I have the KENDO UI chart on the page. But, hooking the data up to the chart is difficult. And, I have not been able to find a clear simple example of getting my series data and binding it to the chart.

    Monday, January 28, 2013 2:08 PM
  • User-525215917 posted

    I think the question falls then to what is up with Kendo UI components. Mappers are different opera. 

    Check out all data that you actually give to chart component. You can always use developer tools your browser offers to find out UI level issues like JavaScript errors etc.

    Monday, January 28, 2013 2:13 PM