对表进行增，删，改数据时，每次都需要访问一次数据库，这样会影响性能；如果把查询的数据拼接成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, );
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") == );
}
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