Avatar of blueskybooking
blueskybooking
Flag 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.
ASP.NET

Avatar of undefined
Last Comment
Bardobrave

8/22/2022 - Mon
SOLUTION
Bardobrave

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Bardobrave

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes