3. The Case Study

We want to write a .NET application that allows a user to simulate payroll calculations for child care providers at the "Maison de la petite enfance" association in a municipality. We will focus as much on the organization of the application’s .NET code as on the code itself.

3.1. The database

The static data needed to generate the pay stub is stored in an SQL Server Express database named dbpam (pam = Paie Assistante Maternelle). This database has an administrator named sa with the password msde.

The database has three tables, EMPLOYEES, CONTRIBUTIONS, and ALLOWANCES, with the following structure:


EMPLOYEES table: contains information about the various child care providers

Structure:

`SS`	employee's social security number - primary key
`NAME`	employee's last name
`first name`	first name
`ADDRESS`	their address
`CITY`	his/her city
`ZIP CODE`	his/her ZIP code
`INDEX`	his processing index - foreign key on the [INDICE] field of the [INDEMNITES] table

Its content could be as follows:


COTISATIONS table: contains the rates of social security contributions deducted from the salary

Structure:

Its content could be as follows:

Social security contribution rates are independent of the employee. The previous table has only one row.


INDEMNITIES table: lists the various allowances based on the employee's index

`INDEX`	salary index - primary key
`BASEHOUR`	net hourly rate in euros
`DAILY MAINTENANCE`	daily allowance in euros per day of on-call duty
`MEALPERDAY`	Meal allowance in euros per day of care
`VACATION PAY`	Paid vacation allowance. This is a percentage applied to the base salary.

Its content could be as follows:

Note that allowances may vary from one child care provider to another. They are linked to a specific child care provider via that provider’s pay grade. Thus, Ms. Marie Jouveinal, who has a pay grade of 2 (EMPLOYEES table), has an hourly wage of 2.1 euros (INDEMNITES table).

The relationships between the three tables are as follows:

There is a foreign key relationship between the EMPLOYEES(INDEX) column and the ALLOWANCES(INDEX) column.

The [dbpam] database created in this way generates two files in the SQL Server Express folder:

The [dbpam.mdf, dbpam_log.ldf] files can be transferred to another machine and reattached to that machine’s SQL Server Express database management system. Here’s how to do it:

The [dbpam] database files are copied to a folder

Launch SQL Server Express
Using the SQL Server Management Studio Express client, attach the [dbpam.mdf] file to the database:

Right-click on [Databases] / Attach
Select the [dbpam.mdf] file using an [Add] button (not shown)
The attached file will create a database that must not already exist. Here, in the [Attach As] field, we have named the new database [dbpam2].
You can see the new database and its tables

This technique for attaching a database is useful for transferring a database from one computer to another, and we will use it occasionally here.

3.2. calculation method for a child care provider’s salary

We will now present the method for calculating a childminder’s monthly salary. As an example, we will use the salary of Ms. Marie Jouveinal, who worked 150 hours over 20 days during the pay period.

The following factors are taken into account:	`[TOTALHOURS]: total hours worked in the month` `[TOTALDAYS]: total days worked in the month`	`[TOTALHOURS]=150 [TOTALDAYS] = 20`
The child care provider's base salary is calculated using the following formula:	`[BASESALARY]=([TOTALHOURS]* [HOURLYRATE])*(1+ [CPALLOWANCE]/100)`	`[BASESALARY]=(150[2.1])(1+0.15)= 362.25`
A number of social security contributions must be deducted from this base salary:	`General social contribution and debt to debt repayment : [BASESALARY][CSGRDS/100]` `Deductible deductible: [BASESALARY][CSGD/100]` `Social Security, Widow's, old age: [BASESALARY][SECU/100]` `Supplementary Pension + AGPF + Unemployment Insurance: [BASESALARY][PENSION/100]`	`CSGRDS: 12.64` `CSGD: 22.28` `Social Security: 34.02` `Pension: 28.55`
Total social security contributions:	`[SOCIALCONTRIBUTIONS] = [SALARY EBASE]*(CSGRDS+CSGD+SECU+RETR`	`[SOCIALCONTRIBUTIONS]=97.48`
In addition, the child care provider is entitled to a daily living allowance and a meal allowance for each day worked. As such, she receives the following allowances:	`[Compensation] = [TOTAL_DAYS] * (EN DAILY ALLOWANCE + DAILY MEAL ALLOWANCE)`	`[ALLOWANCES]=104`
In the end, the net salary to be paid to the childminder is as follows:	`[BASESALARY]- [SOCIALSECURITYCONTRIBUTIONS]+ [ALLOWANCES]`	`[NET SALARY]=368.77`

3.3. ADO.NET Reminders

The payroll calculation application requires information from the [dbpam] database. Its architecture will be as follows:

In [1], the user makes a request
In [2], the payroll application processes it.
It may then need data from the database. It then sends a query to the ADO.NET provider of the DBMS being used [4].
The provider accesses the database [5] and returns the results to the ADO.NET provider, which in turn passes them back to the application
The application processes these results and generates a response [5] for the user

We will now review the main interfaces provided by an ADO.NET provider to its clients [3].

In connected mode, the application:

opens a connection to the data source
works with the data source in read/write mode
closes the connection

Three ADO.NET interfaces are primarily involved in these operations:

IDbConnection, which encapsulates the connection’s properties and methods.
IDbCommand, which encapsulates the properties and methods of the executed SQL command.
IDataReader, which encapsulates the properties and methods of the result of an SQL SELECT statement.

The IDbConnection interface

is used to manage the connection to the database. Among the methods (M) and properties (P) of this interface are the following:

Name	Type	Role
`ConnectionString`	P	Database connection string. It specifies all the parameters required to establish a connection with a specific database.
`Open`	M	Opens the connection to the database defined by ConnectionString
`Close`	M	Closes the connection
`BeginTransaction`	M	starts a transaction.
`State`	P	Connection state: ConnectionState.Closed, ConnectionState.Open, ConnectionState.Connecting, ConnectionState.Executing, ConnectionState.Fetching, ConnectionState.Broken

If Connection is a class that implements the IDbConnection interface, the connection can be opened as follows:

1
2
3

IDbConnection connection = new Connection();
connection.ConnectionString = ...;
connection.Open();

The IDbCommand interface

is used to execute an SQL statement or a stored procedure. Among the methods M and properties P of this interface are the following:

Name	Type	Role
`CommandType`	P	specifies what to execute - takes its values from an enumeration: - CommandType.Text: executes the SQL statement defined in the CommandText property. This is the default value. - CommandType.StoredProcedure: executes a stored procedure in the database
`CommandText`	P	- the text of the SQL statement to execute if CommandType= CommandType.Text - the name of the stored procedure to execute if CommandType= CommandType.StoredProcedure
`Connection`	P	the IDbConnection connection to use to execute the SQL statement
`Transaction`	P	the IDbTransaction transaction in which to execute the SQL statement
`Parameters`	P	The list of parameters for a parameterized SQL statement. The statement `update articles set price=price1.1 where id=@id`* has the parameter `@id`.
`ExecuteReader`	M	To execute a SELECT SQL statement. This returns an IDataReader object representing the result of the SELECT statement.
`ExecuteNonQuery`	M	to execute an SQL Update, Insert, or Delete statement. The number of rows affected by the operation (updated, inserted, or deleted) is returned.
`ExecuteScalar`	M	to execute an SQL Select statement that returns a single result, such as: select count() from articles*.
`CreateParameter`	M	to create the IDbParameter parameters of a parameterized SQL statement.
`Prepare`	M	allows you to optimize the execution of a parameterized query when it is executed multiple times with different parameters.

If Command is a class that implements the IDbCommand interface, executing an SQL statement without a transaction will take the following form:

// open connection 
IDbConnection connection=...
connection.Open();
// prepare command
IDbCommand command = new Command();
command.Connection = connection;
// execute select statement
command.CommandText = "select ...";
IDbDataReader reader = command.ExecuteReader();
...
// execute update, insert, delete commands
command.CommandText = "insert ...";
int rowsInserted = command.ExecuteNonQuery();
...
// close connection
connection.Close();

The IDataReader interface

Used to encapsulate the results of an SQL Select statement. An IDataReader object represents a table with rows and columns, which are processed sequentially: first the first row, then the second, and so on. Among the methods (M) and properties (P) of this interface are the following:

Name	Type	Role
`FieldCount`	P	The number of columns in the IDataReader table
`GetName`	M	GetName(i) returns the name of column i in the IDataReader table.
`Item`	P	Item[i] represents column number i of the current row in the IDataReader table.
`Read`	M	Moves to the next row in the IDataReader table. Returns True if the row was successfully read, False otherwise.
`Close`	M	Closes the IDataReader table.
`GetBoolean`	M	GetBoolean(i): returns the Boolean value of column i in the current row of the IDataReader table. Other similar methods include: GetDateTime, GetDecimal, GetDouble, GetFloat, GetInt16, GetInt32, GetInt64, GetString.
`Getvalue`	M	Getvalue(i): returns the value of column i in the current row of the IDataReader table as an object type.
`IsDBNull`	M	IsDBNull(i) returns True if column i of the current row in the IDataReader table has no value, which is represented by the SQL NULL value.

Using an IDataReader object often looks like the following:

// Open connection 
IDbConnection connection = ...
connection.Open();
// prepare command
IDbCommand command = new Command();
command.Connection = connection;
// execute select statement
command.CommandText = "select ...";
IDataReader reader = command.ExecuteReader();
// process results
while (reader.Read()) {
    // process current row
        ...
}
// close reader
reader.Close();
// close connection
connection.Close();