Searching a table -- SQL Query:
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
Updating a table -- SQL Stored Procedure:
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
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.
strSQL = _ "EXECUTE dbo.sp_update_customer " & vbCrLf & _ " @intCompanyID = 12," & vbCrLf & _ " @intCustomerID = 12345," & vbCrLf & _ " @chrFirstName = 'Jim'," & vbCrLf & _ " @chrLastName = 'Smith'," & vbCrLf & _ " @chrEmail = 'firstname.lastname@example.org' " & vbCrLf
I would like to remain with ADO.NET but I can appreciate that might not be recommended.
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
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”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.