CodeSmith 创建Ado.Net自定义模版(三)

CodeSmith 创建Ado.Net自定义模版(三)


写数据访问层模版:Step3_DAL.cst (接口一这步在这套模版中省略,可以自行补充)


写几个通用方法,比如:得到全部实体集合、通过ID得实体、添加、删除、修改几个方法

 http://www.dtan.so

我这里没有使用sqlhelper之类的帮助类


<%@ CodeTemplate Language="C#" TargetLanguage="C#" ResponseEncoding="UTF-8" Description="实体类" %>
<%@ Property Name="NameSpace" Type="System.String" Default="Service" Category="Property" Description="命名空间" %>
<%@ Property Name="ModelNameSpace" Type="System.String" Default="Model" Category="String" Description="实体层的命名空间" %>
<%@ Property Name="UtilityNameSpace" Type="System.String" Default="Utility" Category="String" Description="工具层的命名空间" %>
<%@ Property Name="Author" Type="System.String" Default="Wilson" Category="Property" Description="作者名" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Optional="True" Category="db" Description="表映射文件" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>

<script runat="template">
    /// <summary>
    ///把数据库类型转化为C#类型
    /// </summary>
    public string DataType2CSharpType(System.Data.DbType dbType)
    {
        switch (dbType)
        {
            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.DateTime2:
                return "DateTime";
            case DbType.DateTimeOffset:
                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.Time:
                return "DateTime";                
            case DbType.UInt16:
                return "ushort";
            case DbType.UInt32:
                return "uint";
            case DbType.UInt64:
                return "ulong";
            case DbType.VarNumeric:
                return "decimal";
            case DbType.Xml:
                return "string";
            default:
                return "object";
        }
    }

    /// <summary>
    /// 字符串转化为小写
    /// </summary>
    public string ToLowercase(string str)
	{
		str = str.ToLower();	
		return str;
	}
	
	/// <summary>
    /// 泛型
    /// </summary>
	public string ToGeneric(string str)
	{
		str = "<" + str + ">";	
		return str;
	}
</script>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using <%=ModelNameSpace%>
using <%=UtilityNameSpace%>

namespace <%=NameSpace%>
{
    /// <summary>
    /// 功能:<%=SourceTable.Name%>类数据访问
    /// 创建人:<%=Author%>
    /// 创建时间:<%=DateTime.Now.ToShortDateString() %>   
    /// </summary>
    public class <%=SourceTable.Name%>Service : IDisposable
    {
        #region 公共
        private System.ComponentModel.Component component = new System.ComponentModel.Component();

        private bool disposed = false;

        ~<%=SourceTable.Name%>Service()
        {
            Dispose(false);
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {

                if (disposing)
                {

                    component.Dispose();
                }

                disposed = true;

            }
        }
        #endregion
				
	    <% if(SourceTable.HasPrimaryKey) %>
		<%{%>
        #region##通过主键ID得到<%=SourceTable.Name%>
        /// <summary>
        /// 功能:通过ID得到<%=SourceTable.Name%>
        /// 创建人:  <%=Author%> 
        /// 创建时间:<%=DateTime.Now.ToShortDateString() %>   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
        public <%=SourceTable.Name%> Get<%=SourceTable.Name%>ById(<%=DataType2CSharpType(SourceTable.PrimaryKey.MemberColumns[0].DataType)%> <%=ToLowercase(SourceTable.PrimaryKey.MemberColumns[0].Name)%>)
        {            
            <%=SourceTable.Name%> obj = null;
            string sql = "select * from <%=SourceTable.Name%> where <%=SourceTable.PrimaryKey.MemberColumns[0].Name%> = @PK";
            SqlConnection conn = new SqlConnection(WebConn.connString);
            conn.Open();
            SqlCommand com = new SqlCommand(sql, conn);
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",<%=SourceTable.PrimaryKey.MemberColumns[0].Size%>)
            };
            sp[0].Value = <%=ToLowercase(SourceTable.PrimaryKey.MemberColumns[0].Name)%>;
            com.Parameters.AddRange(sp);
            SqlDataReader reader = com.ExecuteReader();
            if (reader.Read())
            {
                obj = new <%=SourceTable.Name%>();				
				<%for(int i=0; i < SourceTable.Columns.Count; i ++)%>
				<%{%>
				obj.<%=SourceTable.Columns[i].Name%> = (<%=DataType2CSharpType(SourceTable.Columns[i].DataType)%>)reader["<%=SourceTable.Columns[i].Name%>"];
                <%}%>
				
            }
            reader.Close();
            reader.Dispose();
            com.Dispose();
            conn.Close();
            conn.Dispose();
            return material;
        }
        #endregion
		
		#region##通过主键ID删除<%=SourceTable.Name%>
        /// <summary>
        /// 功能:通过主键ID删除<%=SourceTable.Name%>
        /// 创建人:  <%=Author%> 
        /// 创建时间:<%=DateTime.Now.ToShortDateString() %>   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(<%=DataType2CSharpType(SourceTable.PrimaryKey.MemberColumns[0].DataType)%> <%=ToLowercase(SourceTable.PrimaryKey.MemberColumns[0].Name)%>)
		{
			int i = 0;
			string sql = "delete from <%=SourceTable.Name%> where <%=SourceTable.PrimaryKey.MemberColumns[0].Name%> = @PK";
			SqlConnection conn = new SqlConnection(WebConn.connString);
            conn.Open();
            SqlCommand com = new SqlCommand(sql, conn);
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",<%=SourceTable.PrimaryKey.MemberColumns[0].Size%>)
            };
            sp[0].Value = <%=ToLowercase(SourceTable.PrimaryKey.MemberColumns[0].Name)%>;
			
            com.Parameters.AddRange(sp);
			
			i = com.ExecuteNonQuery();
			
			com.Dispose();
            conn.Close();
            conn.Dispose();
			return i;
		}
		#endregion			
		<%}%>
		
        #region##添加<%=SourceTable.Name%>
        /// <summary>
        /// 功能:添加<%=SourceTable.Name%>
        /// 创建人:  <%=Author%> 
        /// 创建时间:<%=DateTime.Now.ToShortDateString() %>
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add<%=SourceTable.Name%>(<%=SourceTable.Name%> <%=ToLowercase(SourceTable.Name)%>)
        {
            int result = 0;
            string sql = "insert into <%=SourceTable.Name%>(<%for(int i = 0; i < SourceTable.Columns.Count ; i++)%><%{%><%=SourceTable.Columns[i].Name%><%if(i<SourceTable.Columns.Count - 1)%><%{%>,<%}%><%}%>) values(<%for(int i = 0; i < SourceTable.Columns.Count ; i++)%><%{%>@<%=SourceTable.Columns[i].Name%><%if(i<SourceTable.Columns.Count - 1)%><%{%>,<%}%><%}%>);			
            SqlConnection conn = new SqlConnection(WebConn.connString);
            conn.Open();
            SqlCommand com = new SqlCommand(sql, conn);
			
            SqlParameter[] sp = new SqlParameter[]
            {
                <%for(int i=0;i < SourceTable.Columns.Count; i++)%>
				<%{%>
				new SqlParameter("@<%=SourceTable.Columns[i].Name%>",<%=SourceTable.Columns[i].Size%>)<%if(i<SourceTable.Columns.Count - 1)%><%{%>,<%}%>
				<%}%>
            };
			
			<%for(int i=0;i < SourceTable.Columns.Count; i++)%>
			<%{%>
			sp[<%=i%>].Value = <%=ToLowercase(SourceTable.Name)%>.<%=SourceTable.Columns[i].Name%>;
			<%}%>
				
            com.Parameters.AddRange(sp);

            result = com.ExecuteNonQuery();
			
            com.Dispose();
            conn.Close();
            conn.Dispose();
            return result;
        }
        #endregion
		
		#region##得到<%=SourceTable.Name%>集合
		/// <summary>
        /// 功能:得到<%=SourceTable.Name%>集合
        /// 创建人:  <%=Author%> 
        /// 创建时间:<%=DateTime.Now.ToShortDateString() %>
        /// </summary>       
        /// <returns></returns>
		public IList<%=ToGeneric(SourceTable.Name)%> GetList()
		{
		    IList<%=ToGeneric(SourceTable.Name)%> list = new List<%=ToGeneric(SourceTable.Name)%>();
            <%=SourceTable.Name%> obj = null;				
			string sql = "select * from <%=SourceTable.Name%>";			
			SqlConnection conn = new SqlConnection(WebConn.connString);
            conn.Open();
            SqlCommand com = new SqlCommand(sql, conn);
			SqlDataReader reader = com.ExecuteReader();
			while(reader.Read())
			{
				obj = new <%=SourceTable.Name%>();	
				<%for(int i=0; i < SourceTable.Columns.Count; i ++)%>
				<%{%>
				obj.<%=SourceTable.Columns[i].Name%> = (<%=DataType2CSharpType(SourceTable.Columns[i].DataType)%>)reader["<%=SourceTable.Columns[i].Name%>"];
                <%}%>				
				list.Add(obj);				
			}
			
			reader.Close();
            reader.Dispose();
            com.Dispose();
            conn.Close();
            conn.Dispose();
            return list;
		}
		#endregion
		
		#region##修改<%=SourceTable.Name%>
        /// <summary>
        /// 功能:修改<%=SourceTable.Name%>
        /// 创建人:  <%=Author%> 
        /// 创建时间:<%=DateTime.Now.ToShortDateString() %>
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Mod<%=SourceTable.Name%>(<%=SourceTable.Name%> <%=ToLowercase(SourceTable.Name)%>)
        {
            int result = 0;
            string sql = "update <%=SourceTable.Name%> set <%for(int i=0;i<SourceTable.Columns.Count;i++)%><%{%><%=SourceTable.Columns[i].Name%> = @<%=SourceTable.Columns[i].Name%><%if(i < SourceTable.Columns.Count - 1)%><%{%>,<%}%><%}%> where <%=SourceTable.PrimaryKey.MemberColumns[0].Name%> = @<%=SourceTable.PrimaryKey.MemberColumns[0].Name%>";
            SqlConnection conn = new SqlConnection(WebConn.connString);
            conn.Open();
            SqlCommand com = new SqlCommand(sql, conn);
            SqlParameter[] sp = new SqlParameter[]
            {       
				<%for(int i=0;i < SourceTable.Columns.Count; i++)%>
				<%{%>
				new SqlParameter("@<%=SourceTable.Columns[i].Name%>",<%=SourceTable.Columns[i].Size%>)<%if(i<SourceTable.Columns.Count - 1)%><%{%>,<%}%>
				<%}%>
            };
            <%for(int i=0;i < SourceTable.Columns.Count; i++)%>
			<%{%>
			sp[<%=i%>].Value = <%=ToLowercase(SourceTable.Name)%>.<%=SourceTable.Columns[i].Name%>;
			<%}%>
			
            com.Parameters.AddRange(sp);

            result = com.ExecuteNonQuery();
            com.Dispose();
            conn.Close();
            conn.Dispose();
            return result;
        }
        #endregion
		
		#region##修改<%=SourceTable.Name%>
		/// <summary>
        /// 修改<%=SourceTable.Name%>
        /// 创建人:  <%=Author%> 
        /// 创建时间:<%=DateTime.Now.ToShortDateString() %>
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
		public int SelectCount()
		{
			int i = 0;
            string sql = "select count(*) from <%=SourceTable.Name%>";
            SqlConnection conn = new SqlConnection(WebConn.connString);
            conn.Open();
            SqlCommand com = new SqlCommand(sql, conn);
            i = (int)com.ExecuteScalar();
            com.Dispose();
            conn.Dispose();
            conn.Close();
            return i;
		}
		#endregion
                
    }
}



<script runat="template"> :这个标签中,可以写一些自己的方法,供下面调用


其它的就不多说了,大家自行看看


示例下载地址下载


发布了24 篇原创文章 · 获赞 82 · 访问量 11万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览