Insert and Delete sql table records - LINQ to SQL

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.