sql update for dynamic row number

Question

I am working on a ASP.net WebAPI project. One functionality of the project requirement is to update craft type. The craft table contains columns for craftTypeKey, craftTypeName and craftTypeDescription. I need to update the columns craftTypeName and craftTypeDescription. The problem I face is, the number of rows provided from the UI is dynamic. It can be an array (or a list). I am using SQL stored procedure to perform the DB update operations. Now I am using an SP which updates one row of the table and calls the SP in a loop till all the elements are updated. The problem of my approach is if I am having an update error(for some reason) in between the loop, I am having no way to inform the UI part, that their occurred an error while updation. Only the last row update status is provided to the UI client(in JSON format.) I need to update the web api output with number of rows successfully updated and number of rows where update failure occured.

The code I used to perform db update is as follows.

ALTER PROCEDURE [dbo].[usp_UpdateCraftType]
@OrgKey INT,
@CraftTypeKey INT,
@CraftTypeName VARCHAR(50),
@CraftTypeDescription VARCHAR(128)   AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @rowcount1 
        BEGIN   
            UPDATE  [dbo].[CraftTypes]
            SET
               [CraftTypeName]=@CraftTypeName
               ,[CraftTypeDescription]=@CraftTypeDescription
            WHERE CraftTypeKey=@CraftTypeKey                
            SET @rowcount1 = @@ROWCOUNT
        IF @rowcount1 =0
            SELECT  -167;
        ELSE
            SELECT  167;        
        END 
END

C# code for update craft type api is

 public PageViewModel UpdateCraftType( CraftTypes[] craftType)
    {
      objPageViewModel = new PageViewModel();
        EmployeeAccessService emplAccess = new EmployeeAccessService();

        for (int i = 0; i < craftType.Length; i++)
        {
            if (craftType[i] != null)
            {
                DataSet dsCraftTypeUpdateResult = new DataSet();
                dsCraftTypeUpdateResult = emplAccess.UpdateCraftType(new {  CraftTypeKey = craftType[i].CraftTypeKey, CraftTypeName = craftType[i].CraftTypeName, CraftTypeDescription = craftType[i].CraftTypeDescription });
                if (dsCraftTypeUpdateResult != null && dsCraftTypeUpdateResult.Tables[0].Rows.Count > 0)
                {
                    if (Convert.ToInt32(dsCraftTypeUpdateResult.Tables[0].Rows[0][0]) == 167)
                    {
                        objPageViewModel.FillPageViewModelObject(ResponseStatus.CraftTypeUpdated);
                    }
                    else
                    {
                        objPageViewModel.FillPageViewModelObject(ResponseStatus.CraftTypeUpdateFailure);
                    }
                }
            }
        }

        return objPageViewModel;
    }
 public DataSet UpdateCraftType(object model)
    {
        DataSet dsCraftTypeUpdated = new DataSet();
        DatabaseModel dbModel = new DatabaseModel();

        IDataAccess dataAceess = new DataAccessObject();
        dbModel.CommandText = StoredProcedure.usp_UpdateCraftType;
        dbModel.Params = model;

        dsCraftTypeUpdated = dataAceess.GetData(dbModel);

        return dsCraftTypeUpdated;
    }

public DataSet GetData(DatabaseModel dbmodel)
    {
        string connectionstring = _context.Database.Connection.ConnectionString; //_context.Database.Connection.ConnectionString;
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionstring);
        builder.ConnectTimeout = Convert.ToInt32(ConfigManager.GetAppSettingValue("ConnectionTimeOut"));
        SqlConnection connection = new SqlConnection(builder.ConnectionString);
        datahelper = new DataAccessHelper(connection);
        SqlDataAdapter da = null;
        DataSet ds = new DataSet();

        connection.Open();
        using (SqlCommand cmd = datahelper.GetCommandObject(dbmodel))
        {
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
        }

        connection.Close();

        return ds;
    }

public class PageViewModel
{
    public int StatusCode { get; set; }
    public string StatusMessage { get; set; }
    public int TotalCount { get; set; }
    public object ModelObject { get; set; }

    public PageViewModel FillPageViewModelObject(ResponseStatus statusCode_i, object modelObject_i = null)
    {
        this.StatusCode = (int)statusCode_i;
        this.StatusMessage = EnumerationHelper.GetEnumDescription(statusCode_i);
        this.ModelObject = modelObject_i;
        return this;
    }
}

Thanks for the Help.....

Answer 1

I need to update the web api output with number of rows successfully updated and number of rows where update failure occured.

You can use output clause..see below example which gives you some idea

CREATE TABLE test1234 (id int)

INSERT INTO test1234

SELECT top 100 n
FROM numbers
WHERE n<=100

SET nocount ON

DECLARE @id int

SET @id=1

DECLARE @table TABLE (id int)

WHILE (@id<=100)
BEGIN
    BEGIN try
      UPDATE test1234
      SET id= CASE WHEN id<>100 THEN id+1 ELSE 'a' END--error occurs at 100th row**
    OUTPUT deleted.* INTO @table WHERE id=@id
    END try
    BEGIN catch
      SELECT error_message()
      SELECT count(DISTINCT id)
      FROM @table--when error occurs we are outputting number of rows updated so far
    END catch
SET @id=@id+1 END

As you can see in above script,error occured at 100 th row,i am outputting number of rows updated and number of rows which failed can be your array or list minus this...

with out using while loop,in summary,you need to pass datatable to stored proc.So here are steps in general..

1.Create type table
2.convert array or list to datatable
3.Pass datatable to stored proc

with this approach ,your end result will be all or none..

create type test1 as table
(
cratftype int,
All columns
)

create proc usp_test
(
@test test1 readonly
)
as
begin

update 
set t.id=t2.id
from
test1 t
join
@
@test t2
on t1.id=t2.id

end

Hope this helps