#TIL Returning Multiple Data Tables

If you were to do an audit of the average web application, you’d probably find anywhere from one to ten calls to a database per page rendered or API call served. There’s a certain amount of overhead associated with each individual database query, so instead let’s make one query and return as much data back as needed.

More often than not, when people use a data-access-layer technology such as Entity Framework, nHibernate, or even an in-house solution, the communication with the database itself ends up abstracted away. It’s easy to forget the overhead and cost incurred with each connection to your database, since you don’t have to deal with it directly. Using a stored procedure, you can change the amount of data you get back in each request, possibly eliminating the need for multiple calls and reducing the overhead associated per query.

Within your stored procedure, it’s as simple as selecting from the tables you need data from

1
2
3
4
5
6
CREATE PROCEDURE SelectInvoiceData
  (@InvoiceID int)
 AS
  SELECT * FROM Invoices WHERE InvoiceID = @InvoiceID
  SELECT * FROM InvoiceLineItems WHERE InvoiceID = @InvoiceID
  SELECT * FROM InvoicePayments WHERE InvoiceID = @InvoiceID

Nothing too crazy going on there – we’re simply making a select statement against each table we want data from. Once you have that set up in your DB, you can call it using a data adapter in C#

1
2
3
4
5
6
7
var sqlCommand = new SqlCommand("SelectInvoiceData", connectionObject);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@InvoiceID", 1);

var dataAdapter = new SqlDataAdapter(sqlCommand);
var dataSet = new DataSet();
dataAdapter.Fill(dataSet);

From here you can simply refer to the tables in the data set to retrieve the data:

1
2
3
dataSet.Tables["Table"] //Invoices
dataSet.Tables["Table1"] //InvoiceLineItems
dataSet.Tables["Table2"] //InvoiceLineItems

See? Instead of making three distinct calls to our database, we’ve got all the data we need in one!

Comments