浮雲雅築: [研究]SQL Server 2019 欄位是允許 NULL 的 bit，「將這個欄位轉換為 TemplatedField」後的處理

[研究]SQL Server 2019 欄位是允許 NULL 的 bit，ASP.NET 「將這個欄位轉換為 TemplatedField」後的處理

2021-07-01

Visual Studio 2019 v16.10 + ASP.NET + WebForm + WebApplication + C#

如果是 Not Null 欄位，不會有 Null 狀態，不會有問題。

偶然發現 Nullable 的 bit 欄位，使用「將這個欄位轉換為 TemplatedField」把 BoundField 轉成為 TemplateField 後，網頁程式執行時會出錯，所以做了點研究測試。

為何要轉換，因為 RowDataBound() 或 DataBound() 時候，要抓取欄位值，隱藏顯示某些資訊。

(下圖) 建立 16 個相同的 Nullable bit 欄位，因為要測試不同函數轉換，以及 Type 從 Boolean 轉成 Int16 是否可行

(下圖) 建立 4 筆資料，幾乎都是 NULL

(下圖) 把上面問題都註解排除後，顯示畫面如下

(下圖) 把上面問題都註解排除後，程式如下

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="BitTest.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" 
                DeleteCommand="DELETE FROM [TestTable2] WHERE [SN] = @SN" 
                InsertCommand="INSERT INTO [TestTable2] ([NullableBit1], [NullableBit2], [NullableBit3], [NullableBit4], [NullableBit5], [NullableBit6], [NullableBit7], [NullableBit8], [NullableBit9], [NullableBit10], [NullableBit11], [NullableBit12], [NullableBit13], [NullableBit14], [NullableBit15], [NullableBit16], [NotNullBit]) VALUES (@NullableBit1, @NullableBit2, @NullableBit3, @NullableBit4, @NullableBit5, @NullableBit6, @NullableBit7, @NullableBit8, @NullableBit9, @NullableBit10, @NullableBit11, @NullableBit12, @NullableBit13, @NullableBit14, @NullableBit15, @NullableBit16, @NotNullBit)" 
                SelectCommand="SELECT [SN]
      ,[NullableBit1]
      ,[NullableBit2]
      ,[NullableBit3]
      ,[NullableBit4]
      ,ISNULL([NullableBit5],0) AS [NullableBit5]
      ,ISNULL([NullableBit6],0) AS [NullableBit6]
      ,ISNULL([NullableBit7],0) AS [NullableBit7]
      ,ISNULL([NullableBit8],0) AS [NullableBit8]
      ,CASE WHEN [NullableBit9] IS NULL THEN 0 ELSE [NullableBit9] END AS [NullableBit9]
	  ,CASE WHEN [NullableBit10] IS NULL THEN 0 ELSE [NullableBit10] END AS [NullableBit10]
      ,CASE WHEN [NullableBit11] IS NULL THEN 0 ELSE [NullableBit11] END AS [NullableBit11]
	  ,CASE WHEN [NullableBit12] IS NULL THEN 0 ELSE [NullableBit12] END AS [NullableBit12]
      ,CASE WHEN [NullableBit13] IS NULL THEN CAST(0 AS BIT) ELSE [NullableBit13] END AS [NullableBit13]
	  ,CASE WHEN [NullableBit14] IS NULL THEN CAST(0 AS BIT) ELSE [NullableBit14] END AS [NullableBit14]
      ,CASE WHEN [NullableBit15] IS NULL THEN CAST(0 AS BIT) ELSE [NullableBit15] END AS [NullableBit15]
	  ,CASE WHEN [NullableBit16] IS NULL THEN CAST(0 AS BIT) ELSE [NullableBit16] END AS [NullableBit16]
      ,[NotNullBit]
  FROM [TestDB].[dbo].[TestTable2]" 
                UpdateCommand="UPDATE [TestTable2] SET [NullableBit1] = @NullableBit1, [NullableBit2] = @NullableBit2, [NullableBit3] = @NullableBit3, [NullableBit4] = @NullableBit4, [NullableBit5] = @NullableBit5, [NullableBit6] = @NullableBit6, [NullableBit7] = @NullableBit7, [NullableBit8] = @NullableBit8, [NullableBit9] = @NullableBit9, [NullableBit10] = @NullableBit10, [NullableBit11] = @NullableBit11, [NullableBit12] = @NullableBit12, [NullableBit13] = @NullableBit13, [NullableBit14] = @NullableBit14, [NullableBit15] = @NullableBit15, [NullableBit16] = @NullableBit16, [NotNullBit] = @NotNullBit WHERE [SN] = @SN">
                <DeleteParameters>
                    <asp:Parameter Name="SN" Type="Int32" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="NullableBit1" Type="Boolean" />
                    <asp:Parameter Name="NullableBit2" Type="Boolean" />
                    <asp:Parameter Name="NullableBit3" Type="Boolean" />
                    <asp:Parameter Name="NullableBit4" Type="Boolean" />
                    <asp:Parameter Name="NullableBit5" Type="Boolean" />
                    <asp:Parameter Name="NullableBit6" Type="Boolean" />
                    <asp:Parameter Name="NullableBit7" Type="Boolean" />
                    <asp:Parameter Name="NullableBit8" Type="Boolean" />
                    <asp:Parameter Name="NullableBit9" Type="Boolean" />
                    <asp:Parameter Name="NullableBit10" Type="Boolean" />
                    <asp:Parameter Name="NullableBit11" Type="Boolean" />
                    <asp:Parameter Name="NullableBit12" Type="Boolean" />
                    <asp:Parameter Name="NullableBit13" Type="Boolean" />
                    <asp:Parameter Name="NullableBit14" Type="Boolean" />
                    <asp:Parameter Name="NullableBit15" Type="Boolean" />
                    <asp:Parameter Name="NullableBit16" Type="Boolean" />
                    <asp:Parameter Name="NotNullBit" Type="Boolean" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="NullableBit1" Type="Boolean" />
                    <asp:Parameter Name="NullableBit2" Type="Boolean" />
                    <asp:Parameter Name="NullableBit3" Type="Int16" />
                    <asp:Parameter Name="NullableBit4" Type="Int16" />
                    <asp:Parameter Name="NullableBit5" Type="Boolean" />
                    <asp:Parameter Name="NullableBit6" Type="Boolean" />
                    <asp:Parameter Name="NullableBit7" Type="Int16" />
                    <asp:Parameter Name="NullableBit8" Type="Int16" />
                    <asp:Parameter Name="NullableBit9" Type="Boolean" />
                    <asp:Parameter Name="NullableBit10" Type="Boolean" />
                    <asp:Parameter Name="NullableBit11" Type="Int16" />
                    <asp:Parameter Name="NullableBit12" Type="Int16" />
                    <asp:Parameter Name="NullableBit13" Type="Boolean" />
                    <asp:Parameter Name="NullableBit14" Type="Boolean" />
                    <asp:Parameter Name="NullableBit15" Type="Int16" />
                    <asp:Parameter Name="NullableBit16" Type="Int16" />
                    <asp:Parameter Name="NotNullBit" Type="Boolean" />
                    <asp:Parameter Name="SN" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>
            <asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="SN" DataSourceID="SqlDataSource2">
                <Columns>
                    <asp:CommandField ShowEditButton="True" />
                    <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
                    <asp:CheckBoxField DataField="NullableBit1" HeaderText="NullableBit1" SortExpression="NullableBit1" />
                    <asp:TemplateField HeaderText="NullableBit2" SortExpression="NullableBit2">
                        <EditItemTemplate>
                            <%--System.InvalidCastException，HResult=0x80004002，指定的轉換無效。--%>
                            <%--<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("NullableBit2") %>' />--%>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <%--System.InvalidCastException，HResult=0x80004002，指定的轉換無效。--%>
                            <%--<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("NullableBit2") %>' Enabled="false" />--%>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:CheckBoxField DataField="NullableBit3" HeaderText="NullableBit3" SortExpression="NullableBit3" />
                    <asp:TemplateField HeaderText="NullableBit4" SortExpression="NullableBit4">
                        <EditItemTemplate>
                            <%--System.InvalidCastException，HResult=0x80004002，指定的轉換無效。--%>
                            <%--<asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("NullableBit4") %>' />--%>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <%--System.InvalidCastException，HResult=0x80004002，指定的轉換無效。--%>
                            <%--<asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("NullableBit4") %>' Enabled="false" />--%>
                        </ItemTemplate>
                    </asp:TemplateField>

                    <%--********************************************************************************--%>
                    <%--,ISNULL([NullableBit5],0) AS [NullableBit5]--%>
                    <asp:CheckBoxField DataField="NullableBit5" HeaderText="NullableBit5" SortExpression="NullableBit5" />
                    <asp:TemplateField HeaderText="NullableBit6" SortExpression="NullableBit6">
                        <EditItemTemplate>
                            <asp:CheckBox ID="CheckBox3" runat="server" Checked='<%# Bind("NullableBit6") %>' />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:CheckBox ID="CheckBox3" runat="server" Checked='<%# Bind("NullableBit6") %>' Enabled="false" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:CheckBoxField DataField="NullableBit7" HeaderText="NullableBit7" SortExpression="NullableBit7" />
                    <asp:TemplateField HeaderText="NullableBit8" SortExpression="NullableBit8">
                        <EditItemTemplate>
                            <asp:CheckBox ID="CheckBox4" runat="server" Checked='<%# Bind("NullableBit8") %>' />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:CheckBox ID="CheckBox4" runat="server" Checked='<%# Bind("NullableBit8") %>' Enabled="false" />
                        </ItemTemplate>
                    </asp:TemplateField>

                    <%--********************************************************************************--%>

                    <%--,CASE WHEN [NullableBit9] IS NULL THEN 0 ELSE [NullableBit9] END AS [NullableBit9]--%>
                    <%--字串未被辨認為有效的 Boolean。--%>
                    <%--<asp:CheckBoxField DataField="NullableBit9" HeaderText="NullableBit9" SortExpression="NullableBit9" />--%>

                    <asp:TemplateField HeaderText="NullableBit10" SortExpression="NullableBit10">
                        <EditItemTemplate>
                            <%--System.InvalidCastException，HResult=0x80004002，指定的轉換無效。--%>
                            <%--<asp:CheckBox ID="CheckBox5" runat="server" Checked='<%# Bind("NullableBit10") %>' />--%>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <%--System.InvalidCastException，HResult=0x80004002，指定的轉換無效。--%>
                            <%--<asp:CheckBox ID="CheckBox5" runat="server" Checked='<%# Bind("NullableBit10") %>' Enabled="false" />--%>
                        </ItemTemplate>
                    </asp:TemplateField>
                    
                    <%--字串未被辨認為有效的 Boolean。--%>
                    <%--<asp:CheckBoxField DataField="NullableBit11" HeaderText="NullableBit11" SortExpression="NullableBit11" />--%>

                    <asp:TemplateField HeaderText="NullableBit12" SortExpression="NullableBit12">
                        <EditItemTemplate>
                            <%--System.InvalidCastException，HResult=0x80004002，指定的轉換無效。--%>
                            <%--<asp:CheckBox ID="CheckBox6" runat="server" Checked='<%# Bind("NullableBit12") %>' />--%>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <%--System.InvalidCastException，HResult=0x80004002，指定的轉換無效。--%>
                            <%--<asp:CheckBox ID="CheckBox6" runat="server" Checked='<%# Bind("NullableBit12") %>' Enabled="false" />--%>
                        </ItemTemplate>
                    </asp:TemplateField>

                    <%--********************************************************************************--%>

                    <%--CASE WHEN [NullableBit13] IS NULL THEN CAST(0 AS BIT) ELSE [NullableBit13] END AS [NullableBit13]--%>
                    <asp:CheckBoxField DataField="NullableBit13" HeaderText="NullableBit13" SortExpression="NullableBit13" />
                    <asp:TemplateField HeaderText="NullableBit14" SortExpression="NullableBit14">
                        <EditItemTemplate>
                            <asp:CheckBox ID="CheckBox7" runat="server" Checked='<%# Bind("NullableBit14") %>' />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:CheckBox ID="CheckBox7" runat="server" Checked='<%# Bind("NullableBit14") %>' Enabled="false" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:CheckBoxField DataField="NullableBit15" HeaderText="NullableBit15" SortExpression="NullableBit15" />
                    <asp:TemplateField HeaderText="NullableBit16" SortExpression="NullableBit16">
                        <EditItemTemplate>
                            <asp:CheckBox ID="CheckBox8" runat="server" Checked='<%# Bind("NullableBit16") %>' />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:CheckBox ID="CheckBox8" runat="server" Checked='<%# Bind("NullableBit16") %>' Enabled="false" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:CheckBoxField DataField="NotNullBit" HeaderText="NotNullBit" SortExpression="NotNullBit" />
                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>
</html>

結論，如果 bit 欄位是 Not Null ，不會有問題，建議作法。

如果bit欄位一定要 Nullable，SELECT 最好先用 ISNULL() 或 CASE WHEN 把 NULL 換成 0，

而且用 CASE WHEN 時候，要用 CAST 把結果 0 指定為 BIT 型態。

(完)

浮雲雅築

2021年7月2日星期五

[研究]SQL Server 2019 欄位是允許 NULL 的 bit，「將這個欄位轉換為 TemplatedField」後的處理

沒有留言:

張貼留言

2021年7月2日 星期五

[研究]SQL Server 2019 欄位是允許 NULL 的 bit，「將這個欄位轉換為 TemplatedField」後的處理

沒有留言:

張貼留言

2021年7月2日星期五