SqlDataAccess - A lightweight ADO.NET Data Access Layer

The SqlDataAccess 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 or IEnumerable 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...

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;

For Connection String Configuration from web.config or app.config:

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

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("WebStoreConnection"))
{
    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("WebStoreConnection"))
{
    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);
}

© West Wind Technologies, 2019 • Updated: 09/14/18
Comment or report problem with topic