LINQ to SQL Custom Code Review

Manipulate the Database using the DataContext Object

Code Review

Code Walkthrough

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.

More Information

Code Review

Microsoft.NET

Share Content