How to Bind GridView with Class instead of DataTable

You have seen many times that the GridView is bind with DataTable and wonder if you can bind it by another way. The answer is "Yes", you can bind a GridView with a Class too.

Bind GridView with Class

Let me show you how to bind your GridView using a Class.

1. First add the GridView to the Web Form.

<asp:GridView ID="gridView" runat="server"></asp:GridView>

2. Go to the CS page and create a Product Class.

class Product
    {
        public string name { get; set; }
        public string image { get; set; }
        public string categoryName { get; set; }
        public decimal price { get; set; }
    }

3. Create a function that fills the Class with dummy data.

void LoadData()
    {
        product = new List<Product>();
        product.Add(new Product() { name = "Vanheusen Women's Skirt", image = "Image/s1.jpg", categoryName = "Skirts", price = 20.89M });
        product.Add(new Product() { name = "Decot Paradise Animal Print Women's Regular Black Skirt", image = "Image/s2.jpg", categoryName = "Skirts", price = 35.59M });
        product.Add(new Product() { name = "DeeVineeTi Polka Print Women's Wrap Around White, Blue Skirt", image = "Image/s3.jpg", categoryName = "Skirts", price = 98.00M });
        product.Add(new Product() { name = "Raabta Fashion Striped Women's Pencil Black, White Skirt", image = "Image/s4.jpg", categoryName = "Skirts", price = 120.00M });
        product.Add(new Product() { name = "Ishin Solid Women's Regular Orange Skirt", image = "Image/s5.jpg", categoryName = "Skirts", price = 115.85M });
        product.Add(new Product() { name = "Adidas Women's Skirt", image = "Image/s6.jpg", categoryName = "Skirts", price = 105.74M });

        product.Add(new Product() { name = "V Dot by Van Huesen Skinny Fit Men's Grey Trousers", image = "Image/p1.jpg", categoryName = "Pants", price = 10.89M });
        product.Add(new Product() { name = "French Connection Slim Fit Men's Beige Trousers", image = "Image/p2.jpg", categoryName = "Pants", price = 25.59M });
        product.Add(new Product() { name = "Indian Terrain Slim Fit Men's White Trousers", image = "Image/p3.jpg", categoryName = "Pants", price = 48.00M });
        product.Add(new Product() { name = "United Colors of Benetton Slim Fit Men's Beige Trousers", image = "Image/p4.jpg", categoryName = "Pants", price = 120.00M });
        product.Add(new Product() { name = "Numero Uno Slim Fit Men's Black Trousers", image = "Image/p5.jpg", categoryName = "Pants", price = 155.85M });
        product.Add(new Product() { name = "John Players Slim Fit Men's Grey Trousers", image = "Image/p6.jpg", categoryName = "Pants", price = 165.74M });

        product.Add(new Product() { name = "Vu 140cm (55) Full HD Smart LED TV", image = "Image/e1.png", categoryName = "Electronics", price = 80.89M });
        product.Add(new Product() { name = "BPL Vivid 80cm (32) HD Ready LED TV", image = "Image/e2.png", categoryName = "Electronics", price = 235.59M });
        product.Add(new Product() { name = "Moto E3 Power (Black, 16 GB)", image = "Image/e3.jpg", categoryName = "Electronics", price = 198.00M });
        product.Add(new Product() { name = "Krishna M (Silver, 64 GB)", image = "Image/e4.jpg", categoryName = "Electronics", price = 120.00M });
        product.Add(new Product() { name = "Sansui 150 L Direct Cool Single Door Refrigerator", image = "Image/e5.jpg", categoryName = "Electronics", price = 115.85M });
        product.Add(new Product() { name = "Whirlpool 245 L Frost Free Double Door Refrigerator", image = "Image/e6.jpg", categoryName = "Electronics", price = 105.74M });
    }

4. Fill the class with data.

LoadData();

5. Now finally bind the GridView with the class data like this:

gridView.DataSource = product;
gridView.DataBind();


Binding GridView with Class on Page Load Event
Here I will show you how to bind the GridView on Page Load event.

Add the below code to the .cs page.

List<Product> product;
protected void Page_Load(object sender, EventArgs e)
{
    if(!IsPostBack)
    {
        LoadData();
        gridView.DataSource = product;
        gridView.DataBind();
    }
}

void LoadData()
{
    //code
}

class Product
{
    public string name { get; set; }
    public string image { get; set; }
    public string categoryName { get; set; }
    public decimal price { get; set; }
}

and that's it the Binding of GridView is done.

Related Article: Also check the related article which teaches the 'Binding of GridView with Paging using jQuery Load with No Page Refresh'. Check this article here - asp.net gridview example.

Preventing Phantom Reads in SQL Server using Locks

In multi user systems, when a user is allowed to read data from a row that has been modified by another user and not yet committed, is known as Phantom Reads.

For example – I have a Students table that contains Students record. I update the student name, with id 5, from ‘Mila’ to ‘Maria’. But before it is committed, another user reads the student with id 5. The other user will get ‘Mila’ for the name but actually the ‘Mila’ is changed to ‘Maria’ and so it becomes Phantom Read (i.e. a non-existent record or phantom).

How to Prevent Phantom Reads

During the time of insert, update or delete work on the table, the SQL Server will automatically place the table under lock. So it will automatically prevent Phantom Reads.

But if one user removes the lock (for faster execution of query) then Phantom reads can happen

Note - Removing the lock can be done with this statement - with(nolock)

Suppose there are 2 users and both of them executes some SQL Statement.

Note - The second user executes the statement after 1 second.

First User executes:

begin tran

update Student set name='Maria' where ID=5

waitfor delay '00:00:15'

rollback     

Second User executes (after 1 second):

select name from Student with(nolock) where ID=5 

Here you can see that the first user changes the student name from ‘Mila’ to ‘Maria’ then after a delay of 15 seconds rolls back it to ‘Mila’.

The second, who executes the SQL statement by relaxing the lock will read the name as ‘Maria’. 

The name ‘Maria’ is a Phantom (non-existant) since first user has reverted back to ‘Mila’

Note – The relaxation of lock can be used for faster execution of Queries in some situations. But I would not recommend using them when doing CRUD Operations in SQL Server.

You can run both the users statements in separate query window to see the result they give to you.

If the second user does not use with(nolock) then SQL Server will make him wait for 15 seconds till the lock gets removed and then gives him ‘Mila’ for the student with id 5.

How to use jQuery AJAX with Entity Framework in ASP.NET MVC

Introduction

This tutorial will help you to implement jQuery AJAX in your ASP.NET MVC project. You will also learn how to use Entity Framework in doing database operations. This tutorial is for Beginners and Intermediate coders who want to learn jQuery AJAX and Entity Framework.

The tutorial covers the following things –

1. How to use jQuery AJAX in ASP.NET MVC. 
2. How to use Entity Framework for doing DB operations. 
3. How to apply jQuery Validations in the View. How to apply CSS in the view.

Requirements

I have used ASP.NET MVC 5.0 and Entity Framework 6.0 in this tutorial. However the codes will run on all MVC and EF versions.

• The Database is SQL Server 2012. 
• Visual Studio 2015 is used.

Project Briefings

In this project I will make a Database table in SQL Server. Then I will create a MVC form, on submitting this form, the form’s data will be sent to a function, defined on the Controller, using jQuery AJAX. This function will perform the insert in the DB table using Entity Framework and return the outcome of the operation.

STEP 1 – Creating the Database Table.

Let us first create a database table containing the student’s information. It will have the following fields.

Name	Type	Attribute
Id	INT	Identity Primary Key
Name	VARCHAR(50)	Not Null
Address	VARCHAR(50)	Not Null
Age	INT	Not Null
Standard	VARCHAR(10)	Not Null
Percent	DECIMAL(5,2)	Not Null
AddedOn	DATETIME	Not Null Default Binding getdate()
Status	BIT	Not Null

Database Table

You can run the below Script in your database to generate the Student’s table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Student](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Address] [varchar](100) NULL,
    [Age] [int] NOT NULL,
    [Standard] [varchar](10) NOT NULL,
    [Percent] [decimal](5, 2) NOT NULL,
    [AddedOn] [datetime] NOT NULL,
    [Status] [bit] NOT NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[Student] ADD  CONSTRAINT [DF_Student_AddedOn]  DEFAULT (getdate()) 

FOR [AddedOn]
GO

There will also be an Insert Stored Procedure “sp_InsertStudent” which will insert entries in the student’s table. Note that this SP will be executed with the Entity Framework. The SP code is given below:

CREATE PROCEDURE [dbo].[sp_InsertStudent]   
    @Name        VARCHAR(50), 
    @Address     VARCHAR(100),   
    @Age         INT,
    @Standard    VARCHAR(10),
    @Percent     DECIMAL(5,2),
    @Status      BIT, 
    @Result      VARCHAR(50) OUTPUT,
    @CreatedId   INT OUTPUT     
AS           
BEGIN
    INSERT INTO Student(Name,Address,Age,Standard,[Percent],Status) VALUES 

    (@Name,@Address,@Age,@Standard,@Percent,@Status) 
    SET @Result='Insert Successful'   
    SET @CreatedId=@@IDENTITY
END

STEP 2 – Creating the Entity Data Model (EDM)

Now let’s move towards the Entity Framework. I will create EDM model of the Database, this EDM will create an .edmx file.

The EDMX file contains the knowledge of the database table, and the insert stored procedure.

Because of this EDMX file, we will be able to execute the “sp_GetStudent” SP from the MVC code.

Follow the Below Steps to create EDM:

a. Install “Entity Framework” from NuGet Package – Ignore this step if EF is already installed in your Project.

In the Visual Studio right click the project then select “Manage NuGet Packages” . Next, in the search box, enter “Entity FrameworK” and click enter.

Select the “Entity Framework” and click “Install” button.

b. Add “ADO.NET Entity Data Model” – In the Visual Studio right click the project. Then select “Add > New Item” – this will open “Add New Item” Window.

Next select “Visual C#” from the left side (under “Installed”), then select “ADO.NET Entity Data
Model” and click the “Add” button.



c. Choose Model Contents – Select “EF Designer from database” and click next button.



d. Choose Your Data Connection – Click the “New Connection” button to open the “Connection Properties” window.

Enter the “Server name” and then select the database from “Select or enter a database name”.

Next click the “OK” button.

The “Connection Properties” window will close.

Click the “Next >” button.

Note – Select the database where you have created the “Student” table. Click the “Test Connection” button to find out whether the connection is alright or not.



e. Choose Your Database Objects and Settings – Check the “Tables” and “Stored Procedures and Functions”. This is because I have one table and one stored procedure and I want the EDM to have the knowledge of both of them.

Finally click the “Finish” button.

Finally our EDMX file will be created and it will have the students table shown over it.

STEP 3 – Creating the Controller

Right click the “Controller” folder in your project and select “Add > Controller”. Next Click “MVC 5 Controller – Empty” and click “Add” button.

Give the Controller name as “InsertController” and click the “Add” button.



The controller will open in the Visual Studio. Now start entering the code.

First add the entity namespace to the controller -

using System.Data.Entity.Core.Objects;

Then create an insert function in the controller. This function will call the “InsertStudent” stored procedure. Notice how I have defined the 2 output parameter of the SP as type “ObjectParameter”.
This function - I will call through the jQuery AJAX method from the view. I will explain how to do this in STEP 7.

The values of the “result” output parameter is returned at the last line.

[HttpPost]
public string Insert(string name, string age, string standard, string percent, 

    string address, string status)
{
    using (var context = new Entities())
    {
        ObjectParameter result = new ObjectParameter("result", typeof(String));
        ObjectParameter createdId = new ObjectParameter("createdId", typeof(String));
        context.sp_InsertStudent(name, address, Convert.ToInt32(age), standard,

            Convert.ToDecimal(percent), Convert.ToBoolean(Convert.ToInt32(status)), result, createdId);
        return result.Value.ToString();
    }
}

STEP 4 – Creating the View

In your project open the “Views” folder where you will see the “Insert” folder. Right click the “Insert” folder and select “Add > View”. Give the view name as “Index” and click the “Add” button.

This will create the view.

STEP 5 – Creating the Student Form in the View

Now I will create the “Student” form with all the fields given in the “Student” table. I will not include the “Id” and “AddedOn” as they are auto-generated fields.

The below code will create the student form in the “Index” view:

<div id="messageDiv"></div>

<div class="studentFormDiv">

    @using (Html.BeginForm())

    {

        <table>

            <tr>

                <td>

                    <label>name</label>

                    <input id="name" type="text">

                    <span class="nameSpan"></span>

                </td>

                <td>

                    <label>age</label>

                    <input id="age" name="age" type="number">

                    <span class="ageSpan"></span>

                </td>

            </tr>

            <tr>

                <td>

                    <label>standard</label>

                    <input id="standard" type="text">

                    <span class="standardSpan"></span>

                </td>

                <td>

                    <label>percent</label>

                    <input id="percent" type="text">

                    <span class="percentSpan"></span>

                </td>

            </tr>

            <tr>

                <td>

                    <label>address</label>

                    <input id="address" type="text">

                    <span class="addressSpan"></span>

                </td>

                <td>

                    <label>status</label>

                    <select id="status">

                        <option value="Select">Select</option>

                        <option value="1">Active</option>

                        <option value="0">InActive</option>

                    </select>

                    <span class="statusSpan"></span>

                </td>

            </tr>

            <tr><td><button id="submitButton" type="submit">Submit</button></td></tr>

        </table>

    }

</div>

Explanation – The above form will be filled and submitted on clicking the submit button at the bottom. On clicking the button the jQuery AJAX will call the C# function “Insert”. The returned value from the C# function will be shown in the “messageDiv” div.

STEP 6– Applying the Styles to the Form

To give the little bit of styling to this form add the CSS to the view, on top of the form:

<style>

    .studentFormDiv label {

        display: block;

        margin: 0;

        text-transform: capitalize;

    }

    .studentFormDiv span {

        display: block;

        color: red;

    }

    .studentFormDiv > form > div > div {

        padding-top: 25px;

    }

    table {

        width: 100%;

    }

    h1 {

        border-bottom: solid 3px #CCC;

    }

</style>

STEP 7 – Using jQuery AJAX to Call the Insert Function

On the ”submitButton” button click event I will validate all the form’s fields with jQuery. When all the field values are correct then I will use jQuery AJAX method to call the C# function named “insert”. The student’s field values will be sent to this function as parameters. Since this function will be called using jQuery AJAX so there will be no page postback.





The jQuery Code is given below:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>

<script>

    $(document).ready(function () {

        $("#submitButton").click(function (e) {

            var returnValidate = Validate();

            if (returnValidate) {

                $.ajax({

                    type: "POST",

                    url: "/insert/insert",

                    contentType: "application/json; charset=utf-8",

                    data: '{"name":"' + $("#name").val() + '","age":"' + $("#age").val()

+ '","standard":"' + $("#standard").val() + '",

"percent":"' + $("#percent").val() + '","address":"'

+ $("#address").val() + '","status":"' + $("#status").val()

+ '"}',

                    dataType: "html",

                    success: function (result, status, xhr) {

                        $("#messageDiv").html(result);

                    },

                    error: function (xhr, status, error) {

                        $("#messageDiv").html(status);

                    }

                });

            }

            return false;

        });

        function Validate() {

            var errorCounter = 0;

            $(".nameSpan").text("");

            $(".ageSpan").text("");

            $(".standardSpan").text("");

            $(".percentSpan").text("");

            $(".statusSpan").text("");

            if ($("#name").val() == "") {

                $(".nameSpan").text("Please enter name");

                errorCounter++;

            }

            if ($("#age").val() == "") {

                $(".ageSpan").text("Please enter age");

                errorCounter++;

            }

            else if (!(isPositiveInteger($("#age").val()))) {

                $(".ageSpan").text("Age should be in +ve integers");

                errorCounter++;

            }

            if ($("#standard").val() == "") {

                $(".standardSpan").text("Please enter standard");

                errorCounter++;

            }

            if ($("#percent").val() == "") {

                $(".percentSpan").text("Please enter percent");

                errorCounter++;

            }

            else if (!(isPercent($("#percent").val()))) {

                $(".percentSpan").text("Percent should be integers or decimal");

                errorCounter++;

            }

            if ($("#status").val() == "Select") {

                $(".statusSpan").text("Please select status");

                errorCounter++;

            }

            if (errorCounter == 0) {

                return true;

            }

            else {

                return false;

            }

        }

        function isPositiveInteger(n) {

            return ($.isNumeric(n) && (n > 0));

        }

        function isPercent(n) {

            return (n.match(/(^100(\.0{1,2})?$)|(^([1-9]([0-9])?|0)(\.[0-9]{1,2})?$)/));

        }

    });

</script>

I passed “/index/insert” to the jQuery AJAX function to call the “insert” function of the “insert” controller. The C# function’s returned value is shown by the “success: function(){}” given inside the jQuery AJAX method.

STEP 8 – Configuring RouteConfig.cs

To set “Index” view of the “Insert” controller as the starting page of our project, update the RouteConfig.cs file (inside the App_Start folder) as shown below-

routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.MapRoute(
    name: "Default",
    url: "{controller}/{action}/{id}",
    defaults: new { controller = "Insert", action = "Index", id = UrlParameter.Optional }
);

Testing

To test it, let us fill and submit the form. You will see the jQuery AJAX will call the C# function, which in turn will insert the values and return a message. This return message will be shown in the div.

Summary

This tutorial will help you to learn Entity Framework & jQuery AJAX in ASP.NET MVC. From this tutorial you can go on and learn more about the different Entity Framework topics.
Please mark and vote for this tutorial and give me your suggestions.
Also share this article with your friends on email, Facebook, Twitter & Google Plus.
I hope you liked this tutorial!