using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

​

public class QueryResult

{

    public bool IsSuccessful { get; set; }

}

​

public class RetrieveResult<T> : QueryResult

{

    public T Value { get; set; }    

}

​

public abstract class BaseDataAccess

{

    private static string ConnectionString { get { return "ConnectionString"; } }

    private T WithCommand<T>( string text, IEnumerable<SqlParameter> parameters, Func<SqlCommand,T> operation )

    {

        using( var cn = new SqlConnection( ConnectionString ) )

        using( var cmd = new SqlCommand( text, cn ) )

        {

            if( parameters != null && parameters.Any() )

                cmd.Parameters.AddRange( parameters.ToArray() );

            return operation( cmd );

        }

    }

    private void WithCommand( string text, IEnumerable<SqlParameter> parameters, Action<SqlCommand> operation )

    {

        using( var cn = new SqlConnection( ConnectionString ) )

        using( var cmd = new SqlCommand( text, cn ) )

        {

            if( parameters != null && parameters.Any() )

                cmd.Parameters.AddRange( parameters.ToArray() );

            operation( cmd );

        }

    }

    private IList<DataRow> GetRows( SqlCommand cmd )

    {

        using( var dr = cmd.ExecuteReader() )

        using( var da = new SqlDataAdapter() )

        {

            if( !dr.HasRows ) return new List<DataRow>();

            var dt = new DataTable();

            da.Fill( dt );

            return dt.Rows.Cast<DataRow>().ToList();

        }

    }

    protected RetrieveResult<IList<T>> GetCollection<T>( string query, IEnumerable<SqlParameter> parameters, Func<DataRow, T> builder )

    {

        var result = new RetrieveResult<IList<T>>() { IsSuccessful = false };

        try

        {

            var rows = WithCommand( query, parameters, cmd => GetRows( cmd ) );

            result.Value = rows.Select( row => builder( row ) ).ToList();

            result.IsSuccessful = true;

        }

        catch

        {

            // it is generally not good to ignore exceptions.

            // maybe put some kind of error message into the QueryResult

        }

        return result;

    }

    protected RetrieveResult<T> GetSingle<T>( string query, IEnumerable<SqlParameter> parameters, Func<DataRow, T> builder )

    {

        var result = new RetrieveResult<T>() { IsSuccessful = false };

        try

        {

            var row = WithCommand( query, parameters, cmd => GetRows( cmd ) ).Single();

            result.Value = builder( row );

            result.IsSuccessful = true;

        }

        catch

        {

            // it is generally not good to ignore exceptions.

            // maybe put some kind of error message into the QueryResult

        }

        return result;

    }

    protected QueryResult ExecuteCommand( string query, IEnumerable<SqlParameter> parameters )

    {

        var result = new QueryResult { IsSuccessful = false };

        try

        {

            WithCommand( query, parameters, cmd => {} );

            result.IsSuccessful = true;

        }

        catch

        {

            // it is generally not good to ignore exceptions.

            // maybe put some kind of error message into the QueryResult

        }

        return result;

    }

}

​

public class stockItem

{

    public string name { get; set; }

    public double price { get; set; }

    public string size { get; set; }

}

​

public class StockDataAccess : BaseDataAccess

{

    public RetrieveResult<stockItem> GetStockRecord( int stockItemNumber )

    {

        var query = "SELECT name, size, price FROM dbo.products WHERE itemnumber = @itemNumber";

        var parameters = new []

        {

            new SqlParameter("@itemNumber", SqlDbType.Int) { Value = stockItemNumber } 

        };

        var result = GetSingle( query, parameters,

            row => new stockItem

            {

                name = row[ "name" ] as string,

                price = (double)row[ "price" ],

                size = row[ "size" ] as string

            });

        return result;

    }

}

​

public class Program

{

    public static void Main()

    {

        var stockDa = new StockDataAccess();

        var stockItem = stockDa.GetStockRecord( 1 );

        if( stockItem.IsSuccessful )

        {

            // blah blah blah

        }

    }

}