C# : Convert model class from database table using stored procedure

declare @TableName sysname = 'TableName'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        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 'string'
            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,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    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 ColumnId

set @Result = @Result  + '
}'

print @Result
Advertisements

Optimized query to find third and nth highest value or salary from given table in SQL

Table name : demotable 

select * from demotable

3rd maximum value:

SELECT TOP 1 value FROM (
   SELECT DISTINCT TOP 3 value
   FROM demotable
   ORDER BY value DESC) AS val
ORDER BY value ASC

OR 

SELECT MAX(value)
FROM demotable
WHERE value IN(SELECT DISTINCT TOP 3 value FROM demotable ORDER BY value ASC)

 

To get Nth highest value from above table

Nth maximum salary:

SELECT MAX(value)
FROM demotable
WHERE value IN(SELECT TOP N value FROM demotable ORDER BY valueASC)

The SQL Server Service Broker for the Current Database is not Enabled, and as a Result Query Notifications are not Supported. Please Enable the Service Broker for this Database

Start SQL Server and write this query to enable broker.

ALTER DATABASE Demo SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
To check weather broker is enable or not run this query.

SELECT is_broker_enabled FROM sys.databases WHERE name = ‘Demo’

To kill all the database running process, write this query.

USE master

go

DECLARE @dbname sysname

SET @dbname = ‘Demo’

DECLARE @spid int

SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)

WHILE @spid IS NOT NULL

BEGIN

EXECUTE (‘KILL ‘ + @spid)

SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid

END

ALTER DATABASE @dbname SET ENABLE_BROKER

NoteDemo is my database name.

SQL JOINS

DESCRIPTION

SQL JOINS are used to retrieve data from multiple tables. A SQL JOIN is performed whenever two or more tables are joined in a SQL statement.

There are 4 different types of SQL joins:

  • SQL INNER JOIN
  • SQL LEFT OUTER JOIN
  • SQL RIGHT OUTER JOIN
  • SQL FULL OUTER JOIN

Inner Join

Consider there are two tables as following

a) Student Table                                                           b) Advisor Table

student                            advisor

 

Now let us join these tables using INNER JOIN .

SQL>    SELECT * FROM Student S  

INNER JOIN Advisor A

ON

S.Advisor_Id=A.Advisor_Id

The result would be as follows.

innerjoin