VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > temp > 简明python教程 >

  • C# ORM学习笔记：使用特性+反射实现简单ORM(2)

Manager(Host, GenerationEnvironment, true) { OutputPath = Path.GetDirectoryName(Host.TemplateFile)}; #> <# //System.Diagnostics.Debugger.Launch();//调试 var dbSchema = DBSchemaFactory.GetDBSchema(); List<string> tableList = dbSchema.GetTableList(); foreach (string tableName in tableList) { manager.StartBlock(tableName+".cs"); DataTable table = dbSchema.GetTableMetadata(tableName); //获取主键 string strKey = string.Empty; foreach (DataRow dataRow in table.Rows) { if ((bool)dataRow["ISKEY"] == true) { strKey = dataRow["FIELD_NAME"].ToString(); break; } } #> //------------------------------------------------------------------------------- // 此代码由T4模板MultModelAuto自动生成 // 生成时间 <#= DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") #> // 对此文件的更改可能会导致不正确的行为，并且如果重新生成代码，这些更改将会丢失。 //------------------------------------------------------------------------------- using System; using System.Collections.Generic; using System.Text; using LinkTo.ORM.CustomAttribute; namespace LinkTo.ORM.Model { [DataTable("<#= tableName #>","<#= strKey #>")] [Serializable] public class <#= tableName #> { public <#= tableName #>() { } <# foreach (DataRow dataRow in table.Rows) { //获取数据类型 string dbDataType = dataRow["DATATYPE"].ToString(); string dataType = string.Empty; switch (dbDataType) { case "decimal": case "numeric": case "money": case "smallmoney": dataType = "decimal?"; break; case "char": case "nchar": case "varchar": case "nvarchar": case "text": case "ntext": dataType = "string"; break; case "uniqueidentifier": dataType = "Guid?"; break; case "bit": dataType = "bool?"; break; case "real": dataType = "Single?"; break; case "bigint": dataType = "long?"; break; case "int": dataType = "int?"; break; case "tinyint": case "smallint": dataType = "short?"; break; case "float": dataType = "float?"; break; case "date": case "datetime": case "datetime2": case "smalldatetime": dataType = "DateTime?"; break; case "datetimeoffset ": dataType = "DateTimeOffset?"; break; case "timeSpan ": dataType = "TimeSpan?"; break; case "image": case "binary": case "varbinary": dataType = "byte[]"; break; default: break; } #> [DataField("<#= dataRow["FIELD_NAME"].ToString() #>","<#= dataRow["DATATYPE"].ToString() #>",<#= dataRow["LENGTH"].ToString() #>,<#= dataRow["ISIDENTITY"].ToString().ToLower() #>)] public <#= dataType #> <#= dataRow["FIELD_NAME"].ToString() #> {get; set;} <# } #> } } <# manager.EndBlock(); } dbSchema.Dispose(); manager.Process(true); #>
复制代码

    注：由于ORM拼接SQL时使用的是表特性及字段特性，可以看出表特性上使用的表名、字段特性上使用的字段名，都是与数据库一致的。有了这个保障，数据表生成实体类的时候，类名是可以更改的，因为我只需要保证表特性与数据库一致即可。举个例子，我有个数据表Person_A，在生成实体类时，类名可以生成为Class PersonA {...}，但是表特性依然是[DataTable("Person_A","...")]。相同的原理，属性名也是可以更改的。

    四、ORM实现

    数据表的CURD，主要是通过反射来实现SQL拼接，实现如下：

复制代码
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using LinkTo.ORM.CustomAttribute;

namespace LinkTo.ORM
{
    public static class DBHelper
    {
        public static readonly string ConnectionString = "Server=.;Database=Test;Uid=sa;Pwd=********;";
        private static readonly Hashtable _HashTableName = new Hashtable(); //表名缓存
        private static readonly Hashtable _HashKey = new Hashtable();       //主键缓存

        /// <summary>
        /// 数据库连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetConnection()
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            return conn;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static int Insert<TEntity>(TEntity entity) where TEntity : class
        {
            string strTableName = "";                                   //表名
            string strInsertSQL = "INSERT INTO {0} ({1}) VALUES ({2})"; //SQL拼接语句

            //获取表名
            strTableName = GetTableName(entity);

            //获取字段列表及值列表
            StringBuilder strFields = new StringBuilder();
            StringBuilder strValues = new StringBuilder();
            List<SqlParameter> paraList = new List<SqlParameter>();

            PropertyInfo[] infos = entity.GetType().GetProperties();
            DataFieldAttribute dfAttr = null;
            object[] dfAttrs;

            int i = 0;
            foreach (PropertyInfo info in infos)
            {
                dfAttrs = info.GetCustomAttributes(typeof(DataFieldAttribute), false);
                if (dfAttrs.Length > 0)
                {
                    dfAttr = dfAttrs[0] as DataFieldAttribute;
                    if (dfAttr is DataFieldAttribute)
                    {
                        //自增字段不作处理
                        if (dfAttr.IsIdentity) continue;

                        strFields.Append(i > 0 ? "," + dfAttr.FieldName : dfAttr.FieldName);
                        strValues.Append(i > 0 ? "," + "@" + dfAttr.FieldName : "@" + dfAttr.FieldName);
                        i++;
                        paraList.Add(new SqlParameter("@" + dfAttr.FieldName, info.GetValue(entity, null)));
                    }
                }
            }

            //格式化SQL拼接语句
            string[] args = new string[] { strTableName, strFields.ToString(), strValues.ToString() };
            strInsertSQL = string.Format(strInsertSQL, args);

            //执行结果
            int result = 0;
            try
            {
                using (SqlConnection conn = GetConnection())
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandText = strInsertSQL;
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = conn;
                        if (paraList != null)
                        {
                            foreach (SqlParameter param in paraList)
                            {
                                cmd.Parameters.Add(param);
                            }
                        }
                        result = cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }

            //返回影响行数
            return result;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static int Delete<TEntity>(string condition) where TEntity : class, new()
        {
            string strTableName = "";                           //表名
            string strDeleteSQL = "DELETE FROM {0} WHERE {1}";  //SQL拼接语句

            //获取表名
            strTableName = GetTableName(new TEntity());

            //格式化SQL拼接语句
            string[] args = new string[] { strTableName, condition };
            strDeleteSQL = string.Format(strDeleteSQL, args);

            //执行结果
            int result = 0;
            try
            {
                using (SqlConnection conn = GetConnection())
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandText = strDeleteSQL;
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = conn;
                        result = cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }

            //返回影响行数
            return result;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static int Update<TEntity>(TEntity entity) where TEntity : class
        {
            string strTableName = "";                               //表名
            string strUpdateSQL = "UPDATE {0} SET {1} WHERE {2}";   //SQL拼接语句
            string strKey = "";                                     //主键
            string strWhere = "";                                   //条件

            //获取表名及主键
            strTableName = GetTableName(entity);
            strKey = GetKey(entity);

            //获取更新列表
            StringBuilder strSET = new StringBuilder();
            List<SqlParameter> paraList = new List<SqlParameter>();

            PropertyInfo[] infos = entity.GetType().GetProperties();
            DataFieldAttribute dfAttr = null;
            object[] dfAttrs;

            int i = 0;
            foreach (PropertyInfo info in infos)
            {
                dfAttrs = info.GetCustomAttributes(typeof(DataFieldAttribute), false);
                if (dfAttrs.Length > 0)
                {
                    dfAttr = dfAttrs[0] as DataFieldAttribute;
                    if (dfAttr is DataFieldAttribute)
                    {
                        //条件处理
                        if (dfAttr.FieldName == strKey)
                        {
                            strWhere = strKey + "=" + info.GetValue(entity, null);
                        }

                        //自增字段不作处理
                        if (dfAttr.IsIdentity) continue;

                        strSET.Append(i > 0 ? "," + dfAttr.FieldName + "=@" + dfAttr.FieldName : dfAttr.FieldName + "=@" + dfAttr.FieldName);
                        i++;
                        paraList.Add(new SqlParameter("@" + dfAttr.FieldName, info.GetValue(entity, null)));
                    }
                }
            }

            //格式化SQL拼接语句
            string[] args = new string[] { strTableName, strSET.ToString(), strWhere };
            strUpdateSQL = string.Format(strUpdateSQL, args);

            //执行结果
            int result = 0;
            try
            {
                using (SqlConnection conn = GetConnection())
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandText = strUpdateSQL;
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = conn;
                        if (paraList != null)
                        {
                            foreach (SqlParameter param in paraList)
                            {
                                cmd.Parameters.Add(param);
                            }
                        }
                        result = cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }

            //返回影响行数
            return result;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static List<TEntity> Query<TEntity>(string condition) where TEntity : class, new()
        {
            string strTableName = "";                               //表名
            string strSelectSQL = "SELECT * FROM {0} WHERE {1}";    //SQL拼接语句
            List<TEntity> list = new List<TEntity>();               //实体列表

            //获取表名
            strTableName = GetTableName(new TEntity());

            //格式化SQL拼接语句
            string[] args = new string[] { strTableName, condition };
            strSelectSQL = string.Format(strSelectSQL, args);

            //获取实体列表
            PropertyInfo[] infos = typeof(TEntity).GetProperties();
            DataFieldAttribute dfAttr = null;
            object[] dfAttrs;
            try
            {
                using (SqlConnection conn = GetConnection())
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(strSelectSQL, conn))
                    {
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                TEntity entity = new TEntity();
                                foreach (PropertyInfo info in infos)
                                {
                                    dfAttrs = info.GetCustomAttributes(typeof(DataFieldAttribute), false);
                                    if (dfAttrs.Length > 0)
                                    {
                                        dfAttr = dfAttrs[0] as DataFieldAttribute;

                                        if (dfAttr is DataFieldAttribute)
                                        {
                                            info.SetValue(entity, dr[dfAttr.FieldName]);
                                        }
                                    }
                                }
                                list.Add(entity);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }

            //返回实体列表
            return list;
        }

        /// <summary>
        /// 根据实体返回表名
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static string GetTableName<TEntity>(TEntity entity) where TEntity : class
        {
            Type entityType = entity.GetType();
            string strTableName = Convert.ToString(_HashTableName[entityType.FullName]);

            if (strTableName == "")
            {
                if (entityType.GetCustomAttributes(typeof(DataTableAttribute), false)[0] is DataTableAttribute dtAttr)
                {
                    strTableName = dtAttr.TableName;
                }
                else
                {
                    throw new Exception(entityType.ToString() + "未设置DataTable特性。");
                }

                _HashTableName[entityType.FullName] = strTableName;
            }

            return strTableName;
        }

        /// <summary>

相关教程