分享一套简单的CodeSmith三层模板

如果要连接mysql，需要安装驱动：

https://cdn.mysql.com//Downloads/Connector-Net/mysql-connector-net-8.0.12.msi

连接字符串

server=172.20.102.148;port=3306;database=metis_dev;uid=root;pwd=123456;SslMode = none;

这里需要注意ssl的设置，否则会报错

model-java

<%-- 
Name:
Author: maomao
Created:<%=Datetime.Now.ToShortDateString() %>
Description: 
--%>
<%@ Template Language="C#" TargetLanguage="Java" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Collections"%>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="数据库" %>
<%@ Property Name="package" Type="String" Description="包名" Category="包名" %>

package <%=package %>.domain;

import java.util.Date;
import com.fasterxml.jackson.annotation.JsonFormat;

public class <%=convertClassName(SourceTable.Name) %>
{
    <%foreach(ColumnSchema column in SourceTable.Columns){ %>
    <%if(column.DataType==DbType.DateTime){ %>
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    <%} %>
    private <%=convertoJavaType(column.SystemType.ToString()) %> <%=convertCamel(column.Name) %>;
    <%} %>
}

<script runat="template">
    private string convertoJavaType(string name){
        switch(name){
            case "System.Int64":
                return "Long";
            case "System.Int32":
                return "Integer";
            case "System.UInt32":
                return "Integer";
            case "System.Int16":
                return "Integer";
            case "System.SByte":
                return "Integer";
            case "System.String":
                return "String";
            case "System.DateTime":
                return "Date";
            default:
                return "unknown";
        }
    }
    private string convertClassName(string name){
        string[] strs=name.Split(new string[1]{"_"},StringSplitOptions.RemoveEmptyEntries);
        string result="";
        for(int i=0;i<strs.Length;i++){
            string firstLetter=strs[i].Substring(0,1);
            string left=strs[i].Substring(1);
            result+=firstLetter.ToUpper()+left;
        }
        return result;
    }
    private string convertCamel(string name){
        string[] strs=name.Split(new string[1]{"_"},StringSplitOptions.RemoveEmptyEntries);
        string result=strs[0];
        for(int i=1;i<strs.Length;i++){
            string firstLetter=strs[i].Substring(0,1);
            string left=strs[i].Substring(1);
            result+=firstLetter.ToUpper()+left;
        }
        return result;
    }
</script>
View Code

 mapper-java

<%-- 
Name:
Author: maomao
Created:<%=Datetime.Now.ToShortDateString() %>
Description: 
--%>
<%@ Template Language="C#" TargetLanguage="Java" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Collections"%>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Linq" %>
<%@ Import Namespace="System.Text" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="数据库" %>
<%@ Property Name="package" Type="String" Description="包名" Category="包名" %>

package <%=package %>.interfaces;

import <%=package %>.domain.<%=convertClassName(SourceTable.Name) %>;
import <%=package %>.interfaces.provider.<%=convertClassName(SourceTable.Name) %>Provider;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;

@Mapper
public interface <%=convertClassName(SourceTable.Name) %>Mapper
{
    @Insert("insert into <%=SourceTable.Name %>(<%=getInsertColumns(SourceTable) %>) values (<%=getInsertColumnValues(SourceTable) %>) ")
    @Options(useGeneratedKeys = true,keyProperty = "id")
    int insert(<%=convertClassName(SourceTable.Name) %> entity);
    @Update("update <%=SourceTable.Name %> set <%=getUpdateColumnValues(SourceTable) %> where id=#{id}")
    void update(<%=convertClassName(SourceTable.Name) %> entity);
    @Delete("delete from <%=SourceTable.Name %> where id = #{id}")
    void deleteById(int id);
    @SelectProvider(type = <%=convertClassName(SourceTable.Name) %>Provider.class,method = "selectWithParam")
    @Results({
            <%foreach(ColumnSchema column in SourceTable.Columns){ %>
            @Result(column = "<%=column.Name %>",property = "<%=convertCamel(column.Name) %>"),
            <%} %>
    })
    List<<%=convertClassName(SourceTable.Name) %>> selectByMap(Map<String,Object> map);
    @Select("select * from <%=SourceTable.Name %> where id=#{id}")
    <%=convertClassName(SourceTable.Name) %> selectById(int id);
}

<script runat="template">
    private string convertoJavaType(string name){
        switch(name){
            case "System.Int64":
                return "Long";
            case "System.Int32":
                return "Integer";
            case "System.UInt32":
                return "Integer";
            case "System.Int16":
                return "Integer";
            case "System.SByte":
                return "Integer";
            case "System.String":
                return "String";
            case "System.DateTime":
                return "Date";
            default:
                return "unknown";
        }
    }
    private string convertClassName(string name){
        string[] strs=name.Split(new string[1]{"_"},StringSplitOptions.RemoveEmptyEntries);
        string result="";
        for(int i=0;i<strs.Length;i++){
            string firstLetter=strs[i].Substring(0,1);
            string left=strs[i].Substring(1);
            result+=firstLetter.ToUpper()+left;
        }
        return result;
    }
    private string convertCamel(string name){
        string[] strs=name.Split(new string[1]{"_"},StringSplitOptions.RemoveEmptyEntries);
        string result=strs[0];
        for(int i=1;i<strs.Length;i++){
            string firstLetter=strs[i].Substring(0,1);
            string left=strs[i].Substring(1);
            result+=firstLetter.ToUpper()+left;
        }
        return result;
    }
    private string getInsertColumns(TableSchema table){
        List<string> list=new List<string>();
        foreach(var columnSchema in table.Columns){
            if(!columnSchema.IsPrimaryKeyMember){
                list.Add(columnSchema.Name);
            }
        }
        return string.Join(",",list.ToArray());
    }
    private string getInsertColumnValues(TableSchema table){
        List<string> list=new List<string>();
        foreach(var columnSchema in table.Columns){
            if(!columnSchema.IsPrimaryKeyMember){
                list.Add("#{"+convertCamel(columnSchema.Name)+"}");
            }
        }
        return string.Join(",",list.ToArray());
    }
    private string getUpdateColumnValues(TableSchema table){
        List<string> list=new List<string>();
        foreach(var columnSchema in table.Columns){
            if(!columnSchema.IsPrimaryKeyMember){
                list.Add(columnSchema.Name+"=#{"+convertCamel(columnSchema.Name)+"}");
            }
        }
        return string.Join(",",list.ToArray());
    }
</script>
View Code

 provider-java

<%-- 
Name:
Author: maomao
Created:<%=Datetime.Now.ToShortDateString() %>
Description: 
--%>
<%@ Template Language="C#" TargetLanguage="Java" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Collections"%>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Linq" %>
<%@ Import Namespace="System.Text" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="数据库" %>
<%@ Property Name="package" Type="String" Description="包名" Category="包名" %>

package <%=package %>.interfaces.provider;

import <%=package %>.domain.<%=convertClassName(SourceTable.Name) %>;
import org.apache.ibatis.jdbc.SQL;
import java.util.Map;


public class <%=convertClassName(SourceTable.Name) %>Provider
{
    public String selectWithParam(Map<String, Object> param) {
        return new SQL() {
            {
                SELECT("*");
                FROM("<%=SourceTable.Name %>");
                <%foreach(ColumnSchema column in SourceTable.Columns){ %>
                if (param.get("<%=convertCamel(column.Name) %>") != null) {
                    WHERE(" <%=column.Name %>=#{<%=convertCamel(column.Name) %>} ");
                }
                <%} %>
            }
        }.toString();
    }
}

<script runat="template">
    private string convertoJavaType(string name){
        switch(name){
            case "System.Int64":
                return "Long";
            case "System.Int32":
                return "Integer";
            case "System.UInt32":
                return "Integer";
            case "System.Int16":
                return "Integer";
            case "System.SByte":
                return "Integer";
            case "System.String":
                return "String";
            case "System.DateTime":
                return "Date";
            default:
                return "unknown";
        }
    }
    private string convertClassName(string name){
        string[] strs=name.Split(new string[1]{"_"},StringSplitOptions.RemoveEmptyEntries);
        string result="";
        for(int i=0;i<strs.Length;i++){
            string firstLetter=strs[i].Substring(0,1);
            string left=strs[i].Substring(1);
            result+=firstLetter.ToUpper()+left;
        }
        return result;
    }
    private string convertCamel(string name){
        string[] strs=name.Split(new string[1]{"_"},StringSplitOptions.RemoveEmptyEntries);
        string result=strs[0];
        for(int i=1;i<strs.Length;i++){
            string firstLetter=strs[i].Substring(0,1);
            string left=strs[i].Substring(1);
            result+=firstLetter.ToUpper()+left;
        }
        return result;
    }
    private string getInsertColumns(TableSchema table){
        List<string> list=new List<string>();
        foreach(var columnSchema in table.Columns){
            if(!columnSchema.IsPrimaryKeyMember){
                list.Add(columnSchema.Name);
            }
        }
        return string.Join(",",list.ToArray());
    }
    private string getInsertColumnValues(TableSchema table){
        List<string> list=new List<string>();
        foreach(var columnSchema in table.Columns){
            if(!columnSchema.IsPrimaryKeyMember){
                list.Add("#{"+convertCamel(columnSchema.Name)+"}");
            }
        }
        return string.Join(",",list.ToArray());
    }
    private string getUpdateColumnValues(TableSchema table){
        List<string> list=new List<string>();
        foreach(var columnSchema in table.Columns){
            if(!columnSchema.IsPrimaryKeyMember){
                list.Add(columnSchema.Name+"=#{"+convertCamel(columnSchema.Name)+"}");
            }
        }
        return string.Join(",",list.ToArray());
    }
</script>
View Code

Model:

<%-- 
Name:
Author: maomao
Created:<%=Datetime.Now.ToShortDateString() %>
Description: 
--%>
<%@ Template Language="C#" TargetLanguage="C#" Inherits="SqlCodeTemplate" %>
<%@ Assembly Name="Codesmith.BaseTemplates" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Text" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="数据库" %>
<%@ Property Name="NameSpace" Type="String" Description="命名空间" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace <%=NameSpace %>
{
    [Serializable]
    public partial class <%=ConvertTablename2Pascal(SourceTable) %>
    {
        #region 属性
        <%foreach(ColumnSchema col in SourceTable.Columns){ %>
        /// <summary>
        /// <%=col.Description %>
        /// </summary>
        public <%=GetCSharpVariableType(col) %> <%=col.Name %> {get;set;}
        <%} %>
        #endregion
        public <%=ConvertTablename2Pascal(SourceTable) %>() { }
        public <%=ConvertTablename2Pascal(SourceTable) %>(DataRow dr)
        {
        #region 属性
        <%foreach(ColumnSchema col in SourceTable.Columns){ %>
            if(dr["<%=col.Name %>"]!=DBNull.Value)
            {
                this.<%=col.Name %>= (<%=GetCSharpVariableType(col) %>)dr["<%=col.Name %>"];
            }
        <%} %>
        #endregion
        }
    }
}
<script runat="template">
 public string Convert2Pascal(ColumnSchema col)
    {
        StringBuilder sb = new StringBuilder();
        string[] strs = col.Name.Split(new char[] { '_'});
        foreach (string str in strs)
        {
            sb.Append(str.Substring(0,1).ToUpper());
            sb.Append(str.Substring(1));
        }
        return sb.ToString();
    }
 public string ConvertTablename2Pascal(TableSchema table)
    {
        StringBuilder sb = new StringBuilder();
        string[] strs = table.Name.Split(new char[] { '_'});
        int index=0;
        foreach (string str in strs)
        {
            if(index==0)
            {
                index++;
                continue;
            }
            sb.Append(str.Substring(0,1).ToUpper());
            sb.Append(str.Substring(1));
        }
        return sb.ToString();
    }
</script>
Model
<%-- 
Name:
Author: maomao
Created:<%=Datetime.Now.ToShortDateString() %>
Description: 
--%>
<%@ Template Language="C#" TargetLanguage="C#" Inherits="SqlCodeTemplate" %>
<%@ Assembly Name="Codesmith.BaseTemplates" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Text" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="数据库" %>
<%@ Property Name="NameSpace" Type="String" Description="命名空间" %>

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;

namespace <%=NameSpace %>
{
    public partial class <%=SourceTable.Name %>Map:EntityTypeConfiguration<<%=SourceTable.Name %>>
    {
        public <%=SourceTable.Name %>Map()
        {
            this.ToTable("<%=SourceTable.Name %>");
            <%if(SourceTable.HasPrimaryKey){ %>
            this.HasKey(t => new { 
            <%foreach(ColumnSchema col in SourceTable.Columns){ %>
            <%if(col.IsPrimaryKeyMember){ %>
                t.<%=col.Name %>,
            <%} %>
            <%} %>
            });
            <%} %>
        <%foreach(ColumnSchema col in SourceTable.Columns){ %>
        <%if((bool)col.ExtendedProperties["CS_isIdentity"].Value){ %>
            this.Property(t => t.<%=col.Name %>).HasColumnName("<%=col.Name %>").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        <%}else{ %>
            <%if(GetCSharpVariableType(col)=="string"&&col.Size!=-1) {%>
            this.Property(t => t.<%=col.Name %>).HasColumnName("<%=col.Name %>").HasMaxLength(<%=col.Size %>);
            <%}else{ %>
            this.Property(t => t.<%=col.Name %>).HasColumnName("<%=col.Name %>");
            <%} %>
            <%} %>
        <%} %>
        }
    }
}
<script runat="template">
 public string Convert2Pascal(ColumnSchema col)
    {
        StringBuilder sb = new StringBuilder();
        string[] strs = col.Name.Split(new char[] { '_'});
        foreach (string str in strs)
        {
            sb.Append(str.Substring(0,1).ToUpper());
            sb.Append(str.Substring(1));
        }
        return sb.ToString();
    }
 
</script>
Map
<%-- 
Name:
Author: maomao
Created:<%=Datetime.Now.ToShortDateString() %>
Description: 
--%>
<%@ Template Language="C#" TargetLanguage="C#" Inherits="SqlCodeTemplate" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Text" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="数据库" %>
<%@ Property Name="NameSpace" Type="String" Description="命名空间" %>

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace <%=NameSpace %>
{
    public static partial class <%=ConvertTablename2Pascal(SourceTable) %>DAL
    {
        public static List<<%=ConvertTablename2Pascal(SourceTable) %>> Search(string sqlStr,List<SqlParameter> pms)
        {
            List<<%=ConvertTablename2Pascal(SourceTable) %>> list = new List<<%=ConvertTablename2Pascal(SourceTable) %>>();
            DataTable table = SqlHelper.ExecuteDataTable(sqlStr,pms.ToArray());
            foreach (DataRow dr in table.Rows)
            {
                <%=ConvertTablename2Pascal(SourceTable) %> model = new <%=ConvertTablename2Pascal(SourceTable) %>(dr);
                list.Add(model);
            }
            return list;
        }
        public static bool Insert(<%=ConvertTablename2Pascal(SourceTable) %> model)
        {
            string sqlStr = "";
            List<string> fileds = new List<string>();
            List<string> pFileds = new List<string>();
            List<SqlParameter> pms = new List<SqlParameter>();
            #region 添加参数
            <%foreach(ColumnSchema col in SourceTable.Columns){ %>
            <%if((bool)(col.ExtendedProperties["CS_IsIdentity"].Value)==true){continue;} %>
            <%if(col.SystemType==typeof(DateTime))
            { %>
            if(model.<%=col.Name %>!=null&&model.<%=col.Name %>!=new DateTime())
            {
                fileds.Add("[<%=col.Name %>]");
                pFileds.Add("@<%=col.Name %>");
                pms.Add(new SqlParameter("<%=col.Name %>",SqlDbType.<%=GetSqlDbType(col) %>,<%=col.Size %>){Value=model.<%=col.Name %>});
            }
            <% }else {%>
            <%if(!col.SystemType.IsValueType){ %>
            if(model.<%=col.Name %>!=null)
            {
                fileds.Add("[<%=col.Name %>]");
                pFileds.Add("@<%=col.Name %>");
                pms.Add(new SqlParameter("<%=col.Name %>", SqlDbType.<%=GetSqlDbType(col) %>,<%=col.Size %>){Value=model.<%=col.Name %>});
            }
            <%} else{%>
            {
                fileds.Add("[<%=col.Name %>]");
                pFileds.Add("@<%=col.Name %>");
                pms.Add(new SqlParameter("<%=col.Name %>", SqlDbType.<%=GetSqlDbType(col) %>,<%=col.Size %>){Value=model.<%=col.Name %>});
            }
            <%} %>
            <%} %>
            <%} %>
            #endregion
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO <%=SourceTable.Name %> (");
            sb.Append(string.Join(",", fileds));
            sb.Append(") values (");
            sb.Append(string.Join(",", pFileds));
            sb.Append(")");
            sqlStr = sb.ToString();
            int i= SqlHelper.ExecuteNonQuery(sqlStr, pms.ToArray());
            return i>0;
        }
        
        public static bool Update(<%=ConvertTablename2Pascal(SourceTable) %> model)
        {
            string sqlStr = "";
            List<string> fileds = new List<string>();
            List<string> pFileds = new List<string>();
            List<SqlParameter> pms = new List<SqlParameter>();
            #region 添加参数
            <%foreach(ColumnSchema col in SourceTable.Columns){ %>
            <%if(col.IsPrimaryKeyMember){ %>
            pFileds.Add("[<%=col.Name %>]=@<%=col.Name %>");
            pms.Add(new SqlParameter("<%=col.Name %>", SqlDbType.<%=GetSqlDbType(col) %>,<%=col.Size %>){Value=model.<%=col.Name %>});
            <%} else{%>
            <%if(col.SystemType==typeof(DateTime)){ %>
            if(model.<%=col.Name %>!=null&&model.<%=col.Name %>!=new DateTime())
            {
                fileds.Add("[<%=col.Name %>]=@<%=col.Name %>");
                pms.Add(new SqlParameter("<%=col.Name %>", SqlDbType.<%=GetSqlDbType(col) %>,<%=col.Size %>){Value=model.<%=col.Name %>});
            }
            <% }else {%> 
            <%if(!col.SystemType.IsValueType){ %>
            if(model.<%=col.Name %>!=null)
            {
                fileds.Add("[<%=col.Name %>]=@<%=col.Name %>");
                pms.Add(new SqlParameter("<%=col.Name %>", SqlDbType.<%=GetSqlDbType(col) %>,<%=col.Size %>){Value=model.<%=col.Name %>});
            }
            <%} else{%>
            fileds.Add("[<%=col.Name %>]=@<%=col.Name %>");
            pms.Add(new SqlParameter("<%=col.Name %>", SqlDbType.<%=GetSqlDbType(col) %>,<%=col.Size %>){Value=model.<%=col.Name %>});
            <%} %>
            <%} %>
            <%} %>
            <%} %>
            #endregion
            StringBuilder sb = new StringBuilder();
            sb.Append("update <%=SourceTable.Name %> set ");
            sb.Append(string.Join(",", fileds));
            sb.Append(" where ");
            sb.Append(string.Join(" and ", pFileds));
            sqlStr = sb.ToString();
            int i= SqlHelper.ExecuteNonQuery(sqlStr, pms.ToArray());
            return i>0;
        }
    }
}
<script runat="template">
 public string Convert2Pascal(string name)
    {
        StringBuilder sb = new StringBuilder();
        string[] strs = name.Split(new char[] { '_'});
        foreach (string str in strs)
        {
            sb.Append(str.Substring(0,1).ToUpper());
            sb.Append(str.Substring(1));
        }
        return sb.ToString();
    }
public string ConvertTablename2Pascal(TableSchema table)
    {
        StringBuilder sb = new StringBuilder();
        string[] strs = table.Name.Split(new char[] { '_'});
        int index=0;
        foreach (string str in strs)
        {
            if(index==0)
            {
                index++;
                continue;
            }
            sb.Append(str.Substring(0,1).ToUpper());
            sb.Append(str.Substring(1));
        }
        return sb.ToString();
    }
</script>
DAL
<%@ Template Language="C#" TargetLanguage="C#" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="数据库" %>
<%@ Property Name="NameSpace" Type="String" Description="命名空间" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace <%=NameSpace %>
{
    public partial class <%=SourceTable.Name %>
    {
        #region 属性
        <%for(int i=0;i<SourceTable.Columns.Count;i++){ %>
            /// <summary>
            /// <%=SourceTable.Columns[i].Description %>
            /// </summary>
            public <%=SourceTable.Columns[i].SystemType %> <%=SourceTable.Columns[i].Name %> {get;set;}
        <%} %>
        #endregion
        public <%=SourceTable.Name %>() { }
        public <%=SourceTable.Name %>(DataRow dr)
        {
        <%for(int i=0;i<SourceTable.Columns.Count;i++){ %>
            if(dr["<%=SourceTable.Columns[i].Name %>"]!=DBNull.Value)
            {
                this.<%=SourceTable.Columns[i].Name %>= (<%=SourceTable.Columns[i].SystemType %>)dr["<%=SourceTable.Columns[i].Name %>"];
            }
        <%} %>
        }
    }
}
View Code

SqlHelper:

<%@ Template Language="C#" TargetLanguage="C#" %>
<%@ Property Name="NameSpace" Type="String" Category="命名空间" %>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace <%=NameSpace %>
{
    static class SqlHelper
    {
        public static readonly string connstr = 
            ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

        public static int ExecuteNonQuery(string sql, 
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static object ExecuteScalar(string sql,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }

        public static DataTable ExecuteDataTable(string sql,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);

                    DataSet dataset = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dataset);
                    return dataset.Tables[0];
                }
            }
        }

        public static object FromDbValue(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return value;
            }
        }

        public static object ToDbValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }
    }
}
View Code

DAL:

<%@ Template Language="C#" TargetLanguage="C#" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="数据库" %>
<%@ Property Name="NameSpace" Type="String" Description="命名空间" %>

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace <%=NameSpace %>
{
    public static partial class <%=SourceTable.Name %>DAL
    {
        public static List<<%=SourceTable.Name %>> Search(string sqlStr,List<SqlParameter> pms)
        {
            List<<%=SourceTable.Name %>> list = new List<<%=SourceTable.Name %>>();
            DataTable table = SqlHelper.ExecuteDataTable(sqlStr,pms.ToArray());
            foreach (DataRow dr in table.Rows)
            {
                <%=SourceTable.Name %> model = new <%=SourceTable.Name %>(dr);
                list.Add(model);
            }
            return list;
        }
        public static bool Insert(<%=SourceTable.Name %> model)
        {
            string sqlStr = "";
            List<string> fileds = new List<string>();
            List<string> pFileds = new List<string>();
            List<SqlParameter> pms = new List<SqlParameter>();
            #region 添加字段
            <%for(int i=0;i<SourceTable.Columns.Count;i++){ %>
            
            <%if((bool)(SourceTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value)==true){continue;} %>
            
                <%if(SourceTable.Columns[i].SystemType==typeof(DateTime))
                { %>
                if(model.<%=SourceTable.Columns[i].Name %>!=null&&model.<%=SourceTable.Columns[i].Name %>!=new DateTime())
                {
                    fileds.Add("[<%=SourceTable.Columns[i].Name %>]");
                    pFileds.Add("@<%=SourceTable.Columns[i].Name %>");
                    pms.Add(new SqlParameter("<%=SourceTable.Columns[i].Name %>", model.<%=SourceTable.Columns[i].Name %>));
                }
                <% }else {%> 
                if(model.<%=SourceTable.Columns[i].Name %>!=null)
                {
                    fileds.Add("[<%=SourceTable.Columns[i].Name %>]");
                    pFileds.Add("@<%=SourceTable.Columns[i].Name %>");
                    pms.Add(new SqlParameter("<%=SourceTable.Columns[i].Name %>", model.<%=SourceTable.Columns[i].Name %>));
                }
                <%} %>
                
            <%} %>
            #endregion
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO <%=SourceTable.Name %> (");
            sb.Append(string.Join(",", fileds));
            sb.Append(") values (");
            sb.Append(string.Join(",", pFileds));
            sb.Append(")");
            sqlStr = sb.ToString();
            int i= SqlHelper.ExecuteNonQuery(sqlStr, pms.ToArray());
            return i>0;
        }
        
        public static bool Update(<%=SourceTable.Name %> model)
        {
            string sqlStr = "";
            List<string> fileds = new List<string>();
            List<string> pFileds = new List<string>();
            List<SqlParameter> pms = new List<SqlParameter>();
            #region 添加字段
            <%for(int i=0;i<SourceTable.Columns.Count;i++){ %>
            
            <%if(SourceTable.Columns[i].IsPrimaryKeyMember){ %>
            pFileds.Add("[<%=SourceTable.Columns[i].Name %>]=@<%=SourceTable.Columns[i].Name %>");
            pms.Add(new SqlParameter("<%=SourceTable.Columns[i].Name %>", model.<%=SourceTable.Columns[i].Name %>));
            <%} else{ %>
            
                <%if(SourceTable.Columns[i].SystemType==typeof(DateTime))
                { %>
                if(model.<%=SourceTable.Columns[i].Name %>!=null&&model.<%=SourceTable.Columns[i].Name %>!=new DateTime())
                {
                    fileds.Add("[<%=SourceTable.Columns[i].Name %>]=@<%=SourceTable.Columns[i].Name %>");
                    pms.Add(new SqlParameter("<%=SourceTable.Columns[i].Name %>", model.<%=SourceTable.Columns[i].Name %>));
                }
                <% }else {%> 
                if(model.<%=SourceTable.Columns[i].Name %>!=null)
                {
                    fileds.Add("[<%=SourceTable.Columns[i].Name %>]=@<%=SourceTable.Columns[i].Name %>");
                    pms.Add(new SqlParameter("<%=SourceTable.Columns[i].Name %>", model.<%=SourceTable.Columns[i].Name %>));
                }
                <%} %>
                <%} %>
            <%} %>
            #endregion
            StringBuilder sb = new StringBuilder();
            sb.Append("update <%=SourceTable.Name %> set ");
            sb.Append(string.Join(",", fileds));
            sb.Append(" where ");
            sb.Append(string.Join(" and ", pFileds));
            sqlStr = sb.ToString();
            int i= SqlHelper.ExecuteNonQuery(sqlStr, pms.ToArray());
            return i>0;
        }
    }
}
View Code

Tables：遍历库中所有表

<%@ CodeTemplate Language="C#" TargetLanguage="Text" Description="List all database tables" %>
<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" Category="Context" Description="Database containing the tables." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
Tables in database "<%= SourceDatabase %>":
<% for (int i = 0; i < SourceDatabase.Tables.Count; i++) { %>
        <%= SourceDatabase.Tables[i].Name %>

<% } %>
View Code
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/uptothesky/p/3855563.html