LINQ to SQL Integrated 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;
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.

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 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.

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 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.

More Information

Code Review

Microsoft.NET

Share Content