SqlDataAccess - An 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<T> 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...

A few simple examples

ExecuteReader:

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<T>:

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<T>:

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:

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

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, 1996-2016 • Updated: 12/19/15
Comment or report problem with topic