Link to home
Start Free TrialLog in
Avatar of blueskybooking
blueskybookingFlag for Canada

asked on

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

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.
SOLUTION
Avatar of Bardobrave
Bardobrave
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of blueskybooking

ASKER

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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial