Printed from www.rmfusion.com A Developer website designed for Developers

LINQ to SQL Custom Code Review

Code Download

Define Person, Role and Salary Classes

Define the Person class which will map 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 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 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 EntitySet and EntityRef objects are initialized using the "default" keyword, which will return null for reference types and zero for numeric value. So these types are set to null.

When the Role property is updated, the EntityRef type storing the associated role is set to null and the Person object is removed from the corresponding EntitySet collection in the Role object. Then the EntityRef type storing the associated role is updated with the value of the role and the Person object is appended to the corresponding EntitySet collection in the associated Role object.

Define the Role class which will map 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 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.

The Attach_Person method sets the Role object of the associated Person object. The Detach_Person method clears the Role object of the associated Person object.

Define the Salary class which will map to the Person 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 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.

Mapping the Class and Database Objects Using XML Mapping File

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.

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

Define the database that the application objects need to map to using the Database XML element.

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

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

<Table Name="Person" Member="PersonLINQStoredProc.Person"> <Type Name="PersonLINQStoredProc.Person"> <Column Name="ID" Member="PersonID" Storage="m_personID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" /> <Column Name="IDRole" Member="RoleID" Storage="m_roleID" DbType="int NOT NULL" /> <Column Name="LastName" Member="LastName" Storage="m_lastName" DbType="nvarchar NOT NULL" /> <Column Name="FirstName" Member="FirstName" Storage="m_firstName" DbType="nvarchar NOT NULL" /> <Association Name="FK_Person_Role" Member="Role" Storage="m_role" ThisKey="RoleID" OtherKey="RoleID" IsForeignKey="true"/> <Association Name="FK_Salary_Person" Member="Salaries" Storage="m_salaries" OtherKey="PersonID" /> </Type> </Table>

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

<Table Name="Role" Member="PersonLINQStoredProc.Role"> <Type Name="PersonLINQStoredProc.Role"> <Column Name="ID" Member="RoleID" Storage="m_roleID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" /> <Column Name="RoleDescription" Member="RoleDescription" Storage="m_roleDescription" DbType="nvarchar(50) NOT NULL" /> <Association Name="FK_Person_Role" Member="People" Storage="m_people" OtherKey="RoleID" /> </Type> </Table>

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

<Table Name="Salary" Member="PersonLINQStoredProc.Salary"> <Type Name="PersonLINQStoredProc.Salary"> <Column Name="ID" Member="SalaryID" Storage="m_salaryID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" /> <Column Name="IDPerson" Member="PersonID" Storage="m_personID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="false" /> <Column Name="Year" Member="Year" Storage="m_year" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="false" /> <Column Name="SalaryYear" Member="SalaryYear" Storage="m_salaryYear" DbType="numeric(18,0) NOT NULL" /> <Association Name="FK_Salary_Person" Member="Person" Storage="m_person" ThisKey="PersonID" OtherKey="PersonID" IsForeignKey="true"/> </Type> </Table>

Define the mapping between the database insert stored procedures and the methods defined in the DataContext class (object).

<Function Name="dbo.linqInsertRole" Method="InsertRole"> <Parameter Name="RoleDescription" Parameter="RoleDescription" DbType="nvarchar(50)" /> </Function> <Function Name="dbo.linqGetRoleByRoleId" Method="GetRoleByRoleID"> <Parameter Name="IDRole" Parameter="IDRole" DbType="int" /> <ElementType Name="PersonLINQStoredProc.Role" /> </Function> <Function Name="dbo.linqGetPersonByLastName" Method="GetPersonByLastName"> <Parameter Name="LastName" Parameter="LastName" DbType="nvarchar(50)" /> <ElementType Name="PersonLINQStoredProc.Person" /> </Function> <Function Name="dbo.linqInsertPerson" Method="InsertPerson"> <Parameter Name="IDRole" Parameter="IDRole" DbType="int" /> <Parameter Name="LastName" Parameter="LastName" DbType="nvarchar(50)" /> <Parameter Name="FirstName" Parameter="FirstName" DbType="nvarchar(50)" /> </Function> <Function Name="dbo.linqInsertSalary" Method="InsertSalary"> <Parameter Name="IDPerson" Parameter="IDPerson" DbType="int" /> <Parameter Name="Year" Parameter="Year" DbType="int" /> <Parameter Name="SalaryYear" Parameter="SalaryYear" DbType="numeric(18,0)" /> </Function>

Define the mapping between the database query stored procedures and the methods defined in the DataContext class (object).

<Function Name="dbo.linqGetAllPeople" Method="AllPeopleData"> <ElementType Name="PersonLINQStoredProc.Person" /> <ElementType Name="PersonLINQStoredProc.Role" /> </Function> <Function Name="dbo.linqGetRoleByDescription" Method="GetRoleByDescription"> <Parameter Name="RoleDescription" Parameter="RoleDescription" DbType="nvarchar(50)" /> <ElementType Name="PersonLINQStoredProc.Role" /> </Function> <Function Name="dbo.linqGetPersonByID" Method="GetPersonByPersonID"> <Parameter Name="IDPerson" Parameter="IDPerson" DbType="int" /> <ElementType Name="PersonLINQStoredProc.Person" /> </Function> <Function Name="dbo.linqGetAllRoles" Method="AllRoleData"> <ElementType Name="PersonLINQStoredProc.Role" /> <ElementType Name="PersonLINQStoredProc.Person" /> </Function> <Function Name="dbo.linqAllPersonRoleSalary" Method="AllPeopleRoleSalaries"> <ElementType Name="PersonLINQStoredProc.Person" /> <ElementType Name="PersonLINQStoredProc.Role" /> <ElementType Name="PersonLINQStoredProc.Salary" /> </Function> <Function Name="dbo.linqGetSalariesByPersonId" Method="GetSalariesByPersonID"> <Parameter Name="IDPerson" Parameter="IDPerson" DbType="int" /> <ElementType Name="PersonLINQStoredProc.Salary" /> </Function> <Function Name="dbo.linqGetPeopleByRoleId" Method="GetPeopleByRoleID"> <Parameter Name="IDRole" Parameter="IDRole" DbType="int" /> <ElementType Name="PersonLINQStoredProc.Person" /> </Function>

Define the mapping between the database update and delete stored procedures and the methods defined in the DataContext class (object).

<Function Name="dbo.linqUpdatePerson" Method="UpdatePerson"> <Parameter Name="IDRole" Parameter="IDRole" DbType="int" /> <Parameter Name="LastName" Parameter="LastName" DbType="nvarchar(50)" /> <Parameter Name="FirstName" Parameter="FirstName" DbType="nvarchar(50)" /> <Parameter Name="ID" Parameter="IDPerson" DbType="int" /> </Function> <Function Name="dbo.linqDeleteSalary" Method="DeleteSalaryBySalaryID"> <Parameter Name="ID" Parameter="SalaryID" DbType="int" /> </Function> <Function Name="dbo.linqDeletePerson" Method="DeletePersonByPersonID"> <Parameter Name="IDPerson" Parameter="IDPerson" DbType="int" /> </Function> <Function Name="dbo.linqDeleteSalariesByPersonID" Method="DeleteSalariesByPersonID"> <Parameter Name="IDPerson" Parameter="IDPerson" DbType="int" /> </Function> <Function Name="dbo.linqDeleteRole" Method="DeleteRoleByRoleIDAndDescription"> <Parameter Name="IDRole" Parameter="IDRole" DbType="int" /> <Parameter Name="RoleDescription" Parameter="RoleDescription" DbType="nvarchar(50)" /> </Function>

Define Custom DataContext Class

Define the DataContext class, which is used to connect to a database, and invoke stored procedures to retrieve objects from it, and submit changes back to it.

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.

The ExecuteMethodCall() method of the DataContext class is invoked to call the mapped stored procedure on the database. The ExecuteMethodCall() method will read the Function element defined in the XML mapping file to determine which stored procedure should be called, and return the result as a IExecuteResult type.

The ExecuteMethodCall() method accepts the following input parameters:

Define the AllPeopleData() method, which calls the linqGetAllPeople stored procedure on the database to retrieve all people and associated roles.

public IMultipleResults AllPeopleData() { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))); return (result.ReturnValue as IMultipleResults); } { throw; } }

The linqGetAllPeople stored procedure returns two resultsets. The first containing all people instances, and the second containing all role instances. The linqGetAllPeople stored procedure has no input parameters and returns a IMultipleResults type.

Define the AllPeopleRoleSalaries() method, which calls the linqAllPersonRoleSalary stored procedure on the database to retrieve all people, associated roles and salaries.

public IMultipleResults AllPeopleRoleSalaries() { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))); return (result.ReturnValue as IMultipleResults); } catch { throw; } }

The linqAllPersonRoleSalary stored procedure returns three resultsets. The first containing all people instances, the second containing all role instances, and the third containing all salary instances. The linqAllPersonRoleSalary stored procedure has no input parameters and returns a IMultipleResults type.

Define the AllRoleData() method, which calls the linqGetAllRoles stored procedure on the database to retrieve all roles and associated people.

public IMultipleResults AllRoleData() { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))); return (result.ReturnValue as IMultipleResults); } catch { throw; } }

The linqGetAllRoles stored procedure returns two resultsets. The first containing all role instances and the second containing all person instances. The linqGetAllRoles stored procedure has no input parameters and returns a IMultipleResults type.

Define the DeletePersonByPersonID() method, which calls the linqDeletePerson stored procedure on the database to delete a single person from the Person table on the database.

public Int32 DeletePersonByPersonID(Int32 IDPerson) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IDPerson); return (Convert.ToInt32(result.ReturnValue)); } catch { throw; } }

The linqDeletePerson stored procedure returns an integer value indicating the number of person records deleted. The linqDeletePerson stored procedure expects a person identifer as an input parameter.

Define the DeleteRoleByRoleIDAndDescription() method, which calls the linqDeleteRole stored procedure on the database to delete a single role from the Role table on the database.

public Int32 DeleteRoleByRoleIDAndDescription(Int32 IDRole, String RoleDescription) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IDRole, RoleDescription); return (Convert.ToInt32(result.ReturnValue)); } catch { throw; } }

The linqDeleteRole stored procedure returns an integer value indicating the number of role records deleted. The linqDeleteRole stored procedure expects a role identifer and role description as an input parameters.

Define the DeleteSalariesByPersonID() method, which calls the linqDeleteSalariesByPersonID stored procedure on the database to delete all associated salaries for a specific person from the Salary table on the database.

public Int32 DeleteSalariesByPersonID(Int32 IDPerson) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IDPerson); return (Convert.ToInt32(result.ReturnValue)); } catch { throw; } }

The linqDeleteSalariesByPersonID stored procedure returns an integer value indicating the number of salary records deleted. The linqDeleteSalariesByPersonID stored procedure expects a person identifer as an input parameter.

Define the DeleteSalaryBySalaryID() method, which calls the linqDeleteSalary stored procedure on the database to delete a single salary record from the Salary table on the database.

public Int32 DeleteSalaryBySalaryID(Int32 SalaryID) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), SalaryID); return (Convert.ToInt32(result.ReturnValue)); } catch { throw; } }

The linqDeleteSalary stored procedure returns an integer value indicating the number of salary records deleted. The linqDeleteSalary stored procedure expects a salary identifer as an input parameter.

Define the GetPersonByLastName() method, which calls the linqGetPersonByLastName stored procedure on the database to retrieve a single person record from the Person table on the database.

public ISingleResult<Person> GetPersonByLastName(String LastName) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), LastName); return (result.ReturnValue as ISingleResult<Person>); } catch { throw; } }

The linqGetPersonByLastName stored procedure returns a single resultset containing a single person instance. The linqGetPersonByLastName stored procedure expects a lastname value as an input parameter.

Define the GetPersonByPersonID() method, which calls the linqGetPersonByID stored procedure on the database to retrieve a single person from the Person table on the database.

public ISingleResult<Person> GetPersonByPersonID(Int32 IDPerson) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IDPerson); return (result.ReturnValue as ISingleResult<Person>); } catch { throw; } }

The linqGetPersonByID stored procedure returns a single resultset containing a single person instance. The linqGetPersonByID stored procedure expects a person identifer as an input parameter.

Define the GetPeopleByRoleID() method, which calls the linqGetPeopleByRoleId stored procedure on the database to retrieve one or more people from the Person table on the database.

public ISingleResult<Person> GetPeopleByRoleID(Int32 IDRole) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IDRole); return (result.ReturnValue as ISingleResult<Person>); } catch { throw; } }

The linqGetPeopleByRoleId stored procedure returns a single resultset containing one or more person instances. The linqGetPeopleByRoleId stored procedure expects a role identifer as an input parameter.

Define the GetRoleByDescription() method, which calls the linqGetRoleByDescription stored procedure on the database to retrieve a single role from the Role table on the database.

public ISingleResult<Role> GetRoleByDescription(String RoleDescription) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), RoleDescription); return (result.ReturnValue as ISingleResult<Role>); } catch { throw; } }

The linqGetRoleByDescription stored procedure returns a single resultset containing a single role instance. The linqGetRoleByDescription stored procedure expects a role description value as an input parameter.

Define the GetRoleByRoleID() method, which calls the linqGetRoleByRoleId stored procedure on the database to retrieve a single role from the Role table on the database.

public ISingleResult<Role> GetRoleByRoleID(Int32 IDRole) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IDRole); return (result.ReturnValue as ISingleResult<Role>); } catch { throw; } }

The linqGetRoleByRoleId stored procedure returns a single resultset containing a single role instance. The linqGetRoleByRoleId stored procedure expects a role identifier as an input parameter.

Define the GetSalariesByPersonID() method, which calls the linqGetSalariesByPersonId stored procedure on the database to retrieve one or more salaries from the Salary table on the database.

public ISingleResult<Salary> GetSalariesByPersonID(Int32 IDPerson) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IDPerson); return (result.ReturnValue as ISingleResult<Salary>); } catch { throw; } }

The linqGetSalariesByPersonId stored procedure returns a single resultset containing one or more salary instances. The linqGetSalariesByPersonId stored procedure expects a person identifier as an input parameter.

Define the InsertPerson() method, which calls the linqInsertPerson stored procedure on the database to create a new person record in the Person table on the database.

public Int32 InsertPerson(Int32 IDRole, String LastName, String FirstName) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IDRole,LastName,FirstName); return (Convert.ToInt32(result.ReturnValue)); } catch { throw; } }

The linqInsertPerson stored procedure returns an integer value indicating the number of salary records inserted. The linqInsertPerson stored procedure expects a role identifier, lastname value and firstname value as an input parameters.

Define the InsertRole() method, which calls the linqInsertRole stored procedure on the database to create a new role record in the Role table on the database.

public Int32 InsertRole(String RoleDescription) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), RoleDescription); return (Convert.ToInt32(result.ReturnValue)); } catch { throw; } }

The linqInsertRole stored procedure returns an integer value indicating the number of role records inserted. The linqInsertRole stored procedure expects a role description value as an input parameter.

Define the InsertSalary() method, which calls the linqInsertSalary stored procedure on the database to create a new salary record in the Salary table on the database.

public Int32 InsertSalary(Int32 IDPerson, Int32 Year, Double SalaryYear) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IDPerson, Year, SalaryYear); return (Convert.ToInt32(result.ReturnValue)); } catch { throw; } }

The linqInsertSalary stored procedure returns an integer value indicating the number of salary records inserted. The linqInsertSalary stored procedure expects a person identifer, year value and yearly salary value as an input parameters.

Define the UpdatePerson() method, which calls the linqUpdatePerson stored procedure on the database to update an existing person record in the Person table on the database.

public Int32 UpdatePerson(Int32 IDRole, String LastName, String FirstName, Int32 IDPerson) { try { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IDRole, LastName, FirstName, IDPerson); return (Convert.ToInt32(result.ReturnValue)); } catch { throw; } }

The linqUpdatePerson stored procedure returns an integer value indicating the number of person records updated. The linqUpdatePerson stored procedure expects a role identifer, lastname value, firstname value and person identifer as an input parameters.

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; m_dataContext.ObjectTrackingEnabled = false;
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.

The ObjectTrackingEnabled property of the DataContext is set to false to ensure that no stored procedure results are cached.

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

static void AddPersonWithNewRoleAndSalary(String roleDesc) { Int32 salaryID = Int32.MinValue;
try { m_IDAdmin = m_dataContext.InsertRole("Administrator");
m_IDKNash = m_dataContext.InsertPerson(m_IDAdmin, "Nash", "Kevin");
salaryID = m_dataContext.InsertSalary(m_IDKNash, 2006, 12000);
Console.WriteLine("Kevin Nash (Person) has been added."); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Insert a new record into the Person, Role and Salary database tables by invoking the corresponding methods defined in 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 { m_IDAnalyst = m_dataContext.InsertRole("Analyst");
Console.WriteLine("Analyst (Role) has been added."); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Insert a new record into the Role database table by invoking the corresponding method defined in the PeopleDataContext class. Retrieve and store the identifer values for newly created Role database record.

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) { Int32 salaryID = Int32.MinValue;
try { Role role = m_dataContext.GetRoleByRoleID(m_IDAnalyst).Single();
m_IDJBloggs = m_dataContext.InsertPerson(role.RoleID, "Bloggs", "Joe");
salaryID = m_dataContext.InsertSalary(m_IDJBloggs, 2008, 7000);
Console.WriteLine("Joe Bloggs (Person) has been added."); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

Insert a new record into the Person and Salary database tables by invoking the corresponding methods defined in 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.GetPersonByLastName("Grant").Single();
m_SalIDGrant = m_dataContext.InsertSalary(person.PersonID, 2007, 13500); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Insert a new record into the Salary database table by invoking the corresponding method defined in 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 { IMultipleResults results = m_dataContext.AllPeopleData();
List<Person> people = results.GetResult<Person>().ToList(); List<Role> roles = results.GetResult<Role>().ToList();
foreach (Person person in people) { Console.WriteLine("Full Name: {0} {1} Role: {2}", person.FirstName, person.LastName, roles .Where(r => r.RoleID == person.RoleID) .Single() .RoleDescription ); } Console.WriteLine(); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Invoke the corresponding method defined in the DataContext class to return two resultsets. The first resultset has the person instances, and the second resultset has the role instances.

Iterate through the person resultset and for each person use a language integrated query to retrieve the corresponding role object from the role resultset.

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

static void UpdatePersonData(String roleDesc) { try { Role role = m_dataContext.GetRoleByDescription(roleDesc).Single();
Person person = m_dataContext.GetPersonByPersonID(m_IDKNash).Single();
Int32 recordsAffected = Int32.MinValue;
recordsAffected = m_dataContext.UpdatePerson(role.RoleID, person.LastName, person.FirstName, person.PersonID); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Invoke the corresponding methods defined in the DataContext class 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() { Boolean found = false;
try { IMultipleResults results = m_dataContext.AllRoleData();
List<Role> roles = results.GetResult<Role>().ToList<Role>(); List<Person> people = results.GetResult<Person>().ToList<Person>();
foreach (Role role in roles) { Console.WriteLine("Role ID: {0} RoleName: {1}", role.RoleID, role.RoleDescription); Console.WriteLine(new String('=', 30));
found = false; foreach (Person person in people .Where(p => p.RoleID == role.RoleID) .ToList()) { if (!found) found = true; Console.WriteLine("{0},{1}", person.LastName, person.FirstName); } if (!found) { Console.WriteLine("No people found."); } Console.WriteLine(); } Console.WriteLine(); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Invoke the corresponding method defined in the DataContext class to return two resultsets. The first resultset has the role instances, and the second resultset has the person instances.

Iterate through the role resultset and for each role use a language integrated query to retrieve the corresponding person objects associated with that role from the person resultset.

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

static void GetPersonSalaryData() { Boolean found = false;
try { IMultipleResults results = m_dataContext.AllPeopleRoleSalaries();
List<Person> people = results.GetResult<Person>().ToList<Person>(); List<Role> roles = results.GetResult<Role>().ToList<Role>(); List<Salary> salaries = results.GetResult<Salary>().ToList<Salary>();
foreach (Person person in people) { Console.WriteLine("Full Name: {0} {1} Role: {2}", person.FirstName, person.LastName, roles .Where(r => r.RoleID == person.RoleID) .Single() .RoleDescription ); Console.WriteLine(new String('=', 45));
found = false; foreach (Salary salary in salaries .Where(s => s.PersonID == person.PersonID) .ToList()) { if (!found) found = true; Console.WriteLine("{0},{1}", salary.Year, salary.SalaryYear); } if (!found) { Console.WriteLine("No salaries found."); } Console.WriteLine(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Invoke the corresponding method defined in the DataContext class to return three resultsets. The first resultset has the person instances, the second resultset has the role instances and the third resultset has the salary instances.

Iterate through the person resultset and for each person use a language integrated query to retrieve the corresponding role objects associated with that person from the role resultset.

Then, iterate through the salary resultset using a language integrated query to retrieve the corresponding salary objects associated with that person from the person resultset.

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

static void DeletePerson() { Int32 recordsAffected = Int32.MinValue;
try { List<Salary> salaries = m_dataContext.GetSalariesByPersonID(m_IDKNash).ToList();
if (salaries.Count > 0) { foreach (Salary salary in salaries) { recordsAffected = 0; recordsAffected = m_dataContext.DeleteSalaryBySalaryID(salary.SalaryID); } } recordsAffected = m_dataContext.DeletePersonByPersonID(m_IDKNash); Console.WriteLine("Kevin Nash (IDPerson={0}) has been deleted.", m_IDKNash.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

Invoke the corresponding methods defined in the DataContext class to retrieve the salary records for a specific person, from the Salary database table. Store the results in a Salary list collection.

Iterate through the person resultset and invoke the corresponding method defined in the DataContext class to delete a specific salary record from the Salary database table. Once all the salaries have been deleted, invoke the corresponding method defined in the DataContext class to delete the single person from the Person database table.

static void DeleteRoleAndPeople(String roleDesc) { Int32 recordsAffected = Int32.MinValue;
try { List<Person> people = m_dataContext.GetPeopleByRoleID(m_IDAnalyst).ToList();
if (people.Count > 0) { foreach (Person person in people) { recordsAffected = m_dataContext.DeleteSalariesByPersonID(person.PersonID); recordsAffected = m_dataContext.DeletePersonByPersonID(person.PersonID); } } recordsAffected = m_dataContext.DeleteRoleByRoleIDAndDescription(m_IDAnalyst, roleDesc);
Console.WriteLine("{0} (IDRole={1}) has been deleted.", roleDesc, m_IDAnalyst.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

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) { Int32 recordsAffected = Int32.MinValue;
try { List<Person> people = m_dataContext.GetPeopleByRoleID(m_IDAdmin).ToList();
if (people.Count > 0) { foreach (Person person in people) { recordsAffected = m_dataContext.DeleteSalariesByPersonID(person.PersonID); recordsAffected = m_dataContext.DeletePersonByPersonID(person.PersonID); } } recordsAffected = m_dataContext.DeleteRoleByRoleIDAndDescription(m_IDAdmin, roleDesc);
Console.WriteLine("{0} (IDRole={1}) has been deleted.", roleDesc, m_IDAdmin.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

Invoke the corresponding methods defined in the DataContext class to retrieve the person records associated with a specific role, from the Person database table. Store the results in a Person list collection.

Iterate through the person resultset and invoke the corresponding method defined in the DataContext class to delete all the salary records (at one time) from the Salary database table. Once all the salaries have been deleted, invoke the corresponding method defined in the DataContext class to delete the single person from the Person database table.

Finally, invoke the corresponding method defined in the DataContext class to delete the single role record from the Role database table.

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

static void DeleteSalary() { Int32 recordsAffected = Int32.MinValue;
try { recordsAffected = m_dataContext.DeleteSalaryBySalaryID(m_SalIDGrant);
Console.WriteLine("Salary (Salary ID={0}) has been deleted.", m_SalIDGrant.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

Invoke the corresponding methods defined in the DataContext class to delete the single salary record from the Salary database table.