|There are quite a few problems with the code you've shown.
Firstly, your code won't compile. You can't have line-breaks in a standard string. You would need to use either a verbatim string[^] or a raw string[^] for your query.
You should make the query a local
const so that you're not tempted to try to inject parameter values into it incorrectly and introduce a SQL Injection[^] vulnerability into your code.
You seem to be using a shared database connection instance. That's a terrible idea - either your code must be restricted to only service one request at a time, or you'll end up with cross-contamination when multiple users try to access your application, since the connection is not thread-safe. Instead, create the connection when you need it, and wrap it in a
using block to ensure it's disposed of properly.
SqlDataReader instances need to be wrapped in
Your code currently swallows any exceptions, and returns an empty
DataTable instead. The caller is expected to examine the
Error property to determine whether an error occurred, and retrieve a tiny portion of the details of that error - assuming the property hasn't been overwritten by a call from a different user in the meantime. Instead, you should let the exception propagate to the caller naturally. If you need to add more details, then throw a different exception, making sure to include the original exception as the
public static DataTable GetAll()
const string Query = """
using (var connection = Connection.CreateAndOpenConnection())
using (var command = new SqlCommand(connection, Query))
using (var reader = command.ExecuteReader())
DataTable dt = new DataTable();
catch (SqlException ex)
throw new YourCustomException("There was an error retrieving the list of all things. See the inner exception for details.", ex);
Once you've dealt with those issues, then you can start working out what you need to put in the other layers. For example, having a BLL which simply passes all calls through to the DAL and returns the results unchanged doesn't make a lot of sense.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."