I am building a C# WinForms app in which users able to check with a randomly generated chassis number list if a chassis number for a specific action is connected to a part list and if yes the available part numbers are counted. Each chassis number connects to one part list (or not). That specific part list contains parts that are linked to chassis numbers via type defining notes. For example 100024 has a type defining note of D13K420, therefore it should see in part list 13080554 lines with that type defining notes (count 3).
I have two data tables in MSSQL Server. One for chassis numbers:
| ID | chassis_number | model | build_date | factory | type_defining_note | partlist |
|----|----------------|-------|------------|---------|--------------------|----------|
| 1 | 100000 | 1 | 2014-01-04 | 2 | D13K500 | 13080553 |
| 2 | 100001 | 1 | 2014-01-06 | 2 | D13K460 | 13080554 |
| 3 | 100002 | 1 | 2014-01-12 | 2 | D13K460 | 13080554 |
| 4 | 100023 | 1 | 2014-01-15 | 2 | D13K540 | 13080553 |
| 5 | 100024 | 1 | 2014-01-20 | 1 | D13K420 | 13080554 |
| 6 | 100025 | 1 | 2014-01-26 | 2 | D13K500 | 13080553 |
| 7 | 122312 | 1 | 2014-02-08 | 1 | D13K500 | 0 |
| 8 | 104560 | 1 | 2014-02-09 | 1 | D13K460 | 0 |
| 9 | 100045 | 1 | 2014-03-04 | 2 | D13K540 | 0 |
| 10 | 100046 | 1 | 2014-03-12 | 2 | D13K540 | 0 |
And for part lists:
| ID | part_list_no | part_list_description | model | position | part_number | part_description | quantity | type_defining_note | action | action_type |
|----|--------------|-----------------------|-------|----------|-------------|------------------|----------|--------------------|--------|-------------|
| 1 | 13080553 | Oil filter (Engine) | 1 | 1 | 21803087 | Filter | 1 | D13K500 | 1 | P |
| 2 | 13080553 | Oil filter (Engine) | 1 | 1 | 21803088 | Filter | 1 | D13K540 | 1 | P |
| 3 | 13080553 | Oil filter (Engine) | 1 | 2 | 24523486 | Oil cooler | 1 | D13K500 | 1 | S |
| 4 | 13080553 | Oil filter (Engine) | 1 | 2 | 24523486 | Oil cooler | 1 | D13K540 | 1 | S |
| 5 | 13080553 | Oil filter (Engine) | 1 | 3 | 27647538 | O-ring | 2 | D13K500 | 1 | S |
| 6 | 13080553 | Oil filter (Engine) | 1 | 3 | 27647538 | O-ring | 2 | D13K540 | 1 | S |
| 7 | 13080553 | Oil filter (Engine) | 1 | 4 | 23984567 | Pipe | 1 | | 0 | |
| 8 | 13080553 | Oil filter (Engine) | 1 | 5 | 23984558 | Pipe | 1 | | 0 | |
| 9 | 13080554 | Oil filter (Engine) | 1 | 1 | 21803085 | Filter | 1 | D13K420 | 1 | P |
| 10 | 13080554 | Oil filter (Engine) | 1 | 1 | 21803086 | Filter | 1 | D13K460 | 1 | P |
| 11 | 13080554 | Oil filter (Engine) | 1 | 2 | 24523486 | Oil cooler | 1 | D13K420 | 1 | S |
| 12 | 13080554 | Oil filter (Engine) | 1 | 2 | 24523482 | Oil cooler | 1 | D13K460 | 1 | S |
| 13 | 13080554 | Oil filter (Engine) | 1 | 3 | 27647538 | O-ring | 2 | D13K420 | 1 | S |
| 14 | 13080554 | Oil filter (Engine) | 1 | 3 | 27647538 | O-ring | 2 | D13K460 | 1 | S |
| 15 | 13080554 | Oil filter (Engine) | 1 | 4 | 23984567 | Pipe | 1 | | 0 | |
| 16 | 13080554 | Oil filter (Engine) | 1 | 5 | 23984558 | Pipe | 1 | | 0 | |
The connection made with a simple join: FULL OUTER JOIN part_list ON chassis.part_list = part_list.part_list_number
What I would like achieve is this:
| chassis_number | parts |
|----------------|-------|
| 100000 | 3 |
| 100001 | 3 |
| 100002 | 3 |
| 100023 | 3 |
| 100024 | 3 |
| 100025 | 3 |
| 100045 | 0 |
| 100046 | 0 |
| 104560 | 0 |
| 122312 | 0 |
If there is a connection between the chassis number and part list, count all the part numbers that has a valid connection via type defining note, if not simply return a 0.
I was able to create a query which counts the parts, but putting it inside a for loop in C# returns parts only for one chassis number or if multiple chassis number was given returns 0:
SELECT COUNT(chassis.chassis_number) AS parts FROM chassis FULL OUTER JOIN part_list ON chassis.part_list = part_list.part_list_number WHERE chassis.chassis_number = @chassisnumber AND part_list.action = @action AND part_list.type_defining_note LIKE(SELECT chassis.type_defining_note FROM chassis WHERE chassis.chassis_number = @chassisnumber)
And the code that should return the counted parts:
public static void CheckPackages()
{
for (int i = 0; i < chassisNumber.Count; i++)
{
Variables.searchChassisNumber = Convert.ToInt32(chassisNumber[i]);
try
{
command.Connection = connection;
command.CommandText = "SELECT COUNT(chassis.chassis_number) AS parts FROM chassis FULL OUTER JOIN part_list ON chassis.part_list = part_list.part_list_number WHERE chassis.chassis_number = @chassisnumber AND part_list.action = @action AND part_list.type_defining_note LIKE(SELECT chassis.type_defining_note FROM chassis WHERE chassis.chassis_number = @chassisnumber)";
command.CommandType = CommandType.Text;
SqlParameter paramChassisNumber = new SqlParameter
{
ParameterName = "@chassisnumber",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Input,
Value = Variables.searchChassisNumber
};
SqlParameter paramAction = new SqlParameter
{
ParameterName = "@action",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Input,
Value = Variables.selectedAction
};
for (int j = 0; j < part.Count; j++)
{
part.RemoveAt(j);
}
command.Parameters.Add(paramChassisNumber);
command.Parameters.Add(paramAction);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
part.Add((int)reader["parts"]);
}
reader.Close();
command.Parameters.Clear();
partList.Add(new Parts(chassisNumber[i], part[i]));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
I read on other answers here that running a query inside a loop is not too wise (performance issues, in my case there might be some other failure in my code).
Link to sample database on SQLFiddle: http://sqlfiddle.com/#!6/6d4638/74
The question is: what options are available for me to get a working solution? Should I put inside a for loop my query, or create a new parameterised query, or should I rebuilt the whole database to make it work?