Rj. Rj. - 3 years ago 83
SQL Question

how do I pass a database NULL value to a string variable?

Basically if a parameter comes in as NULL, I want to send it to the database as a database NULL. As such (look at the comment inside the code below):

[HttpPost]
public void UpdateTitle(Title title)
{
string query = null;
string description = "";
string episodeAKA = "";

if (title.Description != null)
{
description = "'" + title.Description + "'";
}
else
{
//here's where description should be a DBNULL.
}

if (title.EpisodeAKA == null)
{
title.EpisodeAKA = "NULL";
}

myConnection.Open();
if (title.Operation == 'U')
{
query = "UPDATE dbo.AWD_Titles SET AwardStatusId = " + title.AwardStatus + ", Description = " + description + ", IsVerified = " + title.IsVerified + ", EpisodeAKA = '" + title.EpisodeAKA + "' WHERE AwardTitleId = " + title.AwardTitleId + " SELECT SCOPE_IDENTITY()";
}
var cmd = new SqlCommand(query, myConnection);
cmd.ExecuteScalar();
myConnection.Close();
}
}


And here's the class for Title:

public class Title
{
public int AwardTitleId
{
get;
set;
}

public int AwardStatus
{
get;
set;
}

public int IsVerified
{
get;
set;
}

public string EpisodeAKA
{
get;
set;
}

public string Description
{
get;
set;
}

public char Operation
{
get;
set;
}
}

Answer Source

The original code had several fundamental errors. This demonstrates how to do it right, including how to set DBNull:

[HttpPost]
public void UpdateTitle(Title title)
{
    string query; 
    if (title.Operation == 'U')
    {
        query = 
            "UPDATE dbo.AWD_Titles" + 
            " SET AwardStatusId = @AwardStatusID , Description = @Description , IsVerified= @IsVerified , EpisodeAKA= @EpisodeAKA" + 
            " WHERE AwardTitleId= @AwardTitleId ;" + 
            " SELECT SCOPE_IDENTITY();";
    } else {
       query="";
       //presumably you have a slightly different query string for inserts.
       //Thankfully, they should have pretty much the same set of parameters.
       //If this method will really only be called for updates, the code is quite a bit simpler
    }

    //instead of a shared myConnection object, use a shared connection string.
    // .Net is set up so that you should be creating a new connection object for most queries.
    // I know it sounds backwards, but that's really the right way to do it.
    // Create the connection in a using(){} block, so that you guarantee it is
    //    disposed correctly, even if an exception is thrown.
    using (var cn = new SqlConnection(myConnectionString))
    using (var cmd = new SqlCommand(query, cn))
    {
        //guessing at database types, lengths here. Fix with actual column types
        cmd.Parameters.Add("@AwardStatusId", SqlDbType.Int).Value = title.AwardStatus;
        cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 250).Value = title.Description;
        cmd.Parameters.Add("@IsVerified", SqlDbType.Bit).Value = title.IsVerified;
        cmd.Parameters.Add("@EpisodeAKA", SqlDbType.NVarChar, 100).Value = title.EpisodeAKA;
        cmd.Parameters.Add("@AwardTitleId", SqlDbType.Int).Value = title.AwardTitleId;

        //-------------
        //This is the part that actually answers your question
        foreach (var p in cmd.Parameters.Where(p => p.Value == null))
        {
            p.Value = DBNull.Value;
        }
        //-------------

        cn.Open();
        cmd.ExecuteScalar();
    }
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download