Stored Procedure Help

Question

User-826730287 posted

hello guys 

I' am a new member in your wonderful forum, but I was following up since a long time!

I am building a website that mostly uses Stored Procedures, but now I am facing a problem such that I want to execute the Stored Procedure more than one time each with a different value .

such that when I am calling it from the .vb page I could not execute it more than one time 

this is my sql SP code:

ALTER PROCEDURE dbo.plnCs

@plan int,

@level int 

AS

SELECT p.CNo, c.CLevel FROM Plans p, Course c 

WHERE p.CNo=c.CNo AND p.[Year]=@plan AND c.CLevel=@level

RETURN

ALTER PROCEDURE dbo.plnCs

@plan int,

@level int 

AS

SELECT p.CNo, c.CLevel FROM Plans p, Course c 

WHERE p.CNo=c.CNo AND p.[Year]=@plan AND c.CLevel=@level

RETURN

and this is my VB code:

csrDa.SelectCommand.CommandType = CommandType.StoredProcedure

csrDa.SelectCommand.Parameters.AddWithValue("@plan", plan)

csrDa.SelectCommand.Parameters.AddWithValue("@level", x)

the values of x range from 1 to 8 ,,,

could you please help!

thanx

Answer 1

User-1360095595 posted

I can't really see your code.  But your idea is sound.  Just clear the parameters between calls to the SP and set them to the new values on every iteration.

Answer 2

User-826730287 posted

thanx for reply, i have updated the post

please check the code and may you clarify more !!

Answer 3

User-1360095595 posted

As I stated before, you'll need to iterate, as many times as you need to run the SP, and clear the Parameters collection on every itereation and providing the new set of parameters/values, and re-execute the command.  Does that make sense?

Answer 4

User-826730287 posted

It makes sense, I got the idea but this iteration process should be inside the SP or in the CodeFile ?? 

if it inside ... this will form another problem to me

thanx again for reply MetalAsp.Net

Answer 5

User-1360095595 posted

Well I was thinking it would be in your code behind, the VB code.  That's what I meant by clearing the Parameters collection.  You execute Clear() on the Parameters collections, then you re-add the parameters with the new values, based in your iteration.  So you will use either a For loop, or a ForEach loop for this.  Give it a try and post back your updated code if you have problems.

Apologies if I have misunderstood your needs...

Answer 6

User-826730287 posted

ok this is much clear, 

I'll try and reply for you 

thanx alot

Answer 7

User-826730287 posted

Mr. MetalAsp.Net your solution is just working! So I want to thank you alot 

I have to perform the clear() on DataSet too as it save the result of previous iteration

I want to ask you about this solution in regarding to performance .... as we clear the parameters set and the dataset 

God Save You

this is the final source code for the For Loop

For i As Integer = 1 To 8
			gView(i) = New GridView()
			csrDa.SelectCommand.Parameters.AddWithValue("@plan", plan)
			csrDa.SelectCommand.Parameters.AddWithValue("@level", i)
			csrDa.Fill(ds)
			csrDa.SelectCommand.Parameters.Clear()
			gView(i).DataSource = ds	
			gView(i).DataBind()	'Bind to db
			Panel1.Controls.Add(gView(i)) ' add the dynamically created GridView!
			ds.Clear()
		Next

Answer 8

User-1360095595 posted

I want to ask you about this solution in regarding to performance .... as we clear the parameters set and the dataset 

 

Clearing the Parameters is trivial I believe.  You're just clearing a collection.  The DataSet, I don't really know because I don't understand why you are clearing it as well.  But the best thing, I guess, is to test it out and see if the performance seems reasonable to you.

Answer 9

User-826730287 posted

Ah, if i don't clear the DS it will repeat the result of the 1st iteration, 2nd iteration in the 3rd iteration for example, like this:

CNo               CLevel

1                       5

CNo               CLevel

1                       5

2                       6

CNo               CLevel

1                       5

2                       6

3                       7

Clevel is the value of (i) in the for loop, 

Anyway, i will check it outside as you suggest, thanks

Answer 10

User-1360095595 posted

I would think that you'd be iterating over the rows in your datatable within your dataset, so you wouldn't be duplicating your data.  But I have no view into your code behind so I'm just guessing / using my magical powers , which aren't that magical/successful.

Edit: I must be blind.  You did post your code!  Well I'm an idiot, or blind, or a combination of both.  Let me check it out...

Answer 11

User-826730287 posted

believe me i did  

i am pasting it once again 

For i As Integer = 1 To 8

gView(i) = New GridView()

csrDa.SelectCommand.Parameters.AddWithValue("@plan", plan)

csrDa.SelectCommand.Parameters.AddWithValue("@level", i)

csrDa.Fill(ds)

csrDa.SelectCommand.Parameters.Clear()

gView(i).DataSource = ds ' Set the DataSource of the gView object to be from stdCs (DataView Object)

gView(i).DataBind() 'Bind to db

Panel1.Controls.Add(gView(i)) ' add the dynamically created GridView!

ds.Clear()

Next

For i As Integer = 1 To 8

gView(i) = New GridView()

csrDa.SelectCommand.Parameters.AddWithValue("@plan", plan)

csrDa.SelectCommand.Parameters.AddWithValue("@level", i)

csrDa.Fill(ds)

csrDa.SelectCommand.Parameters.Clear()

gView(i).DataSource = ds

gView(i).DataBind() 'Bind to db

Panel1.Controls.Add(gView(i))

ds.Clear()

Next

you say that i hava to use DataTable which i know nothing about!?

Can You See the CODE

You Have a Powerful Magical Power 

Answer 12

User-1360095595 posted

OK.  I see what you're doing and it makes sense to me perfectly now.

I don't know if it performs better, but you might try these and see if you notice any difference (I suspect you won't):

ds.Tables.Clear(); or ds.Tables[0].Rows.Clear();

And instead of binding the gridview to ds, bind it to ds.Tables[0].

The last one in particular (ds.Tables[0].Rows.Clear();) I like because it's only clearing the rows, so the table is not being recreated on every iteration.  Once again don't know if you'll see any performance gains, or if it's worth you time, but just in case...

Answer 13

User-826730287 posted

OK.  I see what you're doing and it makes sense to me perfectly now.

I don't know if it performs better, but you might try these and see if you notice any difference (I suspect you won't):

ds.Tables.Clear(); or ds.Tables[0].Rows.Clear();

And instead of binding the gridview to ds, bind it to ds.Tables[0].

The last one in particular (ds.Tables[0].Rows.Clear();) I like because it's only clearing the rows, so the table is not being recreated on every iteration.  Once again don't know if you'll see any performance gains, or if it's worth you time, but just in case...

it seems its better like that to me too: clearing the rows instead of the whole table. I tried it and have exactly the same result. 

Locally I cant notice differences between the two ways due to performance!

Thanks Man!