should I use isDbNull? How to use it in this particular case?

Question

I know Null values can't be turned into string values so I looked all over the web and did find some answers like using IsDBNull() but I can't seem to get it working. I'm new at this so please bear with me. I simply need the nullable field to be populated in the proper TextBox as "" when it's Null. If you could give me a hand with the proper syntax it would be great. the field that may be Null (or not) is projects_project_number, I went back to my original code to show what I started with. Here it is:

ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["shopmanagerConnectionString1"];
MySqlConnection con = new MySqlConnection(conSettings.ToString());
string Query = "select * from shopmanager.quotes where idquotes = @search_quote_number;";

MySqlCommand cmdDataBase = new MySqlCommand(Query, con);
MySqlDataReader myReader;
try
{
    con.Open();

    cmdDataBase.Parameters.AddWithValue("@search_quote_number", search_quote_number.Text);
    myReader = cmdDataBase.ExecuteReader();

    while (myReader.Read())
    {
        string sClient_number = myReader.GetString("client_info_client_number");
        string sId = myReader.GetInt16("idquotes").ToString();
        string sClientName = myReader.GetString("client_name");
        string spredicted_start_date = myReader.GetString("predicted_start_date");
        string sdate_required = myReader.GetString("requested_date");
        string sdate_predicted = myReader.GetString("delivery_expected_date");
        string sdate_received = myReader.GetString("date_received");
        string squote_amount = myReader.GetString("quote_amount");
        string sproject_number = myReader.GetString("projects_project_number");

        quote_id.Text = sId;
        client_name.Text = sClientName;
        predicted_start_date.Text = spredicted_start_date;
        date_required.Text = sdate_required;
        date_predicted.Text = sdate_predicted;
        date_received.Text = sdate_received;
        quote_amount.Text = squote_amount;
        project_number.Text = sproject_number;

        temp_client_id.client_id = sClient_number;
        search_quote_number.Text = "";
    }
    cmdDataBase.Parameters.Clear();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
con.Close();

I cant see where DbNull is used at all. Tip: you dont have to store data into a string var before posting it to a control. — Ňɏssa Pøngjǣrdenlarp, Sep 05 '18 at 00:06
How would I go about doing that? Could you share an example? Projet_project_number is the field that can be null — Christian Verner, Sep 05 '18 at 00:12
`if (!myReader.IsDbNull(3)) txtBoxForTheName.Text = myReader.GetString("Name");` Where `3` is the index for that field. Saving dates as strings as appears to be the case is a bad idea — Ňɏssa Pøngjǣrdenlarp, Sep 05 '18 at 00:14
I'd recommend looking into micro ORMs (like PetaPoco or Dapper) to avoid you having to worry about this. — mjwills, Sep 05 '18 at 02:16

Daniel Loudon · Accepted Answer · 2018-09-05T00:41:34.677

To use the DBNull follow the guidance on MSDN about Is.DBNull()

There is multiple ways to use the DBNull but using what you have gave in your question, the best use would be:

string sproject_number = string.Empty;

if (!Convert.IsDBNull(myReader.GetString("projects_project_number"))) //check if value is not null
        sproject_number = myReader.GetString("projects_project_number"); 

project_number.Text = sproject_number;

You can also use the built in function for the SqlDataReader .IsDBNull:

int columnIndex = myReader.GetOrdinal("projects_project_number"); //get column index
string sproject_number = string.Empty;

if (!myReader.IsDBNull(columnIndex)) //check if value is not null
        sproject_number = myReader[columnIndex].ToString(); 

project_number.Text = sproject_number;

and for completeness:

int columnIndex = myReader.GetOrdinal();
string sproject_number = string.Empty;

if (DBNull.Value.Equals(myReader.GetString("projects_project_number"))) //check if value is not null
        sproject_number = myReader.GetString("projects_project_number");

project_number.Text = sproject_number;

Evaluating on a comment on original post about assigning to the textboxes directly and tidying everything up (and adding it into a using block is good practice):

ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["shopmanagerConnectionString1"]
string Query = "select * from shopmanager.quotes where idquotes = @search_quote_number;";
using(MySqlConnection con = new MySqlConnection(conSettings.ToString()))
{
  using(MySqlCommand cmdDataBase = new MySqlCommand(Query, con))
  {
    cmdDataBase.Parameters.AddWithValue("@search_quote_number", search_quote_number.Text.Trim());
    con.Open();
    var myReader = cmdDataBase.ExecuteReader();

    if(myReader.Read())
    {
      temp_client_id.client_id = myReader.GetString("client_info_client_number");
      quote_id.Text = myReader.GetInt16("idquotes").ToString();
      client_name.Text = myReader.GetString("client_name");
      predicted_start_date.Text = myReader.GetString("predicted_start_date");
      date_required.Text = sdate_required = myReader.GetString("requested_date");
      date_predicted.Tex = myReader.GetString("delivery_expected_date");
      date_received.Text = myReader.GetString("date_received");
      quote_amount.Text = myReader.GetString("quote_amount");
      project_number.Text = DBNull.Value.Equals(myReader.GetString("projects_project_number")) ? "" : myReader.GetString("projects_project_number");

      search_quote_number.Text = "";
    }
  }
}

Yeah there is more than one way to skin a cat, I am just going with what he has gave me because we are using `SqlDataReader.GetString(string)` and the use of `SqlDataReader.IsDBNull(int)` would require some more changes to OP's code — Daniel Loudon, Sep 05 '18 at 00:18

score 0 · Answer 2 · answered Sep 05 '18 at 00:16

0

You can do this:

string sproject_number = DBNull.Value.Equals(myReader.GetString("projects_project_number")) ? "" : myReader.GetString("projects_project_number");

Or you can use an extension method like in this SO Post:

answered Sep 05 '18 at 00:16

Gauravsa

6,330
2
21
30

should I use isDbNull? How to use it in this particular case?

2 Answers2