- 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.NameTSQL 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.
Fix user, Role already exists in mssql db
Whenever we restore db but user doesn’t exists and user is created after db restore only. following error appears.
sp_change_users_login ‘AUTO_FIX’, ‘coop_sahakari_paanch’
MVC5 Async RedirectToAction
use Async Task Run for async return redirecttoaction
return await Task.Run<ActionResult>(() =>
{
return RedirectToAction(“MultipleLoanEODFix”);
});
Some useful Commands for Check and Maintain MSSQL Database
— Shrink Database
DBCC SHRINKDATABASE (AdventureWorks2008R2, TRUNCATEONLY);
— Rebuild All Index on a table
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
— Reorganize all indexes on the HumanResources.Employee table.
ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ;
— Check DB for database consistency
DBCC CHECKDB
— Reclaims space from dropped variable-length columns in tables or indexed views
DBCC CLEANTABLE
— Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.
DBCC UPDATEUSAGE (AdventureWorks2012)
Basic Pattern for application
PDF File Link: App Pattern
- Use AsyncControllers
- Use await, ToListAsync, SaveChangesAsync, FindAsync
Configure Email in outlook and other email client software
Manual Settings
If you do not see an auto-configuration script for your client in the list above, you can manually configure your mail client using the settings below:
|
Secure SSL/TLS Settings
(Recommended)
|
Non-SSL Settings
(NOT Recommended)
|
||||||||||||||||||||
Notes:
- IMAP email access coordinates between the server and your mail application. Messages that have been read/deleted/replied to will show up as such, both on the server and in the mail application.
- POP3 does not coordinate with the server. Messages marked as read/deleted/replied to in the mail application will not show up as such on the server. This means that future mail downloads with POP3 will show all messages as unread.
- Outgoing mail is sent using SMTP.
- We recommend using POP3 over SSL/TLS or IMAP over SSL/TLS since they provide increased security for your interactions with the remote mail server.