drop table if exists in SQL Server

/* check if a table already exits before we drop it*/
IF EXISTS(SELECT name FROM sys.tables
WHERE name = 'Table_TempForTest')
BEGIN
/* drop table */
DROP TABLE Table_TempForTest
END
GO

/* see all the tables */
select * from sys.tables

powershell can't recognize the vairable setting for dtexec.exe

when I run dtexec.exe in powershell,the value after ";" is treated as a cmdlet and throw a exception" not recognized as a cmdlet, function, operable program, or script file".

This script can execute under cmd-line, but not in powershell.....

dtexec /f "C:\Users\Administrator\Documents\Visual Studio 2008\Projects\Integration Services Project1\Integration Services Project1\FileCopyPackage.dtsx" /set \package.variables[User::SourceFileName].Value;"C:\sharefolder\ssiscopied"

在64位机上运行dtexec转换Excel数据

在64位操作系统上，dtexec默认是在64位模式下运行的，但是在三种情况下我们需要让dtexec运行于x86的模式:

• 运行sql server 2000的DTS包

• DTS包中使用的托管的.NET Framework Data Provider或者是本地OLE DB provider不支持64位模式

• 脚本中引用的其它程序集或Com组件没有64位版本或者没有安装64位版本

如果我们用DTS来转换Excel数据，比如将数据导入sqlserver 2008 express，那么在读取Excel时就会使用OLE DB provider。OLE DB provider目前是没有64位版本的，按照前面说的这意味着在64模式我们无法使用DTS来导出Excel，而必须使用32位的模式。在dtexec的时候可以通过设置/x86参数来制定运行32位模式：

cd C:\Program Files\Microsoft SQL Server\100\DTS\Binn

dtexec /x86 /f c:\Sharefolder\Package2.dtsx /connection SourceConnectionExcel;"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sharefolder\BusinessTemplate.xls;Extended Properties=Excel 8.0;HDR=YES" /connection DestinationConnectionOLEDB;"Data Source=.\SQLEXPRESS;Intial Catalog=SSISSampleDb;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=false"

pause

上面的例子中我们通过参数的形式重新设置了两个连接的值。在dtexec运行时，每个连接都可以通过一个单独的/connection参数来制定新的值，当有多个连接的时候，就设定多个/connection参数，这个好像在文档中没有提到。

参考资料：
64-bit Considerations for Integration Services

sqlite 分页查询

select * from talbe_name limit @offset,@pageSize

一般 offset = pageSize * pageIndex

Entity Framework Tips

by AlexJ : http://blogs.msdn.com/alexj/archive/2009/03/26/index-of-tips.aspx

Hopefully if you're reading this you've noticed that I've started a series of Tips recently. The Tips will mostly apply to Entity Framework.

Seeing as I expect to have a lots of tips, it probably makes sense to have some sort of index. That is what this is post is, as I add a new tip I will add it to this page too.

If you have any suggested topics for tips please let me know by leaving a comment or emailing me directly at Microsoft (Alexj is my alias at Microsoft, and emails at Microsoft are in the form alias@microsoft.com)

Without further ado here are what I have so far:

Tip 25 – How to get Entities by key the easy way

Tip 24 – How to get the ObjectContext from an Entity

Tip 23 – How to fake Enums in EF 4

Tip 22 - How to make Include really Include

Tip 21 – How to use the Single() operator – EF 4.0 only

Tip 20 – How to deal with Fixed Length Keys

Tip 19 – How to use Optimistic Concurrency with the Entity Framework

Tip 18 – How to decide on a lifetime for your ObjectContext

Tip 17 – How to do one step updates with AttachAsModified(..)

Tip 16 – How to mimic .NET 4.0’s ObjectSet today

Tip 15 - How to avoid loading unnecessary Properties

Tip 14 - How to cache Entity Framework Reference Data

Tip 13 - How to Attach an Entity the easy way

Tip 12 - How to choose an Inheritance Strategy

Tip 11 - How to avoid Relationship Span

Tip 10 - How to understand Entity Framework jargon

Tip 9 - How to delete an object without retrieving it

Tip 8 - How to write 'WHERE IN' style queries using LINQ to Entities

Tip 7 - How to fake Foreign Key Properties in .NET 3.5 SP1

Tip 6 - How and when to use eager loading

Tip 5 - How to restrict the types returned from an EF Query

Tip 4 - Conceptual Schema Definition Language Rules

Tip 3 - How to get started with T4

Tip 2 - Entity Framework Books

Tip 1 - How to sort Relationships in the Entity Framework

Enjoy.

指定程序运行时的CultureInfo

程序的执行结果有时候跟Culture密切相关，比如在格式化“钱”的时候：

textBoxMoneyWonFiltered.Text = moneyWon.ToString("C");

假设moneyWon的值是24.1，在不同的Culture下显示的结果会不同，比如￥24.1（中国）或者$24.1（美国）。对于一个支持Globalization的程序而言，设定基于应用程序或者线程的Culture是一个非常好的习惯，可以避免一些怪异的情况出现。

对于一个Windows程序，默认使用与系统相同的Culture设定，要修改程序特有的Culture很简单，只需要在启动的时候添加如下代码：

//覆盖系统默认的 zh-CHS
Application.CurrentCulture = new System.Globalization.CultureInfo("en-US");

Linq to SQL要复活吗？

自从微软推出了EntityFramework（EF）之后，linq to sql的开发就几乎是中断了，原先开发linq to sql的人转而为EF编写代码，甚至连微软ado.net team的人也推荐开发人员转向EF。.Net社区里基本上认为linq to sql已经被微软枪毙了。这里有一篇关于linq to sql是否已死的讨论：Is Linq to SQL truly dead? .

然而令人意想不到的是，微软数据开发组的Damien Guard却突然给出了一个.net 4.0中Linq to SQL的更新列表，涉及到性能、可用性、稳定性、SqlMetal(从数据库生成dbml文件的工具，也可以生成代码)、类设计器和代码生成等方面的问题。