Making a data access class for all programs or individual C#

Question

So the title is a bit vague, but the question is really this: in practice is it best to make a data access class, in this case access to a SQL Server.

Where all the static methods of the class need a connection string and a SQL statement?

Something along these lines:

public static void ExecuteSql(string connStr, string strSqlStatement)
{
    SqlConnection conn = new SqlConnection(connStr);
    conn.Open();

    SqlCommand cmd = new SqlCommand(strSqlStatement, conn);
    cmd.ExecuteNonQuery();

    conn.Close();
    conn.Dispose();
}

And then a method that does something similar except it returns data you queried for.

Or in practice, is it better to create an object for the specific application you are building, and code these items as the objects extensions. So the SQL to run or stored procedure to execute would be wrapped up in that class.

 obj.GetSomethingViaQueryThatIsWrittenInTheClassLibrary()

So I guess the first one is more like a service library for accessing and writing data in a SQL Server database. What does one typically do in this situation?

Or could you even use the service library in tandem with the objects library?

There's a ton of, not conflicting, but different opinions on data access and I am really trying to see what is more common a practice.

Or 3rd, use an ORM something like entity framework. Which eliminates connection strings hassles/sql injections/touch queries — Jeroen van Langen, Nov 14 '18 at 15:38
I'm not sure if your question is specific enough for stack overflow as you want more general advice on creating a data access layer in .Net. One piece of advice I can give however is you should make use of the using keyword when opening a connection to the database. https://stackoverflow.com/questions/5642469/how-is-dispose-called-on-ado-net-objects — nick gowdy, Nov 14 '18 at 15:39
Look at the "repository" design pattern, that's pretty commonly used. A good example would be Entity Framework. — Josef Fazekas, Nov 14 '18 at 15:44
@JosefFazekas, so there is quite a bit here to look at, but it looks like data access is usually a separate layer of the application? — MZawg, Nov 14 '18 at 16:01
@J.vanLangen So you would go for EF over ADO.net connection for example? For readability sake and ease or programming? — MZawg, Nov 14 '18 at 16:06
@J.vanLangen its these types of articles that always make me question if I am helping or hurting the organization: https://iamtimcorey.com/ask-tim-dont-use-entity-framework/ , thats really where some of my stack questions stem from. — MZawg, Nov 14 '18 at 16:26
Ik would go for the EF if I'm writing an application that lets users interact with data from a database. I would go for the ADO.net if I need to process large amounts of data (like importing/exporting) — Jeroen van Langen, Nov 14 '18 at 18:38
@MikeCMR There is no right or wrong answer to using EF vs Dapper. They both have their advantages and disadvantages. Learn both, then choose based on needs. — mason, Nov 14 '18 at 18:52
@MikeCMR Yes, but that's a rabbit hole you could fall into, adding layer after layer. I like to keep it simple in my projects and create a Data-Access-Layer usually containing "DAL" in the name, containing the repositories (that would be a EF context or two) and my POCO classes. Sure you could add another layer for your models but like mason said: that's a design decision that heavily depends on the scope of your project. — Josef Fazekas, Nov 15 '18 at 10:05
@JosefFazekas Thanks! Your input and advice is much appreciated and super helpful. I was looking at it as if I should only write a data access layer once, and add it to any project I ever have, but it sounds like the way I access data could differ project to project. Again, thank you. — MZawg, Nov 15 '18 at 13:49

Jeroen van Langen · Accepted Answer · 2018-11-14T19:01:09.817

I disagree with Terry, because:

The connections are pooled, so opening/closing connections is not a problem and you don't want to use a single connection to execute multiple concurrent queries. This is very usefull when having many concurrent threads (for example webservers) but this also applies on a normal application (which might uses Tasks to retrieve data on a separate thread to keep the UI responsive)

So I would create a ConnectionManager class that uses a connection string as constructor parameter, this way the connectionstring wouldn't 'travel' thru your program and is encapsulated in a 'manager' object.

This is a poor example, but I think it work just fine when using ADO.NET

public class ConnectionManager
{
    private string _connectionString;

    public ConnectionManager(string connectionString)
    {
        _connectionString = connectionString;
    }

    public SqlConnection GetConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

Then I would use it something like:

var connectionManager = new ConnectionManager(connectionString);


using(var con = connectionManager.GetConnection())
{
    // not all operations require .Open()/.Close()

    // multiple queries.
}

using(var con = connectionManager.GetConnection())
{
    // not all operations require .Open()/.Close()

    // multiple other queries.
}

By using using the connection will be disposed (put back in the pool)

You might even use something like:

public class ConnectionManager
{
    private string _connectionString;

    public ConnectionManager(string connectionString)
    {
        _connectionString = connectionString;
    }

    public void ExecuteNonQuery(string strSqlStatement)
    {
        using(var connection = new SqlConnection(_connectionString))
        using(var command = new SqlCommand(strSqlStatement, connection))
        {
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

Which makes:

var connectionManager = new ConnectionManager(connectionString);

connectionManager.ExecuteNonQuery("SELECT * FROM Whatever");

Tip: You sure need to checkout the SqlParameter to prevent SQL Injection

score 1 · Answer 2 · answered Nov 14 '18 at 16:23

1

I wouldn't create, open, and close the connection in an sql execute method like that. Instead, I recommend creating the connection and pass it in to the query method each time a query is needed and when all queries are finished, then close the connection.

answered Nov 14 '18 at 16:23

Terry Tyson

629
8
18

Upvoted @J. van Langen's answer. I like the "using". – Terry Tyson Nov 14 '18 at 19:19

Making a data access class for all programs or individual C#

2 Answers2