10/1下午茶

For XML Path

SQL Server 中利用這個語法可以將查詢的結果轉換成XML data,常用的語法如下

SELECT ns_signname
FROM signorg
WHERE signorg.seqsn= 111
FOR XML PATH('')

這個語法會將所有查詢結果組成一個字串,效果就像之前的string concatenation

詳細說明可以參考http://it.dianping.com/sql-for-xml-path.htm

Schema Comparison

最近接了一個需要維護的系統,接到的時候傻眼,測試區與上線區的schema是不一樣的,而且沒有文件註明哪裏有新增或是修改過,沒關係,天助自助者,寫個script來看看吧!

首先查詢看看測試區的table名稱

select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

將結果暫存後,再根據每個table名稱(暫存入參數@table),查詢是否有column的設定不一致的

SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS as col
WHERE TABLE_NAME = @table and (
col.COLUMN_NAME not in (
select COLUMN_NAME
from 上線區資料庫.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table) or
col.DATA_TYPE <> (
select DATA_TYPE
from 上線區資料庫.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table and COLUMN_NAME = col.COLUMN_NAME) or
col.CHARACTER_MAXIMUM_LENGTH <> (
select CHARACTER_MAXIMUM_LENGTH
from 上線區資料庫.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table and COLUMN_NAME = col.COLUMN_NAME) or
col.COLUMN_DEFAULT <> (
select COLUMN_DEFAULT
from 上線區資料庫.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table and COLUMN_NAME = col.COLUMN_NAME) or
col.IS_NULLABLE <> (
select IS_NULLABLE
from 上線區資料庫.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table and COLUMN_NAME = col.COLUMN_NAME))

嗯,果然跳出一堆...

MCSE chapter 5 sec 1

SqlDataSource Output Parameter

如果在SqlDataSource裡面使用SelectCommand,InsertCommand,或是UpdateCommand,呼叫stored procedure,我們可以再OnSelected,OnInserted,OnUpdated事件中用output parameter來擷取stored procedure的回傳值,但是如果使用DeleteCommand並且試圖在OnDeleted事件中擷取回傳的output parameter時, 我們會發現,output parameter的值並沒有被改變,換句話說,只有output parameter在與DeleteCommand合用時,沒有辦法發生作用.例如:在以下的範例中,@rtn_code的質便不會被傳回.

protected void ds_activity_base_Deleted(object sender, SqlDataSourceStatusEventArgs e)
{
System.Data.Common.DbCommand cmd = e.Command;

if (cmd.Parameters["@rtn_code"].Value != null && cmd.Parameters["@rtn_code"].Value.ToString() == "0")
ClientScript.RegisterStartupScript(this.GetType(), "error", "alert('國際活動已成功刪除!');location.href='activity_CaseEntry.aspx';", true);
}


註:造成這種情形的原因,我並不清楚,同樣的問題,之前似乎有report回Microsoft過(見http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=105193)但是看情形並未受到重視,也不清楚後來是如何解決的...

雖然無法解釋為何只有DeleteCommand會出現這種情形,但是的確有個方法可以work around這個問題,便是在OnDeleting事件中,將此output parameter的方向再次設定為ParameterDirection.Output,如下:

protected void ds_activity_base_Deleting(object sender, SqlDataSourceCommandEventArgs e)
{
System.Data.Common.DbCommand cmd = e.Command;

if (cmd.Parameters["@rtn_code"] != null)
cmd.Parameters["@rtn_code"].Direction = ParameterDirection.Output;
}

Embedded HTML contents in GridView

GridView comes with a new feature that's not present in DataGrid. This new feature, namely, HtmlEncode, is used to prevent cross-site scripting and is applied to the BoudField element. The default setting for this property is set to be on(HtmlEncode="true"), and what it does is to html-encode the contents of gridviews so they are treated like plain text and therefore any malicious code won't get executed.

Hence if we try to output html contents to some of the gridview columns, we have to do either of the followings:

1. in the RowDataBound event handler, use Server.HTMLDecode to decode all the contents

2. set the HTMLEncode property of the BoundField to false for those columns

As a side note, if we use HTMLEncode="false", then all the contents will be treated as html, and some of the original format might be lost. For example, the new line character (char(13) + char(10)) might be converted into a blank space. In this event, it has to be replaced by '<br>' for the effects to show.

SQL Server row formatting using PIVOT, UNPIVOT

Readers are advised to know that the technique described in a previous artivle: SQL Server string column concatenation is also used in here.

Suppose we a table t1 as follows:

ID	Property1	Property2	Property3	Property4
17	v1,v3	v2	v3,v5	v2,v4,v6

Another table t2 as follows:

Value	Description
v1	eye
v2	mouth
v3	ear
v4	nose
v5	neck
v6	hair

And we would like our output to be like this instead:

ID	Property1	Property2	Property3	Property4
17	eye,ear	mouth	ear,neck	mouth,nose,hair

That is, the list of values from t1 has to be translated into more readable contents according to the correspondences in t2. Here are the steps:

1. UNPIVOT t1, converting rows into columns
2. insert converted data, combining with the corresponding values from t2, into temporary table #tmp
3. perform string concatenation
4. PIVOT the resulting table back to it original form

Here are the SQL commands for step 1:

select property, value
from
(
select * from t1 where ID = '17'
) AS p
UNPIVOT
(
value
for property
in (Property1, Property2, Property3, Property4)
) AS unpvt

resulting table:

property	value
Property1	v1,v3
Property2	v2
Property3	v3,v5
Property4	v2,v4,v6

Here are the complete SQL commands for step 1 and 2:

create table #tmp(tmp_property varchar(10), tmp_description varchar(10), tmp_list varchar(100))

insert into #tmp(tmp_property, tmp_description, tmp_list)
select property, Description, NULL
from
(
select property, value
from
(
select * from t1 where ID = '17'
) AS p
UNPIVOT
(
value
for property
in (Property1, Property2, Property3, Property4)
) AS unpvt
) AS tmp
inner join t2 on value like '%' + t2.Description + '%'

resulting table:

tmp_property	tmp_description	tmp_list
Property1	eye	NULL
Property1	ear	NULL
Property2	mouth	NULL
Property3	ear	NULL
Property3	neck	NULL
Property4	mouth	NULL
Property4	nose	NULL
Property4	hair	NULL

step 3:

update #tmp
set @list = tmp_list = (CASE WHEN @last <> tmp_property THEN tmp_description ELSE @list + ',' + tmp_description END), @last = tmp_property

resulting table:

tmp_property	tmp_description	tmp_list
Property1	eye	eye
Property1	ear	eye,ear
Property2	mouth	mouth
Property3	ear	ear
Property3	neck	ear,neck
Property4	mouth	mouth
Property4	nose	mouth,nose
Property4	hair	mouth,nose,hair

step 4:

select *
from
(
select '17' as ID, tmp_property, max(tmp_list) as tmp_list from #tmp_default group by tmp_property
) as p
PIVOT
(
max(tmp_list)
for tmp_property
in (Property1, Property2, Property3, Property4)
) as pvt

resulting table:

ID	Property1	Property2	Property3	Property4
17	eye,ear	mouth	ear,neck	mouth,nose,hair