We help IT Professionals succeed at work.
Get Started

How do I convert vb.net code into a SQL stored procedure?

853 Views
Last Modified: 2016-08-15
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
AS
      Set nocount on;
      SELECT *
      from tblCrazyLandDevel
      where NASA_Contract = @NASA_Contract and Rev = @Rev
      order by WeekEnding ASC
RETURN 0

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

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
Comment
Watch Question
Programmer
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE