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倒是沒有再出現了,(我只能說,真是神出鬼沒ㄚ...)

escape, encodeURI, encodeURIComponent的比較

這三個function都是javascrip中提供為encoding用的,只是三個略有不同,以下為三者的比較:

escape: 回傳一個Unicode格式的字串,所有空格,符號,以及其他non-ASCII的字元都會以%xx的格式來取代,其中xx表示與字元相對應的十六進位(base-16)數字,例如:空格space即可表示為"%20"

注意: escape並不會encode含有"+"的字串,同時也無法正確處理含有non-ASCII的字元,也因此,建議盡量避免使用escape

encodeURI: 可將一個字串encode為URI,但是無法encode某些特定字串,例如: ":", "/", ";", 和 "?"

注意: encodeURI較escape來說,是比較恰當的,因為encodeURI是將字串encode成URI,而不僅只是QueryString,要特別注意的是encodeURI並不encode "'",因這個字元在URI裡是有效的

encodeURIComponent: 與encodeURI相同,同樣會傳回一個encoded的URI,除此之外,並可以encode所有字串,包括encodeURI無法encode的字元,但是要注意的是,如果字串中包括路徑,那麼路徑中的"/"也會被encoded,因此當我們將這個路徑當作request傳給web server時,便是無效的了

注意: encodeURIComponent是比較適合大多數情況下使用的,但同樣的,encodeURIComponent也並不encode "'"

reference: http://xkr.us/articles/javascript/encode-compare/

.NET client side events and server side events

As we know, we can have both the client-side event handler and server-side event handler for the same server control with the same event. That is, if a user clicks on a server control(of course it would be translated into an input html control), we can have a javascript function handling the the click event on the client side, and then do a postback to the server side for further processing.

Things are a little bit trickier for textboxes. Because for buttons, we have a rather clear correspondence between client-side event and server-side event, which are OnClientClick and OnClick, accordingly. When it comes to textboxes, we have to use onchange(for client side) and OnTextChanged(for server side), and the client-side handler has to be added dynamically, which are neither convenient nor intuitive. However, this is not the only complication we are facing here.

1. First, for the client-side button click event, we can use the following code to ensure the postback will or will not be executed. For example:

btn_Find_Planer.OnClientClick = String.Format(@"return Find_Empno('tbx_PlanerEmpno', '{0}', '{1}', '{2}', false);", tbx_PlanerName.ClientID, tbx_PlanerExt.ClientID, tbx_PlanerDept.ClientID);

Inside Find_Empno function we can decide whether to perform the postback or not, and if we do choose to postback, we know for sure the event will be correctly handled in its server side handler, btn_Find_Planer_Click, right after the client side handler finishes it job.

When it comes to textboxes, I have to do it in a similar, but different way. For example:

tbx_PlanerName.Attributes["onchange"] = String.Format(@"Find_Empno('tbx_PlanerEmpno', '{0}', '{1}', '{2}', true);", tbx_PlanerName.ClientID, tbx_PlanerExt.ClientID, tbx_PlanerDept.ClientID);

Note that there is no return keyword. For some reason, if I add the return statement, the postback won't happen at all. If I get rid of the return keyword, then the postback will be directed to its server side handler, tbx_PlanerName_TextChanged, as I desire. (I haven't quite figured out the reason behind it yet...)

2. Of course, there is another catch. I would have to set the AutoPostBack property to true for the textbox if I want to post back to server side right away, which, by the way, will lead to another error. The browser would complain that an object is required here. I thought it might be caused by the code .NET appends automatically:

setTimeout('__doPostBack(\'ctl00$ContentPlaceHolder1$tbx_PlanerName\',\'\')', 0)" onkeypress="if (WebForm_TextBoxKeyHandler(event) == false) return false;"

The way I solve the problem is to add the reference to the resource WebForms.js as follows:

this.Page.ClientScript.RegisterClientScriptResource(typeof(System.Web.UI.Page), "WebForms.js");

Anyway, there are so many details that are driving me crazy here...

reference: http://www.cnblogs.com/Truly/archive/2007/07/07/809576.html

.NET RegisterForEventValidation and GetPostBackEventReference

It's convenient to use GetPostBackEventReference to force a postback from client side to a server control. However, in .NET 2.0, there is a catch; that is, the event will be validated for security reasons before the postback occurs. Therefore, you will likely get an error if you simply use the following code to force a postback to btn_Hidden1:

ClientScript.RegisterStartupScript(this.GetType(), "message",
ClientScript.GetPostBackEventReference(btn_Hidden1, ""), true);

One solution is to use RegisterForEventValidation. Then again, this method can only be called during the phase of page rendering. So we have to do the following:

protected override void Render(HtmlTextWriter writer)
{
ClientScript.RegisterForEventValidation(new PostBackOptions(btn_Hidden1));
base.Render(writer);
}

Another seemingly easier solution is to set the EnableEventValidation property for the page to false. (This can be done in the @Page directive) Also, you can do it in the application level by setting up the web.config accordingly. For example:

In the page level:

<%@ Page Language="C#" MasterPageFile="Plan.master" AutoEventWireup="true" EnableEventValidation="false" CodeFile="plan_CaseMain.aspx.cs" Inherits="Plan_CaseMain" Title="Untitled Page" MaintainScrollPositionOnPostback="true" %>

In the application level:

<pages validateRequest="false" />

Of course, the second and the third approaches are less preferable, cause they might create security holes in your page or application. The first one is more complicated, but more legid.

使用SqlDataSource呼叫stored procedure並取回Output

當我們用SqlDataSource呼叫stored procedure,如果stored procedure有回傳一個或多個Output值的時候,這些Output也被當成一個Parameter,傳遞給這個stored procedure,例如:

ds_Stop.UpdateParameters.Add("msg", TypeCode.String, "");
ds_Stop.UpdateParameters["msg"].Size = 100;
ds_Stop.UpdateParameters["msg"].ConvertEmptyStringToNull = false;
ds_Stop.UpdateParameters["msg"].Direction = ParameterDirection.Output;

特別要注意的是,假如回傳的是一個字串的話,要設定這個Parameter的Size屬性,不然傳回的值會被truncated.

使用div標籤製作浮動視窗效果

原理並不難,主要是利用div標籤的absolute position來設定浮動視窗的位置,並用onmouseover,onmouseout來控制顯現/隱藏浮動視窗

之前測試時視窗沒有浮動的效果,而是出現在頁面內容的最上方,導致其他內容被往下擠壓,

後來在div標籤上加上style="position:absolute"的設定,便可正常運作

.NET DropDownList

當想要動態設定DropDownList的選定項目時,除了使用SelectedValue(例如:ddl_Orgcd.SelectedValue = args[0];)以外,也可以使用以下語法:

ddl_Orgcd.Items.FindByText(args[0]).Selected = true;

but there's a catch:
在這個指令之前,要先將現有選定項目清除,不然會有錯誤產生,告訴你不能有重複的項目被選取,當然也要注意ddl_Orgcd.Items.FindByText(args[0])可能會return null(hence a null reference exception)的情形,清除選定項目可以使用:

ddl_Orgcd.ClearSelection();
ddl_Orgcd.SelectedIndex = -1;

另外,有一些語法看起來作用很相似,其實不然,例如:

ddl_Orgcd.SelectedItem.Value = args[0];

T-SQL SET XACT_ABORT ON

在SQL Server 2005中針對SET XACT_ABORT ON的測試

1.
SET XACT_ABORT ON
BEGIN TRAN
exec pr_plan_addnew_emper 49, 0.1, '3', '526275'
exec pr_plan_addnew_emper 49, '', '3', '526275' -- data type error
print @@TranCount
COMMIT TRAN

得到的回應是:

訊息 8114，層級 16，狀態 1，程序 pr_plan_addnew_emper，行 0
Error converting data type varchar to decimal.

Observation:
因為第二個exec指令有錯誤,所以連帶著整個TRAN block都被rollback

2.
SET XACT_ABORT OFF
BEGIN TRAN
exec pr_plan_addnew_emper 49, 0.1, '3', '526275'
exec pr_plan_addnew_emper 49, '', '3', '526275' -- data type error
print @@TranCount
COMMIT TRAN

回應:
訊息 8114，層級 16，狀態 1，程序 pr_plan_addnew_emper，行 0
Error converting data type varchar to decimal.
1

Observation:
在XACT_ABORT為OFF的狀態下,整個block並沒有被rollback,僅有產生錯誤的那一行指令有被rollback,因此我們所觀察到的@@TranCount為1

3.
SET XACT_ABORT ON
BEGIN TRAN
exec pr_plan_addnew_emper 49, 0.1, '3', '526275'
RaisError ( N'Uh Oh!', 16, 1 ) -- generate an error
print @@TranCount
COMMIT TRAN

回應:
訊息 50000，層級 16，狀態 1，行 4
Uh Oh!
1

Observation:
使用人工方式產生一個error,並未造成TRAN rollback,(error並沒有被catch到?還是error並沒有造成state changes或是resource lockings?)

4.
SET XACT_ABORT ON
BEGIN TRAN
exec pr_plan_addnew_emper 49, 0.1, '3', '526275'
exec pr_plan_addnew_emper 49, '', '3', '526275' -- data type error
RaisError ( N'Uh Oh!', 16, 1 ) -- generate an error
print @@TranCount
COMMIT TRAN

回應:
訊息 8114，層級 16，狀態 1，程序 pr_plan_addnew_emper，行 0
Error converting data type varchar to decimal.
(same as test case #1)

Observation:
一旦有非人工方式產生的error,整段code block會被interrupt and rollback,因此error message 'Uh Oh!' 並不會被print出來

結論:
使用XACT_ABORT ON,可以提供整個transaction block的rollback,但是使用XACT_ABORT OFF的話,可以做到individual的transaction rollback,各有各的好處,另外XACT_ABORT是T-SQL語法,所以使用範圍有其限制

Reference:
http://www.sommarskog.se/error-handling-II.html#XACT_ABORT

.NET string format

Numbers
Basic number formatting specifiers:


Specifier Type Format Output (Passed Double 1.42) Output (Passed Int -12400)
c Currency {0:c} $1.42 -$12,400
d Decimal (Whole number) {0:d} System.FormatException -12400
e Scientific {0:e} 1.420000e+000 -1.240000e+004
f Fixed point {0:f} 1.42 -12400.00
g General {0:g} 1.42 -12400
n Number with commas for thousands {0:n} 1.42 -12,400
r Round trippable {0:r} 1.42 System.FormatException
x Hexadecimal {0:x4} System.FormatException cf90

Custom number formatting:


Specifier Type Example Output (Passed Double 1500.42) Note
0 Zero placeholder {0:00.0000} 1500.4200 Pads with zeroes.
# Digit placeholder {0:(#).##} (1500).42
. Decimal point {0:0.0} 1500.4
, Thousand separator {0:0,0} 1,500 Must be between two zeroes.
,. Number scaling {0:0,.} 2 Comma adjacent to Period scales by 1000.
% Percent {0:0%} 150042% Multiplies by 100, adds % sign.
e Exponent placeholder {0:00e+0} 15e+2 Many exponent formats available.
; Group separator see below

The group separator is especially useful for formatting currency values which require that negative values be enclosed in parentheses. This currency formatting example at the bottom of this document makes it obvious:

Dates
Note that date formatting is especially dependant on the system's regional settings; the example strings here are from my local locale.


Specifier Type Example (Passed System.DateTime.Now)
d Short date 10/12/2002
D Long date December 10, 2002
t Short time 10:11 PM
T Long time 10:11:29 PM
f Full date & time December 10, 2002 10:11 PM
F Full date & time (long) December 10, 2002 10:11:29 PM
g Default date & time 10/12/2002 10:11 PM
G Default date & time (long) 10/12/2002 10:11:29 PM
M Month day pattern December 10
r RFC1123 date string Tue, 10 Dec 2002 22:11:29 GMT
s Sortable date string 2002-12-10T22:11:29
u Universal sortable, local time 2002-12-10 22:13:50Z
U Universal sortable, GMT December 11, 2002 3:13:50 AM
Y Year month pattern December, 2002

The 'U' specifier seems broken; that string certainly isn't sortable.

Custom date formatting:


Specifier Type Example Example Output
dd Day {0:dd} 10
ddd Day name {0:ddd} Tue
dddd Full day name {0:dddd} Tuesday
f, ff, ... Second fractions {0:fff} 932
gg, ... Era {0:gg} A.D.
hh 2 digit hour {0:hh} 10
HH 2 digit hour, 24hr format {0:HH} 22
mm Minute 00-59 {0:mm} 38
MM Month 01-12 {0:MM} 12
MMM Month abbreviation {0:MMM} Dec
MMMM Full month name {0:MMMM} December
ss Seconds 00-59 {0:ss} 46
tt AM or PM {0:tt} PM
yy Year, 2 digits {0:yy} 02
yyyy Year {0:yyyy} 2002
zz Timezone offset, 2 digits {0:zz} -05
zzz Full timezone offset {0:zzz} -05:00
: Separator {0:hh:mm:ss} 10:43:20
/ Separator {0:dd/MM/yyyy} 10/12/2002

Enumerations

Specifier Type
g Default (Flag names if available, otherwise decimal)
f Flags always
d Integer always
x Eight digit hex.

Some Useful Examples
String.Format("{0:$#,##0.00;($#,##0.00);Zero}", value);

This will output "$1,240.00" if passed 1243.50. It will output the same format but in parentheses if the number is negative, and will output the string "Zero" if the number is zero.

String.Format("{0:(###) ###-####}", 18005551212);

This will output "(800) 555-1212".

quoted from: http://www.cnblogs.com/codingcow/archive/2006/07/23/113339.html

.NET GridView hidden column problem

在.NET 1.1的DataGrid裡面,column的visible屬性可以被設成false,造成hidden column的效果,同時在codebehind裡面,hidden column的值還是可以被取得

但是在NET 2.0的GridView裡面,如果將visible設成false,那麼在codefile裡面,這個欄位的值將是空字串,這是因為只要visible是false,GridView在DataBind時,便會自動略過去此欄位

解決方法為:

在GridView的RowCreated event裡面,再設定隱藏的欄位,例如:

protected void gv_Coorgcd_RowCreated(object sender, GridViewRowEventArgs e)
{
e.Row.Cells[4].Visible = false;
}

因為這個event handler是在data bound之後才會被觸發的,所以column裡面才會有資料

.NET data binding expression

We often use data binding expressions in the following way:

<asp:TextBox ID="tbx_Master" runat="server" Text='<%# Eval("org_master")
%>'></asp:TextBox>

But sometimes it requires explicit type conversion for the expression to work correctly. Otherwise the system will try to perform an implicit conversion for which an InvalicCastException might be thrown if not done right. For example:

<asp:RadioButton ID="rb_Master" runat="server" Checked='<%#
Convert.ToBoolean(Eval("org_master")) %>' GroupName="Master" />

.NET SqlDataSource ConvertEmptyStringToNull (revised)

在SqlDataSource裡面使用Select時,SelectParameters裡面如果有空字串,要將parameter的ConvertEmptyStringToNull屬性定義為false,(曾經有這種情形:設ConvertEmptyStringToNull為false,但空字串還是會被null取代,is this a known bug?or there is a problem with my setting?導致SelectCommand根本不會被執行,或是執行錯誤,也不會出現錯誤訊息
解決方法是使用SqlDataSource的Selecting事件,將所有的null值再度轉換成空字串)

例如:

有一個stored procedure叫做pr_test,pr_test需要兩個參數,@test1,@test2,假如在SQL Server Management Studio裡面查詢時輸入exec pr_test null, null的話,因為我有使用isnull將參數再度轉換回空字串,所以執行結果是正常的,有回傳一筆資料,但是假如在.NET裡面,我沒有設定參數的ConvertEmptyStringToNull屬性為false的話,就會產生錯誤,無法回傳資料

As a side note: 如果在stored procedure裡面同時需要使用Insert語法,並return select results,必須使用SqlDataSource Select,instead of Insert

.NET Exception Handling

在寫程式的時候,難免會有錯誤,因此不但要code defensively,還要確定在錯誤產生,使程式無法正常進行時,能夠保留足夠資訊以便進行debug,
最常見的error handling technique就是使用Exception,在try + catch block裡面catch exception之後,可以進行exception log,或是,直接rethrow exception

PS: For the following examples, the error is caused by an overflow after the user clicks on a "Save" button

1. throw ne Exception("錯誤訊息:" + e.Message);
cannot preserve information regarding the origin of the error


2. throw new Exception("錯誤訊息" + e.ToString());
preserve the information regarding the origin of the error while generating lengthy message


3. throw new Exception("錯誤訊息 + e.Message", e);
passing e as an inner exception helps preserve information while creating concise message regarding the error


4. throw e;
cannot preserve information

.NET 2.0 SqlDataSource notes

SqlDataSource透過各種的Parameters來傳遞參數, 常見的例如SelectParameter, InsertParameter...,使用這些Parameters時要注意的是,當參數中會出現空字串時,SqlDataSource會自動將他轉換成null,

SelectParameter中有一個屬性ConvertEmptyStringToNull default是被設成true的,於是便造成以上所說的情形,要解決這個問題:

1. 在CodeFile裡面:
dsDataSource1.SelectParameters[0].ConvertEmptyStringToNull = false;

2. 在.aspx裡面:
<SelectParameters>

<asp:Parameter Name="conttype1" Type="string" ConvertEmptyStringToNull="false"
/>

<asp:Parameter Name="conttype2" Type="string" ConvertEmptyStringToNull="false"
/>

</SelectParameters>

3. 在Stored Procedure裡面:
使用isnull(@param, '')將他轉換回來

ASP.NET DateTime 格式

ASP.NET (C#) 顯示日期的格式

1.在BoundField內顯示日期, 使用format string

<asp:BoundField DataFormatString= "{0:yyyy-MM-dd} " >

</asp:BoundField>

2.在CodeFile裡面對DataGrid/DataList等物件欄位格式化日期

Convert.ToDateTime([此處代入欄位text]).ToShortDateString()

3.用String.Format轉換日期格式

string.Format( "yyyy-MM-dd ", yourDateTime)

4.直接用ToString + format string方法轉換日期

DateTime.Now.ToString("yyyyMMddhhmmss")

DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss")

DateTime.Now.ToShortDateString()

@@IDENTITY Theft in SQL Server

In T-SQL, you can use the @@IDENTITY keyword to obtain the value of the identity column when you insert a new record. For example, the following query inserts a record into an imaginary table and returns a result set containing the ID of the inserted column.

INSERT INTO SomeTable

SELECT Value1, Value2



SELECT @@IDENTITY -- LAST INSERTED IDENTITY VALUE

There's the potential for a subtle bug here. Suppose later on, a coworker realizes that any time a record is inserted into [SomeTable] a record should also be inserted into the table [SomeTableAudit]. The simplest solution would be to add a trigger to [SomeTable] that inserts a record to [SomeTableAudit]. But in doing so, your coworker introduces a case of @@IDENTITY theft. Your original query will now return the value inserted into the IDENTITY column of the tabel [SomeTableAudit] instead of the IDENTITY value of [SomeTable] as you intended.

At this point some of you are shaking your heads muttering



Well I never use Triggers. Triggers are bad umkaaaay.


That's beside the point, you never know when someone else is going to apply that trigger resulting in this unintended consequence. It pays to program defensively. The issue here is that although @@IDENTITY is constrained to the current session, it is not constrained to the current scope. Instead, use the SCOPE_IDENTITY() function which will return the last IDENTITY column value inserted in the current scope, in this case the value inserted into [SomeTable].

INSERT INTO SomeTable

SELECT Value1, Value2



SELECT SCOPE_IDENTITY() -- LAST INSERTED IDENTITY VALUE

As an aside, I'm fine with triggers in certain cases. One of the primary complaints about triggers mirrors the complaints about Aspects in AOP. Namely that triggers provide for unintended consequences that aren't visible when examining a stored procedure. However when used sparingly for cross-cutting functionality, I think they can add a lot of benefit. Much like Aspects.

contents quoted from: http://haacked.com/archive/2005/04/11/2627.aspx

3D Rose Tryouts

.NET 2.0 GridView RowCommand

我們常常需要再GridView裡面加入Controls,例如LinkButton, ImageButton之類的,
,只要設定CommandName屬性,便可使區分各種不同的Control所引發的事件

加入Button的方法可能有:

1. 使用TemplateField
 <asp:TemplateField HeaderText="規劃案號" SortExpression="planno" ItemStyle-Width="20%">

<ItemTemplate>

<asp:LinkButton runat="server" Text='<%# Eval("planno") %>' CommandName="Select"
CommandArgument='<%# Eval("planno") %>'></asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

2. 使用ButtonField
<asp:ButtonField HeaderText="規劃案號" ButtonType="Link" DataTextField="planno"
SortExpression="planno"

CommandName="Select" ItemStyle-Width="20%" />

問題:

.NET 1.x的DataGrid: 可以使用ItemCommand event handler的e.Item.ItemIndex這樣的語法來獲得目前的RowIndex
.NET 2.0的GridView: 其RowCommand event handler並沒有相對應於ItemIndex這樣的屬性

解決方法:

1. 使用第一種方法(assuming ImageButton Control is used)

int rowIndex = ((GridViewRow)((ImageButton)e.CommandSource).NamingContainer).RowIndex;

或是(assuming LinkButton is used)

int rowindex = ((GridViewRow)((LinkButton)e.CommandSource).NamingContainer).RowIndex

便可取得所需的row index了

2. 使用第二種方式

則e.CommandArgument便可取得RowIndex了,不過缺點是這個方法就沒辦法另外加CommandArgument

另外一個解決方式:

在RowCreated event handler中將屬於每一行的RowIndex給指定到CommandArgument中,便可以在之後的RowCommand event handler裡面使用e.CommandArgument來直接取得RowIndex了,這樣做的缺點是,如果你有其他的參數想要指定給CommandArgument的話,就沒辦法了

參考網頁:
http://www.cnblogs.com/oomusou/archive/2007/06/26/796094.html
http://msdn2.microsoft.com/zh-tw/library/system.web.ui.webcontrols.gridview.rowcreated(VS.80).aspx

.NET 2.0在GridView沒有資料時顯示表頭的方法

DataGrid在沒有資料時,會顯示出表頭,但是在GridView沒有資料時,不會顯示任何東西
這時我們可以使用EmptyDataTemplate來製作要顯示的內容, 例如:

<asp:GridView ID="gv_Emper" runat="server">

<EmptyDataTemplate>

<asp:Table runat="server">

<asp:TableHeaderRow runat="server" CssClass="Theader" HorizontalAlign="center">

<asp:TableHeaderCell runat="server" Text="檢視"></asp:TableHeaderCell>

<asp:TableHeaderCell runat="server" Text="姓名"></asp:TableHeaderCell>

<asp:TableHeaderCell runat="server" Text="工號"></asp:TableHeaderCell>

<asp:TableHeaderCell runat="server" Text="分機"></asp:TableHeaderCell>

</asp:TableHeaderRow>

</asp:Table>

</EmptyDataTemplate>

~~~~略~~~~

</asp:GridView>

.NET SqlDataSource notes

SqlDataSource的一些comments:

1. SelectCommand,也是可以動態設定的,例如:

ds_Emper.SelectCommand = String.Format(@"SELECT com_cname, com_empno, com_telext FROM comper WHERE com_empno IN ('{0}')", Request.QueryString["seqsn"].Replace(",", "','"));

2. 如果將DataSourceMode設為DataReader而不是DataSet,那麼可能會影響到使用這個SqlDataSource的GridView,使用分頁時會出現以下訊息:

System.NotSupportedException: SqlDataSource 'ds_Emper' 並未啟用分頁。請將 DataSourceMode 設為 DataSet 以啟用分頁

3. SelectParameters 是一個Collection, 所以可以動態的去做設定,例如

datasource.SelectParameters[paramname]......
datasource.SelectParameters.Add(......)
datasource.SelectParameters.Clear()
datasource.SelectParameters.Remove(......)