T4生成实体和简单的CRUD操作

主要跟大家交流下T4,我这里针对的是mysql,我本人比较喜欢用mysql,所以语法针对mysql,所以你要准备mysql的DLL了,同理sqlserver差不多,有兴趣可以自己写写,首先网上找了一个T4的帮助类,得到一些数据库属性,命名为 DbHelper.ttinclude

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ assembly name="System.Core.dll" #>
<#@ assembly name="System.Data.dll" #>
<#@ assembly name="System.Data.DataSetExtensions.dll" #>
<#@ assembly name="System.Xml.dll" #>
<#@ assembly name="MySql.Data" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Data" #>
<#@ import namespace=" System.Data.SqlClient" #>
<#@ import namespace="MySql.Data.MySqlClient" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text.RegularExpressions" #><#+

    #region GetDbTables
    public class DbHelper
    {

        #region 去下划线,转大写
        public static string ToSplitFirstUpper(string file)
        {
            string[] words = file.Split('_');
            StringBuilder firstUpperWorld = new StringBuilder();
            foreach (string word in words)
            {
                string firstUpper = ToFirstUpper(word);
                firstUpperWorld.Append(firstUpper);
            }
            string firstUpperFile = firstUpperWorld.ToString().TrimEnd(new char[] { '_' });
            return firstUpperFile;
        }

        // 将字符串设置成首字母大写
        public static string ToFirstUpper(string field)
        {
            string first = field.Substring(0, 1).ToUpperInvariant();
            string result = first;
            if (field.Length > 1)
            {
                string after = field.Substring(1);
                result = first + after;
            }
            return result;
        }

        #endregion

        #region 生成简单的sql语句
        public static string GetInsertSql(string connectionString, string database, string tableName)
        {
            var list = GetDbColumns(connectionString, database, tableName);
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            foreach (var item in list)
            {
                string field = item.Field;
                if (field.ToLower() == "id") continue;
                sb1.Append(field).Append(", ");
                sb2.Append("?").Append(field).Append(", ");
            }
            string s1 = sb1.ToString().Trim(new char[] { ',', ' ' });
            string s2 = sb2.ToString().Trim(new char[] { ',', ' ' });
            return string.Format("INSERT INTO {0}({1}) VALUES({2})", tableName, s1, s2);

        }

        public static string GetParameter(string connectionString, string database, string tableName, bool hasId)
        {
            var list = GetDbColumns(connectionString, database, tableName);
            StringBuilder sb = new StringBuilder();
            sb.Append("MySqlParameter[] paras = new MySqlParameter[] { \r\n");
            foreach (var item in list)
            {
                if (item.Field.ToLower() == "id" && !hasId) continue;
                sb.AppendFormat("                 new MySqlParameter(\"{0}\", this.{1}),\r\n", item.Field, ToSplitFirstUpper(item.Field));
            }
            string s = sb.ToString().Trim(new char[] { ',', ' ', '\r', '\n' });
            s = s + "\r\n            };\r\n";
            return s;
        }

        public static string GetUpdateSql(string connectionString, string database, string tableName)
        {
            var list = GetDbColumns(connectionString, database, tableName);
            StringBuilder sb1 = new StringBuilder();
            foreach (var item in list)
            {
                string field = item.Field;
                if (field.ToLower() == "id") continue;
                sb1.Append(field).Append(" = ").Append("?").Append(field).Append(", ");


            }
            string s1 = sb1.ToString().Trim(new char[] { ',', ' ' });
            return string.Format("UPDATE {0} SET {1} WHERE id = ?id", tableName, s1);

        }

        #endregion

        #region GetDbTables

        public static List<DbTable> GetDbTables(string connectionString, string database)
        {

            #region SQL
            string sql = string.Format("SHOW TABLE STATUS FROM {0};", database);
            #endregion
            DataTable dt = GetDataTable(connectionString, sql);
            return dt.Rows.Cast<DataRow>().Select(row => new DbTable
            {
                TableName = row.Field<string>("Name"),
                Rows = row.Field<UInt64>("Rows"),
                Comment = row.Field<string>("Comment")
            }).ToList();
        }
        #endregion

        #region GetDbColumns

        public static List<DbColumn> GetDbColumns(string connectionString, string database, string tableName)
        {
            #region SQL
            string sql = string.Format("SHOW FULL COLUMNS FROM {0} FROM {1};", tableName, database);
            #endregion
            DataTable dt = GetDataTable(connectionString, sql);
            return dt.Rows.Cast<DataRow>().Select(row => new DbColumn
            {
                IsPrimaryKey = !String.IsNullOrEmpty(row.Field<string>("Key")),
                Field = row.Field<string>("Field"),
                Type = row.Field<string>("Type"),
                Comment = row.Field<string>("Comment"),
                IsNullable = row.Field<string>("NULL") == "YES"
            }).ToList();
        }

        #endregion


        #region GetDataTable

        public static DataTable GetDataTable(string connectionString, string commandText, params SqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = commandText;
                command.Parameters.AddRange(parms);
                MySqlDataAdapter adapter = new MySqlDataAdapter(command);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return dt;
            }
        }

        #endregion
    }
    #endregion

    #region DbTable
    /// <summary>
    /// 表结构
    /// </summary>
    public sealed class DbTable
    {
        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName { get; set; }

        /// <summary>
        /// 行数
        /// </summary>
        public UInt64 Rows { get; set; }

        /// <summary>
        /// 描述信息
        /// </summary>
        public string Comment { get; set; }
    }
    #endregion

    #region DbColumn
    /// <summary>
    /// 表字段结构
    /// </summary>
    public sealed class DbColumn
    {
        /// <summary>
        /// 是否主键
        /// </summary>
        public bool IsPrimaryKey { get; set; }
        /// <summary>
        /// 字段名称
        /// </summary>
        public string Field { get; set; }
        /// <summary>
        /// 字段类型 int(11)
        /// </summary>
        public string Type { get; set; }
        /// <summary>
        /// 字段类型int
        /// </summary>
        public string ColumnType
        {
            get
            {
                return Type.IndexOf('(') == -1 ? Type : Type.Substring(0, Type.IndexOf('('));
            }
        }
        /// <summary>
        /// 数据库类型对应的C#类型
        /// </summary>
        public string CSharpType
        {
            get
            {
                return MysqlDbTypeMap.MapCsharpType(ColumnType);
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public Type CommonType
        {
            get
            {
                return MysqlDbTypeMap.MapCommonType(ColumnType);
            }
        }
        /// <summary>
        /// 描述
        /// </summary>
        public string Comment { get; set; }
        /// <summary>
        /// 是否允许空
        /// </summary>
        public bool IsNullable { get; set; }

        /// <summary>
        /// 字符长度
        /// </summary>
        public int CharLength
        {
            get
            {
                Regex regex = new Regex(@"(?<=\()\d*?(?=\))", RegexOptions.Singleline);
                if (regex.IsMatch(Type))
                {
                    Match match = regex.Match(Type);
                    while (match != null && match.Success)
                    {
                        int charLength;
                        if (Int32.TryParse(match.Value, out charLength))
                        {
                            return charLength;
                        }
                    }
                }
                return 0;
            }
        }
    }
    #endregion

    #region SqlServerDbTypeMap

    public class MysqlDbTypeMap
    {
        public static string MapCsharpType(string dbtype)
        {
            if (string.IsNullOrEmpty(dbtype)) return dbtype;
            dbtype = dbtype.ToLower();
            string csharpType = "object";
            switch (dbtype)
            {
                case "bigint": csharpType = "long"; break;
                case "binary": csharpType = "byte[]"; break;
                case "bit": csharpType = "bool"; break;
                case "char": csharpType = "string"; break;
                case "date": csharpType = "DateTime"; break;
                case "datetime": csharpType = "DateTime"; break;
                case "datetime2": csharpType = "DateTime"; break;
                case "datetimeoffset": csharpType = "DateTimeOffset"; break;
                case "dityint": csharpType = "bool"; break;
                case "decimal": csharpType = "decimal"; break;
                case "float": csharpType = "double"; break;
                case "image": csharpType = "byte[]"; break;
                case "int": csharpType = "int"; break;
                case "money": csharpType = "decimal"; break;
                case "nchar": csharpType = "string"; break;
                case "ntext": csharpType = "string"; break;
                case "numeric": csharpType = "decimal"; break;
                case "nvarchar": csharpType = "string"; break;
                case "real": csharpType = "Single"; break;
                case "smalldatetime": csharpType = "DateTime"; break;
                case "smallint": csharpType = "short"; break;
                case "smallmoney": csharpType = "decimal"; break;
                case "sql_variant": csharpType = "object"; break;
                case "sysname": csharpType = "object"; break;
                case "text": csharpType = "string"; break;
                case "longtext": csharpType = "string"; break;
                case "time": csharpType = "TimeSpan"; break;
                case "timestamp": csharpType = "byte[]"; break;
                case "tinyint": csharpType = "byte"; break;
                case "uniqueidentifier": csharpType = "Guid"; break;
                case "varbinary": csharpType = "byte[]"; break;
                case "varchar": csharpType = "string"; break;
                case "xml": csharpType = "string"; break;
                default: csharpType = "object"; break;
            }
            return csharpType;
        }

        public static Type MapCommonType(string dbtype)
        {
            if (string.IsNullOrEmpty(dbtype)) return Type.Missing.GetType();
            dbtype = dbtype.ToLower();
            Type commonType = typeof(object);
            switch (dbtype)
            {
                case "bigint": commonType = typeof(long); break;
                case "binary": commonType = typeof(byte[]); break;
                case "bit": commonType = typeof(bool); break;
                case "char": commonType = typeof(string); break;
                case "date": commonType = typeof(DateTime); break;
                case "datetime": commonType = typeof(DateTime); break;
                case "datetime2": commonType = typeof(DateTime); break;
                case "datetimeoffset": commonType = typeof(DateTimeOffset); break;
                case "dityint": commonType = typeof(Boolean); break;
                case "decimal": commonType = typeof(decimal); break;
                case "float": commonType = typeof(double); break;
                case "image": commonType = typeof(byte[]); break;
                case "int": commonType = typeof(int); break;
                case "money": commonType = typeof(decimal); break;
                case "nchar": commonType = typeof(string); break;
                case "ntext": commonType = typeof(string); break;
                case "numeric": commonType = typeof(decimal); break;
                case "nvarchar": commonType = typeof(string); break;
                case "real": commonType = typeof(Single); break;
                case "smalldatetime": commonType = typeof(DateTime); break;
                case "smallint": commonType = typeof(short); break;
                case "smallmoney": commonType = typeof(decimal); break;
                case "sql_variant": commonType = typeof(object); break;
                case "sysname": commonType = typeof(object); break;
                case "text": commonType = typeof(string); break;
                case "time": commonType = typeof(TimeSpan); break;
                case "timestamp": commonType = typeof(byte[]); break;
                case "tinyint": commonType = typeof(byte); break;
                case "uniqueidentifier": commonType = typeof(Guid); break;
                case "varbinary": commonType = typeof(byte[]); break;
                case "varchar": commonType = typeof(string); break;
                case "xml": commonType = typeof(string); break;
                default: commonType = typeof(object); break;
            }
            return commonType;
        }
    }
    #endregion

#>
View Code

在加一个c#的sql帮助类, 命名为DBHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Reflection;
using System.Text;

namespace ToolSite.Entity
{

    public class DBHelper
    {
        //添加到配置文件的<configuration>节点中
        //   <connectionStrings>
        //       <!--改写数据库名,登陆名,密码-->
        //        <add name="conStr" connectionString="Data Source=.;Initial Catalog=;User ID=;Password="/>
        //  
        //   </connectionStrings>
        //<appSettings>
        //      <add key="dbConnection" value="server=192.168.1.111\SQL2005;database=GCUMS;UID=sa;PWD=sa;max pool size=20000;Pooling=true;"/>
        //  </appSettings>
        //先添加configuration引用,引入命名空间
        //private static readonly string conStr = ConfigurationManager.AppSettings["connstr"]; 
        //private static readonly string conStr = Config.ConnStr;
        /// <summary>
        /// 获得连接字符串
        /// </summary>
        /// <returns></returns>
        public static MySqlConnection getConn()
        {
            return new MySqlConnection(Config.ConnStr);
        }
        /// <summary>
        /// 查询获得首行首列的值,格式化SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object Scalar(String sql)
        {
            using (MySqlConnection con = getConn())
            {
                try
                {
                    MySqlCommand com = new MySqlCommand(sql, con);
                    con.Open();
                    return com.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
        /// <summary>
        /// 查询获得首行首列的值 参数化sql语句
        /// </summary>
        /// <param name="paras">参数数组</param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static object Scalar(string sql, MySqlParameter[] paras)
        {
            using (MySqlConnection con = getConn())
            {
                try
                {
                    MySqlCommand com = new MySqlCommand(sql, con);
                    con.Open();
                    if (paras != null) //如果参数
                    {
                        com.Parameters.AddRange(paras);
                    }
                    return com.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    throw ex;

                }
            }
        }

        /// <summary>
        /// 增删改操作,返回受影响的行数,格式化SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int NoneQuery(String sql)
        {

            using (MySqlConnection conn = getConn())
            {
                conn.Open();
                using (MySqlCommand comm = new MySqlCommand(sql, conn))
                {
                    return comm.ExecuteNonQuery();
                }

            }
        }
        /// <summary>
        /// 增删改操作,返回受影响的行数 存储过程
        /// </summary>
        /// <param name="sql">存储过程名称</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public static int NoneQuery(String sql, MySqlParameter[] paras)
        {
            using (MySqlConnection conn = getConn())
            {
                conn.Open();
                using (MySqlCommand comm = new MySqlCommand(sql, conn))
                {
                    comm.Parameters.AddRange(paras);
                    return comm.ExecuteNonQuery();
                }

            }
        }
        /// <summary>
        /// 查询操作,返回一个数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDateTable(String sql)
        {
            using (MySqlConnection con = getConn())
            {
                DataTable dt = new DataTable();
                try
                {
                    MySqlDataAdapter sda = new MySqlDataAdapter(sql, con);
                    sda.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return dt;
            }
        }
        /// <summary>
        ///  查询操作,返回一个数据表,存储过程
        /// </summary>
        /// <param name="sp_Name">存储过程名称</param>
        /// <param name="paras">存储过程参数</param>
        /// <returns></returns>
        public static DataTable GetDateTable(String sql, MySqlParameter[] paras)
        {
            using (MySqlConnection con = getConn())
            {
                DataTable dt = new DataTable();
                try
                {
                    MySqlCommand com = new MySqlCommand(sql, con);
                    com.Parameters.AddRange(paras);
                    MySqlDataAdapter sda = new MySqlDataAdapter(com);
                    sda.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return dt;
            }
        }



    }

    /// <summary>
    /// DataTable与实体类互相转换
    /// </summary>
    /// <typeparam name="T">实体类</typeparam>
    public class DatatableFill<T> where T : new()
    {
        #region DataTable转换成实体类
        /// <summary>
        /// 填充对象列表:用DataSet的第一个表填充实体类
        /// </summary>
        /// <param name="ds">DataSet</param>
        /// <returns></returns>
        public List<T> FillModel(DataSet ds)
        {
            if (ds == null || ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0)
            {
                return new List<T>();
            }
            else
            {
                return FillModel(ds.Tables[0]);
            }
        }

        /// <summary>  
        /// 填充对象列表:用DataSet的第index个表填充实体类
        /// </summary>  
        public List<T> FillModel(DataSet ds, int index)
        {
            if (ds == null || ds.Tables.Count <= index || ds.Tables[index].Rows.Count == 0)
            {
                return new List<T>() ;
            }
            else
            {
                return FillModel(ds.Tables[index]);
            }
        }



        /// <summary>  
        /// 填充对象列表:用DataTable填充实体类
        /// </summary>  
        public List<T> FillModel(DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return new List<T>();
            }
            List<T> modelList = new List<T>();
            foreach (DataRow dr in dt.Rows)
            {
                //T model = (T)Activator.CreateInstance(typeof(T));  
                T model = new T();
                for (int i = 0; i < dr.Table.Columns.Count; i++)
                {
                    PropertyInfo propertyInfo = model.GetType().GetProperty(ToSplitFirstUpper(dr.Table.Columns[i].ColumnName));
                    if (propertyInfo != null && dr[i] != DBNull.Value)
                        propertyInfo.SetValue(model, dr[i], null);
                }

                modelList.Add(model);
            }
            return modelList;
        }

        /// <summary>  
        /// 填充对象:用DataRow填充实体类
        /// </summary>  
        public T FillModel(DataRow dr)
        {
            if (dr == null)
            {
                return default(T);
            }

            //T model = (T)Activator.CreateInstance(typeof(T));  
            T model = new T();

            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                PropertyInfo propertyInfo = model.GetType().GetProperty(ToSplitFirstUpper(dr.Table.Columns[i].ColumnName));
                if (propertyInfo != null && dr[i] != DBNull.Value)
                    propertyInfo.SetValue(model, dr[i], null);
            }
            return model;
        }

        // 去下划线,转大写
        public static string ToSplitFirstUpper(string file)
        {
            string[] words = file.Split('_');
            StringBuilder firstUpperWorld = new StringBuilder();
            foreach (string word in words)
            {
                string firstUpper = ToFirstUpper(word);
                firstUpperWorld.Append(firstUpper);
            }
            string firstUpperFile = firstUpperWorld.ToString().TrimEnd(new char[] { '_' });
            return firstUpperFile;
        }

        // 将字符串设置成首字母大写
        public static string ToFirstUpper(string field)
        {
            string first = field.Substring(0, 1).ToUpperInvariant();
            string result = first;
            if (field.Length > 1)
            {
                string after = field.Substring(1);
                result = first + after;
            }
            return result;
        }
        #endregion

        #region 实体类转换成DataTable

        /// <summary>
        /// 实体类转换成DataSet
        /// </summary>
        /// <param name="modelList">实体类列表</param>
        /// <returns></returns>
        public DataSet FillDataSet(List<T> modelList)
        {
            if (modelList == null || modelList.Count == 0)
            {
                return null;
            }
            else
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(FillDataTable(modelList));
                return ds;
            }
        }

        /// <summary>
        /// 实体类转换成DataTable
        /// </summary>
        /// <param name="modelList">实体类列表</param>
        /// <returns></returns>
        public DataTable FillDataTable(List<T> modelList)
        {
            if (modelList == null || modelList.Count == 0)
            {
                return null;
            }
            DataTable dt = CreateData(modelList[0]);

            foreach (T model in modelList)
            {
                DataRow dataRow = dt.NewRow();
                foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
                {
                    dataRow[propertyInfo.Name] = propertyInfo.GetValue(model, null);
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// <summary>
        /// 根据实体类得到表结构
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private DataTable CreateData(T model)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);
            foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
            {
                dataTable.Columns.Add(new DataColumn(propertyInfo.Name, propertyInfo.PropertyType));
            }
            return dataTable;
        }

        #endregion
    }
}
View Code

再家一个主要的T4文件,命名为 Entity.tt

<#@ include file="$(ProjectDir)Entity/DbHelper.ttinclude"#>
using System;
using MySql.Data.MySqlClient;
using System.Data;
using System.Collections.Generic;
namespace ToolSite.Entity
{
    public class Config
    {
        public static string DefaultDb = "<#=config.DbDatabase#>";
        public static string ConnStr = "<#=config.ConnectionString#>";
    }
        
<#foreach(var table in DbHelper.GetDbTables(config.ConnectionString, config.DbDatabase)){#>
<# string tableName = DbHelper.ToSplitFirstUpper(table.TableName); #>
    public partial class <#=tableName#>
    {
        #region Field
<# foreach(DbColumn column in DbHelper.GetDbColumns(config.ConnectionString, config.DbDatabase, table.TableName)){#>
        /// <summary>
        /// <#= column.Comment#>
        /// </summary>
        public <#= column.CSharpType#> <#=DbHelper.ToSplitFirstUpper(column.Field)#> { get; set; }
<#}#>        #endregion

        public int Save()
        {
            <#=DbHelper.GetParameter(config.ConnectionString, config.DbDatabase, table.TableName, false)#>
            string sql = "<#=DbHelper.GetInsertSql(config.ConnectionString, config.DbDatabase, table.TableName)#>";
        return DBHelper.NoneQuery(sql, paras);
        }
        
        public int Update()
        {

            <#=DbHelper.GetParameter(config.ConnectionString, config.DbDatabase, table.TableName, true)#>
            string sql = "<#=DbHelper.GetUpdateSql(config.ConnectionString, config.DbDatabase, table.TableName)#>";
            return DBHelper.NoneQuery(sql, paras);

        }

        public static int Delete(int id)
        {
            string sql = string.Format("DELETE FROM <#=table.TableName#> WHERE id = {0}", id);
            return DBHelper.NoneQuery(sql);

        }

        public static <#=tableName#> GetById(int id)
        {
            string sql = string.Format("SELECT * FROM <#=table.TableName#> WHERE id = {0}", id);
            DataTable table = DBHelper.GetDateTable(sql);
            List<<#=tableName#>> list = new DatatableFill<<#=tableName#>>().FillModel(table);
            //List<<#=tableName#>> list = Mapper.DynamicMap<IDataReader, List<<#=tableName#>>>(table.CreateDataReader());
            if (list == null || list.Count == 0) return null;
            return list[0];
        }

        public static List<<#=tableName#>> GetList()
        {
            string sql = "SELECT * FROM <#=table.TableName#>";
            DataTable table = DBHelper.GetDateTable(sql);
            List<<#=tableName#>> list = new DatatableFill<<#=tableName#>>().FillModel(table);
            //List<<#=tableName#>> list = Mapper.DynamicMap<IDataReader, List<<#=tableName#>>>(table.CreateDataReader());
            return list;

        }

        public static List<<#=tableName#>> Find(string where)
        {
            string sql = string.Format("SELECT * FROM <#=table.TableName#> WHERE {0};", where);
            DataTable table = DBHelper.GetDateTable(sql);
            return new DatatableFill<<#=tableName#>>().FillModel(table);
        }

        public static List<<#=tableName#>> Find(string field, string prop)
        {
            return Find(string.Format(" {0} = '{1}' ", field, prop));

        }

        public static bool Exist(string field, string prop)
        {
            int n = Count(field, prop);
            return n > 0 ? true : false;
        }

        public static int Count(string where)
        {
            string sql = string.Format("SELECT COUNT(1) FROM <#=table.TableName#> WHERE {0}", where);
            DataTable table = DBHelper.GetDateTable(sql);
            return Convert.ToInt32(table.Rows[0][0]);
        }

        public static int Count(string field, string prop)
        {
            return Count(string.Format(" {0} = '{1}' ", field, prop));
        }

        public static int Count()
        {
            return Count(" 1 = 1 ");
        }

        public static List<<#=tableName#>> Find(int index, int size, ref int count)
        {
            count = Count(" 1 = 1 ");
            string sql = string.Format(" 1 = 1 Order by id desc  LIMIT {0}, {1} ", index * size , size);
            return Find(sql);
        }

        public static List<<#=tableName#>> Find(string field, string prop, int index, int size, ref int count)
        {
            count = Count(field, prop);
            string sql = string.Format(" {0} = {1} Order by id desc LIMIT {2}, {3} ", field, prop, index, size);
            return Find(sql);
        }
    }

<#}#>
}

<#+
    class config
    {
        public static readonly string ConnectionString = "Server=127.0.0.1;Database=toolsite;Uid=root;Pwd=root;";
        public static readonly string DbDatabase = "toolsite";
    }
#>
View Code

你需要改的是最后那个文件的这个位置

    class config
    {
        public static readonly string ConnectionString = "Server=127.0.0.1;Database=toolsite;Uid=root;Pwd=root;";
        public static readonly string DbDatabase = "toolsite";
    }

怎么改我相信你懂的,点击下保存,你的实体类,跟数据库操作就生成了

最后生成的代码是这样子的,还是蛮粗糙的,如果你愿意改改,我相信会更好的!!!

using System;
using MySql.Data.MySqlClient;
using System.Data;
using System.Collections.Generic;
namespace ToolSite.Entity
{
    public class Config
    {
        public static string DefaultDb = "toolsite";
        public static string ConnStr = "Server=127.0.0.1;Database=toolsite;Uid=root;Pwd=root;";
    }
        
    public partial class PageInfo
    {
        #region Field
        /// <summary>
        /// 
        /// </summary>
        public int Id { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public int SiteId { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public int ParentId { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string FileName { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string Content { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string Title { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string Keywords { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string Description { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string H1 { get; set; }
        #endregion

        public int Save()
        {
            MySqlParameter[] paras = new MySqlParameter[] { 
                 new MySqlParameter("site_id", this.SiteId),
                 new MySqlParameter("parent_id", this.ParentId),
                 new MySqlParameter("file_name", this.FileName),
                 new MySqlParameter("content", this.Content),
                 new MySqlParameter("title", this.Title),
                 new MySqlParameter("keywords", this.Keywords),
                 new MySqlParameter("description", this.Description),
                 new MySqlParameter("h1", this.H1)
            };

            string sql = "INSERT INTO page_info(site_id, parent_id, file_name, content, title, keywords, description, h1) VALUES(?site_id, ?parent_id, ?file_name, ?content, ?title, ?keywords, ?description, ?h1)";
        return DBHelper.NoneQuery(sql, paras);
        }
        
        public int Update()
        {

            MySqlParameter[] paras = new MySqlParameter[] { 
                 new MySqlParameter("id", this.Id),
                 new MySqlParameter("site_id", this.SiteId),
                 new MySqlParameter("parent_id", this.ParentId),
                 new MySqlParameter("file_name", this.FileName),
                 new MySqlParameter("content", this.Content),
                 new MySqlParameter("title", this.Title),
                 new MySqlParameter("keywords", this.Keywords),
                 new MySqlParameter("description", this.Description),
                 new MySqlParameter("h1", this.H1)
            };

            string sql = "UPDATE page_info SET site_id = ?site_id, parent_id = ?parent_id, file_name = ?file_name, content = ?content, title = ?title, keywords = ?keywords, description = ?description, h1 = ?h1 WHERE id = ?id";
            return DBHelper.NoneQuery(sql, paras);

        }

        public static int Delete(int id)
        {
            string sql = string.Format("DELETE FROM page_info WHERE id = {0}", id);
            return DBHelper.NoneQuery(sql);

        }

        public static PageInfo GetById(int id)
        {
            string sql = string.Format("SELECT * FROM page_info WHERE id = {0}", id);
            DataTable table = DBHelper.GetDateTable(sql);
            List<PageInfo> list = new DatatableFill<PageInfo>().FillModel(table);
            //List<PageInfo> list = Mapper.DynamicMap<IDataReader, List<PageInfo>>(table.CreateDataReader());
            if (list == null || list.Count == 0) return null;
            return list[0];
        }

        public static List<PageInfo> GetList()
        {
            string sql = "SELECT * FROM page_info";
            DataTable table = DBHelper.GetDateTable(sql);
            List<PageInfo> list = new DatatableFill<PageInfo>().FillModel(table);
            //List<PageInfo> list = Mapper.DynamicMap<IDataReader, List<PageInfo>>(table.CreateDataReader());
            return list;

        }

        public static List<PageInfo> Find(string where)
        {
            string sql = string.Format("SELECT * FROM page_info WHERE {0};", where);
            DataTable table = DBHelper.GetDateTable(sql);
            return new DatatableFill<PageInfo>().FillModel(table);
        }

        public static List<PageInfo> Find(string field, string prop)
        {
            return Find(string.Format(" {0} = '{1}' ", field, prop));

        }

        public static bool Exist(string field, string prop)
        {
            int n = Count(field, prop);
            return n > 0 ? true : false;
        }

        public static int Count(string where)
        {
            string sql = string.Format("SELECT COUNT(1) FROM page_info WHERE {0}", where);
            DataTable table = DBHelper.GetDateTable(sql);
            return Convert.ToInt32(table.Rows[0][0]);
        }

        public static int Count(string field, string prop)
        {
            return Count(string.Format(" {0} = '{1}' ", field, prop));
        }

        public static int Count()
        {
            return Count(" 1 = 1 ");
        }

        public static List<PageInfo> Find(int index, int size, ref int count)
        {
            count = Count(" 1 = 1 ");
            string sql = string.Format(" 1 = 1 Order by id desc  LIMIT {0}, {1} ", index * size , size);
            return Find(sql);
        }

        public static List<PageInfo> Find(string field, string prop, int index, int size, ref int count)
        {
            count = Count(field, prop);
            string sql = string.Format(" {0} = {1} Order by id desc LIMIT {2}, {3} ", field, prop, index, size);
            return Find(sql);
        }
    }

    public partial class SiteInfo
    {
        #region Field
        /// <summary>
        /// 
        /// </summary>
        public int Id { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string Name { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string Template { get; set; }
        #endregion

        public int Save()
        {
            MySqlParameter[] paras = new MySqlParameter[] { 
                 new MySqlParameter("name", this.Name),
                 new MySqlParameter("template", this.Template)
            };

            string sql = "INSERT INTO site_info(name, template) VALUES(?name, ?template)";
        return DBHelper.NoneQuery(sql, paras);
        }
        
        public int Update()
        {

            MySqlParameter[] paras = new MySqlParameter[] { 
                 new MySqlParameter("id", this.Id),
                 new MySqlParameter("name", this.Name),
                 new MySqlParameter("template", this.Template)
            };

            string sql = "UPDATE site_info SET name = ?name, template = ?template WHERE id = ?id";
            return DBHelper.NoneQuery(sql, paras);

        }

        public static int Delete(int id)
        {
            string sql = string.Format("DELETE FROM site_info WHERE id = {0}", id);
            return DBHelper.NoneQuery(sql);

        }

        public static SiteInfo GetById(int id)
        {
            string sql = string.Format("SELECT * FROM site_info WHERE id = {0}", id);
            DataTable table = DBHelper.GetDateTable(sql);
            List<SiteInfo> list = new DatatableFill<SiteInfo>().FillModel(table);
            //List<SiteInfo> list = Mapper.DynamicMap<IDataReader, List<SiteInfo>>(table.CreateDataReader());
            if (list == null || list.Count == 0) return null;
            return list[0];
        }

        public static List<SiteInfo> GetList()
        {
            string sql = "SELECT * FROM site_info";
            DataTable table = DBHelper.GetDateTable(sql);
            List<SiteInfo> list = new DatatableFill<SiteInfo>().FillModel(table);
            //List<SiteInfo> list = Mapper.DynamicMap<IDataReader, List<SiteInfo>>(table.CreateDataReader());
            return list;

        }

        public static List<SiteInfo> Find(string where)
        {
            string sql = string.Format("SELECT * FROM site_info WHERE {0};", where);
            DataTable table = DBHelper.GetDateTable(sql);
            return new DatatableFill<SiteInfo>().FillModel(table);
        }

        public static List<SiteInfo> Find(string field, string prop)
        {
            return Find(string.Format(" {0} = '{1}' ", field, prop));

        }

        public static bool Exist(string field, string prop)
        {
            int n = Count(field, prop);
            return n > 0 ? true : false;
        }

        public static int Count(string where)
        {
            string sql = string.Format("SELECT COUNT(1) FROM site_info WHERE {0}", where);
            DataTable table = DBHelper.GetDateTable(sql);
            return Convert.ToInt32(table.Rows[0][0]);
        }

        public static int Count(string field, string prop)
        {
            return Count(string.Format(" {0} = '{1}' ", field, prop));
        }

        public static int Count()
        {
            return Count(" 1 = 1 ");
        }

        public static List<SiteInfo> Find(int index, int size, ref int count)
        {
            count = Count(" 1 = 1 ");
            string sql = string.Format(" 1 = 1 Order by id desc  LIMIT {0}, {1} ", index * size , size);
            return Find(sql);
        }

        public static List<SiteInfo> Find(string field, string prop, int index, int size, ref int count)
        {
            count = Count(field, prop);
            string sql = string.Format(" {0} = {1} Order by id desc LIMIT {2}, {3} ", field, prop, index, size);
            return Find(sql);
        }
    }

}
View Code

 

posted @ 2014-05-09 11:26  ZhanHengZong  阅读(502)  评论(0编辑  收藏  举报