Problem..pls Help!!

[Bernini]

Hi

I'm building this new app and wanted to create a Data Class which should practically open a connection with sql server, mak the necessary command and closes the connection.

So i have a function which i was thinking of hving two input parameters: one for the Stored Procedure to be used within the command and the other a Set of Parameters (ie SQLParameterCollection). The latter is whats making the problem..i don;t what i should use to have a collection of parameters and how i can use them , before and after they have been passd!

any help with this would be fantastic!

thanks in advance
B

 

[BobRodes]

Check out this thread:
thread796-891072

Some friendly advice: you might find that people are more responsive when your thread title is more descriptive than "Problem..pls Help!" How does anyone know if they can help without going to the trouble of opening your post? Maybe "How do I use parameters with a Data Class?" would get people to respond more quickly.



Bob

 

[Bernini]

Hi Bob,

thanks for your reply, and i apologize for the dumb title i've written...it was 2am and i was utterly frustrated as i couldn't find decent examples.

I've went through the thread and didn't seem to find exactly what i wanted.

So let me explain better:

i'm building a 3 tier system, having the data tier, and business tier which are most important ..right.

Now, my database interaction are all done via Stored Procedures and i'm not using any sql commands within the application.

When the business tier wants to retrieve information or insert data within the DB , I would like to send the Name of the stored procedure to the Data Tier plus a number of parameters.

The method i'm trying to create within the simple Data Class is something like this:

 Public Function GetSPData (spname as String, params as SqlParameters) as DataSet

 bla bla

The problem is that i'm not sure as using the params variable is the correct way. Any ideas on this? If on the other hand it is correct, how would i then use the collection to send to the command?

Thanks once again
B

 

[jubble]

With your 3 tier model in mind when passing data from the middle tier to the 3rd tier the middle tier shouldn't really be concerned with sp names and parameters, it just wants to pass a couple of values and get some values back. So it might be better to set up your function to just receive one or more values and then that function will know the names of the sp.

If none of your server tier classes have got the sp names and its your other tiers that have to give this info then that's another issue.

Treat it like departments within an organisation where each department has very little knowledge of other departments processing strategy ... the workflow department passes some info to the server department and couldn't care less how the server dept gets the info, they just want some data back. In a lot of companies if the server dept rings the workflow dept to ask for a sp name they'll either get a lot of question marks or on a Monday a lot of expletives.

Sorry for the thesis, got a bit carried away...here's the code (Northwind DB)

Private Function GetCustomer(ByVal CustomerID As String) As DataTable
GetCustomer = New DataTable

Dim cmd As SqlCommand = SqlConnection1.CreateCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "procGetCustomers"
cmd.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.Text, 5))
cmd.Parameters("@CustomerID").Value = CustomerID

Dim da As New SqlDataAdapter
SqlConnection1.Open()
da.SelectCommand = cmd
da.Fill(GetCustomer)
SqlConnection1.Close()

End Function