CODEWITHSUNDEEP
c#datagridviewrow
K09
K09

Reputation: 201

Querying Database - Best Practice

I have a number of database tables below. I want to display each user in a datagridview and their related 'Pass' field value.

Current query is 1. Load all Users from User table 2. Users added to datagridview 3. Loop datagridview - for each user query i) ExamGeography, ii) ExamMath, iii) ExamSpanish

    foreach (DataGridViewRow dgvRow in resultsDataGridView.Rows)
    {
    dgvRow.Cells["geographyCol"].Value = 'query result from ExamGeography i.e. SELECT Pass FROM ExamGeography WHERE User=@User'
        dgvRow.Cells["mathCol"].Value = 'query result from ExamMath i.e. SELECT Pass FROM ExamMath    WHERE User=@User'
    // ...
    }

So each user makes a new call to database. Is it always better to make as few calls as possible to the database? Rather than multiple calls to the same table? What would be considered best practice/best performance?

Should my query just be for example, 'SELECT Pass FROM ExamGeography' (omitting the WHERE clause) and return for all users?

User Table

User (PK) ----------- Member
User1                 Y
User2                 Y
User3                 N

ExamGeography Table

User (FK) ----------- Pass
User1                  Y
User2                  Y
User3                  Y

ExamMath Table

User (FK) ----------- Pass
User1                  N
User2                  Y
User3                  Y

ExamSpanish Table

User (FK) ----------- Pass
User1                  Y
User2                  N
User3                  N


DataGridView Display

Username   Geography     Math      Spanish
User1          Y           N           Y
User2          Y           Y           N
User3          Y           Y           N

Upvotes: 0

Views: 456

Answers (1)

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Yes, it is always better to make as few calls as possible, assuming that the data you pull from the database can be stored in memory. From the looks of your example, this shouldn't be a problem.

Once you've got all the data out of the database (use stored procedures to do this), you can iterate through the results in a for loop, place them into a new data grid view, and Bob's your uncle.

Your query should end up being something along these lines:

SELECT *
FROM UserTable a
JOIN ExamGeographyTable b
    ON a.User = b.User
JOIN ExamMathTable c
    ON a.User = c.User
etc.

Upvotes: 1

Related Questions