代码生成工具系列--------（三）根据数据库，生成存储过程
代码生成工具系列--------（三）根据数据库，生成存储过程
该代码生成工具，目前只支持MS SQL2005。简单的把操作数据库操作全用存储过程。
操作语句有：
查询，插入，修改，新加。
一：查询
/// <summary>
/// 查询,sql 语句
/// </summary>
/// <param name="tableName">表对象</param>
/// <returns></returns>
public static StringBuilder ProcSelect(Jiang.CodeEasy.Model.TableProcInfo table)
代码中没有什么，全是用StringBuilder拼成的。
生成的如下：
if exists (select [name] from sys.objects where object_id = object_id(N'[dbo].[$NPSP(City)s]') and type in (N'P',N'pc'))
drop proc [dbo].[$NPSP(City)s]
go
create proc [dbo].[$NPSP(City)s]
(
@top INT = NULL,
@offset INT = NULL,
@rows INT = NULL,
@join NVARCHAR(2048) = NULL,
@filter NVARCHAR(1024) = NULL,
@orderby NVARCHAR(1024) = NULL,
@page_between INT = NULL,
@page_end INT = NULL,
@page_count INT = NULL OUTPUT
)
as
DECLARE @fields NVARCHAR(MAX)
DECLARE @cur_fields NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
DECLARE @tmp_sql NVARCHAR(MAX)
set @fields = N'~.[ZID],~.[ZUP],~.[ZName],~.[ZPostCode]'
SET @cur_fields = replace(@fields, N'~.', N'a.')
SET @sql = N'SELECT '
IF ISNULL(@top, 0) > 0 SET @sql = @sql + N'TOP ' + CAST(@top AS NVARCHAR) + N' '
SET @sql = @sql + @cur_fields + N', ROW_NUMBER() OVER(ORDER BY '
IF @orderby IS NULL SET @sql = @sql + N'a.ZID' ELSE SET @sql = @sql + @orderby
SET @sql = @sql + N') AS rownum FROM [dbo].[City] a'
IF NOT @join IS NULL SET @sql = @sql + @join
IF NOT @filter IS NULL SET @sql = @sql + N' WHERE ' + @filter
IF ISNULL(@offset, 0) > 0
begin
SET @sql = N'SELECT ' + @cur_fields + N', ROW_NUMBER() OVER(ORDER BY a.rownum) AS rownum FROM (' +
@sql + N') a WHERE a.rownum '
IF ISNULL(@rows, 0) > 0 SET @sql = @sql + N' BETWEEN ' + CAST(@offset AS NVARCHAR) + N' AND ' +
CAST(@offset + @rows - 1 AS NVARCHAR)
ELSE SET @sql = @sql + N' >= ' + CAST(@offset AS NVARCHAR)
END
IF ISNULL(@page_between, 0) > 0 AND ISNULL(@page_end, 0) > 0
BEGIN
IF ISNULL(@top, 0) > 0 OR ISNULL(@offset, 0) > 0
BEGIN
SET @tmp_sql = N'WITH t AS(' + @sql + N') SELECT @pc = COUNT(*) FROM t'
EXECUTE sp_executesql @tmp_sql, N'@pc INT OUTPUT', @pc = @page_count OUTPUT
END
ELSE
BEGIN
SET @tmp_sql = N'SELECT @pc = COUNT(*) FROM [dbo].[City] a'
IF NOT @join IS NULL SET @tmp_sql = @tmp_sql + @join
IF NOT @filter IS NULL SET @tmp_sql = @tmp_sql + N' WHERE ' + @filter
EXECUTE sp_executesql @tmp_sql, N'@pc INT OUTPUT', @pc = @page_count OUTPUT
END
SET @tmp_sql = N'WITH t AS(' + @sql + N') SELECT * FROM t WHERE t.rownum BETWEEN @between AND @end'
EXECUTE sp_executesql @tmp_sql, N'@between INT, @end INT',
@between = @page_between, @end = @page_end
end
else
begin
EXECUTE sp_executesql @sql
end
go
其中[dbo].[$NPSP(City)s]是存储过程名称。@join与SQL中的inner join,left join,right join差不多。 @filter就是where后面的过滤。
至于修改，新加，删除就更简单了：
if exists (select [name] from sys.objects where object_id = object_id(N'[dbo].[$NPSP(City)iu]') and type in (N'P',N'pc'))
drop proc [dbo].[$NPSP(City)iu]
go
create proc [dbo].[$NPSP(City)iu]
(
@ZID nvarchar(6) = null,
@ZUP nvarchar(6) = null,
@ZName nvarchar(10) = null,
@ZPostCode nvarchar(6) = null,
@NPSP_F_IS_INSERT bit = NULL
)
as
if @NPSP_F_IS_INSERT = 1
begin
SET NOCOUNT ON
declare @table table([ZID]nvarchar(6),[ZUP]nvarchar(6),[ZName]nvarchar(10),[ZPostCode]nvarchar(6))
insert into [dbo].[City]([ZID],[ZUP],[ZName],[ZPostCode])
output inserted.[ZID],inserted.[ZUP],inserted.[ZName],inserted.[ZPostCode] into @table
values(@ZID,@ZUP,@ZName,@ZPostCode)
SET NOCOUNT OFF
SELECT * FROM @table
end
else
begin
update [dbo].[City] set [ZUP] = @ZUP,[ZName] = @ZName,[ZPostCode] = @ZPostCode where [ZID] = @ZID
end
go
if exists (select [name] from sys.objects where object_id = object_id(N'[dbo].[$NPSP(City)d]') and type in (N'P',N'pc'))
drop proc [dbo].[$NPSP(City)d]
go
create proc [dbo].[$NPSP(City)d]
(
@ZID nvarchar(6) = NULL
)
as
if @ZID is not null
delete from [dbo].[City] where [ZID] = @ZID
go
这就是生成的存储过程，生成过程代码也是把传入表对象字段代入就行。本人的代码写得很乱。如果谁要，可留言，不过，最好联系NIC。看看他是怎么写的。（我都觉得我的方法笨）不过也是一种思路。
