This tutorial builds a web API for getting Employee details. A user interface (UI) isn't created.
Prerequisites.
Visual Studio 2017 version 15.7.3 or later with the following workloads:
i. ASP.NET and web development
ii. NET Core cross-platform development
Create the project
Follow these steps in Visual Studio:
1. From the File menu, select New > Project.
i. Select the ASP.NET Core Web Application template. Name the project CoreWebApi and click OK.
ii. In the New ASP.NET Core Web Application – Select the API template and click OK. Do not select Enable Docker Support.
2. Create a connection string on “appsettings.json” file as shown below.
{
"Logging": {
"LogLevel": {
"Default": "Warning"
}
},
"AllowedHosts": "*",
"ConnStrings": {
"DefaultConn": "Data Source=ServerName;Initial Catalog=test;User ID=sa;Password=1234"
}
}
3. Made following changes on your project “Startup.cs” file as shown below.
namespace CoreWebApi
{
public class Startup
{
//Add these property
public static string ConnectionString
{
get;
private set;
}
//Comment this method
//public Startup(IConfiguration configuration)
//{
// Configuration = configuration;
//}
public IConfiguration Configuration { get; }
//Made change on StartUp method
public Startup(IHostingEnvironment env)
{
Configuration = new ConfigurationBuilder().SetBasePath(env.ContentRootPath).AddJsonFile("appSettings.json").Build();
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
//Always place AddCors function before AddMvc function
services.AddCors(); services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
//Always place AddCors function before AddMvc function
services.AddCors(); services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
//Always place UseCors before UseMvc.
//Method 1 to get Access
//app.UseCors(options =>
options.WithOrigins("http://localhost:4200").AllowAnyMethod().AllowAnyHeader().AllowCredentials());
app.UseCors(options =>
options.WithOrigins("http://localhost:4200").AllowAnyMethod());
//Method 2 to get Access
//app.UseCors(builder => builder
// .AllowAnyOrigin()
// .AllowAnyMethod()
// .AllowAnyHeader()
// .AllowCredentials()
// );
app.UseMvc();
// Add this line to fatch connection string from appsetting.json file.
ConnectionString = Configuration["ConnStrings:DefaultConn"];
}
}
}
4. Write the following code on EmployeeDAL.cs to fetch the employee details from the database.
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace CoreWebApi.DAL
{
public class EmployeeDAL
{
SqlCommand cmd = null;
SqlConnection sqlCon = null;
SqlDataAdapter sda = null;
// ConnectionString fetch the database connection string.
string sqlSTR = Startup.ConnectionString;
public DataTable GetEmployee(int Id)
{
using (sqlCon = new SqlConnection(sqlSTR))
{
DataSet ds = new DataSet();
sqlCon.Open();
cmd = new SqlCommand("spGetEmployeeById", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Id", Id));
cmd.CommandTimeout = 5;
sda = new SqlDataAdapter(cmd);
sda.Fill(ds,"employee");
return ds.Tables[0];
}
}
public string DataTableToJSONWithStringBuilder(DataTable table)
{
var JSONString = new StringBuilder();
if (table.Rows.Count > 0)
{
JSONString.Append("[");
for (int i = 0; i < table.Rows.Count; i++)
{
JSONString.Append("{");
for (int j = 0; j < table.Columns.Count; j++)
{
if (j < table.Columns.Count - 1)
{
JSONString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");
}
else if (j == table.Columns.Count - 1)
{
JSONString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");
}
}
if (i == table.Rows.Count - 1)
{
JSONString.Append("}");
}
else
{
JSONString.Append("},");
}
}
JSONString.Append("]");
}
return JSONString.ToString();
}
}
}
5. In Solution Explorer, right-click the Controllers folder. Create your controller “EmployeeController” as shown on the screenshot.
Copy and paste the following code to your controller.
using CoreWebApi.DAL;
using Microsoft.AspNetCore.Mvc;
using System.Data;
namespace CoreWebApi.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class EmployeeController : ControllerBase
{
// GET api/values
[HttpGet]
public string Get()
{
EmployeeDAL dal = new EmployeeDAL();
DataTable dt = dal.GetEmployee(1);
return dal.DataTableToJSONWithStringBuilder(dt);
}
}
}
6. Before running the application by pressing Ctrl + 5, set launchUrl as shown below.
7. Output of API
No comments:
Post a Comment