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

blueskybooking
blueskybooking used Ask the Experts™
on
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

Open in new window

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

Open in new window

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

Open in new window

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

Open in new window

I would like to remain with ADO.NET but I can appreciate that might not be recommended.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Have you considered using LINQ2SQL?

I personally  use it on my ASP.NET MVC projects and feel it better than Entity Framework (however it would mean a lot of work to migrate from classic ASP ADO).

Author

Commented:
I was hoping to expedite a quick upgrade to the site.  I am researching if ASP.NET WebForms is the best way to do that as it is closer to the original code.
I supose you'd already seen this site, but here is a good example about using directly SQL queries with ADO.NET

http://www.nakov.com/blog/2011/01/12/native-sql-queries-in-entity-framework/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial