Rollback in single stored procedure for forloop & insert query in SQL Server 2012 -

Rollback in single stored procedure for forloop & insert query in SQL Server 2012 -

i new rollback. have 6 insert queries. executing 6 queries after button click.

but out of 6 queries, 2 of insert queries in loop (in loop iterating trough repeater).

if of insert query giving exception want rollback if insert queries executed have commit of them.

here's code:

string insertinventory = "insert rs_inventory_master (product_name,category_id,quick_overview,description,image,isactive,product_code,barcode, inventory_group_id,entry_date,update_date) values ('" + productname + "','" + category_ddl.selectedvalue + "','" + quick_overview + "','" + desc + "','" + inventory_img + "','" + active + "','" + productcde_txt.text + "','" + barcode_name + "', '" + brand_ddl.selectedvalue + "','" + datetime.now + "','" + datetime.now + "')"; cm.tableinsert(insertinventory); string query = "insert rs_inventory_buying_master (product_code,buying_price,isactive,date,latest) values('" + productcde_txt.text + "','" + buyingprice + "','1','" + datetime.now.tostring() + "','1')"; cm.tableinsert(query); string qry = "insert rs_inventory_selling_master (product_code,selling_price,isactive,date,latest) values('" + productcde_txt.text + "','" + sellingprice + "','1','" + datetime.now.tostring() + "','1')"; cm.tableinsert(qry); //string weightu = weight_txt.text.replace(".", ""); string insertweight = "insert rs_inventory_weight_master (product_code,weight,latest,date,isactive,unit_id) values('" + productcde_txt.text + "','" + productweight + "','1','" + datetime.now.tostring() + "','1','" + unit_ddl.selectedvalue + "')"; cm.tableinsert(insertweight); (int j = 0; j < rpt_sup_lst.items.count; j++) { checkbox ck = rpt_sup_lst.items[j].findcontrol("vendor_ck") checkbox; hiddenfield vendor_id = rpt_sup_lst.items[j].findcontrol("h_vendor_id") hiddenfield; hiddenfield vendor_code = rpt_sup_lst.items[j].findcontrol("h_vendor_code") hiddenfield; if (ck.checked == true) { string v_id = vendor_id.value; string vc_id = vendor_code.value; string insertvendor = "insert rs_inventory_vendor_mapping (vendor_id,product_code,vendor_code) values ('" + v_id + "','" + productcde_txt.text + "','" + vc_id + "')"; cm.tableinsert(insertvendor); } } (int = 0; < rpt_tax_lst.items.count; i++) { checkbox ck = rpt_tax_lst.items[i].findcontrol("tax_chk") checkbox; hiddenfield tax_id = rpt_tax_lst.items[i].findcontrol("h_tax_id") hiddenfield; hiddenfield tax_code = rpt_tax_lst.items[i].findcontrol("h_taxcde_id") hiddenfield; if (ck.checked == true) { string t_id = tax_id.value; string tc_id = tax_code.value; string inserttax = "insert rs_inventory_tax_mapping (tax_id,product_code,tax_code) values ('" + t_id + "','" + productcde_txt.text + "','" + tc_id + "')"; cm.tableinsert(inserttax); } }

how accomplish rollback this?

any help appreciated.

thank in advance.

easiest way pass values sql stored procedure , allow handle values within transaction block. can same thing in code illustration of step table type

--in sql

create type inventory_tax_mappingtype table ( tax_id varchar(20), product_code varchar(20), tax_code varchar(20)); go create procedure dbo. usp_insertinventory_tax_mapping @tvp inventory_tax_mappingtype readonly set nocount on begin seek begin transaction insert rs_inventory_tax_mapping select tax_id, product_code, tax_code @tvp; commit end seek begin grab rollback end grab go -- in c# datatable mytable = new datatable(); dt.columns.add("tax_id", typeof(string)); dt.columns.add("product_code", typeof(string)); dt.columns.add("tax_code", typeof(string)); (int j = 0; j < rpt_sup_lst.items.count; j++) { checkbox ck = rpt_sup_lst.items[j].findcontrol("vendor_ck") checkbox; hiddenfield vendor_id = rpt_sup_lst.items[j].findcontrol("h_vendor_id") hiddenfield; hiddenfield vendor_code = rpt_sup_lst.items[j].findcontrol("h_vendor_code") hiddenfield; if (ck.checked == true) { string v_id = vendor_id.value; string vc_id = vendor_code.value; mytable.rows.add(v_id, productcde_txt.text, vc_id ); } } (int = 0; < rpt_tax_lst.items.count; i++) { checkbox ck = rpt_tax_lst.items[i].findcontrol("tax_chk") checkbox; hiddenfield tax_id = rpt_tax_lst.items[i].findcontrol("h_tax_id") hiddenfield; hiddenfield tax_code = rpt_tax_lst.items[i].findcontrol("h_taxcde_id") hiddenfield; if (ck.checked == true) { string t_id = tax_id.value; string tc_id = tax_code.value; mytable.rows.add(t_id, productcde_txt.text, tc_id ); } } sqlconnection connection = new sqlconnection("***"); connection.open(); sqlcommand cmd = new sqlcommand("insertvalue", connection); cmd.commandtype = commandtype.storedprocedure; //pass table valued parameter store procedure sqlparameter sqlparam = cmd.parameters.addwithvalue(" @tvp", mytable); sqlparam.sqldbtype = sqldbtype.structured; cmd.executenonquery(); connection.close();

not tested, point.

sql sql-server rollback