ASP.NET三层架构源码(CodeSmith版)之四:Table-SQLServerDAL层
动软代码生成器生成的ASP.NET三层架构代码比较规范,是学习ASP.NET的好例子
此三层架构改造自动软的工厂模式模板,使用CodeSmith进行重写,以方便大家修改模板文件
以下是针对表格的SQLServerDAL层源码:
<%@ CodeTemplate Inherits="CodeTemplate" language="C#" TargetLanguage="Text" Description="NetTiers main template."Debug="True" ResponseEncoding="UTF-8"%> <%-- 加载访问数据库的组件SchemaExplorer,并声明其使用的命名空间 --%> <%@ Assembly Name="SchemaExplorer" %> <%@ Import Namespace="SchemaExplorer" %> <%@ Import Namespace="System.Text" %> <%@ Assembly Name="System.Data" %> <%@ Assembly Name="CodeSmith.BaseTemplates" %> <%@ Import Namespace="CodeSmith.BaseTemplates" %> <%@ Assembly Name="CodeSmith.CustomProperties" %> <%@ Import Namespace="CodeSmith.CustomProperties" %> <%@ Import Namespace="System.Text.RegularExpressions" %> <%@ Property Name="Namespace" Type="String" Category="Context" Description="NameSpace" Default="Crs811NameSpace"%> <%@ Property Name="Author" Type="String" Category="Context" Description="Author" Default="chenr"%> <%@ Property Name="TablePrefix" Type="System.String" Default="T" Category="Context" Description="The prefix to remove from table names" %> <%@ Property Name="TableName" Type="TableSchema" DeepLoad="True" Optional="False" Category="Context" Description="" %> /*------------------------------------------------ // File Name:<%=ClearPrefix(TableName.Name) %>.cs // File Description:<%=ClearPrefix(TableName.Name) %> SQL Server DataBase Access // Author:<%=Author%> // Create Time:<%= DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")%> //------------------------------------------------*/ using System; using System.Text; using System.Data.SqlClient; using System.Collections.Generic; using System.Data; using <%=Namespace%>.DBUtility; using <%=Namespace%>.IDAL; using <%=Namespace%>.Model; namespace <%=Namespace%>.SQLServerDAL { /// <summary> /// <%=ClearPrefix(TableName.Name) %> DAL /// </summary> public partial class <%=ClearPrefix(TableName.Name) %>: I<%=ClearPrefix(TableName.Name) %> { #region Method /// <summary> /// 得到最大ID /// </summary> public int GetMaxId() { return DbHelperSQL.GetMaxID("<%= GetKeyMax(TableName)%>", "<%=ClearPrefix(TableName.Name) %>"); //DbHelperSQL.RunProcedure("tTest_GetMaxId","",out rowsAffected); //return rowsAffected; } /// <summary> /// 是否存在该记录 /// </summary> public bool Exists(<%= GetInParameter(TableName) %>) { int rowsAffected; SqlParameter[] parameters ={ <%= GenerateNewSqlParameter(TableName, true) %>}; <% for (int i=0; i<TableName.PrimaryKey.MemberColumns.Count; i++) {%> parameters[<%=i%>].Value = <%= TableName.PrimaryKey.MemberColumns[i].Name %>; <%}%> int result= DbHelperSQL.RunProcedure("<%=ClearPrefix(TableName.Name) %>_Exists",parameters,out rowsAffected); if(result==1) { return true; } else { return false; } } /// <summary> /// 增加一条数据 /// </summary> public bool Add(m<%=ClearPrefix(TableName.Name) %> model) { int rowsAffected; SqlParameter[] parameters ={ <%= GenerateNewSqlParameter(TableName, false) %>}; <%= GenerateSqlParameter(TableName, false, "C")%> DbHelperSQL.RunProcedure("<%=ClearPrefix(TableName.Name) %>_ADD",parameters,out rowsAffected); if (rowsAffected > 0) { return true; } else { return false; } } /// <summary> /// 更新一条数据 /// </summary> public bool Update(m<%=ClearPrefix(TableName.Name) %> model) { int rowsAffected=0; SqlParameter[] parameters = { <%= GenerateNewSqlParameter(TableName, false) %>}; <%= GenerateSqlParameter(TableName, false, "U") %> DbHelperSQL.RunProcedure("<%=ClearPrefix(TableName.Name) %>_Update",parameters,out rowsAffected); if (rowsAffected > 0) { return true; } else { return false; } } /// <summary> /// 删除一条数据 /// </summary> public bool Delete(<%= GetInParameter(TableName) %>) { int rowsAffected=0; SqlParameter[] parameters = { <%= GenerateNewSqlParameter(TableName, true) %>}; <% for (int i=0; i<TableName.PrimaryKey.MemberColumns.Count; i++) {%> parameters[<%=i%>].Value = <%= TableName.PrimaryKey.MemberColumns[i].Name %>; <%}%> DbHelperSQL.RunProcedure("<%=ClearPrefix(TableName.Name) %>_Delete",parameters,out rowsAffected); if (rowsAffected > 0) { return true; } else { return false; } } <% if (TableName.PrimaryKey.MemberColumns.Count == 1) {%> /// <summary> /// 批量删除数据 /// </summary> public bool DeleteList(string <%=TableName.PrimaryKey.MemberColumns[0].Name%>list) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from <%=ClearPrefix(TableName.Name) %> "); strSql.Append(" where <%=TableName.PrimaryKey.MemberColumns[0].Name%> in ("+<%=TableName.PrimaryKey.MemberColumns[0].Name%>list + ") "); int rows = DbHelperSQL.ExecuteSql(strSql.ToString()); if (rows > 0) { return true; } else { return false; } } <%}%> /// <summary> /// 得到一个对象实体 /// </summary> public m<%=ClearPrefix(TableName.Name) %> GetModel(<%= GetInParameter(TableName) %>) { SqlParameter[] parameters = { <%= GenerateNewSqlParameter(TableName, true) %>}; <% for (int i=0; i<TableName.PrimaryKey.MemberColumns.Count; i++) {%> parameters[<%=i%>].Value = <%= TableName.PrimaryKey.MemberColumns[i].Name %>; <%}%> m<%=ClearPrefix(TableName.Name) %> model=new m<%=ClearPrefix(TableName.Name) %>(); DataSet ds= DbHelperSQL.RunProcedure("<%=ClearPrefix(TableName.Name) %>_GetModel",parameters,"ds"); if(ds.Tables[0].Rows.Count>0) { return DataRowToModel(ds.Tables[0].Rows[0]); } else { return null; } } /// <summary> /// 得到一个对象实体 /// </summary> public m<%=ClearPrefix(TableName.Name) %> DataRowToModel(DataRow row) { m<%=ClearPrefix(TableName.Name) %> model=new m<%=ClearPrefix(TableName.Name) %>(); if (row != null) { <%= GenerateModelWithRow(TableName) %> } return model; } /// <summary> /// 获得数据列表 /// </summary> public DataSet GetList(string strWhere) { StringBuilder strSql=new StringBuilder(); strSql.Append("select <%= GetAllRows(TableName)%>"); strSql.Append(" FROM <%=ClearPrefix(TableName.Name) %> "); if(strWhere.Trim()!="") { strSql.Append(" where "+strWhere); } return DbHelperSQL.Query(strSql.ToString()); } /// <summary> /// 获得前几行数据 /// </summary> public DataSet GetList(int Top,string strWhere,string filedOrder) { StringBuilder strSql=new StringBuilder(); strSql.Append("select "); if(Top>0) { strSql.Append(" top "+Top.ToString()); } strSql.Append(" <%= GetAllRows(TableName)%> "); strSql.Append(" FROM <%=ClearPrefix(TableName.Name) %> "); if(strWhere.Trim()!="") { strSql.Append(" where "+strWhere); } strSql.Append(" order by " + filedOrder); return DbHelperSQL.Query(strSql.ToString()); } /// <summary> /// 获取记录总数 /// </summary> public int GetRecordCount(string strWhere) { StringBuilder strSql=new StringBuilder(); strSql.Append("select count(*) FROM <%=ClearPrefix(TableName.Name) %> "); if(strWhere.Trim()!="") { strSql.Append(" where "+strWhere); } object obj = DbHelperSQL.GetSingle(strSql.ToString()); if (obj == null) { return 0; } else { return Convert.ToInt32(obj); } } /// <summary> /// 分页获取数据列表 /// </summary> public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex) { StringBuilder strSql=new StringBuilder(); strSql.Append("SELECT * FROM ( "); strSql.Append(" SELECT ROW_NUMBER() OVER ("); if (!string.IsNullOrEmpty(orderby.Trim())) { strSql.Append("order by T." + orderby ); } else { strSql.Append("order by T.<%=TableName.PrimaryKey.MemberColumns[0].Name%> desc"); } strSql.Append(")AS Row, T.* from <%=ClearPrefix(TableName.Name) %> T "); if (!string.IsNullOrEmpty(strWhere.Trim())) { strSql.Append(" WHERE " + strWhere); } strSql.Append(" ) TT"); strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex); return DbHelperSQL.Query(strSql.ToString()); } /* /// <summary> /// 分页获取数据列表 /// </summary> public DataSet GetList(int PageSize,int PageIndex,string strWhere) { SqlParameter[] parameters = { new SqlParameter("@tblName", SqlDbType.VarChar, 255), new SqlParameter("@fldName", SqlDbType.VarChar, 255), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int), new SqlParameter("@IsReCount", SqlDbType.Bit), new SqlParameter("@OrderType", SqlDbType.Bit), new SqlParameter("@strWhere", SqlDbType.VarChar,1000), }; parameters[0].Value = "<%=ClearPrefix(TableName.Name) %>"; parameters[1].Value = "<%=TableName.PrimaryKey.MemberColumns[0].Name%>"; parameters[2].Value = PageSize; parameters[3].Value = PageIndex; parameters[4].Value = 0; parameters[5].Value = 0; parameters[6].Value = strWhere; return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds"); }*/ #endregion Method #region MethodEx #endregion MethodEx } } <script runat="template"> public string GetCSharpTypeFromDBFieldType(ColumnSchema column) { if (column.Name.EndsWith("TypeCode")) return column.Name; switch (column.DataType) { case DbType.AnsiString: return "string"; case DbType.AnsiStringFixedLength: return "string"; case DbType.Binary: return "byte[]"; case DbType.Boolean: return "bool"; case DbType.Byte: return "byte"; case DbType.Currency: return "decimal"; case DbType.Date: return "DateTime"; case DbType.DateTime: return "DateTime"; case DbType.Decimal: return "decimal"; case DbType.Double: return "double"; case DbType.Guid: return "Guid"; case DbType.Int16: return "short"; case DbType.Int32: return "int"; case DbType.Int64: return "long"; case DbType.Object: return "object"; case DbType.SByte: return "sbyte"; case DbType.Single: return "float"; case DbType.String: return "string"; case DbType.StringFixedLength: return "string"; //case DbType.StringFixedLength: return "UniqueIdentifier"; case DbType.Time: return "TimeSpan"; case DbType.UInt16: return "ushort"; case DbType.UInt32: return "uint"; case DbType.UInt64: return "ulong"; case DbType.VarNumeric: return "decimal"; default: { return "__UNKNOWN__" + column.NativeType; } } } public string GetSQLVariableType(ColumnSchema column) { if (column.Name.EndsWith("TypeCode")) return column.Name; switch (column.NativeType.ToLower()) { case "bigint" : return "SqlDbType.BigInt"; case "binary" : return "SqlDbType.Binary"; case "bit" : return "SqlDbType.Bit"; case "char" : return "SqlDbType.Char"; case "date" : return "SqlDbType.Date"; case "datetime" : return "SqlDbType.DateTime"; case "datetime2" : return "SqlDbType.DateTime2"; case "datetimeoffset" : return "SqlDbType.DateTimeOffset"; case "decimal" : return "SqlDbType.Decimal"; case "float" : return "SqlDbType.Float"; //case "geography" : return "SqlDbType.BigInt"; //case "geometry" : return "SqlDbType.BigInt"; //case "hierarchyid" : return "SqlDbType.BigInt"; case "image" : return "SqlDbType.Image"; case "int" : return "SqlDbType.Int"; case "money" : return "SqlDbType.Money"; case "nchar" : return "SqlDbType.NChar"; case "ntext" : return "SqlDbType.NText"; case "numeric" : return "SqlDbType.Numeric"; case "nvarchar" : return "SqlDbType.NVarChar"; case "real" : return "SqlDbType.Real"; case "smalldatetime" : return "SqlDbType.SmallDateTime"; case "smallint" : return "SqlDbType.SmallInt"; case "smallmoney" : return "SqlDbType.SmallMoney"; case "sql_variant" : return "SqlDbType.Variant"; case "text" : return "SqlDbType.Text"; case "time" : return "SqlDbType.Time"; case "timestamp" : return "SqlDbType.Timestamp"; case "tinyint" : return "SqlDbType.TinyInt"; case "uniqueidentifier" : return "SqlDbType.UniqueIdentifier"; case "varbinary" : return "SqlDbType.VarBinary"; case "varchar" : return "SqlDbType.VarChar"; case "xml" : return "SqlDbType.Xml"; default: return "__UNKNOWN__" + column.NativeType; } } public string GetSqlDbType(ColumnSchema column) { switch (column.NativeType) { case "bigint": return "BigInt"; case "binary": return "Binary"; case "bit": return "Bit"; case "char": return "Char"; case "datetime": return "DateTime"; case "decimal": return "Decimal"; case "float": return "Float"; case "image": return "Image"; case "int": return "Int"; case "money": return "Money"; case "nchar": return "NChar"; case "ntext": return "NText"; case "numeric": return "Decimal"; case "nvarchar": return "NVarChar"; case "real": return "Real"; case "smalldatetime": return "SmallDateTime"; case "smallint": return "SmallInt"; case "smallmoney": return "SmallMoney"; case "sql_variant": return "Variant"; case "sysname": return "NChar"; case "text": return "Text"; case "timestamp": return "Timestamp"; case "tinyint": return "TinyInt"; case "uniqueidentifier": return "UniqueIdentifier"; case "varbinary": return "VarBinary"; case "varchar": return "VarChar"; default: return "__UNKNOWN__" + column.NativeType; } } public string GenerateNewSqlParameter(TableSchema TableName, bool pKey) { string strReturn = string.Empty; if (pKey) { for (int i=0; i<TableName.PrimaryKey.MemberColumns.Count; i++) { strReturn += "new SqlParameter("@"; strReturn += TableName.PrimaryKey.MemberColumns[i].Name+"","; strReturn += GetSQLVariableType(TableName.PrimaryKey.MemberColumns[i]); if (GetSqlParameter(TableName.PrimaryKey.MemberColumns[i])) strReturn += ","+TableName.PrimaryKey.MemberColumns[i].Size; strReturn+=")"; if (i<TableName.PrimaryKey.MemberColumns.Count-1) strReturn += ","; strReturn+="ntttt"; } } else { for (int i=0; i<TableName.Columns.Count; i++) { strReturn += "new SqlParameter("@"; strReturn += TableName.Columns[i].Name+"","; strReturn += GetSQLVariableType(TableName.Columns[i]); if (GetSqlParameter(TableName.Columns[i])) strReturn += ","+TableName.Columns[i].Size; strReturn+=")"; if (i<TableName.Columns.Count-1) strReturn += ","; strReturn+="ntttt"; } } return strReturn; } public string GenerateSqlParameter(TableSchema TableName, bool pKey, string OpType) { string strReturn = string.Empty; if (!pKey) { for (int i=0; i<TableName.Columns.Count; i++) { strReturn += "parameters["+i+"].Value = "; //新建数据 Guid if (TableName.Columns[i].DataType.ToString() == "Guid" && OpType == "C") strReturn += "Guid.NewGuid()"; else strReturn += "model."+TableName.Columns[i].Name; strReturn += ";nttt"; } } return strReturn; } public string GenerateModelWithRow(TableSchema TableName) { string strReturn = string.Empty; string str_t = "tttt"; for (int i=0; i<TableName.Columns.Count; i++) { switch (GetSQLVariableType(TableName.Columns[i])) { case "SqlDbType.VarChar": case "SqlDbType.NVarChar": case "SqlDbType.Char": case "SqlDbType.NChar": case "SqlDbType.Text": case "SqlDbType.NText": { strReturn += str_t+GeneateModelWithRow_common(TableName.Columns[i].Name, 0); strReturn += "n"+str_t+"{nt"+str_t+"model." + TableName.Columns[i].Name + "=row["" + TableName.Columns[i].Name + ""].ToString();n"+str_t+"}"; break; } case "SqlDbType.UniqueIdentifier": { strReturn += str_t+GeneateModelWithRow_common(TableName.Columns[i].Name, 1); strReturn += "n"+str_t+"{nt"+str_t+"model." + TableName.Columns[i].Name + "=new Guid(row["" + TableName.Columns[i].Name + ""].ToString());n"+str_t+"}"; break; } case "SqlDbType.BigInt": { strReturn += str_t+GeneateModelWithRow_common(TableName.Columns[i].Name, 1); strReturn += "n"+str_t+"{nt"+str_t+"model." + TableName.Columns[i].Name + "=long.Parse(row["" + TableName.Columns[i].Name + ""].ToString());n"+str_t+"}"; break; } case "SqlDbType.Decimal": case "SqlDbType.Float": case "SqlDbType.Money": case "SqlDbType.Real": case "SqlDbType.SmallMoney": case "SqlDbType.Numeric": { strReturn += str_t+GeneateModelWithRow_common(TableName.Columns[i].Name, 1); strReturn += "n"+str_t+"{nt"+str_t+"model." + TableName.Columns[i].Name + "=decimal.Parse(row["" + TableName.Columns[i].Name + ""].ToString());n"+str_t+"}"; break; } case "SqlDbType.Image": case "SqlDbType.Binary": { strReturn += str_t+GeneateModelWithRow_common(TableName.Columns[i].Name, 1); strReturn += "n"+str_t+"{nt"+str_t+"model." + TableName.Columns[i].Name + "=(byte[])row[""+TableName.Columns[i].Name +""].ToString();n"+str_t+"}"; break; } case "SqlDbType.SmallDateTime": case "SqlDbType.Date": case "SqlDbType.DateTime": case "SqlDbType.DateTime2": case "SqlDbType.DateTimeOffset": case "SqlDbType.Time": case "SqlDbType.Timestamp": { strReturn += str_t+GeneateModelWithRow_common(TableName.Columns[i].Name, 1); strReturn += "n"+str_t+"{nt"+str_t+"model." + TableName.Columns[i].Name + "=DateTime.Parse(row["" + TableName.Columns[i].Name + ""].ToString());n"+str_t+"}"; break; } case "SqlDbType.Bit": { strReturn += str_t+GeneateModelWithRow_common(TableName.Columns[i].Name, 1); strReturn += "n"+str_t+"{"; strReturn += "nt"+str_t+"if((row["" + TableName.Columns[i].Name + ""].ToString()=="1")||(row["" + TableName.Columns[i].Name + ""].ToString().ToLower()=="true")) {n"; strReturn += "tt"+str_t+"model." + TableName.Columns[i].Name + "=true;nt"+str_t+"} else {n"; strReturn += "tt"+str_t+"model." + TableName.Columns[i].Name + "=false;nt"+str_t+"}n"+str_t+"}"; break; } case "SqlDbType.Int": case "SqlDbType.SmallInt": case "SqlDbType.TinyInt": { strReturn += str_t+GeneateModelWithRow_common(TableName.Columns[i].Name, 1); strReturn += "n"+str_t+"{nt"+str_t+"model." + TableName.Columns[i].Name + "=int.Parse(row["" + TableName.Columns[i].Name + ""].ToString());n"+str_t+"}"; break; } default: { strReturn += "n"+str_t+"//model." + TableName.Columns[i].Name + "=row[""+TableName.Columns[i].Name +""].ToString();n"; break; } } } return strReturn; } public string GeneateModelWithRow_common(string colName, int type) { string str_t = "tttt"; if (type == 0) return "n"+str_t+"if(row["" + colName+""] != null)"; else return "n"+str_t+"if(row["" + colName + ""] != null && row[""+ colName + ""].ToString() != "")"; } #region GetReaderMethod /* public string GetReaderMethod(ColumnSchema column) { switch (column.DataType) { case DbType.Byte: { return "GetByte"; } case DbType.Int16: { return "GetInt16"; } case DbType.Int32: { return "GetInt32"; } case DbType.Int64: { return "GetInt64"; } case DbType.AnsiStringFixedLength: case DbType.AnsiString: case DbType.String: case DbType.StringFixedLength: { return "GetString"; } case DbType.Boolean: { return "GetBoolean"; } case DbType.Guid: { return "GetGuid"; } case DbType.Currency: case DbType.Decimal: { return "GetDecimal"; } case DbType.DateTime: case DbType.Date: { return "GetDateTime"; } case DbType.Binary: { return "GetBytes"; } default: { return "__SQL__" + column.DataType; } } } */ #endregion public string GetInParameter(TableSchema TableName) { string strReturn = ""; for (int i=0; i<TableName.PrimaryKey.MemberColumns.Count; i++) { strReturn += GetCSharpTypeFromDBFieldType(TableName.PrimaryKey.MemberColumns[i])+" "+TableName.PrimaryKey.MemberColumns[i].Name+","; } return strReturn.TrimEnd(','); } public string GetAllRows(TableSchema TableName) { string strReturn = ""; for (int i=0; i<TableName.Columns.Count; i++) { strReturn += TableName.Columns[i].Name+","; } return strReturn.TrimEnd(','); } public bool GetSqlParameter(ColumnSchema column) { switch (GetSQLVariableType(column)) { case "SqlDbType.SmallDateTime": case "SqlDbType.Date": case "SqlDbType.DateTime": case "SqlDbType.DateTime2": case "SqlDbType.DateTimeOffset": //case "SqlDbType.Time": case "SqlDbType.Timestamp": case "SqlDbType.Text": case "SqlDbType.NText": case "SqlDbType.Image": return false; default: return true; } } public string ClearPrefix(string name) { //int mIndex=name.IndexOf(TablePrefix); //string strResult=name.Remove(0,TablePrefix.Length); //return strResult; return name; } public string GetKeyMax(TableSchema TableName) { string strReturn = "null"; for (int i=0; i<TableName.PrimaryKey.MemberColumns.Count; i++) { //UniqueIdentifier no max value if (GetSQLVariableType(TableName.PrimaryKey.MemberColumns[i]) != "SqlDbType.UniqueIdentifier") { strReturn=TableName.PrimaryKey.MemberColumns[i].Name; break; } } return strReturn; } </script>