Using resource like db connection from outermost Using statement in deeply nested innermost function?

Question

What's the proper pattern for this? I want to obtain a database connection in a Using statement in the outermost function (logging & aborting if connection fails) (or do I really? that's the question), but use that connection in the deeply nested innermost function. I want to import all .csv files in a given directory into a database, doing some custom slicing & dicing in processthisline().

Is a try/catch/finally in methodthatwillbecalled(), saving db to a class property what I'm after? Maybe some other pattern that hasn't occurred to me?

public sub methodthatwillbecalled
  using db                            <--obtain connection
    processallfiles()
  end using

private sub processallfiles
  for each file in source directory
    processthisfile()

private sub processthisfile
  for each line in file
    processthisline()

private sub processthisline
  split on commas and INSERT to db   <--perform work on connection, many, many times

Answer 1

You would want to either pass the db object as a parameter all the way down or set a class property.

If you set the class property, you may want to make sure to close the connection in every case of exiting, so the "using" as you have it is safer.

If you pass the DB object as a parameter, the connection will stay open for each insert and should close properly if something goes wrong.

You definitely don't want to put the connection in any other sub, because that would open and close for each file or line (depending on where you put it).