CSharp 调用存储过程来执行增、删、改操作

最新推荐文章于 2025-03-20 13:52:10 发布

原创最新推荐文章于 2025-03-20 13:52:10 发布 · 350 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#经验分享

本文介绍了一种通过将数据拼接成XML格式并作为参数传递给存储过程的方法，以减少数据库访问次数，从而提高批量增删改操作的效率。具体展示了使用C#与SQL结合实现的示例。

对表进行增，删，改数据时，每次都需要访问一次数据库，这样会影响性能；如果把查询的数据拼接成XML形式，作为一个参数整体传给存储过程来处理，这只访问数据库一次，执行速度会快很多。

1.CSharp 代码如下：

public bool CreateUpdateDeleteHelpCategory(HelpCategoryInfo helpCategory, DataProviderAction action)
{
bool result;
if (null == helpCategory)
{
result = false;
}
else
{
DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_HelpCategoryTest_CreateUpdateDelete");
this.database.AddInParameter(storedProcCommand, "Action", DbType.Int32, (int)action);
this.database.AddOutParameter(storedProcCommand, "Status", DbType.Int32, 4);
if (action != DataProviderAction.Create)
{
this.database.AddInParameter(storedProcCommand, "CategoryId", DbType.Int32, helpCategory.CategoryId);
}
if (action != DataProviderAction.Delete)
{
this.database.AddInParameter(storedProcCommand, "Name", DbType.String, helpCategory.Name);
this.database.AddInParameter(storedProcCommand, "IconUrl", DbType.String, helpCategory.IconUrl);
this.database.AddInParameter(storedProcCommand, "IndexChar", DbType.String, helpCategory.IndexChar);
this.database.AddInParameter(storedProcCommand, "Description", DbType.String, helpCategory.Description);
this.database.AddInParameter(storedProcCommand, "IsShowFooter", DbType.Boolean, helpCategory.IsShowFooter);
}
this.database.ExecuteNonQuery(storedProcCommand);
result = ((int)this.database.GetParameterValue(storedProcCommand, "Status") == 0);
}
return result;
}
2.SQL脚本：

CREATE PROCEDURE [dbo].[cp_HelpCategoryTest_CreateUpdateDelete]
(
@CategoryId INT = NULL,
@Name NVARCHAR(100) = NULL,

@IconUrl NVARCHAR(255) = NULL,
@IndexChar CHAR(1) = NULL,
@Description NVARCHAR(1000) = NULL,
@IsShowFooter BIT = NULL,
@Action INT,
@Status INT OUTPUT
)
AS
DECLARE @DisplaySequence INT
DECLARE @intErrorCode INT
-- 初始化信息
SELECT @Status = 99, @intErrorCode = 0

IF @Action = 2 -- 删除
BEGIN
DELETE FROM HelpCategoriesTest WHERE CategoryId = @CategoryId
IF @@ROWCOUNT = 1
SET @Status = 0
END

IF @Action = 0 -- 创建
BEGIN
IF (SELECT MAX(DisplaySequence) FROM HelpCategoriesTest) IS NULL
SET @DisplaySequence=1
ELSE
SET @DisplaySequence=(SELECT MAX(DisplaySequence) FROM HelpCategoriesTest)+1

INSERT INTO HelpCategoriesTest ([Name], DisplaySequence, IconUrl, IndexChar, Description,IsShowFooter)
VALUES (@Name, @DisplaySequence, @IconUrl, @IndexChar, @Description,@IsShowFooter)

IF @@ROWCOUNT = 1
SET @Status = 0

RETURN
END

IF @Action = 1 -- 修改
BEGIN
SET @DisplaySequence=(SELECT DisplaySequence FROM HelpCategoriesTest WHERE CategoryId=@CategoryId)

-- 修改分类信息
UPDATE HelpCategoriesTest SET [Name] = @Name, DisplaySequence = @DisplaySequence, IconUrl = @IconUrl, IndexChar = @IndexChar, Description = @Description, IsShowFooter = @IsShowFooter
WHERE CategoryId = @CategoryId

SET @intErrorCode = @intErrorCode + @@ERROR

IF @intErrorCode = 0
BEGIN
SET @Status = 0
END

RETURN
END