Sql Helper

    public interface ISqlCommand
    {

        ISqlCommand CommandText(string commandText);
        ISqlCommand Connection(SqlConnection connection);
        ISqlCommand Transaction(SqlTransaction transaction);
        ISqlCommand CommandTimeout(int timeout);
        ISqlCommand CommandType(CommandType commandType);
        ISqlCommand Parameters(Func<ISqlCommandParameter, object> parameter);
        DataSet ToDataSet();
        DataTable ToDataTable();
        DataRow ToDataRow(int rowIndex = 0);
        TResult ToValue<TResult>(int rowIndex = 0, int cellIndex = 0);
        SqlDataReader ExecuteReader(CommandBehavior commandBehavior = CommandBehavior.Default);
        int ExecuteNonQuery();
        object ExecuteScalar();
        TResult ExecuteScalar<TResult>();





    }

    public interface ISqlCommandParameter
    {
        ISqlCommandParameter Add(SqlParameter parameter);
        ISqlCommandParameter Add(string parameterName, object value);
        ISqlCommandParameter Add(string parameterName, SqlDbType sqlDbType);
        ISqlCommandParameter Add(string parameterName, SqlDbType sqlDbType, int size);
        ISqlCommandParameter Add(string parameterName, SqlDbType sqlDbType, int size, string sourceColumn);
        ISqlCommandParameter AddWithValue(string parameterName, object value);

    }

    public class SqlHelper : ISqlCommand, IDisposable
    {

        #region Filds

        public static readonly SqlConnection DefaultConnection = new SqlConnection("");
        private readonly SqlCommand _command = null;
        private SqlConnection _connection = null;
        private SqlTransaction _transaction = null;

        #endregion

        public SqlHelper()
        {
            _connection = DefaultConnection;
            _command = new SqlCommand { Connection = _connection };
        }

        #region ISqlCommand


        public ISqlCommand CommandText(string commandText)
        {
            _command.CommandText = commandText;
            return this;
        }

        public ISqlCommand Connection(SqlConnection connection)
        {
            _connection = connection;
            return this;
        }

        public ISqlCommand Transaction(SqlTransaction transaction)
        {

            _transaction = transaction;
            _command.Transaction = _transaction;
            return this;
        }

        public ISqlCommand CommandTimeout(int timeout)
        {
            _command.CommandTimeout = timeout;
            return this;
        }

        public ISqlCommand CommandType(CommandType commandType)
        {
            _command.CommandType = commandType;
            return this;
        }

        public ISqlCommand Parameters(Func<ISqlCommandParameter, object> parameter)
        {
            SqlCommandParameter param = new SqlCommandParameter(_command);
            parameter.Invoke(param);
            return this;
        }

        public DataSet ToDataSet()
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(_command))
            {
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                return dataSet;
            }
        }

        public DataTable ToDataTable()
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(_command))
            {
                DataTable table = new DataTable();
                adapter.Fill(table);
                adapter.Dispose();
                return table;
            }
        }

        public DataRow ToDataRow(int rowIndex = 0)
        {
            DataTable table = ToDataTable();
            if (table.Rows.Count == 0) return null;
            return table.Rows[rowIndex];
        }

        public TResult ToValue<TResult>(int rowIndex = 0, int cellIndex = 0)
        {
            DataTable table = ToDataTable();
            if (table.Rows.Count == 0) return default(TResult);

            object value = table.Rows[rowIndex][cellIndex];


            if (value is TResult)
            {
                return (TResult)value;
            }
            throw new InvalidCastException();
        }

        public SqlDataReader ExecuteReader(CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            return _command.ExecuteReader(commandBehavior);
        }

        public int ExecuteNonQuery()
        {
            return _command.ExecuteNonQuery();
        }

        public object ExecuteScalar()
        {
            return _command.ExecuteScalar();
        }

        public TResult ExecuteScalar<TResult>()
        {
            object value = _command.ExecuteScalar();
            if (value is TResult)
            {
                return (TResult)value;
            }
            throw new InvalidCastException();
        }

        #endregion

        #region IDisposable

        public void Dispose()
        {
            if (_transaction != null)
            {
                _transaction.Dispose();
            }

            _command.Dispose();
            if (_connection.State != ConnectionState.Open)
            {
                _connection.Close();
            }
            _connection.Dispose();
        }

        #endregion

    }

    public class SqlCommandParameter : ISqlCommandParameter
    {
        private readonly SqlCommand _command;
        public SqlCommandParameter(SqlCommand command)
        {
            _command = command;
        }

        public ISqlCommandParameter Add(SqlParameter parameter)
        {
            _command.Parameters.Add(parameter);
            return this;
        }

        public ISqlCommandParameter Add(string parameterName, object value)
        {
            _command.Parameters.Add(parameterName, value);
            return this;
        }

        public ISqlCommandParameter Add(string parameterName, SqlDbType sqlDbType)
        {
            _command.Parameters.Add(parameterName, sqlDbType);
            return this;
        }

        public ISqlCommandParameter Add(string parameterName, SqlDbType sqlDbType, int size)
        {
            _command.Parameters.Add(parameterName, sqlDbType, size);
            return this;
        }

        public ISqlCommandParameter Add(string parameterName, SqlDbType sqlDbType, int size, string sourceColumn)
        {
            _command.Parameters.Add(parameterName, sqlDbType, size, sourceColumn);
            return this;
        }

        public ISqlCommandParameter AddWithValue(string parameterName, object value)
        {
            _command.Parameters.AddWithValue(parameterName, value);
            return this;
        }
    }