<%--
Name: 数据访问层代码自动生成
Author: 水木
Description: 用来生成三层结构数据访问层代码
--%>
<%@ CodeTemplate Language="C#" TargetLanguage="C#" Src="" ResponseEncoding="UTF-8" Inherits="" Debug="False" Description="Template description here." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Optional="False" Category="SqlTable" Description="请输入数据库表" %>
<%@ Property Name="NameSpace" Type="System.String" Default="DAL" Optional="False" Category="NameSpace" Description="请输入命名空间"%>
<%@ Property Name="Author" Type="System.String" Default="郁时中" Optional="False" Category="other" Description="作者"%>
<%@ Property Name="DefaultDatabase" Type="System.Boolean" Default="True" Optional="False" Category="Booleans" Description="This is a sample boolean property." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Assembly Name="mscorlib" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Collections.Generic" %>
using System.Data;
using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using <%=NameSpace%>.Models;
namespace <%=NameSpace%>.DAL
{
/// <summary>
/// 数据访问类<%=SourceTable.Name%>Service
/// 作者：<%=this.Author%>
/// 创建日期：<%=System.DateTime.Now.ToLongDateString()%>
/// </summary>
public class <%=SourceTable.Name%>Service : <%=NameSpace%>.IDAL.I<%=SourceTable.Name%>Service
{
<% if(!DefaultDatabase) {%>DbHelper DbHelper = new DbHelper();<%}%>
#region CodeSmith自动生成
/// <summary>
/// 得到一个DataSet
/// </summary>
public DataSet GetData(string strWhere)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("select <%=GetColumnNameList(SourceTable)%> ");
strSql.Append(" FROM <%=SourceTable.Name%> ");
if(strWhere.Trim()!="")
{
strSql.Append(" where "+strWhere);
}
return DbHelper.Query(strSql.ToString());
}
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(<%=this.GetModelName(this.SourceTable)%> <%=this.GetObjectName(this.SourceTable)%>)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("insert into <%=SourceTable.Name%>(");
strSql.Append("<%=GetColumnNameList(SourceTable)%>)");
strSql.Append(" values (");
strSql.Append("<%=GetColumnNameListPara(SourceTable)%>)");
SqlParameter[] parameters = {
<%for(int i=0; i<SourceTable.Columns.Count; i++) {%>
<%if(i == SourceTable.Columns.Count -1)
{%>
new SqlParameter("@<%=SourceTable.Columns[i].Name%>", <%=GetSqlDbType(SourceTable.Columns[i])%>,<%=SourceTable.Columns[i].Size%>)
<%}
else
{%>
new SqlParameter("@<%=SourceTable.Columns[i].Name%>", <%=GetSqlDbType(SourceTable.Columns[i])%>,<%=SourceTable.Columns[i].Size%>),
<%}%>
<%}%>
};
<%for(int i=0; i<SourceTable.Columns.Count; i++)
{%>
parameters[<%=i%>].Value=<%=this.GetObjectName(this.SourceTable)%>.<%=SourceTable.Columns[i].Name%>;
<%}%>
DbHelper.ExecuteSql(strSql.ToString(),parameters);
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(<%=this.GetModelName(this.SourceTable)%> <%=this.GetObjectName(this.SourceTable)%>)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("update <%=SourceTable.Name%> set ");
strSql.Append("<%=GetUpdateColumn(SourceTable)%>");
strSql.Append(" where <%=GetKeyColumn(SourceTable).Name%>=@<%=GetKeyColumn(SourceTable).Name%> ");
SqlParameter[] parameters = {
<%for(int i=0; i<SourceTable.Columns.Count; i++)
{%>
<%if(i == SourceTable.Columns.Count -1)
{%>
new SqlParameter("@<%=SourceTable.Columns[i].Name%>", <%=GetSqlDbType(SourceTable.Columns[i])%>,<%=SourceTable.Columns[i].Size%>)
<%}
else{%>
new SqlParameter("@<%=SourceTable.Columns[i].Name%>", <%=GetSqlDbType(SourceTable.Columns[i])%>,<%=SourceTable.Columns[i].Size%>),
<%}%>
<%}%>
};
<%for(int i=0; i<SourceTable.Columns.Count; i++)
{%>
parameters[<%=i%>].Value = <%=this.GetObjectName(this.SourceTable)%>.<%=SourceTable.Columns[i].Name%>;
<%}%>
DbHelper.ExecuteSql(strSql.ToString(),parameters);
}
/// <summary>
/// 删除数据
/// </summary>
public void Delete(<% =GetPrimaryKeyType(SourceTable) + " " + GetKeyColumn(SourceTable).Name.ToLower()%>)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("delete <% =SourceTable.Name%> ");
strSql.Append(" where <% =GetKeyColumn(SourceTable).Name %>=@<% =GetKeyColumn(SourceTable).Name %> ");
SqlParameter[] parameters = {
new SqlParameter("@<% =GetKeyColumn(SourceTable).Name %>", <%=GetKeyColumnSqlType(SourceTable)%>, <%=GetKeyColumnSqlLength(SourceTable)%>)};
parameters[0].Value = <% =GetKeyColumn(SourceTable).Name.ToLower() %>;
DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
}
/// <summary>
/// 得到一个泛型集合
/// </summary>
public IList<<%=this.GetModelName(this.SourceTable)%>> GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select <%=GetColumnNameList(SourceTable)%> ");
strSql.Append(" FROM <%=SourceTable.Name%> ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
IList<<%=this.GetModelName(this.SourceTable)%>> list = new List<<%=this.GetModelName(SourceTable)%>>();
using (SqlDataReader reader = DbHelper.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
<%=this.GetModelName(SourceTable)%> <%=this.GetObjectName(SourceTable)%> = new <%=this.GetModelName(SourceTable)%>();
<% for(int i=0; i<SourceTable.Columns.Count; i++)
{%>
<%=this.GetObjectName(SourceTable)%>.<%=SourceTable.Columns[i].Name%> = <%=GetSqlReader(SourceTable.Columns[i])%>;
<%}%>
list.Add(<%=this.GetObjectName(SourceTable)%>);
}
}
return list;
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public <%=this.GetModelName(SourceTable)%> GetModel(<%=GetPrimaryKeyType(SourceTable) + " " + GetKeyColumn(SourceTable).Name.ToLower()%>)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("select <%=GetColumnNameList(SourceTable)%> from <%=SourceTable.Name%> ");
strSql.Append(" where <%=GetKeyColumn(SourceTable).Name%>=@<%=GetKeyColumn(SourceTable).Name%> ");
SqlParameter[] parameters = {
new SqlParameter("@<% =GetKeyColumn(SourceTable).Name %>", <%=GetKeyColumnSqlType(SourceTable)%>, <%=GetKeyColumnSqlLength(SourceTable)%>)};
parameters[0].Value = <%=GetKeyColumn(SourceTable).Name.ToLower()%>;
<%=this.GetModelName(SourceTable)%> <%=this.GetObjectName(SourceTable)%> = new <%=this.GetModelName(SourceTable)%>();
DataSet ds=DbHelper.Query(strSql.ToString(),parameters);
foreach(DataRow dr in ds.Tables[0].Rows)
{
<% for(int i=0; i<SourceTable.Columns.Count; i++){%>
<%=this.GetObjectName(SourceTable)%>.<%=SourceTable.Columns[i].Name%>=<%=GetDrString(SourceTable.Columns[i])%>
<%}%>
}
if(ds.Tables[0].Rows.Count=0)
{
<%=this.GetObjectName(SourceTable)%>=null;
}
return <%=this.GetObjectName(SourceTable)%>;
}
#endregion
}
}
<script runat="template">
public string GetUpdateColumn(TableSchema table)
{
string strUpdate = "";
for(int i=0; i<table.Columns.Count; i++)
{
if(!table.Columns[i].IsPrimaryKeyMember)
{
if(strUpdate == string.Empty)
strUpdate = "[" + table.Columns[i].Name + "]=@" + table.Columns[i].Name;
else
strUpdate = strUpdate + "," + "[" + table.Columns[i].Name + "]=@" + table.Columns[i].Name;
}
}
return strUpdate;
}
public string GetDrString(ColumnSchema column)
{
string sqlReader = "dr[\"" + column.Name + "\"]";
string csharpType = GetCSharpType(column);
if(csharpType.ToLower() == "string")
return sqlReader + ".ToString();";
string temp = "(" + GetCSharpType(column) + ")" + sqlReader;
if(column.AllowDBNull)
{
temp = sqlReader + ".ToString() == Strimg.Empty ? null : " + temp;
}
temp = temp + ";";
return temp;
}
public string GetDataSet(ColumnSchema column)
{
string sqlReader = "ds.Tables[0].Rows[0][\"" + column.Name + "\"]";
string csharpType = GetCSharpType(column);
if(csharpType.ToLower() == "string")
return sqlReader + ".ToString();";
string temp = "(" + GetCSharpType(column) + ")" + sqlReader;
if(column.AllowDBNull)
{
temp = sqlReader + ".ToString() == Strimg.Empty ? null : " + temp;
}
temp = temp + ";";
return temp;
}
public string GetSqlReader(ColumnSchema column)
{
string sqlReader = "reader[\"" + column.Name + "\"]";
string csharpType = GetCSharpType(column);
if(csharpType.ToLower() == "string")
return sqlReader + ".ToString()";
string temp = "(" + GetCSharpType(column) + ")" + sqlReader;
if(column.AllowDBNull)
{
temp = sqlReader + ".ToString() == Strimg.Empty ? null : " + temp;
}
temp = temp;
return temp;
}
public string GetColumnNameListPara(TableSchema table)
{
string columnList = "";
for(int i=0; i<table.Columns.Count; i++)
{
if(columnList == string.Empty)
columnList = "@" + table.Columns[i].Name;
else
columnList = columnList + ",@" + table.Columns[i].Name;
}
return columnList;
}
public string GetColumnNameList(TableSchema table)
{
string columnList = "";
for(int i=0; i<table.Columns.Count; i++)
{
if(columnList == string.Empty)
columnList = "[" + table.Columns[i].Name + "]";
else
columnList = columnList + ",[" + table.Columns[i].Name + "]";
}
return columnList;
}
public ColumnSchema GetKeyColumn(TableSchema table)
{
ColumnSchema column = null;
for(int i=0; i<table.Columns.Count; i++)
{
if(table.Columns[i].IsPrimaryKeyMember)
column = table.Columns[i];
}
return column;
}
public string GetKeyColumnSqlType(TableSchema table)
{
ColumnSchema column = GetKeyColumn(table);
if(column == null)
return string.Empty;
return GetSqlDbType(column);
}
public int GetKeyColumnSqlLength(TableSchema table)
{
ColumnSchema column = GetKeyColumn(table);
if(column == null)
return 0;
return column.Size;
}
public string GetPrimaryKeyType(TableSchema table)
{
int columnIndex = 0;
for(int i=0; i<table.Columns.Count; i++)
{
if(table.Columns[i].IsPrimaryKeyMember)
columnIndex = i;
}
return GetCSharpType(table.Columns[columnIndex]);
}
public string GetSqlDbType(ColumnSchema column)
{
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 "datetime": return "SqlDbType.DateTime";
case "decimal": return "SqlDbType.Decimal";
case "float": return "SqlDbType.Float";
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.Decimal";
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 "sysname": return "SqlDbType.NChar";
case "text": return "SqlDbType.Text";
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";
default: return "__UNKNOWN__" + column.NativeType;
}
}
public string GetCSharpType(ColumnSchema column)
{
string para = "";
if (column.Name.EndsWith("TypeCode"))
return column.Name;
switch (column.DataType)
{
case DbType.AnsiString:
para = "string";
break;
case DbType.AnsiStringFixedLength:
para = "string";
break;
case DbType.Binary:
para = "byte[]";
break;
case DbType.Boolean:
para = "bool";
break;
case DbType.Byte:
para = "int";
break;
case DbType.Currency:
para = "decimal";
break;
case DbType.Date:
para = "DateTime";
break;
case DbType.DateTime:
para = "DateTime";
break;
case DbType.Decimal:
para = "decimal";
break;
case DbType.Double:
para = "double";
break;
case DbType.Guid:
para = "Guid";
break;
case DbType.Int16:
para = "short";
break;
case DbType.Int32:
para = "int";
break;
case DbType.Int64:
para = "long";
break;
case DbType.Object:
para = "object";
break;
case DbType.SByte:
para = "sbyte";
break;
case DbType.Single:
para = "float";
break;
case DbType.String:
para = "string";
break;
case DbType.StringFixedLength:
para = "string";
break;
case DbType.Time:
para = "TimeSpan";
break;
case DbType.UInt16:
para = "ushort";
break;
case DbType.UInt32:
para = "uint";
break;
case DbType.UInt64:
para = "ulong";
break;
case DbType.VarNumeric:
para = "decimal";
break;
default:
para = "__UNKNOWN__" + column.NativeType;
break;
}
return para;
}
#region Pascal 将字符串转换成Pascal格式(首字母大写:适用于类名,类的属性的名称)
public string ToPascal(string s)
{
return s.Substring(0,1).ToUpper()+s.Substring(1);
}
#endregion
#region 将字符串转换成Camel格式(首字母小写:适用于类的属性的字段)
public string ToCamel(string s)
{
return s.Substring(0,1).ToLower()+s.Substring(1);
}
#endregion
#region 得到实体类型名
public string GetModelName()
{
string s=this.SourceTable.Name;
if(s.EndsWith("s"))
{
s=s.Substring(0,s.Length-1);
}
return this.ToPascal(s);
}
public string GetModelName(TableSchema table)
{
string s=table.Name;
if(s.EndsWith("s"))
{
s=s.Substring(0,s.Length-1);
}
return this.ToPascal(s);
}
#endregion
#region 得到对象的名称
public string GetObjectName(TableSchema table)
{
return this.ToCamel(this.GetModelName(table));
}
#endregion
</script>