how to pass two parameters to call a stored procedure through c# mvc function

c# entity-framework stored-procedures

Question

I can do it easily when I need to pass a single parameter as follows:

 public ProjectsModel GetProjectListBySearch(int projectId)
    {
        try
        {
            using (_context = new Exo_ADBEntities())
            {
                var getdetailprojectlist = _context.Database.SqlQuery<ProjectsModel>("exec dbo.[GetProjectListByID] @ProjectID", new SqlParameter("@ProjectID", projectId)).FirstOrDefault();
                return getdetailprojectlist;
            }

        }
        catch (Exception)
        {

            throw;
        }
    }

This works nicely but when I try to do the same kind of thing but passing two parameters I find syntax error called invalid parameter Please help me to do this.Code when using two parameters are as follows

 public List<ProjectsModel> GetProjectDetailsBySectorAndSubSector(int sectorid,int subsectorid)
    {
        try
        {
            using (_context = new Exo_ADBEntities())
            {
                var projectbysectorandsubsector = _context.Database.SqlQuery<ProjectsModel>("exec dbo.[GetProjectDetailsBySectorAndSubSector] @sectorId,@subSectorId", new SqlParameter("@sectorId, @subSectorId", sectorid, subsectorid)).ToList();
                return projectbysectorandsubsector;
            }

        }
        catch (Exception)
        {

            throw;
        }
    }
1
9
6/1/2014 3:54:18 AM

Accepted Answer

The problem is that you're trying to pass several parameters as a single object.

If you look at the signature of Database.SqlQuery you'll see this:

public DbRawSqlQuery<TElement> SqlQuery<TElement>(
string sql,
params Object[] parameters
)

This is too basic, but, when you see params in a C# function it means "any optional number of parameters". What this means is that you can pass as many parameters as needed. So, in your case, it's simply like this:

_context.Database.SqlQuery<ProjectsModel>(
  "exec dbo.[GetProjectDetailsBySectorAndSubSector] @sectorId,@subSectorId", 
  new SqlParameter("@sectorId", sectorid),
  new SqlParameter("@subSectorId", subsectorid)
).ToList()

Note that when you find params Object[], you can also pass an array instead of several params, i.e.

_context.Database.SqlQuery<ProjectsModel>(
  "exec dbo.[GetProjectDetailsBySectorAndSubSector] @sectorId,@subSectorId", 
  new Object[] { new SqlParameter("@sectorId", sectorid),
                 new SqlParameter("@subSectorId", subsectorid)}
).ToList()

Depending on how your code looks like this can be more convenient.

21
6/2/2017 1:07:44 PM

Popular Answer

There are a few options available to you.

Either pass the view model back to the controler, this is my personal preference.

Or setup a new route in global.asax that takes two parameters.

Or you can manually do the form post and pass the two parameters using jQuery inside the view



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow