Convert Null to DBNull

[scott ocamb]

Hello,
I have a function that expects the following parms. How can I handle null
values. For example, if FirstName comes through as null, how can i propogate
that null value to the stored procedure.

public long InsertPerson(string FirstName, string LastName, string
MiddleName, string EMail, long PersonTypeId,
string HomePhone, string CellPhone, bool EmailOptOut, string City,
long StateId, string PostalCode, string Street, long CountryId, long
InsertUserId)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("ks_Person_InsertPerson", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 30).Value =
LastName;

 

[Otis Mukinfus]

Hello,
I have a function that expects the following parms. How can I handle null
values. For example, if FirstName comes through as null, how can i propogate
that null value to the stored procedure.

public long InsertPerson(string FirstName, string LastName, string
MiddleName, string EMail, long PersonTypeId,
string HomePhone, string CellPhone, bool EmailOptOut, string City,
long StateId, string PostalCode, string Street, long CountryId, long
InsertUserId)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("ks_Person_InsertPerson", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 30).Value =
LastName;

if(string.IsNullOrEmpty(FirstName))
{
cmd.Parameters.AddWithValue("@Firstname", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@Firstname", FirstName);
}

There may be typos here, but you get the idea...

Good luck with your project,

Otis Mukinfus

http://www.otismukinfus.com
http://www.arltex.com
http://www.tomchilders.com
http://www.n5ge.com

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Hello,
I have a function that expects the following parms. How can I handle null
values. For example, if FirstName comes through as null, how can i
propogate that null value to the stored procedure.

Easily:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName
== null? DBNull.Value: FirstName;

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Hi,




Easily:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName
== null? DBNull.Value: FirstName;

Or even easier:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
FirstName ?? DBNull.Value;

 

[scott ocamb]

Thanks for your help, but i get compile errors.

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName ??
DBNull.Value;
Error 1 ) expected C:\KI\Kinesis\Code2\CoachwareEntities\Athlete.cs 176 27
CoachwareEntities

seems to be some sort of casting error.

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = (FirstName
?? DBNull.Value);

Error 3 Operator '??' cannot be applied to operands of type 'string' and
'System.DBNull' C:\KI\Kinesis\Code2\CoachwareDAL\Person.cs 94 87
CoachwareDAL

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Right. The compiler can't find any common type for the operands, so you
have to specify it:

cmd.Parameters.Add("@Firstname", SqlDbType.VarChar, 30).Value =
FirstName ?? (object)DBNull.Value;

By specifying one operand as object, it will see that the other operand
also can be cast to that type.

Thanks for your help, but i get compile errors.

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName ??
DBNull.Value;
Error 1 ) expected C:\KI\Kinesis\Code2\CoachwareEntities\Athlete.cs 176 27
CoachwareEntities

seems to be some sort of casting error.

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = (FirstName
?? DBNull.Value);

Error 3 Operator '??' cannot be applied to operands of type 'string' and
'System.DBNull' C:\KI\Kinesis\Code2\CoachwareDAL\Person.cs 94 87
CoachwareDAL

 

[scott ocamb]

thanks,

you said it was simple, it is only simple once you know how,

i learned something today, thanks : )

Right. The compiler can't find any common type for the operands, so you
have to specify it:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName
?? (object)DBNull.Value;

By specifying one operand as object, it will see that the other operand
also can be cast to that type.