Check for exceptions before returing the string value from the query

My function causes alot of exception, therefore performace is not that great.

Could you advise on how to fix the function and validate the data before I return the string back? I have tried to see if amount of tables are more than 0, but this is not always the case.

    public String getString(String sql)
    {
        DataSet ds = new DataSet();
        string connstring = String.Format("Server={0};Port={1}; User Id={2};Password={3};Database={4};", tbHost, tbPort, tbUser, tbPass, tbDataBaseName);
        NpgsqlConnection conn = new NpgsqlConnection(connstring);
        try
        {
            conn.Open();
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            ds.Reset();
            da.Fill(ds);           
            conn.Close();
            return ds.Tables.Count == 0 ? "0" : ds.Tables[0].Rows[0][0].ToString();
        }
        catch (Exception msg)
        {
            if (conn.State.ToString() == "Open")
            {
                conn.Close();
            }
            return "0";
        }
    }

Answers


You should be using using to automatically dispose of objects you create. You might explicitly open/close connections, but it is not needed here because the behaviour will be exactly the same. DataAdapter will open connection if not opened and remember to close it afterwards.

About validate the data - see comments in code.

public String getString(String sql)
{
    using (DataSet ds = new DataSet())
    {
        string connstring = String.Format("Server={0};Port={1}; User Id={2};Password={3};Database={4};", tbHost, tbPort, tbUser, tbPass, tbDataBaseName);
        using (NpgsqlConnection conn = new NpgsqlConnection(connstring))
        {
            using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn))
            {
                da.Fill(ds);
                // You did check count of tables
                if (ds.Tables.Count > 0)
                {
                   DataTable dt = ds.Tables[0];
                   // But forgot to check count of Rows
                   if (dt.Rows.Count > 0)
                   {
                      object o = dt.Rows[0][0];
                      //  And returned value for nulls
                      //  Check for null is here because I don't know
                      //  This Postgresql classes
                      if (o != DBNull.Value && o != null)
                      {
                         return o.ToString();
                      }
                  }
              }
           }
        }
    }
    // Return default value
    return "0";
}

One final thing - when testing enumeration, don't convert it to string. To see if connection is opened, simply say:

if (conn.State == ConnectionState.Open)

try using a "finally" block as well.

Also I would recommend to divide the Connect functionality to a other method to have better control of where an exception would occur.

Also use stored procedures with SQL to minimize errors with the SQL string.


public String getString(String sql)
{
    DataSet ds = new DataSet();
    string connstring = String.Format("Server={0};Port={1}; User Id={2};Password={3};Database={4};", tbHost, tbPort, tbUser, tbPass, tbDataBaseName);
    NpgsqlConnection conn = new NpgsqlConnection(connstring);
    conn.Open();
    NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
    ds.Reset();
    try
    {
       da.Fill(ds);          
    }
    catch (Exception msg)
    {
        // do something here or log the error.
    }
    finally
    {
       if (conn.State.ToString() == "Open")
       {
            conn.Close();
       }
    }
    return ds.Tables.Count == 0 ? "0" : ds.Tables[0].Rows[0][0].ToString();
}

Need Your Help

call window.frames inside loop "for"

javascript arrays function frames

I have a function that receives an array with the names of functions that I want execute:

plotting contours on grid of scatterplots with pairs() in R

r ggplot2

I've had some success making a grid of scatter plots showing every combination of columns in a data frame using pairs() in R. I followed this guide and got the scatter plot matrix working well. N...