Where to call a stroed procedure?

gajit posted on Wednesday, July 19, 2006

Hi guys,

I am embarking on a new project and using the CSLA for the frst time in a business environment...

I apologize in advance for what I suspect is a really stupid question, and possibly the weather is causing me not to think straight...

What I am trying to do - is create a function based within my "customer" business object that will ultimately return a string message and am having problems trying to implement it.

Basically, it need to call a stored procedure that returns a string value based on the customer# criteria. I have tried to implement something along the lines of the "exists" function using commandbase, but I don't have an example of how this is implemented (in ProjectTracker?). If I try to call from my windows form with something like this...

mystatus = customer.getmystatus(customerno)

the compiler is saying "Access of shared member, constant member, enum member or nested type through an instance; qualifying expression will not be evaluated."

Am I barking up the wrong tree? Should I be approaching this in a different way?

Any help/suggestions would be much appreciated.

Thanks,

Graham

Here's the code I have in my customer business object.... if it's any help..

#Region " Get Status"

Public Shared Function GetStatus(ByVal customerno) As String

Dim result As GetStatusCommand

result = DataPortal.Execute(Of GetStatusCommand)(New GetStatusCommand(customerno))

Return result.myStatus

End Function

<Serializable()> _

Private Class GetStatusCommand

Inherits CommandBase

Private mCUSTOMERNO As String

Private mMyStatus As String

Public ReadOnly Property myStatus() As String

Get

Return mMyStatus

End Get

End Property

Public Sub New(ByVal customerno As String)

mCUSTOMERNO = customerno

End Sub

Protected Overrides Sub DataPortal_Execute()

Using cn As New SqlConnection(Database.crm2k6Connection)

cn.Open()

Using cm As SqlCommand = cn.CreateCommand

cm.CommandType = CommandType.StoredProcedure

cm.Parameters.AddWithValue("@CustomerNo", mCUSTOMERNO)

cm.Parameters.AddWithValue("@ContactNo", "")

cm.Parameters.AddWithValue("@SiteNo", "")

cm.Parameters.AddWithValue("@Status", mMyStatus)

cm.Parameters.AddWithValue("@Flag", "")

cm.Parameters.AddWithValue("@OptArg", "")

cm.Parameters("@Status").Direction = ParameterDirection.Output

cm.CommandText = "C2K6_getstatus"

cm.Parameters.AddWithValue("@CUSTOMERNO", mCUSTOMERNO)

cm.ExecuteNonQuery()

mMyStatus = CType(cm.Parameters("@Status").Value, String)

End Using

End Sub

End Class

#End Region

xal replied on Wednesday, July 19, 2006

gajit,

It seems as if you where using an instance of the object to access a Shared method.

This means:

Public Class MyClass
    Public Shared Sub DoSomething()
    End Sub
End Class

In your form:

Private mInstance as MyClass = New MyClass

    Private Sub RunSomeOtherThing()

        'This throws that error you see because you're
        'accessing the method through an instance

       mInstance.DoSomething()

       'This does what you expect and is the correct approach
       'for calling shared methods (through the class name)

       MyClass.DoSomething()
    End Sub

Andrés

RockfordLhotka replied on Wednesday, July 19, 2006

You are barking up the wrong tree if you ever intend to use the data portal. Unless your method is in a class that only exists on the server, you are violating a core princple of CSLA - putting data access code in a public method. At least on the surface, it appears that you are create a public method that might be called by UI code, and in that method you are directly talking to the database, and that's "illegal".

Data access code should always go into a DataPortal_XYZ method, or into a server-side data access assembly that is only called by a DataPortal_XYZ method.

If your business use case requires the retrieval of a string value as part of the overall process, then there should be an object that represents either that string value, or the command to get the value. The data access code should be in that object's DataPortal_Fetch() or DataPortal_Execute() method respectively.

gajit replied on Thursday, July 20, 2006

I thought I might be ... (darn tree)...

I thought I had approached it correctly by placing the sub/function in my business class a la the obj.exists method - is this not a command? I do want to use the dataportal for this type of thing absolutelty,

I used the string value as a simplified example - I am in fact calling a stored procedure that runs off to another application to trigger a new order - the stored proc, merely returns a completion status (0=successfully populated the order entry module with data, 1=failed, etc...). So the value itself isn't persistent - I only need the value (and can only request it) when I want to 'place an order'.

Any help to put me on track would be appreciated...

thx,

gajit

ajj3085 replied on Thursday, July 20, 2006

gajit, your code looks ok to me. It almost sounds like Rocky's reply actually belongs to another thread, where the poster wants to pass Ado.Net objects out of the business layer into the UI. Hopefully Rocky will post here again to clarify...

gajit replied on Thursday, July 20, 2006

i thought so.. :)... well.. i had a suspicion... but didn't want to upset the boss :)

RockfordLhotka replied on Thursday, July 20, 2006

Yeah, my bad... You are doing exactly what I suggested, making my suggestion rather pontless... Embarrassed [:$]

gajit replied on Thursday, July 20, 2006

Thanks Rocky ... no problem - good to know I'm on the right track - and incidentally - it works. I was trying to call an using a instance of the class and not the class itself.

Onwards I go....

gajit.

ktweedy replied on Thursday, July 20, 2006

RockfordLhotka:

Data access code should always go into a DataPortal_XYZ method, or into a server-side data access assembly that is only called by a DataPortal_XYZ method.

I haven been thinking to seperate the data access routines into a assembly that only gets installed on the server. For two reasons, I can make changes to this code and only have to install a new DLL on the server, and second is kind of reducing risk of having something on the client that tells how the databse is imlpemented and could be disassembled.

So my question is do you have any thoughts on how to implement a server-side data access assemply. I would need to dynamically load this DLL so the client wouldn't have any referernces to it? I am thinking that the client execuatable can't have a reference to this DLL if it is not installed on the client.

ajj3085 replied on Thursday, July 20, 2006

Implement it just as you would if the DAL was going to be client side. Your BO assembly may have a reference to the DAL even.

On your client, the DAL assembly would be missing, but this wouldn't be a problem, as long as you're only accessing the DAL on the server, meaning that your BO may only reference DAL objects in the DataPortal_xxx methods. .Net won't try to load your DAL assembly until it actually needs it, so as long as you keep within the Csla framework, you'll be good to go.

Only your server would actually have the DAL assembly side-by-side with the business assembly. No need for dynamic loading or anything fancy like that.

HTH
Andy

ktweedy replied on Thursday, July 20, 2006

Thanks, I'll have to give it a try.

RockfordLhotka replied on Thursday, July 20, 2006

ktweedy:
So my question is do you have any thoughts on how to implement a server-side data access assemply. I would need to dynamically load this DLL so the client wouldn't have any referernces to it? I am thinking that the client execuatable can't have a reference to this DLL if it is not installed on the client.

Having a reference isn't a problem - .NET lazy-loads assemblies. So your business assembly can reference the DAL without needing the DAL on the client.

However, that doesn't allow you to easily swap to a different DAL. That's why you need dynamic loading - so you can load the right DAL based on a configurable assembly name.

Remember, the data portal always routes your request through the DataPortal_XYZ methods, so it is the business object that must create the DAL.

Now you can abstract this. You could create a DalFactory assembly that did the dynamic loading of the actual DAL. The business assembly could reference the DalFactory and call it, knowing nothing about the actual DAL - which is not a bad idea because it means the business objects would need to know virtually nothing about the DAL itself.

ktweedy replied on Thursday, July 20, 2006

Interesting, I appreciate the reply.