Improve Performance by using OUTPUT in Insert Or Update Statement in single query

Hello friends,

recently I needed to fire a single SQL statement for Insert and Update and using the single statement I also needed to return the result of all Inserted/Updated Records details..

after searching a lot ... I came to know a very interesting way to perform both statement in single Query.

Generally for the scenario I got lots of suggestions to use Stored Procedure, but which was difficult for my scenario.

so I must get solution for it.

ok ok... let's come to point.

let's we have created a sample Table using the query

Create Table Table_1(Id Int Identity,Name nvarchar(100))

to insert records and get its generated Id
basically we perform like this

Insert into Table_1(Name) 
Select 'Sandeep'
Select @@IDENTITY

and same way for update
here the Updated value comes from other process which is not available Directly
[My case is only For Insert. but the concept we can use in Update also]

Declare @NewValue nvarchar(100)='Sandeep Prajapati'
Declare @ID int=1

Update Table_1
set Name=@NewValue
Where Id=@ID

select @ID as ID,@NewValue as Name

Now We can use OUTPUT key word to select the inserted/Updated Value to get affected rows in single query which also helps to improve performance.

Here is query for Insert

Insert Into Table_1(Name)
OutPut Inserted.Id,Inserted.Name
values('Sandeep')

after execution of the query the output is

 

Same way for Update

Update Table_1
set Name='Sandeep Prajapati'
OutPut inserted.ID,inserted.Name

Hope this helps...

Happy Coding...

Fetch Data from Control Without Loop- Best Use of LINQ

Hi friends….

Many times I need to generate collection type Data from GridView or Repeater or any other control.. and in that case I used to loop through each rowitem of particular control..

here each row of repeater is listitem of LINQ query.

same way you can perform for all DataBound Controls like GridView, Repeater, DataList, DropDownlist, CheckboxList, Radiobuttonlist etc…

but after using LINQ, I am able to do that very easily

here is sample of Repeater control to Get List of data

List ctrls = (from item in rptctrlList.Items.Cast()
                                    where ((CheckBox)item.FindControl("chkctrl")).Checked
                                    select new ctrlInfo()
                                    {
                                        ctrlId = Convert.ToInt32(((HiddenField)item.FindControl("hdnctrlId")).Value),
                                        UserctrlId = Convert.ToInt64(((HiddenField)item.FindControl("hdnUserctrlId")).Value),
                                    }).ToList();

now I can pass XML to database from this List<ctrlInfo> using

 

var ctrlXML = from ctrl in ctrls
                          select new XElement("ctrl",
                          new XElement("UserId", ctrl.UserId),
                          new XElement("ctrlId", ctrl.ctrlId),
                          new XElement("UserctrlId", ctrl.UserctrlId));
            ctrlsXML.Add(ctrlXML);

Now…. You can pass the XML to Stored Procedure. and in Stored Procedure

using

SELECT 
c.value('ctrlId[1]','int') As ctrlId
, c.value('UserId[1]','BigInt') As UserId
, c.value('UserctrlId[1]','BigInt') As UserctrlId
FROM @ctrlIdListXML.nodes('/ctrls/ctrl')e(c)

you can have Table of selected records of Repeater control.

using merge statement you can perform operation.

 

Hope this Helps

All the Best