DAL.cst

<%@ CodeTemplate Language="C#" TargetLanguage="C#" ResponseEncoding="UTF-8"	Src="ToolsCodeTemplate.cs" Inherits="ToolsCodeTemplate"%>
<%@ Property Name="TargetTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="TargetTable that the object is based on." %>
<%@ Property Name="ModelsNamespace" Default="MyOffice.Models" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>
<%@ Property Name="DALNamespace" Default="PwbMVC.DAL" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>
<%@ Property Name="DALClassNameSurfix" Default="Service" Type="System.String" Category="Context" Description="类名称后缀" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>


<% PrintHeader(); %>
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using <%= ModelsNamespace %>;

namespace <%= DALNamespace %>
{
	public partial class <%= GetDALClassName() %>
	{
		
        #region Insert
        /// <summary>
        /// 插入数据
        /// </summary>        
        public  bool Insert (<%= GetModelClassName() %> <%= GetModelParamName() %>)
		{
			<%if(IsIdentityPK())
			{%>
				string sql ="<%= GetAutoIncInsertSQLLine()%>";
                
				MySqlParameter[] para = new MySqlParameter[]
					{
						<%
						for(int i=0; i<TargetTable.NonPrimaryKeyColumns.Count; i++)
						{
							ColumnSchema column = TargetTable.NonPrimaryKeyColumns[i];
							
						%>
						new MySqlParameter("@<%= column.Name %>", ToDBValue(<%= GetModelParamName() %>.<%= column.Name %>)),
						<%
						}
						%>
					};
					
				int affectRows = (int)MySqlDBHelper.ExecuteNonQuery(sql, para);
				if(affectRows==1)
				{
					return true;
				}else
				{
					return false;					
				}
			<%}else
			{%>
				string sql ="<%= GetCommonInsertSQLLine()%>";
				MySqlParameter[] para = new MySqlParameter[]
					{
						<%
						for(int i=0; i<TargetTable.Columns.Count; i++)
						{
							ColumnSchema column = TargetTable.Columns[i];							
						%>
						new MySqlParameter("@<%= column.Name %>", ToDBValue(<%= GetModelParamName() %>.<%= column.Name %>)),
						<%
						}
						%>
					};
				int AddId = (int)MySqlDBHelper.ExecuteNonQuery(sql, para);
				if(AddId==1)
				{
					return true;
				}else
				{
					return false;					
				}			
			<%}%>
		}
              
         #endregion

	
        #region  Delete
        /// <summary>
        /// 根据主键删除数据
        /// </summary>
        public  int DeleteBy<%= GetPKPropertyName() %>(<%= GetPKPropertyType() %> <%= GetCamelPKName() %>)
		{
            string sql = "DELETE from <%= TargetTable.Database %>.<%= TargetTable.Name %> WHERE <%= GetPKPropertyName() %> = @<%= GetPKPropertyName() %>";

            MySqlParameter[] para = new MySqlParameter[]
			{
				new MySqlParameter("@<%= GetPKName() %>", <%= GetCamelPKName() %>)
			};
		
            return MySqlDBHelper.ExecuteNonQuery(sql, para);
		}
		 #endregion
					
		
        #region Update
        /// <summary>
        /// 根据主键更新数据
        /// </summary>
        public  int Update(<%= GetModelClassName() %> <%= GetModelParamName() %>)
        {
            string sql ="UPDATE <%= TargetTable.Database %>.<%= TargetTable.Name %> " +"SET " +
			" <%= TargetTable.NonPrimaryKeyColumns[0].Name %> = @<%= TargetTable.NonPrimaryKeyColumns[0].Name %>" 
			<%
			for(int i=1; i<TargetTable.NonPrimaryKeyColumns.Count; i++)
			{
				ColumnSchema column = TargetTable.NonPrimaryKeyColumns[i];				
			%>
                +", <%= column.Name %> = @<%= column.Name %>" 
			<%
			}
			%>
               
            +" WHERE <%= GetPKName() %> = @<%= GetPKName() %>";


			MySqlParameter[] para = new MySqlParameter[]
			{
				new MySqlParameter("@<%= GetPKName() %>", <%= GetModelParamName() %>.<%= GetPKName() %>)
				<%
				for(int i=0; i<TargetTable.NonPrimaryKeyColumns.Count; i++)
				{
					ColumnSchema column = TargetTable.NonPrimaryKeyColumns[i];
				%>
					,new MySqlParameter("@<%= column.Name %>", ToDBValue(<%= GetModelParamName() %>.<%= column.Name %>))
				<%
				}
				%>
			};

			return MySqlDBHelper.ExecuteNonQuery(sql, para);
        }
        #endregion
        
		#region select
        /// <summary>
        /// 根据主键查询数据
        /// </summary>
        public  <%= GetModelClassName() %> GetBy<%= GetPKPropertyName() %>(<%= GetPKPropertyType() %> <%= GetCamelPKName() %>)
        {
            string sql = "SELECT * FROM <%= TargetTable.Database %>.<%= TargetTable.Name %> WHERE <%= GetPKPropertyName() %> = @<%= GetPKPropertyName() %>";
            using(MySqlDataReader reader = MySqlDBHelper.ExecuteDataReader(sql, new MySqlParameter("@<%= GetPKPropertyName() %>", <%= GetCamelPKName() %>)))
			{
				if (reader.Read())
				{
					return ToModel(reader);
				}
				else
				{
					return null;
				}
       		}
        }
     
        ///<summary>
        ///根据字段名获取数据记录IEnumerable<>
        ///</summary>              
		public  IEnumerable<<%= GetModelClassName() %>> GetBycolumnName(string columnName,string columnContent)
		{
			string sql = "SELECT * FROM <%= TargetTable.Database %>.<%= TargetTable.Name %> where "+columnName+"='"+ @columnContent + "'";
			using(MySqlDataReader reader = MySqlDBHelper.ExecuteDataReader(sql,new MySqlParameter(columnName, columnContent)))
			{
				return ToModels(reader);			
			}
		}
        
        ///<summary>
        ///根据字段名获取数据记录IEnumerable<>
        ///</summary>              
		public  IEnumerable<<%= GetModelClassName() %>> GetBycolumnNames(string[] columnNames,string[] columnContents)
		{
			string sql = "SELECT * FROM <%= TargetTable.Database %>.<%= TargetTable.Name %> where ";
            
            if (columnNames.Length!= columnContents.Length)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < columnNames.Length; i++)
                {
                    sql += columnNames[i] +"= '"+ columnContents[i] + "' and ";
                }
                
              sql =  sql.Substring(0, sql.Length - 5);
            }
            
			using(MySqlDataReader reader = MySqlDBHelper.ExecuteDataReader(sql))
			{
				return ToModels(reader);			
			}
		}
	
        
        #endregion
        
     
        /// <summary>
        /// 把DataRow转换成Model
        /// </summary>
		public  <%= GetModelClassName() %> ToModel(MySqlDataReader dr)
		{
			<%= GetModelClassName() %> <%= GetModelParamName() %> = new <%= GetModelClassName() %>();

			<% foreach(ColumnSchema column in TargetTable.Columns) %>
			<% { %>
			<%= GetModelParamName() %>.<%= GetPropertyName(column) %> = (<%=GetPropertyType(column)%>)ToModelValue(dr,"<%=column.Name%>");
			<% } %>
			return <%= GetModelParamName() %>;
		}
	
        
      
        ///<summary>
        /// 获得总记录数
        ///</summary>        
		public  int GetTotalCount()
		{
			string sql = "SELECT count(*) FROM <%= TargetTable.Database %>.<%= TargetTable.Name %>";
			return (int)MySqlDBHelper.ExecuteScalar(sql);
		}

        
        #region 获得分页记录集IEnumerable<>
        ///<summary>
        /// 获得分页记录集IEnumerable<>
        ///</summary>              
		public IEnumerable<<%= GetModelClassName() %>> GetPagedData(int minrownum,int maxrownum)
		{
			string sql = "SELECT * from(SELECT *,(row_number() over(order by <%=this.GetPKName()%>))-1 rownum FROM <%= TargetTable.Database %>.<%= TargetTable.Name %>) t where rownum>=@minrownum and rownum<=@maxrownum";
			using(MySqlDataReader reader = MySqlDBHelper.ExecuteDataReader(sql,
				new MySqlParameter("@minrownum",minrownum),
				new MySqlParameter("@maxrownum",maxrownum)))
			{
				return ToModels(reader);					
			}
		}
		#endregion
        
        

        ///<summary>
        /// 获得总记录集IEnumerable<>
        ///</summary> 
		public  IEnumerable<<%= GetModelClassName() %>> GetAll()
		{
			string sql = "SELECT * FROM <%= TargetTable.Database %>.<%= TargetTable.Name %>";
			using(MySqlDataReader reader = MySqlDBHelper.ExecuteDataReader(sql))
			{
				return ToModels(reader);			
			}
		}
  


        #region 把MySqlDataReader转换成IEnumerable<>
        ///<summary>
        /// 把MySqlDataReader转换成IEnumerable<>
        ///</summary> 
		protected  IEnumerable<<%= GetModelClassName() %>> ToModels(MySqlDataReader reader)
		{
			var list = new List<<%= GetModelClassName() %>>();
			while(reader.Read())
			{
				list.Add(ToModel(reader));
			}	
			return list;
		}		
		#endregion
        
        #region 判断数据是否为空
        ///<summary>
        /// 判断数据是否为空
        ///</summary>
		protected  object ToDBValue(object value)
		{
			if(value==null)
			{
				return DBNull.Value;
			}
			else
			{
				return value;
			}
		}
		#endregion
        
        #region 判断数据表中是否包含该字段
        ///<summary>
        /// 判断数据表中是否包含该字段
        ///</summary>
		protected  object ToModelValue(MySqlDataReader reader,string columnName)
		{
			if(reader.IsDBNull(reader.GetOrdinal(columnName)))
			{
				return null;
			}
			else
			{
				return reader[columnName];
			}
		}
        #endregion
	}
}
<script runat="template">

//CS_IsUnsigned  表示此列 是否是 无符号的，其ExtendedProperties["IsUnsigned"].Value是bool类型，True表示是无符号的否则是有符号的或无法设置为无符号（为我自己添加的）。

//CS_ColumnDefaultIsNull  某列的默认值是否是Null，True或False。

//CS_Default  某列的默认值。

//CS_Description  列的注释。

//CS_IsIdentity  是否是标识列。

//CS_IdentitySeed  是标识列的前提下该列的种子值（起始值）。

//CS_IdentityIncrement  标识列的前提下该列的自增长值。

//CS_ColumnDefault  和CS_Default一样，是某列的默认值

//CS_SystemType 和 CS_ColumnType一样，都是某列在数据库中的 具体类型（通过SQL语句写出的类型）

//CS_ColumnExtra  存储内容是什么暂时不知。

//数据表的ExtendedProperties：

//CS_ShowCreateTable  存储的是执行SQL语句：Show Create Table。。。后的第二部分内容

//CS_Description  该表的注释
                
                
    /// <summary>
    /// 是否为列
    /// </summary>
    /// <returns></returns>
    public bool IsIdentityPK() {
        foreach (ColumnSchema column in TargetTable.Columns) {
            if ((bool) column.ExtendedProperties["CS_IsIdentity"].Value) {
                return true;
            }
        }
        return false;
    }


public string GetDALClassName() {
    return GetModelClassName() + DALClassNameSurfix;
}
// User
public string GetModelClassName() {
    return GetModelClassName(TargetTable);
}
// user
public string GetModelMemberVarName() {
    return GetModelParamName();
}
/// <summary>
/// 获取类名称的驼峰形式
/// </summary>
/// <returns></returns>
public string GetModelParamName() {
    return ConvertToCamel(GetModelClassName());
}


/// <summary>
/// 获取插入SQL语句,不包括主键
/// </summary>
/// <returns></returns>
public string GetAutoIncInsertSQLLine() {
    string result;
    result = "INSERT INTO " +TargetTable.Database+"."+TargetTable.Name + " (";
    foreach (ColumnSchema column in TargetTable.NonPrimaryKeyColumns) {
        result += column.Name + ", ";
    }
    result = result.Substring(0, result.Length - 2);
    result += ") ";
    result += " VALUES (";
    foreach (ColumnSchema column in TargetTable.NonPrimaryKeyColumns) {
        result += "@" + column.Name + ", ";
    }
    result = result.Substring(0, result.Length - 2);
    result += ")";
    //result += ");select @@IDENTITY";
    return result;
}

public string GetCommonInsertSQLLine() {
    string result;
    result = "INSERT INTO "+TargetTable.Database+"."+TargetTable.Name + " (";
    foreach (ColumnSchema column in TargetTable.Columns) {
        result += column.Name + ", ";
    }
    result = result.Substring(0, result.Length - 2);
    result += ") ";
    result += " VALUES (";
    foreach (ColumnSchema column in TargetTable.Columns) {
        result += "@" + column.Name + ", ";
    }
    result = result.Substring(0, result.Length - 2);
    result += ")";
    return result;
}


public string GetPKPropertyType() {
    return GetPKType(TargetTable);
}


public string GetPKPropertyName() {
    return ConvertToPascal(GetPKName());
}
/// <summary>
/// 获取主键驼峰名称
/// </summary>
/// <returns></returns>
public string GetCamelPKName() {
    return ConvertToCamel(GetPKName());
}

// Id
public string GetPKName() {
    return GetPKName(TargetTable);
}
/// <summary>
/// 获取输出文件名称.cs
/// </summary>
/// <returns></returns>
public override string GetFileName() {
    return this.GetDALClassName() + ".cs";
}

</script>