• Wriju's BLOG

    C# 3.0 : Anonymous Types

    • 6 Comments

    C# 3.0’s Anonymous Type is a cool feature and it allows us to create object and collection of no type. Actually the type does not exist but on the fly you can create object out of that type. Let’s say you need to create some object which will have two properties Id of type int and Name of type string. In normal scenario, you have to write a class and then create the object of that class. But C# 3.0 allows us to declare an object of that type and assign value to it.

     

    Suppose you need this Class

     

    public class Cust

    {

        public int CustId { get; set; }

     

        private string _CustName;

        public string CustName

        {

            get { return _CustName; }

            set { _CustName = value; }

        }

    }

     

    But you are too lazy to create the class. This C# 3.0 will create this class for you. So if you write something like

     

    var obj = new { CustId = 1, CustName = "C#" };

     

    OneTypes

     

    Notice here after the keyword “new” I have no type name. This gives instruction to the compiler to create a dummy type for me. Now Visual Studio is so exciting that this does not require me to compile to be able to provide me the IntelliSense support. Without compiling I can get the IntelliSense and auto complete.

     

    Whenever I will use obj and after the “.” Visual Studio 2008 IntelliSense will show me the two properties,

     

    Img1

     

    One of the drawbacks is that you cannot reset the property value. So if you try to write, obj.CustId = 1; will through you an error.

     

    Property or indexer 'AnonymousType#1.CustId' cannot be assigned to

    -- it is read only  

     

    This is because it creates get-only property and can only be initialized through the Object Initializers feature of C# 3.0.

     

    Another observed behavior is that if you create object with the keyword “new”, like,

     

    var obj = new { CustId = 1, CustName = "C#" };

     

    var obj2 = new { Id = 1, Name = "C#" };

     

    TwoTypes

     

     

    Now the compiler will create two anonymous types as the property name differs.

     

    But….

     

    If you use objects like,

     

    var obj = new { CustId = 1, CustName = "C#" };

     

    var obj1 = new { CustId = 1, CustName = "C#" };

     

    Compiler will not create multiple types as the name is same.

     

    But!!!

     

    Interestingly if you have declarations like,

     

    var obj = new { CustId = 1, CustName = "C#" };

     

    var obj1 = new { CustId = "A", CustName = "C#" };

     

    var obj2 = new { CustId = 2.2, CustName = "C#" };

     

     

    The CLR will create only one Type, but the compiler will give you three different date type options for the three objects.

     

    Img2

    For obj the CustId will be inferred as type “int”.

     

    Img3

    For obj1 the CustId will be inferred as type “string”.

     

     

    Img4   

    For obj2 the CustId will be inferred as type “double”.

     

    This is because the generated IL creates the type as Generic type. With the

     

    .field private initonly !'<CustId>j__TPar' '<CustId>i__Field'

     

    "initonly" is readonly and "!" indicates that the type is generic.

     

    Namoskar!!!

  • Wriju's BLOG

    Get 1 GB Storage at Skydrive Windows Live

    • 1 Comments

    Get 1 GB storage online at http://skydrive.live.com. Microsoft launched it today.

    For more details please visit http://skydriveteam.spaces.live.com/default.aspx.

    Namoskar!!!

  • Wriju's BLOG

    Visual Studio 2008 : How to add Visualizer for LINQ

    • 6 Comments

    There are two very important visualizers for LINQ which are not part of default installation.

     

    The visualizers are

    Ø       ExpressionTree Visualizer

    Ø       SqlServerQuery Visualizer  

     

    Both the visualizers are available from

     

    Visual Basic

    Samples for Visual Studio 2008 Beta 2

     

    Visual C#

    LINQ and language samples for Visual Studio 2008 Beta 2

     

    Once you install them you will find the ExpressionTree Visualizer and SqlServerQuery Visualizer under

     

    ..My Documents\Visual Studio Samples\LinqSamples\

    ExpressionTreeVisualizer\ExpressionTreeVisualizer

     

    ..My Documents\Visual Studio Samples\LinqSamples

    \QueryVisualizer\SqlServerQueryVisualizer

     

    If you open the *.proj from these two folders and compile them you will get two dlls

    Ø       ExpressionTreeVisualizer.dll

    Ø       LinqToSqlQueryVisualizer.dll

     

    Under bin\debug or bin\release folder depending on the type of build you select. Now copy both the dlls and drop them to the folder located under My Documents

     

    ..My Documents\Visual Studio 2008\Visualizers

     

    Once it is there, and you are in the debug mode inside your visual studio 2008 these two options will come respectively.

     

    View of LinqToSqlQuery Viauslizer

     

    ExpressionTree

     

    Now click on the "Linq to SQL Debugger Visualizer" button,

     

    Sql

     

     

     

     

    View of ExpressionTree Viauslizer

     

    Sql

     

    Now click on the "Expression Tree Visualizer" button,

     

    Sql

     

     

     

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Change in DataContext from Beta 1 to Beta 2

    • 2 Comments

    During the demonstration I have noticed that two major methods of DataContext has changed from Beta 1 to Beta 2.

     

    First one, is the new name for the class DataShape(). Now there is no class called DataShape() but we have DataLoadOptions().

     

     

    var loadOption = new DataLoadOptions();           

    loadOption.LoadWith<Customer>(c => c.Orders);

     

    db.LoadOptions = loadOption;

     

    Secondly,

     

    DataContext does not have any method called GetQueryText() it got changed to GetCommand().

     

    Please let me know if you find something else. I will update this entry.

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Paging Data

    • 12 Comments

    When we work with relational database and especially when with larger data we generally go for custom paging. This allows us to create superfast application.

     

    The simple logic for paged query is like,

     

    Let’s suppose you have 100 rows. Now you want to get data of page size 10 for page 2.

     

    So the starting record would be

     

    StartRecord = (PageNumber – 1) * PageSize

     

    For page 2 it would be

     

    StartRecord = (2 – 1) * 10 = 10 (may be you need to +1 to skip one record)

     

    Now when this comes to LINQ to SQL scenario remains the same where the implementation is little different.

     

    NorthWindDataContext db = new NorthWindDataContext();

     

    var query = from c in db.Customers

                select c.CompanyName;

     

    //Assuming Page Number = 2, Page Size = 10

    int iPageNum = 2;

    int iPageSize = 10;

     

    var PagedData = query.Skip((iPageNum - 1) * iPageSize).Take(iPageSize);

     

    ObjectDumper.Write(PagedData);

     

    Generated T-SQL,

    For SQL Server 2000 the provider does not support Skip().

     

    For SQL Server 2005

    ===================

    SELECT [t1].[CompanyName]
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CompanyName]) AS [ROW_NUMBER], [t0]
    .[CompanyName]
        FROM [dbo].[Customers] AS [t0]
        ) AS [t1]
    WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
    ORDER BY [t1].[ROW_NUMBER]
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30428.1

     

     

    Namoskar!!!

  • Wriju's BLOG

    Visual Studio 2008 Automatic Property is the default snippet

    • 18 Comments

    Now if you type prop and hit "tab" twice, this will bring the Automatic property not the conventional property declaration.

    This is my new finding.

    Namoskar!!!

  • Wriju's BLOG

    Have you tried LINQPad (Beta)?

    • 1 Comments

    Experiment and learn new concept of LINQ. This is just an exe which requires no extra installation.

    Pre-requisites

    Download

    http://www.albahari.com/LINQPad.exe

    For more information please visit,

    http://www.albahari.com/linqpad.html

    Aha this is from my screen,

     LINQPad

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Using Transaction

    • 14 Comments

    LINQ to SQL uses optimistic concurrency be default. LINQ to SQL is pure in memory operation, because you normally get the data from database and store them in .NET memory and perform some pure in memory update to those objects and finally when you are done with your modification you give those data back to the database. But what if someone changes the data in between; this is quite possible in network scenario. The default behavior in LINQ to SQL is that whoever hits the database wins the race. This is called optimistic concurrency. You can implement pessimistic concurrency with the new Transaction in .NET 3.0.

     

    Let’s check it out

     

    I have a table

     

    Emp

    Id       int     

    Name  varchar(50)

     

    Let’s have some dummy data,

    Id      Name

    1        C# 1.0

    2        C# 2.0

    3        C# 3.0

    4        Orcas

     

    Now I want to play with the Id = 4, and modify as per the condition.

     

    Need this basic,

    [Table(Name="Emp")]

    public class Emp

    {

        [Column(IsPrimaryKey=true, IsDBGenerated=true)]

        public int Id { get; set; }

     

        [Column]

        public string Name { get; set; }

    }

     

    public class TestDB : DataContext

    {

        public Table<Emp> Emps;

     

        public TestDB(string s):base(s){}       

    }

     

    Now using this code I will write an application to update data in the database.

     

    static void Main(string[] args)

    {

        Console.Title = "LINQ to SQL Demo";

     

        string sConn = @"Database connection string";

        TestDB db = new TestDB(sConn);

     

        ObjectDumper.Write(db.Emps);  

           

        //Get the element you want to modify

        var query = db.Emps.First(e => e.Id == 4);

     

        //Update the memory object

        if (query.Name == "Orcas")

            query.Name = "Visual Studio 2008 Beta 2";

        else

            query.Name = "Orcas";

     

        //Just wait for other application to make changes

        //this is intetional as we need to throw an error

        Console.WriteLine("Ready to edit. Press any key..");

        Console.Read();

     

        //Update the database

        db.SubmitChanges();

       

        //Show the changed value

        ObjectDumper.Write(db.Emps);           

     

        }

    }

     

    Now if you compile and run this app in two different command window and both of them will come and wail with the line, “Ready to Edit. Press any key to continue..”.

     

    Whichever you click first will update the data and the second one will throw you and error.

     

    Unhandled Exception: System.Data.Linq.ChangeConflictException: Row not found or changed.

     

    This is optimictic concurrency, who executes first wins the race and other fails because there is a conflict in the actual source and the in memory data which you are modifying.

     

    Now if you inplement the same code with TransactionScope class which is new in .NET Framework 2.0.

     

     

    static void Main(string[] args)

    {

        Console.Title = "LINQ to SQL Demo";

     

        string sConn = @"Database connection";

        TestDB db = new TestDB(sConn);

     

        ObjectDumper.Write(db.Emps);  

       

        //This new Transaction class in .NET Framework 3.0

        using (TransactionScope ts = new TransactionScope())

        {

            //Get the element you want to modify

            var query = db.Emps.First(e => e.Id == 4);

     

            //Update the memory object

            if (query.Name == "Orcas")

                query.Name = "Visual Studio 2008 Beta 2";

            else

                query.Name = "Orcas";

     

            //Just wait for other application to make changes

            //this is intetional as we need to throw an error

            Console.WriteLine("Ready to edit.Press any key..");

            Console.Read();

     

            //Update the database

            db.SubmitChanges();

           

            //Complete the Transaction

            ts.Complete();

        }       

      

        //Show the changed value

        ObjectDumper.Write(db.Emps);           

    }

     

    This again uses the same behavior but with managed scope. Especially when you have multiple update happening and the error is more tempting I must say,

     

    Unhandled Exception: System.Data.SqlClient.SqlException: Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

     

    No one likes to be a deadlock victim.

     

    Namoskar!!!

  • Wriju's BLOG

    S. Somasegar (Soma) and Scott Guthrie on Orcas Beta 2

    • 1 Comments

    Channel 9 video at http://channel9.msdn.com/showpost.aspx?postid=329443

    Namoskar!!!

  • Wriju's BLOG

    I love Visual Studio 2008

    • 2 Comments

    I am using Visual Studio 2008 Beta 2

    VS2008

    Namoskar!!!

  • Wriju's BLOG

    C# 3.0 Specification is available

    • 2 Comments

    The 507 page specification is available at MSDN. Download from http://download.microsoft.com/download/3/8/8/388e7205-bc10-4226-b2a8-75351c669b09/CSharp%20Language%20Specification.doc

    Namoskar!!!

  • Wriju's BLOG

    MSDN Magazine : Data Point Articles

    • 1 Comments
  • Data Points: ADO.NET Entity Framework Overview
  • Data Points: Contrasting the ADO.NET DataReader and DataSet by John Papa
  • Data Points: Data Bound Applications with ADO.NET and Custom Objects by John Papa
  • ADO.NET: Tackle Data Concurrency Exceptions Using the DataSet Object by David Burgett
  • Data Points: DataSet and DataTable in ADO.NET 2.0 by John Papa
  • Data Access: Implement a Data Access Layer for Your App with ADO.NET by Dan Fox
  • Data Points: Managing Hierarchical Inserts in ASP.NET and ADO.NET by John Papa
  • Data Points: Handling Data Concurrency Using ADO.NET by John Papa
  • Data Points: Exploring the ADO.NET DataRow by John Papa
  • Namoskar!!!

  • Wriju's BLOG

    Visual Studio 2008 Beta 2 is here

    • 5 Comments

    aa718325_vs2008_hero_left

    Choose the one you want

  • Visual Studio 2008 Standard Edition Beta 2
  • Visual Studio 2008 Professional Edition Beta 2
  • Visual Studio Team System 2008 - Team Suite Beta 2
  • Visual Studio Team System 2008 - Team Foundation Server Beta 2
  • Visual Studio Team System 2008 - Test Load Agent Beta 2
  • MSDN Library for Visual Studio 2008 Beta 2
  • Namoskar!!!

  • Wriju's BLOG

    LINQ and C# 3.0 Articles and Samples

    • 0 Comments

    LINQ: .NET Language Integrated Query

    By Don Box, Anders Hejlsberg


    LINQ to SQL: .NET Language-Integrated Query for Relational Data

    By Dinesh Kulkarni, Luca Bolognese, Matt Warren, Anders Hejlsberg, Kit George


    .NET Language-Integrated Query for XML Data

    By Michael Champion


    The .NET Standard Query Operators

    By Anders Hejlsberg, Mads Torgersen


    Overview of C# 3.0

    By Anders Hejlsberg, Mads Torgersen

     

    Orcas Beta Samples

    http://go.microsoft.com/fwlink/?LinkID=85559

    (Above link will always point you to the updated sample)

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Object Relational (O/R) Model

    • 1 Comments

    LINQ to SQL is pure Object Relational Model introduced by Microsoft. It used mapping mechanism to represent

     

    Relational Components

    Object Component

    Table

    Class

    Column

    Property

    Foreign-key relationship

    Association

     

    It gets the user input through this object model and gives the translated value to SQL in form of pure T-SQL query and when query gives something back it again translates back to the strongly typed object model. There is a very close relationship between Object and Relation here.

     

    You can perform all sort of data manipulation process offline (like INSERT) and store them in collection. Once you are okay with the set you use DataContext class to submit the changes back to the database. Then LINQ to SQL translates the object to set of parameterized T-SQL query and gives the input to database.

     

    LINQ to SQL Object Model

     

    In lot of scenarios LINQ to SQL is faster than pure ADO.NET approach and with Orcas Beta 2 it started giving much better performance. LINQ to SQL is highly capable to running DML operations.

     

    For modifications over the existing data it uses optimistic concurrency which helps us to prevent the data manipulation conflicts which is again an added advantage.

     

    For more please visit MSDN Site.

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Caching the query execution

    • 9 Comments

    LINQ to SQL executes or generated SQL statement only when you run a foreach statement or you perform to ToList() method to it. If you need to display the output multiple times without executing the real database query, you can store them in memory. This can be done only when you are sure that the data is static.

     

    Let’s suppose,

     

    static void Main(string[] args)

    {

        //This takes the connection string from file Settings.settings

        //which gets generated while creating Linq to Sql (.dml) file

        NorthwindDBDataContext db = new NorthwindDBDataContext();

     

        db.Log = Console.Out;

       

        //Get the Customers from database

        var query = from c in db.Customers

                    where c.City == "London"

                    select c;

     

        //This point the query gets executed

        foreach (var c in query)

            Console.WriteLine(c.CompanyName);

     

        //This point the query AGAIN gets executed

        foreach (var c in query)

            Console.WriteLine(c.CompanyName);

     

    }

     

     

    The output will look like

     

    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT

    itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun

    try], [t0].[Phone], [t0].[Fax]

    FROM [dbo].[Customers] AS [t0]

    WHERE [t0].[City] = @p0

    -- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]

    SqlProvider\AttributedMetaModel

     

    Around the Horn

    Consolidated Holdings

    Eastern Connection

    North/South

    Seven Seas Imports

     

     

    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT

    itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun

    try], [t0].[Phone], [t0].[Fax]

    FROM [dbo].[Customers] AS [t0]

    WHERE [t0].[City] = @p0

    -- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]

    SqlProvider\AttributedMetaModel

     

    Around the Horn

    Consolidated Holdings

    Eastern Connection

    North/South

    Seven Seas Imports

     

     

    Now when I am sure that my data is not changing there is no point again going back to the database and execute the data for another operation. Rather what I can do is that I can cache the output and store them in some object.

     

    Now if I execute the code like,

     

    static void Main(string[] args)

    {

        //This takes the connection string from file Settings.settings

        //which gets generated while creating Linq to Sql (.dml) file

        NorthwindDBDataContext db = new NorthwindDBDataContext();

     

        db.Log = Console.Out;

       

        //Get the Customers from database

        var query = from c in db.Customers

                    where c.City == "London"

                    select c;

     

        var listCusts = query.ToList();

     

        //This point the query does not get executed

        foreach (var c in listCusts)

            Console.WriteLine(c.CompanyName);

     

        Console.WriteLine("+++++");

     

        //This point the query ALSO does not get executed

        foreach (var c in listCusts)

            Console.WriteLine(c.CompanyName);

     

    }

     

     

    Now the output will look like,

     

    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT

    itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun

    try], [t0].[Phone], [t0].[Fax]

    FROM [dbo].[Customers] AS [t0]

    WHERE [t0].[City] = @p0

    -- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]

    SqlProvider\AttributedMetaModel

     

    Around the Horn

    Consolidated Holdings

    Eastern Connection

    North/South

    Seven Seas Imports

    +++++

    Around the Horn

    Consolidated Holdings

    Eastern Connection

    North/South

    Seven Seas Imports

     

    Isn’t it better???

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Deferred and Immediate Loading

    • 2 Comments

    LINQ to SQL has support for both deferred and immediate loading. By default it supports deferred loading but you can configure your code to load the data immediately. Let’s consider the Northwind database. As we know the there are two tables Customer and Orders and one customer can have multiple orders. These two tables have relationship defined in the database. If you use Linq to Sql (.dbml) designer which generates the background code for us. Once you drag both Customers and Orders you will get the following view in designer

     

    Linq to Sql designer

     

    Then in the console I will write the following code,

     

    static void Main(string[] args)

    {

        //This takes the connection string from file Settings.settings

        //which gets generated while creating Linq to Sql (.dbml) file

        NorthwindDBDataContext db = new NorthwindDBDataContext();

     

        //Get the Customers from database

        var query = from c in db.Customers

                    where c.City == "London"

                    select c;

     

        db.Log = Console.Out;

     

        foreach (var c in query)

        {

            Console.WriteLine("++++++++++++++++++++++++++++++");

            Console.WriteLine("Customer Id : " + c.CustomerID);

            Console.WriteLine("++++++++++++++++++++++++++++++");

            foreach (var o in c.Orders)

            {

                //Getting the orders value for

                //each Customers

                Console.WriteLine(o.OrderID);

            }

            Console.WriteLine();

        }

    }

     

     

     

    The output will show up

     

    ++++++++++++++++++++++++++++++

    Customer Id : AROUT

    ++++++++++++++++++++++++++++++

    SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],

    t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[S

    ipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPost

    lCode], [t0].[ShipCountry]

    FROM [dbo].[Orders] AS [t0]

    WHERE [t0].[CustomerID] = @p0

    -- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) NOT NULL [AROUT]

    SqlProvider\AttributedMetaModel

     

    10355

    10383

    10453

    10558

    10707

    10741

    10743

    10768

    10793

    10864

    10920

    10953

    11016

     

    ++++++++++++++++++++++++++++++

    Customer Id : BSBEV

    ++++++++++++++++++++++++++++++

    SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],

    t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[S

    ipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPost

    lCode], [t0].[ShipCountry]

    FROM [dbo].[Orders] AS [t0]

    WHERE [t0].[CustomerID] = @p0

    -- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) NOT NULL [BSBEV]

    SqlProvider\AttributedMetaModel

     

    10289

    10471

    10484

    10538

    10539

    10578

    10599

    10943

    10947

    11023

     

     

    Every time for each customer this will run a query to fetch the corresponding orders. This is welcomed behavior in most web scenarios as we may need to perform some action like click to view the data.

     

    But if you would like to execute the query at one shot and then display the order values (immediate loading) then you need to modify the above code

     

    static void Main(string[] args)

    {

        //This takes the connection string from file Settings.settings

        //which gets generated while creating Linq to Sql (.dml) file

        NorthwindDBDataContext db = new NorthwindDBDataContext();

     

        //This part is new

        var shape = new DataShape();

        //This indicates that I am loading Orders with Customer   

        shape.LoadWith<Customer>(c => c.Orders);

     

        db.Shape = shape;

     

        //end of new part

        

     

        //Get the Customers from database

        var query = from c in db.Customers

                    where c.City == "London"

                    select c;

     

        db.Log = Console.Out;

     

        foreach (var c in query)

        {

            Console.WriteLine("++++++++++++++++++++++++++++++");

            Console.WriteLine("Customer Id : " + c.CustomerID);

            Console.WriteLine("++++++++++++++++++++++++++++++");

            foreach (var o in c.Orders)

            {

                //Getting the orders value for

                //each Customers

                Console.WriteLine(o.OrderID);

            }

            Console.WriteLine();

        }

     

     

    Now the generated query will look like,

     

    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[Contact

    itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Cou

    try], [t0].[Phone], [t0].[Fax], [t1].[OrderID], [t1].[CustomerID] AS [CustomerI

    2], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate

    , [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[Sh

    pCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry], (

        SELECT COUNT(*)

        FROM [dbo].[Orders] AS [t2]

        WHERE [t2].[CustomerID] = [t0].[CustomerID]

        ) AS [count]

    FROM [dbo].[Customers] AS [t0]

    LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]

    WHERE [t0].[City] = @p0

    ORDER BY [t0].[CustomerID], [t1].[OrderID]

    -- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]

    SqlProvider\AttributedMetaModel

     

    ++++++++++++++++++++++++++++++

    Customer Id : AROUT

    ++++++++++++++++++++++++++++++

    10355

    10383

    10453

    10558

    10707

    10741

    10743

    10768

    10793

    10864

    10920

    10953

    11016

     

    ++++++++++++++++++++++++++++++

    Customer Id : BSBEV

    ++++++++++++++++++++++++++++++

    10289

    10471

    10484

    10538

    10539

    10578

    10599

    10943

    10947

    11023

     

     

    Namoskar!!!

  • Wriju's BLOG

    Celebrating my 100 posts!!!

    • 5 Comments

    Thanks to everyone who supported me in this BLOGGING journey. I started blogging back in May 2006, was not very regular blogger till September 2006. I blog to share my .NET adventure, starting from C# 2005 to LINQ (C# 3.0). I use them to convey the interest for others.

     

    In Microsoft I work for independent software vendors who write code and use Microsoft technology to develop their product.

     

    Keep visiting blog and keep commenting on my blog.

     

    Cheers and Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Writing DML query to perform quick operation

    • 1 Comments

    In LINQ to SQL you also can send the DML query to perform the quick database modification.

     

    Caution!!! It is not recommended as it does not support object model so has no compile time checking.

     

    static void Main(string[] args)

    {

        string strConnection = @"Data Source=wriju-orcas\sqlexpress;Initial Catalog=TestDB;";

     

        TestDB db = new TestDB(strConnection);

     

        //To check the SQL query

        db.Log = Console.Out;

     

        //INSERT

        db.ExecuteCommand("INSERT INTO Emp(Name) VALUES('Wriju')", "");

     

        //DELETE

        db.ExecuteCommand("DELETE FROM Emp WHERE Id = 19", "");

     

        //UPDATE

        db.ExecuteCommand("UPDATE Emp SET Name = 'A' WHERE Id = 21", "");

     

        //Check the rows inside table

        ObjectDumper.Write(db.Emps);

    }

     

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Delete data through Object Model

    • 2 Comments

    LINQ to SQL support deleting data through object. Continuing with my previous blog on INSERT, let me discuss about the delete method

     

    static void Main(string[] args)

    {

        string strConnection = @"Connection String";

     

        TestDB db = new TestDB(strConnection);

     

        db.Log = Console.Out;

           

        //Here I am finding the employee with Id 18

        //You can get an error if id does not exists

        var deleteQ = db.Emps.First(e => e.Id == 18);

       

        //Delete the specific entry from collection

        db.Emps.Remove(deleteQ);

     

        //Commit the changes to database

        //at this point DML gets generated

        db.SubmitChanges();

            

    }

     

    Now the line db.Log = Console.Out; gives me the SQL query generated by the background engine.

     

     

    DELETE FROM [Emp] WHERE ([Name] = @p0) AND ([Id] = @p1)

    SELECT

        (CASE

            WHEN (@@ROWCOUNT) > 0 THEN @@ROWCOUNT

            ELSE (

                SELECT -1

                WHERE NOT (EXISTS(

                    SELECT NULL AS [EMPTY]

                    FROM [Emp] AS [t1]

                    WHERE [t1].[Id] = @p2

                    ))

                )

         END) AS [value]

    -- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [Writam]

    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) NOT NULL [18]

    -- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) NOT NULL [18]

    SqlProvider\AttributedMetaModel

     

     

    All the methods are coming from DataContext class (responsible for SQL query generation). The above method converts the object addition to DML query.

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Update data through Object Model

    • 14 Comments

    LINQ to SQL support updating data through object. Continuing with my previous blog on INSERT, let me discuss about the update method

     

    static void Main(string[] args)

    {

        string strConnection = @"Connection String";

     

        TestDB db = new TestDB(strConnection);

       

        //Here I am finding the employee with Id 19

        var updateQ = db.Emps.First(e => e.Id == 19);

     

        //Then I will modify that employee name and give new name

        updateQ.Name = "Updated Employee";

                               

        //Commit the changes to database

        //at this point DML gets generated

        db.SubmitChanges();        

     

        //To view the updated data

        ObjectDumper.Write(db.Emps);

    }

     

    All the methods are coming from DataContext class (responsible for SQL query generation). The above method converts the object addition to DML query.

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL support inserting data through object.

    • 6 Comments

    LINQ to SQL support inserting data through object.

     

    using System;

    using System.Collections.Generic;

    using System.Linq;

    using System.Text;

    using System.Data.Linq;

     

    namespace Linq2Sql_demo_doc

    {

        /// <summary>

        /// Class to represent the SQL Server table

        /// Emp

        /// Id int

        /// Name varchar(50)

        /// </summary>    ///

        [Table(Name="Emp")]

        public class Emp

        {

            //Comlumn mapping used from Data.Linq

            [Column(IsPrimaryKey=true, IsDBGenerated=true)]

            public int Id { get; set; }

     

            [Column]

            public string Name { get; set; }

        }

     

        //Class for the DataContext and List generation

        public class TestDB : DataContext

        {

            public Table<Emp> Emps;

     

            //Initializing base class constructor

            public TestDB(string s) : base(s) { }

        }   

     

        class Program

        {

            static void Main(string[] args)

            {

                string strConnection =

    @"Data Source=.\sqlexpress;Initial Catalog=TestDB;";

     

                TestDB db = new TestDB(strConnection);

     

                Emp emp = new Emp();

     

                emp.Name = "New Employee";

                db.Emps.Add(emp);

                db.SubmitChanges();        

     

            }

        }

    }

     

    All the methods are coming from DataContext class (responsible for SQL query generation). The above method converts the object addition to DML query.

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Executing DML SQL Query

    • 1 Comments

    INSERT/DELETE/UPDATE can be performed in LINQ to SQL through normal T-SQL DML queries,

     

    static void Main(string[] args)

    {

        NorthwindDBDataContext db = new NorthwindDBDataContext();

     

    var query = db.ExecuteCommand("YOUR DML Query", "Your    Parameter");

     

        ObjectDumper.Write(query);

    }

     

    Are you feeling comfortable? Hold on, please try to avoid this as you have better object model approach.

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Executing SQL Query

    • 0 Comments

    Yes, you can run normal SQL queries in LINQ to SQL. Sample looks like (but not recommended as you do not have statement completion and validation)

     

    static void Main(string[] args)

    {

        NorthwindDBDataContext db = new NorthwindDBDataContext();

     

        var query = db.ExecuteQuery<Customer>("SELECT * FROM Customers", "");

     

        ObjectDumper.Write(query);

    }

     

    One of the benefits of this approach is that you may get your comfort zone. Major drawback is you always need a representable CRL type to store the output.

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL (aka DLinq) 34 minutes overview demo

    • 4 Comments

    I have recorded a small 34 minutes session on LINQ to SQL. Covering the topics on

    > Object Model

    > Select/Insert/Update/Delete

    > Stored Procedure Support

    > Linq to Sql designer




    Video: Overview of Linq to Sql

    Namoskar!!!

  • Page 21 of 25 (618 items) «1920212223»