troubleshooting Question

Classic ASP to ASP.NET MVC : Queries and Stored Procedures

Avatar of blueskybooking
blueskybookingFlag for Canada asked on
ASP.NET
3 Comments2 Solutions635 ViewsLast Modified:
I have a Classic ASP website which I am upgrading.

Examples

Basic Connection:
Dim objConnection As SqlConnection = New SqlConnection(strConnection)
Dim objCommand As New SqlCommand
Dim objReader As SqlDataReader

 objConnection.Open()

objCommand.Connection = objConnection

objCommand.CommandText = strSQL
objCommand.CommandTimeout = 0

objReader = objCommand.ExecuteReader()

*** objReader ... ToList() ***

objReader.Close()

objCommand.Dispose()

objCommand = Nothing
objReader = Nothing
Searching a table -- SQL Query:
strSQL = _
"SELECT tblCustomers.CustomerID, tblCustomers.FirstName, tblCustomers.LastName, tblCustomers.Email, " & vbCrLf & _
"  ( SELECT COUNT(*) " & vbCrLf & _
"    FROM tblOrders " & vbCrLf & _
"    WHERE tblOrders.CustomerID = tblCustomers.CustomerID ) As Orders " & vbCrLf & _
"FROM tblCustomers " & vbCrLf & _
"WHERE tblCustomers.CompanyID = 12 " & vbCrLf & _
"AND tblCustomers.LastName LIKE 'Smith%' " & vbCrLf & _
"ORDER BY tblCustomers.FirstName ASC, tblCustomers.LastName ASC " & vbCrLf
Updating a table -- SQL Stored Procedure:
strSQL = _
"EXECUTE dbo.sp_update_customer " & vbCrLf & _
"  @intCompanyID = 12," & vbCrLf & _
"  @intCustomerID = 12345," & vbCrLf & _
"  @chrFirstName = 'Jim'," & vbCrLf & _
"  @chrLastName = 'Smith'," & vbCrLf & _
"  @chrEmail = 'jimsmith@nowhere.com' " & vbCrLf
Using ASP.NET MVC, the replacement looks to be Entity Framework which doesn't fit well for many of the behind the scenes scripting being done by SQL Server.

I'm unclear if this is simply not done in MVC (as all the examples make use of the EF) or if there is a way to integrate the code into the following Controller?
    Function Index() As ActionResult
      Return View(*** QUERY ***)
    End Function

    Function Create() As ActionResult
      Return View()
    End Function

    <HttpPost()> _
    Function Create(ByVal customer As Customer) As ActionResult
      If ModelState.IsValid Then
        *** STORED PROCEDURE ***
        Return RedirectToAction("Index")
      End If

      Return View(customer)
    End Function
I would like to remain with ADO.NET but I can appreciate that might not be recommended.
ASKER CERTIFIED SOLUTION
Bardobrave

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros