How do I convert vb.net code into a SQL stored procedure?
I recently asked the best method for me to speed up a process in an application. The answer was to convert it to a stored procedure. Let the server do the work, versus the application. I use LINQ to SQL in a Visual Studio 2013 windows forms application.
I am attaching the original code.
I never use stored procedures because the method I use works. So this is new to me.
First, I created a stored procedure and it works fine.
CREATE PROCEDURE [dbo].[sp_GetCrazyWeeks]
@NASA_Contract nvarchar(50) = NULL,
@Rev nvarchar(10) = NULL
Set nocount on;
where NASA_Contract = @NASA_Contract and Rev = @Rev
order by WeekEnding ASC
I used LINQ and added the SPROC to my dbml. Works fine.
I call it up in my application. Works fine.
Dim crzyWk = d.sp_GetCrazyWeeks(CStr(Me.NASA_ContractComboBox.SelectedItem), CStr(Me.ComboBoxRev.SelectedItem))
What I do in the app is "For Each" and "Next" each row I retrieve. I try it out in a simple thing and it works.
For Each x In crzyWk
Dim xTO As String = x.NASA_Contract
Dim xRev As String = x.Rev
Dim xWE As String = CStr(x.WeekEnding)
Next, inside this, I pull additional data from the server, as this is a compiling process and update many variables.
So I wrote some more SPROCs.
Dim lwpPlans = d.sp_LastWeeksPlans(CStr(xTO), CStr(xRev), CDate(xWE))
Dim thisWeekPlans = d.sp_ThisWeeksPlans(CStr(xTO), CStr(xRev), CDate(xWE))
Now I am stuck because I don't know how to use was I am pulling. It's there, I can see it, but what to do. I usually do it like this, but something similar is not working.
planLab = thisWeekPlans.Item(0).Labor
And I am also thinking, what is the point between the SPROC and what I am currently doing?? Seriously, it doesn't seem to be any faster. So I must be approaching the SPROC-thing wrong. Should I be SPROC-ing exclusively in SQL Server and just passing parameters through the application?
Any suggestions, direction, links would be appreciated.
Many thanks! getFY13.docx