ASP.Net GridView with LinqDataSource

In this post I will walkthrough on using ASP.Net Gridview with LinqDatasource.

You can bind a GridView with LinqDataSource and would able to

  1. Edit
  2. Delete
  3. View data without writing a single line of code.

To see how it works , follow the steps below;

Step1

Crate a ASP.Net Web Application.

clip_image002

Step 2

Right click and add a new item in ASP.Net Web Application project. Choose LINQ to SQL class from Data tab.

clip_image004

Drag and drop tables from Server Explorer to generate Data Context class. I am creating Data Context class from School Database by selceting Person table.

clip_image005

Now Data Context class has been created.

Step 3

Go ahead and drag and drom a GridView on Default.aspx page.

clip_image006

Switch to the design view on Default.aspx page . Select Properties of GridView by clicking on the arrow key at right top of GridView and then Choose Data Source option selcet New data source.

clip_image007

From the dialog box you need to choose LINQ and if you want you can edit the data source name as well. I am leaving default data source name LinqDataSource1

clip_image009

Next you need to choose the context object . We have already created DataContext named DataClasses1DataContext1 .Choose DataClasses1DataContext1 as context object .

clip_image011

After choosing context object click on Next button . From next dialog box choose table and columns you want to bind grid view . Click on the advanced button,

clip_image013

Since you want data in grid view editable. So go ahead and checked all the three options.

clip_image014

Click Ok and clcik on Finish

Step 4

Since you want to Edit, Delete and Insert data from Grid View. So open properties of GridView and enable the options by cheking the chekboxes.

clip_image015

 

You have done it . Press F5 to run the appliction. You would able to Edit, Delete and View the data in Grid View without writing a single line of code.

clip_image016

I hope this post was useful. Thanks for reading Smile

 

***********************

 

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

Learning Video Executing Stored Procedure in LINQ to SQL

Source code used in Video


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace StoredProcedure
{
    class Program
    {
        static void Main(string[] args)
        {
            DataClasses1DataContext context = new DataClasses1DataContext();
            // Call SP with parameter
            int studentID = 2;
            var result=  context.GetStudentGrades(studentID);
            foreach (var r in result)
            {
                Console.WriteLine(r.Grade);
            }

            // call SP with parameter
            int? a=1;
            var result1 = context.GetStudentCount(ref a);
            Console.WriteLine(a);

            Console.ReadKey(true);

        }
    }
}

***********************************************************

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

Executing SQL Query using LINQ to SQL

In this post, I will discuss how we can execute a SQL Query directly from LINQ.

Explanation

To execute SQL query there is a method in DataContext class called ExecuteQuery

clip_image002

ExecuteQuery takes two input parameter

1. SQL Query as string

2. Parameters used in SQL query

clip_image003

And it returns an IEnumerable.

Example

Below code will execute a simple select statement and it will return IEnumerable<Person>

clip_image005

If you want to pass some parameter in the query, you can pass that as second parameter.

clip_image007

If you want pass input parameter as hardcoded value you can very much do that as below

clip_image009

For your reference source code is as below,


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.Linq;

namespace Relatedtable
{
    class Program
    {

        static DataClasses1DataContext context;
        static void Main(string[] args)
        {
            context = new DataClasses1DataContext();

            var result = context.ExecuteQuery<Person>("select * from Person");
            foreach (var r in result)
            {
                Console.WriteLine(r.PersonID + r.FirstName);
            }

            int idToPass = 1;
            var result1 = context.ExecuteQuery<Person>
                          ("select * from Person where PersonID={0}", idToPass);
            foreach (var r in result1)
            {
                Console.WriteLine(r.PersonID + r.FirstName);
            }

            Console.ReadKey(true);

            var result2 = context.ExecuteQuery<Person>
                          ("select * from Person where PersonID='1'");
            foreach (var r in result2)
            {
                Console.WriteLine(r.PersonID + r.FirstName);
            }

            Console.ReadKey(true);
}
}
}

On pressing F5 you should get output as below,

clip_image002[5]

I hope this post was useful. Thanks for reading Smile

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

Fetching different values in LINQ Concurrency conflict

If you have not read my post Resolving Concurrency conflicts in LINQ then I strongly recommend you to read that. This post is a simple added extension of said post.

After reading resolving concurrency conflict in LINQ you are pretty much aware of resolving mechanism of concurrency conflicts. Now assume a requirement that you need to print or fetch

  1. Current value of object
  2. Original value of object on context
  3. Current data base value.

This is very much possible by

  1. Enumerating over MemberConflicts of ChangeConflict object.
  2. Fetching MemberChangeConflict in object of MemberConflict

clip_image002

In child Foreach loop you can fetch values as ,

clip_image003

Catch statement should be modified as below to fetch and print different values.

clip_image005

For reference you can find full source code below,


using System;
using System.Linq;
using System.Data.Linq;

namespace Concurrency
{
    class Program
    {
        static void Main(string[] args)
        {

            DataClasses1DataContext context = new DataClasses1DataContext();

            #region without handling Conflict
            //Person personToUpdate = (from r in context.Persons
            //                         where r.PersonID == 1
            //                         select r).FirstOrDefault();
            //personToUpdate.FirstName = "John Papa";
            //context.SubmitChanges();

            #endregion

            #region hanlding conflict

            try
            {
                Person personToUpdateConflict = (from r in context.Persons
                                                 where r.PersonID == 1
                                                 select r).FirstOrDefault();
                personToUpdateConflict.FirstName = "John";
                context.SubmitChanges(ConflictMode.FailOnFirstConflict);

            }
            catch (ChangeConflictException c)
            {
                foreach (ObjectChangeConflict o in context.ChangeConflicts)
                {
                    o.Resolve(RefreshMode.KeepChanges);

                    foreach (MemberChangeConflict c1 in o.MemberConflicts)
                    {
                        var currentValue = c1.CurrentValue;
                        var originalValue = c1.OriginalValue;
                        var dataBaseValue = c1.DatabaseValue;

                        Console.WriteLine("Current Value of Object " + c1.CurrentValue);
                        Console.WriteLine("Original value in context " + c1.OriginalValue);
                        Console.WriteLine("Database value " + c1.DatabaseValue);
                    }

                }
                context.SubmitChanges();

            }

            #endregion
            Console.ReadKey(true);
        }
    }
}

On running application, you should get output as below.

image

I hope this post was useful. Thanks for reading Smile

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

Using O/R designer to bind a Windows Form in LINQ

In this post I will show you, how you could bind a Windows Form with Entity created by O/R designer. Essentially we are going to create a DataSource using generated entity of O/R designer.

I assume that,

1. You have created Windows Application project.

2. You have created Datacontext and entity class using LINQ to SQL class. Imagine you have dragged and dropped Person class on O/R designer from School database. Dbml file is looking more or less like below.

clip_image001

Follow the below steps to bind Windows Form,

1. Click on Data from top menu and choose Add New Data Source

clip_image002

2. Select Object as Data Source type

clip_image004

3. Choose Data object. Select Person from listed objects and click Finish

clip_image006

4. Navigate to Windows Form and open design surface and again click on Data in top menu and choose Show Data Sources

clip_image007

5. Now you will get Person in Data Sources listed. Select and drag on design surface.

clip_image009

In bottom of designer you can see Binding Data Source created

clip_image010

6. Now open the code Form class and add below code. Since you know to populate the data you need to set the Data Source of Binding Source as DataContext .

clip_image012

7. To save modification and add new rows , open Properties of Save button from navigation

clip_image013

Then in Properties Windows set Enabled as true.

clip_image014

8. In event of Save button write below code

clip_image016

Press F5 to run the application

clip_image017

Now you should able to perform all the operation.

I hope this post was useful. Thanks for reading Smile




Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

Turning off Pluralization of classes in LINQ to SQL O/R designer

In this quick post, I will show you how you could avoid pluralization of classes in LINQ to SQL

Step1

Open visual studio and click on Tool from Menu. Then select Options

clip_image001

Step 2

Select Database Tools tab

clip_image003

Step 3

Now choose O/R designer option from Database tool tab

clip_image004

Step 4

Change Enabled to False.

clip_image005

Now when you create data model using LINQ to SQL pluralization of classes would be turned off.

I hope this quick post was useful. Thanks for reading

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

Resolving Concurrency conflicts in LINQ

While performing database operation, one major thing need to be taken care of is Concurrency Conflicts. Concurrency conflict can happen when two sources are try to modify database at the same time.

Let us understand conflict in LINQ with an example,

image

From above diagram you can infer that, essentially there are three steps involved in concurrency conflict

Step 1

LINQ fetch data from database when database is in state “A”

Step 2

While LINQ is manipulating the data in DataContext , database has been modified by some other part and changed its state to “B”

Step 3

LINQ is trying to update the data in the database in state “B”

Now there are two ways LINQ can resolve this

  1. Override database changes
  2. Ignore changes made by LINQ itself.

By default, LINQ DataContext supports optimistic concurrency.

Very first to resolve concurrency conflict, you need to find table columns involved in conflict. You can put a check flag on entity column

clip_image002

When you create entity class in LINQ to SQL, you can attribute a column with UpdateCheck as shown above. Any column attributed with UpdateCheck would be checked for optimistic concurrency.

UpdateCheck is an enum with three values.

clip_image003

As it is very readable that,

Never Column value would never be checked for detecting conflict
Always Column value would always be checked for conflict
WhenChanged Column value would only be checked for conflict when value of column is changed.

If you attribute all the columns for update check then obviously performance would be degraded.

Other option you have is as follows

  1. Put the code updating in try catch block
  2. Check for ChangeConflictException
  3. In the catch block use RefreshMode to resolve conflict issue.

You can do concurrency check as follows ,

Modify SubmitChanges

You have submitchnages() overloaded and it takes ConflictMode enum as value

image

If you examine ConflictMode enum

clip_image001

It is having two values

image

Usually you use ConflictMode enum with submitchage as below,

clip_image005

Put SubmitChanges in try catch

After modifying SubmitChanges, on the conflict ChangeConflictException will be thrown by the LINQ .

image

Handle the conflict in Exception

In the Exception block you need to handle that how you are going to resolve the conflict.

RefreshMode enumeration helps you to decide how to handle conflict. This got three values to resolve the conflict.

image

image

Consider a very simple update operation as below with breakpoint

image

Run the code and when code hit breakpoint at that point of time go back to database and change value of FirstName for PersonID =1

After changes made in database then come back to code and run from the breakpoint. You will very likely encounter with below exception at submitChanges() method call

image

Essentially what we did that we take the data in DataContext and then modified the value from other source and we got the concurrency conflict.

If we put all the discussion together we can handle conflict as below,


using System;
using System.Linq;
using System.Data.Linq;

namespace Concurrency
{
    class Program
    {
        static void Main(string[] args)
        {

            DataClasses1DataContext context = new DataClasses1DataContext();

            #region without handling Conflict
            //Person personToUpdate = (from r in context.Persons
            //                         where r.PersonID == 1
            //                         select r).FirstOrDefault();
            //personToUpdate.FirstName = "John Papa";
            //context.SubmitChanges();

            #endregion

            #region hanlding conflict

            try
            {
                Person personToUpdateConflict = (from r in context.Persons
                                                 where r.PersonID == 1
                                                 select r).FirstOrDefault();
                personToUpdateConflict.FirstName = "JohnPapa";
                context.SubmitChanges(ConflictMode.FailOnFirstConflict);

            }
            catch (ChangeConflictException c)
            {
                foreach (ObjectChangeConflict o in context.ChangeConflicts)
                {
                    o.Resolve(RefreshMode.KeepChanges);

                }
                context.SubmitChanges();

            }

            #endregion
            Console.ReadKey(true);
        }
    }
}

Now you know how to resolve conflict in LINQ. I hope this post was useful. Thanks for reading  Smile

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

Logging in LINQ to SQL

It is very common that may come in your mind that, how we can see SQL query being generated at the back end for LINQ query.

Assume you have a LINQ as below,

image

If you want to view SQL query generated for above LINQ on console screen you need to add just one line of code as below,

image

If you closely examine log it is a property in DataContext class and it is of type textwriter.

image

Since it is of type text writer you can save it to a text file as well. If you want to log you need to follow below steps.

Step1

Create a class overriding System.IO.TextWriter class.

image

Step 2

Configure log file in configuration

image

Step 3

Log the query as below

image

Full source code of above explanation is given below ,


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace Relatedtable
{
    class Program
    {

        static DataClasses1DataContext context;
        static void Main(string[] args)
        {
            context = new DataClasses1DataContext();
            context.Log = Console.Out;
            context.Persons.InsertOnSubmit(
                            new Person
                            {
                                FirstName = "The ",
                                LastName = "Poet",
                                HireDate = DateTime.Now,
                                OfficeAssignment = new OfficeAssignment
                                {
                                    Location = "Jamshedpur"
                                }

                            }
                            );
            context.Log = new LogLINQSQL();
            context.SubmitChanges();
            context.Log = Console.Out;
            Console.ReadKey(true);

var result = from r in context.Persons
                         where r.FirstName=="Dhananjay"
                         select r ;
            context.Log = Console.Out;
            foreach (var r in result)
            {
                Console.WriteLine(r.FirstName);
            }
            Console.ReadKey(true);

  }

 }

    class LogLINQSQL : System.IO.TextWriter
    {
        public override void Write(char[] buffer, int index, int count)
        {
            System.Diagnostics.Debug.Write(new String(buffer, index, count));
        }

        public override void Write(string value)
        {
            System.Diagnostics.Debug.Write(value);
        }

        public override Encoding Encoding
        {
            get { return System.Text.Encoding.UTF8; }
        }
    }
}

I hope this post was useful. Thanks for reading  Smile

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine