locked
How to check this without using loop in c# RRS feed

  • Question

  • User269846090 posted

    var result ="select col1,col2,col3,col4 from tbl";

    I need to check any one of the above column have value or all r empty.

    How to check that without using foreach and any other loop ?

    Friday, September 18, 2015 3:53 AM

All replies

  • User-434868552 posted

    @ssjganesh

    SELECT col1, col2, col3, col4
    FROM tbl
    WHERE co11 IS NOT NULL
    OR col2 IS NOT NULL
    OR col3 IS NOT NULL
    OR col4 IS NOT NULL
    OR
    (
    (col1 IS NULL) AND
    (col2 IS NULL) AND
    (col3 IS NULL) AND
    (col4 IS NULL)
    );

    which version of T-SQL are you using?

    EDIT:

    Ganesh, it's important for you to tell your peers here at forums.asp.net the data type of each column!

    if you intended to evaluate       null or empty string:

    SELECT col1, col2, col3, col4
    FROM tbl
    WHERE (co11 IS NOT NULL AND col1 <> '')
    OR (col2 IS NOT NULL AND col2 <> '')
    OR (col3 IS NOT NULL AND col3 <> '')
    OR (col4 IS NOT NULL AND col4 <> '')
    OR
    (
    (col1 IS NULL OR col1 = '') AND
    (col2 IS NULL OR col2 = '') AND
    (col3 IS NULL OR col3 = '') AND
    (col4 IS NULL OR col4 = '')
    );

    The above assumes your are using Microsoft SQL Server; see:
    http://stackoverflow.com/questions/6457710/compatible-sql-to-test-for-not-null-and-not-empty-strings 

    END EDIT.

    EDIT # 2:   in the code above    (coln IS NOT NULL OR coln <> ""),  OR was changed to AND: 

    WHERE (co11 IS NOT NULL AND col1 <> '')
    OR (col2 IS NOT NULL AND col2 <> '')
    OR (col3 IS NOT NULL AND col3 <> '')
    OR (col4 IS NOT NULL AND col4 <> '')

    Ganesh ... the above code is untested ~~ you need to test thoroughly in your environment.

    END EDIT # 2.

    Friday, September 18, 2015 4:25 AM
  • User269846090 posted

    Sorry... I need to do that in C# not in sql.

    after storing that select query result in to a variable I need to check that.

    Friday, September 18, 2015 5:00 AM
  • User1083584480 posted

    Which variable you will get result.

    Will it be a datatable, List of class object or else

    Please provide more code

    Friday, September 18, 2015 5:42 AM
  • User269846090 posted

    Ya It is in a datatable.

    Friday, September 18, 2015 6:10 AM
  • User1083584480 posted

    then use LINQ

    DataTable dt=result set;

    var result=dt.Rows.Where(r=>!string.isnullorEmpty(convert.tostring( r["col1"]))
    or !string.isnullorEmpty(convert.tostring( r["col2"])) or !string.isnullorEmpty(convert.tostring( r["col3"]))).tolist();

    if(result.Any())
    {
    it means any row has column value;

    }

    Please correct syntatical errors

    Friday, September 18, 2015 7:15 AM
  • User269846090 posted

    yeah its good, but the column names are generic, we can't specify those column names like u mentioned r["col1"].

    any other way?

    Friday, September 18, 2015 7:40 AM
  • User-434868552 posted

    ssjGanesh

    Sorry... I need to do that in C# not in sql.

    after storing that select query result in to a variable I need to check that.

    WHY?

    T-SQL will be faster because you will not need to move data from your database into your memory space; T-SQL will handle the filtering for you.

    EDIT:

    yeah its good, but the column names are generic, we can't specify those column names like u mentioned r["col1"].

    Gansesh, you really need to add more clarity to your question!

    Please explain what, if anything, is wrong with this?:

    var result =
    @"SELECT col1, col2, col3, col4
    FROM tbl
    WHERE co11 IS NOT NULL
    OR col2 IS NOT NULL
    OR col3 IS NOT NULL
    OR col4 IS NOT NULL
    OR
    (
    (col1 IS NULL) AND
    (col2 IS NULL) AND
    (col3 IS NULL) AND
    (col4 IS NULL)
    );";

    Ganesh, from your O.P., you appear to be building your SELECT as a c# literal.

    END EDIT.

    Friday, September 18, 2015 8:21 AM
  • User-821857111 posted

    How to check that without using foreach and any other loop ?
    That's a peculiar stipulation. Please note that this forum is intended for people who have actual issues involving applying aspects of the C# language to their ASP.NET application. It is not intended for general questions about C#, or for interview questions. Using a foreach or other loop is the most practical way to solve your issue.

    Friday, September 18, 2015 8:36 AM