Saturday, 25 November 2017

Store Procedure for crud operation with transaction and exception handling (try catch block)

CREATE TABLE [dbo].[tblCustomer](
[CustID] [bigint] NOT NULL,
[CustName] [nvarchar](50) NULL,
[CustEmail] [nvarchar](50) NOT NULL,
[CustAddress] [nvarchar](256) NULL,
[CustContact] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
(
[CustID] ASC,
[CustEmail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[SP_CUSTOMER_INSERT]
(
@CustName NVarchar(50)  
    ,@CustEmail NVarchar(50)  
    ,@CustAddress NVarchar(256)  
    ,@CustContact  NVarchar(50)  
)  
AS BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @CustID Bigint
  SET @CustID = isnull(((SELECT max(CustID) FROM [dbo].[tblCustomer])+1),'1')  
  Insert INTO tblCustomer(CustID,CustName,CustEmail,CustAddress,CustContact)Values(@CustID,@CustName,@CustEmail,@CustAddress,@CustContact)
  Select 1
  Commit Transaction
  End Try
  BEGIN CATCH
   DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;  
            SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();  
            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  
Rollback Transaction
END Catch
End
GO

CREATE  PROCEDURE [dbo].[SP_CUSTOMER_DELETE]
@CustID BIGINT  
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DELETE tblCustomer
 WHERE [CustID] = @CustID   
        SELECT 1  
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ERROR_MEASSAGE NVARCHAR(4000),@ERRORSERVERITY INT , @ERRORLINE INT ,@ERRORSTATE INT;
SELECT @ERROR_MEASSAGE=ERROR_MESSAGE(),@ERRORSERVERITY=ERROR_SEVERITY(),@ERRORLINE=ERROR_LINE(),@ERRORSTATE=ERROR_STATE();
RAISERROR (@ERROR_MEASSAGE ,@ERRORSERVERITY,@ERRORLINE,@ERRORSTATE);
ROLLBACK TRANSACTION
END CATCH
END

CREATE PROCEDURE [dbo].[SP_CUSTOMER_UPDATE]
 @CustID BIGINT 
    ,@CustName NVarchar(50) =NULL
    ,@CustEmail NVarchar(50)  =NULL
    ,@CustAddress NVarchar(256) =NULL 
    ,@CustContact  NVarchar(50) =NULL 
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
UPDATE [dbo].[tblCustomer]
SET CustName=@CustName,
CustAddress=@CustAddress,
CustContact=@CustContact
WHERE CustID=@CustID AND [CustEmail]=@CustEmail
COMMIT TRANSACTION

END TRY

BEGIN CATCH 
SELECT  1    

DECLARE  @ErrorMessage  NVARCHAR(200),@ErrorSeverity INT ,@ErrorState int,@ErrorLinenumber INT;
select  @ErrorMessage =ERROR_MESSAGE(),@ErrorSeverity=ERROR_SEVERITY(), @ErrorState=ERROR_STATE(),@ErrorLinenumber=ERROR_LINE();
SELECT @ErrorMessage,@ErrorSeverity,@ErrorState,@ErrorLinenumber
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState,@ErrorLinenumber)
ROLLBACK TRANSACTION
END CATCH
END

CREATE PROCEDURE [dbo].[SP_SELECT_CUSTOMER_BY_ID]
@CustID  BIGINT
AS BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
SELECT * FROM [dbo].[tblCustomer]
WHERE CustID=@CustID
SELECT 1
COMMIT TRANSACTION
END TRY
BEGIN CATCH 
DECLARE @ERRORMESAGE NVARCHAR(400),@ERRORSERVERITY INT,@ERRORSTATUS INT, @ERRORLINE INT,@ERRORPROCEDURE NVARCHAR(50);
SELECT @ERRORMESAGE=ERROR_MESSAGE(),@ERRORsERVERITY=ERROR_SEVERITY(),@ERRORSTATUS=ERROR_STATE(),@ERRORLINE=ERROR_LINE(),
@ERRORPROCEDURE=ERROR_PROCEDURE();
RAISERROR (@ERRORMESAGE,@ERRORsERVERITY,@ERRORSTATUS,@ERRORLINE,@ERRORPROCEDURE)
ROLLBACK TRANSACTION
END CATCH
END
---------------------------------------------------------------------
Note: -Default exception in SQL Server 2014 is

ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;

The severity parameter specifies the severity of the exception.

Tuesday, 14 November 2017

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;

Friday, 3 November 2017

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

Monday, 9 October 2017

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


Sunday, 24 September 2017

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



Thursday, 7 September 2017

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>

Saturday, 1 July 2017

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 objsumnew numtest(obj.add);
            objsum(10, 20);
            Calc objsubnew 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.