Avatar of Karen Wilson
Karen Wilson
Flag for United States of America

asked on 

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;
      SELECT *
      from tblCrazyLandDevel
      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!
Visual Basic.NETMicrosoft SQL Server

Avatar of undefined
Last Comment
Karen Wilson

8/22/2022 - Mon