Convert database table to c# class using tsql

TSQL script to get c# class from database tables

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

select @Result = @Result + '
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
replace(, ' ', '_') ColumnName,
column_id ColumnId,
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_' +
end ColumnType,
when col.is_nullable = 1 and 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

Posted in SQL Server

find open port on machine

You can use the NETSTAT command to quickly see all the used and listening ports on your computer. Note it is not a complete substitute for a port scanning tool. If you’d like to have your computer remotely scanned for open ports use our Security Scan instead.

To see a list of listening ports, open Command Prompt and type:

C:\> netstat -an |find /i “listening”

You can change “listening” to “established” to see what ports your computer actually communicates with.

It is also useful to use the -o switch with the NETSTAT command to also get a list of all the owning process ID associated with each connection. You can then use those process ids (PIDs) to find out the name of the processes associated with open/listening ports in the Windows Task Manager

C:\> netstat -ao |find /i “listening”

To see all open, closing, established and listening ports, simply use:

C:\> netstat -a (or netstat -ao in XP/Win2k3)

You can also make the netstat command refresh periodically by adding a number of seconds at the end, for example, to make it refresh every 5 seconds, use something like:

C:\> netstat -an 5

Posted in SQL Server

Check if database exists or not in server

Little script which checks database exists in server or not

DECLARE @dbname nvarchar(128)
SET @dbname = N’Databasename’

FROM master.dbo.sysdatabases
WHERE (‘[‘ + name + ‘]’ = @dbname
OR name = @dbname)))

Posted in SQL Server

Reset Password from membership aspnet_users

first get User Name, application Name, PasswordFormat and Salt from apsnet database.

au.username, aa.ApplicationName, password, passwordformat, passwordsalt
aspnet_membership am
aspnet_users au ON (au.userid = am.userid)
aspnet_applications aa ON (au.applicationId = aa.applicationid)
au.UserName = ‘jignesh.patel’

Use following script to reset password.

SET @changeDate = GETDATE()

EXEC aspnet_Membership_setPassword

Posted in SQL Server

Create SQL script for Tables in Database

select  'create table [' + + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed( as varchar) + ',' + 
        cast(ident_incr( as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name =
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')
Tagged with: ,
Posted in SQL Server

Create and apply Patch in git

Creating Patch in git is quite easy and useful when you want to transfer your changes from one branch to another or from one machine to another.

so here is the command you use:

once you clone repo from git using :

git clone git://
cd xyx
git checkout -b branch_1

git checkout -b : it will create new branch with branch_1 name

creating patch

git log --pretty=oneline -3
git format-patch master --stdout > patch_name.patch

above command create new patch file with name patch_name.patch.

now applying patch:
use following command to easily apply patch to branch, apply command shows what are the changes patch will do in your current branch

git apply --check patch_name.patch

use following command to apply the patch

git am --signoff < patch_name.patch
Tagged with:
Posted in SQL Server

TSQL Cursor Syntext

declare cursor cust_cursor fast_forward for
select custid from tblCustomer
Open cust_cursor
fetch next from cust_cursor into @custId
while @@fetch_status = 0
fetch next from cust_cursor into @custId
close cust_cursor
deallocate cust_cursor
Tagged with: ,
Posted in SQL Server

Get every new post delivered to your Inbox.

Join 36 other followers

%d bloggers like this: