none
c# SQLDataReader : replace empty column field /reader data with text RRS feed

  • Question

  • Hello,

    I am reading data from an SQL command, and saving this output into a file.

    What I would like to do is , if the column field is empty, to replace with with "no data"

    The data I get back is in this format

    

    and the code I am currently using is

    using (SqlCommand cmd = new SqlCommand("select [guest_name],[email]from MyTableName", connection))
    {
      using (SqlDataReader reader = cmd.ExecuteReader())
      using (StreamWriter writer = new StreamWriter(output1))
      {
       while (reader.Read())
        {
         writer.WriteLine(reader[0].ToString() +","+ reader[1].ToString());
         }
       }
       SqlCommand dropTable = new SqlCommand("drop table reviewProTemp", connection);
                                        dropTable.ExecuteNonQuery();
    }
    connection.Close();

    what I am having trouble figuring out is (please excuse the pseudo code) , and was wondering if someone could help ?

       if (reader[1] = NULL || reader[1] == "")

       {

        reader[1] = "no data"

       }

    But, I know this wont work but hopefully you get the idea as to what I am trying to achieve

    Thanks 🙂

    Saturday, February 15, 2020 1:30 PM

Answers

  • You cannot change the data that is stored inside the reader, but you can change what you read before writing it out to the destination:

    string myVariable = reader[1] as string;
    if (string.IsNullOrEmpty(myVariable))
    {
        myVariable = "no data";
    }
    
    writer.WriteLine(reader[0].ToString() +","+ myVariable);
        

    Note that I used "as string" instead of ".ToString()". If the field is NULL in the database, calling .ToString() will return "System.DBNull", which is probably not what you want.



    Saturday, February 15, 2020 1:47 PM
    Moderator

All replies

  • You cannot change the data that is stored inside the reader, but you can change what you read before writing it out to the destination:

    string myVariable = reader[1] as string;
    if (string.IsNullOrEmpty(myVariable))
    {
        myVariable = "no data";
    }
    
    writer.WriteLine(reader[0].ToString() +","+ myVariable);
        

    Note that I used "as string" instead of ".ToString()". If the field is NULL in the database, calling .ToString() will return "System.DBNull", which is probably not what you want.



    Saturday, February 15, 2020 1:47 PM
    Moderator
  • Hello,

    The following example the FirstName column is nullable and in one work FirstName is null. Using the following extension method there is a check for DbNull, if the value is null the parameter defaultValue is used, otherwise the non-null value is used.

    Ignore the : SqlServerConnection and the new constructor as they are to setup the connection via the NuGet package in my signature.

    Also note I don't use a stream but using a stream works too, instead I use a StringBuilder.

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Text;
    using BaseConnectionLibrary.ConnectionClasses;
    
    namespace SqlServerShowNull
    {
        public class Operations : SqlServerConnection
        {
            public Operations()
            {
                DatabaseServer = "KARENS-PC";
                DefaultCatalog = "CustomerDatabase";
            }
    
            public void WriteToFile(string fileName)
            {
                StringBuilder sb = new StringBuilder();
                using (var cn = new SqlConnection {ConnectionString = ConnectionString})
                {
                    using (var cmd = new SqlCommand() {Connection = cn})
                    {
                        cmd.CommandText = "SELECT FirstName, LastName FROM Table_1";
                        cn.Open();
                        var reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            sb.AppendLine($"{reader.GetStringAsNull("FirstName", "no data")}, {reader.GetString(1)}");
                        }
                    }
                }
    
                File.WriteAllText(fileName,sb.ToString());
            }
        }
    
        public static class DataReaderExtensions
        {
            public static string GetStringAsNull(this IDataReader pReader, string pField, string defaultValue)
            {
                if (pReader[pField] is DBNull)
                {
                    return defaultValue;
                }
                else
                {
    
                    return pReader[pField].ToString();
                }
    
            }
        }
    }
    

    Called from a button click event

    private void button1_Click(object sender, EventArgs e)
    {
        var ops = new Operations();
        ops.WriteToFile(Path.Combine(AppDomain.CurrentDomain.BaseDirectory,"Data.txt"));
    }

    Results in file

    Karen, Payne
    no data, Payne
    Karen, Gallagher

    Table data


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, February 15, 2020 3:00 PM
    Moderator
  • Thank you Both for you help on this. This makes perfect sense now. :-)
    Sunday, February 16, 2020 9:06 AM