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>