Extending C# to Support SQL Syntax at Compile Time

using System;
using System.Collections;
using System.Data;
using System.Reflection;

//TODO: Row.ToString() for debugging
//TODO: Add support for SQL SP's that return readers

namespace Indy.Data {

	public abstract class View  : IDisposable {

    protected View(Transaction aTx, Type aRowType) : base() {
      _Connection = aTx.Connection;
      if (aRowType == null) {
        Type xType = GetType();
        // Currently only one ancestor is checked
        RowType = xType.Assembly.GetType(xType.FullName + "+Row");
        if (RowType == null) {
          RowType = xType.Assembly.GetType(xType.FullName + "Row");
        }
        if (RowType == null) {
          RowType = xType.Assembly.GetType(xType.BaseType.FullName + "+Row", true);
        }
      } else {
        RowType = aRowType;
      }
    }

    // This[] and also Read() assume that the only fields are DbType and that they are ordered
    // So users should not add others. This does provide some limitations, but really
    // the user should not be expanding the row with other fields anyways
    // and it makes it much easier on the library and the user. Attributes and
    // other flags are not needed for the dynamic queries to come
    //
    // In future, initialize on startup and resolve names etc in a static
    // For now names must be in order and resolve properly.

    public abstract class Row {    
      //TODO: Add a this for strings (name)
      public DbType this[int aCol] { 
        get {
          //TODO: Cache xFields in a static? Overhead associated with calling
          // GetType().GetFields();?
          FieldInfo[] xFields = GetType().GetFields();
          EData.e.If(aCol < 0 | aCol >= xFields.Length, "No such column.");
          return (DbType)xFields[aCol].GetValue(this);
        }
      }      
    }

    public bool LoadBlobs = true;

    // Merged this class with the Row class successfully so that
    // only one needed to be declared. However C# is "biased" towards bools
    // and prematurely converts things to bools, and it didnt work in all
    // scenarios as we need the evals to be both bools and conditions.
    public abstract class Columns { 
      public Columns() : base() {
        FieldInfo[] xFields = GetType().GetFields();
        for (int i = 0; i < xFields.Length; i++) {  
          FieldInfo xField = xFields[i];
          object[] xArgs = {xField.Name};
          Column xCol = (Column)Activator.CreateInstance(xField.FieldType, xArgs);
          xField.SetValue(this, xCol);
        }
      }
    }

    protected readonly Type RowType;
    static public DbNull Null;
    protected abstract string BuildSQL();
    protected Connection _Connection = null;
    protected Row _CurrentRow = null;
    protected IDataReader _DataReader = null;
    protected string _OrderBy = "";
    protected bool _Selected = false;

    private Condition _Where;
    public Condition Where { 
      get {
        return _Where;
      }
      set {
        Close();
        _WhereString = "";
        _Selected = true;
        _Where = value;
      }
    }

    private string _WhereString = "";
    protected string WhereString { 
      get {
        return _WhereString;
      }
      set {
        Close();
        _Where = null;
        _Selected = true;
        _WhereString = value;
      }
    }
    
    protected string WhereClause() {
      if (_Where == null) {
        return _WhereString;
      } else {
        return _Where.ToString();
      }
    }

    #region Enumerator
    public Enumerator GetEnumerator() {
      return new Enumerator(this);
    }

    // this enum is not thread safe or multi enum safe. only one enum per reader
    public class Enumerator {
      private View _View;

      internal Enumerator(View aView) {
        _View = aView;
      }

      public bool MoveNext() {
        _View.Next();
        return _View._CurrentRow != null;
      }

      public object Current {
        get { return _View._CurrentRow; }
      }
    }
    #endregion

    // This is virtual as it can be overridden. I havent run speed tests, but I suspect there is little difference.
    // Here is a sample override that can be created by the class gen
    //
    //    public override void Read(View.Row aRow, IDataReader aReader) {
    //      // Could change this to use .ObjectValue and loop, in fact move this
    //      // up and no need to even use generate code. However this routine
    //      // is used a lot and this explicit form is faster. Have not done
    //      // measured tests.
    //      Row xRow = (Row)aRow;
    //      if (!aReader.IsDBNull(0)) { 
    //        xRow.CustomerID = new DbInt32((int)aReader[0], false); 
    //      } else {
    //        xRow.CustomerID = new DbInt32(false);
    //      }
    //      if (!aReader.IsDBNull(1)) { 
    //        xRow.NameFirst = new DbString((string)aReader[1], false); 
    //      } else {
    //        xRow.NameFirst = new DbString(false);
    //      }
    //      if (!aReader.IsDBNull(2)) { 
    //        xRow.NameLast = new DbString((string)aReader[2], false); 
    //      } else {
    //        xRow.NameLast = new DbString(false);
    //      }
    //      if (!aReader.IsDBNull(3)) { 
    //        xRow.Tag = new DbInt32((int)aReader[3], false); 
    //      } else {
    //        xRow.Tag = new DbInt32(false);
    //      }
    //    }
    public virtual void Read(View.Row aRow, IDataReader aReader) {
      FieldInfo[] xFields = aRow.GetType().GetFields();
      for (int i = 0; i < xFields.Length; i++) {  
        FieldInfo xField = xFields[i];
        // This can be done with reflection
        // However it might be slower, and it certainly will be more complicated
        // This is fine for now...
        if (xField.FieldType == typeof(DbInt32)) {
          if (aReader.IsDBNull(i)) { 
            xField.SetValue(aRow, new DbInt32(false));
          } else {
            xField.SetValue(aRow, new DbInt32((int)aReader[i], false));
          }
        } else if (xField.FieldType == typeof(DbInt16)) {
          if (aReader.IsDBNull(i)) { 
            xField.SetValue(aRow, new DbInt16(false));
          } else {
            xField.SetValue(aRow, new DbInt16((Int16)aReader[i], false));
          }
        } else if (xField.FieldType == typeof(DbInt64)) {
          if (aReader.IsDBNull(i)) { 
            xField.SetValue(aRow, new DbInt64(false));
          } else {
            xField.SetValue(aRow, new DbInt64((Int64)aReader[i], false));
          }
        } else if (xField.FieldType == typeof(DbDecimal)) {
          if (aReader.IsDBNull(i)) { 
            xField.SetValue(aRow, new DbDecimal(false));
          } else {
            xField.SetValue(aRow, new DbDecimal((decimal)aReader[i], false));
          }
        } else if (xField.FieldType == typeof(DbDateTime)) {
          if (aReader.IsDBNull(i)) { 
            xField.SetValue(aRow, new DbDateTime(false));
          } else {
            xField.SetValue(aRow, new DbDateTime((DateTime)aReader[i], false));
          }
        } else if (xField.FieldType == typeof(DbString)) {
          if (aReader.IsDBNull(i)) { 
            xField.SetValue(aRow, new DbString(false));
          } else {
            xField.SetValue(aRow, new DbString((string)aReader[i], false));
          }
        } else if (xField.FieldType == typeof(DbText)) {
          if (aReader.IsDBNull(i)) { 
            xField.SetValue(aRow, new DbText(false));
          } else if (LoadBlobs) {
            xField.SetValue(aRow, new DbText((string)aReader[i], false));
          } else {
            xField.SetValue(aRow, new DbText(aReader, i));
          }
        } else { 
          // Currently it is assumed all fields are DbType. 
          // Should change this to only throw an exception if it descends
          // from DbType, but not for other types
          throw new EData("DbType unrecognized");
        }
      }
    }

    public void Dispose() {
      Close();
    }

    public virtual void Close() {
      if (_DataReader != null) {
        if (!_DataReader.IsClosed) {
          _DataReader.Close();
          }
        _DataReader.Dispose();
        _DataReader = null;
      }
      _Selected = false;
      _WhereString = "";
      _Where = null;
      _OrderBy = "";
      // Dont dispose connection. It may have been passed in by user, and also might be pooled.
    }

    public virtual void Validate(Row aRow) {
    }

    protected void DoValidate(Row aRow) {
      try {
        Validate(aRow);
      } catch (EFailedRowValidation e) {
        EFailedRowValidation.e.Throw(e.Message + "\n", aRow);
      }
    }

    protected void CreateDataReader() {
      EData.e.IfNot(_Selected, "View has not been selected.");
      IDbCommand xCmd = _Connection.PrepareQuery(BuildSQL());
      if (_Where != null) {
        _Where.SetParams(xCmd);
      }
      _DataReader = xCmd.ExecuteReader();
    }

    protected void Next() {
      if(_DataReader == null) {
        CreateDataReader();
      }
      if(!_DataReader.Read()) {
        _CurrentRow = null;
        _DataReader.Close();
      } else {
        Row xRow = (View.Row)Activator.CreateInstance(RowType);
        Read(xRow, _DataReader);
        DoValidate(xRow);
        // Assign only after validation
        _CurrentRow = xRow;
      }
    }

    public Row Read() {
      Next();
      return _CurrentRow;
    }

    public void Select() {
      Where = null;
    }

    public static implicit operator ArrayList(View aView) {
      ArrayList xResult = new ArrayList();
      foreach(Row xRow in aView) {
        xResult.Add(xRow);
      }
      return xResult;
    }

	}
}