Database lookup in a GridView?

I have a gridview where I'm getting a number of rows returned...

SELECT SessionName, Capacity from Sessions

However,  this makes my GridView look something like this

Session 1 |  20
Session 2 |  25

However, I'd like to do another lookup on the registrants table to see how many are registered for each session, so that my GridView would look something like this...

Session 1 | 14/20
Session 2 | 11/25

Is there a way that I could do a database lookup from the registrants table for each row??  Kind of hard to explain...

But basically it will add a database lookup for every session in the sessions table.  That might be 30-40 lookups.  Is that a problem?

Can I do this in a GridView?  Or do I need to use a C# loop?
LVL 2
sayguhAsked:
Who is Participating?
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
There should be no problem with that. You could use the OnRowDataBound event of the GridView to perform the subsequent lookups.

However, it would be more efficient to perform all of the data retrieval using SQL. If your registrants table has a foreign key field to your Sessions table, you should be able to retrieve a count of the registrants per session. From there, you can either use SQL to format the returned information (registrants / capacity), or return them as separate values in your dataset.
0
 
sayguhAuthor Commented:
Anyway you could provide some sample code?  Everything I'm googling looks overly complicated

Thanks if you can!
0
 
sayguhAuthor Commented:
Alright, I have it doing the database lookup for each row now..   but I'm just doing

"SELECT count from registrants"

How can I do "SELECT count from registrants WHERE session_ID=" + ???

How can I reference the dynamic session ID for that row?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
sayguhAuthor Commented:
I tried

session_ID=" + e.Row.Cells[1].Text

But that didn't seem to work...

(the session ID is in that cell)
0
 
sayguhAuthor Commented:
Got it!
0
 
Shaun KlineLead Software EngineerCommented:
Just to follow up on the my SQL comment above, you could use a SQL call such as:

SELECT Sessions.Session_ID, Sessions.SessionName, Sessions.Capacity, Count(*) Registrant_Count
FROM Sessions
   INNER JOIN registrants ON Sessions.session_ID = registrants.session_ID
GROUP BY Sessions.Session_ID, Sessions.SessionName, Sessions.Capacity

This should bring back all of the parts you would need to display the data that you want in your gridview and, therefore, reduce the number of database calls your application makes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.