Best way to deal with null values from SQL database?

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 1453 Views - Last Post: 09 August 2016 - 08:10 AM Rate Topic: -----

#1 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Best way to deal with null values from SQL database?

Posted 05 August 2016 - 10:12 AM

Hi Guys

I am looking for some advice on the best way to deal with null values from a SQL database.

In this particular instance it is with an int.

Obviously ints cannot be null in C# so currently what I am doing is if the value in the database is null I am storing it as -1 in my program.

Then when i am inserting into the database I am doing a NULLIF on if it is -1.

Obviously this is ok until the user doesn't realise that -1 means null.

Any advice please?

Thank You

Daniel

Is This A Good Question/Topic? 0
  • +

Replies To: Best way to deal with null values from SQL database?

#2 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Best way to deal with null values from SQL database?

Posted 05 August 2016 - 10:51 AM

Quote

Obviously ints cannot be null in C# so currently what I am doing is if the value in the database is null I am storing it as -1 in my program.


Well, every non-nullable (i.e. structs) can be "wrapped" in a nullable type, called Nullable<T>. There's even a language shortcut: add a ? to the typename.

int? nullableInt = null;
nullableInt = 5;


So just use nullable types.
Was This Post Helpful? 0
  • +
  • -

#3 general656   User is offline

  • D.I.C Regular

Reputation: 12
  • View blog
  • Posts: 288
  • Joined: 25-March 15

Re: Best way to deal with null values from SQL database?

Posted 05 August 2016 - 03:29 PM

  • Definition of nullable integer :
    int? x = null;
    


  • The "?" operator version for a null pointer is :
    int? x;
    x = int.Parse(Console.ReadLine());
    Console.WriteLine(x ?? "x is Null");
    

    This basically is :
    int? x;
    x = int.Parse(Console.ReadLine());
    Console.WriteLine(x == Null ? "x is Null" : x);
    


  • In case an object is a nullable type :
    int ?x;
    x = int.Parse(Console.ReadLine());
    Console.WriteLine(x?.ToString() ?? "x is Null");
    

Was This Post Helpful? 0
  • +
  • -

#4 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Best way to deal with null values from SQL database?

Posted 06 August 2016 - 08:56 AM

Note that your last example uses a C# 6 feature, which requires you to compile with the latest C# compiler.
Was This Post Helpful? 0
  • +
  • -

#5 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Re: Best way to deal with null values from SQL database?

Posted 06 August 2016 - 09:12 AM

Thanks.

I didn't realise there was a nullable int type.

Am I right in guessing that it is best to not store strings as null?
Was This Post Helpful? 0
  • +
  • -

#6 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7189
  • View blog
  • Posts: 24,365
  • Joined: 05-May 12

Re: Best way to deal with null values from SQL database?

Posted 06 August 2016 - 09:15 AM

View Postgeneral656, on 05 August 2016 - 06:29 PM, said:

int? x;
x = int.Parse(Console.ReadLine());
Console.WriteLine(x ?? "x is Null");

:


int.Parse() will never return null.
Was This Post Helpful? 0
  • +
  • -

#7 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Best way to deal with null values from SQL database?

Posted 06 August 2016 - 11:04 AM

Quote

Am I right in guessing that it is best to not store strings as null?


I'm not sure what you're asking. Strings are a nullable type, and there's a material difference between an empty string and a null string (implying that empty is data, and null is the lack of any data).
Was This Post Helpful? 0
  • +
  • -

#8 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Re: Best way to deal with null values from SQL database?

Posted 06 August 2016 - 11:08 AM

Oh.

Well I was thinking that they can't be null because when I get the data from the database of it is null in the database then it will just show as blank in my program.

How can I make it stay as null?
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6829
  • View blog
  • Posts: 28,315
  • Joined: 12-December 12

Re: Best way to deal with null values from SQL database?

Posted 06 August 2016 - 11:16 AM

You haven't posted any code so I'm curious why you are having to take a manual approach to handling nulls? Even WinForms is able to account for them; e.g. DataGridViewCellStyle.NullValue Property.

If you have a data model (a DAL) then it is understandable that your model might need to accommodate nulls.
Was This Post Helpful? 0
  • +
  • -

#10 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Re: Best way to deal with null values from SQL database?

Posted 06 August 2016 - 11:30 AM

What i am trying to do is ensure that if anything in the database is null then it stays as null in my application and gets inserted back into the database as null.

Here is some of my code:
Get data from database:
private void GetTemplatePageLayoutData_Live()
        {
            if (M_GlobalVariables.GetConnection == null)
            {

            }
            else
            {
                try
                {
                    //Create the SQL Connection
                    SqlConnection cn = new SqlConnection(Models.M_GlobalVariables.Get_SelectedCompany_SQLConnection);

                    //Open the SQL connection.
                    cn.Open();

                    //SELECT Header_RectString, ISNULL(Header_BrowserWidth, -1) 'Header_BrowserWidth', Body_RectString, Body_BrowserWidth, Footer_RectString, ISNULL(Footer_BrowserWidth, -1) 'Footer_BrowserWidth', Watermark_RectString, ISNULL(Watermark_BrowserWidth, -1) 'Watermark_BrowserWidth', Watermark_HTML, Landscape, IsChainable, Deleted, MediaBox, PriorityOrder, FloorplanSystemType FROM TemplatePageLayout WHERE TemplatePageLayoutID = @TemplatePageLayoutID
                    using (SqlCommand cmd = new SqlCommand("SELECT Header_RectString, Header_BrowserWidth, Body_RectString, Body_BrowserWidth, Footer_RectString, Footer_BrowserWidth, Watermark_RectString, Watermark_BrowserWidth, Watermark_HTML, Landscape, IsChainable, Deleted, MediaBox, PriorityOrder, FloorplanSystemType FROM TemplatePageLayout WHERE TemplatePageLayoutID = @TemplatePageLayoutID", cn))
                    {
                        cmd.Parameters.AddWithValue("@TemplatePageLayoutID", TemplatePageLayout_ID);

                        SqlDataReader dr = cmd.ExecuteReader();

                        if (dr.HasRows)
                        {
                            while (dr.Read())
                            {
                                TemplatePageLayout_Header_RectString = dr["Header_RectString"].ToString();
                                TemplatePageLayout_Header_BrowserWidth = (dr["Header_BrowserWidth"] as int?) ?? null;
                                TemplatePageLayout_Body_RectString = dr["Body_RectString"].ToString();
                                TemplatePageLayout_Body_BrowserWidth = (dr["Body_BrowserWidth"] as int?) ?? null;
                                TemplatePageLayout_Footer_RectString = dr["Footer_RectString"].ToString();
                                TemplatePageLayout_Footer_BrowserWidth = (dr["Footer_BrowserWidth"] as int?) ?? null;
                                TemplatePageLayout_Watermark_RectString = dr["Watermark_RectString"].ToString();
                                TemplatePageLayout_Watermark_BrowserWidth = (dr["Watermark_BrowserWidth"] as int?) ?? null;
                                TemplatePageLayout_Watermark_HTML = dr["Watermark_HTML"].ToString();
                                TemplatePageLayout_Landscape = (dr["Landscape"] as bool?) ?? null;
                                TemplatePageLayout_IsChainable = (dr["IsChainable"] as bool?) ?? null;
                                TemplatePageLayout_Deleted = (dr["Deleted"] as DateTime?) ?? null;
                                TemplatePageLayout_MediaBox = dr["MediaBox"].ToString();
                                TemplatePageLayout_PriorityOrder = (dr["PriorityOrder"] as int?) ?? null;
                                TemplatePageLayout_FloorplanSystemType = (dr["FloorplanSystemType"] as int?) ?? null;
                            }
                        }
                        else
                        {
                            System.Windows.MessageBox.Show("There is no data.");
                        }
                        dr.Close();
                    }
                    //Close the SQL connection
                    cn.Close();
                }
                catch (Exception ex)
                {
                    System.Windows.MessageBox.Show("Error: " + Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace);
                }
            }
        }

Insert into database:
public bool InsertNewTemplatePageLayoutVersion(int TemplatePageLayoutID, string SearchString, string HeaderRectString, int? HeaderBrowserWidth, string HeaderHTML, string BodyRectString, int? BodyBrowserWidth, string FooterRectString, int? FooterBrowserWidth, string FooterHTMLOverride, string WatermarkRectString, int? WatermarkBrowserWidth, string WatermarkHTML, bool? Landscape, bool? IsChainable, DateTime? Deleted, string MediaBox, int? PriorityOrder, int? FloorplanSystemType)
        {
            bool Successful = false;

            try
            {
                //Create the SQL Connection
                SqlConnection cn = new SqlConnection(Models.M_GlobalVariables.Get_SelectedCompany_SQLConnection);

                //Open the SQL connection.
                cn.Open();

                using (SqlCommand cmd = new SqlCommand("UPDATE TemplatePageLayout SET SearchString = NULLIF(@SearchString, 'NULL'), Header_RectString = NULLIF(@HeaderRectString, 'NULL'), Header_BrowserWidth = NULLIF(@HeaderBrowserWidth, -1), Header_HTML = NULLIF(@HeaderHTML, 'NULL'), Body_RectString = @BodyRectString, Body_BrowserWidth = NULLIF(CAST(@BodyBrowserWidth as varchar(max)), 0), Footer_RectString = NULLIF(@FooterRectString, 'NULL'), Footer_BrowserWidth = NULLIF(@FooterBrowserWidth, -1), Footer_HTML_Override = NULLIF(@FooterHTMLOverride, 'NULL'), Watermark_RectString = NULLIF(@WatermarkRectString, 'NULL'), Watermark_BrowserWidth = NULLIF(@WatermarkBrowserWidth, -1), Watermark_HTML = NULLIF(@WatermarkHTML, 'NULL'), Landscape = @Landscape, IsChainable = @IsChainable, Deleted = NULLIF(@Deleted, 'NULL'), MediaBox = @MediaBox, PriorityOrder = @PriorityOrder, FloorplanSystemType = NULLIF(@FloorplanSystemType, 'NULL') WHERE TemplatePageLayoutID = @TemplatePageLayoutID", cn))
                {

                    cmd.Parameters.AddWithValue("@TemplatePageLayoutID", TemplatePageLayoutID);
                    cmd.Parameters.AddWithValue("@SearchString", SearchString);
                    cmd.Parameters.AddWithValue("@HeaderRectString", HeaderRectString);
                    cmd.Parameters.AddWithValue("@HeaderBrowserWidth", HeaderBrowserWidth);
                    cmd.Parameters.AddWithValue("@HeaderHTML", HeaderHTML);
                    cmd.Parameters.AddWithValue("@BodyRectString", BodyRectString);
                    cmd.Parameters.AddWithValue("@BodyBrowserWidth", BodyBrowserWidth);
                    cmd.Parameters.AddWithValue("@FooterRectString", FooterRectString);
                    cmd.Parameters.AddWithValue("@FooterBrowserWidth", FooterBrowserWidth);
                    cmd.Parameters.AddWithValue("@FooterHTMLOverride", FooterHTMLOverride);
                    cmd.Parameters.AddWithValue("@WatermarkRectString", WatermarkRectString);
                    cmd.Parameters.AddWithValue("@WatermarkBrowserWidth", WatermarkBrowserWidth);
                    cmd.Parameters.AddWithValue("@WatermarkHTML", WatermarkHTML);
                    cmd.Parameters.AddWithValue("@Landscape", ConvertBoolToIntForInsert(Landscape));
                    cmd.Parameters.AddWithValue("@IsChainable", ConvertBoolToIntForInsert(IsChainable));
                    cmd.Parameters.AddWithValue("@Deleted", Deleted);
                    cmd.Parameters.AddWithValue("@MediaBox", MediaBox);
                    cmd.Parameters.AddWithValue("@PriorityOrder", PriorityOrder);
                    cmd.Parameters.AddWithValue("@FloorplanSystemType", FloorplanSystemType);

                    foreach (SqlParameter parameter in cmd.Parameters)
                    {
                        if ((parameter.Value == null) || (parameter.Value == ""))
                        {
                            parameter.Value = DBNull.Value;
                        }
                    }

                    cmd.ExecuteNonQuery();
                }

                //Close the SQL connection
                cn.Close();

                Successful = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace);
            }

            return Successful;
        }

Was This Post Helpful? 0
  • +
  • -

#11 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6829
  • View blog
  • Posts: 28,315
  • Joined: 12-December 12

Re: Best way to deal with null values from SQL database?

Posted 07 August 2016 - 12:39 AM

That's a lot of work (particularly for a single row). What type of application is this and what object is a TemplatePageLayout?

You should have a data model, then you would bind controls to properties of your model, providing a data context. This way, as well as pursuing separation of concerns, you would abstract away the SQL and a single Update call would reconcile changes back to the database.

As I mentioned, WinForms and most application types have a NullValue (or similarly named) property for controls so you shouldn't have to inject, and later remove, token values.

NullValue:
"Gets or sets the DataGridView cell display value corresponding to a cell value of DBNull.Value or null."



We do see patterns like yours - using a DataReader and converting nulls - but this is typically for a one-time only, read-only, conversion of a set of data for output to another system:

Quote

You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database.

link

Essentially, you are taking a read-only feature and jumping through lots of hoops to attempt to make it updateable.
Was This Post Helpful? 0
  • +
  • -

#12 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Re: Best way to deal with null values from SQL database?

Posted 07 August 2016 - 02:13 AM

This is a C# W.P.F application and it's for my place of work. A templatepagelayout is a table in a database and this is getting the data from the database for whatever row the user wants letting the user modify it and then updating the database to reflect the changes the user has made.

I have the whole 'Model - View - ViewModel' set up in my application and then I have most of my SQL Queries in separate classes.

Is what I'm doing wrong?
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6829
  • View blog
  • Posts: 28,315
  • Joined: 12-December 12

Re: Best way to deal with null values from SQL database?

Posted 07 August 2016 - 04:07 AM

Are you copying data between two SQL Server databases? Other options are discussed here.

[Another option that occurs to me is to write the rows to a holding table in the first database and then bulk copy this table-data regularly to the other database.]

But you aren't just copying, you are allowing modification of the row. I wonder why, as the data in the second version is no longer a copy of the data in the first version? Or are you also writing the changes back to the first database?

I suppose it is not unreasonable to take this manual approach for a single row, although, if between two SQL Server databases, then I still doubt that you should need to do all this null juggling.

Still it's your boat ;) :boat:
Was This Post Helpful? 0
  • +
  • -

#14 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Re: Best way to deal with null values from SQL database?

Posted 07 August 2016 - 04:18 AM

The idea is that the second database is storing a backup of what the first database was like before the user changed whatever they need to change.
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6829
  • View blog
  • Posts: 28,315
  • Joined: 12-December 12

Re: Best way to deal with null values from SQL database?

Posted 07 August 2016 - 04:46 AM

Mm I'm opinionated enough to add further comments but my experience of tracking data changes is limited so feel free to ignore this post ;)

It seems inefficient to me to immediately write to a second database. I would probably consider, as I mentioned, copying changes to table(s) within the current database and periodically copying these table(s) data to the backup database. Triggers are typically used in this process.

You might read this:

Track Data Changes (SQL Server)

Quote

The ability to query for data that has changed in a database is an important requirement for some applications to be efficient. Typically, to determine data changes, application developers must implement a custom tracking method in their applications by using a combination of triggers, timestamp columns, and additional tables. Creating these applications usually involves a lot of work to implement, leads to schema updates, and often carries a high performance overhead.

Using change data capture or change tracking in applications to track changes in a database, instead of developing a custom solution, has the following benefits:

etc..

[I don't know why it says "to be efficient..", that doesn't seem apropos.]

Is the backup effectively a backup of the entire database? It'll be bigger, though, because it records all changes to rows? I suspect it is more typical to record changed values, and perhaps to marry these with the main database to obtain snapshops.

You're probably better off waiting for another member with more direct experience (and knowledge) to offer, rather than trusting to my speculations ;)
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2