Fill Dropdown control from database in MVC 4/5 using ADO.NET without Entity Framework

Dear Reader, today we will see that how can we fill dropdown control from Database using ADO.NET without Entity Framework in MVC 4/5 application. I am using SQL Server for database and Visual Studio 2015 IDE for coding.

This blog is especially for those programmers who have not used Entity Framework till now and are into the habit of using old ADO.NET framework for database coding.

Note: Here I am considering that you are aware of basics of MVC application creation such as View, Model and Controller. If not, Please get some basics of MVC First Application.

So, let’s start with an example.

First of all, create a table in your database which contain data to show in the dropdown control. Here I am going to show list of countries in dropdown. Table name is CountryList.

Data is:

Create Model-

public class ShowCountry

    {

        public int CountryID { get; set; }

        public string cname { get; set; }

        public List<ShowCountry> CountryData { get; set; }

    }

Now create an action method in controller (I have taken HomeController). The code is –

public ActionResult ShowCountry()

        {

            DataTable dt = DAL.GetCountryData();

            List<ShowCountry> lst = new List<Models.ShowCountry>();

            foreach(DataRow dr in dt.Rows)

            {

                lst.Add(new Models.ShowCountry() {

                    CountryID = Convert.IsDBNull(dr["CountryID"]) ? default(int) : Convert.ToInt32(dr["CountryID"]),

                    cname = Convert.ToString(dr["Name"])

                });

            }

            ShowCountry objSC = new Models.ShowCountry();

            objSC.CountryData = lst;

            return View(objSC);

        }

In the above code, GetCountryData() is a method of DAL class which is used to retrieve the data from the database. It return DataTable type. DAL class is representing to Data Access Layer class. You can keep this code inside any class where you want. The code of this function is –

public static DataTable GetCountryData()

        {

            SqlConnection con = new SqlConnection("Data Source=myserver;Initial Catalog=DBName;Integrated Security=True");

            SqlDataAdapter da = new SqlDataAdapter("select * from CountryList", con);

            DataTable dt = new DataTable();

            da.Fill(dt);

            return dt;

        }

In the above code I have coded inline query to select data but I highly recommend to do it by using store procedures. And it is better to define your connection string inside web.config file. To shorten the code I have defined inside the function only.

Now create View by right click inside action and choose Add View… option then

The code of dropdown inside the View is-

<div>

        The List of Country is  :

        @Html.DropDownListFor(x => x.CountryID,new SelectList(Model.CountryData, "CountryID", "cname"),"-Select-")

    </div>

CountryData is the List type property of Model which contains List of countries coming from the database.

Build and run the project.

I hope it will be helpful for you.

Happy Coding.

Rudra Pratap Singh

Software Engineer

singhrudrapratap29[at]gmail[dot]com

How to Export data from database to Excel in MVC 4/5

Dear Readers, Today I am going to explain that how can we export data from database to Excel File in MVC 4/5 application. To understand this blog very clearly, please read this article first –

http://programmingwithrudra.blogspot.in/2016/05/how-to-search-data-by-dates-in-mvc-45.html

In the last application, our controller was Home. Action was SearchDataByDate, Model was SearchDataByDateModel and view was SearchDataByDate.cshtml. 

We retrieved data from the database in the last article according to the input starting and ending dates.

Now to export data, we will have to take following steps:

Step-1: First of all add reference in your project of name – ClosedXML.dll. Download it from here.

Step-2: Add the namespace in the controller - using ClosedXML.Excel;

Step-3: Open MS Excel in your computer system and save it inside your project with name Book1.xlsx. There is no need to create data inside the file, just save it.

Step-4: Refresh your project by using Solution Explorer.

Step-5: Now add an action method in the controller. The code is:

public ActionResult ExportData()

        {

            DateTime datefrom =Convert.ToDateTime(TempData["datefrom"]);

            DateTime todate = Convert.ToDateTime(TempData["todate"]);

            DataTable dt = DAL.GetOrderData(datefrom, todate);

            string filepath = System.Web.HttpContext.Current.Server.MapPath("~/Book1.xlsx");

            using (XLWorkbook wb = new XLWorkbook(filepath))

            {

                wb.Worksheets.Add(dt, "Order-Report");

// “Order-Report” is the name of auto generated worksheet

                wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

                wb.Style.Font.Bold = true;

                Response.Clear();

                Response.Buffer = true;

                Response.Charset = "";

                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                Response.AddHeader("content-disposition", "attachment;filename= OrderReport.xlsx");

                using (MemoryStream MyMemoryStream = new MemoryStream())

                {

                    wb.SaveAs(MyMemoryStream);

                    MyMemoryStream.WriteTo(Response.OutputStream);

                    Response.Flush();

                    Response.End();

                }

            }

            return RedirectToAction("SearchDataByDate");

        }

Step-5: Add a link in View to export data. The code is –

<p>@Html.ActionLink("Export This Data","ExportData")</p>

Build and Run the project.

I hope it will be helpful for you.

Happy Coding.

Find my YouTube channel from HERE.

Rudra Pratap Singh

Software Engineer

singhrudrapratap29[at]gmail[dot]com

Web Browser Snap Shots:

How to Search Data by Dates in MVC 4/5 using ADO.NET Without Entity Framework

Dear Readers, I going to explain how to search data from the database according to the input date and ending date in MVC application. I am using SQL Server for database and Visual Studio 2015 IDE.

This blog is especially for those programmers who have not used Entity Framework till now and are into the habit of using old ADO.NET framework for database coding.

Note: Here I am considering that you are aware of basics of MVC application creation such as View, Model and Controller. If not, Please get some basics of MVC First Application.

So, let’s start with an example.

First of all, create table (name it – OrderSampleData ) in your database according to the following figure:

 

Put some data in the table: (You can design your separate application for inserting data also)

Now create a model of name – SearchDataByDateModel . The code is:

public class SearchDataByDateModel

    {

        public int id { get; set; } // Property For order id

        public string itemname {get; set; } // Property for item name

        public DateTime orderdate { get; set; } // Property for orderdate

        public DateTime datefrom { get; set; } // Property for starting date

        public DateTime todate { get; set; } //Property for Ending Date

        // List for storing data which is coming from the DataBase

        public List<SearchDataByDateModel> orderdata { get; set; }       

    }

Descriptions are given in the comments(green color).

Now create action in Controller. I am taking Home Controller in this example.

[HttpGet]

        public ActionResult SearchDataByDate()// ---- For get request

        {

            SearchDataByDateModel obj = new SearchDataByDateModel(); // ---- Point-1

            obj.orderdata = null; // ---- Point-2

            return View(obj); // ---- Point-3

        }

        [HttpPost]

        public ActionResult SearchDataByDate(SearchDataByDateModel obj) // ---- For post request

        {

            DataTable dt = DAL.GetOrderData(obj.datefrom, obj.todate);// ---- Point-4

            List<SearchDataByDateModel> listorderdata = new List<SearchDataByDateModel>();// ---- Point-5

            foreach (DataRow dr in dt.Rows) // ---- Point-6

            {

                listorderdata.Add(new SearchDataByDateModel() {

                    id = Convert.ToInt32(dr["id"]),

                    itemname = Convert.ToString(dr["itemname"]),

                    orderdate = Convert.ToDateTime(dr["orderdate"])

                });

            }

            obj.orderdata = listorderdata; // ---- Point-7

            return View(obj); // ---- Point-8

        }

Descriptions:

Point-1: Creating object of model, so that we can access its properties like ‘id’, ‘itemname’ etc.

Point-2: We have a list type orderdata property in Model. We are setting it null for first time otherwise it will throw a null exception.

Point-3: We are passing object of Model with the view.

Point-4: We are using a method of DAL class to get data from the database. The code is:

public static DataTable GetOrderData(DateTime dtfrom, DateTime dateTo)

        {

            SqlConnection con = new SqlConnection("Data Source=servername;Initial Catalog=DBname;Integrated Security=True");

            SqlDataAdapter da = new SqlDataAdapter("select * from OrderSampleData where orderdate Between '" + dtfrom + "' AND '"+ dateTo +"'", con);

            DataTable dt = new DataTable();

            da.Fill(dt);

            return dt;

        }

I have used in-line query here but I highly recommend to use store procedure for retrieving data in place of in-line queries. To learn about basics of Store procedures, see my videos here.

Point-5: Creating an object of List type so that we can add data in it which is stored in the DataTable object.

Point-6: We are using foreach loop to fetch data from DataTable object and add data into the List object.

Point-7: We are storing the list object into the List property of Model Class. So that it can be accessed in the view.

Point-8: Sending Object of Model into the view.

Note: I recommend to define codes from Point-5 to Point-7 in the Separate class which should contain Business Logics. Or in other words we can say that BAL (Business Layer Class).

Now create view for this. Right click inside the action methods and choose Add View option.

Now put the code in following manner in your View:

@model MVC18042016.Models.SearchDataByDateModel

@{

    Layout = null;

}

<!DOCTYPE html>

<html>

<head>

    <meta name="viewport" content="width=device-width" />

    <title>Search Data By Date</title>

</head>

<body>

    @using (Html.BeginForm("SearchDataByDate", "Home", FormMethod.Post))

    {

        <div>

            Please Select Dates :

            Start Date @Html.TextBox("datefrom", Model.datefrom)

            End Date @Html.TextBox("todate", Model.todate)

            <input type="submit" name="submit" id="btnsubmit" value="Get Report" />

        </div>

        if(Model.orderdata != null)

        {

            <table border="1">

                <tr>

                    <th>Order ID </th>

                    <th>Order Item</th>

                    <th>Order Date</th>

                </tr>

                @foreach (var item in Model.orderdata)

                {

                    <tr>

                        <td>@Html.DisplayFor(modelItem =>item.id)</td>

                        <td>@Html.DisplayFor(modelItem => item.itemname)</td>

                        <td>@Html.DisplayFor(modelItem => item.orderdate)</td>

                    </tr>

                }

            </table>

        }

    }

</body>

</html>

In the above code,

if(Model.orderdata != null) is used to show the data only when the list is not null. If the list is null, the blocked code will not execute.

@foreach (var item in Model.orderdata) -  loop is used to iterate through the orderdata list.

@Html.DisplayFor – It is a helper which is used to display data in non-editable format.

Build and Run the project.

I hope it will be helpful for you.

Happy Coding.

Find my youtube channel from HERE.

Rudra Pratap Singh

Software Engineer

singhrudrapratap29[at]gmail[dot]com