[研究]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() 時候，要抓取欄位值，隱藏顯示某些資訊。
(下圖) 把上面問題都註解排除後，顯示畫面如下
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 型態。
(完)
沒有留言:
張貼留言