Madhyapur Hospital Backup Plan

Application/Files/Database to backup

SN Application Description Runtime User Uploads Database
1 Main MIS Web Application Yes Yes Yes
2 Desktop Application Yes No No
3 Payroll Application Yes Yes Yes
4 Inventory Application Yes No Yes
5 Main MIS Web Application [2073] Yes No Yes
6 Old Record Check Application Yes Yes Yes

Fix User Server Principal authentication error after db restore

use database_name;
— ALTER DATABASE database_name SET READ_WRITE

——– Reset SQL user account guids ———————
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = su.name
FROM sysusers su
JOIN sys.server_principals sp ON sp.name = su.name
WHERE issqluser = 1 AND
(su.sid IS NOT NULL AND su.sid <> 0x0) AND
suser_sname(su.sid) is null
ORDER BY su.name

OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName

WHILE (@@fetch_status = 0)
BEGIN
–PRINT @UserName + ‘ user name being resynced’
exec sp_change_users_login ‘Update_one’, @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END

CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur

Getting Started with .Net MVC c#

Using DB First Approach

Database Part MSSQL Management Studio

  • Create DB Tables,
  • Views and
  • Stored Procedure as requirement

Visual Studio Application Part

  • Create New Project
  • Create Separate Projects for DAL
  • Add Reference of DAL Project to main project
  • Add Nuget package of Entity Framework to both projects [DAL and Main]
  • Create EDMX Model from Database to project in DAL Project
  • Build Project
  • Add scaffolding Controllers
  • Test Application

Using Code First Approach

Visual Studio Application Part

  • Create New Project
  • Create Separate Projects for DAL
  • Add Reference of DAL Project to main project
  • Add Nuget package of Entity Framework to both projects [DAL and Main]
  • Create Models and DB Context required for matching database structure
  • Build Project
  • Add scaffolding Controllers
  • Test Application

TSQL Clear All Database objects

use dbname;

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = ‘ALTER TABLE [‘ + tc2.TABLE_NAME + ‘] DROP [‘ + rc1.CONSTRAINT_NAME + ‘]’
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec sp_executesql @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

EXEC sp_MSforeachtable ‘DROP TABLE ?’
GO

Steps to add model for MFI UI project which is same as API Request Model

  • Add Common Models to MFI_API_MODEL Project
  • Build project and make error free
  • Change Nuget Package Version in MFI_API_MODEL.nuspec file
  • Generate Nuget Package from CMD using command:
    • nuget pack mfi_api_model.nuspec -prop configuration=release
  • Host Nuget Package to accessible server location in Ants or local computer
  • Open Own UI project,
  • Update Package version

MSSQL Check Running Queries

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Get Size of All Tables of MSSQL Database

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS ‘Indexsize_KB’
FROM
sys.indexes AS i JOIN
sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id JOIN
sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY
i.OBJECT_ID,
i.index_id,
i.name
ORDER BY
OBJECT_NAME(i.OBJECT_ID),
i.index_id

Next Query:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

TSQL Min, Max, AVG and Last Value in single query

declare @MyTable table
(
SN INT
,[Month] INT
,Acc INT
,Bal INT
)
— GO

INSERT INTO @MyTable VALUES
(1, 7, 101, 1000),
(2, 7, 101, 800),
(3, 7, 101, 1700),
(4, 8, 101, 1200),
(5, 8, 101, 900),
(6, 9, 101, 2500)
— GO

select distinct Acc, [Month], MinBal, AvgBal, MaxBal, LastVal from
(
SELECT sn,Acc,[Month] ,Bal
, MIN(Bal) OVER(PARTITION BY Acc,[Month]) MinBal
, AVG(Bal*1.) OVER(PARTITION BY Acc,[Month]) AvgBal
, MAX(Bal) OVER(PARTITION BY Acc,[Month]) MaxBal
, FIRST_VALUE(Bal) OVER(PARTITION BY Acc,[Month] ORDER BY SN DESC) lastVal
FROM @MyTable
) y
–group by Acc, [Month], MinBal, AvgBal, MaxBal, LastVal

SELECT Acc,[Month],MAX(MinBal)MinBal,MAX(AvgBal)AvgBal,MAX(MaxBal)MaxBal,MAX(lastVal)lastVal
FROM
(
SELECT sn,Acc,[Month] ,Bal
, MIN(Bal) OVER(PARTITION BY Acc,[Month]) MinBal
, AVG(Bal*1.) OVER(PARTITION BY Acc,[Month]) AvgBal
, MAX(Bal) OVER(PARTITION BY Acc,[Month]) MaxBal
, FIRST_VALUE(Bal) OVER(PARTITION BY Acc,[Month] ORDER BY SN DESC) lastVal
FROM @MyTable
)u GROUP BY Acc,[Month]

 

Sweet one solutions suggested:

select *
from
( SELECT sn, Acc, [Month], Bal
, MIN(Bal) OVER(PARTITION BY Acc, [Month]) MinBal
, AVG(Bal) OVER(PARTITION BY Acc, [Month]) AvgBal
, MAX(Bal) OVER(PARTITION BY Acc, [Month]) MaxBal
, row_number() OVER(PARTITION BY Acc, [Month] ORDER BY SN DESC) as rn
from @MyTable
) tt
where rn = 1
ORDER By sn

Basic Controller Variables

  • If controller is related to central only i.e it contains dbCore only it should inherit from BaseAsyncController
  • If controller is related to Organisation also i.e it contains both dbCore and dbOrg it should inherit from BaseAsyncFOController
  • While inheriting Any Controller from BaseAsyncController or BaseAsyncFOController, it should initialise base controller with respective variables also.
  • eg For BaseAsyncFOController:
using System;
 
public class DataMaintenanceController : BaseAsyncFOController
{
    #region Constructor
    public DataMaintenanceController(
        CoreAccountingMasterEntities dbCore
        , OrgAccDBEntities dbOrg
        , IErrorLog ErrorLog
        , DBHelper DBHelper
        , IAuthServices AuthService
        //,... extra
        )
    : base(dbCore, AuthService, ErrorLog, dbOrg, DBHelper)
    {
        //..extra code
    }
    #endregion
}
  • eg For BaseAsyncController
using System;
 
public class DataMaintenanceController : BaseAsyncController
{
    #region Constructor
    public DataMaintenanceController(
        CoreAccountingMasterEntities dbCore
        ,IErrorLog ErrorLog
        ,IAuthServices AuthService
        //,.. extra
        )
    : base(dbCore, AuthService, ErrorLog)
    {
        //..extra code
    }
    #endregion
}
  • The Result controller must be inside autofac config/startup file in order to make it start automatically. File: App_start/AutoFacConfig.cs
  • Code to Init Controller:
  • builder.RegisterType<DataMaintenanceController>();//.InstancePerRequest();
  • If the controller already contains variable for dbCore or dbOrg or AuthService or ErrorLog or DBHelper, then we should rename that variable to matching variable name with our BaseAsyncFOController or BaseAsyncController.
  • Our Variable Name in BaseAsyncController and BaseAsyncFOController are:
  • _dbCore, _dbOrg, _AuthService, _ErrorLog, _DBHelper
  • Also Remove base Controller variable initialisation in derived controller.