LINQ to SQL Custom Code Review

Define Custom DataContext Class

Code Review

• LINQ to SQL Custom Code Review: Define Person, Role and Salary Classes
• LINQ to SQL Custom Code Review: Mapping the Class and Database Objects Using XML Mapping File
• LINQ to SQL Custom Code Review: Define Custom DataContext Class
• LINQ to SQL Custom Code Review: Manipulate the Database using the DataContext Object

Code Walkthrough

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.

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:

• Object instance - represents the instance of the DataContext invoking the method.
• MethodInfo methodInfo - returns a MethodBase object representing the currently executing method, which is used to map to Function element in the XML mapping file.
• params Object[] parameters - The array of input parameters to be passed to the stored procedure.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.