Select NULL values from SQL Server table

Question

I am working with ASP.NET MVC 4 using C# and SQL Server

I am selecting a row of data from the following table

CREATE TABLE [dbo].[Mem_Basic] (
[Id]          INT           IDENTITY (1, 1) NOT NULL,
[Mem_NA]      VARCHAR (100) NOT NULL,
[Mem_Occ]     VARCHAR (200) NOT NULL,
[Mem_Role]    VARCHAR (200) NOT NULL,
[Mem_Email]   VARCHAR (50)  NULL,
[Mem_MPh]     VARCHAR (15)  NULL,
[Mem_DOB]     DATE          NULL,
[Mem_BGr]     NCHAR (10)    NULL,
[Mem_WAnn]    DATE          NULL,
[Mem_Spouse]  VARCHAR (75)  NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

using the following code

public MemberBasicData GetMemberProfile(int id)
{
        MemberBasicData mb = new MemberBasicData();
        using (SqlConnection con = new SqlConnection(Config.ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Mem_Basic WHERE Id="+id+"", con))
            {
                try
                {
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                   if(reader.Read()==true)
                    {
                        mb.Id = (int)reader["Id"];
                        mb.Mem_NA = (string)reader["Mem_NA"];
                        mb.Mem_Occ = (string)reader["Mem_Occ"];
                        mb.Mem_Role = (string)reader["Mem_Role"];
                        mb.Mem_Email = (string)reader["Mem_Email"];
                        mb.Mem_MPh = (string)reader["Mem_MPh"];
                        mb.Mem_DOB = (Convert.ToDateTime(reader["Mem_DOB"]));
                        mb.Mem_BGr = (string)reader["Mem_BGr"];
                        mb.Mem_WAnn = (Convert.ToDateTime(reader["Mem_WAnn"]));
                        mb.Mem_Spouse = (string)reader["Mem_Spouse"];
                   }
                }
                catch (Exception e) { throw e; }
                finally { if (con.State == System.Data.ConnectionState.Open) con.Close(); }
            } 
        }
        return mb;
    }

This shows the error

Unable to cast object of type 'System.DBNull' to type 'System.String'.

(Mem_Email, MPh.. etc sometimes contain a NULL value.. if the value is null I want return null). Anybody please help me.

Unrelated to your question (as you have an answer below) but your try, catch and finally are all redundant. Catching and throwing is pointless (and will truncate the stack trace in the way you have implemented it), and the finally is closing the connection even though your use of the using statement will handle that for you already. — Daniel Kelley
– Daniel Kelley, Commented Aug 30, 2013 at 8:21
Using a connector or ORM would help a lot here. Personally, when I hear "C#" and "SQL Server", I think Entity Framework, but there are plenty of alternatives. — anaximander
– anaximander, Commented Aug 30, 2013 at 9:03

King King · Accepted Answer · 2013-08-30 08:18:43Z

3

Just make some short if, you should do the same for all the other variables:

 mb.Mem_Email = reader["Mem_Email"] == System.DBNull.Value ? null : (string) reader["Mem_Email"];

answered Aug 30, 2013 at 8:18

King King

63.5k16 gold badges118 silver badges140 bronze badges

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

Comments

Marc Gravell · Accepted Answer · 2013-08-30 08:21:14Z

3

You could save yourself a serious amount of pain here with a tool like dapper (http://www.nuget.org/packages/Dapper):

public MemberBasicData GetMemberProfile(int id)
{
    using (var con = new SqlConnection(Config.ConnectionString))
    {
        return con.Query<MemberBasicData>(
            "SELECT * FROM Mem_Basic WHERE Id=@id",
            new { id } // full parameterization, done the easy way
        ).FirstOrDefault();
    }
}

things this does:

does correct parameterization (for both performance and safety), but without any inconvenience
does all the materialization, handling nulls (both in parameters and columns) for you
is insanely optimized (basically, it is measurably the same speed as writing all that code yourself, except fewer things to get wrong)

answered Aug 30, 2013 at 8:21

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

2 Comments

neel Over a year ago

how can i return a model by using this

Marc Gravell Over a year ago

@Parvathiiiii the code above shows how you return a model by using this. It does precisely that. Basically, the Query<T> method materializes each row found into an instance of T via IEnumerable<T> (i.e. per row); from there we can use LINQ if we want, so the FirstOrDefault gets us from IEnumerable<T> to T.

Vasea · Accepted Answer · 2013-08-30 08:32:57Z

2

Alternatively to King King's answer you can write code like this:

mb.Mem_Email = reader["Mem_Email"] as string;

For value types, if the column allows nulls, it's a good practice to map them to nullable value types in C# so that this code reader["Mem_DOB"] as DateTime? works

edited Aug 30, 2013 at 8:32

answered Aug 30, 2013 at 8:22

Vasea

5,3512 gold badges29 silver badges30 bronze badges

Comments

MikkaRin · Accepted Answer · 2013-08-30 08:22:25Z

0

Change for all columns, that might be NULL from this

mb.Mem_NA = (string)reader["Mem_NA"];

to that

mb.Mem_NA = reader["Mem_NA"].ToString();

answered Aug 30, 2013 at 8:22

MikkaRin

3,08422 silver badges35 bronze badges

Comments

Amen Ayach · Accepted Answer · 2013-08-30 08:22:58Z

0

Treat the nullable fields:

mb.Mem_Email = System.DBNull.Value.Equals(reader["Mem_Email"])?"":
                                      (string)reader["Mem_Email"];

Do the same for: mb.Mem_MPh, mb.Mem_BGr and mb.Mem_Spouse.

answered Aug 30, 2013 at 8:22

Amen Ayach

4,3481 gold badge26 silver badges23 bronze badges

Comments

ChicagoJohnnyVegas · Accepted Answer · 2019-01-15 23:25:41Z

I don't mean to sound like a SQL bigot (which of course means I DO mean to sound like a SQL bigot), but if you followed SQL best practices and used a column list instead of SELECT * you could resolve this problem by using COALESCE on the nullable columns thus:

SELECT
  [Id],
  [Mem_NA],
  [Mem_Occ],
  [Mem_Role],
  COALESCE( [Mem_Email], '' ) AS [Mem_Email],
  COALESCE( [Mem_MPh], '' ) AS [Mem_MPh],
  COALESCE( [Mem_DOB], CAST( '1753-1-1' AS DATE ) ) AS [Mem_DOB],
  COALESCE( [Mem_BGr, '' ) AS [Mem_BGr],
  COALESCE( [Mem_WAnn], CAST( '1753-1-1' AS DATE ) ) AS [Mem_WAnn],
  COALESCE( [Mem_Spouse], '' ) AS [Mem_Spouse]
FROM 
  [dbo].[Mem_Basic];

Your c# code can now dependably process the result set without having to account for outliers (the exception being the dates; you should probably check for whatever default you use in the COALESCE for those (I used the minimum allowable value for a SQL Date variable in the above example), and handle them appropriately.

Additionally, you can get rid of the finally block in your c# code. You wrapped the connection in a "using" block; it will automatically close the connection when you go out of scope (that is the purpose of the "using" block).

Collectives™ on Stack Overflow

Select NULL values from SQL Server table

6 Answers 6

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related