The SqlDataAccess and DapperSqlDataAccess classes provides an easy to use and very lightweight Data Access Layer for direct ADO.NET data access. Using this simple class you can perform most data access tasks with single method calls:

• Query data and receive DataReader, DataTable, DataSet, IEnumerable, List lists
• Execute non-query operations like INSERT, Update, Deletes
• Call stored procedures with more natural syntax
• Simplified named parameter passing either using positional or named parameters
• Query, Update and Insert strongly typed objects into a database
• Execute generated SQL Scripts
• and much more...

The DapperSqlDataAccess class uses Dapper to provide query and object serialization services to improve Query list and record retrieval operations for improved performance. In this document you can choose between the two classes but the document only described SqlDataAccess.

For Class documentation you can check the DatabaseAccessBase which is the core data access base class from which specific implementations like the SQL Server specific SqlDataAccess implementation, which primarily just inherits the base functionality.

Connection Strings

In order to use the Data Access class you'll need a .NET connection string. These classes support connection strings in two formats:

• Raw Connection Strings
• Configuration ConnectionStrings entry

Raw connection strings are whatever the provider uses. For SQL Server that'd be:

server=.;database=WebStore;uid=me;pwd=ultra-seekrit;encrypt=false;

For Connection String Configuration from web.config or app.config in full framework applications:

<configuration>
    <connectionStrings>
        <add name="WebStoreConnection"
             connectionString="Data Source=.;Database=WebStore;integrated security=true;enlist=false;" />
    </connectionStrings>
</configuration>

For non-full framework applications you always have to provide an explicit connection string. It's recommended you read the connection string from your configuration during startup and store it in an easy accessible configuration object either using .NET Core Configuration, or a static instance that holds common config values retrieved from your configuration source(s).

A few simple examples

With connection strings configured you can now create an instance of the Data Access class and call its methods:

ExecuteReader: returns a .NET DataReader

using (var data = new SqlDataAccess(AppConfiguration.Current.ConnectionString))
{
    var reader = data.ExecuteReader("select * from customers");

    Assert.IsTrue(reader.HasRows);
                
    while (reader.Read())
    {
        Console.WriteLine((string)reader["LastName"] + " " + (DateTime)reader["Entered"]);
    }
}

Query: Returns a collection of typed objects

using (var data = new SqlDataAccess(AppConfiguration.Current.ConnectionString))
{
    var custList = data.Query<Customer>("select * from customers where LastName like @0", "S%");

    Assert.IsNotNull(custList, data.ErrorMessage);

    foreach (var customer in custList)
    {
        Console.WriteLine(customer.Company + " " + customer.Entered);
    }
}

Find: Finds the first occurrance of a typed object (or null)

using (var data = new SqlDataAccess(STR_ConnectionString))
{
    var customer = data.Find<Customer>("select * from customers where id=@0", 1);
    Assert.IsNotNull(customer, data.ErrorMessage);
    Console.WriteLine(customer.Company);
}

ExecuteNonQuery: Executes a non-query operation like Insert/Update/Delete

using (var data = new SqlDataAccess(STR_ConnectionString))
{
    var count = data.ExecuteNonQuery("update Customers set Updated=@1 where id=@0",
                                        1, DateTime.Now);

    Assert.IsTrue(count > -1, data.ErrorMessage);
    Assert.IsTrue(count > 0, "No record found to update");

    Assert.IsTrue(count == 1, "Invalid number of records updated.");
}

InsertEntity: Allows inserting a typed object into a table based on a PK

using (var data = new SqlDataAccess(STR_ConnectionString))
{

    Customer customer = new Customer()
    {
        FirstName = "Mike",
        LastName = "Smith",
        Company = "Smith & Smith",
        Entered = DateTime.UtcNow,
        Updated = DateTime.UtcNow
    };

    // insert into customers and skip Id,Order properties and return id
    object newId = data.InsertEntity(customer, "Customers", "Id,Orders");

    Assert.IsNotNull(newId, data.ErrorMessage);
    Console.WriteLine(newId);
}