Batch Updation

I have the scenario of selecting values from more than one tables, also while updating the values to DB from Datagrid some changes may or may not be made in grod

Then check the values alreay existing table to the grid values now i had written in OPENXML inside a Stored procedure,the SP have if statements, INSERT,UPDATE for one table or more than one table

which one is best openxml or the adapter.update is best,

Also how can achieve more than one t-sql operation in adapter.update

the SP is below

/*

To UPDATE And INSERT the Asset Values and Asset Status, and to INSERT Asset Change History

For the Assets which are Value and Status Changed

*/

Create procedure AssetValueStatusAdjustment_InsertUpdate

@.XMLString text
,@.LastModifiedUserID varchar(10)
,@.LastModifiedDate varchar(10)
,@.CultureID varchar(5)
As
BEGIN
SET NOCOUNT ON
Declare @.InventoryHistoryID bigint
Declare @.handle int
Declare @.XMLStatusName nvarchar(50)
Declare @.TableStatusName nvarchar(50)
Declare @.Error int

EXEC sp_xml_preparedocument @.handle OUTPUT, @.XMLString

-- This is For INSERT the Details of the Value Adjustment Assets into the Table
-- Inventory_ValueAdjustment

INSERT INTO Inventory_ValueAdjustment
(InventoryID
,PostDate
,PreAdjustmentOriginalCost
,PreAdjustmentNetValue
,AdjustmentAmount
,Comment
,GLTemplateID
,LastModifiedUserID
,LastModifiedDate)

SELECT
XMLAdjustmentColumns.InventoryID
,XMLAdjustmentColumns.PostDate
,XMLAdjustmentColumns.PreAdjustmentOriginalCost
,XMLAdjustmentColumns.PreAdjustmentNetValue
,XMLAdjustmentColumns.AdjustmentAmount
,XMLAdjustmentColumns.Comment
,XMLAdjustmentColumns.GLTemplateID
,@.LastModifiedUserID as LastModifiedUserID
,@.LastModifiedDate as LastModifiedDate

From OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_ValueAdjustment Table

With(InventoryID bigint,
PostDate datetime,
PreAdjustmentOriginalCost decimal(18,2),
PreAdjustmentNetValue decimal(18,2),
AdjustmentAmount decimal(18,2),
Comment nvarchar(200),
GLTemplateID int)XMLAdjustmentColumns

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

-- UPDATE the ValueAdjustment to Inventory_Profile

UPDATE
Inventory_Profile
SET
NetValue=XMLAdjustmentColumns.NetValue
,LastModifiedUserID= @.LastModifiedUserID
,LastModifiedDate=@.LastModifiedDate

From OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_ValueAdjustment Table

With(InventoryID bigint,
NetValue decimal(19,2))XMLAdjustmentColumns

WHERE
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

--INSERT a ValueAdjustment Change History Record

INSERT INTO Inventory_History
(InventoryHistoryID
,InventoryID
,AsOfDate--
,ANum--
,LNum--
,HisoryGenerationReasonID
,Alias
,InventoryStatusID
,InventorySubStatusID
,InventorySubTypeID
,InventoryTypeID
,AcquisitionDate
,OriginalCost
,NetValue
,UnitNumber
,YearOfManufacture
,Manufacturer
,Make
,Model
,SerialNumber
,FundingSourceID--
,OwnerSinceDate--
,UsageConditionID
,Description1
,Description2
,InventoryLocationID
,LocationEffectiveFromDate
,IsFlaggedForSale
,IsDepreciationAllowed--
,PortfolioID
,RentalPurchaseOrderNumber
,AquisitionPurchaseOrderNumber
,CostCenterID
,OwnerTypeID
,IsReplacementUnitOnOrder
,CustomerBookValue
,GLTemplateID
,FlaggedForCofAAlias
,FlaggedForLeaseAlias
,CustomerID
,PreviousLeaseID
,SortOrder
,IsSaleLeaseBack
,ParentInventoryID
,Quantity
,OldSerialNumber
,InventoryGroupID
,InvoiceGroupID
,FactorCategoryID
,IsEligibleForPropertyTaxManagement
,InterimRentReceivableUpfrontTaxModeID
,LeaseRentalReceivableUpfrontTaxModeID
,OverTermReceivableUpfrontTaxModeID
,LastModifiedUserID
,LastModifiedDate)

SELECT
XMLAdjustmentColumns.InventoryHistoryID as InventoryHistoryID
,Inventory_Profile.InventoryID
,XMLAdjustmentColumns.AsOfDate as AsOfDate
,XMLAdjustmentColumns.ANum as ANum
,XMLAdjustmentColumns.LNum as LNum
,XMLAdjustmentColumns.HisoryGenerationReasonID as HisoryGenerationReasonID
,Inventory_Profile.Alias
,Inventory_Profile.InventoryStatusID
,Inventory_Profile.InventorySubStatusID
,Inventory_Profile.InventorySubTypeID
,Inventory_Profile.InventoryTypeID
,Inventory_Profile.AcquisitionDate
,Inventory_Profile.OriginalCost
,Inventory_Profile.NetValue
,Inventory_Profile.UnitNumber
,Inventory_Profile.YearOfManufacture
,Inventory_Profile.Manufacturer
,Inventory_Profile.Make
,Inventory_Profile.Model
,Inventory_Profile.SerialNumber
,XMLAdjustmentColumns.FundingSourceID as FundingSourceID
,XMLAdjustmentColumns.OwnerSinceDate as OwnerSinceDate
,Inventory_Profile.UsageConditionID
,Inventory_Profile.Description1
,Inventory_Profile.Description2
,Inventory_Profile.InventoryLocationID
,Inventory_Profile.LocationEffectiveFromDate
,Inventory_Profile.IsFlaggedForSale
,XMLAdjustmentColumns.IsDepreciationAllowed as IsDepreciationAllowed
,Inventory_Profile.PortfolioID
,Inventory_Profile.RentalPurchaseOrderNumber
,Inventory_Profile.AquisitionPurchaseOrderNumber
,Inventory_Profile.CostCenterID
,Inventory_Profile.OwnerTypeID
,Inventory_Profile.IsReplacementUnitOnOrder
,Inventory_Profile.CustomerBookValue
,Inventory_Profile.GLTemplateID
,Inventory_Profile.FlaggedForCofAAlias
,Inventory_Profile.FlaggedForLeaseAlias
,Inventory_Profile.CustomerID
,Inventory_Profile.PreviousLeaseID
,Inventory_Profile.SortOrder
,Inventory_Profile.IsSaleLeaseBack
,Inventory_Profile.ParentInventoryID
,Inventory_Profile.Quantity
,Inventory_Profile.OldSerialNumber
,Inventory_Profile.InventoryGroupID
,Inventory_Profile.InvoiceGroupID
,Inventory_Profile.FactorCategoryID
,Inventory_Profile.IsEligibleForPropertyTaxManagement
,Inventory_Profile.InterimRentReceivableUpfrontTaxModeID
,Inventory_Profile.LeaseRentalReceivableUpfrontTaxModeID
,Inventory_Profile.OverTermReceivableUpfrontTaxModeID
,@.LastModifiedUserID as LastModifiedUserID
,@.LastModifiedDate as LastModifiedDate

FROM
Inventory_Profile INNER JOIN OPENXML(@.handle,'Root/Asset',1)

--This Field From Inventory_ValueAdjustment Table

WITH(InventoryHistoryID bigint,
InventoryID bigint,
--This Fields From Inventory_History Table
AsOfDate datetime,
ANum nvarchar(50),
LNum int,
HisoryGenerationReasonID tinyint,
FundingSourceID int,
OwnerSinceDate datetime,
IsDepreciationAllowed tinyint) XMLAdjustmentColumns

ON
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

-- Check Wheather the Status has been Changed or not
SELECT
@.XMLStatusName = XMLAdjustmentColumns.InventoryStatusName
,@.TableStatusName = Inventory_Status_CnfgLocale.InventoryStatusName

FROM
Inventory_Status_Cnfg

INNER JOIN
Inventory_Status_CnfgLocale ON Inventory_Status_Cnfg.InventoryStatusID=Inventory_Status_CnfgLocale.InventoryStatusID

INNER JOIN
OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_Profile Table

WITH(InventoryStatusName nvarchar(100)
,InventoryStatusID tinyint
,CultureID varchar(5))XMLAdjustmentColumns

ON
Inventory_Status_CnfgLocale.InventoryStatusID=XMLAdjustmentColumns.InventoryStatusID

AND
Inventory_Status_CnfgLocale.CultureID=@.CultureID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

-- If New Status is not matched with the existing Status Name then

IF(@.XMLStatusName <> @.TableStatusName)

BEGIN --To Insert Status Change Record

--UPDATE the New Status Changed Value to Inventory_Profile

UPDATE
Inventory_Profile
SET
InventoryStatusID = XMLAdjustmentColumns.InventoryStatusId
,LastModifiedUserID= @.LastModifiedUserID
,LastModifiedDate=@.LastModifiedDate

FROM
OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_Profile Tavle

WITH(InventoryStatusID tinyint
,InventoryID bigint)XMLAdjustmentColumns

WHERE
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

--Select the Maximum available InventoryHistoryID to avoid primary key violation
SELECT
@.InventoryHistoryID=COUNT(InventoryHistoryID)
FROM
Inventory_History

SET @.InventoryHistoryID = @.InventoryHistoryID+1

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

-- INSERT a status change history record

INSERT INTO Inventory_History
(InventoryHistoryID
,InventoryID
,AsOfDate--
,ANum--
,LNum--
,HisoryGenerationReasonID
,Alias
,InventoryStatusID
,InventorySubStatusID
,InventorySubTypeID
,InventoryTypeID
,AcquisitionDate
,OriginalCost
,NetValue
,UnitNumber
,YearOfManufacture
,Manufacturer
,Make
,Model
,SerialNumber
,FundingSourceID--
,OwnerSinceDate--
,UsageConditionID
,Description1
,Description2
,InventoryLocationID
,LocationEffectiveFromDate
,IsFlaggedForSale
,IsDepreciationAllowed--
,PortfolioID
,RentalPurchaseOrderNumber
,AquisitionPurchaseOrderNumber
,CostCenterID
,OwnerTypeID
,IsReplacementUnitOnOrder
,CustomerBookValue
,GLTemplateID
,FlaggedForCofAAlias
,FlaggedForLeaseAlias
,CustomerID
,PreviousLeaseID
,SortOrder
,IsSaleLeaseBack
,ParentInventoryID
,Quantity
,OldSerialNumber
,InventoryGroupID
,InvoiceGroupID
,FactorCategoryID
,IsEligibleForPropertyTaxManagement
,InterimRentReceivableUpfrontTaxModeID
,LeaseRentalReceivableUpfrontTaxModeID
,OverTermReceivableUpfrontTaxModeID
,LastModifiedUserID
,LastModifiedDate)

SELECT
@.InventoryHistoryID as InventoryHistoryID
,Inventory_Profile.InventoryID
,XMLAdjustmentColumns.AsOfDate as AsOfDate
,XMLAdjustmentColumns.ANum as ANum
,XMLAdjustmentColumns.LNum as LNum
,XMLAdjustmentColumns.HisoryGenerationReasonID as HisoryGenerationReasonID
,Inventory_Profile.Alias
,Inventory_Profile.InventoryStatusID
,Inventory_Profile.InventorySubStatusID
,Inventory_Profile.InventorySubTypeID
,Inventory_Profile.InventoryTypeID
,Inventory_Profile.AcquisitionDate
,Inventory_Profile.OriginalCost
,Inventory_Profile.NetValue
,Inventory_Profile.UnitNumber
,Inventory_Profile.YearOfManufacture
,Inventory_Profile.Manufacturer
,Inventory_Profile.Make
,Inventory_Profile.Model
,Inventory_Profile.SerialNumber
,XMLAdjustmentColumns.FundingSourceID as FundingSourceID
,XMLAdjustmentColumns.OwnerSinceDate as OwnerSinceDate
,Inventory_Profile.UsageConditionID
,Inventory_Profile.Description1
,Inventory_Profile.Description2
,Inventory_Profile.InventoryLocationID
,Inventory_Profile.LocationEffectiveFromDate
,Inventory_Profile.IsFlaggedForSale
,XMLAdjustmentColumns.IsDepreciationAllowed as IsDepreciationAllowed
,Inventory_Profile.PortfolioID
,Inventory_Profile.RentalPurchaseOrderNumber
,Inventory_Profile.AquisitionPurchaseOrderNumber
,Inventory_Profile.CostCenterID
,Inventory_Profile.OwnerTypeID
,Inventory_Profile.IsReplacementUnitOnOrder
,Inventory_Profile.CustomerBookValue
,Inventory_Profile.GLTemplateID
,Inventory_Profile.FlaggedForCofAAlias
,Inventory_Profile.FlaggedForLeaseAlias
,Inventory_Profile.CustomerID
,Inventory_Profile.PreviousLeaseID
,Inventory_Profile.SortOrder
,Inventory_Profile.IsSaleLeaseBack
,Inventory_Profile.ParentInventoryID
,Inventory_Profile.Quantity
,Inventory_Profile.OldSerialNumber
,Inventory_Profile.InventoryGroupID
,Inventory_Profile.InvoiceGroupID
,Inventory_Profile.FactorCategoryID
,Inventory_Profile.IsEligibleForPropertyTaxManagement
,Inventory_Profile.InterimRentReceivableUpfrontTaxModeID
,Inventory_Profile.LeaseRentalReceivableUpfrontTaxModeID
,Inventory_Profile.OverTermReceivableUpfrontTaxModeID
,@.LastModifiedUserID as LastModifiedUserID
,@.LastModifiedDate as LastModifiedDate

FROM
Inventory_Profile INNER JOIN OPENXML(@.handle,'Root/Asset',1)

--This Field From Inventory_ValueAdjustment Table

WITH(InventoryHistoryID bigint,
InventoryID bigint,
--This Fields From Inventory_History Table

AsOfDate datetime,
ANum nvarchar(50),
LNum int,
HisoryGenerationReasonID tinyint,
FundingSourceID int,
OwnerSinceDate datetime,
IsDepreciationAllowed tinyint) XMLAdjustmentColumns

ON
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

END

GOTO NextStatement --dont want to Update the Status GOTO Next expression

NextStatement:

ErrorCatch:
RETURN @.Error

EXEC sp_xml_removedocument @.handle
END
Go

/*

This Stored Procedure Will Insert two tables as Inventory_Profile, Inventory_History and

Update TaxDepreciation_Profile by OPENXML BULK UPDATE CONCEPT

-
The @.xmlString Parameter is used to get the value as XML Nodes From the Non Transaction

Class

-

*/

guideme please

SqlDataAdapter has an UpdateBatchSize property that will batch updates together before sending them to the server. Value 0 should send as much across as possible. In addition you can use a Transaction to ensure that your updates happen in a consistent manner.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatebatchsize.aspx

In this way you could use the dataset and SqlDataAdapter to acheive what you are doing with your stored procedure. This will probably reduce the amount of code and get your business logic into c#.