help with reducing the number of connections to the database

Question

I have a list of strings that are just invoice numbers. I am enumerating through this list to get the details of each invoice from the database. This list can easily be 700 to 1000 in size. the way I am doing it now results in 700-1000 connections to the database. this is taking too long to complete is there a better way to do this that I just don't know about? Any pointers would be great.

here is an example of my enumeration

foreach(string i in invoiceList)
{
  Invoice inv = invoiceData.GetInvoice(i);
  //do something with the invoice
}

then here is an example of my data access method using ado.net

public Invoice GetInvoice(string invoice)
{
      SqlConnection con = new SqlConnection(//connection string);
      SqlCommand cmd = new SqlCommand("dbo.getInvoices", con);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Add("invoice", SqlDbType.VarChar).Value = invoice;
      SqlDataReader dr;
      Invoice inv = new Invoice();
      try{
            con.Open();
            dr = cmd.ExecuteReader
            while(dr.read())
            {
                 //assign values from the database fields
            }


      }
      catch{}
      finally{con.close();}

}

so basically the getInvoice method gets called 1000 times opening a new connection every time. What is a better(faster) way to do this. Thank you!

Answer 1

You can put your connection opening and closing code outside of your loop. That would make you only have one connection to the database. But that one connection would be open for a while. That's the trade-off. One connection open for a long time or lots of connections opening and closing.

I am also noticing that you are not closing your connection in the try code. Maybe try this.

public Invoice GetInvoice(string invoice)
{
      SqlConnection con = new SqlConnection(//connection string);
      SqlCommand cmd = new SqlCommand("dbo.getInvoices", con);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Add("invoice", SqlDbType.VarChar).Value = invoice;
      SqlDataReader dr;
      Invoice inv = new Invoice();
      try{
            con.Open();
            dr = cmd.ExecuteReader
            while(dr.read())
            {
                 //assign values from the database fields
            }
      }
      catch{}
      finally
      {
        con.Close();
      }
}

Answer 2

Simply put all invoice nos in a IN statement and run this select statement in a single connection.

Answer 3

Hi,

I believe you may want to consider a different approach if you consistently are processing 700 to 1000 or more invoice numbers at one time why not send all the invoice numbers down in a single query instead of many individual query's. As an example you could use an sql in list to do this as in below.

select
 *
from
 ivoice_table
where
 invoice_table.invoice_number in (123,124,125,126,127,128 etc....)

Enjoy!

Answer 4

Something like this might be an improvement.

public List<Invoice> GetInvoices(List<string> invoiceList) {
  List<Invoice> invoices = new List<Invoice>();

  Invoice inv;
  SqlDataReader dr;

  using (SqlConnection con = new SqlConnection(//connection string)) {
    using(SqlCommand cmd = new SqlCommand("dbo.getInvoices", con)) {
      cmd.CommandType = CommandType.StoredProcedure;
      SqlParameter param = cmd.Parameters.Add("invoice", SqlDbType.VarChar);

      foreach(string i in invoiceList) {
        inv = new Invoice();
        param.Value = i;
        using (dr = cmd.ExecuteReader()) {
          while(dr.read())
          {
            // assign values from the database fields
            inv.Property = dr.GetString(0);

            // Add invoice to the result list
            invoices.Add(inv);
          }
        }
      }
    }
  }

  return invoices;
}

Then you can use this method like so...

var invoiceList = new List<string> { "123", "456", "789" };
var invoices = GetInvoices(invoiceList);
foreach(var i in invoices) {
  Console.WriteLine(i.SomeInvoiceProperty);
}