Check if value exists or is null

Question

A question...I have a code in the example below...I have to insert some values into the database..but directly from data row..I don't want to use variables.So my question is, is there a way to check if value in data row exists or if it is null, if the value does not exist I have to insert null or if it's null just insert null...

The example:

myQuery = " INSERT INTO AGR3PL_CS (IDOC_NUM, PSEG_NUM, SEG_NUM, COMP01, GLNO01, NAME01) " +
          " VALUES (" + Lidoc_num + ", '" +
                        PSEG_NUM + "','" +
                        SEG_NUM + "','" +
                        dr_art_custsuply["Id"] + "','" +
                        dr_art_custsuply["GLN"] + "','" +
                        dr_art_custsuply["Name"] + "')";

This is the way that I don't want to use...

if (!dr_art_custsuply.Table.Columns.Contains("GLN") || dr_art_custsuply["GLN"].ToString().Length <= 0)
{
    gln = "";
}
else
{
    gln = dr_art_custsuply["GLN"].ToString();
}

**NEVER** write queries like this, use [parameters](http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx) instead — Tzah Mama, Jun 16 '14 at 06:40
Yea I know that that's not the right way..but in other "Insert" command I have 50 parameters to insert... — CrBruno, Jun 16 '14 at 06:44
@CrBruno what difference does the number of parameters make, exactly? There are tools that can help with that, though - dapper makes parameterization *insanely* easy. If anything, having 50 parameters instead of 6 just means there are 8 times as many reasons to do it correctly - and 8 times as many ways for somebody to break your system if you don't. — Marc Gravell, Jun 16 '14 at 06:46
Even ignoring malicious users, is it that unreasonable for `dr_art_custsuply["Name"]` to contain single quotes (e.g., an art supply named *O'Malley's Green Paint*)? And no, storing it escaped is not the right way to fix this. — Brian, Jun 18 '14 at 18:13

Marc Gravell · Answer 1 · 2014-06-16T06:50:18.140

Parameters
DBNull.Value

For example (since you are using oracle):

cmd.CommandText = @"
INSERT INTO AGR3PL_CS (IDOC_NUM, PSEG_NUM, SEG_NUM, COMP01, GLNO01, NAME01)
VALUES (:IDOC_NUM, :PSEG_NUM, :SEG_NUM, :COMP01, :GLNO01, :NAME01)";
cmd.Parameters.AddWithValue("IDOC_NUM", Lidoc_num);
cmd.Parameters.AddWithValue("PSEG_NUM", PSEG_NUM);
cmd.Parameters.AddWithValue("SEG_NUM", SEG_NUM);
cmd.Parameters.AddWithValue("COMP01", dr_art_custsuply["Id"] ?? DBNull.Value);
cmd.Parameters.AddWithValue("GLNO01", dr_art_custsuply["GLN"] ?? DBNull.Value);
cmd.Parameters.AddWithValue("NAME01", dr_art_custsuply["Name"] ?? DBNull.Value);
cmd.ExecuteNonQuery();

Or if you want some help via "dapper" (since you mention the number of parameters being a problem):

connection.Execute(@"
INSERT INTO AGR3PL_CS (IDOC_NUM, PSEG_NUM, SEG_NUM, COMP01, GLNO01, NAME01)
VALUES (:Lidoc_num, :PSEG_NUM, :SEG_NUM, :Comp, :Gln, :Name)",
    new { Lidoc_num, PSEG_NUM, SEG_NUM,
        Comp = (int?)dr_art_custsuply["Id"],
        Gln = (string)dr_art_custsuply["GLN"],
        Name = (string)dr_art_custsuply["Name"] });

Hey Marc..the upper example with cmd.Parameter works like a charm..So if I want to make udate it's the same like the insert? — CrBruno, Jun 16 '14 at 07:03

score 3 · Answer 2 · edited May 23 '17 at 11:57

3

Simply do

Convert.ToString(dr_art_custsuply["GLN"])

If it has value, then will send that value. Else null as Convert.ToString() handles null.

Note: Don't do .ToString() as it will give exception when value is null.
Read this Difference between Convert.ToString() and .ToString()

edited May 23 '17 at 11:57

Community

1
1

answered Jun 16 '14 at 06:39

Nikhil Agrawal

47,018
22
121
208

Dmitry Bychenko · Answer 3 · 2014-06-16T06:57:29.513

Never insert values into SQL, use parameters/Bind variables instead:

using Oracle.DataAccess.Client; // <- In case of using Oracle

...

String myQuery = 
 @"insert into AGR3PL_CS(
     IDOC_NUM, 
     PSEG_NUM, 
     SEG_NUM, 
     COMP01, 
     GLNO01, 
     NAME01)
   values (
     :PRM_IDOC_NUM, -- <- Assuming that you use Oracle; @PRM_IDOC_NUM for MS SQL
     :PRM_PSEG_NUM, 
     :PRM_SEG_NUM, 
     :PRM_COMP01, 
     :PRM_GLNO01, 
     "PRM_NAME01)"; 

  ....

  using(OracleCommand cmd = new OracleCommand()) {
    ...
    cmd.CommandText = myQuery;

    // For MS SQL use
    // cmd.Parameters.AddWithValue("@PRM_IDOC_NUM", Lidoc_num); 
    cmd.Parameters.Add(":PRM_IDOC_NUM", Lidoc_num);
    cmd.Parameters.Add(":PRM_PSEG_NUM", PSEG_NUM);
    cmd.Parameters.Add(":PRM_SEG_NUM", SEG_NUM);
    cmd.Parameters.Add(":PRM_COMP01", dr_art_custsuply["Id"]);
    cmd.Parameters.Add(":PRM_GLNO01", dr_art_custsuply["GLN"]);
    cmd.Parameters.Add(":PRM_NAME01", dr_art_custsuply["Name"]);

    cmd.ExecuteNonQuery();
  }

Thanks..I use Oracle data base – CrBruno Jun 16 '14 at 06:50 — CrBruno, Jun 16 '14 at 06:50

Diana Nassar · Accepted Answer · 2014-06-17T10:53:08.300

Use inline ternary operators:

 gln = (!dr_art_custsuply.Table.Columns.Contains("GLN") || dr_art_custsuply["GLN"].ToString().Length <= 0 ?  string.Empty : dr_art_custsuply["GLN"].ToString());

So myQuery becomes:

myQuery = " INSERT INTO AGR3PL_CS (IDOC_NUM, PSEG_NUM, SEG_NUM, COMP01, GLNO01, NAME01) " +
          " VALUES (" + Lidoc_num + ", '" +
                        PSEG_NUM + "','" +
                        SEG_NUM + "','" +
                        dr_art_custsuply["Id"] + "','" +
                        (!dr_art_custsuply.Table.Columns.Contains("GLN") || dr_art_custsuply["GLN"].ToString().Length <= 0 ?  string.Empty : dr_art_custsuply["GLN"].ToString()) + "','" +
                        dr_art_custsuply["Name"] + "')";

EDIT: answering the question in your comment:

myQuery = " INSERT INTO AGR3PL_CS (IDOC_NUM, PSEG_NUM, SEG_NUM, COMP01, GLNO01, NAME01) " +
          " VALUES (" + Lidoc_num + ", '" +
                        PSEG_NUM + "','" +
                        SEG_NUM + "','" +
                        dr_art_custsuply["Id"] + "','" +
                        (!dr_art_custsuply.Table.Columns.Contains("GLN") || dr_art_custsuply["GLN"].ToString().Length <= 0 ? 0 : dr_art_custsuply["GLN"].ToString()) + "','" +
                        (!dr_art_custsuply.Table.Columns.Contains("Name") || dr_art_custsuply["Name"].ToString().Length <= 0 ? String.Empty : dr_art_custsuply["Name"].ToString()) + "')";

I tried this example and it's good, but what if some values have to be number, like I want to insert 0 for GLN and empty string for Name, how to write that? — CrBruno, Jun 17 '14 at 10:47
Hi Diana,a question?How to use inline ternary operators with this example,I don't want to use a variable during insert but I have some conversion in code... if (!dr_art_line.Table.Columns.Contains("WeightIssueUnit") || dr_art_line["WeightIssueUnit"].ToString().Length <= 0) { WeightIssueUnit = 0; } else { WeightIssueUnit = Convert.ToDecimal(dr_art_line["WeightIssueUnit"], CultureInfo.InvariantCulture); } — CrBruno, Jun 23 '14 at 11:41
`WeightIssueUnit = (!dr_art_line.Table.Columns.Contains("WeightIssueUnit") || dr_art_line["WeightIssueUnit"].ToString().Length <= 0) ? 0 : Convert.ToDecimal(dr_art_line["WeightIssueUnit"], CultureInfo.InvariantCulture);` :) — Diana Nassar, Jun 23 '14 at 12:39
Thanks...I'll try that.... :-) Oh and one more...also during my insert into the database I have this WeightIssueUnit.ToString().Replace(",", "."), can this also be merged like example above? :-) — CrBruno, Jun 30 '14 at 12:53

score 0 · Answer 5 · answered Jun 16 '14 at 06:45

You could do something like this (and use it for most types expected):

var customerId = (CheckNull(dr_art_custsuply["Id"], String.Empty)).ToString();


public static object CheckNull(object value, object defaultValue)
{
     return ((value == null || value == DBNull.Value) ? defaultValue : value);
}

Check if value exists or is null

5 Answers5