Insert INTO Table From Storedprocedure

 Insert into Table From stored procedure execution

  Insert INTo PFStatement_Data

  EXEC GCPF.SP_RPT_GetProvidentStatement '29226', '31 December, 2021', 1  

Access API with basic Authorization

 C# Code To access API using basic authorization.

using (HttpClient client = new HttpClient())

            {

                string TransactionID = "6334360607";

                string url = $"http://103.11.137.17:8081/BillPayGW/BillInquiryService?shortcode=555&userid=dbbl&password=dbbl&txnid={ TransactionID }";

                Uri baseUri = new Uri(url);

                //This is the key section  UserName Password    

                var plainTextBytes = System.Text.Encoding.UTF8.GetBytes("dbill:dBILL!23");

                 

                string base64EncodedAuthenticationString = System.Convert.ToBase64String(plainTextBytes);

                

                var requestMessage = new HttpRequestMessage(HttpMethod.Get, baseUri);

                requestMessage.Headers.Authorization = new AuthenticationHeaderValue("Basic", base64EncodedAuthenticationString);

                requestMessage.Headers.Add("responseType", "json");

                 

                //make the request

                var task = client.SendAsync(requestMessage);

                var response = task.Result;

                response.EnsureSuccessStatusCode();

                string responseBody = response.Content.ReadAsStringAsync().Result;

            }

Postman setting: 

If Column not exist then create

 --SELECT ETinNo FROM Employee

IF NOT EXISTS (

  SELECT * 

  FROM   sys.columns 

  WHERE  object_id = OBJECT_ID(N'[dbo].[Employee]') 

         AND name = 'ETinNo'

)

BEGIN

ALTER TABLE Employee

ADD ETinNo varchar(20);

SELECT 1

END

Kill Job

 SET NOCOUNT ON

-- Table variable to hold InputBuffer data

DECLARE @Inputbuffer TABLE

    (

      EventType NVARCHAR(30) NULL,

      Parameters INT NULL,

      EventInfo NVARCHAR(4000) NULL

    )

-- Table variable to hold running processes information

DECLARE @BusyProcess TABLE

    (

      SPID INT,

      Status VARCHAR(100),

      Login VARCHAR(100),

      HostName VARCHAR(100),

      DBName VARCHAR(100),

      Command VARCHAR(200),

      CPUTime INT,

      DiskIO INT,

      LastBatch DATETIME,

      ProgramName VARCHAR(200),

      EventInfo NVARCHAR(4000), -- extra column to hold actual stored procedure or batch call text

      EventTime INT   -- time in minutes, a process is running

    )

-- Insert all running processes information to table variable

INSERT  @BusyProcess

        ( SPID, Status, Login, HostName,  DBName, Command, CPUTime,

          DiskIO, LastBatch, ProgramName )

    

        SELECT spid,status,loginame,hostname,DB_NAME(dbid),cmd,cpu,physical_io,last_batch,program_name 

        FROM SYS.SYSPROCESSES

        WHERE 

1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1

--Transactions that are open not yet committed or rolledback

WHEN Status = 'SLEEPING' AND open_tran  > 0 THEN 1 

ELSE 0 END

AND cmd NOT LIKE 'BACKUP%' and DB_NAME(dbid)='gHRM'

            

-- Cursor to add actuall Procedure or Batch statement for each process

DECLARE cur_BusyProcess Cursor

    FOR SELECT  SPID

        FROM    @BusyProcess

OPEN cur_BusyProcess 

DECLARE @SPID INT     

Fetch NEXT FROM cur_BusyProcess INTO @SPID

While ( @@FETCH_STATUS <> -1 )

    BEGIN

        INSERT  @Inputbuffer

                EXEC ( 'DBCC INPUTBUFFER(' + @SPID + ')'

                    ) 

        UPDATE  @BusyProcess

        SET     EventInfo = I.EventInfo,

                EventTime = DATEDIFF(MI,LastBatch,GETDATE())

        FROM    @BusyProcess b

                CROSS JOIN @Inputbuffer i

        WHERE   B.SPID = @SPID

        DELETE  FROM @Inputbuffer

        FETCH NEXT FROM cur_BusyProcess INTO @SPID

    END

CLOSE cur_BusyProcess

DEALLOCATE cur_BusyProcess

-- Create html mail 

IF EXISTS(SELECT 1

FROM  @BusyProcess I

WHERE   EventInfo NOT LIKE '---BusyProcess Detection%'

AND EventTime >= 3

)

BEGIN

Declare @Body varchar(max), @TableHead varchar(1000), @TableTail varchar(1000)

Set NoCount On;

DECLARE @QKILLsp VARCHAR(1000)

SET @QKILLsp= (SELECT DISTINCT '  KILL '+ CONVERT(VARCHAR,SPID)

FROM  @BusyProcess I

WHERE   EventInfo NOT LIKE '---BusyProcess Detection%'

AND EventTime >= 3 -- Transactions Running for 3 minutes or more

for XML path('')

)

EXEC(@QKILLsp) 

END

SQL Function Lead To Get the Next Row Value

SELECT OfficeId, JoiningDate, ReleaseDate FROM trns.EmployeeTransfer WHERE EmployeeId = 521

Order By JoiningDate

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

SELECT OfficeId, JoiningDate, ReleaseDate  ,

isNUll(    

 LEAD(ISNULL(OfficeId,0)) OVER (ORDER BY JoiningDate)     

 , isNull(OfficeId, 0)     

 ) AS NextOfficeId     

FROM trns.EmployeeTransfer

WHERE EmployeeId = 521

Access web config.

 Get value from web config  <appSettings>.

  <add key="adminuser" value="Administrator" />

    <add key="adminpassword" value="GC@gcomm123456" />

In Controller write:

   string adminuser = WebConfigurationManager.AppSettings["adminuser"];

  string adminpassword = WebConfigurationManager.AppSettings["adminpassword"];

Use this reference: using System.Web.Configuration;

Optional Parameter in Stored Procedure

 ALTER Procedure [dbo].[SetEmploymentDetails]

(

   @LoanSummaryID    AS bigint

      ,@OfficeId    AS int

      ,@MemberId    AS bigint

      ,@CenterId    AS int

      ,@txtMaleFullTimeP1    AS int

      ,@txtFeMaleFullTimeP1    AS int

      ,@txtMalePartTimeP1    AS int

      ,@txtFeMalePartTimeP1    AS int

      ,@txtMaleFullTimeP2    AS int = 0  -- Optional pararameter

      ,@txtFeMaleFullTimeP2    AS int = 0 -- Optional Parameter

      ,@txtMalePartTimeP2    AS int = 0 -- Optional Parameter

      ,@txtFeMalePartTimeP2    AS int = 0 -- Optional Parameter

      ,@txtMaleFullTimeP3    AS int

      ,@txtFeMaleFullTimeP3    AS int

      ,@txtMalePartTimeP3    AS int

      ,@txtFeMalePartTimeP3    AS int

      ,@isActive    AS bit  = 1

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

--if Not Exist then Insert.

IF EXISTS

    (

    SELECT 1

    FROM EmploymentDetails

    WHERE [LoanSummaryID] = @LoanSummaryID

    )

    begin

        --RAISERROR('Timesheets have already been posted!', 16, 1)

      

  Select 1

  

  Update EmploymentDetails 

  SET

   [SEmpMaleFullTimeP1] =  @txtMaleFullTimeP1 ,

   [SEmpFeMaleFullTimeP1] =  @txtFeMaleFullTimeP1 ,

   [SEmpMalePartTimeP1] =  @txtMalePartTimeP1 ,

   [SEmpFeMalePartTimeP1] =  @txtFeMalePartTimeP1 ,

   --[FEmpMaleFullTimeP2] =  @txtMaleFullTimeP2 ,

   --[FEmpFeMaleFullTimeP2] =  @txtFeMaleFullTimeP2 ,

   --[FEmpMalePartTimeP2] =  @txtMalePartTimeP2 ,

   --[FEmpFeMalePartTimeP2] =  @txtFeMalePartTimeP2 ,

   [WEmpMaleFullTimeP3] =  @txtMaleFullTimeP3 ,

   [WEmpFeMaleFullTimeP3] =  @txtFeMaleFullTimeP3 ,

   [WEmpMalePartTimeP3] =  @txtMalePartTimeP3 ,

   [WEmpFeMalePartTimeP3] =  @txtFeMalePartTimeP3

WHERE [LoanSummaryID] = @LoanSummaryID

   

    end

ELSE

begin

INSERT INTO EmploymentDetails 

(

   [LoanSummaryID]

      ,[OfficeId]

      ,[MemberId]

      ,[CenterId]

      ,[SEmpMaleFullTimeP1]

      ,[SEmpFeMaleFullTimeP1]

      ,[SEmpMalePartTimeP1]

      ,[SEmpFeMalePartTimeP1]

      ,[FEmpMaleFullTimeP2]

      ,[FEmpFeMaleFullTimeP2]

      ,[FEmpMalePartTimeP2]

      ,[FEmpFeMalePartTimeP2]

      ,[WEmpMaleFullTimeP3]

      ,[WEmpFeMaleFullTimeP3]

      ,[WEmpMalePartTimeP3]

      ,[WEmpFeMalePartTimeP3]

      ,[isActive]

   

)

values

(

   @LoanSummaryID

      ,@OfficeId

      ,@MemberId

      ,@CenterId

      ,@txtMaleFullTimeP1

      ,@txtFeMaleFullTimeP1

      ,@txtMalePartTimeP1

      ,@txtFeMalePartTimeP1

      ,@txtMaleFullTimeP2

      ,@txtFeMaleFullTimeP2

      ,@txtMalePartTimeP2

      ,@txtFeMalePartTimeP2

      ,@txtMaleFullTimeP3

      ,@txtFeMaleFullTimeP3

      ,@txtMalePartTimeP3

      ,@txtFeMalePartTimeP3

      ,@isActive

 

)

end -- end of else

END