Nisi’s work blog

How to create a new xml with LINQ

[sourcecode language='csharp ']

var objCars= new[]
{
new {CarID = 2, CarName = “Ford”, Fuel = “Diesel”},
new {CarID = 3, CarName = “Audi”, Fuel = “Diesel”},
new {CarID = 4, CarName = “Mercedes”, Fuel = “Diesel”},
new {CarID = 1, CarName = “BMW”, Fuel = “Diesel”}
};

XElement _cars = new XElement(“cars”,
from c in objCars
select new XElement(“car”,
new XElement(“name”, c.CarName),
new XAttribute(“ID”, c.CarID),
new XElement(“Fuel”, c.Fuel)
)
);
Console.WriteLine(_cars);

[/sourcecode]
so this will print something like:

Ford
Diesel

Audi
Diesel

Mercedes
Diesel

BMW
Diesel

string s = String.Format("{{ hello to all }}");
Console.WriteLine(s);    //prints '{ hello to all }' 

int i = 42;
string s = String.Format("{0}", i);   //prints '42' 

int i = 42;
string s = String.Format("{{{0}}}", i);   //prints '{42}' 

int i = 42;
string s = String.Format("{0:N}", i);   //prints '42.00' 

int i = 42;
string s = String.Format("{{{0:N}}}", i);   //prints '{N}' 

int i = 42;
string s = String.Format("{0:N!}", i);   //prints 'N!' 

int i = 42;
string s = String.Format("{{{0:N}}}", i);   //prints '{N}' 

string s =
String.Format("{0}{1}{2}", "{", i, "}");   //prints '{42.00}' 

int i = 42;
string s = String.Format("{{{0}}}", i.ToString("N")) ;   //prints '{42.00}' 

int i = 42;
string s = String.Format("{0:{{0.00}}}", i);   //prints '{42.00}' 

int i = 42;
string s = String.Format("{0,-7:N}", i);   //prints '42.00  ', ",-7" left-justifies the string 

int i = 42;
string s1 = String.Format("{{{0,-7:N}}}", i);   //prints '{42.00  }'
string s2 = String.Format("{{{0,-7}}}", i.ToString("N")) ;   //prints '{42.00  }'
string s3 = String.Format("{0,-9:{{0.00}}}", i);   //prints '{42.00}  ' 

int i = 42000;
string s = String.Format("{0,-15:{{#,##0.00}}}", i);   //prints '{42,000.00}     '

LINQ to DataSet

• Perform set operations on sequences of DataRow objects.
• Retrieve and set DataColumn values
• Obtain a LINQ standard IEnumerable sequence from a DataTable so Standard Query Operatorsmay be called.

To use LINQ to Dataset include next namespaces

using System.Data; using System.Linq;

Step by Step Exemple:

Starting with a simple class:

class Student {
      public int Id;
      public string Name;
 }

this class is like a table where we have an row Id (public int Id) and a value (here is Name public string Name)

Next convert a list of Student Class in a Data Table

     // create DataTable
      DataTable table = new DataTable();
      table.Columns.Add("Id", typeof(Int32));
      table.Columns.Add("Name", typeof(string));
         // fill with info
     foreach (Student student in students){
          table.Rows.Add(student.Id, student.Name);
     }

Other Query examples:

There are to way of write a LINQ Query
Query expression
and
Method Query

// Query expression example
var query = from r in customerDataTable.AsEnumerable()
     where r.Field("LastName") == "Smith"
     select r.Field(“FirstName”);

// same example in Method query
var query = customerDataTable.AsEnumerable()
            .Where(dr => dr.Field("LastName") == "Smith")
            .Select(dr => dr.Field("FirstName"));

Buy Visual studio:

• Microsoft Visual Studio 2008 Professional 632$
• Microsoft Visual Studio 2008 Standard 240$
• Microsoft Visual Studio 2008 Professional Upgrade 452$
• Microsoft Visual Studio 2008 Standard Upgrade 165$
• Microsoft Visual Studio 2008 Professional with MSDN Premium 2225$

LINQ (Language integrated Query)

string[] numbers = { "0042", "010", "9", "27" };
int[] nums = numbers.Select(s => Int32.Parse(s)).ToArray();
foreach (var num in nums)
Console.WriteLine(num);

What where LINQ is useful?

LINQ to Objects -

LINQ to XML -

LINQ to SQL -

LINQ to DataSet

• Perform set operations on sequences of DataRow objects.
• Retrieve and set DataColumn values
• Obtain a LINQ standard IEnumerable sequence from a DataTable so Standard Query Operatorsmay be called.

LINQ to Entities -

101 LinQ Samples

What You can do with LINQ

int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
// select var numsPlusOne =
from n in numbers
select n + 1;
// where var lowNums =

from n in numbers
       where n < 5
       select n;

// groupby var numberGroups =
       from n in numbers
       group n by n % 5 into g
       select new { Remainder = g.Key, Numbers = g };

// many select
     int[] numbersA = { 0, 2, 4, 5, 6, 8, 9 };
     int[] numbersB = { 1, 3, 5, 7, 8 };
     var pairs =
         from a in numbersA
               from b in numbersB
               where a < b
               select new {a, b};

// Take - first n elements.
     int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
     var first3Numbers = numbers.Take(3);
     Console.WriteLine("First 3 numbers:");
     foreach (var n in first3Numbers) {
         Console.WriteLine(n);
     }        

// Skip - This sample uses Skip to get all but the first 4 elements of the array.
     int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
     var allButFirst4Numbers = numbers.Skip(4);
     var firstNumbersLessThan6 = numbers.TakeWhile(n => n > 6);        

// TakeWhile  (take 5, 4, 1 3)
     int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
     var firstNumbersLessThan6 = numbers.TakeWhile(n => n > 6);       

// SkipWhile (allButFirst3Numbers  contain 3, 9, 8, 6, 7, 8, 0)
     int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
     var allButFirst3Numbers = numbers.SkipWhile(n => n % 3 != 0);       

// OrderBy
     string[] words = { "cherry", "apple", "blueberry" };
     var sortedWords =
          from w in words
          orderby w   /* [ascending][descending] */
          select w;   

/* ascending or descending are optional, if order direction is missing
(ascending/descending) by default is sort ascending*/       

// Reverse
     string[] digits = { "zero", "one", "two", "three", "four",
                               "five", "six", "seven", "eight", "nine" };
     var reversedIDigits = (
     from d in digits select d)
           .Reverse();       

/* exemple reverse digits list*/       

// DISTINCT
    int[] factorsOf300 = { 2, 2, 3, 5, 5 };
    var uniqueFactors = factorsOf300.Distinct();
/* uniqueFactors become something like 2, 3, 5*/      

// UNION
    int[] numbersA = { 0, 2, 4, 5, 6, 8, 9 };
    int[] numbersB = { 1, 3, 5, 7, 8 };
    var uniqueNumbers = numbersA.Union(numbersB);
/* append numbersB to numbersA and store it in uniqueNumbers  */      

// INTERSECT
    int[] numbersA = { 0, 2, 4, 5, 6, 8, 9 };
    int[] numbersB = { 1, 3, 5, 7, 8 };
    var commonNumbers = numbersA.Intersect(numbersB);      

// EXCEPT
    int[] numbersA = { 0, 2, 4, 5, 6, 8, 9 };
    int[] numbersB = { 1, 3, 5, 7, 8 };
    IEnumerable aOnlyNumbers = numbersA.Except(numbersB);    

 /* aOnlyNumbers contain just numbers who are in one list and not in the other */

Buy Visual studio:

Microsoft Visual Studio 2008 Professional 632$
Microsoft Visual Studio 2008 Standard 240$
Microsoft Visual Studio 2008 Professional Upgrade 452$
Microsoft Visual Studio 2008 Standard Upgrade 165$
Microsoft Visual Studio 2008 Professional with MSDN Premium 2225$

Startup for LINQ to SQL with C#

This walkthrough requires the following:

This walkthrough uses a dedicated folder (“c:\linqtest6″) to hold files. Create this folder before you begin the walkthrough.

The Northwind sample database.

If you do not have this database on your development computer, you can download it from the Microsoft download site. For instructions, see Downloading Sample Databases (LINQ to SQL). After you have downloaded the database, copy the northwnd.mdf file to the c:\linqtest6 folder.

A C# code file generated from the Northwind database.

You can generate this file by using either the Object Relational Designer or the SQLMetal tool. This walkthrough was written by using the SQLMetal tool with the following command line:

sqlmetal /code:”c:\linqtest6\northwind.cs” /language:csharp “C:\linqtest6\northwnd.mdf” /pluralize

For more information, see Code Generation Tool (SqlMetal.exe).
Overview

This walkthrough consists of six main tasks:

Creating the LINQ to SQL solution in Visual Studio.

Adding the database code file to the project.

Creating a new customer object.

Modifying the contact name of a customer.

Deleting an order.

Submitting these changes to the Northwind database.
Creating a LINQ to SQL Solution

In this first task, you create a Visual Studio solution that contains the necessary references to build and run a LINQ to SQL project.
To create a LINQ to SQL solution

On the Visual Studio File menu, point to New, and then click Project.

In the Project types pane in the New Project dialog box, click Visual C#.

In the Templates pane, click Console Application.

In the Name box, type LinqDataManipulationApp.

In the Location box, verify where you want to store your project files.

Click OK.
Adding LINQ References and Directives

This walkthrough uses assemblies that might not be installed by default in your project. If System.Data.Linq is not listed as a reference in your project, add it, as explained in the following steps:
To add System.Data.Linq

In Solution Explorer, right-click References, and then click Add Reference.

In the Add Reference dialog box, click .NET, click the System.Data.Linq assembly, and then click OK.

The assembly is added to the project.

Add the following directives at the top of Program.cs:
C#

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

Adding the Northwind Code File to the Project

These steps assume that you have used the SQLMetal tool to generate a code file from the Northwind sample database. For more information, see the Prerequisites section earlier in this walkthrough.
To add the northwind code file to the project

On the Project menu, click Add Existing Item.

In the Add Existing Item dialog box, navigate to c:\linqtest6\northwind.cs, and then click Add.

The northwind.cs file is added to the project.
Setting Up the Database Connection

First, test your connection to the database. Note especially that the database, Northwnd, has no i character. If you generate errors in the next steps, review the northwind.cs file to determine how the Northwind partial class is spelled.
To set up and test the database connection

Type or paste the following code into the Main method in the Program class:
C#

// Use the following connection string.
Northwnd db = new Northwnd(@"c:\linqtest6\northwnd.mdf");    

// Keep the console window open after activity stops.
Console.ReadLine();

Press F5 to test the application at this point.

A Console window opens.

You can close the application by pressing Enter in the Console window, or by clicking Stop Debugging on the Visual Studio Debug menu.
Creating a New Entity

Creating a new entity is straightforward. You can create objects (such as Customer) by using the new keyword.

In this and the following sections, you are making changes only to the local cache. No changes are sent to the database until you call SubmitChanges toward the end of this walkthrough.
To add a new Customer entity object

Create a new Customer by adding the following code before Console.ReadLine(); in the Main method:
C#

// Create the new Customer object.
Customer newCust = new Customer();
newCust.CompanyName = "AdventureWorks Cafe";
newCust.CustomerID = "ADVCA";    

// Add the customer to the Customers table.
db.Customers.InsertOnSubmit(newCust);    

Console.WriteLine("\nCustomers matching CA before insert");    

foreach (var c in db.Customers.Where(cust => cust.CustomerID.Contains("CA")))
{
Console.WriteLine("{0}, {1}, {2}",
c.CustomerID, c.CompanyName, c.Orders.Count);
}

Press F5 to debug the solution.

Press Enter in the Console window to stop debugging and continue the walkthrough.
Updating an Entity

In the following steps, you will retrieve a Customer object and modify one of its properties.
To change the name of a Customer

Add the following code above Console.ReadLine();:
C#

// Query for specific customer.
// First() returns one object rather than a collection.
var existingCust =
(from c in db.Customers
where c.CustomerID == "ALFKI"
select c)
.First();    

// Change the contact name of the customer.
existingCust.ContactName = "New Contact";

Deleting an Entity

Using the same customer object, you can delete the first order.

The following code demonstrates how to sever relationships between rows, and how to delete a row from the database. Add the following code before Console.ReadLine to see how objects can be deleted:
To delete a row

Add the following code just above Console.ReadLine();:
C#

// Access the first element in the Orders collection.
Order ord0 = existingCust.Orders[0];    

// Access the first element in the OrderDetails collection.
OrderDetail detail0 = ord0.OrderDetails[0];    

// Display the order to be deleted.
Console.WriteLine
("The Order Detail to be deleted is: OrderID = {0}, ProductID = {1}",
detail0.OrderID, detail0.ProductID);    

// Mark the Order Detail row for deletion from the database.

Submitting Changes to the Database

The final step required for creating, updating, and deleting objects, is to actually submit the changes to the database. Without this step, your changes are only local and will not appear in query results.
To submit changes to the database

Insert the following code just above Console.ReadLine:
C#

db.SubmitChanges();

Insert the following code (after SubmitChanges) to show the before and after effects of submitting the changes:
C#

Console.WriteLine("\nCustomers matching CA after update");
foreach (var c in db.Customers.Where(cust =>
cust.CustomerID.Contains("CA")))
{
Console.WriteLine("{0}, {1}, {2}",
c.CustomerID, c.CompanyName, c.Orders.Count);
}

Press F5 to debug the solution.

Press Enter in the Console window to close the application.