c# - Stored Procedure does not call Update Command -
i have stored procedure re-create info various tables of security db tenant db. in procedure performing insert operation followed update query. both of commands should execute each time procedure when execute procedure not execute update query. here construction of procedure:
complete sp:
alter procedure [ams].[usp_copymasterservicedatatoclient] @serviceids [varchar] (max) execute caller begin declare @processid uniqueidentifier declare @steps int set @processid = newid() set @steps = 1 exec usp_insertserverprocesslog 'usp_copymasterservicedatatoclient' ,@steps ,'starting procedure usp_copymasterservicedatatoclient' ,@processid ,'info' begin seek select * #serviceids ams.splitdelimiterstring(@serviceids, ',') select bs.* #tmpbkgservices [ams].[syn_backgroundservice] bs inner bring together #serviceids si on bs.[bse_id] = si.item bs.bse_isdeleted = 0 select * #tmpbkgserviceswithchilds #tmpbkgservices union select mbs.* #tmpbkgservices bs inner bring together [ams].[syn_backgroundservice] mbs on bs.[bse_id] = mbs.bse_parentserviceid bs.bse_isdeleted = 0 select #tmpbkgserviceswithchilds.bse_id item #serviceid #tmpbkgserviceswithchilds --for storing mappings bkgattributegroupmapping , bkgsvcattributegroupmapping based on service ids select * #tmpbkgattrgrpandbkgsvcattrgrpmapping [ams].[syn_backgroundservice] bs inner bring together [ams].[syn_bkgsvcattributegroupmapping] bpagp on bs.bse_id = bpagp.bsagm_serviceid , bpagp.bsagm_isdeleted = 0 inner bring together [ams].[syn_bkgattributegroupmapping] bsagm on bsagm.bagm_id = bpagp.bsagm_attributegroupmappingid , bsagm.bagm_isdeleted = 0 inner bring together #serviceid si on bs.[bse_id] = si.item bs.bse_isdeleted = 0 --for bkgsvcattributes select distinct [bsa_id] ,[bsa_name] ,[bsa_description] ,[bsa_label] ,[bsa_datatypeid] ,[bsa_isrequired] ,[bsa_reqvalidationmessage] ,[bsa_maxintvalue] ,[bsa_minintvalue] ,[bsa_maxdatevalue] ,[bsa_mindatevalue] ,[bsa_maxlength] ,[bsa_minlength] ,[bsa_iseditable] ,[bsa_issystempreconfiguredq] ,[bsa_code] ,[bsa_copiedfromcode] ,[bsa_isdeleted] ,[bsa_createdbyid] ,[bsa_createddate] ,[bsa_modifiedby] ,[bsa_modifieddate] ,[bsa_active] #tmpbkgsvcattributes #tmpbkgattrgrpandbkgsvcattrgrpmapping bs inner bring together [ams].[syn_bkgsvcattribute] bsa on bs.bagm_bkgsvcatributeid = bsa.bsa_id --and bsa.bsa_active = 1 , bsa.bsa_isdeleted = 0 --for bkgsvcattributeoption select [ebsao_id] ,[ebsao_bkgsvcattributeid] ,[ebsao_optiontext] ,[ebsao_optionvalue] ,[ebsao_isdeleted] ,[ebsao_isactive] ,[ebsao_createdbyid] ,[ebsao_createdon] ,[ebsao_modifiedbyid] ,[ebsao_modifiedon] #tmpbkgsvcattributeoption #tmpbkgsvcattributes tmpbsa inner bring together [ams].[syn_bkgsvcattributeoption] bsao on tmpbsa.bsa_id = bsao.ebsao_bkgsvcattributeid --and bsao.ebsao_isactive = 1 , bsao.ebsao_isdeleted = 0 --for bkgsvgattributesgroup select distinct [bsad_id] ,[bsad_name] ,[bsad_description] ,[bsad_iseditable] ,[bsad_issystempreconfigured] ,[bsad_code] ,[bsad_copiedfromcode] ,[bsad_isdeleted] ,[bsad_createdbyid] ,[bsad_createddate] ,[bsad_modifiedby] ,[bsad_modifieddate] --,[bsad_isrequired] #tmpbkgsvcattributesgroup #tmpbkgattrgrpandbkgsvcattrgrpmapping bs inner bring together [ams].[syn_bkgsvcattributegroup] bsag on bsag.bsad_id = bs.bagm_bkgsvcattributegroupid , bsag.bsad_isdeleted = 0 --for backgroundservices select distinct [bse_id] ,[bse_name] ,[bse_description] ,[bse_svctypeid] ,[bse_iseditable] ,[bse_issystempreconfigured] ,[bse_isdeleted] ,[bse_createdbyid] ,[bse_createddate] ,[bse_modifiedby] ,[bse_modifieddate] ,[bse_clientcount] ,[bse_parentserviceid] #tmpbackgroundservices #tmpbkgserviceswithchilds tmpbs tmpbs.bse_id not in ( select cbs.bse_id ams.backgroundservice cbs ) --for bkgattributegroupmapping select distinct [bagm_id] ,[bagm_bkgsvcattributegroupid] ,[bagm_bkgsvcatributeid] ,[bagm_iseditable] ,[bagm_issystempreconfigured] ,[bagm_code] ,[bagm_copiedfromcode] ,[bagm_isdeleted] ,[bagm_createdby] ,[bagm_createdon] ,[bagm_modifiedby] ,[bagm_modifiedon] ,[bagm_isdisplay] ,[bagm_displaysequence] ,[bagm_isrequired] #tmpbkgattributegroupmapping #tmpbkgattrgrpandbkgsvcattrgrpmapping tmpbagbsg tmpbagbsg.bagm_id not null , tmpbagbsg.bagm_id not in ( select cbagm.bagm_id ams.bkgattributegroupmapping cbagm ) --for bkgattributegroupmapping - entries select distinct [bagm_id] ,[bagm_bkgsvcattributegroupid] ,[bagm_bkgsvcatributeid] ,[bagm_iseditable] ,[bagm_issystempreconfigured] ,[bagm_code] ,[bagm_copiedfromcode] ,[bagm_isdeleted] ,[bagm_createdby] ,[bagm_createdon] ,[bagm_modifiedby] ,[bagm_modifiedon] ,[bagm_isdisplay] ,[bagm_displaysequence] ,[bagm_isrequired] #tmpbkgattributegroupmapping_allentries #tmpbkgattrgrpandbkgsvcattrgrpmapping tmpbagbsg tmpbagbsg.bagm_id not null --for bkgsvcattributegroupmapping select distinct [bsagm_id] ,[bsagm_serviceid] ,[bsagm_attributegroupmappingid] ,[bsagm_iseditable] ,[bsagm_issystempreconfigured] ,[bsagm_isdeleted] ,[bsagm_createdby] ,[bsagm_createdon] ,[bsagm_modifiedby] ,[bsagm_modifiedon] ,[bsagm_code] ,[bsagm_copiedfromcode] #tmpbkgsvcattributegroupmapping #tmpbkgattrgrpandbkgsvcattrgrpmapping tmpbagbsg tmpbagbsg.bsagm_id not null , tmpbagbsg.bsagm_id not in ( select cbsagm.bsagm_id ams.bkgsvcattributegroupmapping cbsagm ) --for bkgsvcattributegroupmapping-allentries select distinct [bsagm_id] ,[bsagm_serviceid] ,[bsagm_attributegroupmappingid] ,[bsagm_iseditable] ,[bsagm_issystempreconfigured] ,[bsagm_isdeleted] ,[bsagm_createdby] ,[bsagm_createdon] ,[bsagm_modifiedby] ,[bsagm_modifiedon] ,[bsagm_code] ,[bsagm_copiedfromcode] #tmpbkgsvcattributegroupmapping_allentries #tmpbkgattrgrpandbkgsvcattrgrpmapping tmpbagbsg tmpbagbsg.bsagm_id not null begin transaction -- inserting info -- 1. insert info tenant attribute security attribute if exists ( select 1 #tmpbkgsvcattributes ) begin insert [ams].[bkgsvcattribute] ( [bsa_id] ,[bsa_name] ,[bsa_description] ,[bsa_label] ,[bsa_datatypeid] ,[bsa_isrequired] ,[bsa_reqvalidationmessage] ,[bsa_maxintvalue] ,[bsa_minintvalue] ,[bsa_maxdatevalue] ,[bsa_mindatevalue] ,[bsa_maxlength] ,[bsa_minlength] ,[bsa_iseditable] ,[bsa_issystempreconfiguredq] ,[bsa_code] ,[bsa_copiedfromcode] ,[bsa_isdeleted] ,[bsa_createdbyid] ,[bsa_createddate] ,[bsa_modifiedby] ,[bsa_modifieddate] ,[bsa_active] ) select [bsa_id] ,[bsa_name] ,[bsa_description] ,[bsa_label] ,[bsa_datatypeid] ,[bsa_isrequired] ,[bsa_reqvalidationmessage] ,[bsa_maxintvalue] ,[bsa_minintvalue] ,[bsa_maxdatevalue] ,[bsa_mindatevalue] ,[bsa_maxlength] ,[bsa_minlength] ,[bsa_iseditable] ,[bsa_issystempreconfiguredq] ,[bsa_code] ,[bsa_copiedfromcode] ,[bsa_isdeleted] ,[bsa_createdbyid] ,getutcdate() --[bsa_createddate] ,[bsa_modifiedby] ,[bsa_modifieddate] ,[bsa_active] #tmpbkgsvcattributes #tmpbkgsvcattributes.bsa_id not in ( select cbsa.bsa_id ams.bkgsvcattribute cbsa ) end -- 2. insert info tenant attributegroup security attribute grouping if exists ( select 1 #tmpbkgsvcattributesgroup ) begin insert [ams].[bkgsvcattributegroup] ( [bsad_id] ,[bsad_name] ,[bsad_description] ,[bsad_iseditable] ,[bsad_issystempreconfigured] ,[bsad_code] ,[bsad_copiedfromcode] ,[bsad_isdeleted] ,[bsad_createdbyid] ,[bsad_createddate] ,[bsad_modifiedby] ,[bsad_modifieddate] ) --,[bsad_isrequired] select [bsad_id] ,[bsad_name] ,[bsad_description] ,[bsad_iseditable] ,[bsad_issystempreconfigured] ,[bsad_code] ,[bsad_copiedfromcode] ,[bsad_isdeleted] ,[bsad_createdbyid] ,getutcdate() --[bsad_createddate] ,[bsad_modifiedby] ,[bsad_modifieddate] --,[bsad_isrequired] #tmpbkgsvcattributesgroup #tmpbkgsvcattributesgroup.bsad_id not in ( select cbsad.bsad_id ams.bkgsvcattributegroup cbsad ) end -- 3. insert info tenant backgroundservice security backgroundservices if exists ( select 1 #tmpbackgroundservices ) begin insert [ams].[backgroundservice] ( [bse_id] ,[bse_name] ,[bse_description] ,[bse_svctypeid] ,[bse_iseditable] ,[bse_issystempreconfigured] ,[bse_isdeleted] ,[bse_createdbyid] ,[bse_createddate] ,[bse_modifiedby] ,[bse_modifieddate] ,[bse_clientcount] ,[bse_parentserviceid] ) select [bse_id] ,[bse_name] ,[bse_description] ,[bse_svctypeid] ,[bse_iseditable] ,[bse_issystempreconfigured] ,[bse_isdeleted] ,[bse_createdbyid] ,getutcdate() --[bse_createddate] ,[bse_modifiedby] ,[bse_modifieddate] ,null ,[bse_parentserviceid] #tmpbackgroundservices end -- 4. insert info tenant [bkgattributegroupmapping] security [bkgattributegroupmapping] if exists ( select 1 #tmpbkgattributegroupmapping ) begin insert [ams].[bkgattributegroupmapping] ( [bagm_id] ,[bagm_bkgsvcattributegroupid] ,[bagm_bkgsvcatributeid] ,[bagm_iseditable] ,[bagm_issystempreconfigured] ,[bagm_code] ,[bagm_copiedfromcode] ,[bagm_isdeleted] ,[bagm_createdby] ,[bagm_createdon] ,[bagm_modifiedby] ,[bagm_modifiedon] ,[bagm_isdisplay] ,[bagm_displaysequence] ,[bagm_isrequired] ) select [bagm_id] ,[bagm_bkgsvcattributegroupid] ,[bagm_bkgsvcatributeid] ,[bagm_iseditable] ,[bagm_issystempreconfigured] ,[bagm_code] ,[bagm_copiedfromcode] ,[bagm_isdeleted] ,[bagm_createdby] ,getutcdate() --[bagm_createdon] ,[bagm_modifiedby] ,[bagm_modifiedon] ,[bagm_isdisplay] ,[bagm_displaysequence] ,[bagm_isrequired] #tmpbkgattributegroupmapping end -- 5. insert info tenant [bkgsvcattributegroupmapping] security bkgsvcattributegroupmapping if exists ( select 1 #tmpbkgsvcattributegroupmapping ) begin insert [ams].[bkgsvcattributegroupmapping] ( [bsagm_id] ,[bsagm_serviceid] ,[bsagm_attributegroupmappingid] ,[bsagm_iseditable] ,[bsagm_issystempreconfigured] ,[bsagm_isdeleted] ,[bsagm_createdby] ,[bsagm_createdon] ,[bsagm_modifiedby] ,[bsagm_modifiedon] ,[bsagm_code] ,[bsagm_copiedfromcode] ) select [bsagm_id] ,[bsagm_serviceid] ,[bsagm_attributegroupmappingid] ,[bsagm_iseditable] ,[bsagm_issystempreconfigured] ,[bsagm_isdeleted] ,[bsagm_createdby] ,getutcdate() --[bsagm_createdon] ,[bsagm_modifiedby] ,[bsagm_modifiedon] ,[bsagm_code] ,[bsagm_copiedfromcode] #tmpbkgsvcattributegroupmapping end -- 6. insert info tenant [bkgsvcattributeoption] security bkgsvcattributeoption if exists ( select 1 #tmpbkgsvcattributeoption ) begin insert [ams].[bkgsvcattributeoption] ( [ebsao_id] ,[ebsao_bkgsvcattributeid] ,[ebsao_optiontext] ,[ebsao_optionvalue] ,[ebsao_isdeleted] ,[ebsao_isactive] ,[ebsao_createdbyid] ,[ebsao_createdon] ,[ebsao_modifiedbyid] ,[ebsao_modifiedon] ) select [ebsao_id] ,[ebsao_bkgsvcattributeid] ,[ebsao_optiontext] ,[ebsao_optionvalue] ,[ebsao_isdeleted] ,[ebsao_isactive] ,[ebsao_createdbyid] ,getutcdate() --[ebsao_createdon] ,[ebsao_modifiedbyid] ,[ebsao_modifiedon] #tmpbkgsvcattributeoption #tmpbkgsvcattributeoption.ebsao_id not in ( select cbsao.ebsao_id ams.bkgsvcattributeoption cbsao ) end --for storing mappings bkgattributegroupmapping , bkgsvcattributegroupmapping based on service id select * #tmpclientattrgrpandsvcattrgrpmapping [ams].[backgroundservice] bs inner bring together [ams].[bkgsvcattributegroupmapping] bpagp on bs.bse_id = bpagp.bsagm_serviceid , bpagp.bsagm_isdeleted = 0 inner bring together [ams].[bkgattributegroupmapping] bsagm on bsagm.bagm_id = bpagp.bsagm_attributegroupmappingid , bsagm.bagm_isdeleted = 0 inner bring together #serviceid si on bs.[bse_id] = si.item bs.bse_isdeleted = 0 -- bkgattributegroupmapping select distinct [bagm_id] ,[bagm_bkgsvcattributegroupid] ,[bagm_bkgsvcatributeid] ,[bagm_iseditable] ,[bagm_issystempreconfigured] ,[bagm_code] ,[bagm_copiedfromcode] ,[bagm_isdeleted] ,[bagm_createdby] ,[bagm_createdon] ,[bagm_modifiedby] ,[bagm_modifiedon] ,[bagm_isdisplay] ,[bagm_displaysequence] ,[bagm_isrequired] #tmpclientbkgattributegroupmap #tmpclientattrgrpandsvcattrgrpmapping select * #tempunmatchedbkgattributegroupmap #tmpclientbkgattributegroupmap t2 not exists ( select * #tmpbkgattributegroupmapping_allentries t1 t1.bagm_id = t2.bagm_id ) -- bkgsvcattributegroupmapping select distinct [bsagm_id] ,[bsagm_serviceid] ,[bsagm_attributegroupmappingid] ,[bsagm_iseditable] ,[bsagm_issystempreconfigured] ,[bsagm_isdeleted] ,[bsagm_createdby] ,[bsagm_createdon] ,[bsagm_modifiedby] ,[bsagm_modifiedon] ,[bsagm_code] ,[bsagm_copiedfromcode] #tmpclientbkgsvcattributegroupmap #tmpclientattrgrpandsvcattrgrpmapping select * #tempunmatchedbkgsvcattributegroupmap #tmpclientbkgsvcattributegroupmap t2 not exists ( select * #tmpbkgsvcattributegroupmapping_allentries t1 t1.bsagm_id = t2.bsagm_id ) --do not delete --update [ams].[bkgattributegroupmapping] --set bagm_isdeleted = 1 -- ,bagm_modifiedon = getdate() --where bagm_id in ( -- select bagm_id -- #tempunmatchedbkgattributegroupmap -- ); update bsagm set bsagm.bsagm_isdeleted=1,bsagm.bsagm_modifiedon=getutcdate() [ams].[bkgsvcattributegroupmapping] bsagm bring together #tempunmatchedbkgsvcattributegroupmap temp on bsagm.bsagm_id=temp.bsagm_id commit transaction -- drop temp tables drop table #serviceids drop table #serviceid drop table #tmpbkgserviceswithchilds drop table #tmpbkgsvcattributeoption drop table #tmpbkgattrgrpandbkgsvcattrgrpmapping drop table #tmpbkgsvcattributesgroup drop table #tmpbkgsvcattributes drop table #tmpbackgroundservices drop table #tmpbkgattributegroupmapping drop table #tmpbkgsvcattributegroupmapping drop table #tmpbkgservices drop table #tmpclientattrgrpandsvcattrgrpmapping drop table #tmpclientbkgattributegroupmap drop table #tempunmatchedbkgattributegroupmap drop table #tmpclientbkgsvcattributegroupmap drop table #tempunmatchedbkgsvcattributegroupmap drop table #tmpbkgattributegroupmapping_allentries drop table #tmpbkgsvcattributegroupmapping_allentries end seek begin grab rollback transaction set @steps = @steps + 1 exec usp_insertserverprocesslog '[usp_copymasterservicedatatoclient]' ,@steps ,'starting usp_copymasterservicedatatoclient catch' ,@processid ,'error' declare @errormessage varchar(max) declare @errorseverity int; declare @errorstate int; select @errorseverity = error_severity(); select @errormessage = error_message(); select @errorstate = error_state(); exec usp_insertserverprocesslog usp_copymasterservicedatatoclient ,0 ,@errormessage raiserror ( @errormessage ,@errorseverity ,@errorstate ); end grab set @steps = @steps + 1 exec usp_insertserverprocesslog '[usp_copymasterservicedatatoclient]' ,@steps ,'ending procedure usp_copymasterservicedatatoclient' ,@processid ,'info' end in code using ado .net phone call procedure.
using (sqlconnection con = new sqlconnection(connection.storeconnection.connectionstring)) { sqlcommand _command = new sqlcommand("ams.usp_copydata", con); _command.commandtype = commandtype.storedprocedure; _command.parameters.addwithvalue("@serviceids", selectedservices.tostring()); con.open(); int32 rowsaffected = _command.executenonquery(); if (rowsaffected > 0) { //some operation perform homecoming true; } con.close(); } c# sql sql-server stored-procedures ado.net
No comments:
Post a Comment