Suny Saju Suny Saju - 4 months ago 31
AngularJS Question

How to write stored procedure in WEB API?

I don't know how to write stored procedure in WEB API

I called serivice

var promisePost = crudService.candidatePost(Candidates);
promisePost.then(function (pl) {
alert("Sucessfully Inserted")

}, function (err) {
alert("NOt Inserted")
});


my app.js is

MyApp.service('crudService', function ($http, RESOURCES) {
this.candidatePost = function (Candidate) {
var request = $http({
method: "post",
url: RESOURCES.baseUrl + "saveCandidate",
data: Candidate
});
return request;
}


});
my controller is

[HttpPost]
[Route("api/saveCandidate")]

public HttpResponseMessage AddDetail(Candidate ct)
{
SqlConnection con = new SqlConnection(Constant.ConnectionString);
SqlCommand cmd = new SqlCommand();
int rowInserted = 0;

try
{

cmd = new SqlCommand("sp_Insert_tblCandidate", con);
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", ct.Name);
cmd.Parameters.AddWithValue("@Gender", ct.Gender);
cmd.Parameters.AddWithValue("@Dob", ct.Dob);
cmd.Parameters.AddWithValue("@Mob", ct.Mob);
cmd.Parameters.AddWithValue("@EntryDate", ct.EntryDate);
cmd.Parameters.AddWithValue("@Note", ct.Note);
cmd.Parameters.AddWithValue("@Emial", ct.Emial);
cmd.Parameters.AddWithValue("@Address", ct.Address);
rowInserted = cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception obj)
{
if (rowInserted != 1)
{
var message = obj.Message;// string.Format("Insertion Of Data is not Succefully Executed");
HttpError err = new HttpError();
return Request.CreateResponse(HttpStatusCode.NotFound, err);

}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{

con.Close();
}
}
var alertmessage = string.Format("Insertion Of Data is Succefully Executed");
return Request.CreateResponse(HttpStatusCode.OK, alertmessage);

}


My stored Procedure is

IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = object_id(N'[dbo].[sp_Insert_tblCandidate]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE [dbo].[sp_Insert_tblCandidate]
END

GO
Create procedure [dbo].[sp_Insert_tblCandidate]
(@Name varchar(50) ,
@Gender char(1),
@dob varchar(25),
@Mob varchar(15),
@EntryDate varchar(50),
@Note varchar(100),
@Emial varchar(50),
@Address varchar(50)
)As
Begin
INSERT INTO [dbo].[tblCandidate] VALUES (@Name,@Gender,@dob,@Mob,@EntryDate,@Note,@Emial,@Address);
SELECT SCOPE_IDENTITY() as CandidateId;
end
GO


My stored procedure purpose is insert data & return last inserted id. In this table id set to auto increment.Anyone can help me?

Answer

You are returning the new Id so you shouldn't be using

cmd.ExecuteNonQuery()

But something that can handle the return value. Try changing it to

 rowInserted = (int) cmd.ExecuteScalar();

Also right now you are checking in the catch the rowInserted which doesn't makes much sense.