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 = 'email@example.com' " & 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
Do more with