Loan Settlement Letter

Kasun Nanayakkara — Wed, 26 Mar 2025 09:17:35 +0000

(Date)
The Manager
Bank Address

Personal loan settlement

I'm writing this letter to inform you of my desire to settle my loan (number ) earlier than the due date.I hope to settle in 2025/March(date or month) month.I am eager to conclude this process at the earliest, hence I would appreciate it if you could facilitate the prompt processing of my request.

Name:
Personal Loan: (Loan number)

Loan amount: ($ Amount)

ID: (ID number)

Address:

Your faithful

(Name & Sign)

On-line compilers

Kasun Nanayakkara — Thu, 27 Feb 2025 15:29:54 +0000

SQL server Online compiler sql-server/online-compiler

MSSQL-Change Identity seed, table records using cursors and verify records.

Kasun Nanayakkara — Thu, 27 Feb 2025 08:16:31 +0000

Find the tables that belong to the column name. Show the primary tables and foreign key tables as a list here.

SELECT      c.name  AS 'ColumnName'
            ,(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%MyColumnName%'
ORDER BY    TableName
            ,ColumnName;

ColumnName         TableName
MyColumnName       dbo.FkTable1
MyColumnName       dbo.FkTable2
MyColumnName       dbo.MyTable

The task here is to update data in MyTable. In this table, I need to change the records code with primary keys 1, 2, and 3, and update other records primary keys as well. Additionally, I need to verify the current primary keys of the records and correct them if necessary.I will also need to check the current structure of MyTable and update the structure accordingly. After making the updates, the identity seed should be reset.

Current Mytable 
id     code     name
1      abc      abc-disc 
2      efg      efg-disc 
3      ijh      ijh-disc 
20     kmn      kmn-disc 
30     qrs      qrs-disc 

Udated Mytable
id        code     name
1         cba      abc-disc 
2         gfe      efg-disc 
3         hji      ijh-disc 
100000000 kmn      kmn-disc 
100000001 qrs      qrs-disc

Stop the current identity check

SET IDENTITY_INSERT Mytable ON
GO

Declare a table parameter to capture the current data before making any changes to the MyTable, so that you can keep the original data intact and then modify it accordingly. This is a common approach to ensure data integrity during updates. You can create a table variable or temporary table to store the original data before making any changes.

DECLARE @TempMytable TABLE (ID INT, Code VARCHAR(MAX), Name VARCHAR(MAX), NewRecordID INT);

Insert Mytable record to tmp table call TempMytable.

INSERT INTO @TempMytable(ID, Code, Name)
SELECT ID, Code, Name
FROM Mytable;

DECLARE @ID INT, @Code VARCHAR(MAX), @Name VARCHAR(MAX)

Set the default identity seed value and other related parameter values.

DECLARE @Seed INT= 10000000, @NewRecordID INT = 1, @NewCode VARCHAR(MAX) = '';

Disable the check constraint.

ALTER TABLE MPP NOCHECK CONSTRAINT [FK_MPP_Mytable];
ALTER TABLE Mytable NOCHECK CONSTRAINT ALL;

Remove the unique constraint.

ALTER TABLE Mytable
DROP CONSTRAINT U1_Mytable, UK_Mytable_Name;

Disable the trigers

ALTER TABLE MyTable DISABLE TRIGGER Mytable_InsteadOfDTrig

Declare the cursor and refer to the records available in MyTable because I created a temporary table. When the cursor runs, it gets the current table information. If the table is updated, the record will also be fetched and processed. So, I want to avoid that behavior and run based on the previous (old) state of the table and data.

DECLARE db_cursor CURSOR FOR
SELECT ID, Code, Name
FROM @TempMytable

BEGIN
    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @ID, @Code, @Name
    WHILE @@FETCH_STATUS = 0  
    BEGIN

        IF @Name = 'abc-disc' OR @Name = 'efg-disc' OR  @Name = 'ijh-disc'
        BEGIN
            /* Insert / Update system code */

            IF @Name = 'abc-disc' 
                SELECT @NewRecordID = 1, @NewCode = 'cba';
            ELSE IF @Name = 'efg-disc' 
                SELECT @NewRecordID = 2, @NewCode = 'gfe';
            ELSE IF @Name = 'ijh-disc'
                SELECT @NewRecordID = 3, @NewCode = 'hji';

            IF @ID = @NewRecordID
            BEGIN
                UPDATE Mytable SET Code = @NewCode WHERE ID = @NewRecordID
            END
            ELSE IF EXISTS(SELECT * FROM Mytable WHERE ID = @NewRecordID)
            BEGIN 
                UPDATE Mytable SET Code = @NewCode,
                Name = @Name
                WHERE ID = @NewRecordID
            END 
            ELSE
            BEGIN
                INSERT INTO Mytable(ID, Code, Name)
                VALUES(@NewRecordID, @NewCode, @Name)
            END

        END

In here, I want to check whether records already exist in your table before inserting new ones, so you can avoid inserting duplicate records that would violate a unique constraint.

 ELSE IF(@ID < 10000000)
/* Check table id morethan the 10,000,000 */  
        BEGIN
            /* Insert record as a new record Mytable */

            INSERT INTO Mytable(ID, Code, Name)
            VALUES(@Seed, @Code, @Name)

            SELECT @NewRecordID = @Seed;

            SELECT @Seed = @Seed + 1;

        END

In this case, I need to get the new record ID into a temporary table called @TempMyTable to check if the records have new IDs

 /* Update @TempMytable tables with new ID */ 

        UPDATE @TempMytable 
            SET NewRecordID = CASE WHEN @ID > @NewRecordID THEN @ID
                ELSE @NewRecordID
            END
            WHERE ID = @ID;

        PRINT  CAST(@ID AS VARCHAR) +' - ' + @Code +' - ' + @Name +' - ' + CAST(@NewRecordID AS VARCHAR) + ' - ' + @NewCode

        FETCH NEXT FROM db_cursor
        INTO @ID, @Code, @Name

    END 
    CLOSE db_cursor
    DEALLOCATE db_cursor
END

Reset @id , @NewRecordsID values

SELECT @ID = 0, @NewRecordID = 0;

/* Verify date at @TempMytable table and foreign key tables */

SELECT * FROM @TempMytable
id     code     name        newRecordid
1      abc      abc-disc    1
2      efg      efg-disc    2
3      ijh      ijh-disc    3
20     kmn      kmn-disc    100000000
30     qrs      qrs-disc    100000002

Check if foreign key tables have records.

SELECT m.ID,Count(m.pkid) FkTable1_Count FROM FkTable1 m GROUP BY m.ID
SELECT m.ID,Count(m.pkid) FkTable2_Count FROM FkTable2 m GROUP BY m.ID
FkTable1
id          FkTable1_Count 
1           1170
2           5522

FkTable2
id          FkTable2_Count 
1           15877
2           155895
3           187958

Declare another cursor and refer to the records available in the temporary table called @TempMyTable to update the new record ID.

DECLARE db_newCursor CURSOR FOR
SELECT ID, NewRecordID
FROM @TempMytable

BEGIN
    OPEN db_newCursor  
    FETCH NEXT FROM db_newCursor INTO @ID, @NewRecordID
    WHILE @@FETCH_STATUS = 0  
    BEGIN

Update the foreign key value as per the newly generated record ID.

 /* Update foreign key tables*/
        IF @ID = @NewRecordID
             If record id s is same no need to change. 
             PRINT 'IDs are same. '+ CAST(@ID AS VARCHAR) + ' -> ' + CAST(@NewRecordID AS VARCHAR);
        ELSE 
        BEGIN
            PRINT 'FkTable1 # ' + CAST(@ID AS VARCHAR) + ' -> ' + CAST(@NewRecordID AS VARCHAR);
            UPDATE FkTable1 SET ID = @NewRecordID
            WHERE ID = @ID;

            PRINT 'FkTable2 # ' + CAST(@ID AS VARCHAR) + ' -> ' + CAST(@NewRecordID AS VARCHAR);
            UPDATE FkTable2 SET ID = @NewRecordID
            WHERE ID = @ID;

        END

        FETCH NEXT FROM db_newCursor INTO @ID, @NewRecordID

    END 
    CLOSE db_newCursor
    DEALLOCATE db_newCursor
END

start the current identity check

SET IDENTITY_INSERT Mytable OFF
GO

Why does the previous state not delete the record? Because it has a foreign key value, and it will be affected when I delete the record. I know the old records have a range from 4 to 99999999, and those records should be deleted in the delete query.

/* Delete old Mytable */
DELETE FROM Mytable WHERE ID BETWEEN 4 AND 9999999

Set up the identity seed value to match the maximum record value.
Generally, users reset the identity seed using this snippet:
DBCC CHECKIDENT ('MyTable', RESEED, @IdentSeed).
However, when I use it, I get the following error:
Checking identity information: current identity value '52228', current column value '52228'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I used the keyword WITH NO_INFOMSGS; to overcome the issue.
This problem is described in the following link: identity-column-value-falling-behind-randomly

/* Set new identity seed */
DECLARE @IdentSeed INT = 10000000;
SELECT @IdentSeed = Max(ID) FROM Mytable;

PRINT 'New identity seed # ' + CAST(@IdentSeed AS VARCHAR);
DBCC CHECKIDENT ('Mytable', RESEED, @IdentSeed)  WITH NO_INFOMSGS;
GO

/* Add uniqe constraint */
SET ANSI_PADDING ON
GO

Add a unique key constraints for table records.

/****** Object:  Index [Mytable] */
ALTER TABLE [dbo].[Mytable] ADD  CONSTRAINT [U1_Mytable] UNIQUE NONCLUSTERED 
(
    [Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

ALTER INDEX [U1_Mytable] ON [dbo].[Mytable] DISABLE
GO
PRINT 'Added constrant - U1_Mytable';

/****** Object:  Index [UK_Mytable_Name] */
ALTER TABLE [dbo].[Mytable] ADD  CONSTRAINT [UK_Mytable_Name] UNIQUE NONCLUSTERED 
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
PRINT 'Added constrant - UK_Mytable_Name';

ALTER TABLE MPP CHECK CONSTRAINT [FK_MPP_Mytable]
GO
ALTER INDEX ALL ON Mytable REBUILD

GO

Enable the trigers

ALTER TABLE MyTable ENABLE TRIGGER MyTable_InsteadOfDTrig
GO

.NET MVC Architecture

Kasun Nanayakkara — Sat, 15 Feb 2025 15:31:28 +0000

MVC describes the Model-View-Controller architecture.

Model describes the data layer. The data layer contains data attributes, as well as getter and setter methods. The data can be defined using specific behaviors, such as DisplayName, Range, Required, etc.
The data model can also describe the functionality related to the data structure, such as data retrieval, updates, and deletion functions. Some models manipulate data into another format.

View describes the user interface that presents the data to the user. It can include elements like textboxes, dropdowns, buttons, tables, and other UI components.

Controller describes the API and control methods for retrieving the data. It communicates using routing methods. There are several methods to route to the API. The default route is ‘Api/Controller/Action/id’, but the user can define their preferred routing method. For example,

• /api/contacts
• /api/contacts/21
• /api/products/apple

Can an API use HTTP verbs?

Yes, an API can use HTTP verbs such as [HttpGet], [HttpPost], [HttpPut], [HttpDelete], etc.
Additionally, the user can define the action name using the [ActionName("name")] attribute. This allows the user to hide the mechanism of the action. For example, the user can define an API route as Api/enroll/enrollment/1.

[RoutePrefix("enroll")] 
public class EnrollmentConroller: APIController(
[HttpPost]
[ActionName("enrollment")]
public void getEnrollment(int id){}

Router attributes can be added to manipulate the data directly.

[Route("patient /{EnrollmentID}/enrollment")]
[HttpGet]
public IEnumerable<Enrollment> get enrollment (int EnrollmentID) { }
[Route("patient / enrollment ")]
[HttpPost]
 public HttpResponseMessage updateEnrollment(Enrollment obj) { }

Lifecycle of the MVC

User request ->
Fetch the routing table ->
Execute router ->
Controller initiate ->
Execute action ->
Execute/Map result ->
view engine maps the result to UI ->
Retrieve data / request

DEV Community: Kasun Nanayakkara

Loan Settlement Letter

On-line compilers

MSSQL-Change Identity seed, table records using cursors and verify records.

.NET MVC Architecture