Batch of SQL statements in a single query

I'm using the following query for multiple transactions. This is the scenario.

Generate Salary (this is not in the scope of the query)
I have employee attendance in Attendance table. Then the salary is calculate from the attendance by using a stored procedure and resulting figures will be displayed in an interface allowing users to monitor / modify (entering allowances etc).

THE QUERY

-
When the user press save, the salary figures should be inserted to
SalaryTrans table.

-
If salaries are regenerated and re saved the user then the existing

figures should be updated. (After modifying attendance or salary figures such as allowances etc)

-
When regenerated, in case if an employee who was in the attendance

earlier but now has been deleted later from attendance, then the salary

figures already in SalaryTrans table should also be deleted. (To

ensure that only the employees in attendance table are the ones in

SalaryTrans table)

The following query was used for the above purpose and I want to know whether this approach is acceptable or not, and also any feedback regarding the logic and its implementation. So could you review this code please?

NOTE : The SalaryTrans table has a varChar field called reference to hold the month and year of salary (ex value. Jul2014)

The Attendance table has DateTime fields called in_time and out_time to hold on and off of attendance. This in_time field should be used when filtering data pertaining to a month.

```
public bool InsertEarnings(List earningsList, string reference, DateTime fromDate, DateTime toDate)
{
using (SqlConnection sqlConnection = new SqlConnection(db.ConnectionString))
{
string insertStatement = "IF NOT EXISTS (SELECT * FROM SalaryTrans WHERE employee_id=@employee_id and reference = @reference )" +
"INSERT INTO salaryTrans " +
"(e

As this method is quite large, let's start digging through the code.

• insertStatement should be a const on class level

-
The creation of the SqlParameters can be extracted to a method

private SqlParameter[] GetInsertEarningsParameters()
{
    //I use a List because it is easier to read.
    List sqlParameters = new List();

    sqlParameters.Add(new SqlParameter("@employee_id", SqlDbType.Char));
    sqlParameters.Add(new SqlParameter("@work_days", SqlDbType.Int));
    sqlParameters.Add(new SqlParameter("@day_offs", SqlDbType.Int));
    sqlParameters.Add(new SqlParameter("@leave_days", SqlDbType.Int));
    sqlParameters.Add(new SqlParameter("@absent_days", SqlDbType.Int));
    sqlParameters.Add(new SqlParameter("@extra_shifts", SqlDbType.Int));
    sqlParameters.Add(new SqlParameter("@basic_salary", SqlDbType.Decimal));
    sqlParameters.Add(new SqlParameter("@budjetory_allowance", SqlDbType.Decimal));
    sqlParameters.Add(new SqlParameter("@no_pay_days", SqlDbType.Int));
    sqlParameters.Add(new SqlParameter("@less_no_pay_amount", SqlDbType.Decimal));
    sqlParameters.Add(new SqlParameter("@amount_for_epf", SqlDbType.Decimal));
    sqlParameters.Add(new SqlParameter("@over_time_amount", SqlDbType.Decimal));
    sqlParameters.Add(new SqlParameter("@extra_shifts_amount", SqlDbType.Decimal));
    sqlParameters.Add(new SqlParameter("@incentive_allowance", SqlDbType.Decimal));
    sqlParameters.Add(new SqlParameter("@special_allowance", SqlDbType.Decimal));
    sqlParameters.Add(new SqlParameter("@other_allowance", SqlDbType.Decimal));
    sqlParameters.Add(new SqlParameter("@brought_forward_amount", SqlDbType.Decimal));
    sqlParameters.Add(new SqlParameter("@epf", SqlDbType.Decimal));
    sqlParameters.Add(new SqlParameter("@reference", SqlDbType.VarChar));
    sqlParameters.Add(new SqlParameter("@fromDate", SqlDbType.DateTime));
    sqlParameters.Add(new SqlParameter("@toDate", SqlDbType.DateTime));

    return sqlParameters.ToArray();
}

and then be called

sqlCommand.Parameters.AddRange(GetInsertEarningsParameters());

-
As the values for reference, toDate and fromDate won't change, we will assign the parameter values before the foreach loop only once

sqlCommand.Parameters["@reference"].Value = reference;
sqlCommand.Parameters["@toDate"].Value = toDate;
sqlCommand.Parameters["@fromDate"].Value = fromDate;

-
The assignments of the values of the Earning's object should be extracted to a method and the magic number 100 * 8 should be a const

private const int AMOUNT_FOR_EPF_DIVISOR = 100 * 8;
private void FillInsertEarningsParameterValues(Earning earning, SqlParameterCollection parameters)
{
    parameters["@employee_id"].Value = earning.EmployeeID;
    parameters["@work_days"].Value = earning.WorkDays;
    parameters["@day_offs"].Value = earning.DayOffs;
    parameters["@leave_days"].Value = earning.LeaveDays;
    parameters["@absent_days"].Value = earning.AbsentDays;
    parameters["@extra_shifts"].Value = earning.ExtraShifts;
    parameters["@basic_salary"].Value = earning.BasicSalaryAmount;
    parameters["@budjetory_allowance"].Value = earning.BudjetoryAllowance;
    parameters["@no_pay_days"].Value = earning.NoPayDays;
    parameters["@less_no_pay_amount"].Value = earning.LessNoPayAmount;
    parameters["@amount_for_epf"].Value = earning.AmountForEPF;
    parameters["@over_time_amount"].Value = earning.OverTimeAmount;
    parameters["@extra_shifts_amount"].Value = earning.ExtraShiftAmount;
    parameters["@incentive_allowance"].Value = earning.IncentiveAllowance;
    parameters["@special_allowance"].Value = earning.SpecialAllowance;
    parameters["@other_allowance"].Value = earning.OtherAllowance;
    parameters["@brought_forward_amount"].Value = earning.BroughtForwardAmount;
    parameters["@epf"].Value = earning.AmountForEPF / AMOUNT_FOR_EPF_DIVISOR;
}

now the foreach will be

foreach (Earning earning in earningsList)
{
    FillInsertEarningsParameterValues(earning,sqlCommand.Parameters);

    sqlCommand.ExecuteNonQuery();
}

-
The result of the refactoring of your large method would be

```
public bool InsertEarnings(List earningsList, string reference, DateTime fromDate, DateTime toDate)
{
using (SqlConnection sqlConnection = new SqlConnection(db.ConnectionString))
{

using (SqlCommand sqlCommand = new SqlCommand(insertStatement, sqlConnection))
{
sqlCommand.Parameters.AddRange(GetInsertEarningsParameters());

sqlCommand.Parameters["@reference"].Value = reference;
sqlCommand.Parameters["@toDate"].Value = toDate;
sqlCommand.Parameters["@fromDate"].Value = fromDate;

sqlConnection.Open();
SqlTransaction sqlTrans = sqlConnection.BeginTransaction("Insert");
sqlCommand.Transaction = sqlTrans;
try
{
foreach (Earning earning in earningsList)
{
FillInsert

StackExchange Code Review Q#58353, answer score: 7