.NET和SQL Server中“空值”辨析

 

.NET和SQL Server中“空值”辨析

 

     初學資料庫編程我們可能會有一些對“空值”的疑問，比如通過編程建立的一個表中所有資料皆顯示為<NULL>，手動添加並刪除文字後又變成了空白；一個字串類型的欄位，明明沒有填值，卻不等於""；用ADO.NET從資料庫中取值，每遇到有<NULL>的就出錯……這需要我們正確認識.NET和SQL Server中幾種不同的“空值”。

    1、真正的空值，也就是“沒有輸入的值”，可以出現在大多數類型的欄位中（如果沒有別的約束條件），SQL server中表示為null，顯示為<NULL>，手工在SQL server企業管理器中輸入的方法是按Ctrl+0。它在.NET中對應System.DBNull.Value。在T-SQL命令中，判斷一個值是不是空值，要用“is null”而不是“= null”；處理空值有個ISNULL函數，它使用指定的值替換null。用ADO.NET從資料庫得到的空值無法自動轉化為空白字串或Nothing，須手動檢測：如果得到System.DBNull.Value，則賦給資料對象Nothing或其它自訂的有意義的值。

    2、Null 字元串（零長度字串），只出現在字串類型（如nvarchar）的欄位中，SQL server中表示為''，顯示為空白，手工在SQL server企業管理器中輸入時清空一個儲存格即可。它在.NET中對應System.String.Empty，也就是我們常用的""。在T-SQL命令中處理Null 字元串和處理一般的字串沒什麼區別。用ADO.NET從資料庫得到的Null 字元串也和一般的字串沒什麼區別。

 DBNull簡介

    DBNull在DotNet是單獨的一個類型 System.DBNull 。它只有一個值 DBNull.Value 。DBNull 直接繼承 Object ，所以 DBNull 不是 string , 不是 int , 也不是 DateTime 。。。

     但是為什麼 DBNull 可以表示資料庫中的字串，數字，或日期呢？原因是DotNet儲存這些資料的類（DataRow等）都是以 object 的形式來儲存資料的。對於 DataRow , 它的 row[column] 返回的值永遠不為 null ， 要麼就是具體的為column 的類型的值 。 要麼就是 DBNull 。 所以 row[column].ToString() 這個寫法永遠不會在ToString那裡發生NullReferenceException。

    DBNull 實現了 IConvertible 。 但是，除了 ToString 是正常的外，其他的ToXXX都會拋出不能轉換的錯誤。

    在 IDbCommand(OleDbCommand,SqlCommand...) 的ExecuteScalar的傳回值中，情況可以這樣分析：

select 1 這樣返回的object是 1
select null 這樣返回的是DBNull.Value
select isnull(null,1) 返回的是 1
select top 0 id from table1 這樣返回的值是null
select isnull(id,0) from table1 where 1=0 返回的值是null

這裡 ExecuteScalar 的規則就是，返回第一列，第一行的資料。如果第一列第一行不為空白，那麼ExecuteScalar就直接對應的DotNet的值。如果有第一行，但是第一列為空白，那麼返回的是 DBNull 。如果一行都沒有，那麼ExecuteScalar就返回null

規則就是這樣的。這裡容易犯的一個錯誤是，把ExecuteScalar返回DBNull與null的情況混淆，例如：

string username=cmd.ExecuteScalar().ToString();

除非你認為cmd執行後，肯定至少有一行資料，否則這裡就會出錯。

又或者 select id from usertable where username=@name 這樣的sql語句，如果找不到記錄，那麼ExecuteScalar則會返回null,所以千萬不要

int userid=Convert.ToInt32(cmd.ExecuteScalar());

或者你會這樣寫 SQL 陳述式：select isnull(id,0) from usertable where username=@name

但是 int userid=Convert.ToInt32(cmd.ExecuteScalar()); 依然會出錯，因為上面的語句不成立時，仍然是不返回任何行。

對於IDbDataParameter(OleDDbParameter,SqlParameter..)的Value，如果為null,則代表該參數沒有指定，或者是代表DEFAULT。如果為DBNull.Value，則代表SQL中的NULL

所以，如果你要調用預存程序，裡面有參數 @val nvarchar(20)="AABB" ,
那麼cmd.Parameters["@val"].Value=null 代表使用這個預設的 "AABB"
而cmd.Parameters["@val"].Value=DBNull.Value 代表使用NULL來傳給 @val

你可以用Convert.IsDBNull來判斷一個值是否DBNull。注意Convert.IsDBNull(null)是false。

補充：DBNull指的是資料庫中的“null”，而不是CLR中的“null”。

如很多初學者以為下面是一樣的：

cmd.Parameters["@payment_type"].Value = "";

cmd.Parameters["@payment_type"].Value = System.DBNull.Value;

 

“”表示的是Null 字元串，而System.DBNull.Value代表的是資料庫中的NULL，既沒有資料。就象老師說“0”不是沒有一樣