Export DataSet To Excel in MVC

NameSpace :
using System.Web.Hosting;
using ClosedXML.Excel;

Code  :

[MTAThread]
        private void ExportDataSetToExcel(DataSet ds, String excelName)
        {

            string path = HostingEnvironment.MapPath("~/FolderName/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            path = path + excelName;
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(ds.Tables[0]);
                wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                wb.Style.Font.Bold = true;
                wb.SaveAs(path, false);
            }
        }

Example:


DataSet objds = new Dataset();
ExportDataSetToExcel(objds, "FileName.xlsx");

DataTable To JSON With StringBuilder in MVC

Namespace 

using System.Text;

code :

      /// <summary>
        /// Datatable to JSON
        /// <CreatedBy>Ashish Srivastava</CreatedBy>
        /// <Date>12 Dec 2016</Date>
        /// </summary>
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public string DataTableToJSONWithStringBuilder(DataTable table)
        {
            var JSONString = new StringBuilder();      
            if (table.Rows.Count > 0)
            {
                JSONString.Append("{  \"status\":\"1\" ,\"result\": [");
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    JSONString.Append("{");
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (j < table.Columns.Count - 1)
                        {
                            JSONString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");
                        }
                        else if (j == table.Columns.Count - 1)
                        {
                            JSONString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");
                        }
                    }
                    if (i == table.Rows.Count - 1)
                    {
                        JSONString.Append("}");
                    }
                    else
                    {
                        JSONString.Append("},");
                    }
                }
            }
            else
            {
                JSONString.Append("{  \"status\":\"0\" ,\"result\": [");
                JSONString.Append("{\"value\":" + "\"No record found\"}");
            }          
           JSONString.Append("]}");
            return JSONString.ToString();
        }

SQL Query Time Execute Check

1. SELECT    
2.     qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,  
3.     qs.total_elapsed_time / 1000000.0 AS total_seconds,  
4.     qs.execution_count,  
5.     SUBSTRING (qt.text,qs.statement_start_offset/2,   
6.          (CASE WHEN qs.statement_end_offset = -1   
7.             THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2   
8.           ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,  
9.     o.name AS object_name,  
10.     DB_NAME(qt.dbid) AS database_name  
11. FROM sys.dm_exec_query_stats qs  
12.     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt  
13.     LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id  
14. WHERE qt.dbid = DB_ID()  
15. ORDER BY average_seconds DESC;  

How to check SQL Database missing Index

1. SELECT   
2. dm_mid.database_id AS DatabaseID,  
3. dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,  
4. dm_migs.last_user_seek AS Last_User_Seek,  
5. OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],  
6. 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'  
7. + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')   
8. + CASE  
9. WHEN dm_mid.equality_columns IS NOT NULL  
10. AND dm_mid.inequality_columns IS NOT NULL THEN '_'  
11. ELSE ''  
12. END  
13. + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')  
14. + ']'  
15. + ' ON ' + dm_mid.statement  
16. + ' (' + ISNULL (dm_mid.equality_columns,'')  
17. + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns   
18. IS NOT NULL THEN ',' ELSE  
19. '' END  
20. + ISNULL (dm_mid.inequality_columns, '')  
21. + ')'  
22. + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement  
23. FROM sys.dm_db_missing_index_groups dm_mig  
24. INNER JOIN sys.dm_db_missing_index_group_stats dm_migs  
25. ON dm_migs.group_handle = dm_mig.index_group_handle  
26. INNER JOIN sys.dm_db_missing_index_details dm_mid  
27. ON dm_mig.index_handle = dm_mid.index_handle  
28. WHERE dm_mid.database_ID = DB_ID()  
29. ORDER BY Avg_Estimated_Impact DESC  
30. GO 

Remove HTML all css Microsoft word (MS WORD) format

public static string StripHtml(string source)
    {
        source = Regex.Replace(source, "(<style.+?</style>)|(<script.+?</script>)", "", RegexOptions.IgnoreCase | RegexOptions.Singleline);
        source = Regex.Replace(source, "(<img.+?>)", "", RegexOptions.IgnoreCase | RegexOptions.Singleline);
        source = Regex.Replace(source, "(<o:.+?</o:.+?>)", "", RegexOptions.IgnoreCase | RegexOptions.Singleline);
        source = Regex.Replace(source, "<!--.+?-->", "", RegexOptions.IgnoreCase | RegexOptions.Singleline);
        source = Regex.Replace(source, "class=.+?>", ">", RegexOptions.IgnoreCase | RegexOptions.Singleline);

        return source = Regex.Replace(source.Replace(System.Environment.NewLine, "<br/>"), "<[^(a|img|b|i|u|ul|ol|li)][^>]*>", " ");

    }

SQL query maximum time execution check

SELECT TOP 20
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2,
         (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC;

SQL Query Indian all bank and loop example

CREATE TABLE dbo.M_Bank
(
ID           INT IDENTITY NOT NULL,
MB_NAME      NVARCHAR (50),
MB_SNAME     NVARCHAR (20),
MB_ADDUSER   INT,
MB_ADDDate   DATETIME CONSTRAINT DF_M_Bank_MB_ADDDate DEFAULT (getdate()),
MB_MODUSER   INT,
MB_MODDate   DATETIME,
MB_ISActive  BIT CONSTRAINT DF_M_Bank_ISActive DEFAULT ((1)),
MB_ISDeleted BIT CONSTRAINT DF_M_Bank_ISDeleted DEFAULT ((0)),
MB_IP        VARCHAR (30),
CONSTRAINT PK_M_Bank PRIMARY KEY (ID)
)
GO

--Create Function
CREATE FUNCTION [dbo].[nop_splitstring_to_table]
(
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @output TABLE(
    data NVARCHAR(MAX)
)
BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0
            SET @end = LEN(@string) + 1

        INSERT INTO @output (data)
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
    END
    RETURN
END

GO



DECLARE @BankNameDetails NVARCHAR(4000)=
'Axis Bank,Allahabad Bank,American Express,Andhra Bank,Arab Bangladesh,Bank of Baroda India,Bank Muscat,Bank of America
Bank of India,Bank of Maharashtra,Bank of Punjab,Bank of Rajasthan,Barclays Bank PLC,Bharat Overseas,Canara Bank,Catholic Syrian
,Centurion,Ceylon,Citibank,Corporation,Cosmos Co-operative Bank,DBS,Dena,Deutsche Bank,Development Credit,
Dhanlakshmi,Export-Import Bank Of India,Federal Bank India,Global Trust,HDFC,Hongkong Shanghai Banking,
ICICI Bank,IDBI Bank,Ind Bank Housing,Indian Overseas,IndusInd Bank,Industrial Development,ING Vysya,Jammu and Kashmir
,JP Morgan Chase,Karnataka,Karur vysya,Kotak Mahindra,Lakshmi Vilas,Lord Krishna,Mizuho Corporate,Mudra Bank,
The Nainital Bank Ltd.,North Knara G.S.B. Co-op.,Oriental Bank of Commerce,Punjab and Sind,Punjab National,Ratnakar,
Reserve Bank of India,Royal Bank of Scotland,SBI Commercial,Shamrao Vithal Co-operative,South Indian,
Standard Chartered,State Bank Of Bikaner & Jaipur,State Bank of Hyderabad,State Bank of India,State Bank of Indore,
State Bank of Mysore,State Bank of Patiala,State Bank of Travancore,Syndicate Bank,Tamilnad Mercantile,Union Bank Of India,
UCO Bank,United Bank of India,Vijaya Bank,Yes Bank'

SELECT row_number() OVER(ORDER BY data ASC) AS ROWnno,  data into #temp FROM dbo.nop_splitstring_to_table (@BankNameDetails, ',')

DECLARE @MB_NAME NVARCHAR(50)='Allahabad Bank'


DECLARE @TOTALV BIGINT
DECLARE @InitialValue BIGINT
SET @InitialValue=1
SET @TOTALV=12
SELECT @TOTALV=Count(*) from #temp
WHILE (@InitialValue<=@TOTALV)
BEGIN
SELECT @MB_NAME=data  from #temp WHERE ROWnno=@InitialValue
IF NOT exists(SELECT 1 FROM M_Bank WHERE MB_NAME=@MB_NAME)
BEGIN
INSERT INTO dbo.M_Bank (MB_NAME, MB_SNAME, MB_ISActive, MB_ISDeleted, MB_IP)
VALUES (rtrim(ltrim(@MB_NAME)), '', 1, 0, '127.0.0.1')
SELECT -200
END
print @InitialValue
SET @InitialValue=@InitialValue+1
END
drop table #temp