LINQ to SQL Integrated Code Review

Code Download

Download Description:linq to sql integrated download
.NET Framework:3.5
.NET Language:C#
Date Published:2009-07-01
Download Size:25 KB

Define Person, Role and Salary Classes

Define the Person class which maps to the Person database table.

public class Person { private Int32 m_personID; private Int32 m_roleID; private String m_lastName; private String m_firstName;
private EntityRef<Role> m_role; private EntitySet<Salary> m_salaries;
public Person() { this.m_role = default(EntityRef<Role>); this.m_salaries = new EntitySet<Salary>( new Action<Salary>(this.Attach_Salary), new Action<Salary>(this.Detach_Salary) ); }
public EntitySet<Salary> Salaries { get { return this.m_salaries; } set { this.m_salaries.Assign(value); } } public Role Role { get { return this.m_role.Entity; } set { Role previousValue = this.m_role.Entity;
if ((previousValue != value) || (this.m_role.HasLoadedOrAssignedValue == false)) { if (previousValue != null) { this.m_role.Entity = null; previousValue.People.Remove(this); } this.m_role.Entity = value;
if (value != null) { value.People.Add(this); this.m_roleID = value.RoleID; } else { this.m_roleID = default(int); } } } } public Int32 PersonID { get { return this.m_personID; } set { this.m_personID = value; } } public Int32 RoleID { get { return this.m_roleID; } set { this.m_roleID = value; } } public String LastName { get { return this.m_lastName; } set { this.m_lastName = value; } } public String FirstName { get { return this.m_firstName; } set { this.m_firstName = value; } }
private void Attach_Salary(Salary entity) { entity.Person = this; } private void Detach_Salary(Salary entity) { entity.Person = null; } }

The object relational mapping of the Person object to the Person database table is handled in the XML Mapping file.

The EntitySet class is a special collection used by LINQ to SQL and implements the IEnumerable interface. The EntitySet class represents the many side of a one-to-many relationship. The object on the one side of a one-to-many relationship stores its associated many objects in the EntitySet type. So in the Person class, one Person object can be linked to many Salary objects, and the Salary objects are stored in the EntitySet type.

The EntitySet collection is initialized with handlers for add and remove operations. The Attach_Salary method is invoked when a Salary object is added to the EntitySet collection, and the Detach_Salary method is invoked when a Salary object is removed from the EntitySet collection.

The EntityRef class is a special collection used by LINQ to SQL and implements the IEnumerable interface. The EntityRef class represents the one side of a one-to-many relationship. The object on the many side of a one-to-many relationship stores its associated one object in the EntityRef type. So in the Person class, one Role object can be linked to many Person objects.

The Role property in the Person class defines specific logic to manage the collection of Person objects stored against a specific Role object. When the role property in the Person object is changed, the Person object is first removed from the EntitySet collection defined in associated Role object. Then the Person object is appended to the EntitySet collection in the newly associated Role object.

Define the Role class which maps to the Role database table.

public class Role { private Int32 m_roleID; private String m_roleDescription;
private EntitySet<Person> m_people;
public Role() { this.m_people = new EntitySet<Person>( new Action<Person>(this.Attach_Person), new Action<Person>(this.Detach_Person) ); }
public EntitySet<Person> People { get { return this.m_people; } set { this.m_people.Assign(value); } } public Int32 RoleID { get { return this.m_roleID; } set { this.m_roleID = value; } } public String RoleDescription { get { return this.m_roleDescription; } set { this.m_roleDescription = value; } }
private void Attach_Person(Person entity) { entity.Role = this; } private void Detach_Person(Person entity) { entity.Role = null; } }

The object relational mapping of the Role object to the Role database table is handled in the XML Mapping file.

The EntitySet class is a special collection used by LINQ to SQL and implements the IEnumerable interface. The EntitySet class represents the many side of a one-to-many relationship. The object on the one side of a one-to-many relationship stores its associated many objects in the EntitySet type. So in the Role class, one Role object can be linked to many Person objects, and the Person objects are stored in the EntitySet type.

The EntitySet collection is initialized with handlers for add and remove operations. The Attach_Person method is invoked when a Person object is added to the EntitySet collection, and the Detach_Person method is invoked when a Person object is removed from the EntitySet collection.

Define the Salary class which maps to the Salary database table.

public class Salary { private Int32 m_salaryID; private Int32 m_personID; private Int32 m_year; private Double m_salaryYear;
private EntityRef<Person> m_person;
public Salary() { this.m_person = default(EntityRef<Person>); }
public Int32 SalaryID { get { return this.m_salaryID; } set { this.m_salaryID = value; } } public Person Person { get { return this.m_person.Entity; } set { Person previousValue = this.m_person.Entity;
if ((previousValue != value) || (this.m_person.HasLoadedOrAssignedValue == false)) { if (previousValue != null) { this.m_person.Entity = null; previousValue.Salaries.Remove(this); } this.m_person.Entity = value;
if (value != null) { value.Salaries.Add(this); this.m_personID = value.PersonID; } else { this.m_personID = default(int); } } } } public Int32 PersonID { get { return this.m_personID; } set { this.m_personID = value; } } public Int32 Year { get { return this.m_year; } set { this.m_year = value; } } public Double SalaryYear { get { return this.m_salaryYear; } set { this.m_salaryYear = value; } } }

The object relational mapping of the Salary object to the Salary database table is handled in the XML Mapping file.

The EntityRef class is a special collection used by LINQ to SQL and implements the IEnumerable interface. The EntityRef class represents the one side of a one-to-many relationship. The object on the many side of a one-to-many relationship stores its associated one object in the EntityRef type. So in the Salary class, one Person object can be linked to many Salary objects.

The EntityRef object is initialized using the "default" keyword, which will return null for reference types and zero for numeric value. So the EntityRef type is set to null.

The Person property in the Salary class defines specific logic to manage the collection of Salary objects stored against a specific Person object. When the person property in the Salary object is changed, the Salary object is first removed from the EntitySet collection defined in associated Person object. Then the Salary object is appended to the EntitySet collection in the newly associated Person object.

Mapping the Class and Database Objects Using XML Mapping File

The following XML elements are used to define the key objects in the database model:

Database - map the database attributes.
Table - map specific table attributes.
Type - map a database object to an application specific type.
Column - map column attributes.
Association - map a primary key/foreign key relationship.
ElementType - map stored procedure output to application specific types.

The below XML is stored in the PersonLinqToSqlMapping.xml file and is used to specify mapping between the data model of the database and the application object model. The SqlMetal command-line tool can also be used to generate code and mappings.

<?xml version="1.0" encoding="utf-8" ?> <Database Name="Foo" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">

Name - name of the database being mapped to.
xmlns - used to reference the LinqToSqlMapping.xsd schema to get intellisense as you create the XML mappings.

Define the mapping between the Person database table and the Person class (object).

Name - name of the database object.
Member - name of the application object being mapped to.
Storage - private variable used to hold the value of the database table column.
DbType - type of the database table column.
IsPrimaryKey - whether or not the class member represents a column that is part of the primary key of the databse table.
IsDbGenerated - whether or not the column contains values that the database auto-generates.
ThisKey - name of the property representing the foreign key side of the primary key to foreign key relationship from the Person object perspective.
OtherKey - name of the property representing the primary key side of the primary key to foreign key relationship from the Person object perspective.
IsForeignKey - whether or not the class member represents a foreign key in an association representing a database relationship.

Define the mapping between the Role database table and the Role class (object).

Name - name of the database object.
Member - name of the application object being mapped to.
Storage - private variable used to hold the value of the database table column.
DbType - type of the database table column.
IsPrimaryKey - whether or not the class member represents a column that is part of the primary key of the databse table.
IsDbGenerated - whether or not the column contains values that the database auto-generates.
OtherKey - name of the property representing the foreign key side of the primary key to foreign key relationship from the Role object perspective.

Define the mapping between the Salary database table and the Salary class (object).

Name - name of the database object.
Member - name of the application object being mapped to.
Storage - private variable used to hold the value of the database table column.
DbType - type of the database table column.
IsPrimaryKey - whether or not the class member represents a column that is part of the primary key of the databse table.
IsDbGenerated - whether or not the column contains values that the database auto-generates.
ThisKey - name of the property representing the foreign key side of the primary key to foreign key relationship from the Salary object perspective.
OtherKey - name of the property representing the primary key side of the primary key to foreign key relationship from the Salary object perspective.
IsForeignKey - whether or not the class member represents a foreign key in an association representing a database relationship.

Define Custom DataContext Class

Define the DataContext class, which is used to connect to a database, retrieve objects from it, and submit changes back to it. The purpose of the DataContext is to translate requests for objects into SQL queries to be made against the database, and then to assemble objects out of the results.

public partial class PeopleDataContext : DataContext { public PeopleDataContext(String connString, XmlMappingSource xmlMap) : base(connString, xmlMap) { } }

The PeopleDataContext class inherits from the DataContext class. Create a class constructor that inherits from the base DataContext constructor, accepting a connection string and a XML mapping file as input parameters. The XML mapping file defines the relationship between the database data model and the application object model.

Manipulate the Database using the DataContext Object

Define the database connection string and declare a PeopleDataContext object.

class Program { static String connString = @"Server=SQLEXPRESS;Initial Catalog=Foo;Integrated Security=SSPI"; static PeopleDataContext m_dataContext;
private static Int32 m_IDKNash = Int32.MinValue; private static Int32 m_IDJBloggs = Int32.MinValue; private static Int32 m_IDAdmin = Int32.MinValue; private static Int32 m_IDAnalyst = Int32.MinValue; private static Int32 m_SalIDGrant = Int32.MinValue;

The static Main() method defines various LINQ functions to be applied against the database.

static void Main(string[] args) { using (StreamWriter sw = new StreamWriter("datacontext.log")) { using (m_dataContext = new PeopleDataContext(connString, XmlMappingSource.FromUrl("PersonLinqToSqlMapping.xml"))) { m_dataContext.Log = sw;
AddPersonWithNewRoleAndSalary("Administrator"); AddRole(); AddPersonWithRoleAndSalary("Analyst"); AddSalaryForPerson();
GetPersonData(); UpdatePersonData("Manager");
GetPersonData(); GetRoleData(); GetPersonSalaryData();
DeletePerson(); DeleteRoleAndPeople("Analyst"); DeleteRole("Administrator"); DeleteSalary(); } } Console.Read(); }

Create an instance of the StreamWriter class to create a logging file. The logging file will contain all logging messages generated by LINQ to SQL database updates.

Create an instance of the PeopleDataContext class passing in the database connection string and the location of the XML mapping file as an input parameters.

The Log Property of DataContext class accepts a TextWriter object displaying the SQL generated by the DataContext class. In this case, the Log property is configured to write the SQL output to a logging file.

Create new Person, Role and Salary objects and store these objects in the relevant tables in the database.

static void AddPersonWithNewRoleAndSalary(String roleDesc) { try { Role role = new Role(); role.RoleDescription = "Administrator";
Person person = new Person(); person.FirstName = "Kevin"; person.LastName = "Nash"; person.Role = role; role.People.Add(person);
Salary salary = new Salary(); salary.Year = 2006; salary.SalaryYear = 12000; person.Salaries.Add(salary);
m_dataContext.GetTable<Person>().InsertOnSubmit(person); m_dataContext.SubmitChanges();
m_IDKNash = person.PersonID; m_IDAdmin = role.RoleID;
Console.WriteLine("Kevin Nash (Person) has been added."); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

Insert a new record into the Person, Role and Salary database tables. The InsertOnSubmit() method is invoked on the Person table in the PeopleDataContext class, however, the records are not appended to the database until the SubmitChanges() method is invoked on the PeopleDataContext class. Retrieve and store the identifer values for newly created Person and Role database records.

Create a new Role object and store this object in the Role table in the database.

static void AddRole() { try { Role role = new Role(); role.RoleDescription = "Analyst"; m_dataContext.GetTable<Role>().InsertOnSubmit(role); m_dataContext.SubmitChanges();
m_IDAnalyst = role.RoleID;
Console.WriteLine("Analyst (Role) has been added."); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Insert a new record into the Role database table. The InsertOnSubmit() method is invoked on the Role table in the PeopleDataContext class, however, the record is not appended to the database until the SubmitChanges() method is invoked on the PeopleDataContext class.

Create a new Person and Salary object, associating the Person object to a existing Role object, and store these created objects in the relevant tables in the database.

static void AddPersonWithRoleAndSalary(String roleDesc) { try { Role role = m_dataContext.GetTable<Role>().Single ( r => r.RoleDescription == "Analyst" && r.RoleID == m_IDAnalyst );
Person person = new Person(); person.FirstName = "Joe"; person.LastName = "Bloggs"; person.Role = role; role.People.Add(person);
Salary salary = new Salary(); salary.Year = 2008; salary.SalaryYear = 7000; person.Salaries.Add(salary);
m_dataContext.GetTable<Person>().InsertOnSubmit(person); m_dataContext.SubmitChanges();
m_IDJBloggs = person.PersonID; Console.WriteLine("Joe Bloggs (Person) has been added."); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

Use a language integrated query to retrieve the analyst role record from the Role database table. Store the result in Role object.

Insert a new record into the Person and Salary database tables. The InsertOnSubmit() method is invoked on the Person table in the PeopleDataContext class, however, the records are not appended to the database until the SubmitChanges() method is invoked on the PeopleDataContext class. Retrieve and store the identifer value for the newly created Person database record.

Create a new Salary object, associating the Salary object to a existing Person object, and store the created object in the Salary table in the database.

static void AddSalaryForPerson() { try { Person person = m_dataContext.GetTable<Person>().Single ( p => p.LastName == "Grant" );
Salary salary = new Salary(); salary.Year = 2007; salary.SalaryYear = 13500; person.Salaries.Add(salary);
m_dataContext.GetTable<Salary>().InsertOnSubmit(salary); m_dataContext.SubmitChanges();
m_SalIDGrant = salary.SalaryID; } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

Use a language integrated query to retrieve the person record from the Person database table. Store the result in Person object.

Insert a new record into the Salary database table. The InsertOnSubmit() method is invoked on the Salary table in the PeopleDataContext class, however, the records are not appended to the database until the SubmitChanges() method is invoked on the PeopleDataContext class. Retrieve and store the identifer value for the newly created Salary database record.

Retrieve all the records from the Person database table, together with the corresponding roles in the Role database table.

static void GetPersonData() { try { var query = from p in m_dataContext.GetTable<Person>() from r in m_dataContext.GetTable<Role>() where p.RoleID == r.RoleID select new { p.LastName, p.FirstName, r.RoleDescription };
foreach (var row in query) { Console.WriteLine("Full Name: {0} {1} Role: {2}", row.FirstName, row.LastName, row.RoleDescription); } Console.WriteLine(); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a language integrated query to retrieve an anonymous type containing data from the Person and Role database tables. Store the result in an implicit collection object.

Modify the role associated with a particular person in the database.

static void UpdatePersonData(String roleDesc) { try { Person person = m_dataContext.GetTable<Person>().Single ( p => p.PersonID == m_IDKNash ); Role role = m_dataContext.GetTable<Role>().Single ( r => r.RoleDescription == roleDesc );
person.Role = role; m_dataContext.SubmitChanges();
Console.WriteLine(); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a language integrated query to retrieve the person record from the Person database table, and the role record from the Role database table. Store the results in a Person and Role object respectively.

Assign the role object to the Person role property and invoke the SubmitChanges() method in the PeopleDataContext class to save the changes.

Retrieve all the records from the Role database table, and display the associated people assigned to the role.

static void GetRoleData() { try { var query = from r in m_dataContext.GetTable<Role>() select r;
foreach (Role role in query) { Console.WriteLine("Role ID: {0} RoleName: {1}", role.RoleID, role.RoleDescription); Console.WriteLine(new String('=', 30));
if(role.People.Count > 0) { foreach (Person person in role.People) { Console.WriteLine("{0},{1}", person.LastName, person.FirstName); } } else { Console.WriteLine("No people found."); } Console.WriteLine(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a language integrated query to retrieve an anonymous type containing data from the Role database table. Store the result in an implicit collection object.

Iterate through the Role collection and for each Role object iterate through the corresponding Person EntitySet collection.

Retrieve all the records from the Person database table, and display the role and salaries associated with the person.

static void GetPersonSalaryData() { try { var query = from p in m_dataContext.GetTable<Person>() select p;
foreach (Person person in query) { Console.WriteLine("Full Name: {0} {1} Role: {2}", person.FirstName, person.LastName, person.Role.RoleDescription); Console.WriteLine(new String('=', 45));
if (person.Salaries.Count > 0) { foreach (Salary salary in person.Salaries) { Console.WriteLine("{0},{1}", salary.Year, salary.SalaryYear); } } else { Console.WriteLine("No salaries found."); } Console.WriteLine(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a language integrated query to retrieve an anonymous type containing data from the Person database table. Store the result in an implicit collection object.

Iterate through the Person collection and for each Person object iterate through the corresponding Salary EntitySet collection.

Delete a single person record and all associated salary records from the Person and Salary tables in the database.

static void DeletePerson() { try { Person person = m_dataContext.GetTable<Person>().Single ( p => p.PersonID == m_IDKNash );
if (person.Salaries.Count > 0) { foreach (Salary salary in person.Salaries) { m_dataContext.GetTable<Salary>().DeleteOnSubmit(salary); } } m_dataContext.GetTable<Person>().DeleteOnSubmit(person); m_dataContext.SubmitChanges();
Console.WriteLine("Kevin Nash (IDPerson={0}) has been deleted.", m_IDKNash.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

LINQ to SQL does not support cascading delete functionality, so this functionality must be implemented manually in code.

Use a language integrated query to retrieve an anonymous type containing data from the Person database table. Store the result in an implicit collection object.

Iterate through the Person collection and for each Person object iterate through the corresponding Salary EntitySet collection.

The DeleteOnSubmit() method is invoked on the corresponding tables in the PeopleDataContext class, however, the records are not deleted from the database until the SubmitChanges() method is invoked on the PeopleDataContext class.

Delete a single role record and all associated people and salary records from the Person and Salary tables in the database.

static void DeleteRoleAndPeople(String roleDesc) { try { Role role = m_dataContext.GetTable<Role>().Single ( r => r.RoleDescription == roleDesc && r.RoleID == m_IDAnalyst );
if (role.People.Count > 0) { foreach (Person person in role.People) { foreach (Salary salary in person.Salaries) { m_dataContext.GetTable<Salary>().DeleteOnSubmit(salary); } m_dataContext.GetTable<Person>().DeleteOnSubmit(person); } } m_dataContext.GetTable<Role>().DeleteOnSubmit(role); m_dataContext.SubmitChanges();
Console.WriteLine("{0} (IDRole={1}) has been deleted.", roleDesc, m_IDAnalyst.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

LINQ to SQL does not support cascading delete functionality, so this functionality must be implemented manually in code.

Use a language integrated query to retrieve an anonymous type containing data from the Role database table. Store the result in an implicit collection object.

Iterate through the Role collection and for each Role object iterate through the corresponding Person and Salary EntitySet collections.

Delete a single role record and all associated people and salary records from the Person and Salary tables in the database.

static void DeleteRole(String roleDesc) { try { Role role = m_dataContext.GetTable<Role>().Single ( r => r.RoleDescription == roleDesc && r.RoleID == m_IDAdmin );
if (role.People.Count > 0) { foreach (Person person in role.People) { foreach (Salary salary in person.Salaries) { m_dataContext.GetTable<Salary>().DeleteOnSubmit(salary); } m_dataContext.GetTable<Person>().DeleteOnSubmit(person); } } m_dataContext.GetTable<Role>().DeleteOnSubmit(role); m_dataContext.SubmitChanges();
Console.WriteLine("{0} (IDRole={1}) has been deleted.", roleDesc, m_IDAdmin.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

Use a language integrated query to retrieve an anonymous type containing data from the Role database table. Store the result in an implicit collection object.

Iterate through the Role collection and for each Role object iterate through the corresponding Person and Salary EntitySet collections.

Delete a single salary record from the Salary table in the database.

static void DeleteSalary() { try { Salary salary = m_dataContext.GetTable<Salary>().Single ( s => s.SalaryID == m_SalIDGrant ); m_dataContext.GetTable<Salary>().DeleteOnSubmit(salary); m_dataContext.SubmitChanges();
Console.WriteLine("Salary (Salary ID={0}) has been deleted.", m_SalIDGrant.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

Use a language integrated query to retrieve the salary record from the Salary database table. Store the result in a Salary object.

The DeleteOnSubmit() method is invoked on the Salary table in the PeopleDataContext class, however, the record is not deleted from the database until the SubmitChanges() method is invoked on the PeopleDataContext class.