rychrist88 rychrist88 - 2 months ago 6
SQL Question

How to Remove Nulls From List Then Add

I have a list that is being populated from my database table and i'm trying to do a conversion later in the code. I am unable to do the conversion because the program reads the null values and says it's an illegal operation so I figure excluding the null values before they are even added to the list may be my best option. I know there are a lot of similar posts but i've tried a few of them with no luck. It's for the list oldNames. I've tried

oldNames = oldNames.Where(m => !string.IsNullOrEmpty(m)).ToList();
,
oldNames.RemoveAll(string.IsNullOrWhiteSpace);
,
oldNames.Where(i => i != null).ToList().Add(actualPDF);
But i need to get a count from them later and all three of those return 0 for the count
for (int i = 0; i < oldNames.Count; i++)
. Any help would be appreciated, Thanks!

List<string> oldNames = new List<string>(); //<----

public Form1()
{
InitializeComponent();

SqlConnection con = new SqlConnection(@"Data Source=x; Initial Catalog=x; Integrated Security=True;");
SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM x", con);
DataTable dt = new DataTable();
sda.Fill(dt);

FolderBrowserDialog fbd = new FolderBrowserDialog();
fbd.RootFolder = Environment.SpecialFolder.Desktop;

if (fbd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
txtFileLocation.Text = fbd.SelectedPath;
}

string[] files = Directory.GetFiles(txtFileLocation.Text, "*.*", SearchOption.AllDirectories);

foreach (string val in files)
{
listBox2.Items.Add(Path.GetFileName(val));
}

foreach (DataRow row in dt.Rows)
{
var actualPDF = row["x"].ToString();
var namedFN = row["y"].ToString();
var fileID = row["z"].ToString();
var filesinFolder = listBox2.Items.ToString();

listBox1.Items.Add(fileID);
listBox4.Items.Add(namedFN);
oldNames.Add(actualPDF); // <--- This List
}
}

Answer

If the intent is to retrieve a count of non-null items from row["x"], then perform the query on the database:

select count(*) from [table] where x is not null

Otherwise, exclude values from x where they are null in the original query:

select x, y, z from [table] where x is not null

Alternatively, the foreach loop should check for DBNull before performing any operations against the data row:

foreach (DataRow row in dt.Rows)
{
    if(row["x"] == DBNull.Value || string.IsNullOrWhiteSpace(row["x"].ToString())
    {
        continue;
    }

    var actualPDF = row["x"].ToString();
    var namedFN = row["y"].ToString();
    var fileID = row["z"].ToString();
    var filesinFolder = listBox2.Items.ToString();

    listBox1.Items.Add(fileID);
    listBox4.Items.Add(namedFN);

    oldNames.Add(actualPDF);
}
Comments