Suppose you’re trying to fetch user data from your database using ado.net in you .net core webapi. You have a SQLHelper class that calls a Stored Procedure and returns data that requires to be converted to a DTO object with pre-defined properties in C#.

The SQLHelper class will have the following method to call your Stored Procedure:

public static TData ExtecuteProcedureReturnData<TData>(string connString, string procName,
	Func<SqlDataReader, TData> translator,
	params SqlParameter[] parameters)
{
	using (var sqlConnection = new SqlConnection(connString))
	{
		using (var sqlCommand = sqlConnection.CreateCommand())
		{
			sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
			sqlCommand.CommandText = procName;
			if (parameters != null)
			{
				sqlCommand.Parameters.AddRange(parameters);
			}
			sqlConnection.Open();
			using (var reader = sqlCommand.ExecuteReader())
			{
				TData elements;
				try
				{
					elements = translator(reader);
				}
				finally
				{
					while (reader.NextResult())
					{ }
				}
				return elements;
			}
		}
	}
}

What is a Translator?

A translator is a class like a DTO in C# which will serialize your data returned from the Stored Procedure into it’s properties.
This will be returned as a json object by your WebApi to your Client front-end.

You can create a Translators folder in your .net core WebApi Project to have all such classes in one place.

An example Translator is as shown below:

public static class UserTranslator
{
	public static User TranslateAsUser(this SqlDataReader reader)
	{
		if (!reader.HasRows)
			return null;
		reader.Read();

		var item = new User();

		if (reader.IsColumnExists("Username"))
			item.Username = SqlHelper.GetNullableString(reader, "Username");

		if (reader.IsColumnExists("FullName"))
			item.FullName = SqlHelper.GetNullableString(reader, "FullName");
			
		if (reader.IsColumnExists("RoleName"))
                item.RoleName = SqlHelper.GetNullableString(reader, "RoleName");

		if (reader.IsColumnExists("Email"))
			item.Email = SqlHelper.GetNullableString(reader, "Email");

		return item;
	}
}

In the above example, you data will have the following columns as Username, FullName and Email. It only returns one row and not a list.

For returning a list:

public static List<User> TranslateAsUsersList(this SqlDataReader reader)
{
	var list = new List<User>();
	while (reader.Read())
	{
		list.Add(TranslateAsUser(reader, true));
	}
	return list;
}

Make sure your reader.Read() method is not called twice.

The DTO for user is as follows:

public class User
{
	public string Username { get; set; }
	public string FullName { get; set; }
	public string RoleName { get; set; }
	public string Email { get; set; }
}

Now, you need to call your Stored Procedure from your Repository:

public User getUserDetails(string UserName)
{
	string connString = CommonUtil.ConnectionString;
	SqlParameter[] param =
	{
		new SqlParameter("@Username", UserName)
	};

	User user = SqlHelper.ExtecuteProcedureReturnData<User>(
		connString,
		"GetUserDetailsFromDB",
		r => r.TranslateAsUser(), //call TranslateAsUsersList if List of Users is required and return List<User>
		param
		);

	return user;
}

Assuming, you’re using the Repository pattern in your WebApi Data Layer. Else, you can call the above method however your Project structure works.

I’ve written another post on multiple ways to fetch data for calling StoredProcedure in your WebApi for your SQLHelper class.