sql server - sql update for dynamic row number -

i working on asp.net webapi project. 1 functionality of project requirement update craft type. craft table contains columns crafttypekey, crafttypename , crafttypedescription. need update columns crafttypename , crafttypedescription. problem face is, number of rows provided ui dynamic. can array (or list). using sql stored procedure perform db update operations. using sp updates 1 row of table , calls sp in loop till elements updated. problem of approach if having update error(for reason) in between loop, having no way inform ui part, occurred error while updation. last row update status provided ui client(in json format.) need update web api output number of rows updated , number of rows update failure occured.

the code used perform db update follows.

alter procedure [dbo].[usp_updatecrafttype] @orgkey int, @crafttypekey int, @crafttypename varchar(50), @crafttypedescription varchar(128)   begin     set nocount on;     declare @rowcount1          begin                update  [dbo].[crafttypes]             set                [crafttypename]=@crafttypename                ,[crafttypedescription]=@crafttypedescription             crafttypekey=@crafttypekey                             set @rowcount1 = @@rowcount         if @rowcount1 =0             select  -167;         else             select  167;                 end  end 

c# code update craft type api

 public pageviewmodel updatecrafttype( crafttypes[] crafttype)     {       objpageviewmodel = new pageviewmodel();         employeeaccessservice emplaccess = new employeeaccessservice();          (int = 0; < 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 help.....

i need update web api output number of rows updated , number of rows update failure occured.

you can use output clause..see below example gives idea

create table test1234 (id int)  insert test1234  select top 100 n numbers 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 id+1 else 'a' end--error occurs @ 100th row**     output deleted.* @table id=@id     end try     begin catch       select error_message()       select count(distinct id)       @table--when error occurs outputting number of rows updated far     end catch set @id=@id+1 end 

as can see in above script,error occured @ 100 th row,i outputting number of rows updated , number of rows failed can array or list minus this...

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

1.create type table
2.convert array or list datatable
3.pass datatable stored proc

with approach ,your end result or none..

create type test1 table ( cratftype int, columns )  create proc usp_test ( @test test1 readonly ) begin  update  set t.id=t2.id test1 t join @ @test t2 on t1.id=t2.id  end 

hope helps