.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(......)