How do I post SQL data using submit button in asp.net c# razor

Question

Thanks in advance.

Can anyone please help me get this button working so I can display a row from the Northwind database 'only' when the button is clicked.

I have been doing webgrid tutorials; however, in each tutorial the grid/ webgrid is always showing before it is searched.

It would be great if I could learn how to use a button to show data from a row in the Northwind database in a list/label/table or grid 'only' when a form is submitted.

Keeping it simple, I have this:

@{
    Layout = "~/_Layout.cshtml";
    Page.Title = "Compare";

   var db = Database.Open("Northwind");
   var selectedData = db.Query("SELECT * FROM Products");

<h1>Products</h1>

 <form action="" method="post">
    <div>
      <input type="Submit" value="Get Products" /><br/> <br/> 
    </div>
 </form>

    <ol>
       @foreach(var row in selectedData)
       {
        <li><a href="#">@row.ProductName</a></li>
       }
     </ol>
  }

EDIT: In Mike's tutorial (at: http://www.mikesdotnetting.com/Article/211/Adding-A-Footer-To-The-Razor-WebGrid) Mike uses a webgrid in Razor to access the Northwind database and display orders.

I would like to use form data to access the database with a submit button; is this possible please? If so how? Thanks.

Answer 1

If you're using ASP.NET MVC, the point of using it is to divide the application in three kind of components... The view which is what you have up there, the controller which is the one that handles your requests from and to the models, and finally the models which is where you should place your query in the first place.

There's no point of using MVC if you're not using it all.

Answer 2

Thanks to everyone for your comments. I managed to add a simple button to the Razor code from Mikes tutorial (mentioned in my above post) and it works.

Here is Mike's code with the button I added:

@{
    Layout = "~/_Layout.cshtml";
    Page.Title = "WebGrid.Table method";

    var db = Database.Open("Northwind");
    var sql = "SELECT OrderId FROM Orders";
    var orders = db.Query(sql).Select(o => new SelectListItem {
        Value = o.OrderId.ToString(), 
        Text = o.OrderID.ToString(),
        Selected = o.OrderID.ToString() == Request["OrderID"]
    });

    WebGrid grid = null;
    var orderTotal = 0f;

    if(IsPost){
        sql = @"SELECT p.ProductName, o.UnitPrice, o.Quantity, 
                (o.UnitPrice * o.Quantity) - (o.UnitPrice * o.Quantity * o.Discount) As TotalCost 
                FROM OrderDetails o INNER JOIN Products p ON o.ProductID = p.ProductID 
                WHERE o.OrderID = @0";

        var orderDetails = db.Query(sql, Request["OrderID"]);
        orderTotal = orderDetails.Sum(o => (float)o.TotalCost);

        grid = new WebGrid(orderDetails, canPage: false, canSort: false);
    }
}
<h1>@Page.Title</h1>
<form method="post">
    @Html.DropDownList("OrderID", orders)
    <input type="Submit" value="Get Products" /><br/> <br/> 
</form>

@if(grid != null){
    @grid.Table(
            columns: grid.Columns(
                grid.Column("ProductName", "Product", style: "_220"),
                grid.Column("UnitPrice", "Price", style: "_60", format: @<text>@item.UnitPrice.ToString("c")</text>),
                grid.Column("Quantity", style: "_90"),
                grid.Column("TotalCost", "Total Cost", style: "_90", format: @<text>@item.TotalCost.ToString("c")</text>)
            ), 
            footer: @<table class="footer">
                         <tr>
                             <td class="_220">Total</td>
                             <td colspan="2" class="_150">&nbsp;</td>
                             <td class="_90">@orderTotal.ToString("c")</td>
                         </tr>
                    </table>);
}

Answer 3

Here's a crude example I hacked together.

When you first load the page at http://yourDomain/Product/Index, you get nothing but a Text Box and Search Button. When you enter in a few letters into the TextBox and click the Search Button, you get a list of the Northwind Products that start with the inputted letters.

Here is the contents of the ~/Controllers/ProductController.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcApplication1.Controllers
{
    public class ProductController : Controller
    {
        //
        // GET: /Product/

        public ActionResult Index(String startString)
        {
            List<Models.Product> theProducts;
            using (
                Models.NorthwindEntities NWC = 
                new Models.NorthwindEntities()
            )
            {
                 theProducts =
                    (from e in NWC.Products
                     where e.ProductName.StartsWith(startString)
                    select e).ToList();
            }
            return View(theProducts);
        }

    }
}

and here is the contents of the ~/Views/Product/Index.cshtml file:

@model List<MvcApplication1.Models.Product>

@{
    ViewBag.Title = "Product";
}

<h2>Product</h2>

@* Use a very crude means to dump matching products out *@ 
@foreach (MvcApplication1.Models.Product p in Model) {
    @Html.Raw(p.ProductName + "<br />");
}
<br />
@* See this for example use of form and submit button
http://weblogs.asp.net/raduenuca/archive/2011/03/13/asp-net-mvc-cascading-dropdown-lists-tutorial-part-2-cascading-using-normal-form-post-html-beginform-helper.aspx *@
@using (Html.BeginForm("Index", "Product", FormMethod.Get)) { 
    @Html.Raw("Enter Search Text Here:<br />");
    <input type="text" name="startString" id="txtSearch" /><br />
    <input type="submit" value="Click Hear to Search" />
}

Answer 4

You can connect to a container that connects directly via SQL Server data connection using the onclick event in your input or button type="submit". This button calls the class Insert method, the container class would be from what I am showing: ContYardDogAdmin

onclick=Insert(ContYardDogAdmin ContYardDogData)

Example: <button id="NewRowSubmitButton" runat="server" type="submit" onclick=Insert(ContYardDogAdmin ContYardDogData)>Submit