How to run Stored Procedure

[Anurag Jamloki]

Hi,

I some time back I asked same question. The answer I got was:

Dim cc As CCursor = pBroker.ProcessDirectSQL("exec usersp_MyProc @param = '" & pValue.ToString & "'")

The returned dataset will be available at
cc.ResultSet.ResultSet

I tried this using c# like this:
            CPersistenceBroker objPersistenceBroker = new CPersistenceBroker();
            CPersistentObject objPersistentObject = this;
            objPersistenceBroker.ClassMaps.Add("ContractHeader", objPersistentObject.getClassMap());
            CCursor objCursor = objPersistenceBroker.ProcessDirectSQL("exec testSP");
            return objCursor.CurrentRow["Name"].ToString();

But the above code failed.

I needed to run stored procedure badly. So I overloaded the Function ProcessDirectSQL in CPersistenceBroker class.

Here is the overloaded function:

    Public Function ProcessDirectSQL(ByVal SPSql As String, ByVal ClassName As String) As CCursor
        Dim clMap, firstClassMap As CClassMap
        Dim conn As _CConnection
        Dim clauses(), subclauses() As String
        Dim clause, subclause As String
        Dim objString() As String
        Dim am As CAttributeMap
        Dim i As Integer
        Dim preString, postString As String

        Try
            Dim statement As New CSqlStatement
            statement.SqlString = SPSql

            firstClassMap = Me.getClassMap(ClassName)
            conn = firstClassMap.RelationalDatabase.getConnection(Nothing)
            conn.AutoCommit = False

            Dim cursor As CCursor
            Dim rs As CResultset
            rs = conn.processSelectStatement(statement)

            cursor = New CCursor
            cursor.ParentCriteria = Nothing
            cursor.ResultSet = rs
            cursor.ClassMap = Nothing
            cursor.HoldsProxies = False

            Return cursor
        Catch
            firstClassMap.RelationalDatabase.freeConnection(conn)
            Return Nothing
        End Try
    End Function

The above overloaded function runs perfectly fine.

But still is there a better code to run stored procedure's.

Thanks and Regards

Anurag Jamloki

[Richard Banks]

Hi Anurag,

I've added new methods to the persistence broker to make things a little simpler.

ProcessStoredProcedure(databaseName, sqlstring)
-and-
ProcessStoredProcedure(database, sqlstring)

You C# code would be
CPersistenceBroker objPersistenceBroker = new CPersistenceBroker();
CCursor objCursor = objPersistenceBroker.ProcessDirectSQL("databaseName", "exec testSP");
return objCursor.CurrentRow["Name"].ToString();

The new methods are in CVS, so grab the code from there.  I hope that's a little better for you.

- Richard