Linq MVC with view model

Question

I am very new to ASP.NET/MVC/LINQ. I have three table in my database and their corresponding models in my solution:

salesperson
  id
, name
, address
, dob

sales
  id
, shopid
, salespersonid
, productid
, AmountSale
, saleDate

Shop
  id
, shopName
, Location

I also have a stored procedure which will return following data for all sales person:

  SalesPersonid
, TotalAmount_Sale
, Most_Recent_ShopName
, Count_Of_sales_Lifetime
, count_of_sales_ThisMonth

How do I call the stored procedure using LINQ and display the data in the front end? All of the samples I've seen so far return a model which already exists. I am very confused please help.

Are you using any kind of ORM? (Entity Framework, NHibernate, etc.) — Brad Christie
– Brad Christie, Commented Sep 30, 2014 at 20:23
Typically you would not use stored procedures, you'd usually use the Entity Framework ORM. Calling stored procedures with Entity Framework is absolutely possible, but it is usually something you do rarely and only to optimize specific cases. — George Mauer
– George Mauer, Commented Sep 30, 2014 at 20:28

Brad Christie · Accepted Answer · 2014-09-30 20:36:36Z

Assuming you don't mind adding a library into the mix:

First, go install Dapper dot Net. Then, establish a model to store your results in:

class MySprocResult
{
  public int SalesPersonid { get; set; }
  public decimal TotalAmount_Sale { get; set; }
  public string Most_Recent_ShopName { get; set; }
  public int Count_Of_sales_Lifetime { get; set; }
  public int count_of_sales_ThisMonth { get; set; }
}

Next, establish a database connection and use Dapper dot net to return the results:

String connectionString = "connectionString";
using (IDbConnection db = new System.Data.SqlClient.SqlConnection(connectionString))
{
    // Open SQL connection
    db.Open();

    // Fetch results from stored procedure
    IEnumerable<MySprocResult> results = db.Query<MySprocResult>(
        "mySprocName",                           // stored procedure name

        //--- OPTIONAL
        //param: new { id = 123 },               // pass parameters if necessary
        //--- /OPIONAL

        commandType: CommandType.StoredProcedure // tell dapper it's a SPROC
    );

    // Close SQL connection
    db.Close();

    /* work with "results" here */
}

Collectives™ on Stack Overflow

Linq MVC with view model

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related