I have this method, GetDataAsync()
, that grabs data from the database using a stored procedure if sqlToRun
Dictionary contains the table name. If not, then there is no stored procedure to run and just grab the table from the db directly (no SP) and convert it to a C# object to use.
Here's the code:
private async Task<Response> GetDataAsync(string table, int? start, int? end)
{
if (_sqlToRun.ContainsKey(table))
{
var sql = _sqlToRun[table];
var data = new List<Dictionary<string, object>>();
var resp = new Response();
using (var conn = new SqlConnection(_ConnectionString))
{
var cmd = new SqlCommand(sql, conn) { CommandType = CommandType.StoredProcedure };
var startParam = new SqlParameter
{
ParameterName = "@startRowNum",
Value = start,
SqlDbType = SqlDbType.Int
};
cmd.Parameters.Add(startParam);
var endParam = new SqlParameter
{
ParameterName = "@endRowNum",
Value = end,
SqlDbType = SqlDbType.Int
};
cmd.Parameters.Add(endParam);
var outputParam = cmd.Parameters.Add("@recordCount", SqlDbType.Int);
outputParam.Direction = ParameterDirection.Output;
await conn.OpenAsync();
using (var rdr = await cmd.ExecuteReaderAsync())
{
while (await rdr.ReadAsync())
{
var dictionary = new Dictionary<string, object>();
foreach (var i in Enumerable.Range(0, rdr.FieldCount))
{
var dbValue = string.IsNullOrWhiteSpace(rdr.GetValue(i).ToString()) == true
? string.Empty
: rdr.GetValue(i);
dictionary.Add(rdr.GetName(i), dbValue);
}
data.Add(dictionary);
}
}
Int32.TryParse(cmd.Parameters["@recordCount"].Value.ToString(), out var recordCount);
resp.Data = data;
resp.RecordCount = recordCount;
}
return resp;
}
else
{
var data = new List<Dictionary<string, object>>();
var resp = new Response();
using (var conn = new SqlConnection(_ConnectionString))
{
string sql = "Select * from " + table;
SqlCommand cmd = new SqlCommand(sql, conn);
await conn.OpenAsync();
using (var rdr = await cmd.ExecuteReaderAsync())
{
while (await rdr.ReadAsync())
{
var dictionary = new Dictionary<string, object>();
foreach (var i in Enumerable.Range(0, rdr.FieldCount))
{
var dbValue = string.IsNullOrWhiteSpace(rdr.GetValue(i).ToString()) == true
? string.Empty
: rdr.GetValue(i);
dictionary.Add(rdr.GetName(i), dbValue);
}
data.Add(dictionary);
}
}
Int32.TryParse(cmd.Parameters["@recordCount"].Value.ToString(), out var recordCount);
resp.Data = data;
resp.RecordCount = recordCount;
}
return resp;
}
}
Where Response is below:
public class Response
{
public int RecordCount { get; set; }
public List<Dictionary<string, object>> Data { get; set; }
}
How can I improve this code and refactor it?