动软根据MSSQL数据库生成C#.NET DAL数据层模版代码
<#@ template language="c#" HostSpecific="True" #>
<#@ output extension= ".cs" #>
<#
int searchColumn=0;
TableHost host = (TableHost)(Host);
host.NameSpace = "CarLifeDAL";
string ModelSpace = "SqUserNote";
string DbParaHead=host.DbParaHead;
string DbParaDbType=host.DbParaDbType;
string preParameter=host.preParameter;
ColumnInfo identityKey=host.IdentityKey;
string returnValue = "void";
if (identityKey!=null)
{
returnValue = CodeCommon.DbTypeToCS(identityKey.TypeName);
}
#>
/*
* Copyright (C)
* All rights reserved
* 文件摘要：
* 当前版本：
* 编写日期：
* 设 计：
* 编 写 人：
* 修改记录：
*/
using System;
using System.Text;
using System.Collections.Generic;
using System.Data;
using Common.SqlDBUtility;
using System.Data.SqlClient;
using CarLifeModel;
using Common.Log;
namespace <#= host.NameSpace #>
<# if( host.Folder.Length > 0){ #>
.<#= host.Folder #>
<# } #>
{
<# if( host.TableDescription.Length > 0) {#>
//<#= host.TableDescription #>
<# } #>
public partial class <#= ModelSpace #>DAL:DalBase
{
#region 公共方法
/// <summary>
/// 增加一条数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Add(<#= ModelSpace #> model, bool isTransaction=false)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("insert into <#= host.TableName #>(");
strSql.Append("<# for(int i=1;i< host.Fieldlist.Count;i++) { ColumnInfo c = host.Fieldlist[i]; if (!c.IsIdentity) {#><#= c.ColumnName#><# if (i< host.Fieldlist.Count-1 ) {#>,<#}#><#}}#>");
strSql.Append(") values (");
strSql.Append("<# for(int i=1;i< host.Fieldlist.Count;i++) { ColumnInfo c = host.Fieldlist[i]; if (!c.IsIdentity) {#><#=preParameter#><#= c.ColumnName#><# if (i< host.Fieldlist.Count-1 ) {#>,<#}#><#}}#>");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY;");
SqlParameter[] parameters = {
<# for(int i=1;i< host.Fieldlist.Count;i++)
{
ColumnInfo c = host.Fieldlist[i];
if(c.IsIdentity) continue;
#>
new SqlParameter("<#=preParameter#><#=c.ColumnName#>", SqlDbType.<#=CodeCommon.DbTypeLength(host.DbType, c.TypeName, c.Length)#>) <# if (i< host.Fieldlist.Count-1 ) {#>,<#}#>
<# }#>
};
<# for(int i=1;i< host.Fieldlist.Count;i++){ ColumnInfo c = host.Fieldlist[i]; #>
parameters[<#= i-1 #>].Value = <# if ("uniqueidentifier" == c.TypeName.ToLower()){#>Guid.NewGuid();<#} else {#>model.<#=c.ColumnName#>;<#} #>
<# }#>
object obj;
try
{
if (isTransaction)
{
obj = SqlHelper.ExecuteScalar(myTran, CommandType.Text, strSql.ToString(), parameters);
}
else
{
obj = SqlHelper.ExecuteScalar(this.ConnectionString, CommandType.Text, strSql.ToString(), parameters);
}
}
catch (Exception ex)
{
string exMessage=ex.Message.ToLower();
if (exMessage.Contains("duplicate"))
{
return -2;
}
else
{
LogWriter.Write(ex.Message, System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name + "()");
return -1;
}
}
if (obj == null)
{
return -1;
}
else
{
return Convert.ToInt32(obj);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(<#= ModelSpace #> model, bool isTransaction = false)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("update <#= host.TableName #> set ");
<# for(int i=0;i< host.Fieldlist.Count;i++)
{ ColumnInfo c = host.Fieldlist[i]; #>
<# if (!c.IsIdentity) {#>
strSql.Append(" <#= c.ColumnName #> = <#=preParameter#><#=c.ColumnName#> <# if (i< host.Fieldlist.Count-1 ) {#>,<#}#> ");<# }#>
<# }#>
strSql.Append(" where <#= CodeCommon.GetWhereParameterExpression(host.Keys, true ,host.DbType) #> ");
SqlParameter[] parameters = {
<# for(int i=0;i< host.Fieldlist.Count;i++)
{ ColumnInfo c = host.Fieldlist[i]; #>
new SqlParameter("<#=preParameter#><#=c.ColumnName#>", SqlDbType.<#=CodeCommon.DbTypeLength(host.DbType, c.TypeName, c.Length)#>) <# if (i< host.Fieldlist.Count-1 ) {#>,<#}#>
<# }#>
};
<# n=0; #>
<# foreach (ColumnInfo c in host.Fieldlist) { #>
parameters[<#= n #>].Value = model.<#=c.ColumnName#>;<# n=n+1; #>
<# }#>
int rows;
try
{
if (isTransaction)
{
rows = SqlHelper.ExecuteNonQuery(myTran, CommandType.Text, strSql.ToString(), parameters);
}
else
{
rows = SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, strSql.ToString(), parameters);
}
}
catch (Exception ex)
{
string exMessage=ex.Message.ToLower();
if (exMessage.Contains("duplicate"))
{
return -2;
}
else
{
LogWriter.Write(ex.Message, System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name + "()");
return -1;
}
}
return rows;
}
/// <summary>
/// 删除一条数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool Delete(<#=CodeCommon.GetInParameter(host.Keys, true)#>,bool isTransaction = false)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("delete from <#= host.TableName #> ");
strSql.Append(" where <#= CodeCommon.GetWhereParameterExpression(host.Keys, true, host.DbType)#>");
<#= CodeCommon.GetPreParameter(host.Keys, true, host.DbType) #>
int rows;
try
{
if (isTransaction)
{
rows = SqlHelper.ExecuteNonQuery(myTran, CommandType.Text, strSql.ToString(), parameters);
}
else
{
rows = SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, strSql.ToString(), parameters);
}
}
catch (Exception ex)
{
LogWriter.Write(ex.Message, System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name + "()");
return false;
}
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public <#= ModelSpace #> GetModelByRow(DataRow row)
{
<#=ModelSpace#> model=new <#=ModelSpace#>();
<# foreach (ColumnInfo c in host.Fieldlist) { #>
<# if(CodeCommon.DbTypeToCS(c.TypeName)=="int"||
CodeCommon.DbTypeToCS(c.TypeName)=="long"||
CodeCommon.DbTypeToCS(c.TypeName)=="float"||
CodeCommon.DbTypeToCS(c.TypeName)=="DateTime"||
CodeCommon.DbTypeToCS(c.TypeName)=="decimal"||
CodeCommon.DbTypeToCS(c.TypeName)=="double")
{#>
try
{
if(row["<#=c.ColumnName#>"].ToString()!="")
{
model.<#=c.ColumnName#>=<#=CodeCommon.DbTypeToCS(c.TypeName)#>.Parse(row["<#=c.ColumnName#>"].ToString());
}
}
catch (Exception ex){}
<# } #>
<# if(CodeCommon.DbTypeToCS(c.TypeName)=="string") {#>
try
{
model.<#=c.ColumnName#>= row["<#=c.ColumnName#>"].ToString();
}
catch (Exception ex){}
<# } #>
<# if(CodeCommon.DbTypeToCS(c.TypeName)=="byte[]") {#>
try
{
if(row["<#=c.ColumnName#>"].ToString()!="")
{
model.<#=c.ColumnName#>= (byte[])row["<#=c.ColumnName#>"];
}
}
catch (Exception ex){}
<# } #>
<# if(CodeCommon.DbTypeToCS(c.TypeName)=="Guid") {#>
try
{
if(row["<#=c.ColumnName#>"].ToString()!="")
{
model.<#=c.ColumnName#>= row["<#=c.ColumnName#>"].ToString();
}
}
catch (Exception ex){}
<# } #>
<# if(CodeCommon.DbTypeToCS(c.TypeName)=="bool") {#>
try
{
if(row["<#=c.ColumnName#>"].ToString()!="")
{
if((row["<#=c.ColumnName#>"].ToString()=="1")||(row["<#=c.ColumnName#>"].ToString().ToLower()=="true"))
{
model.<#=c.ColumnName#>= true;
}
else
{
model.<#=c.ColumnName#>= false;
}
}
}
catch (Exception ex){}
<# } #>
<# } #>
return model;
}
/// <summary>
/// 得到一个对象实体
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public <#= ModelSpace #> GetModel(<#= CodeCommon.GetInParameter(host.Keys,true) #>)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("select * ");
strSql.Append(" from <#= host.TableName #> ");
strSql.Append(" where <#= CodeCommon.GetWhereParameterExpression(host.Keys, true, host.DbType) #>");
<#=CodeCommon.GetPreParameter(host.Keys, true, host.DbType)#>
<#=ModelSpace#> model=new <#=ModelSpace#>();
DataSet ds;
try
{
ds=SqlHelper.ExecuteDataSet(this.ConnectionString, CommandType.Text, strSql.ToString(), parameters);
}
catch (Exception ex)
{
LogWriter.Write(ex.Message, System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name + "()");
return null;
}
if(ds.Tables[0].Rows.Count>0)
{
return GetModelByRow(ds.Tables[0].Rows[0]);
}
else
{
return null;
}
}
public List<<#= ModelSpace #>> GetListByTable(DataTable dt)
{
List<<#= ModelSpace #>> list = new List<<#= ModelSpace #>>();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
list.Add(GetModelByRow(dt.Rows[i]));
}
}
return list;
}
/// <summary>
/// 获得数据列表
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public DataSet GetList(string strWhere, params SqlParameter[] cmdParms)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("select * ");
strSql.Append(" FROM <#= host.TableName #> ");
if(strWhere.Trim()!="")
{
strSql.Append(" where "+strWhere);
}
try
{
return SqlHelper.ExecuteDataSet(this.ConnectionString, CommandType.Text, strSql.ToString(), cmdParms);
}
catch (Exception ex)
{
LogWriter.Write(ex.Message, System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name + "()");
return null;
}
}
/// <summary>
/// 获取记录总数
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public int GetRecordCount(string strWhere, params SqlParameter[] cmdParms)
{
try
{
StringBuilder strSql=new StringBuilder();
strSql.Append("select count(0) from <#= host.TableName #> where 1=1 ");
if (strWhere.Trim() != "")
{
strSql.Append(strWhere);
}
strSql.Append(";");
object obj = SqlHelper.ExecuteScalar(this.ConnectionString, CommandType.Text, strSql.ToString(),cmdParms);
if (obj == null)
{
return 0;
}
else
{
return Convert.ToInt32(obj);
}
}
catch (Exception ex)
{
LogWriter.Write(ex.Message, System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name + "()");
return 0;
}
}
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int pageSize,out int total, params SqlParameter[] cmdParms)
{
total = 0;
try
{
int endIndex = startIndex + pageSize;
StringBuilder strSql=new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT top "+endIndex+" ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby );
}
else
{
strSql.Append(" order by <#=host.Fieldlist[searchColumn].ColumnName#> desc");
}
strSql.Append(")AS Row, T.* from <#= host.TableName #> T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Row>{0}", startIndex);
strSql.Append(";select count(0) from <#= host.TableName #> where "+strWhere);
DataSet ds =SqlHelper.ExecuteDataSet(this.ConnectionString, CommandType.Text, strSql.ToString(), cmdParms);
total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
return ds;
}
catch (Exception ex)
{
LogWriter.Write(ex.Message, System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name + "()");
return null;
}
}
#endregion Model
}
}
<#+
int n=0;
#>
