CODEWITHSUNDEEP
c#sqlwpflinq
Roxy'Pro
Roxy'Pro

Reputation: 4444

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

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%!

enter image description here

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

Upvotes: 1

Views: 99

Answers (3)

Abdulkarim Kanaan
Abdulkarim Kanaan

Reputation: 1763

Why don't you call FirstOrDefault()

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

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

Upvotes: 1

Oscar
Oscar

Reputation: 13960

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

Upvotes: 0

Cataklysim
Cataklysim

Reputation: 677

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

DBNull.Value

In your case: 

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

Upvotes: 0

Related Questions