操作数据库(MySql)表的扩展方法类，对单表可以进行增、删、改、查。

最近在学ASP.NET MVC的时候，有的时候，要对进行增、删、改、查操作

一开始感觉用自己带的那个 ADO.NET Entity Data Model 来进行增、删，改、查挺方便的

但后来发一个问题，在MYSQL里不能同时在*.edmx打开多个Read操作，SQL里可以设置一个属性，不记得就可以了。

所以就不用它了，由于都是对单表进行操作，没有复杂的操作。

就写一个简单扩展方法

首先有一点就是，自己写的那个类的名称，必须和数据库里表的名称一至

要不能在进行增、删、改、查的时候，就会出问题了。

举个例子吧

//-->查询操作

  var sp = new SortedParam[] { new SortedParam { FieldName = "id", Sorted = SortedType.Desc } };
  user_info ui = new user_info();
  var video = ui.ToList(sp, 30);   //-->获取用户信息，按 id 降序取出表中前三十条记录。

 //-->插入操作

string errorMessage = string.Empty;
var ui = new user_info();
ui.Username = 'aaaa';
ui.Password = 'bbbb';
ui.Type = 1
var result=ui.Insert(null, ref errorMessage);
if(0< result )
{
    //...
}
else
{
    //...
}

//-->删除、修改基本上都差不多。

个人水平有限，如果更好的见记，还望各告诉。

具体的实例还是看看扩展方法。

扩展中使用到的两个类

1、排序用

    /// <summary>
    /// 排序类型
    /// </summary>
    public enum SortedType
    {
        /// <summary>
        /// 升序
        /// </summary>
        Asc,
        /// <summary>
        /// 降序
        /// </summary>
        Desc
    }
    /// <summary>
    /// 排序参数
    /// </summary>
    public class SortedParam
    {
        /// <summary>
        /// 字段名称
        /// </summary>
        public string FieldName { get; set; }
        /// <summary>
        /// 排序类型
        /// </summary>
        public SortedType Sorted { get; set; }
    }

 1、条件用

    /// <summary>
    /// 字段参数
    /// </summary>
    public class FieldParam
    {
        /// <summary>
        /// 字段名称
        /// </summary>
        public string FieldName { get; set; }
        /// <summary>
        /// 字段值
        /// </summary>
        public object FieldValue { get; set; }
        /// <summary>
        /// 字段类型
        /// </summary>
        public Type FieldType { get; set; }
    }

    /// <summary>
    /// 操作数据库单扩展
    /// </summary>
    public static class AccessDBExtend
    {
        /// <summary>
        /// 获取 T 表中记录集合
        /// </summary>
        /// <typeparam name="T">表的Model类</typeparam>
        /// <param name="source">类的变量</param>
        /// <returns>返回IList集合</returns>
        public static IList<T> ToList<T>(this T source) where T : new()
        {
            string table = typeof(T).Name;
            string sqlSentence = string.Format("select * from {0} ", table);
            return DataTableToList<T>(sqlSentence);
        }

        /// <summary>
        /// 获取 T 表中记录集合
        /// </summary>
        /// <typeparam name="T">表的Model类</typeparam>
        /// <param name="source">类的变量</param>
        /// <param name="pageSize">每页的大小</param>
        /// <param name="pageIndex">当前页面索引</param>
        /// <param name="total">当前表中记录总数</param>
        /// <returns>返回IList集合</returns>
        public static IList<T> ToList<T>(this T source, int pageSize, int pageIndex, out int total) where T : new()
        {
            return ToList<T>(source, null, null, pageSize, pageIndex, out total);
        }

        /// <summary>
        /// 获取表的总数
        /// </summary>
        /// <param name="tableName">表的名称</param>
        /// <param name="fields">条件</param>
        /// <returns>返回记录数</returns>
        static int GetTotal(string tableName, FieldParam[] fields)
        {
            string whereCondition = GetCondition(fields);
            string sqlSentece = string.Format(" select count(*) from {0} {1} ", tableName, whereCondition);
            return string.Format("{0}", DBAccess.ExecuteScalar(sqlSentece)).ConvertTo<int>();
        }

        /// <summary>
        /// 获取 T 表中记录集合
        /// </summary>
        /// <typeparam name="T">表的Model类</typeparam>
        /// <param name="source">类的变量</param>
        /// <param name="fields">条件字段</param>
        /// <param name="total">当前表中记录总数</param>
        /// <returns>返回IList集合</returns>
        public static IList<T> ToList<T>(this T source, FieldParam[] fields, out int total) where T : new()
        {
            return ToList<T>(source, fields, null, out total);
        }

        /// <summary>
        /// 获取 T 表中记录集合
        /// </summary>
        /// <typeparam name="T">表的Model类</typeparam>
        /// <param name="source">类的变量</param>
        /// <param name="fields">条件字段</param>
        /// <param name="sorted">排序</param>
        /// <param name="total">当前表中记录总数</param>
        /// <returns>返回IList集合</returns>
        public static IList<T> ToList<T>(this T source, FieldParam[] fields, SortedParam[] sorted, out int total) where T : new()
        {
            string table = typeof(T).Name;
            string sqlSentence = string.Format("select * from {0} {1} {2}", table, GetCondition(fields), GetOrderBy(sorted));
            total = GetTotal(table, fields);
            return DataTableToList<T>(sqlSentence);
        }

        /// <summary>
        /// 获取 T 表中记录集合
        /// </summary>
        /// <typeparam name="T">表的Model类</typeparam>
        /// <param name="source">类的变量</param>
        /// <param name="fields">条件字段</param>
        /// <param name="pageSize">每页的大小</param>
        /// <param name="pageIndex">当前页面索引</param>
        /// <param name="total">当前表中记录总数</param>
        /// <returns>返回IList集合</returns>
        public static IList<T> ToList<T>(this T source, FieldParam[] fields, int pageSize, int pageIndex, out int total) where T : new()
        {
            return ToList<T>(source, fields, null, pageSize, pageIndex, out total);
        }

        /// <summary>
        /// 获取 T 表中记录集合
        /// </summary>
        /// <typeparam name="T">表的Model类</typeparam>
        /// <param name="source">类的变量</param>
        /// <param name="fields">条件字段</param>
        /// <param name="sorted">排序</param>
        /// <param name="pageSize">每页的大小</param>
        /// <param name="pageIndex">当前页面索引</param>
        /// <param name="total">当前表中记录总数</param>
        /// <returns>返回IList集合</returns>
        public static IList<T> ToList<T>(this T source, FieldParam[] fields, SortedParam[] sorted, int pageSize, int pageIndex, out int total) where T : new()
        {
            string table = typeof(T).Name;
            string sqlSentence = GetSqlSentence(table, fields, sorted, pageSize, pageIndex);
            total = GetTotal(table, fields);

            List<String> item = new List<string>();
            item.Add(string.Format("set @count = 0"));
            item.Add(string.Format("set @count = 0"));
            DBAccess.ExecuteNonQuery(item);
            return DataTableToList<T>(sqlSentence);
        }

        /// <summary>
        /// 获取 T 表中记录集合
        /// </summary>
        /// <typeparam name="T">表的Model类</typeparam>
        /// <param name="source">类的变量</param>
        /// <param name="sorted">排序</param>
        /// <param name="pageSize">每页的大小</param>
        /// <param name="pageIndex">当前页面索引</param>
        /// <param name="total">当前表中记录总数</param>
        /// <returns>返回IList集合</returns>
        public static IList<T> ToList<T>(this T source, SortedParam[] sorted, int pageSize, int pageIndex, out int total) where T : new()
        {
            return ToList<T>(source, null, sorted, pageSize, pageIndex, out total);
        }

        /// <summary>
        /// 获取 T 表中记录集合
        /// </summary>
        /// <typeparam name="T">表的Model类</typeparam>
        /// <param name="source">类的变量</param>
        /// <param name="sorted">排序</param>
        /// <param name="topSize">获取表的前几条记录</param>
        /// <returns>返回IList集合</returns>
        public static IList<T> ToList<T>(this T source, SortedParam[] sorted, int topSize) where T : new()
        {
            string table = typeof(T).Name;
            string orderBy = GetOrderBy(sorted);
            string sqlSentence = string.Format("SELECT * from {0} {1} ", table, orderBy);
            if (0 < topSize)
            {
                sqlSentence += string.Format(" LIMIT 0,{0} ", topSize);
            }
            return DataTableToList<T>(sqlSentence);
        }

        /// <summary>
        /// 获取 T 表中记录集合
        /// </summary>
        /// <typeparam name="T">表的Model类</typeparam>
        /// <param name="source">类的变量</param>
        /// <param name="sorted">排序</param>
        /// <returns>返回IList集合</returns>
        public static IList<T> ToList<T>(this T source, SortedParam[] sorted) where T : new()
        {
            return ToList<T>(source,sorted,0);
        }

        /// <summary>
        /// 将DataTable转成 IList 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlSentence"></param>
        /// <returns></returns>
        static IList<T> DataTableToList<T>(string sqlSentence) where T : new()
        {
            var dt = Database.DBAccess.GetDataTable(sqlSentence);
            if (null == dt)
            {
                return null;
            }
            return dt.ToList<T>();
        }

        static string GetSqlSentence(string tableName, FieldParam[] fields, SortedParam[] sorted, int pageSize, int pageIndex)
        {
            string _orderBy = GetOrderBy(sorted);
            string sql = string.Format("select * from (");
            sql += string.Format("	SELECT (@count := @count +1) SerialNumber, t.* from {0} t {1} {2}", tableName, GetCondition(fields), _orderBy);

            if (0 < pageSize)
            {
                sql += string.Format(" LIMIT 0,{0} ", pageSize * (pageIndex));
            }
            sql += string.Format(") v {0}", _orderBy);

            if (0 < pageSize)
            {
                sql += string.Format(" LIMIT {0},{1}", pageSize * (pageIndex - 1), pageSize * (pageIndex));
            }
            return sql;
        }

        static string GetCondition(FieldParam[] fields)
        {
            if (null == fields || 0 == fields.Length)
            {
                return null;
            }
            string whereCondtion =
             string.Format(" where {0}",
                    string.Join(" and ",
                                        from p in fields
                                        select string.Format("{0} = '{1}'", p.FieldName, p.FieldValue)));

            return whereCondtion;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fields"></param>
        /// <returns></returns>
        static string GetOrderBy(SortedParam[] fields)
        {
            if (null == fields || 0 == fields.Length)
            {
                return null;
            }
            string whereCondtion =
             string.Format(" order by {0}",
                    string.Join(" and ",
                                        from p in fields
                                        select string.Format("{0} {1}", p.FieldName, p.Sorted)));

            return whereCondtion;
        }

        /// <summary>
        /// 获取单条记录
        /// </summary>
        /// <typeparam name="T">表的Model类</typeparam>
        /// <param name="source">类的变量</param>
        /// <param name="fields">条件字段</param>
        /// <returns>返回model类</returns>
        public static T ToModel<T>(this T source, FieldParam[] fields) where T : new()
        {
            string table = typeof(T).Name;
            string sqlSentence = string.Format("select * from {0} {1}", table, GetCondition(fields));
            var dt = Database.DBAccess.GetDataTable(sqlSentence);
            return dt.FirstModel<T>();
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        /// <typeparam name="T">表的Model类</typeparam>
        /// <param name="source">类的变量</param>
        /// <param name="fields">条件字段</param>
        /// <returns>是否成功 , >0成功；否则失败。</returns>
        public static int Delete<T>(this  T Source, FieldParam[] fields, ref string errorMessage)
        {
            try
            {
                string tableName = typeof(T).Name;
                string sqlSentence = string.Format("delete from {0} {1} ", tableName, GetCondition(fields));
                var result = DBAccess.ExecuteNonQuery(sqlSentence);
                errorMessage = DBAccess.ErrorMessage;
                return result;
            }
            catch (Exception ee)
            {
                errorMessage = ee.Message;
                return -1;
            }
        }

        /// <summary>
        /// 向表中插入记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="source"></param>
        /// <param name="fields">不用进行插入的字符集合</param>
        /// <param name="errorMessage"></param>
        /// <returns></returns>
        public static int Insert<T>(this T source, FieldParam[] fields, ref string errorMessage)
        {
            try
            {
                var tt = typeof(T);
                string sqlSentence = GetInsertSentence<T>(source, fields);
                var result = DBAccess.ExecuteNonQuery(sqlSentence);
                errorMessage = DBAccess.ErrorMessage;
                return result;
            }
            catch (Exception ee)
            {
                errorMessage = ee.Message;
                return -1;
            }
        }

        /// <summary>
        /// 编辑，先删除，然后再进行插入操作，在一个事务里处理的【 0 成功；其它失败 】
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Source"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static int Edit<T>(this T Source, FieldParam[] fields, ref string errorMessage)
        {
            try
            {
                string tableName = typeof(T).Name;
                List<String> item = new List<string>();
                string sqlSentence = string.Format("delete from {0} {1} ", tableName, GetCondition(fields));
                item.Add(sqlSentence);
                sqlSentence = GetInsertSentence<T>(Source, null);
                item.Add(sqlSentence);

                var result = DBAccess.ExecuteNonQuery(item);
                errorMessage = DBAccess.ErrorMessage;
                return result;
            }
            catch (Exception ee)
            {
                errorMessage = ee.Message;
                return -1;
            }
        }

        /// <summary>
        /// 获取插入的SQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="source"></param>
        /// <param name="fields">不用插入的字符</param>
        /// <returns></returns>
        public static string GetInsertSentence<T>(this T source, FieldParam[] fields)
        {
            var tt = typeof(T);
            List<String> fieldNames = new List<String>();
            List<String> fieldValues = new List<String>();
            var piItem = tt.GetProperties();
            foreach (var v in piItem)
            {
                if (v.Name.ToLower().Equals("SerialNumber".ToLower()))
                {
                    continue;
                }
                //-->判断是否存在fields
                if (null != fields && 0 < fields.Length)
                {
                    var fp = fields.Where(p => p.FieldName.ToLower().Equals(v.Name.ToLower())).GetFirst<FieldParam>();
                    if (null != fp)
                    {
                        continue;
                    }
                }
                fieldNames.Add(v.Name);
                fieldValues.Add(GetPropertyValue(v, source));
            }

            string sql = string.Format("insert into {0} ({1}) values ({2}) ",
                tt.Name,
                string.Join(",", fieldNames),
                string.Join(",", fieldValues));

            return sql;
        }

        /// <summary>
        /// 获取属性的值
        /// </summary>
        /// <param name="pi"></param>
        /// <param name="source"></param>
        /// <returns></returns>
        static string GetPropertyValue(PropertyInfo pi, object source)
        {
            object value = pi.GetValue(source, null);
            string result = string.Format(" '{0}' ", value);
            switch (pi.PropertyType.FullName)
            {
                case "int":
                case "int16":
                case "int32":
                case "int64":
                case "System.UInt16":
                case "System.UInt32":
                case "System.UInt64":
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                case "System.Single":
                case "System.Double":
                case "System.decimal":
                    result = string.Format(" {0} ", value);
                    break;
                case "System.String":
                    value = Wrapper.StringReplace(string.Format("{0}", value));
                    result = string.Format(" '{0}' ", value);
                    break;
                case "System.DateTime":
                    var dt = Convert.ToDateTime(value);
                    result = string.Format(" STR_TO_DATE('{0}','%Y-%m-%d %H:%i:%s') ", dt.ToString("yyyy-MM-dd HH:mm:ss"));
                    break;
            }
            return result;
        }
    }