Navy Navy - 3 months ago 48
ASP.NET (C#) Question

Stored Procedure using Dapper in MVC 4

I am trying to do CRUD operation in MVC using Dapper and stored procedure but I am not able to return result from model to controller due to conversion error which I am not able to solve. Please can anyone help what should I return as my result

This is my controller

public ActionResult AllMobileList()
{
MobileMain MM = new MobileMain();
return View(MM.AllMobileListing().ToList());
}

[HttpGet]
public ActionResult Edit(string MobileID)
{
MobileMain MM = new MobileMain();
return View(MM.GetMobileList(MobileID));
}


Model

public IEnumerable<TBMobileDetails> AllMobileListing()
{
var para = new DynamicParameters();
para.Add("@Type", 1);
var result= con.Execute("Sp_MVCDapper", para, commandType: CommandType.StoredProcedure).ToString();

return result; // Error happens here
}

public TBMobileDetails GetMobileList(string MobileId)
{
var para = new DynamicParameters();
para.Add("@Type", 2);
para.Add("@MobileId",Convert.ToInt32(MobileId));
var result = con.Execute("Sp_MVCDapper", para, commandType: CommandType.StoredProcedure).ToString();

return result; // Error happens here
}


Error:


Cannot implicitly convert type 'string' to 'System.Collections.Generic.IEnumerable'


I know its a very common error and I am doing some stupid mistake.

Answer

You should use Dapper's Query<T> extension method to get the results of a stored procedure call - if the SP uses a select statement to return data.

Query<T> returns an IEnumerable<T>, so you can simply use for IEnumerable<TBMobileDetails> AllMobileListing():

return con.Query<TBMobileDetails>(
    "Sp_MVCDapper", para, commandType: CommandType.StoredProcedure)

and for TBMobileDetails GetMobileList(string MobileId)

var list = con.Query<TBMobileDetails >(
    "Sp_MVCDapper", para, commandType: CommandType.StoredProcedure);

return list.Single(); // assuming that the SP only returns a single item

And as a remark: if your parameter is a number then don't use a string type. It only causes headaches later.