NullReferenceException on this simple C# code.

Question

Edit: The line of code that gives the exception is "foreach (Column column in table.Columns) " and that's because the Table table object is null. How would I load a Table object? My code seemed to be the right approach but it doesn't work as expected. :D

I'm pulling a list of all columns in a given SQL table, and then loading them to a listbox. Here is the code:

public Dictionary<string,string> FindColumns(string tableName)
{
    using (var mySqlConnection = new SqlConnection(Properties.Settings.Default.ConnectionString))
    {
        var myConnection = new ServerConnection(mySqlConnection);
        var myServer = new Server(myConnection);
        var myDatabase = myServer.Databases["Shipments"];

        //Using a breakpoint I figured out that table is remaining null. That's
        //why I'm getting this error. How can I pull a table then?
        Table table = myDatabase.Tables[tableName];

        foreach (Column column in table.Columns)
        {
            Columns.Add(column.Name, column.DataType.ToString());
        }
    }

    return Columns;
}

I'm getting the error on the above foreach loop.

And here is where I invoke the method:

private void lstTableNames_SelectedIndexChanged(object sender, EventArgs e)
{
    foreach (var column in db.FindColumns("Shipments"))
    {
        lstTableColumns.Items.Add(column.Key + "--" + column.Value);
    }
}

Thanks for the help!

Answer 1

What are Server and ServerConnection? What does the indexer used via Tables return if the given table doesn't exist?

Find out which variable is null (e.g. table potentially) and then you can work out why it's null.

Answer 2

Using the debugger, find out what's in myDatabase.Tables. Does it have any values? If so, what are they? Does it contain one for "tableName"?

Answer 3

Does the account under which your code is running (the one in the connection string) have at least datareader access to the "Shipments" table? I'm assuming that "Shipments" is the correct table name and that your ServerConnection and Server objects are set up properly.

Answer 4

Double, triple, and quadruple check that you did not have a spelling error in the tableName parameter when you run the function. If this is SMO, a misspelled table will give you a null result.