How to insert Asp.net form data into database using Web API 2 in Asp.net MVC 5
Introduction:
In my previous article, we have seen How to retrieve data from database using Web API in MVC, Today in this article, I will explain How to insert Asp.net form data using Web API into the database.
In this example, we will use Asp.net MVC 5 for insert data into database using Asp.net Web API into MVC
Follow these steps in order to implement “Insert Asp.net form data using Web API into database”
Step1: Create a table and Stored Procedure.
In this example, I have created fallowing table and stored procedure for insert customer information
CREATE TABLE [dbo].[tbl_Customer_master] (
[Customer_id] INT IDENTITY (1, 1) NOT NULL,
[FullName] NVARCHAR (MAX) NULL,
[Email] NVARCHAR (100) NOT NULL,
[Password] NVARCHAR (MAX) NULL,
[Mobile_no] NVARCHAR (MAX) NULL,
[Created_At] DATE NULL,
PRIMARY KEY CLUSTERED ([Email] ASC)
);
The design of the table look like this as follows:
Now create a stored procedure to insert data into the table as:
CREATE proc Sp_Customer_Add
@FullName NVARCHAR (MAX) ,
@Email NVARCHAR (MAX) ,
@Password NVARCHAR (100) ,
@Mobile_no NVARCHAR (MAX) ,
@Creatd_At NVARCHAR (MAX)
as
begin
insert into tbl_Customer_master values (
@FullName ,
@Email ,
@Password ,
@Mobile_no ,
@Creatd_At
)
end
Run above script in MS SQL Server and click to execute button
Step2: Create New Project.
Go to File > New > Project > Web > Asp.net MVC web project > Enter Application Name > Select your project location > click to add button > It will show new dialog window for select template > here we will select Web API project > then click to ok
Step3: Add Connection string in web.config file
Here I have added connectionstring in web.config file under Configuration section as follows
<connectionStrings>
<add name="con" connectionString="Data Source=.;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Step4: Add Model for Customer table.
Right click on Models> Add Class > Enter class name > click to Add button
Write the fallowing properties as follows
public class customer
{
public int Custimer_id { get; set; }
public string Fullname { get; set; }
public string Email { get; set; }
public string Password { get; set; }
public string Mobileno { get; set; }
public string Createdat { get; set; }
}
Step5: Create Database Access layer.
Go to Solutions Explorer > right click on project solution> Add New Folder > Enter Folder name (Here I rename it as ‘database_access_layer’) > right click on folder > Add new class.
Now add the fallowing method into Database access class named Add_Customer that does all the database access related activities.
public void Add_Customer(customer cs)
{
SqlCommand com = new SqlCommand("Sp_Customer_Add", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@FullName",cs.Fullname);
com.Parameters.AddWithValue("@Email",cs.Email);
com.Parameters.AddWithValue("@Password",cs.Password);
com.Parameters.AddWithValue("@Mobile_no",cs.Mobileno);
com.Parameters.AddWithValue("@Creatd_At",cs.Createdat);
con.Open();
com.ExecuteNonQuery();
con.Close();
}
The entire db.cs class file will be follows
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using InsertdataWebapi.Models;
namespace InsertdataWebapi.database_access_layer
{
public class db
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
customer cs = new customer();
public void Add_Customer(customer cs)
{
SqlCommand com = new SqlCommand("Sp_Customer_Add", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@FullName",cs.Fullname);
com.Parameters.AddWithValue("@Email",cs.Email);
com.Parameters.AddWithValue("@Password",cs.Password);
com.Parameters.AddWithValue("@Mobile_no",cs.Mobileno);
com.Parameters.AddWithValue("@Creatd_At", DateTime.Now);
con.Open();
com.ExecuteNonQuery();
con.Close();
}
}
}
Step6: Create POST method
Create a Post method in the Asp.net Web API Controller Class.
Open the ValuesController, delete the existing method and create the AddCustomer method and call the database access class (db.cs) method as the fallowing:
public class ValuesController : ApiController
{
database_access_layer.db dblayer = new database_access_layer.db();
[HttpPost]
public IHttpActionResult AddCustomer([FromBody]customer cs)
{
try
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
dblayer.Add_Customer(cs);
return Ok("Success");
}
catch (Exception)
{
return Ok("Something went wrong, try later");
}
}
}Here ValuesController class is inherited from APiController class and we have created the method AddCustomer that calls a method named Add_Customer. The entire ValuesContoller class will be followed:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using InsertdataWebapi.Models;
namespace InsertdataWebapi.Controllers
{
public class ValuesController : ApiController
{
database_access_layer.db dblayer = new database_access_layer.db();
[HttpPost]
public IHttpActionResult AddCustomer([FromBody]customer cs)
{
try
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
dblayer.Add_Customer(cs);
return Ok("Success");
}
catch (Exception)
{
return Ok("Something went wrong, try later");
}
}
}
}
Step7: Configure Asp.net Web API routing
We need to configure the routing of the incoming request. Let us create WebApiConfig.cs by right-clicking on the App_start folder and create the fallowing method as follows:
public static void Register(HttpConfiguration config)
{
config.MapHttpAttributeRoutes();
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }
);
}
The entire WebApiController.cs will be as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;
namespace InsertdataWebapi
{
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
config.MapHttpAttributeRoutes();
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }
);
}
}
}
Step8: Call Asp.net Web API Controller method
Call Asp.net Web API Controller method from the hHTMLpage using JSON.
Now we need to call to Web API Controller method from HTML page. To do this we need to create a JSON method using Jquery as follows:
$(function () {
$("#btnAdd").click(function () {
var PersonalDetails = {
"FullName": "vivek",
"Email": "test6@gmail.com",
"Password": "1234",
"Mobileno": "9876543210"
};
$.ajax({
type: "POST",
url: '/api/values/AddCustomer',
data: JSON.stringify(PersonalDetails),
contentType: "application/json;charset=utf-8",
success: function (data, status, xhr) {
alert("The result is : " + data);
},
error: function (xhr) {
alert(xhr.responseText);
}
});
});
});
Now the entire test.html page will be followed:
<!DOCTYPE html>
<html >
<head>
<title></title>
<script src="/Scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript">
$(function () {
$("#btnAdd").click(function () {
var PersonalDetails = {
"FullName": "vivek",
"Email": "test6@gmail.com",
"Password": "1234",
"Mobileno": "9876543210"
};
$.ajax({
type: "POST",
url: '/api/values/AddCustomer',
data: JSON.stringify(PersonalDetails),
contentType: "application/json;charset=utf-8",
success: function (data, status, xhr) {
alert("The result is : " + data);
},
error: function (xhr) {
alert(xhr.responseText);
}
});
});
});
</script>
</head>
<body>
<input id="btnAdd" type="button" value="Add" />
</body>
</html>
Also, e need to add a Jquery library reference so don’t forgot to add it.
Now the entire solution explorer will look as follows:
Step 10: Run Application.
We have done all steps, now it’s time to run the application
