In the LINQ to SQL Overview, we saw the internals of LINQ to SQL with a simple example showing how to retrieve data from SQL. In this article we will see how to insert and delete records in a SQL table using LINQ to SQL.

Lets take the same example as in the Overview article and insert to and delete records from the Countries table.

Insert Records

To insert records to the table, the LINQ provides two methods -
InsertOnSubmit() - Used insert single record to the database.
InsertAllOnSubmit() - Used to insert multiple records to the database.

These inserts are actually commited to the database when SubmitChanges() method of the DataContext is called. Lets insert Australia, Germany & Russia to the Countries table.

Note: The insert, delete and update operations won't work if the table does not have a primary key set.

public static void InsertCountries()
{
//Create the Countries DataContext object.
CountriesDataContext _countriesDataContext = new CountriesDataContext();

//Create the List of type Country.
//This list would contain the new countries that we will insert in the database.
List lstCountry = new List();

//New country objects
Country _1stCountry = new Country();
_1stCountry.CountryId = 7;
_1stCountry.CountryName = "Russia";
lstCountry.Add(_1stCountry);

Country _2ndCountry = new Country();
_2ndCountry.CountryId = 8;
_2ndCountry.CountryName = "Germany";
lstCountry.Add(_2ndCountry);

Country _3rdCountry = new Country();
_3rdCountry.CountryId = 9;
_3rdCountry.CountryName = "Australia";
lstCountry.Add(_3rdCountry);

//Use InsertAllOnSubmit method to insert the new country list.
_countriesDataContext.Countries.InsertAllOnSubmit(lstCountry);

//Commit to the database.
_countriesDataContext.SubmitChanges();
}

Delete Records

To delete records, we use DeleteOnSubmit() or DeleteAllOnSubmit() methods followed by SubmitChanges() method of the DataContext.

Lets delete few countries from the Country table. Lets say, we want to delete all the countries with CountryId > 5.

public static void DeleteCountries()
{
CountriesDataContext _countriesDataContext = new CountriesDataContext();

var cntries = from c in _countriesDataContext.Countries
where c.CountryId > 5
select c;

_countriesDataContext.Countries.DeleteAllOnSubmit(cntries);
_countriesDataContext.SubmitChanges();
}

There are many insert, delete or update scenarios that people can encounter. MSDN provides good reference - LINQ to SQL Samples covering most of the scenarios.

Introduction

Now what is this new thing? As we all know that every day we have something new coming up. It is the new way to access data from the SQL. LINQ to SQL got introduced in VS 2008, .Net Framework 3.5. Hence update your visual studio to this version before you can work on LINQ to SQL.

First thing before we start, Please forget everything about ADO.Net concepts so that you don't try to relate any of its concepts with LINQ to SQL internals.

Overview

LINQ to SQL uses an Entity-based Framework. It uses a DataContext that contains one or more entity classes. These classes contain public properties that map to each column of the database table that they map to. When we execute LINQ to SQL queries and retrieve data from database, instances of these entity objects get created for each row in that SQL table. You do not have to do all this work, the whole process is internally taken care by the DataContext object.

If you are new to LINQ to SQL, you may not understand some of the above mentioned
terminologies. In short, DataContext is required which helps retrieve data from the
database and also submits our changes back to the data to the database.

Lets see a step-by-step example and relate to what we have read.

Example

a. Lets say we have a Countries table in SQL as below. We will use LINQ to SQL query to retrieve the countryName values from Countries table.



Here we will work on a simple console application mainly to display the data that we retrieve using LINQ to SQL.

b. Add new item to the project called 'LINQ to SQL Classes'. This creates a .dbml file. Select the .dbml file to load the Object Relational Designer (ORD). Drag and drop the Countries table from Server Explorer to the ORD.



As we dropped the Countries table, it creates the Countries DataContext underneath (can be viewed in Countries.designer.cs file). Using the instance of this DataContext object we can read and update data to and from the database.

As we see above, the Country Entity class gets created and it has public properties that map directly to the columns of Countries table. In our code we would use this entity class that stores the data retrieved from SQL table using the CountriesDataContext.

c. Sourcecode:

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

namespace linqtosql
{
class Program
{
static void Main(string[] args)
{
GetCountries();
}

Public static void GetCountries()
{
//Create instance of the Countries DataContext class.
CountriesDataContext _countriesDataContext = new CountriesDataContext();

try
{
//Prepare the query to retrieve the data from Countries table
var resCountries = from c in _countriesDataContext.Countries
select c;

//Execute the SQL Query and print the CountryName values

//Also here the CountryDataContext creates
//instances of Country class with data values for each row of data
//in the Countries table.
foreach (var resCountry in resCountries)
{
Console.WriteLine(resCountry.CountryName);
}
Console.Read();
}
catch (Exception ex)
{
Console.Write(ex.Message);
Console.Read();
}
}
}
}

Optional

This is one way of creating DataContext and the Entity Classes. You can also create them on your own, without any tool. Refer http://msdn.microsoft.com/en-us/library/bb386940.aspx for more information about creating manually DataContext & Entity Classes, map them to the database table and query to get the data in the similar way as above.

In the next article we will see some of the common SQL-like operations using LINQ to SQL like how to insert, update, delete, and read using filters, sorting, grouping.

References: http://msdn.microsoft.com/en-us/library/bb386976.aspx

Linq to XML is the modern approach to programming with XML using any .NET Languages. It provides an in-memory XML programming interface that allows querying XML data using SQL-like queries thereby making developers life easy. It also reduces the code size as compared to the old approach.

In the old approach, one had to start with XMLDocument. Using the XMLDocument, create root XMLElement followed by using helper methods to append or create child elements which involved lot of code writing. Also to read/query XMLDocuments, one had to write XPATH queries to get the required data, the validation of which did not happened at compile time.

Using Linq to XML, life has become very easy with the introduction of simple-to-use XML tree creation objects. Also to get data from XDocument, one just have to use simple SQL-like queries having all the required methods to filter & get the required match data. The code size has reduced drastically, more readable and easy to understand.

In .Net 3.5 a new namespace called System.Xml.Linq contains all the classes for creating XML documents in any required format. Some of its members are as listed:

XAttribute – Represents an XML attribute.
XComment – Represents an XML comment.
XDeclaration - Represents an XML declaration.
XDocument - Represents an XML document.
XElement - Represents an XML element.
XName - Represents the name of the XElement or XAttribute
XNamespace - Represents an XML namespace.
XNode - Represents any XML node within an XML document(e.g.element,comment)

Lets see how to create an entire XML Document tree using a single statement.

XDocument doc =
new XDocument(
new XElement("Customer",
new XElement("FirstName", "Atul"),
new XElement("LastName", "Ashpalia")));

doc.save(@"d:\sample.xml");

File output:
<customer>
<FirstName>Atul</FirstName>
<LastName>Ashpalia</LastName>
</customer>

Now lets load this XML document in XDocument object from sample.xml file. Then retrieve the value of the FirstName and print it.
Note: We can also load the XML Document from a stream.

Code:
// Load from file.
XDocument customer = XDocument.Load(@"d:\sample.xml");

// Query the data and print the FirstName
var q = from c in customer
where c.Element("FirstName")
select c;

foreach (string name in q)
Console.WriteLine("First Name = {0}", name);

As seen above, the code to create XML Document and to query it looks much more simplified and easy compared to what the old approach had to offer us. Enjoy coding.