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

Inserting/Updating Record on related entities using LINQ

It is a very common scenario that you need to insert rows in related tables using LINQ. Assume you have two tables as below,

imageimage

Insert a Person with no Office Assignment

If you have requirement to insert a Person without any Office Assignment then it is quiet straight forward task like below

image

If you need to insert Office Assignment while inserting Person, you can create instance of office assignment and insert along with Person like below,

image

Now I have created function to perform both Insert and update operation. If you provide person Id as 0 this function will insert a new row else it will update existing row.

image

When I tried to call above function to update Person with ID 1, I encountered with below exception

image

To get rid of above exception, you need to call attach with original entity as well. We need to pass modified entity as well as original entity to make a call.

image

So I created a function as below,

image

And while attaching you need to call above function as below

image

Full source code is as below,


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

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.SubmitChanges();
            Console.ReadKey(true);

            //Person p = new Person {FirstName ="xuz", LastName = "abdefg" };
            //p.OfficeAssignment = new OfficeAssignment { Location = "Mumbai" };
            //p.OfficeAssignment = new OfficeAssignment { Location = "Delhi" };

            //p.
            //context.SubmitChanges();

            Person p = new Person { PersonID = 1, FirstName = "Dhananjay " ,LastName="Kumar"};
            SavePerson(p);

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

            var res1 = from r in context.OfficeAssignments select r;
            foreach (var a in res1)
            {
                Console.WriteLine(a.Location+ a.Person.PersonID);
            }

            Console.ReadKey(true);

        }

        //static string GetConnectionString(string serverName)
        //{

        //    System.Data.SqlClient.SqlConnectionStringBuilder builder =
        //                   new System.Data.SqlClient.SqlConnectionStringBuilder();
        //    builder["Data Source"] = devServerName;
        //    builder["integrated Security"] = true;
        //    builder["Initial Catalog"] = "Sample2";
        //    Console.WriteLine(builder.ConnectionString);
        //    Console.ReadKey(true);
        //    return builder.ConnectionString;

        //}

        static void SavePerson(Person p)
        {

            if (p.PersonID == 0)
            {
                context.Persons.InsertOnSubmit(p);
            }
            else
            {

               context.Persons.Attach(p, GetOriginal(p.PersonID));
               context.SubmitChanges();

            }
        }

        static Person GetOriginal(int id)
        {
            DataClasses1DataContext db = new DataClasses1DataContext();
            return db.Persons.Single(r => r.PersonID == id);

        }
    }
}

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

DataContext in LINQ

To make a communication with Database a connection must be made. In LINQ this connection is created by DataContext.

Essentially Data Context class performs below two tasks

  1. Create connection to database.
  2. It submits and retrieves object to database.
  3. Converts objects to SQL queries and vice versa

image

You can say, it acts as exactly the same as SqlConnection class and perform some extra tasks as well like conversion of object to SQL query.

DataContext class is having four types of overloaded constructor.

image

It may take

  1. Connection string
  2. IDbConnection etc

Various public methods of DataContext class help us to perform below tasks

  1. Create data base
  2. Delete data base etc

You can create and drop a database like below,

Create database

clip_image002

Delete database

clip_image004

Full source code is as below,


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

namespace ConsoleApplication1
{
    class Program
    {

        static void Main(string[] args)
        {

            DataContext context = new DataContext(GetConnectionString("Yourservername"));
            bool dbExist = context.DatabaseExists();
            if (dbExist == true)
            {

                context.DeleteDatabase();
                Console.WriteLine("Database deleted");
            }
            else
            {
                context.CreateDatabase();
                Console.WriteLine("Database created");
            }
            Console.ReadKey(true);

        }

        static string GetConnectionString(string serverName)
        {

            System.Data.SqlClient.SqlConnectionStringBuilder builder =
                           new System.Data.SqlClient.SqlConnectionStringBuilder();
            builder["Data Source"] = serverName;
            builder["integrated Security"] = true;
            builder["Initial Catalog"] = "Sample2";
            Console.WriteLine(builder.ConnectionString);
            Console.ReadKey(true);
            return builder.ConnectionString;

        }
    }
}

Strongly Typed Data Context

Strongly typed Data context can be created by below steps

  1. Create class to represent strongly type data context
  2. Inherits the class from DataContext class.

clip_image002[5]

Advantage of using strongly typed data context is that each table is available in Table collections. So you do not need to fetch tables using GetTable method.

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

Connection string in LINQ

It is very common that you come across scenario when at run time you need to change the database server and so the connection string is used in your LINQ. This may come as requirement, if you are moving your application from dev server to staging and from staging to production.

There are two ways you can change connection string on fly

  1. Edit config file
  2. Build connection string to pass as constructor of DataContext.

Edit config file

You can do it in two ways. Open the configuration file and edit connection string here.

clip_image002

In other way, you can change connection string is open DBML file and right click then select Properties

clip_image002[5]

In properties tab create on Connection and click on Connection String

clip_image004

In connection properties dialog box you can change the connection so the connection string.

clip_image006

Editing Connection String in code

Assuming you is creating a function to return connection string. Crete function that it takes server name as input parameter. Your function should be like below.

clip_image008

You can pass server name to this function to create connection string and use output of this function in constructor of DataContext

clip_image010

So here you can pass any server name to create DataContext as of server.

For reference code is as below ,

 static string GetConnectionString(string serverName)
        {

            System.Data.SqlClient.SqlConnectionStringBuilder builder =
                           new System.Data.SqlClient.SqlConnectionStringBuilder();
            builder["Data Source"] = serverName;
            builder["integrated Security"] = true;
            builder["Initial Catalog"] = "Sample2";
            Console.WriteLine(builder.ConnectionString);
            Console.ReadKey(true);
            return builder.ConnectionString;

        }

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

Reading XML file through LINQ: few Tips

In this post I will discuss how could be read XML file using LINQ. We will see some tips as well.

Let us say we have XML file as below, this file is stored on D drive. This XML contains information about books.

Data.Xml

 


<?xml version="1.0" encoding="utf-8" ?>
<catalog>
 <books>
<book id="bk101">
  <author id="1">Gambardella, Matthew</author>
  <title>XML Developer’s Guide</title>
  <genre>Computer</genre>
  <price>44.95</price>
  <publish_date>2000-10-01</publish_date>
   <description> An in-depth look at creating applications with XML.</description>
</book>
<book id="bk102">
  <author id="2">Ralls, Kim</author>
  <title>Midnight Rain</title>
  <genre>Fantasy</genre>
  <price>5.95</price>
  <publish_date>2000-12-16</publish_date>
  <description> A former architect battles corporate zombies,an evil sorceress, and her own childhood to become queen of the world.</description>
</book>
<book id="bk103">
  <author id="3">Corets, Eva</author>
  <title>Maeve Ascendant</title>
  <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2000-11-17</publish_date>
    <description>After the collapse of a nanotechnology society in England, the young survivors lay the foundation for a new society. </description>
  </book>

<book id="bk104">
    <author id="4">Corets, Eva</author>
    <title>Oberon’s Legacy</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2001-03-10</publish_date>
    <description>In post-apocalypse England, the mysterious agent known only as Oberon helps to create a new life for the inhabitants of London. Sequel to Maeve Ascendant.</description>

  </book>

<book id="bk105">
    <author id="5">Corets, Eva</author>
    <title>The Sundered Grail</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2001-09-10</publish_date>
    <description>The two daughters of Maeve, half-sisters, battle one another for control of England. Sequel to Oberon’s Legacy.</description>

  </book>
  </books>
</catalog>

 

Fetching all the Books

To fetch all the Books, just we need to parse the XML file. Find the descendants book and fetch it in anonymous class.

 

image

 

Fetching a Particular Book

 

If you want to fetch a particular book, we need to apply where condition while parsing XML file.

 

image

 

Fetching Attribute Value of a particular Book

Imagine you need to fetch author Id of a particular book with Id bk102. To do that you need to select as below,

 

image

 

Fetching all the Authors Name only

To fetch the entire author name, we need to execute below query.

 

image

 

For your reference full source code is as below,

Program.cs

 



using System;
using System.Linq;
using System.Xml.Linq;


namespace ConsoleApplication23
{
    class Program
    {
        static void Main(string[] args)
        {
            XDocument document = XDocument.Load("D:\\Data.xml");
            #region Fetch All the Books
            var books = from r in document.Descendants("book")
                        select new
                            {
                                Author = r.Element("author").Value,
                                Title = r.Element("title").Value,
                                Genere = r.Element("genre").Value,
                                Price = r.Element("price").Value,
                                PublishDate = r.Element("publish_date").Value,
                                Description = r.Element("description").Value,

                            };

            foreach (var r in books)
            {
                Console.WriteLine(r.PublishDate + r.Title + r.Author);
            }

            Console.ReadKey(true);
            #endregion

            #region Fetching a particular  Book
            var selectedBook = from r in document.Descendants("book").Where
                                   (r=>(string)r.Attribute("id")=="bk102")
                        select new
                        {
                            Author = r.Element("author").Value,
                            Title = r.Element("title").Value,
                            Genere = r.Element("genre").Value,
                            Price = r.Element("price").Value,
                            PublishDate = r.Element("publish_date").Value,
                            Description = r.Element("description").Value,

                        };

            foreach (var r in selectedBook)
            {
                Console.WriteLine(r.PublishDate + r.Title + r.Author);
            }
            Console.ReadKey(true);
            #endregion



            #region Fetching a particular  Book


            var selectedBookAttribute = (from r in document.Descendants("book").Where
                                        (r => (string)r.Attribute("id") == "bk102")
                                        select r.Element("author").Attribute("id").Value).FirstOrDefault();

            Console.WriteLine(selectedBookAttribute);

            Console.ReadKey(true);

            #endregion


            #region Fetching all Authors


            var allauthors = from r in document.Descendants("book")
                                         select r.Element("author").Value;
            foreach(var r in allauthors)
            {
                Console.WriteLine(r.ToString());
            }


            Console.ReadKey(true);

            #endregion



        }
    }
}



Output

image

 

I hope this post was useful. Thanks for reading  Smile

SQL Azure with LINQ

LINQ could be used with SQL Azure in the same way it is used with SQL Server. We are going to use School database again. So to see how we can use SQL Azure with LINQ

1. Create a console Appli8cation. For purpose of this walkthrough , I am creating Console app. You can use in same way in other managed application.

2. Right click on console application project. Select add new item and choose LINQ to SQL Class form Data tab.

clip_image002

3. Choose the option Server explorer and add a new connection. In Add new connection windows provide information for SQL Azure.

clip_image003

4. Now we need to write usual LINQ query to do CRUD operation against database in SQL Azure. Here I am fetching all the records from Person class

Program.cs


using System;
using System.Linq;

namespace LINQSQLAzure
{
 class Program
 {
 static void Main(string[] args)
 {
 DataClasses1DataContext context = new DataClasses1DataContext();
 var result = from r in context.Persons select r;
 foreach (var r in result)
 {
 Console.WriteLine(r.LastName + " " + r.FirstName);
 }
 Console.ReadKey(true);
 }
 }
}

Expected output,

clip_image005

In this post , I explained LINQ with SQL Azure.