在GridView裡合併儲存格

GridView裡合併相同內容的儲存格，是常有的需求。

以下的範例可選擇多個需要合併的欄位：(在PreRender事件裡處理)

protected void gv_PreRender(object sender, EventArgs e)

{

int[] mergeColumns = { 0 }; //要合併的欄位-編號(款)

foreach (int mergeColumn in mergeColumns)

{

int i = 1;

// 取得來源GridView

GridView gv = (GridView)sender;

if (gv!= null)

{

foreach (GridViewRow row in gv.Rows)

{

row.Cells[mergeColumn].RowSpan = 0;

if (row.RowIndex != 0)

{

if (row.Cells[mergeColumn].Text.Trim() == gv.Rows[(row.RowIndex - i)].Cells[mergeColumn].Text.Trim())

{

gv.Rows[(row.RowIndex - i)].Cells[mergeColumn].RowSpan += 1;

row.Cells[mergeColumn].Visible = false;

i += 1;

}

else

{

row.Cells[mergeColumn].RowSpan += 1;

i = 1;

}

}

else

{

row.Cells[mergeColumn].RowSpan = 1;

}

}

}

}

}

由xml到table

SQL 2005提供xml的支援，其中包括xml的data type，使用範例如下:

DECLARE @xml xml
SET @xml = N'<polist>
<po><ponumber>100</ponumber><podate>2008-09-10</podate></po>
<po><ponumber>101</ponumber><podate>2008-09-11</podate></po>
</polist>'
SELECT
doc.col.value('ponumber[1]', 'nvarchar(10)') ponumber
,doc.col.value('podate[1]', 'datetime') podate
,doc.col.value('poxml[1]', 'nvarchar(4000)') poxml
FROM @xml.nodes('/polist/po') doc(col)

只要再加入insert語法，就可以從xml參數理讀取資料，存到資料表了。

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

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.

.NET Regular Expressions

Regular expression, in short, is a way to describe and identify patterns within text. It could be used in text search, pattern matching, text replacement, ...etc. We could imagine regular expression as the partial metadata of text.

Regular expression comes with a set of symbols and rules in order to describe text patterns. For example, [0-9] is used to describe any digit, while [^0-9] is used to describe any non-numeric character. Here are some rules we can use in regular expressions. (Please note that these are just grammars for regular expressions. We will see the actual applications latter.)

Pattern	Description
Escaping
\	Escapes special characters to literal and literal characters to special. E.g: /\(s\)/ matches '(s)' while /(\s)/ matches any whitespace and captures the match.
Quantifiers
{n}, {n,}, {n,m}, *, +, ?	Quantifiers match the preceding subpattern a certain number of characters. The subpattern can be a single character, an escape sequence, a pattern enclosed by parentheses or a character set. {n} matches exactly n times. {n,} matches n or more times. {n,m} matches n to m times. * is short for {0,}. Matches zero or more times. + is short for {1,}. Matches one or more times. ? is short for {0,1}. Matches zero or one time. E.g: /o{1,3}/ matches 'oo' in "tooth" and 'o' in "nose".
Pattern delimiters
(pattern), (?:pattern)	Matches entire contained pattern. (pattern) captures match. (?:pattern) doesn't capture match E.g: /(d).\1/ matches and captures 'dad' in "abcdadef" while /(?:.d){2}/ matches but doesn't capture 'cdad'. Note: (?:pattern) is very badly supported in older browsers.
Lookaheads
(?=pattern), (?!pattern)	A lookahead matches only if the preceeding subexpression is followed by the pattern, but the pattern is not part of the match. The subexpression is the part of the regular expression which will be matched. (?=pattern) matches only if there is a following pattern in input. (?!pattern) matches only if there is not a following pattern in input. E.g: /Win(?=98)/ matches 'Win' only if 'Win' is followed by '98'. Note: Support for lookaheads is lacking in most but the newest browsers.
Alternation
	Alternation matches content on either side of the alternation character. E.g: /(ab)a/ matches 'aa' in "dseaas" and 'ba' in "acbab".
Character sets
[characters], [^characters]	Matches any of the contained characters. A range of characters may be defined by using a hyphen. [characters] matches any of the contained characters. [^characters] negates the character set and matches all but the contained characters E.g: /[abcd]/ matches any of the characters 'a', 'b', 'c', 'd' and may be abbreviated to /[a-d]/. Ranges must be in ascending order, otherwise they will throw an error. (E.g: /[d-a]/ will throw an error.) /[^0-9]/ matches all characters but digits. Note: Most special characters are automatically escaped to their literal meaning in character sets.
Special characters
^, $, ., ? and all the highlighted characters above in the table.	Special characters mean characters that match something else than what they appear as. ^ matches beginning of input (or new line with m flag). $ matches end of input (or end of line with m flag). . matches any character except a newline. ? directly following a quantifier makes the quantifier non-greedy (makes it match minimum instead of maximum of the interval defined). E.g: /(.)*?/ matches nothing or '' in all strings. Note: Non-greedy matches are not supported in older browsers such as Netscape Navigator 4 or Microsoft Internet Explorer 5.0.
Literal characters
All characters except those with special meaning.	Mapped directly to the corresponding character. E.g: /a/ matches 'a' in "Any ancestor".
Backreferences
\n	Backreferences are references to the same thing as a previously captured match. n is a positive nonzero integer telling the browser which captured match to reference to. /(\S)\1(\1)+/g matches all occurrences of three equal non-whitespace characters following each other. /<(\S+).*>(.*)<\/\1>/ matches any tag. E.g: /<(\S+).*>(.*)<\/\1>/ matches '<div id="me">text</div>' in "text<div id=\"me\">text</div>text".
Character Escapes
\f, \r, \n, \t, \v, \0, [\b], \s, \S, \w, \W, \d, \D, \b, \B, \cX, \xhh, \uhhhh	\f matches form-feed. \r matches carrriage return. \n matches linefeed. \t matches horizontal tab. \v matches vertical tab. \0 matches NUL character. [\b] matches backspace. \s matches whitespace (short for [\f\n\r\t\v\u00A0\u2028\u2029]). \S matches anything but a whitespace (short for [^\f\n\r\t\v\u00A0\u2028\u2029]). \w matches any alphanumerical character (word characters) including underscore (short for [a-zA-Z0-9_]). \W matches any non-word characters (short for [^a-zA-Z0-9_]). \d matches any digit (short for [0-9]). \D matches any non-digit (short for [^0-9]). \b matches a word boundary (the position between a word and a space). \B matches a non-word boundary (short for [^\b]). \cX matches a control character. E.g: \cm matches control-M. \xhh matches the character with two characters of hexadecimal code hh. \uhhhh matches the Unicode character with four characters of hexadecimal code hhhh.

Regular expressions can be used with javascript, or in our case, .NET. Since regular expression does nothing more than describing certain patterns, it has to be used with available functions in order to be put to practices. Such functions can perform tasks on strings, replacing a pattern with another, or detect certain patterns to ensure correct input format...etc. See below for a list of available functions.

Description	Example
RegExp.exec(string)
Applies the RegExp to the given string, and returns the match information.	var match = /s(amp)le/i.exec("Sample text") match then contains ["Sample","amp"]
RegExp.test(string)
Tests if the given string matches the Regexp, and returns true if matching, false if not.	var match = /sample/.test("Sample text") match then contains false
String.match(pattern)
Matches given string with the RegExp. With g flag returns an array containing the matches, without g flag returns just the first match or if no match is found returns null.	var str = "Watch out for the rock!".match(/r?or?/g) str then contains ["o","or","ro"]
String.search(pattern)
Matches RegExp with string and returns the index of the beginning of the match if found, -1 if not.	var ndx = "Watch out for the rock!".search(/for/) ndx then contains 10
String.replace(pattern,string)
Replaces matches with the given string, and returns the edited string.	var str = "Liorean said: My name is Liorean!".replace(/Liorean/g,'Big Fat Dork') str then contains "Big Fat Dork said: My name is Big Fat Dork!"
String.split(pattern)
Cuts a string into an array, making cuts at matches.	var str = "I am confused".split(/\s/g) str then contains ["I","am","confused"]

Here is an actual example using regular expression. In this example, we try to retrieve all 6-digit employee #'s inside parenthesis, exclude parenthesis and saperate employee #'s by comma. Therefore we try to match the string with a a pattern containing anything but series of 6-digit numbers inside parenthesis and commas, and delete everything else except for those matching the pattern.

A sample input will be like: 三趨一(526274),三趨二(526275),三趨三(526279)

The regular expression is: ([^0-9,])*(?=[0-9]{6})\)

We first specify a 6-digit number by [0-9]{6}; [0-9], as we say before, matches a digit, while {6} means matching of the pattern 6 times. (?=[0-9]{6}) indicates a pattern matches only when it's followed by a 6-digit number. ([^0-9,])* represents any number of characters excluding numeric digits and commas. These two combined, it means any number of characters excluding numeric digits and commas followed by a 6-digit number. \) matches a single right parenthesis, and means the "or" operation. Hence the whole regular expression means any number of characters excluding numeric digits and commas followed by a 6-digit number, or a single right parenthesis.

The idea to delete everything matching the pattern, which will leave us only 6-digit numbers and commas, is to replace the pattern with empty strings.

The complete code looks like:

Regex.Replace("三趨一(526274),三趨二(526275),三趨三(526279)", @"([^0-9,])*(?=[0-9]{6})\)", "");

For those who are interested in regular expression with javascript, check out the following websites.

http://www.evolt.org/article/Regular_Expressions_in_JavaScript/17/36435/

http://www.sitepoint.com/print/expressions-javascript

SQL Server 2005 Full-Text Search

SQL Server 2005 comes with its own full-text search capabilities, which enables developers to use full-text search functions and predicates, provided that the correct full-text search catalog and indexing exist.

Full-text functions and predicates include:

1. CONTAINS (predicate)
2. FREETEXT (predicate)
3. CONTAINSTABLE (function)
4. FREETEXTTABLE (function)

Here are some of their characteristics and differences:

CONTAINS and FREETEXT both return a TRUE or FALSE value and are specified in the WHERE or HAVING clauses of a SELECT statement.

CONTAINSTABLE and FREETEXTTABLE both return a table of zero, one, or more rows, so they must always be specified in the FROM clause.

CONTAINS and FREETEXT can only be used to specify selection criteria, which Microsoft SQL Server uses to determine the membership of the result set, ie, inside or outside the result set.

CONTAINSTABLE and FREETEXTTABLE are also used to specify selection criteria. The table returned has a column named KEY that contains full-text key values. Each full-text registered table has a column whose values are guaranteed to be unique. The values returned in the KEY column of CONTAINSTABLE or FREETEXTTABLE are the unique values, from the full-text registered table, of the rows that match the selection criteria specified in the full-text search condition.

Here are some example full-text queries(extracted from MSDN):

1. SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');


2. DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'performance'
SELECT Description
FROM Production.ProductDescription
WHERE FREETEXT(Description, @SearchWord);

3. SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Categories AS FT_TBL
INNER JOIN
FREETEXTTABLE(Categories, Description,
'How can I make my own beers and ales?') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;

4. SELECT K.RANK, CompanyName, ContactName, Address
FROM Customers AS C
INNER JOIN
CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*",
Rue WEIGHT(0.5),
Bouchers WEIGHT(0.9))', 3) AS K
ON C.CustomerID = K.[KEY];


5. SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
, 10
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY];

Note that full-text search is different from LIKE predicate. With the LIKE predicate, you will get exact matches, whereas in full-text search, you can specify certain ambiguity.

Also, for some far eastern characters, such as Chinese, Japanese...etc., SQL Server has some language-specific features to generate more desirable results.

Reference: http://msdn2.microsoft.com/en-us/library/ms142519.aspx

.NET GridView EmptyDataTemplate

在.NET 2.0裡面,GridView的EmptyDataTemplate是用來指定GridView內容為0比資料時的顯示結果,當葉面執行以後,GridView會被render為以下形式:

<div>
<table cellspacing="2" border="0" id="ctl00_ContentPlaceHolder1_gv_Authpat">
......
</table>
</div>

而EmptyDataTemplate則會被render為以下形式:

<tr class="Theader" align="center">
<td colspan="8">

~ EmptyDataTemplate的內容 ~

</td>
</tr>

在EmptyDataTemplate裡面,我們除了可以使用一般的server control之外,也可以加入一般的html語法,因為在EmptyDataTemplate裡面的html語法並不需要通過editor的驗證,所以即使是不合乎定義的html語法,也可以被加進來,例如:

專利件編號</td>
<td style="width:8%">單位</td>
<td style="width:10%">申請國別</td>
<td style="width:25%">專利名稱</td>
<td style="width:10%">成果來源</td>
<td style="width:20%">獲證日及証號</td>
<td style="width:10%">申請狀態

綜合以上的例子,所render出來的html結果如下:

<div>
<table cellspacing="2" border="0" id="ctl00_ContentPlaceHolder1_gv_Authpat">
<tr class="Theader" align="center">
<td colspan="8">
專利件編號</td>
<td style="width:8%">單位</td>
<td style="width:10%">申請國別</td>
<td style="width:25%">專利名稱</td>
<td style="width:10%">成果來源</td>
<td style="width:20%">獲證日及証號</td>
<td style="width:10%">申請狀態
</td>
</tr>
</table>
</div>

.NET 識別

ASP.NET Web 應用程式可以使用以下的識別來存取資源：

1. ASP.NET 處理序識別 (如同 web.config 中的 項目上所設定)。預設值是以使用者名稱「machine」和密碼「AutoGenerate」指定的本機 ASPNET 帳戶。

<identity impersonate="false" />

2. 通過驗證的呼叫者的識別(如果已在 web.config 內啟用模擬)。這個設定會模擬原始呼叫者，由 IIS 進行驗證，如果您未停用 IIS 的匿名存取，這將會是 IUSR_MACHINE。設定方法是在web.config檔裡面指定：

(<authentication mode="Windows" />)
<identity impersonate="true" />

3. 指定的模擬識別 (雖然不建議這樣做) 如下所示。

<identity impersonate="true" username="Bob" password="password">

.NET 匯入Excel

1. 使用OleDbDataAdapter + GridView

string OleConStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", Server.MapPath(xlsFullName));

OleDbConnection Conn = new OleDbConnection(OleConStr);

Conn.Open();
DataSet Ds = new DataSet();
OleDbDataAdapter Da = new OleDbDataAdapter("Select * from [Sheet1$]", Conn);
Da.Fill(Ds, "excel_data");
Conn.Close();

gv_test.DataSource = Ds.Tables["excel_data"];
gv_test.DataBind();

OleCn.Close();

2. 使用OleDbDataReader (read row by row)

string OleConStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", Server.MapPath(xlsFullName));

OleDbConnection OleCn = new OleDbConnection(OleConStr);
OleDbCommand OleCmd = new OleDbCommand();
OleCmd.Connection = OleCn;
string strSQL = @"SELECT * FROM [Sheet1$]";
OleCmd.CommandText = strSQL;

try
{
OleCn.Open();
OleDbDataReader OleDr = OleCmd.ExecuteReader();
while (OleDr.Read())
{

~~~ CUSTOM CODE ~~~

{
catch (Exception ex)
{
String scriptString = string.Format(@"alert('Excel格式錯誤! FilePath:{0} ; Advance MSG:{1}');", xlsFullName, ex.Message);
ClientScript.RegisterStartupScript(this.GetType(), "s", scriptString, true);
return;
}

OleCn.Close();


註:read Excel檔案時要注意是否有權限,包括檔案的權限,資料夾的權限,以及web.config檔裡面的屬性設定成 false

<identity impersonate="false"/>

.NET 2.0 RadioButtonList access on the client side using Javascript

The asp:RadioButtonList control will be rendered as a series of input HTML controls wrapped in a span on the client side. To access each element of the RadioButtonList on the client side, use the following codes, whearas id as the ClientID of the RadioButtonList:

var obj = document.getElementById(id);
var radios = obj.getElementsByTagName('input');

Afterwards, radios can be used as an array of radio inputs, which you can access as in normal conditions.

.NET 2.0 TextBox ReadOnly attribute

在.NET 2.0裡面,asp:TextBox的ReadOnly屬性如果被設成true的話(例如: <asp:textbox id="tbx_test" runat="server" />,或是使用tbx_test.ReadOnly = true;),會對前端的javascript造成一點影響,譬如我們常常在前端使用javascrip將textbox的值改變,但是當程式postback到後方以後,會發現textbox所保有的值仍然是舊的值,要想改正這個行為,有幾種方法:

1. 在後端以Request["tbx_test"],或是Request.Form["tbx_test"]來取得改正過後的值,然後用tbx_test.Text = Request["tbx_test"].ToString();在後重新設定textbox的值,

2. 不使用ReadOnly="true"或是tbx_test.ReadOnly = true;,而是在後端使用
tbx_test.Attributes.Add("ReadOnly", "ReadOnly");,或是
tbx_test.Attributes["ReadOnly"] = "ReadOnly";


The Text value of a TextBox control with the ReadOnly property set to true is sent to the server when a postback occurs, but the server does no processing for a read-only text box. This prevents a malicious user from changing a Text value that is read-only. The value of the Text property is preserved in the view state between postbacks unless modified by server-side code. (Quoted from MSDN)

reference: http://darkthread.blogspot.com/2006/11/kb-aspnet-20-textboxreadonly.html

.NET 2.0 asp:TextBox AutoPostBack

之前在使用asp:TextBox時,有時會基於某些原因將AutoPostBack設成true,當這樣做時,發現在本機上執行是沒有問題的(其實一開始本機上執行也不行,但是當我註冊WebForms.js這個resource到Page上以後,本機上就可以了,所以讓我以為問題已經解決,後來發現在別的電腦上還是不行...),但是當我發佈到server上,在別的電腦執行時,每按一下鍵盤,就會一直出現一個javascript錯誤:

此處需要物件(Object expected)

根據原始碼以及錯誤訊息指出的line number,發現是一段code的嫌疑最大,並且懷疑WebForm_TextBoxKeyHandler是此處所缺少的object:

<asp:TextBox.......onchange="javascript:setTimeout('__doPostBack(\'ctl00$ContentPlaceHolder1$tbx_contname\',\'\')', 0)" onkeypress="if (WebForm_TextBoxKeyHandler(event) == false) return false;"......

問題成因: 有聽過討論區裡有人說過是IE7.0的問題,但是我懷疑這個可能性,因為我們用的IE版本是6.0的,也有聽過是因為WebResource沒有被正確download到client端,這個我比較相信一點,我唯一確定的是,這段code是因為使用TextBox的AutoPostBack="true"屬性所造成的

如上面所述,本以為RegisterClientScriptResource可以解決問題(因為WebForm_TextBoxKeyHandler定義於其中),但是沒有,又找不到如何使.NET不要自動加這段程式碼,所以最後決定將這段code的生成原因:AutoPostBack拿掉,拿掉AutpPostBack之後,果然問題便解決了

相當奇怪的一個bug

之前在測試計畫管理系統時,發現了一個很奇怪的bug,就是在使用者於TextBox裡面輸入某些字串,並試圖以這些字串為參數帶入popup視窗時,出現了403:Access Forbidden error,error message如下:

Due to the presence of characters known to be used in Cross Site Scripting attacks, access is forbidden. This web site does not allow URls which might include embedded HTML tags.

為什麼說這個error很奇怪呢?因為當時所測試的字串,為中文字,並有經過escape()的encoding,(就我所知,一般escape是將自元轉換成%xx格式,xx為hex數字,照道理講並沒有內含任何HTML tags才對),經過反覆嘗試,確定有內含"大"字的字串,都會出現同樣錯誤,我一直不知這個錯誤的詳細原因為何(雖然是有懷疑是因為encoding方式的問題)

所以後來將escape置換為encodeURI後,error倒是沒有再出現了,(我只能說,真是神出鬼沒ㄚ...)