Identification of "null" in. NET and SQL Server

. NetAndSQL ServerMedium"Null Value"Discrimination

 

For beginner database programming, we may have some"Null Value"For example, all data in a new table is displayed<Null>, Manually add and delete text and then become blank; a string type field, clearly not filled in, but not equal""; UseAdo. netValue from the database.<Null>An error occurs.......This requires a correct understanding.. NetAndSQL ServerSeveral different"Null Value".

1 , true null value, that is, " no input value " , can appear in most types of fields (if there are no other constraints ), SQL server indicates null , displayed as , manually enter SQL server Enterprise Manager by pressing Ctrl + 0 . It corresponds to system. dbnull. value in . Net . In the T-SQL command, determine whether a value is null, use "is null" instead of "= NULL" ; there is a isnull function to process null values, it replaces null with the specified value. Use ADO. net the null value obtained from the database cannot be automatically converted to an empty string or nothing . Manual detection is required: if system. dbnull. value is assigned to the Data Object nothing or other custom meaningful values.

2, Empty string (zero-length string), only appears in the string type (suchNvarchar,SQL ServerIs'', Displayed as blank, manuallySQL ServerEnter a cell in the Enterprise Manager. It is in. NetCorrespondingSystem. String. EmptyThat is, what we commonly use"". InT-SQLThere is no difference between processing a Null String in a command and processing a general string. UseAdo. netThe Null String obtained from the database is no different from the general string.

 About dbnull

dbnull DOTNET is a separate type. system. dbnull . It has only one dbnull. value . dbnull direct inheritance Object , therefore, dbnull not string, NO int, not datetime ...

But why?DbnullCan it represent a string, number, or date in the database? The reason is:DOTNETClasses that store the data (DatarowAnd so on.ObjectTo store data.ForDatarow,ItsRow [column]The returned value is neverNull, OrColumnType value . OrDbnull. SoRow [column]. tostring ()This statement will never be written inTostringOccurred thereNullreferenceexception.

DbnullImplementedIconvertible. However,TostringIs normal, OtherToxxxWill throw an error that cannot be converted.

InIdbcommand (oledbcommand, sqlcommand ...)OfExecutescalarIn the returned value, the situation can be analyzed as follows:

Select 1ReturnsObjectYes1
Select nullThe returned result isDbnull. Value
Select isnull (null, 1)The returned result is1
Select top 0 ID from Table1The returned value isNull
Select isnull (ID, 0) from Table1 where 1 = 0The returned value isNull

HereExecutescalarThe rule is to return the data in the first column and the first row. If the first row of the first column is not emptyExecutescalarThe correspondingDOTNET. If the first row exists but the first column is emptyDbnull. If none of the rows existExecutescalarReturnsNull

The rule is like this. One easy mistake here isExecutescalarReturnDbnullAndNullFor example:

String username = cmd. executescalar (). tostring ();

Unless you thinkCMDAfter execution, there must be at least one row of data; otherwise, an error will occur.

OrSelect ID from usertable where username = @ nameSuchSQLStatement. If no record is foundExecutescalarWill returnNull,So never

Int userid = convert. toint32 (CMD. executescalar ());

Or you will writeSQLStatement:Select isnull (ID, 0) from usertable where username = @ name

HoweverInt userid = convert. toint32 (CMD. executescalar ());An error still occurs because no rows are returned if the preceding statement is not valid.

ForIdbdataparameter (oleddbparameter, sqlparameter ..)OfValue, IfNull,This parameter is not specified or representsDefault. IfDbnull. Value, IndicatesSQLInNull

Therefore, if you want to call a stored procedure, there are parameters in it@ Val nvarchar (20) = "AABB ",
SoCmd. Parameters ["@ Val"]. value = NULLIndicates that the default"AABB"
WhileCmd. Parameters ["@ Val"]. value = dbnull. ValueRepresentativeNullTo pass@ Val

You can useConvert. isdbnullTo determine whether a value isDbnull. Note:Convert. isdbnull (null)YesFalse.

Supplement:DbnullRefers to"Null"InsteadCLRIn"Null".

For example, many beginners think the following is the same:

Cmd. Parameters ["@ payment_type"]. value = "";

Cmd. Parameters ["@ payment_type"]. value = system. dbnull. value;

 

""Represents a null string, andSystem. dbnull. ValueRepresentsNull, No data. As the teacher said"0"Not the same