need to performance tune by datareader

Question

Hi,

 

Below am sending my code and I need some improvement by replacing dataadapter,dataset with data reader:

 

public DataSet GetEpiInputData(decimal forecastId, decimal scenarioId, decimal? lineId)

{

var result = new DataSet();

using (IndForecastingContext dbContext = new IndForecastingContext())

{

var conn = dbContext.Database.Connection;

var connectionState = conn.State;

List<SqlParameter> spParams = new List<SqlParameter>();

try

{

spParams.Add(new SqlParameter { ParameterName = "@ForecastID", Value = forecastId });

spParams.Add(new SqlParameter { ParameterName = "@ScenarioID", Value = scenarioId });

if (lineId.HasValue)

{

spParams.Add(new SqlParameter { ParameterName = "@LineID", Value = lineId });

}

if (connectionState != ConnectionState.Open)

conn.Open();

DbProviderFactory factory = DbProviderFactories.GetFactory(conn);

using (var cmd = conn.CreateCommand())

{

cmd.CommandText = "[dbo].[GetEpiInputData]";

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandTimeout = 0;

cmd.Parameters.AddRange(spParams.ToArray());

using (var adapter = factory.CreateDataAdapter())

{

adapter.SelectCommand = cmd;

adapter.Fill(result);

}

}

}

catch (Exception ex)

{

}

finally

{

if (connectionState == ConnectionState.Open)

conn.Close();

}

}

return result;

}

 

The line I marked as yellow its taking 3 to 4 mnts.when it executes datadapter fills multiple table  into datasets.Is there any way to optimize .Finally any how I have to return dataset output.