Generate Class Object From SQL Table

declare @TableName sysname = 'TABLE_Name'
declare @result Nvarchar(max) = ''

select @result = @result + '
    private ' + ColumnType + ' ' + ' m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';'
from
(
    select
        replace(col.name, ' ', '_') ColumnName,
        column_id,
        case typ.name
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'char'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by column_id

SET @result = @result + '
'

select @result = @result + '
    public ' + ColumnType + ' ' + ColumnName + ' { get { return m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';} set {m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ' = value;} }' from
(
    select
        replace(col.name, ' ', '_') ColumnName,
        column_id,
        case typ.name
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'char'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by column_id


SELECT CONVERT(NTEXT,@result)

How To Track Database Changes

CREATE DATABASE Audit


USE [Audit]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[RunDbAudit]

(
    @DataBaseName VARCHAR(100)
)
 
AS
BEGIN


DECLARE @AuditDbName VARCHAR(100);
DECLARE @BaseTable VARCHAR(100);
DECLARE @SqlCmd NVARCHAR(MAX);
DECLARE @ParamDef NVARCHAR(MAX);

SET @BaseTable =  '__DB__'+ @DataBaseName ;
SET @AuditDbName = DB_NAME();




DECLARE @AuditExcludedObjects NVARCHAR(MAX);
SET @AuditExcludedObjects = 'fn_diagramobjects,sysdiagrams,'+@BaseTable+'';

DECLARE @BaseTableCount INT;

SET @SqlCmd = 'SELECT @BaseTableCount = COUNT(*)  FROM '+@AuditDbName+'.INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_CATALOG = @AuditDbName
                 AND  TABLE_NAME = @BaseTable';
SET @ParamDef = '@BaseTable VARCHAR(100), @DataBaseName VARCHAR(100), @AuditDbName VARCHAR(100), @BaseTableCount INT OUTPUT';

                 EXECUTE sp_executesql @SqlCmd,
                 @ParamDef,
                 @BaseTable = @BaseTable, @DataBaseName = @DataBaseName, @AuditDbName = @AuditDbName, @BaseTableCount = @BaseTableCount OUTPUT;


--SELECT @SqlCmd;
--SELECT @BaseTableCount;

IF (@BaseTableCount = 0)
BEGIN

    SET @SqlCmd = '
    SELECT    
    @DataBaseName AS [DB_NAME]
    , name AS [OBJECT_NAME]
    , object_id AS [OBJECT_ID]
    , type AS [OBJECT_TYPE]
    , type_desc AS [TYPE_DESC]
    , create_date AS [CREATED_ON]
    , modify_date AS [MODIFIED_ON]
    , CONVERT(DATETIME,NULL) AS [DELETED_ON]
    , CONVERT(BIT,0) AS [MASTER_TABLE]
    , CONVERT(BIT,0) AS [AUDIT_EXCLUDED]
    , CONVERT(VARCHAR(MAX),'''') AS [CHANGE_INFO]
    , CONVERT(DECIMAL(18,2), 1.00) AS [VERSION]
    INTO [dbo].['+@BaseTable+']
    FROM ['+@DataBaseName+'].sys.objects WHERE type in (''P'',''U'',''FN'',''V'');

    UPDATE [dbo].['+@BaseTable+'] SET [AUDIT_EXCLUDED] = 1 WHERE [OBJECT_NAME] IN (@AuditExcludedObjects)
    ';


    SET @ParamDef = '@AuditExcludedObjects NVARCHAR(MAX), @DataBaseName VARCHAR(100)';
    EXECUTE sp_executesql @SqlCmd,
    @ParamDef,
    @AuditExcludedObjects = @AuditExcludedObjects, @DataBaseName = @DataBaseName
    ;

END
ELSE
BEGIN

------------------------------------------------------------ Audit--------------------------------------------------------------

SET @SqlCmd = '

DECLARE @PrevVer DECIMAL(18,2);
DECLARE @PrevVerDt DATETIME;

DECLARE @NextVer DECIMAL(18,2);
DECLARE @NextVerDt DATETIME;

SET @NextVerDt = GETDATE();
SET @PrevVerDt = (SELECT MAX(MODIFIED_ON) FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED] = 0);
SET @PrevVer = (SELECT MAX([VERSION]) FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED] = 0);


SET @NextVer = @PrevVer + 0.01;

IF OBJECT_ID(''tempdb..#temp__DB_OBJECT'') IS NOT NULL DROP TABLE #temp__DB_OBJECT

      SELECT    
      @DataBaseName AS [DB_NAME]
    , name AS [OBJECT_NAME]
    , object_id AS [OBJECT_ID]
    , type AS [OBJECT_TYPE]
    , type_desc AS [TYPE_DESC]
    , create_date AS [CREATED_ON]
    , modify_date AS [MODIFIED_ON]
    , CONVERT(DATETIME,NULL) AS [DELETED_ON]
    , CONVERT(BIT,0) AS [MASTER_TABLE]
    , CONVERT(BIT,0) AS [AUDIT_EXCLUDED]
    , CONVERT(VARCHAR(MAX),'''') AS [CHANGE_INFO]
    , @NextVer AS [VERSION]
    INTO #temp__DB_OBJECT
    FROM ['+@DataBaseName+'].sys.objects WHERE type in (''P'',''U'',''FN'',''V'')
    AND name NOT IN ((SELECT [OBJECT_NAME] FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED]=1))


    UPDATE P
    SET P.MODIFIED_ON = tmp.MODIFIED_ON, P.[VERSION] = tmp.[VERSION]
    FROM [dbo].['+@BaseTable+'] P
    INNER JOIN #temp__DB_OBJECT tmp ON
    (P.[OBJECT_NAME] = tmp.[OBJECT_NAME] AND P.TYPE_DESC = tmp.TYPE_DESC)
    WHERE P.AUDIT_EXCLUDED = 0
    AND P.MODIFIED_ON <> tmp.MODIFIED_ON



    INSERT INTO [dbo].['+@BaseTable+']
    SELECT t.* FROM #temp__DB_OBJECT t
    LEFT OUTER JOIN [dbo].['+@BaseTable+'] DO ON t.[OBJECT_ID] = DO.[OBJECT_ID]
    WHERE DO.[OBJECT_NAME] IS NULL



    UPDATE P
     SET P.DELETED_ON = @NextVerDt, P.[VERSION] = @NextVer
    FROM [dbo].['+@BaseTable+'] P
    INNER JOIN (
        SELECT
        DO.[OBJECT_NAME],
        DO.[OBJECT_ID],
        DO.[OBJECT_TYPE],
        DO.[TYPE_DESC]
        FROM  [dbo].['+@BaseTable+'] DO
        LEFT OUTER JOIN #temp__DB_OBJECT t ON t.[OBJECT_ID] = DO.[OBJECT_ID]
        WHERE t.[OBJECT_NAME] IS NULL AND DO.AUDIT_EXCLUDED = 0
    ) Q ON (P.[OBJECT_NAME] = Q.[OBJECT_NAME] AND P.OBJECT_TYPE = Q.OBJECT_TYPE AND P.TYPE_DESC = Q.TYPE_DESC)
    WHERE P.[DELETED_ON] IS NULL AND P.AUDIT_EXCLUDED = 0


    DECLARE @CurrentVer DECIMAL(18,2);
    SET @CurrentVer = (SELECT MAX(VERSION) FROM [dbo].['+@BaseTable+'] WHERE AUDIT_EXCLUDED = 0)

    SELECT CASE WHEN @CurrentVer = @NextVer
        THEN ''Current Database Version : '' + CONVERT(VARCHAR,@CurrentVer)  + ''  :: Changes info given below.''
        ELSE ''Current Database Version : '' + CONVERT(VARCHAR,@CurrentVer) + ''  :: Changes in last version given below''
        END AS [Message];

    SELECT * FROM [dbo].['+@BaseTable+'] WHERE [VERSION] = @CurrentVer;


IF OBJECT_ID(''tempdb..#temp__DB_OBJECT'') IS NOT NULL DROP TABLE #temp__DB_OBJECT
';


SET @ParamDef = '@DataBaseName VARCHAR(100)';

EXECUTE sp_executesql @SqlCmd
,@ParamDef
,@DataBaseName = @DataBaseName;


------------------------------------------------------------/Audit--------------------------------------------------------------
END

   


END


exec RunDbAudit 'DATABASE NAME' --RUN 2 TIMES FOR 1st RUN

Convert HTML Tables To DataSet In C#

private DataSet ConvertHTMLTablesToDataSet(string HTML) {
        // Declarations
        DataSet ds = new DataSet();
        DataTable dt = null;
        DataRow dr = null;
        DataColumn dc = null;
        string TableExpression = "<table[^>]*>(.*?)</table>";
        string HeaderExpression = "<th[^>]*>(.*?)</th>";
        string RowExpression = "<tr[^>]*>(.*?)</tr>";
        string ColumnExpression = "<td[^>]*>(.*?)</td>";
        bool HeadersExist = false;
        int iCurrentColumn = 0;
        int iCurrentRow = 0;
        // Get a match for all the tables in the HTML
        MatchCollection Tables = Regex.Matches(HTML, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
        // Loop through each table element
        foreach(Match Table in Tables) {
            // Reset the current row counter and the header flag
            iCurrentRow = 0;
            HeadersExist = false;
            // Add a new table to the DataSet
            dt = new DataTable();
            //Create the relevant amount of columns for this table (use the headers if they exist, otherwise use default names)
            // if (Table.Value.Contains("<th"))
            if (Table.Value.Contains("<th")) {
                // Set the HeadersExist flag
                HeadersExist = true;
                // Get a match for all the rows in the table
                MatchCollection Headers = Regex.Matches(Table.Value, HeaderExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
                // Loop through each header element
                foreach(Match Header in Headers) {
                    dt.Columns.Add(Header.Groups[1].ToString());
                }
            } else {
                for (int iColumns = 1; iColumns <= Regex.Matches(Regex.Matches(Regex.Matches(Table.Value, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase)[0].ToString(), RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase)[0].ToString(), ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase).Count; iColumns++) {
                    dt.Columns.Add("Column " + iColumns);
                }
            }
            //Get a match for all the rows in the table
            MatchCollection Rows = Regex.Matches(Table.Value, RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
            // Loop through each row element
            foreach(Match Row in Rows) {
                    // Only loop through the row if it isn't a header row
                    if (!(iCurrentRow == 0 && HeadersExist)) {
                        // Create a new row and reset the current column counter
                        dr = dt.NewRow();
                        iCurrentColumn = 0;
                        // Get a match for all the columns in the row
                        MatchCollection Columns = Regex.Matches(Row.Value, ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
                        // Loop through each column element
                        foreach(Match Column in Columns) {
                                // Add the value to the DataRow
                                dr[iCurrentColumn] = Column.Groups[1].ToString();
                                // Increase the current column
                                iCurrentColumn++;
                            }
                            // Add the DataRow to the DataTable
                        dt.Rows.Add(dr);
                    }
                    // Increase the current row counter
                    iCurrentRow++;
                }
                // Add the DataTable to the DataSet
            ds.Tables.Add(dt);
        }
        return ds;
    }  

Find out the SQL Database Actual Size and used size

set nocount on
select
   [FileSizeMB]   =
      convert(numeric(10,2),sum(round(a.size/128.,2))),
        [UsedSpaceMB]   =
      convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
        [UnusedSpaceMB]   =
      convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
   [Type] =
      case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
   [DBFileName]   = isnull(a.name,'*** Total for all files ***')
from
   sysfiles a
group by
   groupid,
   a.name
   with rollup
having
   a.groupid is null or
   a.name is not null
order by
   case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
   a.groupid,
   case when a.name is null then 99 else 0 end,
   a.name

Query to produce time pattern with 30 minutes interval

DECLARE @lTimeinterval INT = 30;

WITH mycte
     AS (SELECT 1                                                                                 AS Time_ID,
                RIGHT(CONVERT(VARCHAR(16), Dateadd(day, Datediff(day, 0, Getdate()), 0), 120), 5) AS Time_Slot
         UNION ALL
         SELECT Time_ID + 1,
                RIGHT(CONVERT(VARCHAR(16), Dateadd(minute, Time_ID * @lTimeinterval, Dateadd(day, Datediff(day, 0, Getdate()), 0)), 120), 5)
         FROM   mycte
         WHERE  Dateadd(minute, Time_ID * @lTimeinterval, Dateadd(day, Datediff(day, 0, Getdate()), 0)) < Dateadd(day, Datediff(day, 0, Getdate()) + 1, 0))
SELECT Time_Slot
FROM   mycte 

Data Encryption and Decryption in SQL Server

--Step 1: Create a Master Key
SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101

CREATE MASTER KEY ENCRYPTION
BY PASSWORD ='Password!2';

GO
--Step 2: Create Certificate
CREATE CERTIFICATE Cert_Password
ENCRYPTION BY PASSWORD = 'Password!2'
WITH SUBJECT = 'Password protection',
EXPIRY_DATE = '12/31/2099';

--Step 3: Create Symmetric Key
CREATE SYMMETRIC KEY Sym_password
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Cert_Password;

SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 256

GO
--Step 4: Encrypt Data

OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = 'Password!2';

DECLARE @tempSecurity AS TABLE (UserID VARCHAR(200), Password NVARCHAR(1000))
INSERT INTO @tempSecurity (UserID, Password)
VALUES ('schinna',ENCRYPTBYKEY(KEY_GUID(N'Sym_password'), 'PASSWORD'))
CLOSE SYMMETRIC KEY Sym_password;

SELECT * FROM @tempSecurity

--Step 5: Decrypt Data
OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = 'Password!2';
SELECT UserID, CAST(DECRYPTBYKEY([Password]) as varchar(200))
FROM @tempSecurity
CLOSE SYMMETRIC KEY Sym_password;

SELECT * FROM @tempSecurity

GO
--DROP ALL THE KEY
DROP SYMMETRIC KEY Sym_password
GO
DROP CERTIFICATE  Cert_Password
GO
DROP MASTER KEY  

Proper case in sql server

CREATE  Function [dbo].[fn_get_ProperCase](@InputString as varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))

WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)

IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
IF @Index + 1 <= LEN(@InputString)
BEGIN
IF @Char != '''' OR UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
SET @OutputString = STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
SET @Index = @Index + 1
END

RETURN ISNULL(@OutputString,'')


END