How to call stored procedure that returns columns with spaces

asp.net-mvc c# entity-framework-6 stored-procedures

Question

I'm trying to call a stored procedure using entity 6.0. After looking to these solutions here and here, they didn't solve the problem. The issue is that one of the returned columns has a spaces in it (I'm unable to change the stored procedure). I've have tried the following with no success:

MyModel.cs

using System;
...

namespace ...
{
    public class MyModel
    {
        [Display(Name = "ID")]
        public string id{ get; set; }

        [Display(Name = "Number Of ID")]
        [Column("Number Of ID")]
        public int number_Of_ID { get; set; }

    }

    public class MyModelContext : DbContext
    {
        public DbSet<MyModel> MyModels{ get; set; }
    }
}

MyController.cs

using System;
...

namespace ...
{
    public class MyController : Controller
    {
        private MyEntities db = new MyEntities ();

        public ActionResult Index()
        {

            var query = @"exec usp_getIds";

            var results = db.Database.SqlQuery<MyModel>(query);
            return View(results);
        }
    }

When debugging the code, my model has a value for id but not for number_Of_ID. The value is always 0. I've also tried changing the datatype to a string and it returns null. Please help.

1
0
5/23/2017 12:19:23 PM

Accepted Answer

While I was not able to use a DBContext, I found a solution using SQLDataAdapter. I was able to make the call successfully even with columns that had spaces. The following was changed in the controller:

MyController.cs

using System;
...

namespace ...
{
    public class MyController : Controller
    {   
        public ActionResult Index()
        {
            var myResultsList = new List<MyModel>();
            string connectionString = ConfigurationManager.AppSettings["myDBConn"];
            var conn = new SqlConnection(connectionString);
            conn.Open();
            string query = @"usp_getIds";
            using (var sqlAdpt = new SqlDataAdapter(query, conn))
            {
                sqlAdpt.SelectCommand.CommandType = CommandType.StoredProcedure;
                var results = new DataSet();
                sqlAdpt.Fill(results);
                myResultsList= results.Tables[0].AsEnumerable().
                Select(dataRow => new MyModel
                    {
                        id = dataRow.Field<string>("ID"),
                        numberOfID = dataRow.Field<int>("Number Of ID")
                    }).ToList();

            }
            return View(myResultsList);
        }
    }
1
6/24/2016 6:02:26 PM

Popular Answer

Do you mean there are spaces in a column name or in a value?

For a stored procedure usp_getIds, you should be able to do:

List<usp_getIds_Result> results = db.usp_getIds().ToList();

The class usp_getIds_Result is automatically created when you add the procedure to the model.

See if it helps to try this.



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