Miado - Much-improved ADO.Net
Defines methods for simplifying the use of ADO.Net in a data access layer

Namespace:  Miado
Assembly:  Miado (in Miado.dll) Version: 0.8.10214.0 (0.8.10214.0)

Syntax

C#
public interface IDbStatement
Visual Basic (Declaration)
Public Interface IDbStatement
Visual C++
public interface class IDbStatement

Examples

Most of the methods on this interface return a reference to itself, so you can use the IDbStatement to build up the SQL call in a Domain-Specific-Language sort of way. Assuming the following SQL statements:
CopyC#
private static readonly string INSERT_ADDRESS_SQL = 
    "insert into Address(Address1, Address2, City, State, ZipCode) " +
    "values(@Addr1, @Addr2, @City, @State, @ZipCode)";

private static readonly string SELECT_ADDRESS_SQL = 
    "select AddressId, Address1, Address2, City, " +
    "State, ZipCode " +
    "from Address ";

private static readonly string SELECT_ADDRESS_BY_ID = 
    SELECT_ADDRESS_SQL + "where AddressId = @Id ";

private static readonly string SELECT_ADDRESS_BY_ZIP_CODE =
    SELECT_ADDRESS_SQL + "where ZipCode = @ZipCode ";

private static readonly string SELECT_COUNT_BY_STATE = 
    "select count(*) from Address where State = @State ";
 You can see the IDbStatement in action:
CopyC#
private string _connString = 
    ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

private IDatabase _db = new SqlServerDatabase(_connString);

public IDatabase Database 
{ 
    get { return _db; }
}

public void SaveAddress(Address addr)
{
    this.Database
        .ExecutingSql(INSERT_ADDRESS_SQL)
        .AddParameter("Addr1", addr.Address1)
        .AddParameter("Addr2", addr.Address2)
        .AddParameter("City", addr.City)
        .AddParameter("State", addr.State)
        .AddParameter("ZipCode", addr.ZipCode)
        .ExecuteNonQuery();
}

public IEnumerable<Address> FindAddressesByZipCode(string zipCode) 
{
    IEnumerable<Address> addresses = 
        this.Database
            .ExecutingSql(SELECT_ADDRESS_BY_ZIP_CODE)
            .AddParameter("ZipCode", zipCode)
            .QueryForResults<Address>(
                row => new Address() 
                    {
                        AddressId = row.GetValue<int>("AddressId"), 
                        Address1 = row.GetValue<string>("Address1"),
                        Address2 = row.GetValue<string>("Address2"),
                        City = row.GetValue<string>("City"),
                        State = row.GetValue<string>("State"),
                        ZipCode = row.GetValue<string>("ZipCode")
                    });
    return addresses;
}

public Address LoadAddress(int addrId) 
{
    return 
        this.Database
            .ExecutingSql(SELECT_ADDRESS_BY_ID)
            .AddParameter("Id", addrId)
            .QueryForOne<Address>(
                row => new Address() 
                    {
                        AddressId = row.GetValue<int>("AddressId"), 
                        Address1 = row.GetValue<string>("Address1"),
                        Address2 = row.GetValue<string>("Address2"),
                        City = row.GetValue<string>("City"),
                        State = row.GetValue<string>("State"),
                        ZipCode = row.GetValue<string>("ZipCode")
                    });
}

public int FindCountByState(string state) 
{ 
    return 
        this.Database
            .ExecutingSql(SELECT_COUNT_BY_STATE)
            .AddParameter("State", state)
            .QueryForOne<int>(row => row.GetValue<int>(0));
}
 Instead of using lambda expressions like in the example above, you could also define a method as pass it as a delegate:
CopyC#
private Address CreateAddress(IResultSetRow row) 
{ 
    return new Address() 
        { 
            AddressId = row.GetValue<int>(0)
            // remainder omitted for brevity
        };
}
CopyC#
public IEnumerable<Address> FindAddressesByZipCode(string zipCode) 
{
    return 
        this.Database
            .ExecutingSql(SELECT_ADDRESS_BY_ZIP_CODE)
            .AddParameter("ZipCode", zipCode)
            .QueryForResults<Address>(CreateAddress);
}

See Also