In this post we will see how to add data into MS SQL Database using .NET Core API. We will create a .NET Controller to add product information into database table. We will be using procedures to add and get product data from database. Please check the previous post on how to create Login API in .NET Core. Let’s begin.
In your SQL database create a tables using below scripts:
Products table:
CREATE TABLE tblProducts( [Id] [int] IDENTITY(1,1) NOT NULL, [Product] [nvarchar](max) NOT NULL, [CategoryId] [int] NOT NULL, [Price] [float] NOT NULL )
Category table:
CREATE TABLE tblCategory( [Id] [int] IDENTITY(1,1) NOT NULL, [Category] [nvarchar](50) NOT NULL )
Create a SQL Procedure which will help us in adding data into Products table like below:
CREATE proc sp_products ( @product nvarchar(max)=null, @price float=null, @categoryid int=null, @stmt nvarchar(50) ) as if(@stmt='add') begin Insert into tblProducts (Product,CategoryId,Price) values (@product,@categoryid,@price) end if(@stmt='get') begin Select p.Id, p.Product,p.CategoryId,p.Price,c.Category from tblProducts p left outer join tblCategory c on c.Id = p.CategoryId end GO
Now with SQL tables and procedures in place, let’s start with creating the .net core web APIs to add the product data.
My .NET Core API project name is “Web_Project”, so you will see this namespace in the classes mentioned over here (just to avoid any possible confusions).
In your .NET Core’s project add a controller like below:
My-Project-Name > Controllers > ProductController.cs:
using Microsoft.AspNetCore.Mvc;
using System.Data.SqlClient;
using Web_Project.Data_Service;
using Web_Project.Models;
namespace Web_Project.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class ProductController : ControllerBase
{
SqlConnection conn;
private readonly IConfiguration _configuration;
public ProductController(IConfiguration configuration)
{
_configuration = configuration;
}
[HttpPost, Route("[action]", Name = "add")]
public APIResponse addProducts(Products prod)
{
APIResponse res = new APIResponse();
ProductService productService = new ProductService(_configuration);
string result = productService.addProducts(prod);
if(result.ToLower().Equals("success"))
{
res.message = "Product added successfully";
res.result = true;
var product = productService.getProducts();
res.response = product;
}
else
{
res.message = result;
res.result = false;
}
return res;
}
[HttpGet, Route("[action]", Name = "get")]
public APIResponse getProducts()
{
APIResponse res = new APIResponse();
ProductService productService = new ProductService(_configuration);
var product = productService.getProducts();
if (product!=null && product.Count>0)
{
res.response = product;
res.message = "success";
res.result = true;
}
else
{
res.message = "No Products found";
res.result = false;
}
return res;
}
}
}
Now we have to create 2 Model classes, one for Product and another for APIResponses. Create a folder with name Models and add below classes.
My-Project-Name > Models > Products.cs:
namespace Web_Project.Models
{
public class Products
{
public int? productid { get; set; }
public string? product { get; set; }
public int? categoryid { get; set; }
public string? category { get; set; }
public double? price { get; set; }
}
public class Category
{
public int categoryid { get; set; }
public string category { get; set; }
}
}
My-Project-Name > Models > APIResponse.cs:
namespace Web_Project.Models
{
public class APIResponse
{
public bool result { get; set; }
public string message { get; set; }
public object response { get; set; }
}
}
Create a new folder in your project with Data-Service. We will add a class inside this folder which we have methods to have and get data from SQL Database. This is just for code-reusability.
My-Project-Name > Data-Service > ProductService.cs:
using System.Data;
using System.Data.SqlClient;
using Web_Project.Models;
namespace Web_Project.Data_Service
{
public class ProductService
{
SqlConnection conn;
private readonly IConfiguration _configuration;
public ProductService(IConfiguration configuration)
{
_configuration = configuration;
}
public string addProducts(Products prod)
{
try
{
if (prod != null && prod.categoryid > 0
&& prod.price > 0)
{
conn = new SqlConnection(_configuration["ConnectionStrings:sql-conn"]);
using (conn)
{
SqlCommand cmd = new SqlCommand("sp_products", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@stmt", "add");
cmd.Parameters.AddWithValue("@product", prod.product);
cmd.Parameters.AddWithValue("@price", prod.price);
cmd.Parameters.AddWithValue("@categoryid", prod.categoryid);
conn.Open();
cmd.ExecuteNonQuery();
}
return "success";
}
else
{
return "Please enter all fields";
}
}
catch (Exception ex)
{
return ex.Message.ToString();
}
}
public List<Products> getProducts()
{
List< Products> lstProducts = new List<Products>();
try
{
conn = new SqlConnection(_configuration["ConnectionStrings:sql-conn"]);
using (conn)
{
SqlCommand cmd = new SqlCommand("sp_products", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@stmt", "get");
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
Products products = new Products();
products.product = dr["Product"].ToString();
products.categoryid = Convert.ToInt32(dr["categoryid"].ToString());
products.price = Convert.ToDouble(dr["price"].ToString());
products.category = dr["category"].ToString();
lstProducts.Add(products);
}
}
}
}
catch (Exception ex)
{
}
return lstProducts;
}
}
}
Now we will add the SQL ConnectionString in appsettings.json which we will use to connect our API with the SQL Database.
My-Project-Name > appsettings.json:
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"ConnectionStrings": {
"sql-conn": "Data Source=DESKTOP-T0RTF1A;Initial Catalog=SampleDB;Persist Security Info=True;User ID=sa;Password=1234",
},
"AllowedHosts": "*"
}
Now run your project.
asp-dot-net-core-api-how-to-add-data-to-ms-sql-database
Sample Request:
API URL – Post Req: https://localhost:7223/api/Product/addProducts/
Request Parameters:
{
"product": "OnePlus Nord CE 2",
"categoryid": 2,
"price": 29999
}
Response:
{
"result": true,
"message": "Product added successfully",
"response": [
{
"productid": 1,
"product": "OnePlus Nord CE 2 Lite 5G",
"categoryid": 2,
"category": "Mobiles",
"price": 19999
},
{
"productid": 2,
"product": "OnePlus Nord CE 2",
"categoryid": 2,
"category": "Mobiles",
"price": 29999
}
]
}
To learn how to create login API in ASP.NET Core, please check below post:
In post is only about adding data into databse, I will create a post on using this API using Xamarin forms, Angular and other few front-end applications to learn how to consume the API. Please check the previous post on Xamarin forms where I have create a simple interface to use .NET Core Login API to create a very simple Login application.
Thank you.