7月 31

查询某张表被调用的存储过程

select *   
from sysobjects o, syscomments s   
where o.id = s.id   
and text like '%QNX_Twitter_t%'   
and o.xtype = 'P'

清空数据库告诉缓存

dbcc freeproccache

sql server中分组查询后按排序顺序取每组第一条记录

SELECT * FROM TXN_Log A
 WHERE LogId in
       (SELECT TOP 1 LogId FROM TXN_Log 
         WHERE A.intkey2 = intkey2
                 AND ACTIONId=2 
                 AND PID=2
     AND LGID=1
                 AND IntKey1=229428 ORDER BY LogId DESC)
ORDER BY LogId DESC

如何快速查找使用了某个字段的所有存储过程

SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容  
FROM syscomments sc  
INNER JOIN sysobjects obj ON sc.Id = obj.ID  
WHERE sc.TEXT LIKE '%'   'stayResvSTID'   '%'  
AND TYPE = 'P'  
GO

收缩数据库

DBCC SHRINKDATABASE(StockDB)

执行Store Procedure

exec USP_GetProcessID 'ARCXR00004'

执行Function

select dbo.fn_GetSeqNum('xxxxx')

复制数据表中一部分数据,变更值

--首先把需要复制的数据拉出来
 select * INTO [AppRouting].[dbo].[ApprovalLimitbbb] from   [AppRouting].[dbo].[ApprovalLimit] where appid =24 and cocode ='asdc' and cctrcode = 'AROS'
--变更至
  update [AppRouting].[dbo].[ApprovalLimitbbb] set [CctrCode]='ASLB'
--插回原表中
  insert into [AppRouting].[dbo].[ApprovalLimit] 
  select [AppID]
      ,[ProcessID]
      ,[StepNo]
      ,[CoCode]
      ,[PsnlAreaCode]
      ,[SubAreaCode]
      ,[CctrCode]
      ,[MgmtLvl]
      ,[HODInd]
      ,[LimitRM]
      ,[StartDate]
      ,[EndDate]
      ,[CreatedOn]
      ,[CreatedBy]
      ,[Seq]
  FROM [AppRouting].[dbo].[ApprovalLimitbbb]

查找高CPU的sql语句

SELECT TOP 10 TEXT AS 'SQL Statement'
    ,last_execution_time AS 'Last Execution Time'
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
    ,execution_count AS "Execution Count",qs.total_physical_reads,qs.total_logical_writes
    ,qp.query_plan AS "Query Plan"FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY total_elapsed_time / execution_count DESC
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats 
ORDER BY [Avg CPU Time] DESC

written by ocean

7月 31

有时候要做测试,数据库插入3000W条数据

写了个代码模板,下次调用起来就方便了  

int total = 10000000;
          int segementCount = 200000; 
          for (int n = 1; n <= total / segementCount; n++)
          {
              DataTable dt = new DataTable();
              dt.Columns.AddRange(new DataColumn[]{  
                      new DataColumn("TwitterId",typeof(long)),  
                      new DataColumn("TagName",typeof(string)),});
              for (int index = (n - 1) * segementCount; index < segementCount * n; index++)
              {
                  var dataRow = dt.NewRow();
                  dataRow["TwitterId"] = (long)index;
                  dataRow["TagName"] = "标签" + (index + 10) % 100000;
                  dt.Rows.Add(dataRow);
                  var dataRow2 = dt.NewRow();
                  dataRow2["TwitterId"] = (long)index;
                  dataRow2["TagName"] = "标签" + (index + 20) % 100000;
                  dt.Rows.Add(dataRow2);
                  var dataRow3 = dt.NewRow();
                  dataRow3["TwitterId"] = (long)index;
                  dataRow3["TagName"] = "标签" + (index + 30) % 100000;
                  dt.Rows.Add(dataRow3);
              }
              Inert2DBBySqlBulkCopy(dt, "TestTagName");
              Console.WriteLine("insert" + segementCount * n * 3 / 10000 + "w");
          }
 
 
  public static void Inert2DBBySqlBulkCopy(DataTable data, string tableName)
      {
          using (SqlConnection conn = new SqlConnection(ConnectionString))
          {
              if (conn.State != ConnectionState.Open)
                  conn.Open();
              using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn))
              {
                  sqlBulkCopy.ColumnMappings.Add(0, "TwitterId");
                  sqlBulkCopy.ColumnMappings.Add(1, "TagName"); 
                  sqlBulkCopy.BatchSize = data.Rows.Count; 
                  sqlBulkCopy.BulkCopyTimeout = 600; 
                  sqlBulkCopy.DestinationTableName = tableName; 
                  sqlBulkCopy.WriteToServer(data); 
              }
              if (conn.State != ConnectionState.Closed)
                  conn.Close();
          }
      }

还是挺快的,插入3000W条数据大概10分钟

written by ocean

7月 24
<%@ CodeTemplate Inherits="CodeTemplate" Language="C#" TargetLanguage="C#" Description="生成千牛星所有模板" Debug="True" CompilerVersion="v4.0" ResponseEncoding="UTF-8"%>
<%@ Register Name="EntityTemplate" Template="QNX_Entity.cst" MergeProperties="Flase" ExcludeProperties=""%>
<%@ Register Name="DALTemplate" Template="QNX_DAL.cst" MergeProperties="Flase" ExcludeProperties=""%>
<%@ Register Name="StoreProcedureTemplate" Template="QNX_StoreProc.cst" MergeProperties="Flase" ExcludeProperties=""%>
<%@ Register Name="LogicTemplate" Template="QNX_Logic.cst" MergeProperties="Flase" ExcludeProperties=""%>
 
<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" DeepLoad="True" Optional="False" Category="01. Getting Started - Required" Description="Database that the tables views, and stored procedures should be based on. IMPORTANT!!! If SourceTables and SourceViews are left blank, the Entire Database will then be generated." %>
 
<%
if(this.OutputDirectory=="" || !System.IO.Directory.Exists(this.OutputDirectory))
{
    Debug.WriteLine("----------------------Error: 请核实OutputDirectory是否存在!----------------------");
    return;
}
  
this.GenerateClasses(); 
 
Debug.WriteLine("Create Over!");
%>
 
 
<script runat="template">
 
  
    //生成实体Entity类
    private void GenerateClasses()
    {
        Debug.WriteLine("----------------------实体Entity类 生成Start----------------------");
         
         
        foreach(TableSchema table in this.SourceDatabase.Tables)
        {
            CodeTemplate Template = new EntityTemplate();
            string FileDirectory = this.GetFileDirectory(table.Name,"Entity",".cs");
            Template.SetProperty("SourceTable",table); 
            //文件输出
            Template.RenderToFile(FileDirectory,true);
            Debug.WriteLine(FileDirectory +" 创建成功.");
        }
        Debug.WriteLine("----------------------实体Entity类 生成End----------------------");
         
         
         
        Debug.WriteLine("----------------------DAL类 生成Start----------------------");
        
         
        foreach(TableSchema table in this.SourceDatabase.Tables)
        {
            CodeTemplate dal = new DALTemplate();
            string FileDirectory = this.GetFileDirectory(table.Name,"DAL",".cs");
            dal.SetProperty("SourceTable",table); 
            //文件输出
            dal.RenderToFile(FileDirectory,true);
            Debug.WriteLine(FileDirectory +" 创建成功.");
        }
        Debug.WriteLine("----------------------DAL类 生成End----------------------");
         
         
        Debug.WriteLine("----------------------StoreProcedure 生成Start----------------------");
         
         
        foreach(TableSchema table in this.SourceDatabase.Tables)
        {
            CodeTemplate sql = new StoreProcedureTemplate();
            string FileDirectory = this.GetFileDirectory(table.Name,"SQL",".sql");
            sql.SetProperty("SourceTable",table); 
            //文件输出
            sql.RenderToFile(FileDirectory,true);
            Debug.WriteLine(FileDirectory +" 创建成功.");
        }
        Debug.WriteLine("----------------------StoreProcedure 生成End----------------------");
         
        Debug.WriteLine("----------------------Logic 生成Start----------------------");
         
         
        foreach(TableSchema table in this.SourceDatabase.Tables)
        {
            CodeTemplate sql = new LogicTemplate();
            string FileDirectory = this.GetFileDirectory(table.Name,"Logic",".cs");
            sql.SetProperty("SourceTable",table); 
            //文件输出
            sql.RenderToFile(FileDirectory,true);
            Debug.WriteLine(FileDirectory +" 创建成功.");
        }
        Debug.WriteLine("----------------------Logic 生成End----------------------");
         
    }
     //将字符串首字母转换为大写  
    private string MakeCamel(string value)
    {
        return value.Substring(0, 1).ToUpper() + value.Substring(1);
    }
     
    private string GetFileDirectory(string flolderName,string tabName,string surfix)
    {
        return string.Format("{0}\\{1}\\{2}{3}",OutputDirectory,flolderName,MakeCamel(tabName),surfix);
    }
     
     
    //解决方案输出路径
    private string Directory = String.Empty;
     
    [Editor(typeof(System.Windows.Forms.Design.FolderNameEditor), typeof(System.Drawing.Design.UITypeEditor))] 
    [Optional, NotChecked]
    [DefaultValue("")]
    public string OutputDirectory 
    { 
        get
        {
            return Directory;
        }
        set
        {
            if (value.EndsWith("\\")) value = value.Substring(0, value.Length - 1);
            Directory = value;
        } 
    }
</script>

written by ocean

7月 19

之前一篇随想

http://blog.wx6.org/2012/328.htm

 

最后留下一个疑问,如何卸载dll

这个只能依靠appdomain来执行

写了一个例子,记录一下

首先定义一个接口

记录被调用的dll内部实现

注意:此处因为要跨appdomin调用,因此需要继承自MarshalByRefObject

最后就是调用部分了

基本就是这样了,如果要实现云推送,只不过是dll根据string代码生成dll而已,思路是一样的

written by ocean

7月 17

一、引言

     在实际的项目开发过程中大多数人会利用VSS来管理项目代码文件及其它资源,我们可以把解决方案中要用到的所有的第三方DLL放到解决方案下的某个项目下的一个固定的文件夹中,其它的多个项目都引用该文件夹下的DLL,这样就可以利用VSS来管理项目的DLL,但是这种解决办法还是有些不太合理之处:

     1、一个解决方案下有多个项目,多个项目引用相同的DLL,那么这个专门用来存放DLL的文件夹应该放在与解决方案同级目录下面(也就是与.sln文件在同一目录下),而不是放在多个项目中的一个项目下来供多个其它项目引用, 那么如何把与解决方案文件同目录级的文件夹放入VSS中管理呢?

     2、在团队合作开发中,其它多个团队成员从VSS上首次获取解决方案及项目文件之后不得不手动的把所有的DLL都引用一遍来使得从VSS上获取的代码能编译通过,那么有没有办法把项目对DLL的引用关系也放到VSS中来统一管理呢?

二、解决方法

    1、团队项目中对第三方的DLL的管理由一个团队成员负责。

    2、打开VS并新建一个“空白解决方案”命名为“SolutionTest”,同时选中“添加到源代码管理”,如图:

       

       

  3、在与解决方案文件同级目录下建立“Library”文件夹来存放DLL,把项目所用到的第三方DLL都复制到Library目录下,如图:

       

       

  4、在解决方案中新建一个同名的Library“解决方案文件夹”,如图:

     

     

 5、通过添加“现有项”的方式来把物理磁盘上的”Library”下的所以DLL加入到解决方案下该“解决方案文件夹”下,如图:

   

   

 6、在需要使用这些DLL的项目中添加对这些DLL的引用,并编译整个通过,然后递归的把解决方案签入到VSS

   

   

 7、其它团队成员直接从VSS获取最新版本后,就可以直接编译通过,有用再次的对每个项目添加DLL引用。

 8、若引用的第三方DLL有的新的版本后,应该先将该DLL从VSS签出,然后打开物理的Library文件夹,用新版本的DLL物理替换老版本的 DLL后将其签入到VSS, 其它团队成员直接获取最新版本即可。

 9、若要删除DLL,则直接在VS中的DLL上右键删除即可。

三、结束语

 本人在实际项目中也为如何高效的管理DLL而恼苦,多次Google之后无果,后经多次尝试,终于找到了比较好的解决办法,为了与多人分享才有此博文。

 若有不妥之处欢迎指正。

http://www.cnblogs.com/zghlx/archive/2011/11/24/2262189.html

written by ocean

7月 17

Visual Studio 2010代码编辑器里面有时候会有很多无用的空行

如果少的话手工删除一下还好,如果多可以按照下面的办法删除

其实就是正则替换

Ctrk+H   查找内容^:b*$\n   替换为空

vs2017里面

^\s*\n

替换的时候选择正则表达式

written by ocean

7月 16
<%@ CodeTemplate Language="C#"   ResponseEncoding="UTF-8" TargetLanguage="C#" Src="HeYangCodeSmithBase.cs" Inherits="HeYangCodeSmithBase" Debug="true" CompilerVersion="v4.0" Description="千牛星生成DAL层" %> 
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema"  Optional="False" Category="Required" %>
<%-- Assembly References --%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="CodeSmith.CustomProperties" %>
<%@ Assembly Name="System.Data" %>
<%-- Namespace Imports --%>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="CodeSmith.CustomProperties" %>
<%@ Import Namespace="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
<%@ Import Namespace="System.Collections.Generic" %>    
<% Init(SourceTable); %>
 
 
        /// <summary>
        /// insert <%= ClassName %>
        /// </summary>
        public static <%= GetIdentityReturnType() %> Insert_<%= ClassName %>
        (
            <% if(this.FieldIdentity==null) {%>
            <% for(int index=0;index<this.FA.Count;index++){%>
            <%=Get_Func_Param(this.FA,index) %> 
            <% }}else{ %> 
            <% for(int index=0;index<this.FG.Count;index++){ %>
            <%=Get_Func_Param(this.FG,index) %>
            <% }} %>
        )
        {
            using (SqlConnection conn = new SqlConnection(DNS.QnxCommunityDNS))
            {
                SqlCommand cmd = new SqlCommand("<%= ProcedureName %>_Insert_p", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                <% for(int index=0;index<this.FA.Count;index++){ %>
                <%=Get_FuncCMD_Param(this.FA,index,true) %>
                <% } %>
                conn.Open();
                try
                {
                    <% if(this.FieldIdentity!=null){%>
                    cmd.ExecuteNonQuery();
                    <%= GetIdentityReturnType() %> result = <%= GetIdentityReturnType() %>.Parse(cmd.Parameters["@<%=this.FieldIdentity.FieldName %>"].Value.ToString());
                    return result;
                    <% }else{ %>
                    int returnValue = cmd.ExecuteNonQuery();
                    if (returnValue > 0)
                        return true;
                    return false;                    
                    <% } %>  
                }
                catch (Exception ex)
                {
                    //To do...
                    throw ex;
                }
            }
        }
        /// <summary>
        /// delete <%= ClassName %>
        /// </summary> 
        public static bool Delete_<%= ClassName %>
        (
            <% for(int index=0;index<this.FK.Count;index++){%>
            <%=Get_Func_Param(this.FK,index) %> 
            <% } %>
        )
        {
            using (SqlConnection conn = new SqlConnection(DNS.QnxCommunityDNS))
            {
                SqlCommand cmd = new SqlCommand("<%= ProcedureName %>_Delete_p", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                <% for(int index=0;index<this.FK.Count;index++){ %>
                <%=Get_FuncCMD_Param(this.FK,index,false) %>
                <% } %>
                conn.Open();
                try
                {
                    int rowCount = cmd.ExecuteNonQuery();
                    if (rowCount > 0)
                        return true;
                    return false;
                }
                catch (Exception ex)
                {
                    //To do...
                    throw ex;
                }
            }
        }
        /// <summary>
        /// update <%= ClassName %>
        /// </summary> 
        public static bool Update_<%= ClassName %>
        (
            <% for(int index=0;index<this.FA.Count;index++){%>
            <%=Get_Func_Param(this.FA,index) %> 
            <% } %>
        )
        {
            using (SqlConnection conn = new SqlConnection(DNS.QnxCommunityDNS))
            {
                SqlCommand cmd = new SqlCommand("<%= ProcedureName %>_Update_p", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                <% for(int index=0;index<this.FA.Count;index++){ %>
                <%=Get_FuncCMD_Param(this.FA,index,false) %>
                <% } %>
                conn.Open();
                try
                {
                    int rowCount = cmd.ExecuteNonQuery();
                    if (rowCount > 0)
                        return true;
                    return false;
                }
                catch (Exception ex)
                {
                    //To do...
                    throw ex;
                }
            }
        }
        /// <summary>
        /// get <%= ClassName %>
        /// </summary>         
        public static <%= ClassName %>Model Load_<%= ClassName %>
        (
            <% for(int index=0;index<this.FK.Count;index++){%>
            <%=Get_Func_Param(this.FK,index) %> 
            <% } %>
        )  
        {
            using (SqlConnection conn = new SqlConnection(DNS.QnxCommunityDNS))
            {
                SqlCommand cmd = new SqlCommand("<%= ProcedureName %>_Load_p", conn);
                cmd.CommandType = CommandType.StoredProcedure; 
                <% for(int index=0;index<this.FK.Count;index++){ %>
                <%=Get_FuncCMD_Param(this.FK,index,false) %>
                <% } %>
                conn.Open(); 
                try
                {
                    SqlDataAdapter dp = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    dp.Fill(dt);
                    <%= ClassName %>Model model = DataSetParser.ToModel<<%= ClassName %>Model>(dt);
                    return model; 
                }
                catch (Exception ex)
                {
                    //To do...
                    throw ex;
                }
            }
        } 
        /// <summary>
        /// getlist <%= ClassName %>
        /// </summary>         
        public static List<<%= ClassName %>Model> GetList_<%= ClassName %>
        (
            int pageIndex, 
            int pageSize,
            <% for(int index=0;index<this.FK.Count;index++){%>
            <%=Get_Func_Param(this.FK,index) %> 
            <% } %>
        )
        {
            using (SqlConnection conn = new SqlConnection(DNS.QnxCommunityDNS))
            {
                SqlCommand cmd = new SqlCommand("<%= ProcedureName %>_GetList_p", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                <% for(int index=0;index<this.FK.Count;index++){ %>
                <%=Get_FuncCMD_Param(this.FK,index,false) %>
                <% } %>
                cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", pageSize);
                conn.Open();
                try
                {
                    SqlDataAdapter dp = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    dp.Fill(dt);
                    List<<%= ClassName %>Model> list = DataSetParser.ToModels<<%= ClassName %>Model>(dt);
                    return list;
                }
                catch (Exception ex) {
                    //To do...
                    throw ex;
                }
            }
        }
         
         
        /// <summary>
        /// getlistcount <%= ClassName %>
        /// </summary>     
        public static int GetListCount_<%= ClassName %>
        (
            <% for(int index=0;index<this.FK.Count;index++){%>
            <%=Get_Func_Param(this.FK,index) %> 
            <% } %>
        )
        {
            using (SqlConnection conn = new SqlConnection(DNS.QnxCommunityDNS))
            {
                SqlCommand cmd = new SqlCommand("<%= ProcedureName %>_GetListCount_p", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                <% for(int index=0;index<this.FK.Count;index++){ %>
                <%=Get_FuncCMD_Param(this.FK,index,false) %>
                <% } %> 
                conn.Open();
                try
                {
                    object obj = cmd.ExecuteScalar();
                    if(obj is int)
                        return (int)obj;
                    return 0;
                }
                catch (Exception ex)
                {
                    //To do...
                    throw ex;
                }
            }
        }
         
         
        /// <summary>
        /// getalllist <%= ClassName %>
        /// </summary>         
        public static List<<%= ClassName %>Model> GetAllList_<%= ClassName %>
        (
            <% for(int index=0;index<this.FK.Count;index++){%>
            <%=Get_Func_Param(this.FK,index) %> 
            <% } %>
        )
        {
            using (SqlConnection conn = new SqlConnection(DNS.QnxCommunityDNS))
            {
                SqlCommand cmd = new SqlCommand("<%= ProcedureName %>_GetAllList_p", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                <% for(int index=0;index<this.FK.Count;index++){ %>
                <%=Get_FuncCMD_Param(this.FK,index,false) %>
                <% } %> 
                conn.Open();                
                try
                {
                    SqlDataAdapter dp = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    dp.Fill(dt);
                    List<<%= ClassName %>Model> list = DataSetParser.ToModels<<%= ClassName %>Model>(dt);
                    return list;
                }
                catch (Exception ex)
                {
                    //To do...
                    throw ex;
                }
            }
        }
         
         /// <summary>
        /// getlist <%= ClassName %>
        /// </summary>         
        public static List<<%= ClassName %>Model> GetListAndCount_<%= ClassName %>
        (
            int pageIndex, 
            int pageSize,
            out int totalRec,
            <% for(int index=0;index<this.FK.Count;index++){%>
            <%=Get_Func_Param(this.FK,index) %> 
            <% } %>
        )
        {
            using (SqlConnection conn = new SqlConnection(DNS.QnxCommunityDNS))
            {
                SqlCommand cmd = new SqlCommand("<%= ProcedureName %>_GetListAndCount_p", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                <% for(int index=0;index<this.FK.Count;index++){ %>
                <%=Get_FuncCMD_Param(this.FK,index,false) %>
                <% } %>
                cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", pageSize);
                cmd.Parameters.AddWithValue("@TotalRec", SqlDbType.Int); 
                cmd.Parameters["@TotalRec"].Direction = ParameterDirection.Output;
                     
                conn.Open();
                try
                {
                    SqlDataAdapter dp = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    dp.Fill(dt);
                    totalRec = Convert.ToInt32(cmd.Parameters["@TotalRec"].Value);
                    List<<%= ClassName %>Model> list = DataSetParser.ToModels<<%= ClassName %>Model>(dt);
                    return list;
                }
                catch (Exception ex) {
                    //To do...
                    throw ex;
                }
            }
        }

written by ocean

7月 13
<%@ CodeTemplate Language="C#"  ResponseEncoding="UTF-8" TargetLanguage="SQL"   Src="HeYangCodeSmithBase.cs" Inherits="HeYangCodeSmithBase" Debug="true" CompilerVersion="v4.0" Description="千牛星生成存储过程" %> 
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema"  Optional="False" Category="Required" %>
<%-- Assembly References --%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="CodeSmith.CustomProperties" %>
<%@ Assembly Name="System.Data" %>
<%-- Namespace Imports --%>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="CodeSmith.CustomProperties" %>
<%@ Import Namespace="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
<%@ Import Namespace="System.Collections.Generic" %> 
<% Init(SourceTable); %>
 
      
-- =============================================
-- Author:  heyang
-- Create date: <%= DateTime.Now.ToString("yyyy-MM-dd") %>
-- Description: insert <%= SourceTable %>
-- ============================================= 
CREATE PROCEDURE <%= ProcedureName %>_Insert_p
    <% for(int index=0;index<this.FA.Count;index++){%>
        <%= Get_SP_Param(this.FA,index,true) %>
    <% } %> 
AS
    INSERT INTO [<%= SourceTable.Name %>] (
        <% if(this.FieldIdentity!=null){ %>
        <% for(int index=0;index<this.FG.Count;index++){%>
        <%= Get_Param(this.FG,index,"[","]",",") %> 
        <% }}else{  for(int index=0;index<this.FA.Count;index++){ %>
        <%= Get_Param(this.FA,index,"[","]",",") %>
        <% } } %>
    ) VALUES (
        <% if(this.FieldIdentity!=null){ %>
        <% for(int index=0;index<this.FG.Count;index++){%>
        <%= Get_Param(this.FG,index,"@","",",") %> 
        <% }}else{  for(int index=0;index<this.FA.Count;index++){ %>
        <%= Get_Param(this.FA,index,"@","",",") %>
        <% } } %>
    );    
    <% if(this.FieldIdentity!=null){ %>
    SET @<%=this.FieldIdentity.FieldName %> = SCOPE_IDENTITY();
    <% }%>
 
GO 
 
 
-- =============================================
-- Author:  heyang
-- Create date: <%= DateTime.Now.ToString("yyyy-MM-dd") %>
-- Description: delete <%= SourceTable %>
-- ============================================= 
CREATE PROCEDURE <%= ProcedureName %>_Delete_p
    <% for(int index=0;index<this.FK.Count;index++){%>
    <%= Get_SP_Param(this.FK,index,false) %>
    <% } %> 
AS
    DELETE FROM 
        [<%= SourceTable.Name %>]
    WHERE
        <% for(int index=0;index<this.FK.Count;index++){%>
        <%= Get_Param(this.FK,index,"[","]","") %> = <%= Get_Param(this.FK,index,"@",""," AND") %><%= (index==this.FK.Count-1)?";":string.Empty%>
        <% } %>  
    SELECT @@ROWCOUNT;    
     
     
GO    
     
-- =============================================
-- Author:  heyang
-- Create date: <%= DateTime.Now.ToString("yyyy-MM-dd") %>
-- Description: update <%= SourceTable %>
-- ============================================= 
CREATE PROCEDURE <%= ProcedureName %>_Update_p
    <% for(int index=0;index<this.FA.Count;index++){%>
        <%= Get_SP_Param(this.FA,index,false) %>
    <% } %> 
AS
    UPDATE [<%= SourceTable.Name %>] SET
        <% for(int index=0;index<this.FG.Count;index++){%>
        <%= Get_Param(this.FG,index,"[","]","") %> = <%= Get_Param(this.FG,index,"@","",",") %>
        <% } %> 
    WHERE
        <% for(int index=0;index<this.FK.Count;index++){%>
        <%= Get_Param(this.FK,index,"[","]","") %> = <%= Get_Param(this.FK,index,"@",""," AND") %><%= (index==this.FK.Count-1)?";":string.Empty%>
        <% } %>    
    SELECT @@ROWCOUNT;    
     
GO    
     
     
-- =============================================
-- Author:  heyang
-- Create date: <%= DateTime.Now.ToString("yyyy-MM-dd") %>
-- Description: Load <%= SourceTable %>
-- ============================================= 
CREATE PROCEDURE <%= ProcedureName %>_Load_p
    <% for(int index=0;index<this.FK.Count;index++){%>
    <%= Get_SP_Param(this.FK,index,false) %>
    <% } %>   
AS
    SELECT
        <% for(int index=0;index<this.FA.Count;index++){%>
        <%= Get_Param(this.FA,index,"[","]",",") %>
        <% } %>
    FROM
        [<%= SourceTable.Name %>]
    WHERE
        <% for(int index=0;index<this.FK.Count;index++){%>
        <%= Get_Param(this.FK,index,"[","]","") %> = <%= Get_Param(this.FK,index,"@",""," AND") %>
        <% } %>   
             
             
GO            
     
     
-- =============================================
-- Author:  heyang
-- Create date: <%= DateTime.Now.ToString("yyyy-MM-dd") %>
-- Description: GetAllList <%= SourceTable %>
-- ============================================= 
CREATE PROCEDURE <%= ProcedureName %>_GetAllList_p
    <% for(int index=0;index<this.FK.Count;index++){%>
    <%= Get_SP_Param(this.FK,index,false) %>
    <% } %>   
AS
    SELECT
        <% for(int index=0;index<this.FA.Count;index++){%>
        <%= Get_Param(this.FA,index,"[","]",",") %>
        <% } %>
    FROM
        [<%= SourceTable.Name %>]
    WHERE
        <% for(int index=0;index<this.FK.Count;index++){%>
        <%= Get_Param(this.FK,index,"[","]","") %> = <%= Get_Param(this.FK,index,"@",""," AND") %>
        <% } %>   
    ORDER BY 
        OrderID DESC
             
             
             
-- =============================================
-- Author:  heyang
-- Create date: <%= DateTime.Now.ToString("yyyy-MM-dd") %>
-- Description: GetList <%= SourceTable %>
-- ============================================= 
CREATE PROCEDURE <%= ProcedureName %>_GetList_p
 @PageIndex int,
 @PageSize int,
    <% for(int index=0;index<this.FK.Count;index++){%>
    <%= Get_SP_Param(this.FK,index,false) %>
    <% } %>   
AS
    SELECT 
        *     
    FROM
        (SELECT
        ROW_NUMBER() OVER(ORDER BY OrderID DESC) AS rownum,
        <% for(int index=0;index<this.FA.Count;index++){%>
        <%= Get_Param(this.FA,index,"[","]",",") %>
        <% } %>
        FROM
        [<%= SourceTable.Name %>]
        WHERE
        <% for(int index=0;index<this.FK.Count;index++){%>
        <%= Get_Param(this.FK,index,"[","]","") %> = <%= Get_Param(this.FK,index,"@",""," AND") %>
        <% } %>   
        ) AS T
    WHERE 
        rownum between (( @PageIndex-1) * @PageSize+ 1 ) AND ( (@PageIndex) * @PageSize ) 
         
         
-- =============================================
-- Author:  heyang
-- Create date: <%= DateTime.Now.ToString("yyyy-MM-dd") %>
-- Description: GetListCount <%= SourceTable %>
-- ============================================= 
CREATE PROCEDURE <%= ProcedureName %>_GetListCount_p 
    <% for(int index=0;index<this.FK.Count;index++){%>
    <%= Get_SP_Param(this.FK,index,false) %>
    <% } %>   
AS
    SELECT
        COUNT(1)
    FROM
        [<%= SourceTable.Name %>]
    WHERE
        <% for(int index=0;index<this.FK.Count;index++){%>
        <%= Get_Param(this.FK,index,"[","]","") %> = <%= Get_Param(this.FK,index,"@",""," AND") %>
        <% } %>   
         
         
         
        -- =============================================
-- Author:  heyang
-- Create date: <%= DateTime.Now.ToString("yyyy-MM-dd") %>
-- Description: GetAllList <%= SourceTable %>
-- ============================================= 
CREATE PROCEDURE <%= ProcedureName %>_GetAllList_p
    <% for(int index=0;index<this.FK.Count;index++){%>
    <%= Get_SP_Param(this.FK,index,false) %>
    <% } %>   
AS
    SELECT
        <% for(int index=0;index<this.FA.Count;index++){%>
        <%= Get_Param(this.FA,index,"[","]",",") %>
        <% } %>
    FROM
        [<%= SourceTable.Name %>]
    WHERE
        <% for(int index=0;index<this.FK.Count;index++){%>
        <%= Get_Param(this.FK,index,"[","]","") %> = <%= Get_Param(this.FK,index,"@",""," AND") %>
        <% } %>   
    ORDER BY 
        OrderID DESC
             
             
             
-- =============================================
-- Author:  heyang
-- Create date: <%= DateTime.Now.ToString("yyyy-MM-dd") %>
-- Description: GetListAndCount <%= SourceTable %>
-- ============================================= 
CREATE PROCEDURE <%= ProcedureName %>_GetListAndCount_p
 @PageIndex int,
 @PageSize int,
 @TotalRec INT OUTPUT ,
    <% for(int index=0;index<this.FK.Count;index++){%>
    <%= Get_SP_Param(this.FK,index,false) %>
    <% } %>
AS
     
  
    SELECT  @TotalRec= COUNT(*)  FROM [<%= SourceTable.Name %>] WHERE <% for(int index=0;index<this.FK.Count;index++){%><%= Get_Param(this.FK,index,"[","]","") %> = <%= Get_Param(this.FK,index,"@",""," AND") %><% } %>  ;
    
    SELECT 
        *     
    FROM
        (SELECT
        ROW_NUMBER() OVER(ORDER BY OrderID DESC) AS rownum,
        <% for(int index=0;index<this.FA.Count;index++){%>
        <%= Get_Param(this.FA,index,"[","]",",") %>
        <% } %>
        FROM
        [<%= SourceTable.Name %>]
        WHERE
        <% for(int index=0;index<this.FK.Count;index++){%>
        <%= Get_Param(this.FK,index,"[","]","") %> = <%= Get_Param(this.FK,index,"@",""," AND") %>
        <% } %>   
        ) AS T
    WHERE 
        rownum between (( @PageIndex-1) * @PageSize+ 1 ) AND ( (@PageIndex) * @PageSize )

written by ocean

7月 13

生成Entity模板

<%@ CodeTemplate Language="C#"  ResponseEncoding="UTF-8" TargetLanguage="C#" Src="HeYangCodeSmithBase.cs" Inherits="HeYangCodeSmithBase" Debug="true" CompilerVersion="v4.0" Description="千牛星生成Entity" %> 
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema"  Optional="False" Category="Required" %>
<%-- Assembly References --%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="CodeSmith.CustomProperties" %>
<%@ Assembly Name="System.Data" %>
<%-- Namespace Imports --%>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="CodeSmith.CustomProperties" %>
<%@ Import Namespace="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
<%@ Import Namespace="System.Collections.Generic" %>
   
<% Init(SourceTable); %>
       
       
/*
-- =============================================
-- Author:      heyang
-- Create date: <%= DateTime.Now.ToString("yyyy-MM-dd") %>
-- Description: insert <%= SourceTable %>
-- ============================================= 
*/
using System;
namespace <%= NameSpace %>
{
    [Serializable]
    public class <%= ClassInfoName %>
    {
        <%  foreach(var item in this.FA){ %>
        /// <summary>
        /// <%= item.Description.Length==0?item.FieldName:item.Description %>
        /// </summary>
        public <%= item.CSharpType %>  <%= item.FieldName %> {get;set;}
        <% } %>
    }
}

written by ocean

7月 13

之前在公司写的CodeSmith生成模板

模板基类

最重要的文件

HeYangCodeSmithBase.cs

using System.Collections.Generic;
using SchemaExplorer;
using System.Data;
  
public class HeYangCodeSmithBase : CodeSmith.Engine.CodeTemplate
{
    public void SetTableInfo(TableSchema sourceTable)
    {
        this.TableInfo = sourceTable;
    }
  
  
    #region
    public List<FieldsInfo> FA = new List<FieldsInfo>();
    public List<FieldsInfo> FK = new List<FieldsInfo>();
    public List<FieldsInfo> FG = new List<FieldsInfo>();
    public List<FieldsInfo> FF = new List<FieldsInfo>();
    public SchemaExplorer.TableSchema TableInfo = null;
    public FieldsInfo FieldIdentity = null;
    public string TableName = string.Empty;
    public string ClassName
    {
        get
        {
            return this.TableInfo.Name.Replace("QNX_", "").Replace("_t", "").Replace("_", "");
        }
    }
    public void Init(TableSchema sourceTable)
    {
        this.TableInfo = sourceTable;
        this.TableName = this.TableInfo.Name;
        for (int index = 0; index < TableInfo.Columns.Count; index++)
        {
            FieldsInfo fieldsInfo = new FieldsInfo()
            {
                FieldName = TableInfo.Columns[index].Name,
                IsKey = TableInfo.Columns[index].IsPrimaryKeyMember,
                IsForeignKey = TableInfo.Columns[index].IsForeignKeyMember,
                VariableName = GetVariableName(TableInfo.Columns[index].Name),
                SQLType = DataType2SQLType(TableInfo.Columns[index]),
                CSharpType = DataType2CSharpType(TableInfo.Columns[index]),
                IsIdentity = (bool)(TableInfo.Columns[index].ExtendedProperties["CS_IsIdentity"].Value),
                Size = TableInfo.Columns[index].Size,
                Description = TableInfo.Columns[index].Description,
                Column = TableInfo.Columns[index],
            };
            if (fieldsInfo.IsKey)
                FK.Add(fieldsInfo);
            else
                FG.Add(fieldsInfo);
            if (fieldsInfo.IsForeignKey)
                FF.Add(fieldsInfo);
            if (fieldsInfo.IsIdentity)
                FieldIdentity = fieldsInfo;
            this.FA.Add(fieldsInfo);
        }
  
    }
  
    #endregion 
  
    #region -- modify --
    public string ProcedureName { get { return this.TableInfo.Name; } }
    public string ClassInfoName
    {
        get
        {
            return this.ClassName + "Model";
        }
    }
    public string NameSpace
    {
        get
        {
            return "TestDemo.WebApplication." + this.ClassName;
        }
    }
    #endregion
    #region -- base --
    public string GetDefaultValue(string type)
    {
        switch (type)
        {
            case "long":
                return "0";
        }
        return "_unknow_default_value_" + type;
    }
       
    public string GetIdentityReturnType()
    {
        if (this.FieldIdentity == null)
            return "bool";
        return this.FieldIdentity.CSharpType;
    }
  
    public string Get_Func_Param(List<FieldsInfo> list, int index)
    {
        var fieldInfo = list[index];
        string result = fieldInfo.CSharpType + " " + fieldInfo.VariableName;
        if (index < list.Count - 1)
            result += ",";
        return result;
    }
  
    public string Get_FuncCMD_Param(List<FieldsInfo> list, int index, bool isOut)
    {
        string result = string.Empty;
        var fieldInfo = list[index];
        if (isOut)
        {
            if (fieldInfo.IsIdentity)
            {
                result += "cmd.Parameters.AddWithValue(\"@" + fieldInfo.FieldName + "\", SqlDbType." + fieldInfo.SQLType + ");";
                result += "cmd.Parameters[\"@" + fieldInfo.FieldName + "\"].Direction = ParameterDirection.Output;";
                return result;
            }
        }
        return "cmd.Parameters.AddWithValue(\"@" + fieldInfo.FieldName + "\", " + fieldInfo.VariableName + ");";
    }
    /// <summary>
    /// 获取存储过程的参数字符串
    /// </summary> 
    public string Get_SP_Param(List<FieldsInfo> list, int index, bool outPut)
    {
        var fieldInfo = list[index];
        string result = "@" + fieldInfo.FieldName + " " + fieldInfo.SQLType;
        if (fieldInfo.IsIdentity && outPut)
        {
            result += " " + "OUTPUT";
        }
        else
        {
            if (IsLengthType(fieldInfo.Column.DataType))
                result += "(" + (fieldInfo.Size == -1 ? "MAX" : fieldInfo.Size.ToString()) + ")";
        }
        if (index < list.Count - 1)
            result += ",";
        return result;
    }
    public string Get_Param(List<FieldsInfo> list, int index, string prefix, string suffix, string filling)
    {
        var fieldInfo = list[index];
        return string.Format("{0}{1}{2}{3}", prefix, fieldInfo.FieldName, suffix, index < list.Count - 1 ? filling : string.Empty);
    }
  
    public string Get_Param(string filed, int index, int total)
    {
        return string.Format("{0}{1}", filed, index < total - 1 ? "," : string.Empty);
    }
  
  
    public string GetVariableName(string input)
    {
        string first = input.Substring(0, 1);
        return first.ToLower() + input.Substring(1);
    }
  
    public string DataType2SQLType(ColumnSchema column)
    {
        switch (column.NativeType.ToLower())
        {
            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 DataType2CSharpType(ColumnSchema column)
    {
        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.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 bool IsNumericType(ColumnSchema column)
    {
        switch (column.NativeType.ToLower())
        {
            case "bigint":
            case "bit":
            case "decimal":
            case "float":
            case "int":
            case "money":
            case "numeric":
            case "real":
            case "smallint":
            case "smallmoney":
            case "tinyint":
                return true;
            default:
                return false;
        }
    }
    public bool IsLengthType(DbType dataType)
    {
        switch (dataType)
        {
            case DbType.AnsiString:
            case DbType.AnsiStringFixedLength:
            case DbType.String:
            case DbType.StringFixedLength:
            case DbType.Binary:
                return true;
            default:
                return false;
        }
    }
    #endregion
}
  
public class DataInfo
{
    public string TableName { get; set; }
    public string ClassName { get; set; }
    public FieldsInfo FieldIdentity = new FieldsInfo();//自增主键
    public List<FieldsInfo> FA { get; set; } //Files All
    public int FAC { get { return this.FA.Count; } }
    public List<FieldsInfo> FK { get; set; } //Files Key
    public int FKC { get { return this.FK.Count; } }
    public List<FieldsInfo> FG { get; set; } //Files general
    public int FGC { get { return this.FG.Count; } }
    public List<FieldsInfo> FF { get; set; } //ForeignKey
    public int FFC { get { return this.FF.Count; } }
}
public class FieldsInfo
{
    public bool IsKey { get; set; }//是否主键
    public bool IsForeignKey { get; set; }//是否外键 
    public bool IsIdentity { get; set; }//是否自增主键
    public string FieldName { get; set; }//键名
    public string VariableName { get; set; }//变量名
    public string SQLType { get; set; }//数据库类型
    public string CSharpType { get; set; }//C#类型
    public int Size { get; set; } //长度
    public string Description { get; set; } //描述 
    public ColumnSchema Column { get; set; } //原始列
}

written by ocean