C# on rails! 基本信息CRUD操作的简单实现

     在信息系统的开发中，经常需要对大量的基本信息开发“添加，更新，显示，删除”操作，针对这些功能去写相应的SQL语句的工作是相当的繁琐和易出错的，而使用ORM组件的话，又要进行复杂的配置和学习，并且在实现业务功能的时候，这些组件的对应方式也是相当复杂和有难度的。
    在实际的开发中，想把问题一分为二，基本信息的维护（CRUD）操作使用自己实现的简单ORM类来进行解决，复杂的业务处理还是使用SQL更为便捷。在下面窝列出了我所使用的简单的ORM类的源程序和相应的例子，希望能起到抛砖引玉的效果。
    说明： Controller中实现了大部分数据操作的方法，从Control继承的其他类(如BrandController)则可以简单的通过Save，Delete，findAll方法处理数据。

//Controller.cs
////////////////////////////////////////////////////////////
using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using logic.Properties;
using System.Reflection;
using System.Collections;

namespace logic
{
    public abstract class Controller
    {
        private IDbConnection m_conn = null;
        private DataTable m_data = null;
        private string m_keys = "";

        public abstract string TableName{get;}
        public abstract Type ModelType { get;}

        #region Session Open/Close
        public virtual bool openSession()
        {
            if (m_conn == null)
            {
                Settings settings = new Settings();
                m_conn = new OleDbConnection(settings.esaleConnectionString);
            }

            if (m_conn.State == ConnectionState.Open)
            {
                return true;
            }

            try
            {
                m_conn.Open();
            }
            catch
            {
                return false;
            }
 
            return true;
        }

        public void closeSession()
        {
            if (m_conn != null)
            {
                m_conn.Close();
                m_conn = null;
            }
        }
        #endregion

        #region find all rows
        public DataTable findAll()
        {
            return findAll(null, null, null);
        }

        public DataTable findAll(string condition)
        {
            return findAll(condition, null, null);
        }

        public DataTable findAll(string condition, string fields)
        {
            return findAll(condition, fields, null);
        }

        public DataTable findAll(string condition, string fields, string order)
        {
            if (ModelType == null || TableName == null)
            {
                return null;
            }

            string sql = "SELECT";

            if (fields != null && fields.Length > 0)
            {
                sql += " " + fields;
            }
            else
            {
                sql += " *";
            }

            sql += " FROM " + TableName;

            if (condition != null && condition.Length > 0)
            {
                sql += " WHERE " + condition;
            }

            if (order != null && order.Length > 0)
            {
                sql += " ORDER BY " + order;
            }

            return query(sql);
        }
        #endregion

        #region find a object
        public object find()
        {
            return find(null, null, null);
        }

        public object find(string condition)
        {
            return find(condition, null, null);
        }

        public object find(string condition, string fields)
        {
            return find(condition, fields, null);
        }

        public object find(string condition, string fields, string order)
        {
            DataTable dt = findAll(condition, fields, order);

            if (dt.Rows.Count == 0)
            {
                return null;
            }

            return CreateObject(dt.Rows[0]);
        }
        #endregion

        #region find rows count
        public int findCount(string condition)
        {
            string sql = "SELECT COUNT('X') FROM " + TableName;

            if (condition != null && condition.Length > 0)
            {
                sql += " WHERE " + condition;
            }

            object ret = scalar(sql);
            return ret == null || ret == DBNull.Value ? 0 : int.Parse(ret.ToString());
        }

        public int findCount()
        {
            return findCount(null);
        }
        #endregion

        #region save a record
        public int save(object entity)
        {
            bool updateData = true;
            string condition = "";

            if (entity == null)
            {
                return -2;
            }

            object val = getPropertyValue(entity, "Id");

            if (val == null || val == DBNull.Value)
            {
                updateData = false;
            }
            else
            {
                condition = "id = " + val.ToString();

                if (findCount(condition) == 0)
                {
                    updateData = false;
                }
            }

            return updateData ? update(entity, condition) : insert(entity);
        }

        public int insert(object entity)
        {
            if (entity == null)
            {
                return -2;
            }

            string sql = "INSERT INTO [" + TableName + "](";
            string value = " VALUES(";
            object val;

            if (!prepareSchema())
            {
                return -1;
            }

            foreach (DataColumn dc in m_data.Columns)
            {
                val = getPropertyValue(entity, windowsName(dc.ColumnName));

                if (! dc.AutoIncrement)
                {
                    sql += "[" + dc.ColumnName + "],";

                    if (isChar(dc))
                    {
                        value += "'" + (val == null || val == DBNull.Value? "" : val.ToString()) + "',";
                    }
                    else
                    {
                        value += (val == null || val == DBNull.Value) ? "0" : val.ToString() + ",";
                    }
                }
            }

            sql = sql.TrimEnd(',');
            value = value.TrimEnd(',');
            sql += ")" + value + ")";
            return execute(sql);
        }

        public int update(object entity, string condition)
        {
            if (entity == null)
            {
                return -2;
            }

            string sql = "UPDATE [" + TableName + "] SET ";
            object val;

            if (!prepareSchema())
            {
                return -1;
            }

            foreach (DataColumn dc in m_data.Columns)
            {
                if (m_keys.IndexOf(dc.ColumnName) >= 0)
                {
                    continue;
                }

                val = getPropertyValue(entity, windowsName(dc.ColumnName));

                if (val != null && val != DBNull.Value)
                {
                    sql += "[" + dc.ColumnName + "]=";

                    if (isChar(dc))
                    {
                        sql += "'" + val.ToString() + "',";
                    }
                    else
                    {
                        sql += val.ToString() + ",";
                    }
                }
            }

            sql = sql.TrimEnd(',');
            sql += " WHERE " + condition;
            return execute(sql);
        }
        #endregion

        #region delete a record
        public int delete(object entity)
        {
            if (entity == null)
            {
                return -2;
            }

            object val = getPropertyValue(entity, "Id");
            string sql = "DELETE FROM [" + TableName + "] WHERE id = " + val.ToString();
            return execute(sql);
        }
        #endregion

        #region basic DB operation
        public virtual DataTable query(string sql)
        {
            return query(sql, CommandType.Text);
        }

        public virtual DataTable query(string sql, CommandType cmdType)
        {
            if (!openSession())
            {
                return null;
            }

            IDbCommand cmd = new OleDbCommand(sql);
            cmd.Connection = m_conn;
            cmd.CommandType = cmdType;
            DataTable dt = new DataTable("Data");
            IDataReader reader = null;

            try
            {
                reader = cmd.ExecuteReader();
            }
            catch
            {
                closeSession();
                return null;
            }

            dt.Load(reader);
            reader.Close();
            closeSession();
            return dt;
        }

        public virtual int execute(string sql)
        {
            int ret = -1;

            if (!openSession())
            {
                return ret;
            }

            IDbCommand cmd = new OleDbCommand(sql);
            cmd.Connection = m_conn;

            try
            {
                ret = cmd.ExecuteNonQuery();
            }
            catch
            {
            }

            closeSession();
            return ret;
        }

        public virtual object scalar(string sql)
        {
            object ret = null;

            if (!openSession())
            {
                return ret;
            }

            IDbCommand cmd = new OleDbCommand(sql);
            cmd.Connection = m_conn;

            try
            {
                ret = cmd.ExecuteScalar();
            }
            catch
            {
            }

            closeSession();
            return ret;
        }
        #endregion

        #region Util function
        public object CreateObject(DataRow dr)
        {
            Assembly assembly = ModelType.Assembly;
            object model = assembly.CreateInstance(ModelType.ToString());
            object val;

            foreach (PropertyInfo pi in ModelType.GetProperties())
            {
                if (pi.CanWrite)
                {
                    val = dr[unixName(pi.Name)];

                    if (val != null && val != DBNull.Value)
                    {
                        pi.SetValue(model, val, null);
                    }
                }
            }

            return model;
        }

        protected bool prepareSchema()
        {
            if (m_data != null)
            {
                return true ;
            }

            if (!openSession())
            {
                return false;
            }

            string sql = "SELECT * FROM " + TableName + " WHERE 1 = 0";
            IDataAdapter da = new OleDbDataAdapter(sql, (OleDbConnection)m_conn);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Mapped);
            m_data = ds.Tables["Table"];

            foreach (DataColumn dc in m_data.PrimaryKey)
            {
                m_keys += dc.ColumnName + ",";
            }

            m_keys.TrimEnd(',');

            return true;
        }

        public string windowsName(string str)
        {
            if (str.Length <= 1)
            {
                return str.ToUpper();
            }

            string temp = "";
            bool toUpper = true;

            foreach (char ch in str)
            {
                if (ch == '_')
                {
                    toUpper = true;
                    continue;
                }

                if (toUpper)
                {
                    temp += ch.ToString().ToUpper();
                }
                else
                {
                    temp += ch;
                }

                toUpper = false;
            }

            return temp;
        }

        public string unixName(string str)
        {
            if (str.Length <= 1)
            {
                return str.ToLower();
            }
           
            string temp = "";
            short pos = 0;

            foreach (char ch in str)
            {
                if (ch >= 'A' && ch <= 'Z' && pos > 0)
                {
                    temp += "_";
                }

                temp += ch;
                ++pos;
            }

            return temp.ToLower();
        }

        public object getPropertyValue(object entity, string name)
        {
            PropertyInfo pi = ModelType.GetProperty(name);

            if (pi != null)
            {
                return pi.GetValue(entity, null);
            }
            else
            {
                return null;
            }
        }

        public bool isChar(DataColumn dc)
        {
            switch (dc.DataType.Name.ToLower())
            {
                case "datetime":
                case "string":
                    return true;
                case "int32":
                case "int64":
                    return false;
                default:
                    return true;
            }
        }
        #endregion
    }
       
}

// BrandController.cs
//////////////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using domain;

namespace logic
{
    public class BrandsController : Controller
    {
        public override string TableName
        {
            get { return "brands"; }
        }

        public override Type ModelType
        {
            get { return typeof(Brand); }
        }
    }
}
// Brand.cs
///////////////////////////
using System;

namespace domain
{
    /// <summary>
    ///   
    /// </summary>
    public sealed class Brand
    {
        #region Private Members
        private int m_id;
        private string m_name;
        private string m_image;        
        #endregion
       
        #region Default ( Empty ) Class Constuctor
        /// <summary>
        /// default constructor
        /// </summary>
        public Brand()
        {
            m_id = 0;
            m_name = String.Empty;
            m_image = String.Empty;
        }
        #endregion // End of Default ( Empty ) Class Constuctor
       
        #region Public Properties
           
        /// <summary>
        ///
        /// </summary>       
        public int Id
        {
            get { return m_id; }
            set { m_id = value; }
        }
           
        /// <summary>
        ///
        /// </summary>       
        public string Name
        {
            get { return m_name; }
            set   
            {
                if( value.Length > 40)
                    throw new ArgumentOutOfRangeException("Invalid value for Name", value, value.ToString());
               
                m_name = value;
            }
        }
           
        /// <summary>
        ///
        /// </summary>       
        public string Image
        {
            get { return m_image; }
            set   
            {
                if( value.Length > 80)
                    throw new ArgumentOutOfRangeException("Invalid value for Image", value, value.ToString());
               
                m_image = value;
            }
        }
           
        #endregion
       
        #region Equals And HashCode Overrides
        /// <summary>
        /// local implementation of Equals based on unique value members
        /// </summary>
        public override bool Equals( object obj )
        {
            if( this == obj ) return true;
            if( ( obj == null ) || ( obj.GetType() != this.GetType() ) ) return false;
            Brand castObj = (Brand)obj;
            return ( castObj != null ) &&
                ( this.m_id == castObj.Id );
        }
       
        /// <summary>
        /// local implementation of GetHashCode based on unique value members
        /// </summary>
        public override int GetHashCode()
        {
           
            int hash = 57;
            hash = 27 * hash * m_id.GetHashCode();
            return hash;
        }
        #endregion
       
    }
}

// TestBrand.cs
////////////////////////////////////////

BrandController brandCtrl = new BrandController();
Brand brand = new Brand();
brand.Name = "Test";
brand.Image = filePath;
brandCtrl.save(brand);