--#region [dbo].USP_[TableName]Insert
--DELETE
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_[TableName]Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[USP_[TableName]Insert]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
------------------------------------------------------------------------------------------------------------------------
-- Function: Add new record to table [TableName]
-- Date Created: [CreateDate]
-- Created By: [Owner]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].USP_[TableName]Insert
[FieldsInsert]
@[PriKeyName] int OUTPUT
AS
INSERT INTO [dbo].[[TableName]] (
[FieldsSelectNoPK]
) VALUES (
[FieldsSelectValue]
)
SET @[PriKeyName] = @@IDENTITY
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--#endregion
--#region [dbo].USP_[TableName]Update
--DELETE
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_[TableName]Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[USP_[TableName]Update]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
------------------------------------------------------------------------------------------------------------------------
-- Function: Update an existing record in table [TableName]
-- Date Created: [CreateDate]
-- Created By: [Owner]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].USP_[TableName]Update
[FieldsUpdate]
AS
UPDATE [dbo].[[TableName]] SET
[FieldsUpdateSet]
WHERE
[[PriKeyName]] = @[PriKeyName]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--#endregion
--#region [dbo].USP_[TableName]Delete
--DELETE
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_[TableName]Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[USP_[TableName]Delete]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
------------------------------------------------------------------------------------------------------------------------
-- Function: Delete an existing record from table [TableName]
-- Date Created: [CreateDate]
-- Created By: [Owner]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].USP_[TableName]Delete
@[PriKeyName] int
AS
DELETE FROM [dbo].[[TableName]]
WHERE
[[PriKeyName]] = @[PriKeyName]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--#endregion
--#region [dbo].USP_[TableName]Get
--DELETE
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_[TableName]Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[USP_[TableName]Get]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
------------------------------------------------------------------------------------------------------------------------
-- Function: Select a record from table [TableName]
-- Date Created: [CreateDate]
-- Created By: [Owner]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].USP_[TableName]Get
@[PriKeyName] int
AS
SELECT
[FieldsSelect]
FROM
[dbo].[[TableName]]
WHERE
[[PriKeyName]] = @[PriKeyName]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--#endregion
--#region [dbo].USP_[TableName]GetAllDynamic
--DELETE
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_[TableName]GetAllDynamic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[USP_[TableName]GetAllDynamic]
GO
------------------------------------------------------------------------------------------------------------------------
-- Function: select an existing record in table [TableName]
-- Date Created: [CreateDate]
-- Created By: [Owner]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].USP_[TableName]GetAllDynamic
@WhereCondition nvarchar(500),
@OrderByExpression nvarchar(250) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @SQL nvarchar(3250)
SET @SQL = '
SELECT
[FieldsSelect]
FROM
[dbo].[[TableName]]
'
IF @WhereCondition IS NOT NULL AND LEN(@WhereCondition) > 0
BEGIN
SET @SQL = @SQL + '
WHERE
' + @WhereCondition
END
IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
SET @SQL = @SQL + '
ORDER BY
' + @OrderByExpression
END
EXEC sp_executesql @SQL
GO
--#endregion
--region [dbo].USP_[TableName]GetAllDynamicFromView
--DELETE
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_[TableName]GetAllDynamicFromView]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[USP_[TableName]GetAllDynamicFromView]
GO
------------------------------------------------------------------------------------------------------------------------
-- Function: select an existing record in VIEW VIEW_[TableName]
-- Date Created: [CreateDate]
-- Created By: [Owner]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].USP_[TableName]GetAllDynamicFromView
@WhereCondition nvarchar(500),
@OrderByExpression nvarchar(250) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @SQL nvarchar(3250)
SET @SQL = '
SELECT
*
FROM
[dbo].[view_[TableName]]
'
IF @WhereCondition IS NOT NULL AND LEN(@WhereCondition) > 0
BEGIN
SET @SQL = @SQL + '
WHERE
' + @WhereCondition
END
IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
SET @SQL = @SQL + '
ORDER BY
' + @OrderByExpression
END
EXEC sp_executesql @SQL
GO
--#endregion
--region [dbo].USP_[TableName]GetAllByPageFromView
--DELETE
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[USP_[TableName]GetAllByPageFromView]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[USP_[TableName]GetAllByPageFromView]
GO
------------------------------------------------------------------------------------------------------------------------
-- Function: select an existing record in VIEW VIEW_[TableName]
-- Date Created: [CreateDate]
-- Created By: [Owner]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE USP_[TableName]GetAllByPageFromView
@PageSize int = 20,
@CurrentPage int,
@WhereCondition varchar(1000),
@OrderByExpression varchar(1000),
@RowCount int = 0 out, -- 总的记录数
@PageCount int = 0 out -- 总的页数
AS
BEGIN
DECLARE
@return_value int,
@EXESQL NVARCHAR(2000)
--@WhereCondition
IF @WhereCondition IS NOT NULL AND LEN(@WhereCondition)>0
BEGIN
SET @WhereCondition = ' WHERE ' + @WhereCondition
END
--@OrderByExpression
IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression)>0
BEGIN
SET @OrderByExpression = ' ORDER BY ' + @OrderByExpression
END
SET @EXESQL = 'SELECT * FROM VIEW_[TableName] ' + @WhereCondition + @OrderByExpression
EXEC @return_value = [dbo].[USP_GetAllByPage]
@SelectCommandText = @EXESQL,
@CurrentPageIndex = @CurrentPage,
@PageSize = @PageSize,
@RowCount = @RowCount OUTPUT,
@PageCount = @PageCount OUTPUT
--SELECT @RowCount as N'@RowCount',@PageCount as N'@PageCount'
END
GO
--#endregion
mycoder for sqlserver三层代码生产器
需积分: 9 123 浏览量
2013-05-02
16:31:01
上传
评论 1
收藏 100KB RAR 举报
MyCoder-for sql server.rar （31个子文件） 
MyCoder-for sql server 
About.txt 204B
vssver2.scc 453B Robin.SqlHelper.dll 20KB SchemaExplorer.pdb 32KB MyCoder.exe.config.bak 2KB MyCoder.pdb 70KB 基础文件 BLL oDataValidator.cs 7KB UIDefiniens.cs 5KB CurrentUser.cs 2KB SqlHelper.cs 5KB DragHelper.cs 10KB UIHelper.cs 14KB StoredProcedures USP_GetAllByPage.sql 1KB USP_ExeSql.sql 372B DALFactory DataCache.cs 840B SQLServerDAL SQLUtil-2.cs 15KB SQLUtil.cs 15KB MyCoder.exe.config 1KB
MyCoder.vshost.exe 6KB MyCoder.exe 340KB Robin.Encrypt.dll 6KB OutPutFile SchemaExplorer.dll 32KB MyCoder.vshost.exe.config 1KB Robin.ReadWriteConfig.dll 20KB Template IDAL.cs 2KB vssver2.scc 427B Model.cs 847B StoredProcedures.txt 8KB SQLServerDAL.cs 6KB BLL.cs 7KB DALFactory.cs 1020B资源评论
