The differences between Stored Procedure (SP) and User Defined Functions (UDF) in MSSQL

Written with StackEdit.

Read More »

##How to Extract sql query from LINQ expressions?

###GetCommand You can use the DataContext.GetCommand method to get the generated SQL:

var query = dataContext.Persons.Where(p => p.Name == "Alice");
string generatedSql = dataContext.GetCommand(query).CommandText;

###Display in VisualStudio Output Window Redirect/set the DataContext’s log to Console.Out. Then we can see the SQL statements in the VisualStudio’s output window when the query gets executed.

dataContext.Log = Console.Out;
var query = dataContext.Persons.Where(p => p.Name == "Alice");
var persons = query.ToList();

Written with StackEdit.

Read More »

##Turn on IDENTITY_INSERT

Sometimes we want to copy a table that contains an identity column from another one exactly with the id column’s values kept, e.g. Migration, Recovering from a backup table. In this case we need to turn on the IDENTITY_INSERT, which enables the ability to insert specific values into the target table. And after inserted we should turn it off to bring the behaviour back to normal.

The following commands work on MSSQLServer.

# to turn on
SET IDENTITY_INSERT YourTableName ON
# to turn off
SET IDENTITY_INSERT YourTableName OFF

Written with StackEdit.

Read More »

ElementName As Source Of Content Menu Command Binding

We can’t bind using ElementName for a context menu command binding as popups are not on the visual tree, thus not able to find the element by its name. But we can workaround it using a couple of tricks though…

1. Use RoutedUICommands with a command binding on the UserControl, then no binding is needed.

2. Use the placement target binding on the context menu’s DataContext. This allows you to at least get the data context of the element the context menu appears on to the context menu.

   DataContext=”{Binding RelativeSource={RelativeSource Mode=Self}, Path=PlacementTarget.DataContext}”.

3. Use [ElementSpy][1] to link to the window using a static resource so we can then use a defacto ElementName bindings.

Copied and modified from here

Written with StackEdit.

Read More »

仅仅sybase 的windows客户端有这个问题, isql 没有这个问题. select 的效果也是一样的.

执行以下代码, 若 MyTable 表已经存在, 执行会悄无声息. 不论 @sqlstr的值是什么(比如set @sqlstr=“asbsdf”), 都不会报错.

declare @sqlstr varchar(1024)
set @sqlstr =
    'create table MyTable (
        DiskId              int                 identity,
        NruId               char(32)            not null,
        DiskName            varchar(128)        null,
        LastOfflineTime     int                 null,
        constraint MyTable primary key(DiskId)
    )with identity_gap = 100'

IF NOT EXISTS(select name from sysobjects where name = 'MyTable')
EXECUTE (@sqlstr)
go

经过研究发现, 只要 set/select 一出, EXECUTE 就不与争锋了. 具体原因不明.

declare @sqlstr varchar(1024)
set @sqlstr = 'ssdf'
EXECUTE ("absc")
go

解决方法 目前暂时将语句放入EXECUTE中, 不使用set语句.

IF NOT EXISTS(select name from sysobjects where name = 'MyTable')
EXECUTE (
    'create table MyTable (
        DiskId              int                 identity,
        NruId               char(32)            not null,
        DiskName            varchar(128)        null,
        LastOfflineTime     int                 null,
        constraint MyTable primary key(DiskId)
    )with identity_gap = 100'
)
go

Read More »