Project Description
Small wrapper for classes SqlConnection and SqlCommand to execute queries and stored procedures in Sql Server.

Example

Step 0: Add connection string to app.config or web.config file.

<connectionStrings>
  <add name="Northwind" connectionString="..." />
</connectionStrings>

Step 1: Create instance of SqlClient class.

public static class DB
{
    public static readonly SqlClient Northwind = new SqlClient("Northwind");
}

Step 2: Create models.

public sealed class Category
{
    public int ID { get; set; }
    public string Name { get; set; }

    public static Category Mapper(SqlDataReader reader)
    {
        return new Category
        {
            ID = (int) reader["CategoryID"],
            Name = (string) reader["CategoryName"],
        };
    }
}

public sealed class Product
{
    public int ID { get; set; }
    public string Name { get; set; }

    public static Product Mapper(SqlDataReader reader)
    {
        return new Product
        {
            ID = (int) reader["ProductID"],
            Name = (string) reader["ProductName"],
        };
    }
}

public sealed class CategoryAndProducts
{
    public Category Category { get; set; }
    public List<Product> Products { get; set; }

    public static CategoryAndProducts Mapper(SqlDataReader reader)
    {
        var result = new CategoryAndProducts();

        result.Category = SqlUtility.ReadObject(reader, Category.Mapper);
        reader.NextResult();
        result.Products = SqlUtility.ReadObjects(reader, Product.Mapper);

        return result;
    }
}

Step 3: Use instance of SqlClient class to execute queries and stored procedures.

// Without result.
DB.Northwind.StoredProcedure("SP_DoSomething")
    .WithParameter("Parameter1", "Hello, World!")
    .WithParameter("Parameter2", 42)
    .WithParameter("Parameter3", 3.14)
    .WithParameter("Parameter4", DateTime.Now)
    .WithParameter("Parameter2", null /* will be used DBNull.Value */)
    .Execute();

// Getting single object.
var category = DB.Northwind
    .Query("select * from [Categories] where [CategoryID] = @CategoryID")
    .WithParameter("CategoryID", categoryId)
    .ExecuteObject(Category.Mapper);

// Getting multiple objects.
var products = DB.Northwind
    .Query("select * from [Products] where [CategoryID] = @CategoryID")
    .WithParameter("CategoryID", categoryId)
    .ExecuteObjects(Product.Mapper);

// Getting multiple objects from multiple result sets.
var categoryAndProducts = DB.Northwind
    .Query(@"
        select * from [Categories] where CategoryID = @CategoryID
        select * from [Products] where CategoryID = @CategoryID")
    .WithParameter("CategoryID", categoryId)
    .ExecuteResult(CategoryAndProducts.Mapper);

Last edited Jan 1 at 11:57 AM by ordos, version 5