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 #>
在加一个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 } }
再家一个主要的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"; } #>
你需要改的是最后那个文件的这个位置
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); } } }
