php to asp.net: howto display sql results in a view

Firstly, im a PHP developer trying to get my head around asp.net.

so i have created a basic MVC project.

I have a query without the fields known (ie. select * from products) how do I:

1. execute in Controller - my attempt:

   public ActionResult getProducts() 
   {
   
       using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ToString()))
       {
           string sql = "select * from products";
   
           SqlCommand cmd = new SqlCommand(sql, cn);
           cn.Open();
           SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);              
       }
   
       return View();  
   }
   

2. how do i pass the results to a View and then loop through them like:

   foreach ($data as $key => $val) 
   {
       echo $key.' = '.$val.'<br>';
   }
   

please help as this is SOOOOO simple in PHP but seems to be very confusing in asp.net.

ps. sorry for the formatting.

开发者_运维百科

cheers, trav.

---

First you'll need a model class:

public class Product
{
    public string Name { get; set; }
    public int Price { get; set; }
    public int Id { get; set; }
} 

And in your controller:

public ActionResult getProducts() 
    {

                    var products = new List<Product>();

        using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ToString()))
        {
            string sql = "select * from products";

            SqlCommand cmd = new SqlCommand(sql, cn);
            cn.Open();
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 

                            //read the results
                            while(rdr.Read() )
                            {
                                //map or hydrate a new product
                                var p = new Product();
                                p.Name = rdr["Name"];
                                p.Id = Int.Parse(rdr["Id"]);
                                p.Price = Int.Parse(rdr["Price"]);

                                //add new product to list we created earlier
                                products.Add( p );
                            }            
        }

        return View(products);  
    }

And in the View:

     //make sure the page inherits from ViewPage<List<Product>>       

     <% foreach( var product in Model ) { %>
          <%= product.Name %>
     <% } %>

One caveat. Your doing things "the hard way" here by not using an ORM like Entity Framework or Nhibernate and not following best practices by not using a View Model. Thats fine for just learning of course, just something to be aware of. ;)