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...

Sample for WCF using json

Here is a sample for start WCF using json
in aspx page

  <script type="text/javascript">
        function ajaxService2() {
            serviceUrl = "http://localhost/WCFS/Service1.svc/GetData";
            var time = new Object();
            time.value = "1";
            $.ajax({
                type: "POST",
                url: serviceUrl,
                data: JSON.stringify(time),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (transport) {
                    var string = transport;
                    $("#<%= Text2.ClientID %>").val(string);
                },
                error: function (ex) {
                    alert("error" + ex.responseText);
                }
            });
        }

        function ajaxService1() {
            serviceUrl = "http://localhost/WCFS/Service1.svc/GetAllProducts";
            $.ajax({
                type: "GET",
                url: serviceUrl,
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (transport) {
                    var string = transport;
                    $("#<%= Text1.ClientID %>").val(string);
                },
                error: function (ex) {
                    alert("error" + ex);
                }
            });
        }
    </script>
    <h2>
        Welcome to ASP.NET!
    </h2>
    <p>
        <p>
            <input id="Button2" onclick="ajaxService1()" value="Ajax call service 1" type="button">
            <asp:textbox id="Text1" runat="server" />
        </p>
        <p>
            <input id="Button3" onclick="ajaxService2()" value="Ajax call service 2" type="button">
            <asp:textbox id="Text2" runat="server" />
        </p>
    

Web.config Settings for WCF and json in WCF’s Web.config

<?xml version="1.0"?>

<configuration>
    <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
    <system.serviceModel>
        <services>
            <service name="WCFS.Service1">
                <endpoint
                address="" binding="webHttpBinding"
                behaviorConfiguration="EndBehave"
                contract="WCFS.IService1"/>
            </service>
        </services>
        <behaviors>
            <endpointBehaviors>
                <behavior name="EndBehave">
                    <webHttp/>
                </behavior>
            </endpointBehaviors>
            <serviceBehaviors>
                <behavior>
                    <serviceMetadata httpGetEnabled="true"  />
                    <serviceDebug  includeExceptionDetailInFaults="false" />
                </behavior>
            </serviceBehaviors>
        </behaviors>
        <serviceHostingEnvironment multipleSiteBindingsEnabled="true" aspNetCompatibilityEnabled="true" />
    </system.serviceModel>
    <system.webServer>
        <modules runAllManagedModulesForAllRequests="true"/>
    </system.webServer>
</configuration>

in WCF Interface

 // NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together.
    [ServiceContract]
    public interface IService1
    {

        [OperationContract]
        [WebInvoke( ResponseFormat = WebMessageFormat.Json, Method = "POST", BodyStyle = WebMessageBodyStyle.WrappedRequest)]
        string GetData(int value);

        [OperationContract]
        CompositeType GetDataUsingDataContract(CompositeType composite);

        [OperationContract]
        [WebInvoke(UriTemplate = "/GetAllProducts", ResponseFormat = WebMessageFormat.Json, Method = "GET")]
        List<product> GetAllProducts();
        // TODO: Add your service operations here
    }


    [DataContract]
    public class Product
    {
        [DataMember]
        public int ProdId { get; set; }
        [DataMember]
        public string PropName { get; set; }
        [DataMember]
        public int Quantity { get; set; }
        [DataMember]
        public int Price { get; set; }
    }

    // Use a data contract as illustrated in the sample below to add composite types to service operations.
    [DataContract]
    public class CompositeType
    {
        bool boolValue = true;
        string stringValue = "Hello ";

        [DataMember]
        public bool BoolValue
        {
            get { return boolValue; }
            set { boolValue = value; }
        }

        [DataMember]
        public string StringValue
        {
            get { return stringValue; }
            set { stringValue = value; }
        }
    }

Service Class

[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)] 
    public class Service1 : IService1
    {
        public List<product> GetAllProducts()
        {
            return new List<product>
            {
                 new Product() {ProdId=101,PropName="Laptop",Quantity=200,Price=45000},
                 new Product() {ProdId=102,PropName="Desktop",Quantity=300,Price=35000},
            };
        }
        public string GetData(int value)
        {
            return string.Format("You entered: {0}", value.ToString());
        }

        public CompositeType GetDataUsingDataContract(CompositeType composite)
        {
            if (composite == null)
            {
                throw new ArgumentNullException("composite");
            }
            if (composite.BoolValue)
            {
                composite.StringValue += "Suffix";
            }
            return composite;
        }
    }

Happy Coding….