C#,js和sql实用技巧选2

1.为什么"foo".Equals()不是好做法？因为当正确的写法是"foo".Equals(obj.value)，却写成了"foo".Equals(obj)，不会出现错误提示，从而成为一个隐蔽的bug

2.从stacktrace里提取行号:

var st = new System.Diagnostics.StackTrace(e.Error, true);
var lineNo = st.GetFrame(0).GetFileLineNumber();
MessageBox.Show(this, e.Error.TargetSite.ReflectedType.FullName + Environment.NewLine + e.TargetSite.Name

 

3.ie 6.0以后的版本，http://username:password@abc.com 这样的写法不允许了。

变通办法：

var request = new ActiveXObject('msxml2.xmlhttp');
var url = 'myurl';
request.open('GET', url, false, username, password);
window.top.location.href= url;

 

4.选择符合5个条件中的两个的记录

select * from T3
where 
(
case when lid = @lid then 1 else 0 end +
case when lname = @lname then 1 else 0 end +
case when lpass = @lpass then 1 else 0 end +
case when ltouxiang = @ltouxiang then 1 else 0 end + 
case when lxingbie = @lxingbie then 1 else 0 end

) = 3

 

5.sql server:只拷贝表结构，不拷贝数据

select * into tbl2 from tbl where 1 = 0

值得一提的是select into这种写法并不提倡，有可能会搞乱tempdb，另外，有时会有怪异的行为。

6.去掉最大和最小值后再算平均值：

select avg(sal)
from emp 
where sal not in (
(select min(sal) from emp),
(select max(sal) from emp)
)

 

7.sql server的一个问题:

declare @foo int
set @foo = null
select case when @foo is null then 'bar' else <某个日期类型的字段> end as abc

会报错：

Conversion failed when converting date and/or time from character string.

但是，如果@foo 不是null，就不会报错。

8.cte感觉性能很差，宁可用物理表。而且很多情况下，物理表的性能比临时表(#temp这样的)好。在一个存储过程中，把临时表改成物理表，原来要两小时，现在只要10分钟。另外一个应用里，开始用cte实现分页，后来改成用物理表，性能提高了两倍。

9.匿名函数的一个问题：

Action foo = (bar) => {
bar = new Bar();
}

Bar bar2 = null;
foo(bar2);

bar2 还是null，下面这样写才不是null:

bar = new Bar();
bar2 = bar;

10. 找某表所有相关的外键

select t.name as TableWithForeignKey, fk.constraint_column_id as FK_PartNo , c.name as ForeignKeyColumn 
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where fk.referenced_object_id = (select object_id from sys.tables where name = 'Complaint')
order by TableWithForeignKey, FK_PartNo

找外键列名：

select distinct name from sys.objects where object_id in 
( select fk.constraint_object_id from sys.foreign_key_columns as fk
where fk.referenced_object_id = 
(select object_id from sys.tables where name = 'Complaint')
)