StoredProcedureGet <%-- Name:SQL StoredProcedureGet Author: XuYabao Description: StoredProcedureGet --%> <%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" ResponseEncoding="UTF-8" Description="Generates a update stored procedure." %> <%@ Assembly Name="SchemaExplorer" %> <%@ Import Namespace="SchemaExplorer" %> <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the stored procedures should be based on." %> <%@ Property Name="Author" Type="String" Default="徐亚豹" Category="Context" Description="Author." %> <%@ Property Name="Description" Type="String" Default="获取" Category="Context" Description="Description." %> <mce:script runat="template"><!-- public string GetSqlParameterStatement(ColumnSchema column) { string param = "@" + column.Name + " " + column.NativeType; switch (column.DataType) { case DbType.Int32: break; case DbType.Int64: break; case DbType.Double: break; case DbType.DateTime: break; case DbType.Decimal: { param += "(" + column.Precision + ", " + column.Scale + ")"; break; } default: { if (column.Size > 0) { param += "(" + column.Size + ")"; } break; } } return param; } // --></mce:script> ----------------------------------------------------------------- -- 描述: <% if (SourceTable.Description.Trim().Length == 0) { %><%= SourceTable.Name.Substring(2) %><% }else{%><%= SourceTable.Description %><% } %><%= Description %> -- 创建: <%= Author %> <%= DateTime.Now.ToString("yyyy-MM-dd") %> ----------------------------------------------------------------- CREATE PROCEDURE p_<%= SourceTable.Name.Substring(2) %>Get <% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %> <%= GetSqlParameterStatement(SourceTable.PrimaryKey.MemberColumns[i]) %><% if (i < SourceTable.PrimaryKey.MemberColumns.Count - 1) { %>,<% } %> --<%= SourceTable.PrimaryKey.MemberColumns[i].Description%> <% } %> AS IF (@<%= SourceTable.PrimaryKey.MemberColumns[0].Name %> > 0) BEGIN SELECT <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %> a.<%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> --<%= SourceTable.Columns[i].Description%> <% } %> FROM v<%= SourceTable.Name.Substring(1) %> a WHERE <% if( SourceTable.PrimaryKey.MemberColumns.Count > 0 ){ %>a.<%= SourceTable.PrimaryKey.MemberColumns[0].Name %> = @<%= SourceTable.PrimaryKey.MemberColumns[0].Name %><% } %> <% for (int i = 1; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %> AND a.<%= SourceTable.PrimaryKey.MemberColumns[i].Name %> = @<%= SourceTable.PrimaryKey.MemberColumns[i].Name %> <% } %> END ELSE BEGIN SELECT <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %> a.<%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> --<%= SourceTable.Columns[i].Description%> <% } %> FROM v<%= SourceTable.Name.Substring(1) %> a ORDER BY a.<%= SourceTable.Columns[0].Name %> END