Paging store Procedure to increase the performance data loading

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;

Splite Function use4

declare @receivedsetupPeriod varchar(1000),@currentperiod varchar(1000),@toperiod varchar(1000),@fromperiod varchar(1000)
select @receivedsetupPeriod = tbl_ReceivalbeSetupPeriodId,@currentperiod = CurrentPeriod,@toperiod = [TO],@fromperiod= [FROM] from tbl_ReceivableSetup

select R.Id,R.Item as receivedid,C.Item as Currentperiod,T.Item as [To],F.Item as [From] from
dbo.SplitString(@receivedsetupPeriod,',') R
Join
dbo.SplitString(@currentperiod,',') C ON R.Id=C.Id
Join
dbo.SplitString(@toperiod,',') T ON T.Id=C.Id
Join
dbo.SplitString(@fromperiod,',') F ON T.Id=F.Id

How to Identify Error Line , Message in store Procedure (1)at time of Exception Handling .

CREATE TABLE [dbo].[tbl_dbLogs](
[LogSerialId] [int] IDENTITY(1,1) NOT NULL,
[Error_line] [int] NULL,
[ERROR_MESSAGE] [nvarchar](max) NULL,
[ERROR_PROCEDURE] [nvarchar](100) NULL,
[CreateDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Alter Procedure SP_PaymentToSupplierEmployee_Periodically

@tbl_OrganizationId nvarchar(50)

AS

BEGIN

SET NOCOUNT ON;

Begin try

SELECT  

COUNT(tbl_PaymentToSupplierEmployeeId) As TotalTransaction, DateName(wk,PaymentDate)As [Week],DateName(d, PaymentDate)As [days], DateName(dw,PaymentDate) As [DayName], DateName(m,PaymentDate) AS [month],

DateName(qq,PaymentDate) As [Quarter],DateName(yy, PaymentDate) As [Year],

SUM(Total) As TotalRevenue,  case when IsPosted='False' then count(IsPosted)end As pending,

CASE WHEN IsPosted='true' then count(IsPosted)end As Posted 

FROM tbl_PaymentToSupplierEmployee

--WHERE IsDeleted='False' And tbl_OrganizationId='42D1401E-EA99-42E2-8954-2969858CE625'

WHERE IsDeleted='False' And tbl_OrganizationId=@tbl_OrganizationId

GROUP BY PaymentDate,IsPosted

End try

Begin catch 

  insert into tbl_dbLogs([Error_line],[ERROR_MESSAGE],[ERROR_PROCEDURE],CreateDate)values(ERROR_LINE(),ERROR_MESSAGE(),ERROR_PROCEDURE(),GetDate())

End  catch

End

How to fetch/ retrieve data in period order (Day,week,Month,Quarter,year) from SQL Server with aggregate function.

CREATE Procedure SP_PaymentToSupplierEmployee_Periodically
@tbl_OrganizationId nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT 
COUNT(tbl_PaymentToSupplierEmployeeId) As TotalTransaction, DateName(wk,PaymentDate)As [Week],DateName(d, PaymentDate)As [days], DateName(dw,PaymentDate) As [DayName], DateName(m,PaymentDate) AS [month],
DateName(qq,PaymentDate) As [Quarter],DateName(yy, PaymentDate) As [Year],
SUM(Total) As TotalRevenue,  case when IsPosted='False' then count(IsPosted)end As pending,
CASE WHEN IsPosted='true' then count(IsPosted)end As Posted
FROM tbl_PaymentToSupplierEmployee
WHERE IsDeleted='False' And tbl_OrganizationId=@tbl_OrganizationId
GROUP BY PaymentDate,IsPosted
END

How to consume wcf service using Jquery.

Data Base:-


CREATE TABLE [dbo].[tbl_registration](
[FormID] [int] IDENTITY(1,1) NOT NULL,
[ModelName] [nvarchar](50) NULL,
[RegFees] [float] NULL,
[RegNo] [nvarchar](50) NOT NULL,
[SmartNo] [nvarchar](50) NULL,
[CustomerName] [nvarchar](50) NULL,
[Address] [nvarchar](150) NULL,
[Location] [nvarchar](50) NULL,
[Contactno] [nvarchar](50) NULL,
[EngineNo] [nvarchar](50) NULL,
[chassisaNo] [nvarchar](50) NULL,
[PurchaseDate] [datetime] NULL,
[validupto] [datetime] NULL,
[RegDate] [datetime] NULL,
[username] [nvarchar](50) NULL,
 CONSTRAINT [PK_tbl_registration] PRIMARY KEY CLUSTERED
(
[RegNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE procedure [dbo].[SP_RegistrationDetails]

@FormID int

AS BEGIN

Select * from tbl_registration 

where FormID= @FormID

END

GO

-------------------------------------------------------------------------------------------------------------------------

File--New--Project eg- Demo

 Add-New Item -registrationDetails.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

namespace Demo

{

    public class registrationDetails

    {

        public int FormID { get; set; }

        public string ModelName { get; set; }

        public Decimal RegFees { get; set; }

        public string RegNo { get; set; }

        public string SmartNo { get; set; }

        public string CustomerName { get; set; }

        public string Address { get; set; }

        public string Location { get; set; }

        public string Contactno { get; set; }

        public string EngineNo { get; set; }

        public string chassisaNo { get; set; }

        public string PurchaseDate { get; set; }

        public string validupto { get; set; }

        public string RegDate { get; set; }

        public string username { get; set; }

    }

}

Add-New Item-WCF Service(Ajax-Enable) eg-RegistrationServices

using System;

using System.Collections.Generic;

using System.Linq;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.ServiceModel.Activation;

using System.ServiceModel.Web;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace Demo

{

    [ServiceContract(Namespace = "")]

    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]

    public class RegistrationServices

    {

       

        [OperationContract]

       

        public registrationDetails GetRegistrationById(int FormID)

        {

            registrationDetails rd = new registrationDetails();

            string cs = ConfigurationManager.ConnectionStrings["dmo"].ConnectionString;

            using (SqlConnection con = new SqlConnection(cs))

            { SqlCommand cmd = new SqlCommand("SP_RegistrationDetails", con);

                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param = new SqlParameter();

                param.ParameterName = "@FormID";

                param.Value = FormID;

                cmd.Parameters.Add(param);

                con.Open();

                SqlDataReader sdr = cmd.ExecuteReader();

                while (sdr.Read())

                {

                  

                    rd.ModelName = sdr["ModelName"].ToString();

                    rd.RegFees = Convert.ToDecimal(sdr["RegFees"]);

                    rd.RegNo = sdr["RegNo"].ToString();

                    rd.SmartNo = sdr["SmartNo"].ToString();

                    rd.CustomerName = sdr["CustomerName"].ToString();

                    rd.Address = sdr["Address"].ToString();

                    rd.Location= sdr["Location"].ToString();

                    rd.Contactno = sdr["Contactno"].ToString();

                    rd.EngineNo = sdr["EngineNo"].ToString();

                    rd.chassisaNo = sdr["chassisaNo"].ToString();

                    rd.PurchaseDate = sdr["PurchaseDate"].ToString();

                    rd.validupto = Convert.ToString(sdr["validupto"]);

                    rd.RegDate = Convert.ToString(sdr["RegDate"]);

                    rd.username = Convert.ToString(sdr["username"]);                           

                 

                  

                }

            }

            return rd;

        }

        

    }

}

web Config :-

 <connectionStrings>

    <add name="dmo" connectionString="Data Source=AJIT; Database=BBAJAJ; user id=sa; password=admin@123" />

  </connectionStrings>

---------------------------------------------------------------------------------------------------------

Add-New Item-Web Form egWebForm1.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="Demo.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

<script src="Scripts/jquery-1.11.2.js"></script>

<script type="text/javascript">

$(document).ready(function () {

$('#btnGetRegistrationById').click(function () {

var FormId = $('#txtFormId').val();

$.ajax({

url: 'RegistrationServices.svc/GetRegistrationById',

method: 'post',

contentType: 'application/json;charset=utf-8',

data: JSON.stringify({ FormID: FormId }),

dataType: 'json',

success: function (data) {

$('#txtModelName').val(data.d.ModelName);

$('#txtRegFees').val(data.d.RegFees);

$('#txtRegNo').val(data.d.RegNo);

$('#txtSmartNo').val(data.d.SmartNo);

$('#txtCustomerName').val(data.d.CustomerName);

$('#txtAddress').val(data.d.Address);

$('#txtContactno').val(data.d.Contactno);

$('#txtLocation').val(data.d.Location)

$('#txtEngineNo').val(data.d.EngineNo);

$('#txtchassisaNo').val(data.d.chassisaNo);

$('#txtPurchaseDate').val(data.d.PurchaseDate);

$('#txtvalidupto').val(data.d.validupto);

$('#txtRegDate').val(data.d.RegDate);

$('#txtusername').val(data.d.username);

},

error: function (error) {

alert(error);

}

});

});

});

function formatJSONDate(jsonDate) {

var newDate = new dateFormat(jsonDate, "mm/dd/yyyy");

//var newDate = dateFormat(jsonDate, "mm/dd/yyyy");

return newDate;

}

</script>

</head>

<body>

    <form id="form1" runat="server">

        FormId: <input id="txtFormId" type="text" style="width:89px"/>

<input type="button" id="btnGetRegistrationById" value="ResitrartionDetails" />

<br />

<br />

<table border="1" style="border-collapse:collapse">

<tr><td>ModelName :</td><td><input id="txtModelName" type="text" /></td></tr>

<tr><td>RegFees :</td><td><input id="txtRegFees" type="text" /></td></tr>

<tr><td>RegNo :</td><td><input id="txtRegNo" type="text" /></td></tr>

<tr><td>SmartNo :</td><td><input id="txtSmartNo" type="text" /></td></tr>

<tr><td>CustomerName :</td><td><input id="txtCustomerName" type="text" /></td></tr>

<tr><td>Address :</td><td><input id="txtAddress" type="text" /></td></tr>

<tr><td>Location :</td><td><input id="txtLocation" type="text" /></td></tr>

<tr><td>Contactno :</td><td><input id="txtContactno" type="text" /></td></tr>

<tr><td>EngineNo :</td><td><input id="txtEngineNo" type="text" /></td></tr>

<tr><td>chassisaNo:</td><td><input id="txtchassisaNo" type="text" /></td></tr>

<tr><td>PurchaseDate: </td><td><input id="txtPurchaseDate" type="datetime" /></td></tr>

<tr><td>validupto:</td><td><input id="txtvalidupto" type="datetime" /></td></tr>

<tr><td>RegDate: </td><td><input id="txtRegDate" type="datetime" /></td></tr>

<tr><td>username:</td><td><input id="txtusername" type="text" /></td></tr>

</table>

    </form>

</body>

</html>

https://github.com/ajitagarwal89/WCF-USING-JQUERY

Delegate in c#

What is Delegate in C#.

Delegate is an object which holds the  references to a method  within the delegate object.It is  type safe object and invoking the method asynchronously manner.

Advantage of Delegates

1 Improved the performance of Application
2 Call a method asynchronous.

Type of Delegate 
1. Single Delegate and
2 Multicast  Delegate
3 Generic Delegate

Single Delegate :-A delegate is need to pass single parameter as reference to a method with the delegate object.

using System;
using System.Collections.Generic;
using System.Text; 

namespace DelegateTest
{
    public delegate void Calc(int x, int y);

    class A    {
        public void add(int x, int y)
        {
            Console.WriteLine("The Sum is " + (x + y));
        } 

        public void sub(int x, int y)
        {
            Console.WriteLine("The Difference is " + (x - y));
            Console.ReadLine();
        }
    }

    class Program    {
        static void Main(string[] args)
        {
            A obj = new A();
            //make an object of class A         
            Calc objsum= new numtest(obj.add);
            objsum(10, 20);
            Calc objsub= new numtest(obj.sub);
            objsub(10, 20);
        }
    }
}

https://msdn.microsoft.com/en-us/library/system.delegate.aspx


Multicast Delegate: - When we need to pass more than one parameter as reference  to a method within delegate object.

How to find schema changes report in SQL Server ?

How to find schema  changes report in SQL Server ?

Select o.name As [Objcet_Name],
 s.name [Schema_Name],
 o.type_desc [Description],
 o.create_date [Creation_Date],
 o.modify_date [Modified_Date]
 from sys.all_objects o
 Left outer join sys.schemas s
 on o.schema_id=s.schema_id
 where create_date >(GETDATE()-7) or modify_date >(GETDATE()-7)

Or

We can check SSMS  Wizard .

Step 1:- Select Database 

Step 2:-click right mouse button and  Select Report.

Step 3:- Select Standard 

Step 4: finally Select Schema change History .