Upload word document to .Net core WebAPI

We’ll be working with a .docm template in Word 2016 which contains a textbox. This control is given a name using the Content Control Properties.

This is available under Developer Menu in MS Word, then click on Properties.

Save this template as .docm

Create .Net Core WebAPI under .net core version 3.1 for this example. The sample code is as below. The library System.IO is used to access the value in the variable.

using System;
using System.IO;
using System.IO.Packaging;
using System.Xml;
using System.Xml.XPath;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;

namespace SampleApplicationUpload.Controllers
{
    public class UploadController : ControllerBase
    {
        [HttpPost]
        [Route("upload")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        public IActionResult Upload()
        {
            string filename = string.Empty;
            string extension = string.Empty;
            string result = string.Empty;
            bool uploaded = false;
            string ModifiedBy = string.Empty;
            var request = HttpContext.Request;

            if (request.Form.Files.Count > 0)
            {
                filename = request.Form.Files[0].FileName;
                extension = filename.Substring(filename.LastIndexOf(".")).ToLower();
                if (!string.IsNullOrEmpty(extension) && extension.Equals(".docm"))
                {
                    using (var memoryStream = new MemoryStream())
                    {
                        request.Form.Files[0].CopyTo(memoryStream);
                        if (memoryStream.Length < 2097152)
                        {
                            result = UploadWorStream(memoryStream, extension, out ModifiedBy, out uploaded);
                        }
                        else
                        {
                            return BadRequest("File size too large");
                        }
                    }
                }
                else
                {
                    return BadRequest("File format not recognised. Upload .docm file only");
                }
            }           
            return Ok(result);
        }
        private string UploadWorStream(MemoryStream ms, string ext, out string ModifiedBy, out bool uploaded)
        {
            string contenttype = string.Empty;
            bool erroroccurs = false;
            ModifiedBy = string.Empty;
            var xmlDoc = new XmlDocument();
            uploaded = false;
            switch (ext)
            {
                case ".docm":
                    contenttype = "application/vnd.ms-word";
                    break;
                default:
                    erroroccurs = true;
                    break;
            }

            if (!erroroccurs)
            {
                if (contenttype != string.Empty)
                {
                    Stream fs = ms;
                    var br = new BinaryReader(fs);
                    string xmlDocRelType = "http://schemas.openxmlformats.org/officeDocument/" +
                                           "2006/relationships/customXml";
                    string officeDocRelType = "http://schemas.openxmlformats.org/" +
                                              "officeDocument/2006/relationships/officeDocument";
                    PackagePart xmlPart = default(PackagePart);
                    PackagePart documentPart = default(PackagePart);
                    Uri documentUri = default(Uri);
                    Uri xmlUri = default(Uri);
                    using (Package officePackage = Package.Open(fs, FileMode.Open, FileAccess.Read))
                    {
                        foreach (var relationship1 in officePackage.GetRelationshipsByType(officeDocRelType))
                        {
                            documentUri = PackUriHelper.ResolvePartUri(new Uri("/", UriKind.Relative),
                                                                       relationship1.TargetUri);
                            documentPart = officePackage.GetPart(documentUri);
                            break;
                        }

                        if (documentPart != null)
                            foreach (var relationship2 in documentPart.GetRelationshipsByType(xmlDocRelType))
                            {
                                xmlUri = PackUriHelper.ResolvePartUri(new Uri("/", UriKind.Relative),
                                                                      relationship2.TargetUri);
                            }
                        xmlPart = officePackage.GetPart(xmlUri);
                        Stream inputStream = xmlPart.GetStream(FileMode.Open, FileAccess.Read);
                        var ts = new StreamReader(inputStream);
                        xmlDoc.Load(ts);
                        ts.Close();
                        ts.Dispose();
                        officePackage.Close();
                    }

                    XPathNavigator xpnGet2 = xmlDoc.CreateNavigator();
                   // string txtSample = xpnGet2.Evaluate("string(//ROOT[1]/SampleDoc[1]/@txtSample)").ToString();
                    string txtSample = Convert.ToString(xpnGet2.Evaluate("string(//ROOT/SampleDoc/@txtSample)"));
                    if (txtSample.Length > 0)
                    {
                        return txtSample;
                    }
                    else {
                        return "Unable to read document";
                    }
                }
                else
                {
                    uploaded = false;
                    return "Error";
                }
            }
            else
            {
                uploaded = false;
                return "Error";
            }
        }
    }
}

Finally try to access the WebAPI using a Postman request and check the value while debugging the end-point.

curl --location --request POST 'https://localhost:<port>/upload' \
--form 'attachment
=@/C:/Users/username/Desktop/TestDocument.docm'

Upload file in ReactJS to WebAPI

Create a new React App using create-react-app as explained in this Post. The sample ReactJS code is available here.

In the index.js file change the code as below:

import React from 'react';
import ReactDOM from 'react-dom';
import App from './App';

ReactDOM.render(<App />, document.getElementById('root'));

For the App.js file, change the code as below:

import React from 'react';       
import './App.css';
import Fileupload from './FileUpload'
function App() {    
  return (    
    <div className="App">    
      <Fileupload></Fileupload>
    </div>    
  );    
}
export default App;

Now comes the FileUpload component as FileUpload.js:

import React from 'react';
import axios from 'axios';

class Fileupload extends React.Component {

    constructor(props) {    
            super(props);    
            this.state = {    
                    file: '',    
        };    
    }

    async submit(e) {    
        e.preventDefault();    
        const url = `http://url`;    
        const formData = new FormData();    
        formData.append('body', this.state.file);    
        const config = {    
            headers: {    
                'content-type': 'multipart/form-data',
                'token': 'xxx'                
            },
        };
        const HTTP = axios.create({
            withCredentials: true
        });

        //return post(url, formData, config);
        return HTTP.post(url, formData, config);
    }
    setFile(e) {    
        this.setState({ file: e.target.files[0] });    
    }

    render() {    
        return (    
                <div className="container-fluid">    
                        <form onSubmit={e => this.submit(e)}>    
                                <div className="col-sm-12 btn btn-primary">    
                                        File Upload    
                        </div>    
                                <h1>File Upload</h1>    
                                <input type="file" onChange={e => this.setFile(e)} />    
                                <button className="btn btn-primary" type="submit">Upload</button>    
                        </form>    
                </div>    
        )    
    }
}
export default Fileupload

Here I’m using axios to call the API via Post request. Passing the credentials in the call with create.
The token part is optional in the Headers, you can add/remove headers based on your requirement.

From the WebAPI end, I’m using .Net core. You can receive the file in the Action method as below:

[HttpPost]
[Route("upload")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status400BadRequest)]
public IActionResult UploadForm([FromQuery] string username)
{
	var request = HttpContext.Request;
            
	if (request.Form.Files.Count > 0)
	{
		filename = request.Form.Files[0].FileName;
		extension = filename.Substring(filename.LastIndexOf(".")).ToLower();
		if (!string.IsNullOrEmpty(extension) && extension.Equals(".docx"))
		{
			using (var memoryStream = new MemoryStream())
			{
				request.Form.Files[0].CopyTo(memoryStream);
				
				// Upload the file if less than 2 MB
				if (memoryStream.Length < 2097152)
				{
					string check = Service.CheckifValid(username);
					if (check.Equals("ok"))
					{
						//Process the stream and pass the data to the back-end in the Service Layer.
						result = Service.UploadStream(memoryStream, filename, extension, username);
					}
					else
					{
						return Ok(check);
					}
				}
				else
				{
					return BadRequest("File size too large");
				}
			}
		}
		else
		{
			return BadRequest("File format not recognised.");
		}
	}
	//Other ToDos...
}

How to use Server.MapPath in asp.net core

Server.MapPath is not available in asp.net core. Instead, you can use IWebHostEnvironment interface to access any physical contents kept in the specified path.

Below is the Controller code using the IWebHostEnvironment ContentRootPath property which gets the absolute path to the directory that contains the Application content files:

[ApiController]
public class DocumentController : ControllerBase
{
	private IDocumentService offService = null;
	private readonly IWebHostEnvironment _host;

	public DocumentController(IDocumentService offService, IWebHostEnvironment host)
	{
		this.offService = offService;
		this._host = host;
	}
	
	[HttpGet]
	[Route("some/route")]
	public IActionResult ExportToFile([FromQuery] int UserId)
	{
		var username = HttpContext.User.Identity.Name.Split('\\')[1].ToString();
		
		//get the memoryStream by passing the absolute path:
		MemoryStream memoryStream = offService.GetWordStream(_host.ContentRootPath);
		
		memoryStream.Position = 0;
		
		return File(memoryStream, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", "File.docx"); // returns a FileStreamResult
	}
}	

Use the path in the File IO methods, example below:

File.Copy(path + "\\DocPath\\File.docm", strFileName);

In the debug mode, this path is the WebAPI Project absolute path to the directory. During runtime, this path is the absolute path where the files are hosted on the Server.

Download word file using axios get from WebAPI

The below code is using axios get method with Headers token to be passed with credentials which in this case would be Windows Authentication to the WebAPI.

The response returned by the Promise contains the header “content-disposition” which has the name of the file being downloaded.

The content-disposition header value is as below:

attachment; filename="SomeFile.docm"; filename*=UTF-8''SomeFile.docm

The content-type required for this file from the API is:

application/vnd.openxmlformats-officedocument.wordprocessingml.document

The returned response has the blob in the data property which then requires to be converted to a downloadble link. The filename is being extracted from the content-disposition header.

const HTTP1 = axios.create({
                  withCredentials: true
                });
const response = HTTP1.get('http://path/worddoc?userid=<someid>', {
		        headers: {
		          'token': 'xxxxx'
		        },
				responseType: 'blob'
		      }).then((response) => {
			
				const headerval = response.headers['content-disposition'];
				var filename = headerval.split(';')[1].split('=')[1].replace('"', '').replace('"', '');
				
				const downloadUrl = window.URL.createObjectURL(new Blob([response.data]));
				const link = document.createElement('a');
				link.href = downloadUrl;
				link.setAttribute('download', filename); //any other extension
				document.body.appendChild(link);
				link.click();
				link.remove();
			  });

For IE11 browser, downloading blob response type directly does not work. A workaround for this is to recognize the browser via User Agent modify the above code in Javscript for IE:

var ua = window.navigator.userAgent;
var msie = ua.indexOf(".NET ");

if(msie>0) {
          window.navigator.msSaveBlob(new Blob([response.data]), filename);
}

How to add folders in .net core webapi to Publish directory

When you add a folder in your project, your .csproj file gets modified as shown below:

<ItemGroup>
	<Folder Include="AppData\" />
	<Folder Include="OfflineFiles\2019\" />
</ItemGroup>

To add a folder to the Publish directory with some pre-existing contents, add the below ItemGroup in your .csproj file:

<ItemGroup>
	<Content Include="OfflineFiles\**">
		<CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
	</Content>
</ItemGroup>

If you have a folder without any pre-existing content, add the below Target action in the .csproj file:

<Target Name="CreateAppDataFolder" AfterTargets="AfterPublish">
	<MakeDir Directories="$(PublishUrl)AppData" Condition="!Exists('$(PublishUrl)AppData')" />
</Target>

If you check the WebPublishMethod (.pubxml file under Properties folder) which in this is case is FileSystem, the publishUrl contains the directory where the files are published:

<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <WebPublishMethod>FileSystem</WebPublishMethod>
    <PublishProvider>FileSystem</PublishProvider>
    <LastUsedBuildConfiguration>Release</LastUsedBuildConfiguration>
    <LastUsedPlatform>Any CPU</LastUsedPlatform>
    <SiteUrlToLaunchAfterPublish />
    <LaunchSiteAfterPublish>True</LaunchSiteAfterPublish>
    <ExcludeApp_Data>False</ExcludeApp_Data>
    <ProjectGuid>xxxx</ProjectGuid>
    <publishUrl>C:\Path</publishUrl>
    <DeleteExistingFiles>False</DeleteExistingFiles>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <SelfContained>false</SelfContained>
  </PropertyGroup>
</Project>

I’m using .net core 3.1 for the above method. This method might vary in other versions of .net core.

Authorize .net core webapi with token using Windows Authentication

Create a TokenFactory class that creates an encrypted token with the Windows authenticated username:

public class TokenFactory
{
	public static Aes aes = Aes.Create();
	public static string GenerateToken(string UserName)
	{
		//Get the required values...
		TokenDetails encrytionData = new TokenDetails
		{
			UserName = uName,
			Role = rName,
			SecretKey = sKey,
			Date = DateTime.Now,
			Interval = duration
		};
		//To Do: Generate with Cryptography encryption logic.
		var jsonSerialize = JsonConvert.SerializeObject(encrytionData);
		byte[] encrypted = Cryptography.Encrypt(jsonSerialize, aes.Key, aes.IV);
		var authToken = Convert.ToBase64String(encrypted);
		return authToken;
	}
	
	public static bool ValidateToken(string authToken)
	{
		bool isValidated;
		if (!String.IsNullOrEmpty(authToken))
		{
			var bytarr = Convert.FromBase64String(authToken);
			// Decrypt the bytes to a string.
			string dcryptToken = Cryptography.Decrypt(bytarr, aes.Key, aes.IV);
			var obj = JsonConvert.DeserializeObject<TokenDetails>(dcryptToken);
			TimeSpan timeSpan = DateTime.Now - obj.Date;
			
			if (timeSpan.TotalMinutes > CommonUtil.Duration)
			{
				isValidated = false;
			}
			else
			{
				isValidated = true;
			}
		}
		else
		{
			isValidated = false;
		}
		return isValidated;
	}
	
	public static string GetUserFromToken(string authToken)
	{
		var bytarr = Convert.FromBase64String(authToken);
		// Decrypt the bytes to a string.
		string dcryptToken = Cryptography.Decrypt(bytarr, aes.Key, aes.IV);
		var obj = JsonConvert.DeserializeObject<TokenDetails>(dcryptToken);
		return Convert.ToString(obj.UserName);
	}
}

Below is TokenDetails class:

public class TokenDetails
{
	public DateTime Date { get; set; }
	public string  SecretKey { get; set; }
	public string Role { get; set; }
	public string UserName { get; set; }
	public int Interval { get; set; }
}

Create a RequestFilter that will inherit ActionFilterAttribute to authorize any request using the [RequestFilter] attribute:

public class RequestFilter : ActionFilterAttribute
{
	public override void OnActionExecuting(ActionExecutingContext context)
	{
		Microsoft.AspNetCore.Http.IHeaderDictionary headers = context.HttpContext.Request.Headers;
		var user = context.HttpContext.User.Identity.Name;
		bool IsValidated = false;
		string token = string.Empty;
		if (CheckValidHeaders(context))
		{
			if (context.HttpContext.Request.Headers.ContainsKey("token"))
			{
				token = context.HttpContext.Request.Headers["token"].ToString();
				IsValidated = TokenFactory.ValidateToken(token, user);
			}
		}

		if (!IsValidated)
		{
			if (user.Equals(TokenFactory.GetUserFromToken(token)))
			{
				token = TokenFactory.GenerateToken(user);
				context.HttpContext.Request.Headers.Remove("token");
				context.HttpContext.Request.Headers.Add("token", token);
			}
			else
			{
				//Return UnAuthorized if current Windows User is not same as the one encrypted in token.
				context.Result = new UnauthorizedResult();
			}
		}
	}

	public override void OnActionExecuted(ActionExecutedContext context)
	{
		var token = Convert.ToString(context.HttpContext.Request.Headers["token"]);
		context.HttpContext.Response.Headers.Add("token", token);
	}

	private bool CheckValidHeaders(ActionExecutingContext context)
	{
		if (String.IsNullOrEmpty(context.HttpContext.Request.Headers["token"]))
		{
			return false;
		}

		return true;
	}
}

Sample Controller implementation to fetch the token:

[ApiController]
public class UserController : ControllerBase
{
	// GET: api/User
	[Route("User")]
	[Authorize]
	[HttpGet]
	public IActionResult GetUser()
	
	{
		var user = HttpContext.User.Identity.Name;
		string token = TokenFactory.GenerateToken(user);
		//bool isValid = TokenFactory.ValidateToken(token, user);
		return Ok(token);
	}
}

Use the [Authorize] attribute if you’ve also enabled Anonymous Authentication to allow OPTIONS request. This is explained here in further detail.

You can Authorize your users with the [RequestFilter] to filter out the Unauthorized candidates.

[RequestFilter]
[ApiController]
[Authorize]
public class AppraisalsHomeController : ControllerBase
{
	//To Do...
}

Enable CORS in .Net Core WebAPI

It is a common scenario where a React front-end SPA is calling a .Net Core WebAPI to fetch data. In this case, suppose both Applications are using their respective domains shown below:

React App => http://myfrontend.com
API=> http://myapi.com

CORS (Cross-Origin Resource Sharing) is a standard that works by adding HTTP headers that allow servers to describe the set of origins that are permitted to fetch information using a web browser and the kind of requests that are allowed.
For the API to allow the React App to fetch the data, it has to allow the Origin of the React App. This is a common CORS problem. So if you’re using, say axios or Fetch, to fetch data from the .Net Core WebAPI, it will only succeed if the API allows the domain http://myfrontend.com to call itself.

Below I’ve used the technique of CORS with named policy and middleware:

public class Startup
{
	public Startup(IConfiguration configuration)
	{
		Configuration = configuration;
	}
	readonly string MyAllowedOrigins = "_myAllowedOrigins";
	public IConfiguration Configuration { get; }
	public IContainer ApplicationContainer { get; private set; }
	// This method gets called by the runtime. Use this method to add services to the container.
	public void ConfigureServices(IServiceCollection services)
	{
		services.AddControllers();
		services.AddCors(options =>
		{
			options.AddPolicy(MyAllowedOrigins,
			builder =>
			{
				//Allowing both the localhost and hosted domains.
				builder.WithOrigins("http://localhost:3000",
									"http://www.myfrontend.com");
			});
		});
	}
	
	//Apply CORS policies to all endpoints via CORS Middleware:
	// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
	public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
	{
		//Omitted code
		app.UseCors(MyAllowedOrigins);
		app.UseEndpoints(endpoints =>
		{
			endpoints.MapControllers();
		});
		//Omitted code
	}
}

The example above is how you apply CORS policy globally to all endpoints in your API. You can also use the [EnableCors(“Policy String”)] attribute on your controllers/page-model/action method.

e.g. Applying CORS on Action method:

[EnableCors("_myAllowedOrigins")]
[HttpGet]
public ActionResult<IEnumerable<string>> Get()
{
	return new string[] { "test1", "test2" };
}

It is recommended to enable CORS either locally or globally and not combine the two approaches.

We can also expose the allowed Headers and http methods the following way:

public void ConfigureServices(IServiceCollection services)
{
	services.AddControllers();
	services.AddCors(options =>
	{
		options.AddPolicy(MyAllowSpecificOrigins,
		builder =>
		{
			builder.WithOrigins("http://myfrontend.com", "http://localhost:3000")
			.WithHeaders("token")
			.WithMethods("OPTIONS", "GET", "POST");
		});
	});
}

You can also allow credentials to be passed to the WebAPI from the Client by chaining the .AllowCredentials method.

Pre-flight requests for OPTIONS method call fails with Windows authentication and gives 401 Unauthorized. This can be worked around by enabling both Windows and Anonymous authentication.

If you are using Windows Authentication in the case of an Intranet Application and also enabled Anonymous Authentication for allowing OPTIONS pre-flight request, then make sure to use [Authorize] and [AllowAnonymous] attributes to the respective endpoints where required. Without using [Authorize], the name of the user is returned
null with context.HttpContext.User.Identity.Name where context is the ActionExecutingContext object. These attributes can be used on Controllers or Actions as required.
Also, make sure to setup the middleware in the correct order for using with UseCors, UseAuthentication and UseAuthorization in the Startup.cs file Configuration.

Global Exception Handling and Logging in aspnet core webapi

Create a Model for your Error details as below:

public class ErrorDetails
{
	public int StatusCode { get; set; }
	public string Message { get; set; }


	public override string ToString()
	{
		return JsonConvert.SerializeObject(this); //JsonConvert is part of Newtonsoft.Json package.
	}
}

Create the Exception Factory which will handle Exceptions globally in your Api:

public static class ExceptionFactory
{ 
    public static void ConfigureExceptionHandler(this IApplicationBuilder app, int StatusCode = 0, string message = "")
    {
        app.UseExceptionHandler(appError =>
        {
            appError.Run(async context =>
            {
                context.Response.StatusCode = (int)HttpStatusCode.InternalServerError;
                context.Response.ContentType = "application/json";
                
                var contextFeature = context.Features.Get<IExceptionHandlerFeature>();
                if (contextFeature != null)
                {
                    LogTraceFactory.LogError($"Something went wrong: {contextFeature.Error}");

                    await context.Response.WriteAsync(new ErrorDetails()
                    {
                        StatusCode = context.Response.StatusCode,
                        Message = "Internal Server Error."
                    }.ToString());
                }
            });
        });
    }
}

Register Exception handling in your Api:

// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
	if (env.IsDevelopment())
	{
		app.UseDeveloperExceptionPage();
	}
	app.ConfigureExceptionHandler();
	.....
}

Also, you can return the error details in your Action Controllers as below:

[HttpGet]
[Route("product/getproductdetails")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status400BadRequest)]
public IActionResult GetproductDetails([FromQuery]int productId)
{
	LeadsProduct lead = null;
	if (productId == 0)
	{
		LogTraceFactory.LogError($"Incorrect parameters, productId: {productId}");
		return BadRequest(new ErrorDetails { StatusCode = Convert.ToInt32(HttpStatusCode.BadRequest), Message = $"Missing parameters, productId: {productId} for product Leads." });
	}

	lead = leadService.FetchProductDetails(productId);

	if (lead == null)
	{
		LogTraceFactory.LogError($"product Leads Not Found for productId: {productId}");
		return NotFound(new ErrorDetails { StatusCode = Convert.ToInt32(HttpStatusCode.NotFound), Message = $"product Leads not found for productId {productId}." });
	}

	return Ok(lead);
}

The example above uses the nlog package in the .net core Web Api. You can create LogTraceFactory class as below:

public static class LogTraceFactory
{
	private static ILogger logger = LogManager.GetCurrentClassLogger();

	public static void LogDebug(string message)
	{
		logger.Debug(message);
	}

	public static void LogError(string message)
	{
		logger.Error(message);
	}

	public static void LogInfo(string message)
	{
		logger.Info(message);
	}

	public static void LogWarn(string message)
	{
		logger.Warn(message);
	}
}

Configure nlog package as below in the nlog.config file:

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      autoReload="true"
      internalLogLevel="Trace"
      internalLogFile="C:\internal_logs\internallog.txt">

  <targets>
    <target name="logfile" xsi:type="File"
            fileName="C:\ProjectLogs\${shortdate}_logfile.txt"
            layout="${longdate} ${level:uppercase=true} ${message}"/>
  </targets>

  <rules>
    <logger name="*" minlevel="Debug" writeTo="logfile" />
  </rules>
</nlog>

You can modify the above configuration as required. As part of the clean architecture, it is better to setup the ExceptionFactory and LogTraceFactory in the Common Layer of your Solution.


Omg! Now you can earn a six figure income. Click here to know how.

How to serialize data using translator C# .net core webapi

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.

How to read connection strings stored in appsettings file C#

This post is based on a setup of an asp.net core application. Configuration is read in the Startup class upon the Application startup. The Configure method in this class calls the ApiBootstrapper to check whether the connection string for Dev or Production is required.
This can be further used to call the Stored Procedures or query tables using ADO.Net.

Appsettings.json file is the asp.net core config file. This file contains the Connection Strings is as shown below:

{
	"configSetting": {
		"ConnectionStrings": {
			"ProdConnection": "Data Source=ServerName;Initial Catalog=DBProd;UID=username;PWD=password;",
			"DevConnection": "Data Source=ServerName;Initial Catalog=DBDev;UID=username;PWD=password;"
		},
		"Parameters": {
			"IsProduction": true
			"IsDev": false
		}
	}
}
public class Startup
{
	public Startup(IConfiguration configuration)
	{
		Configuration = configuration;
	}
	
	public IConfiguration Configuration { get; }
	
	public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
	{
		////
	
	
		ApiBootstrapper.Initialize(Configuration);
	}
	
}

Below is the code for ApiBootstrapper class:

public class ApiBootstrapper
{
	
	public static void Initialize(IConfiguration configuration)
	{
		CommonUtil.IsProduction = configuration.GetSection(ConfigKeys.configSetting.ToString()).GetSection(ConfigKeys.Log.ToString()).Value.ToString();
		if (Convert.ToBoolean(CommonUtil.IsProduction))
		{
			CommonUtil.ConnectionString = configuration.GetSection("configSetting").GetSection("ConnectionStrings").GetSection("ProdConnection").Value.ToString();
		}
	}
}

The above example shows how we can store Connection Strings for different environments like Dev and Prod and read it based on Config file settings.