how to pass two parameters to call a stored proced

2020-08-17 05:49发布

问题:

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:

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.



回答2:

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