How to pass typed DataTable as output parameter to helper method

Question

I am learning Typed Dataset,

Have one helper method which takes procedure name & returns DataTable,

public static DataTable ExecuteProcedureReturnDataTable(string procedureName, SqlParameter[] prmArray = null)
        {
            DataTable dt = new DataTable();
            dt = null;
            using (SqlConnection connection = new SqlConnection(Common.GetConnectionString()))
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter da = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                try
                {
                    // attempt to open sql connection and exec command
                    connection.Open();
                    command.Connection = connection;
                    command.CommandText = procedureName;
                    command.CommandType = CommandType.StoredProcedure;
                    // add parameters to command if they exist
                    if (prmArray != null)
                    {
                        foreach (SqlParameter p in prmArray)
                        {
                            command.Parameters.AddWithValue(p.ParameterName, p.Value);
                        }
                    }

                    da.Fill(ds);

                    //Check whether there is table in dataset
                    if (ds != null )
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                        }
                    }

                }
                catch (SqlException exSql)
                {
                    EventLogging.LogEvent("Exception", exSql.ToString());
                    dt = null;
                }
                catch (Exception ex)
                {
                    EventLogging.LogEvent("Exception", ex.ToString());
                    dt = null;
                }
                finally
                {
                    command.Dispose();
                    connection.Dispose();
                }
            }
            return dt;
        }

I have 3-4 different DataTables under my Typed DataSet, I want a Helper function to work for my all typed datatable. How Can I pass Typed DataTable as output parameter to this function? I also doubt, is this possible?

Answer 1

Make the method generic. So that it can return your typed DataTable. You should add DataTable, new() constraint to your method.

public static T ExecuteProcedureReturnDataTable<T>(string procedureName, SqlParameter[] prmArray = null) where T : DataTable, new()
{
    T dt = new T();
    using (SqlConnection connection = new SqlConnection(Common.GetConnectionString()))
    {
        SqlCommand command = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter(command);
        try
        {
            // attempt to open sql connection and exec command
            connection.Open();
            command.Connection = connection;
            command.CommandText = procedureName;
            command.CommandType = CommandType.StoredProcedure;
            // add parameters to command if they exist
            if (prmArray != null)
            {
                foreach (SqlParameter p in prmArray)
                {
                    command.Parameters.AddWithValue(p.ParameterName, p.Value);
                }
            }

            da.Fill(dt);
        }
        catch (SqlException exSql)
        {
            EventLogging.LogEvent("Exception", exSql.ToString());
            dt = null;
        }
        catch (Exception ex)
        {
            EventLogging.LogEvent("Exception", ex.ToString());
            dt = null;
        }
        finally
        {
            command.Dispose();
            connection.Dispose();
        }
    }
    return dt;
}

Then call it as:

var table = ExecuteProcedureReturnDataTable<YourDataSet.YourTable>(...);