How to detect when there is no value from database SQL LINQ C#

Question

I'm having trouble with detecting when my value in C# code is null or not, (I'm using LINQ to call stored procedures from SQL SERVER), I mean I have issues when I want to determine did SQL returned something to me after I called SP which expecting one parameter (ArticleID) or acctualy SQL did not return anything ( null - row does not exist ).

In fact, I want to check does any of my articles has some "Sub articles", and I'm passing ArticleID from C# code to stored procedure.

So In case if article is there, I want to do something with it, in case article with passed ArticleID does not exist I want to do something also, so as I said I need to detect does my article with given ArticleID exist in my sql database.

Here is some of my code:

private void btnCheckForArticle(object sender, RoutedEventArgs e)
{
        try
        {  
        if (gridArticles.SelectedItems.Count > 0) {
            Article a = (Article)gridArticles.SelectedItem;



                if (a.ArticleID != null)
                {

                    var existingArticle = DataServices.POS.proc_GetSubArticleByArticleID(a.ArticleID);
                    if (existingArticle != null)
                    {
                       //DO SOMETHING
                        return;
                    }
              }
        }
    }
}

My stored procedure :

ALTER PROCEDURE [dbo].[proc_GetSubArticleByArticleID]
(
    @ArticleID int
)
AS
BEGIN
Select *
From SubArticles as S
Where S.ArticleID = @ArticleID
END

It's interesting that I do not know how to detect in c# code is my value null or not, because obliviosuly even if there is no row in sql database still I'm not getting null, and because of that code below existingArticle != null will allways execute..

Now I will post what is happening with article which for sure does not have any subarticles, I mean, where result should be null 100%!

ID that I passed to procedure is 2351, so I executed sp which should return value directly on sql and I replaced @ArticleID with 2351 and ofcourse It did not return me any results, but how can I notice that in my C# code..

because existingArticle will never be null cuz somehow it allways has some value and code below my if (existingArticle != null) will allways execute. what I really dont want :/ ...

Thanks guys! Cheers

what is the return type of proc_GetSubArticleByArticleID method? if it is IEnumerable<Article>, call FirstOrDefault() and compare with null — ASh
– ASh, Commented Feb 9, 2017 at 14:42
@Cataklysim I will check for it in a few hours, Im driving for few hrs allready so I can't code now. But to all thanks a lot! This is great community! — Roxy'Pro
– Roxy'Pro, Commented Feb 9, 2017 at 22:39

Abdulkarim Kanaan · Accepted Answer · 2017-02-09 15:03:57Z

1

Why don't you call FirstOrDefault()

var existingArticle = DataServices.POS.proc_GetSubArticleByArticleID(a.ArticleID).FirstOrDefault();

if (existingArticle != null)
{
    // 
    return;
}

answered Feb 9, 2017 at 15:03

Abdulkarim Kanaan

1,7634 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cataklysim Over a year ago

If you get called out as answer, may you put my answer also to yours as edit? Cause your way "transforms" it into a casual Null value and my answer shows how to filter a database Null value. Both should be interesting for people with the same question.

Cataklysim · Accepted Answer · 2017-02-09 14:43:15Z

0

There is a special Datatype for checking null values from a database.

DBNull.Value

In your case:

if (existingArticle != DBNull.Value)
{
   //DO SOMETHING
   return;
}

answered Feb 9, 2017 at 14:43

Cataklysim

6871 gold badge8 silver badges21 bronze badges

3 Comments

Rahul Over a year ago

What if the column is a non-nullable column?

Cataklysim Over a year ago

@Rahul if so, it will pass this statement. But for those you must ask different. In his question it was just asked how to see if a column is null or not. And a simple if(columnA != null) won't work, because the Database NULL Value is different then the .Net NULL

Roxy'Pro Over a year ago

@Cataklysim .FirstOrDefault() fixed it

Oscar · Accepted Answer · 2017-02-09 14:45:51Z

0

First, modify your SP to have after BEGIN:

SET NOCOUNT ON

This is to avoid interference of "xx rows affected" message.

Seccond, dont do SELECT *, instead use

SELECT 1 From SubArticles as S
Where S.ArticleID = @ArticleID

Last, check for System.DBNull.Value instead of NULL, as this is what will be returned from DDBB. This is because in .Net value types as Boolean can't be null

answered Feb 9, 2017 at 14:45

Oscar

14.1k9 gold badges53 silver badges84 bronze badges

Collectives™ on Stack Overflow

How to detect when there is no value from database SQL LINQ C#

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related