Difference Between DataReader, DataSet, DataAdapter and DataTable in C#

DataReader

DataReader is used to read the data from database and it is a read and forward only connection oriented architecture during fetch the data from database. DataReader will fetch the data very fast when compared with dataset. Generally we will use ExecuteReader object to bind data to datareader.

To bind DataReader data to GridView we need to write the code like as shown below:

Protected void BindGridview()

{

using (SqlConnection conn = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test"))

{

con.Open();

SqlCommand cmd = new SqlCommand("Select UserName, First Name,LastName,Location FROM Users", conn);

SqlDataReader sdr = cmd.ExecuteReader();

gvUserInfo.DataSource = sdr;

gvUserInfo.DataBind();

conn.Close();

}

}

• Holds the connection open until you are finished (don't forget to close it!).
• Can typically only be iterated over once
• Is not as useful for updating back to the database

DataSet

DataSet is a disconnected orient architecture that means there is no need of active connections during work with datasets and it is a collection of DataTables and relations between tables. It is used to hold multiple tables with data. You can select data form tables, create views based on table and ask child rows over relations. Also DataSet provides you with rich features like saving data as XML and loading XML data.

protected void BindGridview()

{

    SqlConnection conn = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test");

    conn.Open();

    SqlCommand cmd = new SqlCommand("Select UserName, First Name,LastName,Location FROM Users", conn);

    SqlDataAdapter sda = new SqlDataAdapter(cmd);

    DataSet ds = new DataSet();

    da.Fill(ds);

    gvUserInfo.DataSource = ds;

    gvUserInfo.DataBind();

}

DataAdapter

DataAdapter will acts as a Bridge between DataSet and database. This dataadapter object is used to read the data from database and bind that data to dataset. Dataadapter is a disconnected oriented architecture. Check below sample code to see how to use DataAdapter in code:

protected void BindGridview()

{

    SqlConnection con = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test");

    conn.Open();

    SqlCommand cmd = new SqlCommand("Select UserName, First Name,LastName,Location FROM Users", conn);

    SqlDataAdapter sda = new SqlDataAdapter(cmd);

    DataSet ds = new DataSet();

    da.Fill(ds);

    gvUserInfo.DataSource = ds;

    gvUserInfo.DataBind();

}

• Lets you close the connection as soon it's done loading data, and may even close it for you automatically
• All of the results are available in memory
• You can iterate over it as many times as you need, or even look up a specific record by index
• Has some built-in faculties for updating back to the database.

DataTable 
DataTable represents a single table in the database. It has rows and columns. There is no much difference between dataset and datatable, dataset is simply the collection of datatables.

protected void BindGridview()

{

     SqlConnection con = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test");

     conn.Open();

     SqlCommand cmd = new SqlCommand("Select UserName, First Name,LastName,Location FROM Users", conn);

     SqlDataAdapter sda = new SqlDataAdapter(cmd);

     DataTable dt = new DataTable();

     da.Fill(dt);

     gridview1.DataSource = dt;

     gvidview1.DataBind();

}

How To Perform "CURD" Operation With Grid View Using ASP .NET MVC.

In this Article I will show you how to perform "curd" operation in MVC WebGrid.As per my previous article http://mvctpoint.blogspot.in/2015/10/how-to-bind-data-to-webgrid-in-aspnet.html that having an Action column and all row it having Edit Action.
Description:
In my previous article you have already Model,Connection,View,Controller also.When you run you application over the browser then Click in WebGrid Action Edit.It will Show you Delete,Update and Cancel.you can perform the following.
This Type of Grid show when you run your Application.

When you click on Edit then the output is :

you just right down the View Code as per my previous article.you just add only in your User or HomeController the following method.

 [HttpGet]
        public ActionResult InsertData()
        {
            return RedirectToAction("Demo");
        }

        [HttpPost]
        public ActionResult InsertData(string FirstName, string LastName, string Email, string Password, string Mobile)
        {


            SqlConnection con = new SqlConnection("Pass Your Connection String");

            con.Open();
            SqlCommand cmd = new SqlCommand("update Regist set FirstName=' " + FirstName + "',LastName = '" + LastName + "',Pass = '" + Password + "',Mobile ='" + Mobile + "' where Email ='" + Email + "' ", con);
            int i = cmd.ExecuteNonQuery();
            if (i == 1)
            {
                return RedirectToAction("Demo");
            }
            else
            {
                con.Close();
                con.Dispose();
                return View();
            }

        }
        [HttpPost]
        public ActionResult DeleteData(string FirstName, string LastName, string Email, string Password, string Mobile)
        {
            SqlConnection con = new SqlConnection("Pass Your Connection String");

            con.Open();
            SqlCommand cmd = new SqlCommand("delete from Regist where Email ='" + Email + "' ", con);
            int i = cmd.ExecuteNonQuery();
            if (i == 1)
            {
                return RedirectToAction("Demo");
            }
            else
            {
                con.Close();
                con.Dispose();
                return View();
            }
        }

What do you think?

I hope you will enjoy to perform "CURD" operation in WebGrid while programming with Asp.Net MVC. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

How to Bind Data to Webgrid in ASP.net MVC Using C#.Net

In This Article I will show you how to Bind Data to Webgrid in MVC. In this i will fetch data from database using Sql Query.And I Have a DropDown List if you selected any of value from list then webgrid shrink according to selected list value.
Before starting to read this article you must aware with partial view in mvc (how to work thak and how to create).

So for this article first we will create our table to add....

Once table created in database enter some dummy data to test application now right click your project in solution explorer write the code like as shown below.

Add Model just like this and model name UserModel you can choose any name of model,as my previous article i have already told you how to add model in MVC application(right click on Model folder--->ADD---->Class and write the name of class UserModel)--->
write the code in UserModel like this..

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.ComponentModel.DataAnnotations;




namespace MvcApplication3.Models

{

    public class UserModel

    {

        [Required(ErrorMessage = "Please Enter usre Name:")]

        [Display(Name = "FirstName")]

        public string FirstName { get; set; }







        [Required(ErrorMessage = "Please Enter usre Name:")]

        [Display(Name = "LastName")]

        public string LastName { get; set; }




        public string ddlJType { get; set; }




         [Required(ErrorMessage = "Please Enter Email Address")]

        [Display(Name = "Email")]

        [RegularExpression(@"^([a-zA-Z0-9_\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9]{2,4})+$", 

        ErrorMessage = "Please Enter Correct Email Address")]

        public string Email { get; set; }







         [Required(ErrorMessage = "Please enter your Password")]

         [StringLength(50, ErrorMessage = "Use 50 characters only.")]

         [DataType(DataType.Password)]

         public string Password { get; set; }







         [DataType(DataType.Password)]

         [Display(Name = "Confirm Password")]

         [System.Web.Mvc.Compare ("Password", ErrorMessage = "Password and confirm password must be same!")]

         public string ConfirmPassword { get; set; }




        [Required(ErrorMessage = "Please Enter Mobile No")]

        [Display(Name = "Mobile")]

        [StringLength(10, ErrorMessage = "The Mobile must contains 10 characters", MinimumLength = 10)]

        [RegularExpression(@"^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$", ErrorMessage = "Entered mobile format is not valid.")]




        public string MobileNo { get; set; }

     }

} 

Step 2-
Make a separate folder for Connection for this right click on your application in solution explorer then Add then New Folder write the new folder name Connection . 

   











add a class class for Connection in Connection Folder.
you can add same as just like model.

Write the code in Connection Class like this--->

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
namespace MvcApplication3.Connection
{
    public class Connection
    {

        public DataSet mydata()
        {
            SqlConnection con = new SqlConnection("Pass Your Connection String");
            SqlCommand cmd = new SqlCommand("select * from regist", con);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;

        }
    }
}

Step 3--->

Create a Controller you can choose any name of Controller and also working with HomeController.Write the code like in Controller.

  public ActionResult Demo()
        {
            List<UserModel> rgst = new List<UserModel>();
            DataSet ds = new DataSet();
            Connection.Connection con = new Connection.Connection();
            ds = con.mydata();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rgst.Add(new UserModel
                {

                    FirstName = dr["FirstName"].ToString(),
                    LastName = dr["LastName"].ToString(),
                    Email = dr["Email"].ToString(),
                    Password = dr["Pass"].ToString(),
                    MobileNo = dr["Mobile"].ToString()
                });
            }
            return View(rgst);

        }

Step 4-->
Create a view-->To Create on public ActionResult Demo() and then Add View...

Write the code like this in after Add View.

@model List<MvcApplication3.Models.UserModel>

@{
  ViewBag.Title = "Demo";
  var grid = new WebGrid(source: Model, canPage: true, rowsPerPage: 3,canSort:false);
  Layout = "~/Views/Shared/_Layout.cshtml";
}
 
 <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js" type="text/javascript"></script>
  
<script src="../../Scripts/jquery-1.7.1.min.js" type="text/javascript"></script> 
   <script src="../../Scripts/jquery-ui-1.8.20.js" type="text/javascript"></script> 
    <script src="../../Scripts/jquery-ui-1.8.20.min.js" type="text/javascript"></script> 
<script src="../../Scripts/jquery.validate.unobtrusive.min.js" type="text/javascript"></script>
<script src="../../Scripts/jquery.validate.unobtrusive.js" type="text/javascript"></script>
 <script type="text/javascript">

$(document).ready(function(){
$('.edit-mode').hide(); 
  
        $('.edit-user, .cancel-user').on('click', function () {
            var tr = $(this).parents('tr:first');
            tr.find('.edit-mode, .display-mode').toggle();
        });
 
  $('.save-user').on('click', function () {
           
   var tr = $(this).parents('tr:first');
  
            var fn = $(this).parents('tr').find("#FirstName").val();  
            var ln =$(this).parents('tr').find("#LastName").val();  
            var email =$(this).parents('tr').find("#Email").val(); 
   var pass = $(this).parents('tr').find('#Password').val(); 
   var mo =$(this).parents('tr').find("#MobileNo").val(); 
   alert(fn+ln+email+pass+mo);
   $.post("/User/InsertData",{FirstName:fn,LastName:ln,Email:email,Password:pass,Mobile:mo});
   alert("ok");
   location.reload();
          });
  

   $('.delete-user').on('click', function () {
 
            var fn = $(this).parents('tr').find("#FirstName").val();  
            var ln =$(this).parents('tr').find("#LastName").val();  
            var email =$(this).parents('tr').find("#Email").val(); 
   var pass = $(this).parents('tr').find('#Password').val(); 
   var mo =$(this).parents('tr').find("#MobileNo").val(); 
   alert(fn+ln+email+pass+mo);
   $.post("/User/DeleteData",{FirstName:fn,LastName:ln,Email:email,Password:pass,Mobile:mo});   
   alert("Are You Sure To Delete Data:" +email);
   location.reload();
  });
})
</script> 


<style type="text/css">
        .table
        {
            margin: 8px;
            border-collapse: collapse;
            width: 300px;
        }
        .header
        {
            background-color: green;
            font-weight: bold;
            color: #fff;
        }
        .table th, .table td
        {
            border: 2px solid black;
            padding: 10px;
        }
    </style>


     <div id="gridContent"> 

 @grid.GetHtml(
 
    tableStyle: "table", 
 
    fillEmptyRows: true, 
 headerStyle: "header",
    alternatingRowStyle: "alt",
    
    footerStyle: "grid-footer", 
 
    mode: WebGridPagerModes.All, 
    firstText: "<< First",
    previousText: "< Prev",
    nextText: "Next >",
    lastText: "Last >>",
    
    columns: new[]  
    {

  grid.Column("FirstName", "First Name",format: @<text><label id="lblFirstName"  >@item.FirstName</label> <input type="text" id="FirstName" value="@item.FirstName" class="edit-mode" ,readonly = "true" /></text>, style: "col1Width"), 
        grid.Column("LastName", "Last Name", format: @<text>   <label id="lblLastName" >@item.LastName</label> <input type="text" id="LastName" value="@item.LastName" class="edit-mode" /></text>, style: "col1Width"), 
        grid.Column("Email", "Email", format: @<text>   <label id="lblEmail"  >@item.Email</label>  <input type="text" id="Email" value="@item.Email" class="edit-mode" /></text>, style: "col1Width"), 
  grid.Column("Password", "Password", format: @<text>   <label id="lblPassword"  >@item.Password</label>  <input type="text" id="Password" value="@item.Password" class="edit-mode" /></text>, style: "col1Width"), 
  grid.Column("Mobile", "Mobile Number", format: @<text>   <label id="lblMobileNo"  >@item.MobileNo</label>  <input type="text" id="MobileNo" value="@item.MobileNo" class="edit-mode" /></text>, style: "col1Width"), 
  grid.Column("Action", format: @<text> <button class="edit-user display-mode" >Edit</button>  
        <button class="save-user edit-mode"  >Save</button>  
                                <button class="cancel-user edit-mode" >Cancel</button>  
          <button class="delete-user edit-mode" >Delete</button>  
       
                            </text>,  style: "col3Width" , canSort: false)  

   
  })
   
   

Run The project it will show you the output as follows-->

It will show you in Paging format if you are not interested in paging then, In view you can remove the following canPage: true, rowsPerPage: 3 OR you do change canPage: false, rowsPerPage: 3 .

Multiple ways to generate DropDownList in MVC using HTML helpers.

There are many way to generate DropDownList in ASP .NET MVC.In this article I am going to explain step by step fill DropDownList.I have already explain about the Html Helper such as @Html.DropDownList() and @Html.DropDownListFor() you know very how to use in MVC.

1.Fill statically DropDownList  in ASP .NET MVC using Controller.

2.Fill statically DropDownList  in ASP .NET MVC using View.

3.Fill dynamically DropDownList  in ASP .NET MVC using Database.

4.Dynamically bind Asp.Net MVC Dropdownlist from Sql Server Database using entity framework.

1st Approach:

1.Fill statically DropDownList  in ASP .NET MVC using Controller.

First you create a MVC Apllication.As per previous article how to be create an application visit following link http://mvctpoint.blogspot.in/2015/08/aspnet-mvc-create-new-aspnet-mvc.html .

You can easily bind data to dropdown list using  @Html.DropDownList() and @Html.DropDownListFor(),difference between both  @Html.DropDownList() not strictly bind with model.but @Html.DropDownListFor() is strictly bind with model.

So first bind the data using @Html.DropDownList().No need to take any type model or say any type of variable inside the model.

These following step you can follow:

Inside the HomeController take any ActionResult Method.and can write the such type of code:

 public ActionResult YourList()

        {

          

            List<SelectListItem> list = new List<SelectListItem>();

            list .Add(new SelectListItem { Text = "India", Value = "0" });

            list .Add(new SelectListItem { Text = "Pakistan, Value = "1" });

            list .Add(new SelectListItem { Text = "England", Value = "2" });

            list .Add(new SelectListItem { Text = "Saudi", Value = "3" });

            list .Add(new SelectListItem { Text = "Japan", Value = "4" });

            list .Add(new SelectListItem { Text = "China", Value = "5" });

        

            ViewData["Country"] = list ;

}

or is you want to fill data to dropdown as date,month,year.so you can use the following loop.

 public ActionResult YourList()

        {

            List<SelectListItem> list = new List<SelectListItem>();

            for (int i = 1; i <= 31; i++)

            {

                list .Add(new SelectListItem { Text = i.ToString(), Value = i.ToString() });

            }

            ViewData["Date"] = list ;

 }

In View you can write such type of code.

@{

    ViewBag.Title = "TravelRequest";

    Layout = "~/Views/Shared/_Layout.cshtml";

   }

@using (Html.BeginForm())

{

   @Html.ValidationSummary(true)

<table>

    <tr>

        <td>

                Country:

       </td>

       <td>

                @Html.DropDownList("Country", ViewData["Country"] as List<SelectListItem>)

       </td>

   </tr>

 <tr>

        <td>

                Date:

       </td>

       <td>

                @Html.DropDownList("Date", ViewData["Date"] as List<SelectListItem>)

       </td>

   </tr>

</table>

}

Here Country,Date work as 'Id' that is use to get selected text Text Or Value. 

Using Jquery we can get the Text from dropdown in mvc as such:

<script type="text/javascript">

      $(document).ready(function () {

        var country= $("#Country :selected").text();

        var date = $("#Date :selected").text();

})

<script>

Using Jquery we can get the Value from dropdown in mvc as such:

<script type="text/javascript">

      $(document).ready(function () {

        var country= $("#Country :selected").val();

        var date = $("#Date :selected").val();

})

<script>

------------------------------------------------------------------------------------------------------------
2nd Approach:

Fill statically DropDownList  in ASP .NET MVC using View:

You have to follow the code

@Html.DropDownList("Country",new List<SelectListItem>{

           new SelectListItem { Text = "India", Value = "0" },

            new SelectListItem { Text = "Pakistan, Value = "1" },

           new SelectListItem { Text = "England", Value = "2" },

            new SelectListItem { Text = "Saudi", Value = "3" },

            new SelectListItem { Text = "Japan", Value = "4" },

            new SelectListItem { Text = "China", Value = "5" }

})

or 

If you want to create DropDownList statically using Razor so you can change the following code as follows:

@{

       var listItem = new List<ListItem>();
       listItem.add(new List{Text = "India", Value = "0" });
       listItem.add(new List{Text = "Pakistan, Value = "1" });
       listItem.add(new List{ Text = "England", Value = "2"});

}

@Html.DropDownListFor(model=>mode.SelectedValue,listItem)

 

--------------------------------------------------------------------------------------

3rd Approach:
  
Fill dynamically DropDownList  in ASP .NET MVC using Database.

Here I will explain how to bind dropdownlist using JQuery ajax or JSON in asp.net.To implement this concept first design table in database and give name as Country as shown below

Column Name	Data Type	Allow Nulls
CountryId	int(set identity property=true)	No
CountryName	varchar(50)	Yes

After completion table design enter some of Country details in database to work for our sample and write the following code in your aspx page.

Bind DropDownList Using Entity Framework in ASP.Net MVC Using C#

In this article I will show you how you can bind/populate dropdownlist using entityframework in asp.net mvc usingC#. In this article I will also show you after binding how you can retrieve the selected value of  dropdownlist at controller end. This article you can use in MVC2/MVC3/MVC4/MVC5 application.

Now for this article first we will create a new MVCapplication. After creating application we will create model. In our model file we will add the below code.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace bIND_Dropdownin_MVC.Models

{

    public class CountryModel

    {

        public SelectList CountryListModel { get; set; }

    }

}

Now just check the Entity Framework and SQL table.For Using the Entity Framework you can select your database and create the .edmx File.You can read brief knowledge about how to create .edmx file.

Entityframework

Now again come to solution explorer and add the controller class file. In your controller add the below code to bind the DropDownList.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using bIND_Dropdownin_MVC.Models;

namespace bIND_Dropdownin_MVC.Controllers

{

    public class HomeController : Controller

    {

        //Bind/Populate DropDownList Using Entity Framework in ASP.Net MVC Using C#

        public ActionResult Index()

        {

            /*Create instance of entity model*/

            NorthwindEntities objentity = new NorthwindEntities();

            /*Getting data from database*/

            List<Country> objcountrylist = (from data in objentity.Countries

                                            select data).ToList();

            Country objcountry = new Country();

            objcountry.CountryName = "Select";

            objcountry.Id = 0;

            objcountrylist.Insert(0, objcountry);

            SelectList objmodeldata = new SelectList(objcountrylist, "Id","CountryName", 0);

            /*Assign value to model*/

            CountryModel objcountrymodel = new CountryModel();

            objcountrymodel.CountryListModel = objmodeldata;

            return View(objcountrymodel);

        }

    }

}

Now we will create View for the model. In this view we will add all the HTML tags.

@model bIND_Dropdownin_MVC.Models.CountryModel

@{

    ViewBag.Title = "Bind/Populate DropDownList using Entity Framework in ASP.Net MVC Using C#";

}

<h2>

    Bind DrowpDownList</h2>

@Html.DropDownList("ddlcountry", Model.CountryListModel, new {@style="width:200px;"})

In above code we have first accessed the model. This model we will use to bind with the control. Now we have done run the page and see the output.

Now we will learn how we can retrieve the selected value at controller end. For this you needed to create post method in the controller. Now add the below post method in your controller.

        [HttpPost]

        public ActionResult Index(int ddlcountry)

        {

            /*Create instance of entity model*/

            NorthwindEntities objentity = new NorthwindEntities();

            /*Getting data from database*/

            List<Country> objcountrylist = (from data in objentity.Countries

                                            select data).ToList();

            Country objcountry = new Country();

            objcountry.CountryName = "Select";

            objcountry.Id = 0;

            objcountrylist.Insert(0, objcountry);

            SelectList objmodeldata = new SelectList(objcountrylist, "Id","CountryName", 0);

            /*Assign value to model*/

            CountryModel objcountrymodel = new CountryModel();

            objcountrymodel.CountryListModel = objmodeldata;

            /*Get the selected country name*/

            ViewBag.CountryName = objcountrylist.Where(m => m.Id == ddlcountry).FirstOrDefault().CountryName;

            return View(objcountrymodel);

        }

In above code you will see the index method parameter name is same as the dropdownlist in our view. You need  to keep both name same other wise you will not be able to get the selected  value.

Now run the page.

Now click on submit button you will see the selected country value in parameter.

Now press F5 and check the final output.

What do you think?

I hope you will enjoy to create DropDownList while programming with Asp.Net MVC. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.