CODEWITHSUNDEEP
asp.net-mvclinqasp.net-mvc-4razorviewmodel
Ganesh Raj
Ganesh Raj

Reputation: 15

Linq MVC with view model

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.

Upvotes: 0

Views: 154

Answers (1)

Brad Christie
Brad Christie

Reputation: 101604

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 */
}

Upvotes: 2

Related Questions