<%--PrcHelpre.cs--%>
using System;
using System.IO;
using System.Xml;
using System.Data;
using SchemaExplorer;
using System.Web;
using CodeSmith.BaseTemplates;
/// <summary>
/// PK strategy of a table
/// </summary>
public enum PKStrategy {
Identity, Customize
}
public class SQLHelper:OutputFileCodeTemplate
{
public string ReturnDirectory(string str)
{
int index=str.LastIndexOf('/x5C');
return str.Substring(0,index+1);
}
}
<%--GeneratorCode.cst-%>
<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Src="HelpFile/PrcHelpre.cs" Inherits="SQLHelper" Debug="true" Description="Generates a update store procedure." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Default="" Optional="false" Category="Context" Description="Table that the stored procedure should be based on." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.IO" %>
<%this.OutputFile = "D://GeneratorCode"+"//" + "procedure.sql";%>
<% if(!System.IO.Directory.Exists(OutputFile)) System.IO.Directory.CreateDirectory(ReturnDirectory(OutputFile));%>
<%-- <%=ReturnDirectory(OutputFile)%>--%>
<%System.Diagnostics.Process.Start(ReturnDirectory(OutputFile));%> <%-- Open directory --%>
<script runat="template">
// My methods here.
#region setParam
public string GetSqlParameterStatement(ColumnSchema column)
{
string param = "@" + column.Name + " " + column.NativeType;
switch (column.DataType)
{
case DbType.Decimal:
{
param += "(" + column.Precision + ", " + column.Scale + ")";
break;
}
case DbType.Int32: //DbType.int
case DbType.Int16: //DbType.smallint
case DbType.DateTime: //DbType.DateTime
case DbType.Byte: //DbType.tinyint
case DbType.Double: //DbType.double/float
{
break;
}
default:
{
if (column.Size > 0)
{
param += "(" + column.Size + ")";
}
break;
}
}
// Debugger.Break(); not use bugger
return param;
}
#endregion setParam
</script>
-----------------------------------------------------------------
-- Date Created: <%= DateTime.Now.ToLongDateString() %>
-- Name: procedure
-- Author: qianjin
-- Description:prc_Insert<%=SourceTable.Name%>、prc_Delete<%=SourceTable.Name%>、prc_Update<%=SourceTable.Name%>、prc_Select<%=SourceTable.Name%>、
----------------------------------------------------------------- ------------------------------------------------------------------------------------
---------------------------------------INSERT---------------------------------------
if exists(select *from sysobjects where name=prc_Insert<%=SourceTable.Name%>)
drop procedure prc_Insert<%=SourceTable.Name%>
go
CREATE PROCEDURE prc_Insert<%=SourceTable.Name%>
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
AS
begin
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) { %>,<% } %>
<% } %>
)
end
go
---------------------------------------DELETE---------------------------------------
if exists(select *from sysobjects where name=prc_Delete<%=SourceTable.Name%>)
drop procedure prc_Delete<%=SourceTable.Name%>
go
CREATE PROCEDURE prc_Delete<%=SourceTable.Name%>
<% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.PrimaryKey.MemberColumns[i]) %><% if (i < SourceTable.PrimaryKey.MemberColumns.Count - 1) { %>,<% } %>
<% } %>
AS
begin
DELETE FROM <%=SourceTable.Name%>
WHERE
<% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>
<% if (i > 0) { %>AND <% } %>
<%= SourceTable.PrimaryKey.MemberColumns[i].Name %> = @<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>
<% } %>
end
go
---------------------------------------UPDATE---------------------------------------
if exists(select *from sysobjects where name=prc_Update<%=SourceTable.Name%>)
drop procedure prc_Update<%=SourceTable.Name%>
go
CREATE PROCEDURE prc_Update<%=SourceTable.Name%>
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
AS
begin
UPDATE <%= SourceTable.Name %> SET
<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
<%= SourceTable.NonPrimaryKeyColumns[i].Name %> = @<%= SourceTable.NonPrimaryKeyColumns[i].Name %><% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
<% } %>
WHERE
<% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>
<% if (i > 0) { %>AND <% } %>
<%= SourceTable.PrimaryKey.MemberColumns[i].Name %> = @<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>
<% } %>
end
go
---------------------------------------SELECT---------------------------------------
if exists(select *from sysobjects where name=prc_Select<%=SourceTable.Name%>)
drop procedure prc_Select<%=SourceTable.Name%>
go
CREATE PROCEDURE prc_Select<%=SourceTable.Name%>
<% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.PrimaryKey.MemberColumns[i]) %><% if (i < SourceTable.PrimaryKey.MemberColumns.Count - 1) { %>,<% } %>
<% } %>
AS
begin
SELECT <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
FROM <%= SourceTable.Name %>
WHERE
<% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>
<% if (i > 0) { %>AND <% } %>
<%= SourceTable.PrimaryKey.MemberColumns[i].Name %> = @<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>
<% } %>
end
go