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?
Yes. avoid loop it at the front-end, it is bad for performance.
You are almost on the right track.
you need chassis LEFT OUTER JOIN to partlist on 3 conditions partlist, type_defining_note and action
then GROUP By the chassis_number and couting partlist (and not count(*) as you only want to count those there exists in partlist table)
select chassis.chassis_number, count(partlist.part_list_no) as parts
from chassis
left join partlist on chassis.partlist = partlist.part_list_no
and chassis.type_defining_note = partlist.type_defining_note
and partlist.action = '1'
group by chassis.chassis_number
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments