To use LinqToSql in a project:
First add "System.Data.Linq" reference to the project
Map the Entity Classes to Tables. Need a "[Table]XxxTable" class per table with appropriate properties for each column.
Note that the columns names have to match the property names. Although this table class maps to the Db table an instance of it represents a row in the table. For example:
// Table mapping entity for the MyTableRow table row
[Table(Name = "MyTable")]
internal class MyTableRow
{
// Default constructor is Required for Linq to Sql
public MyTableRow()
{
}
/////////////////////////////////
// Database columns defined here
[Column(IsPrimaryKey = true, IsDbGenerated = true)]
public int Id { get; set; }
[Column(CanBeNull = false)]
public string Name { get; set; }
[Column(DbType = "Bit NOT NULL")]
public bool IsMandatory { get; set; }
}
This maps to a row in the SQL table "MyTable".
Need a "DataContext" derived class to access these tables, call it xxxDataContext. Mark it with the "[Database]" attribute. For example:
// Linq to SQL data context for accessing the DB
[Database]
internal class MyDataContext : DataContext
{
// Constructor
public MyDataContext(string connectionString)
: base(connectionString)
{
}
}
Found that making the correct key definitions (including foreign keys) on the SQL tables was critical to getting the Linq to Sql working. With those definitions we can start to query the DB using Linq.
Querying SQL with LINQ
LINQ to SQL: .NET Language-Integrated Query for Relational Data
private void CreatePattern(
MyDataContext dbAccess,
MyTableRow[] toCreate)
{
var table = dbAccess.GetTable<MyTableRow>();
table.InsertAllOnSubmit(toCreate);
dbAccess.SubmitChanges();
}
private MyTableRow[] ReadPattern()
{
var allRows = new MyTableRow[0];
using (var dbAccess = new MyDataContext(GetDatabaseConnectionString()))
{
var table = dbAccess.GetTable<MyTableRow>();
allRows = table.ToArray();
}
return allRows.ToArray();
}
private void UpdatePattern(
MyAdminContext dbAccess,
MyTableRow[] changed)
{
var table = dbAccess.GetTable<MyTableRow>();
foreach (var target in changed)
{
// Find row to update in the table
var id = target.Id;
var row = table.FirstOrDefault(rowx => rowx.Id == id);
if (row != null) // IF it was found
{
// Copy the changes from target into the row
UpdateRow(row, target);
}
}
dbAccess.SubmitChanges();
}
private void DeletePattern(
MyDataContext dbAccess,
MyTableRow[] deleted)
{
var table = dbAccess.GetTable<MyTableRow>();
foreach (var target in deleted)
{
// Find row to delete in the table
var id = target.Id;
var row = table.FirstOrDefault(ipdx => ipdx.Id == id);
if (row != null) // Was it found?
{
table.DeleteOnSubmit(row);
}
}
dbAccess.SubmitChanges();
}
There are 2 ways to create transactions; using the TransactionScope class and using the standard DbTransaction class.
Using new TransactionScope() Considered Harmful
All About TransactionScope
Using Transaction Scope (need to add the System.Transactions assembly in the references)
using System.Transactions;
...
using (var scope = TransactionScopeFactory.CreateTransactionScope()) // Asscociate all the changes with 1 transaction
{
// Use 1 data context for all operations, in this case MS DTC will not be used
// See http://weblog.west-wind.com/posts/2009/Jul/14/LINQ-to-SQL-and-Transactions
// paragraph 'TransactionScope DTC Requirements'
using (var dbAccess = new MyDataContext (GetDatabaseConnectionString()))
{
MakeDbChangesUsingLinqToSql(dbAccess);
scope.Complete();
log.WriteInfo("Transaction completed, the database changes are committed.");
}
}
Using a DbTransaction
using (var dbAccess = new MyDataContext(GetDatabaseConnectionString()))
{
dbAccess.Connection.Open();
// To absolutely guarantee that the MS DTC will not be used (which can occur when using TransactionScope)
// we will use a standard DB transaction here
dbAccess.Transaction = dbAccess.Connection.BeginTransaction();
try
{
MakeDbChangesUsingLinqToSql(dbAccess);
dbAccess.Transaction.Commit(); // No exceptions so commit the changes
log.WriteInfo("Transaction completed, the database changes are committed.");
}
catch (Exception) // Rollback if any exception is encountered
{
dbAccess.Transaction.Rollback();
throw;
}
}
