[RESOLVED] Exception Message In A DataTable Function

[FunkySloth]

Hi,

Let me show you my code:

        public static DataTable SelectCommand(SqlCommand command, string connection)
        {
            DataTable dataTable = new DataTable();
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            SqlConnection DBConnection = new SqlConnection(connection);

            command.Connection = DBConnection;
            command.CommandType = CommandType.Text;

            try
            {
                DBConnection.Open();
                dataAdapter.SelectCommand = command;
                dataAdapter.Fill(dataTable);
                return dataTable;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            finally
            {
                DBConnection.Close();
                dataAdapter.Dispose();
                DBConnection.Dispose();
            }
        }

My 'return ex.Message' have wiggly red line, indicating an error, that is because my Function needs to return a DataTable not a string. But what I want to do is, if it capture an error during execution I want to show the exception message. How am I going to do that using that function?

Thank you

[jmcilhinney]

You shouldn't be catching the exception there then. If the method that calls that method is the one making use of the exception then it should be the one catching it. You can get rid of that exception handler altogether and use `using` statements to create and destroy your data access object:

using (var dbConnection = new SqlConnection(connection))
using (var dataAdapter = new SqlDataAdapter {SelectCommand = command, Connection = dbConnection})
{
    // Use dataAdapter here.
}

Even if you weren't going to do that, there's no point calling both Close and Dispose on the connection because Dispose calls Close implicitly. There's also no point calling Open because the Fill and Update methods of a data adapter will both implicitly open a connection if it's closed and then close again if it needed to be opened.

You would then catch the exception where you call SelectCommand, e.g.

try
{
    var table = SelectCommand(command, connection);
 
    // ...
}
catch (Exception ex)
{
    // ...
}

By the way, 'SelectCommand' is a terrible name for that method. As a method name, it implies that the method selects a command, which it certainly doesn't. A method name should be a concise description of what the method does. It should be verb-based. Property names should be noun-based and describe what the property is, which is why a data adapter has a SelectCommand property that is a command that selects. Your method would be more appropriate named 'GetDataTable' or something along those lines, because that's actually what it does.

[jmcilhinney]

By the way, this question has absolutely nothing to do with MVC so it should not be in the MVC forum. Questions here should be MVC-specific. Your question should have been posted in the C# forum.

[FunkySloth]

Oh, I thought this would be the best thread, cause what I'm going to do is catch the exception message through Controller and pass it to View.

But thank you for a very informative tips jm.

Thanks mate