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

SQL Server string column concatenation

In SQL command, it is easy to perform the SUM aggregation operation on a column containing numeric data. However, it is not so easy to perform a similar operation, namely, string concatenation, on a column containing strings or text.

Here we describe a way to do string concatenation on a data column.

Say we have a table like the following:

table name: t1

ID	Value
17	v1
17	v2
17	v3
28	v4
28	v5

Sometimes we may want our output be like this instead:

ID	Value
17	v1,v2,v3
28	v4,v5

We can use string concatenation technique to achieve the effect.

First we create a temporary storage table called #tmp, with columns tmp_ID, tmp_value, tmp_list. Then we insert data from t1 into #tmp, with the tmp_list column set to NULL, ordered by ID column. The order by criteria is crutial here, for all the data has to be in order for this algorithm to work. Usually we pick the column that serves as the identity column to be in the order by list. And the reason will be elaborated latter.

create table #tmp(tmp_ID varchar(2), tmp_value varchar(10), tmp_list varchar(100))

insert into #tmp select ID, Value, NULL from t1 order by ID

Now our table looks like the following:

table name: #tmp

tmp_ID	tmp_value	tmp_list
17	v1	NULL
17	v2	NULL
17	v3	NULL
28	v4	NULL
28	v5	NULL

Then we do the following:

declare @list varchar(100)
declare @last varchar(10)
select @list = ''
select @last = ''

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

In doing this, we set the values in tmp_list column to be the strings concatenated by all the string in tmp_value column in all the preceding rows. Note that whenever the tmp_ID column changes it value, we start the concatenation process all over again. Hence our table looks like the following now:

tmp_ID	tmp_value	tmp_list
17	v1	v1
17	v2	v1,v2
17	v3	v1,v2,v3
28	v4	v4
28	v5	v4,v5

Now all we have to do is to select the values:

select tmp_ID, max(tmp_list) as tmp_list from #tmp group by tmp_ID

And we'll get the following result:

tmp_ID	tmp_list
17	v1,v2,v3
28	v4,v5

As a side note, there is another way to do the string concatenation on a column.

declare @list varchar(100)
declare @list2 varchar(100)
select @list = ''
select @list2 = ''

select @list = @list+ Value + ',' from t1 where ID = '17'
select @list2 = @list2+ Value + ',' from t1 where ID = '28'

This will create the same results, too. But there is an obvious drawback. Imagine if you have many different ID values in t1, you have to hardcode it many times or put it in a loop, which will make the solution less concise and less desirable. Performance wise, these two are pretty much the same. Space wise, the first solution definitely requires more space.

.NET master pages

Master pages, as we know, are meant to shared amongst more than one content pages. Imagine the following scenario when we need different pages to edit or insert contents to the same database table, after the "Save" button is clicked on each page. Under the circumstances, it looks natural to put the "Save" button in the master page, because the button is a common element for the two pages.

But here comes the question, how do we make sure that different actions are performed after the button is clicked on different pages? That is, how do we make sure the data is inserted when the button is clicked on the insert page, and the data is edited when the same button is clicked on the edit page?

The trick is to put the declaration of the button in the master page, specifying all properties except for the click events. For example:

<asp:Button ID="btnSave" runat="server" CssClass="button" onmouseout="this.className='btn_mouseout'" onmouseover="this.className='btn_mouseover'" Text="存檔" />

Inside each content page, we specify the click event for the button.

protected void Page_Init(object sender, EventArgs e)
{
Control ctl = null;
ctl = Master.FindControl("btnSave");
if (ctl != null)
{
Button btnSave = (Button)ctl;
btnSave.Command += new CommandEventHandler(btnSave_Command);
}
}

And of course, the function performing the corresponding task is defined in each content page as well.