CSharp call stored procedure to perform increment, delete, change operation

To increase the table, delete, change the data, each time you need to access the database, which will affect performance; If the data of the query is stitched into XML form, it is passed as a parameter to the stored procedure as a whole, which accesses the database only once and executes much faster.

The 1.CSHARP code is as follows:

 Public BOOLcreateupdatedeletehelpcategory (Helpcategoryinfo helpcategory, dataprovideraction action) {BOOLresult; 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); }    returnresult;}

2.SQL script:

CREATE PROCEDURE [dbo].[Cp_helpcategorytest_createupdatedelete]    (        @CategoryId INT = NULL,        @Name NVARCHAR( -)= NULL,        @IconUrl NVARCHAR(255)= NULL,        @IndexChar CHAR(1)= NULL,        @Description NVARCHAR( +)= NULL,        @IsShowFooter BIT = NULL,        @Action INT,        @Status INTOUTPUT) as        DECLARE @DisplaySequence INT    DECLARE @intErrorCode INT    --Initializing Information    SELECT @Status =  About,@intErrorCode = 0        IF @Action = 2 --Delete    BEGIN         DELETE  fromHelpcategoriestestWHERECategoryId= @CategoryId        IF @ @ROWCOUNT = 1            SET @Status = 0    END    IF @Action = 0 --Create    BEGIN      IF(SELECT MAX(displaysequence) fromHelpcategoriestest) is NULL          SET @DisplaySequence=1      ELSE         SET @DisplaySequence=(SELECT MAX(displaysequence) fromHelpcategoriestest)+1                        INSERT  intoHelpcategoriestest ([Name], Displaysequence, IconUrl, Indexchar, Description,isshowfooter)VALUES(@Name,@DisplaySequence,@IconUrl,@IndexChar,@Description,@IsShowFooter)                IF @ @ROWCOUNT = 1            SET @Status = 0                RETURN    END    IF @Action = 1 --Modify    BEGIN        SET @DisplaySequence=(SELECTDisplaysequence fromHelpcategoriestestWHERECategoryId=@CategoryId)                    --Modify classification Information        UPDATEHelpcategoriestestSET [Name] = @Name, displaysequence= @DisplaySequence, ICONURL= @IconUrl, Indexchar= @IndexChar, Description= @Description, Isshowfooter= @IsShowFooter        WHERECategoryId= @CategoryId                SET @intErrorCode = @intErrorCode + @ @ERROR                IF @intErrorCode = 0        BEGIN            SET @Status = 0        END                RETURN    END

CSharp call stored procedure to perform increment, delete, change operation