LINQ To SQL

1) We can use XSD Tool ( Xml Schema Definition Tool ) or XSD.exe to generate class files (.cs or .vb ) as per your requirments from Typed Datasets. XSD.exe is the XML Schema Definition tool which generates XML schema or common language runtime classes from XDR, XML, and XSD files.

The file locates at C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\XSD.exe
OR
C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\Bin Depends up on your version of .Net.

If you are having Express edition of ( V 2.0) then it might possible that you don’t have .Net command prompt option available in Start->Programs ,

then you have to go command prompt and goto location

“C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin”

and then type following with location of .xsd file on prompt

xsd.exe -c -l:c# C:\MyProjects\Employee\EmpTableDataSet.xsd

2 ) This tool can be also be used to generate xml schema file (.xsd) from an xml data file (.xml). Suppose we have file Catelog.xml as shown ,then we can generate XSD files as below from Command prompt.
C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin>xsd.exe C:\CatelogOne.Xml

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin>xsd.exe C:\CatelogOne.Xml Microsoft (R) Xml Schemas/DataTypes support utility
[Microsoft (R) .NET Framework, Version 2.0.50727.42]
Copyright (C) Microsoft Corporation. All rights reserved.
Writing file ‘C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\CatelogOne.xsd’.

3 )To generate schema from a compiled class library ( .dll)
C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin>xsd C:\BLLTable.dll
on enter it will generate the schema of the various types present in the dll.
( Consider Class library you created in project or any other )

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin>xsd C:\BLLTable.dll
Microsoft (R) Xml Schemas/DataTypes support utility
[Microsoft (R) .NET Framework, Version 2.0.50727.42]
Copyright (C) Microsoft Corporation. All rights reserved.
Writing file ‘C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\schema0.xsd’.

Problem:

When you change the structure of your sql tables — say, change a type from a varchar to…  say an int — the linq to sql class model (eg. project.dbml) is not updated or sync’d.

Is there a way to sync the model with the db?

Solution:

1) One way is to delete the ‘table’ in the class model, then drag-n-drop / re-add it back.

2) Another work around:

1. Make the changes you want in the dbml file, and save it, if the designer did not regenerate the code:
2. Rename the dbml file (for example Northwind.dbml to Northwind2.dbml)
3. Make a copy of it (Drag it to the same folder)
4. Now rename the New file to the original name.
5. while renaming I found that the designer has regenerated the code file

The SqlMetal command-line tool generates code and mapping for the LINQ to SQL component of the .NET Framework. By applying options that appear later in this topic, you can instruct SqlMetal to perform several different actions that include the following:

* From a database, generate source code and mapping attributes or a mapping file.
* From a database, generate an intermediate database markup language (.dbml) file for customization.
* From a .dbml file, generate code and mapping attributes or a mapping file.

The SQLMetal file is included in the Windows SDK that is installed with Visual Studio. By default, the file is located at drive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin. If you do not install Visual Studio, you can also get the SQLMetal file by downloading the Windows SDK.

SQLMetal is an all or nothing process.  It includes everything within the database and there is no way to tailor the selection of tables/viewes required. If you do not want select all of the database tables, stored procs etc you will need to use the designer.

SQLMetal is fine for generating the model classes. I am not very fond of the LINQ designer, so typically, I will use the designer in the initial design phase, and later additions or alterations to the datamodel is then done by hand editing the dbml file.

I use this simple command for building my LINQ-to-SQL classes, which I put in a batch file for maximum convenience:

sqlmetal /code:YourCodeFile.designer.cs /namespace:Desired.Namespace YourModel.dbml

The SQLMetal approach works fine for me, and is a reliable way of generating the code. Any handwritten additions to the generated classes, should of course be kept in partial class declarations in a separate code file.

Examples:

Generate a .dbml file that includes extracted SQL metadata:

sqlmetal /server:myserver /database:northwind /dbml:mymeta.dbml

Generate a .dbml file that includes extracted SQL metadata from an .mdf file by using SQL Server Express:

sqlmetal /dbml:mymeta.dbml mydbfile.mdf

Generate a .dbml file that includes extracted SQL metadata from SQL Server Express:

sqlmetal /server:.\sqlexpress /dbml:mymeta.dbml /database:northwind

Generate source code from a .dbml metadata file:

sqlmetal /namespace:nwind /code:nwind.cs /language:csharp mymetal.dbml

Generate source code from SQL metadata directly:

sqlmetal /server:myserver /database:northwind /namespace:nwind /code:nwind.cs /language:csharp

You can extract only functions or only stored procedures or only views using switches:

/views Extracts database views.
/functions Extracts database functions.
/sprocs Extracts stored procedures.

You also have an option to output schema as .dbml file, source code or xml mapping file

/dbml[:file] Sends output as .dbml. Cannot be used with /map option.
/code[:file] Sends output as source code. Cannot be used with /dbml option.
/map[:file] Generates an XML mapping file instead of attributes. Cannot be used with /dbml option.

Miscellaneous output options:

/language:<language> Specifies source code language. Valid <language>: vb, csharp. Default value: Derived from extension on code file name.

/namespace:<name> Specifies namespace of the generated code. Default value: no namespace.

/context:<type> Specifies name of data context class. Default value: Derived from database name.

/entitybase:<type> Specifies the base class of the entity classes in the generated code. Default value: Entities have no base class.

/pluralize Automatically pluralizes or singularizes class and member names. This option is available only in the U.S. English version.

/serialization:<option> Generates serializable classes. Valid <option>: None, Unidirectional. Default value: None.

To update a row in the database

// Query the database for the row to be updated.
var query =
    from ord in db.Orders
    where ord.OrderID == 11000
    select ord;

// Execute the query, and change the column values
// you want to change.
foreach (Order ord in query)
{
    ord.ShipName = "Mariner";
    ord.ShipVia = 2;
    // Insert any additional changes to column values.
}

// Submit the changes to the database.
try
{
    db.SubmitChanges();
}
catch (Exception e)
{
    Console.WriteLine(e);
    // Provide for exceptions.
}

To insert a row into the database

What earlier was adding rows to tables, now is just adding new objects to context collections. When you are ready to send the changes to the database, call SubmitChanges() method of the context. Before doing this, you must first set all properties that do not support null (Nothing) values. The SubmitChanges() method generates and executes commands that perform the equivalent INSERT, UPDATE, or DELETE statements against the data source.

CrmDemoDataContext context = new CrmDemoDataContext();

// Create a new category
ProductCategory newCategory = new ProductCategory();
newCategory.CategoryID = 1000;
newCategory.CategoryName = “New category”;

// Create a new product
Product newProduct = new Product();
newProduct.ProductID = 2000;
newProduct.ProductName = “New product”;
newProduct.Price = 20;

// Associate the new product with the new category
newProduct.ProductCategory = newCategory;
context.Products.InsertOnSubmit(newProduct);

// Send the changes to the database.
// Until you do it, the changes are cached on the client side.
context.SubmitChanges();

// Request the new product from the database
var query = from it in context.Products
where it.ProductID == 2000
select it;

// Since we query for a single object instead of a collection, we can use the method First()
Product product = query.First();
Console.WriteLine(”{0} | {1} | {2}”,
product.ProductCategory.CategoryName, product.ProductName, product.Price);
Console.ReadLine();

The InsertOnSubmit() method is created for every collection in the context. This method stores in the database information about all linked objects. As shown in the example, it is only necessary to call InsertOnSubmit() once to submit both product and category objects.

Note that after you have added the new product and category by submitting the changes, you cannot execute this solution again as is. To execute the solution again, change the IDs of the objects to be added.

LINQ to SQL: .NET Language-Integrated Query for Relational Data” says (last item in the “Column Attribute” section): “Specifies if the column is automatically synchronized from the value generated by the database on insert or update commands.

public enum AutoSync {

Default = 0, // Automatically choose

Always = 1,

Never = 2,

OnInsert = 3,

OnUpdate = 4

}

Now that we have generated our data layer. We will work on ASP.NET web application where we will use our data layer. To keep things simple we will create a web forms to search for customers and display search results. We will also create a web form to insert new customers. Let’s start by creating our web form for customer search. For this we will use the Default.aspx page. We will place few controls on the web form. These controls will give us search parameters and a button which will do the search and display results when clicked. This is what the form will look like after placing our controls.

We will also place a GridView control on our form to display search results. We will now put in some code in our button’s click event handler to do the search and display results in GridView. Make sure that we have a reference to Data Layer project, System.Data.Linq and appropriate using statement. Here is what our button click event handler will contain.

protected void buttonSearch_Click(object sender, EventArgs e)

{

  using (NorthwindDataContext context = new NorthwindDataContext())

  {

    var customers =

      from c in context.Customers

      select c;

    gridViewCustomers.DataSource = customers;

    gridViewCustomers.DataBind();

  }

}

This code will query the customers table in northwind database and will return all customers. We will now modify it slightly to accept customer name and company name as parameters for our query. After modification our event handler looks like this.

protected void buttonSearch_Click(object sender, EventArgs e)

{

  using (NorthwindDataContext context = new NorthwindDataContext())

  {

    var customers =

      from c in context.Customers

      where (

        c.ContactName.Contains(textBoxCustomerName.Text.Trim())

        &&

        c.CompanyName.Contains(textBoxCompanyName.Text.Trim()))

      select c;

      gridViewCustomers.DataSource = customers;

      gridViewCustomers.DataBind();

  }

}

Our search results will now be filtered.

Let us now created a data entry form for customers.  We will insert a new web form in our ASP.NET project and call it CustomerEntry. To start with we will make sure that our form contains fields required to insert a customer. Our form after completion will look like this.

We expect a new row to be inserted into customers table when Save Customer button is clicked. This code achieves data insertion into customers table for us.

protected void buttonSave_Click(object sender, EventArgs e)

{

  using (NorthwindDataContext context = new NorthwindDataContext())

  {

    Customer customer = new Customer

    {

      CustomerID = textBoxCustomerID.Text,

      CompanyName = textBoxCompanyName.Text,

      ContactName = textBoxCustomerName.Text,

      ContactTitle = textBoxTitle.Text,

      Address = textBoxAddress.Text,

      City = textBoxCity.Text,

      Region = textBoxRegion.Text,

      PostalCode = textBoxPostalCode.Text,

      Country = textBoxCountry.Text,

      Phone = textBoxPhone.Text,

      Fax = textBoxFax.Text

    };

    context.Customers.InsertOnSubmit(customer);

    context.SubmitChanges();

  }

}

Similarly an existing row in database can be updated by first retrieving the data and then submitting it via DataContext.

Conclusion

In this tutorial we have not written a single SQL statement to retrieve or insert data into a database. This is the beauty of LINQ To SQL. Further our retrieval code while in C# looks a lot like a query. We can already appreciate the benefits of such a streamlined and unified approach in dealing with data.

1) Before we generate our data layer we must create a new connection in Server Explorer which points to Northwind database.

2) We will now generate our data layer using LINQ To SQL. To do this you need to add a new item to the data layer project of type LINQ to SQL Classes. We will name it Northwind as shown below.

3) After adding a LINQ to SQL Class we are presented with a designer surface. Here we can simply drag the tables which will become part of our data layer. For this article we will drag all tables on the designer by selecting them all in one go. Our designer should look like this after dragging all tables on it.

4) We should now build our solution to make sure everything is okay. And that’s it. We have successfully generated our data layer. In Solution Explorer we can see that we have two new files namely Northwind.dbml.layout and Northwind.designer.cs. We can also see that references required to compile and run our code have been added by Visual Studio.

The .cs file contains the code for our data layer. Let’s examine the code that has been generated for us. We will look at the Region class.

[Table(Name="dbo.Region")]

public partial class Region : INotifyPropertyChanging, INotifyPropertyChanged

The class itself is decorated with Table attribute and the Name property has been assigned the actual table name we have in our database. Region class also implements INotifyPropertyChanging and INotifyPropertyChanged interfaces. These interfaces are used for databinding. Region class also contains one property per column. Let’s look at the RegionDescription property.

[Column(Storage="_RegionDescription", DbType="NChar(50) NOT NULL",

CanBeNull=false)]

public string RegionDescription

{

  get

  {

    return this._RegionDescription;

  }

  set

  {

    if ((this._RegionDescription != value))

    {

      this.OnRegionDescriptionChanging(value);

      this.SendPropertyChanging();

      this._RegionDescription = value;

      this.SendPropertyChanged("RegionDescription");

      this.OnRegionDescriptionChanged();

    }

  }

}

Columns are decorated with Column attribute and values are passed in for Storage, DbType and CanBeNull which indicates if the column can be null or not.

Traditionally, working with collections of objects meant writing a lot of looping code using for loops or foreach loops to iterate through a list carrying out filtering using if statements, and some action like keeping a running sum of a total property. LINQ frees you from having to write looping code; it allows you to write queries that filter a list or calculate aggregate functions on elements in a collection as a set.

We can write queries against any collection type that implements an interface called IEnumerable (and also a new interface called IQueryable, but more on that later). This is almost any collection type built into the .NET class libraries including simple arrays like string[], or int[], and any List<T> collection we define. Let us look at a few of the simplest examples to understand the basic syntax.

Language Integrated Query (or LINQ, pronounced “link” for short), is a set of Microsoft® .NET technologies that provide built-in language querying functionality similar to SQL, not only for database access, but for accessing data from any source.

There is no one definition for LINQ, but it aims to solve the problem where we currently use different techniques for manipulating and selecting data from databases versus XML versus object collections; LINQ aims to make our life easier by giving us a coordinated, consistent and efficient syntax from our development environment and by using your one chosen programming language, rather than switching between programming languages.

The current LINQ family of technologies and concepts allows an extensible set of operators that work over objects, SQL data and XML data sources. The generalized architecture of the technology also allows the LINQ concepts to be expanded to almost any data domain or technology, so what LINQ is today will expand in the future.