CODEWITHSUNDEEP
c#asp.netasp.net-mvcasp.net-web-api
trx
trx

Reputation: 2157

Web API to call the stored Procedure and return Result

I am creating a Web API that accepts two input parameter called ACC. Created a stored procedure to insert or update the Account table in the SQL server. Account table has just two fields AccountID nvarchar(50) (primaryKey) and Cnt int

 CREATE PROCEDURE [dbo].[usp_InserUpadte] 
 @Account_TT AS Account_TT READONLY

 AS
 BEGIN
 SET NOCOUNT ON;

 BEGIN TRANSACTION;

 MERGE dbo.[Account] prj
 USING @Account_TT tt
 ON prj.AccountID = tt.AccountID
 WHEN MATCHED THEN UPDATE SET prj.Cnt = prj.Cnt+1
 WHEN NOT MATCHED THEN INSERT (AccountID,Cnt)
 VALUES (tt.AccountID, 1);

 COMMIT TRANSACTION;

Now I tried to connect to the SQL server not sure how to how would I call the stored procedure into the ASP.NET Web API application and pass the Account ID in it to create or updadte the table

namespace WebService.Controllers
{
public class CreationController : ApiController
{
    [HttpGet]
    public HttpResponseMessage Get(string ACC)
    {
        string strcon =  System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        SqlConnection DbConnection = new SqlConnection(strcon);

I know we can call the query directly like 

var strQuery = "SELECT * from ACCOUNT where ACC = :ACC"

But dont know how to call the above stored procedure and pass the Account Value. Any help is greatly appreciated.

Upvotes: 3

Views: 24245

Answers (2)

Saadi
Saadi

Reputation: 2237

Here is the complete working example. Please have a look on it.

string strcon = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
SqlConnection DbConnection = new SqlConnection(strcon);
DbConnection.Open();
SqlCommand command = new SqlCommand("[dbo].[usp_InserUpadte]", DbConnection);
command.CommandType = CommandType.StoredProcedure;

//create type table
DataTable table = new DataTable();
table.Columns.Add("AccountID", typeof(string));
table.Rows.Add(ACC);

SqlParameter parameter = command.Parameters.AddWithValue("@Account_TT", table);
parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "Account_TT";
command.ExecuteNonQuery();
DbConnection.Close();

Upvotes: 5

Hypnobrew
Hypnobrew

Reputation: 1140

To call a stored procedure you need to use a SqlCommand something like this:

string strcon =  System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
using (var connection = new SqlConnection(strcon)) {
    using (var command = new SqlCommand("usp_InserUpadte", connection)) {
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.Add("@Account_TT ", SqlDbType.NVarChar).Value = ACC;
      command.Open();
      var reader = command.ExecuteReader();

      // Handle return data here
    }
  }

Upvotes: 2

Related Questions