locked
How would write a Sql class to populatehydrate my model class RRS feed

  • Question

  • User-14642827 posted

    My end goal is to display a view.  How do hydrate a class here is my Model:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    
    namespace TestMvc.Models
    {
        public class CustomerSummary
        {
            public string ContactName { get; set; }     // Customer table
            public string City { get; set; }            // Customer table
            public string PostalCode { get; set; }      // Order table
            public string ShipName { get; set; }        // Order table
            public string ProductName { get; set; }     // Product table
            public bool Discontinued { get; set; }      // product table
    
        }
    }

    My controller is:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using TestMvc.Models;
    
    namespace TestMvc.Controllers
    {
        public class CustomerSummaryController : Controller
        {
            //
            // GET: /CustomerSummary/
            private CustomerSummaries _customerSummaries = new CustomerSummaries();
    
            public ViewResult Index()
            {
                IEnumerable<CustomerSummary> summaries = _customerSummaries.GetAll();
                return View(summaries);
            }
    
        }
    }

    I'm using a Sql query to hydrate my class like:

    class CustomerSummaries
        {
            SqlConnection conn = new SqlConnection();
    
            public IEnumerable<CustomSummary> GetAll()
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("GetAll", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    SqlDataReader sdr;
                    conn.Open();
                    sdr = cmd.ExecuteReader();
                    while (sdr.Read())
                    {
                        if (sdr.IsDBNull(sdr.GetOrdinal("ContactName")) != true)
                        {
                            sdr["ContactName"].ToString();
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw;
                    //lblErrorMsg.Visible = true;
                    //lblErrorMsg.Text += "<br><b>getProjectLead_Error: </b> " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            } 
        }

    Here is my stored procudure:

    ALTER PROCEDURE [dbo].[GetAll]
    
    AS
    BEGIN
     SET NOCOUNT ON;
     SELECT * FROM Customers
     SELECT * FROM Orders
     SELECT * FROM Products
    END

    now my error is: not all code path return value how can I change my GetAll method to correct the error?

    Sunday, March 30, 2014 12:07 PM

Answers

  • User-933407369 posted

    ALTER PROCEDURE [dbo].[GetAll]
    
    AS
    BEGIN
     SET NOCOUNT ON;
     SELECT * FROM Customers
     SELECT * FROM Orders
     SELECT * FROM Products
    END

    The Stored PROCEDURE returns three tables, if you want to use the SqlCommand  object , you don't use ExecuteReader(). the best way  is that you change your Sql statement and make it return a single table result.

    >>1. i would suggest you try use DataSet and fill the DataSet. Then the DataSet would get three tables , default Table0,Table1,Table2 etc.

    >>2. If you use Linq to Sql, i would suggest you try the codes:

                     // 6. Execute the command and materialize the car entities
                       using ( IDataReader dataReader = oaCommand.ExecuteReader() )
                       {
                           List<Customer> cars = dbContext.Translate<Customer>( dataReader as DbDataReader ).ToList();
    
                           // 7. Advance to the next result sets
                           dataReader.NextResult();
                           List<Order> Orders = dbContext.Translate<Order>( dataReader as DbDataReader ).ToList();
    ... }

    check here:

    How to: Execute Stored Procedures Returning Multiple Result Sets

    >>3. If you use Entity Framework, i would suggest you try the codes:

      // 6. Execute the command and materialize the car entities
                        using (IDataReader dataReader = oaCommand.ExecuteReader())
                        {
                            // ((IObjectContextAdapter)db)
                            //            .ObjectContext
                            //            .Translate<Blog>(reader, "Blogs", MergeOption.AppendOnly);
                            List<Customer> cars = ((IObjectContextAdapter)dbContext).ObjectContext.Translate<Customer>(dataReader as DbDataReader).ToList();
                           // List<Customer> cars = dbContext.Translate<Customer>(dataReader as DbDataReader).ToList();
    
                            // 7. Advance to the next result sets
                            dataReader.NextResult();
                            List<Order> Orders = ((IObjectContextAdapter)dbContext).ObjectContext.Translate<Order>(dataReader as DbDataReader).ToList();
    
                            dataReader.NextResult();
                            List<Product> Products = ((IObjectContextAdapter)dbContext).ObjectContext.Translate<Product>(dataReader as DbDataReader).ToList();
    
                           // List<Category> categories = dbContext.Translate<Category>(dataReader as DbDataReader).ToList();
                        }

    check here:

    How to: Stored Procedures with Multiple Result Sets

    http://msdn.microsoft.com/en-us/data/jj691402.aspx

    Hope it helps you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 31, 2014 4:02 AM

All replies

  • User-933407369 posted

    ALTER PROCEDURE [dbo].[GetAll]
    
    AS
    BEGIN
     SET NOCOUNT ON;
     SELECT * FROM Customers
     SELECT * FROM Orders
     SELECT * FROM Products
    END

    The Stored PROCEDURE returns three tables, if you want to use the SqlCommand  object , you don't use ExecuteReader(). the best way  is that you change your Sql statement and make it return a single table result.

    >>1. i would suggest you try use DataSet and fill the DataSet. Then the DataSet would get three tables , default Table0,Table1,Table2 etc.

    >>2. If you use Linq to Sql, i would suggest you try the codes:

                     // 6. Execute the command and materialize the car entities
                       using ( IDataReader dataReader = oaCommand.ExecuteReader() )
                       {
                           List<Customer> cars = dbContext.Translate<Customer>( dataReader as DbDataReader ).ToList();
    
                           // 7. Advance to the next result sets
                           dataReader.NextResult();
                           List<Order> Orders = dbContext.Translate<Order>( dataReader as DbDataReader ).ToList();
    ... }

    check here:

    How to: Execute Stored Procedures Returning Multiple Result Sets

    >>3. If you use Entity Framework, i would suggest you try the codes:

      // 6. Execute the command and materialize the car entities
                        using (IDataReader dataReader = oaCommand.ExecuteReader())
                        {
                            // ((IObjectContextAdapter)db)
                            //            .ObjectContext
                            //            .Translate<Blog>(reader, "Blogs", MergeOption.AppendOnly);
                            List<Customer> cars = ((IObjectContextAdapter)dbContext).ObjectContext.Translate<Customer>(dataReader as DbDataReader).ToList();
                           // List<Customer> cars = dbContext.Translate<Customer>(dataReader as DbDataReader).ToList();
    
                            // 7. Advance to the next result sets
                            dataReader.NextResult();
                            List<Order> Orders = ((IObjectContextAdapter)dbContext).ObjectContext.Translate<Order>(dataReader as DbDataReader).ToList();
    
                            dataReader.NextResult();
                            List<Product> Products = ((IObjectContextAdapter)dbContext).ObjectContext.Translate<Product>(dataReader as DbDataReader).ToList();
    
                           // List<Category> categories = dbContext.Translate<Category>(dataReader as DbDataReader).ToList();
                        }

    check here:

    How to: Stored Procedures with Multiple Result Sets

    http://msdn.microsoft.com/en-us/data/jj691402.aspx

    Hope it helps you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 31, 2014 4:02 AM
  • User-14642827 posted

    I put this code in my sql class correct!

    Monday, March 31, 2014 5:48 PM