[RESOLVED] [2005] using CLR stored procedures in VB

[Krenshau]

Hello,

I am learning how to create CLR stored procedures in VB 2005 for SQL Server 2005, and I have created them well enough, but in all of the tutorials I have been finding they are all consumed using T-SQL. Is it possible to use a CLR stored procedure directly from an application? If not, I know how to execute the T-SQL using SSMS (SQL Server Management Studio), but not how to do it from code. Can someone help me out with how that would be done, if the CLR sproc can't be called directly?

Also, in my clr sproc, I send a uniqueidentifier back thru a pipe since Return won't send it. If I do have to use T-SQL to execute the CLR sproc, will it still return the uniqueidentifier? If it depends on how I make my t-sql command, then how can I make it return the uniqueidentifier?

Sorry to unload so many questions at once. Thank you for your help.

[Krenshau]

bump

[jmcilhinney]

I've never created CLR sprocs but I would assume that they are accessed the same way as any other sprocs. In that case you would create an SqlCommand, set its CommandType to StoredProcedure and its CommandText to the name of the sproc. In that case you would have a parameter with a Direction of ReturnValue that corresponds to the value returned by the sproc. You can also create a typed DataSet and configure a TableAdapter to use your sproc.

[Krenshau]

Thank you, jmcilhinney. From further reading, it looks like I have to create a stored procedure that I call that calls the CLR stored procedure, but I am not totally sure. I am just having a problem with the syntax.

I might post the stored procedure in the database forum, since it isn't actually VB.

To call the stored procedure would my commandtext look like this?

MyCom.CommandText = "Exec KeyWordHandler MyParameter"
Dim MyGUID as GUID = MyCom.ExecuteScalar()

That isn't all of the code, but that is the part I am having a hard time getting right.

Thank you for your help.

[jmcilhinney]

For a normal sproc it would be:

Dim cmd As New SqlCommand("StoredProcedureName", con)
 
cmd.CommandType = CommandType.StoredProcedure
 
'Add parameters here if required, e.g.
cmd.Parameters.AddWithValue("@ID", idValue)
 
con.Open()
 
Dim result As Object = cmd.ExecuteScalar()
 
con.Close()
 
If result IsNot DBNull.Value Then
    Dim myGUID as Guid = DirectCast(result, Guid)
 
    'Use myGuid here.
End If

If your query cannot return a null value then you can cast the return value of ExecuteScalar directly.

[Krenshau]

Great! Thank you very much. That helps me a lot. I will give that a go tomorrow.

Yes, my sproc always returns a guid. Basically, I pass a word to the CLR sproc, and if the word is already in the database, it pulls the id and sends it back. If it is not in the database, then it creates a comb guid for a pk, and inserts the values into the database, and passes back the new guid.

Thanks again for helping me with this.

[jmcilhinney]

I'm no database guru and I've never created managed sprocs but I think that if your sproc has a 'RETURN' statement then you should be able to get that value via a parameter, e.g.

Dim cmd As New SqlCommand("sproc name here", con)
 
cmd.Parameters.Add("RETURN_VALUE", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.ReturnValue

[Krenshau]

Well, a regular sproc won't return a guid, at least I couldn't find a command that would. I only found RETURN. My CLR sproc uses a pipe to send the guid back. I am not sure how it does it, though. I will test out what you have, and play around with it a bit. This is all new to me.

Thank you.

[Krenshau]

OK, I used most of your code, and I can get it to almost work. Here is my code.

Dim X As System.Data.SqlClient.SqlConnection = Me.GetKeyWordConnection
                Dim Y As New System.Data.SqlClient.SqlCommand("KeyWordHandler", X)

                Y.CommandType = CommandType.StoredProcedure
                Y.Parameters.Add("@KeyWord", SqlDbType.VarChar).Value = Me.KeyWord.Text.Trim.ToString

                Try
                    X.Open()

                    Dim Results As Object = Y.ExecuteScalar

                    X.Close()

                    If Results IsNot DBNull.Value Then
                        Dim MyGUID As Guid = DirectCast(Results, Guid)
                        Me.KeyWordID.Text = MyGUID.ToString
                    End If

                Catch ex As Exception
                    MessageBox.Show(ex.Message.ToString)
                Finally
                    Y.Dispose()
                    Y = Nothing
                    X.Dispose()
                    X = Nothing
                End Try

I get an object not set to an instance of the object on this line.

Dim MyGUID As Guid = DirectCast(Results, Guid)

I also tried.

Try
                Dim X As System.Data.SqlClient.SqlConnection = Me.GetKeyWordConnection
                Dim Y As New System.Data.SqlClient.SqlCommand("KeyWordHandler", X)

                Y.CommandType = CommandType.StoredProcedure
                Y.Parameters.Add("@KeyWord", SqlDbType.VarChar).Value = Me.KeyWord.Text.Trim.ToString
                Y.Parameters.Add("@Results", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.ReturnValue

                Try
                    X.Open()

                    Y.ExecuteScalar()

                    Dim Results As Object = Y.Parameters.Item("@Results").Value

                    X.Close()

                    If Results IsNot DBNull.Value Then
                        Dim MyGUID As Guid = DirectCast(Results, Guid)
                        Me.KeyWordID.Text = MyGUID.ToString
                    End If

                Catch ex As Exception
                    MessageBox.Show(ex.Message.ToString)
                Finally
                    Y.Dispose()
                    Y = Nothing
                    X.Dispose()
                    X = Nothing
                End Try

Same results both ways. I also tried changing ReturnValue for Output, but it said I specified too many arguments.

I am not sure how it gets past the null statement if it isn't set, but I think the problem is that I don't actually return a value directly. I use a pipe to send back the value. I thought that the way it is now would catch the results of the pipe.send, but it seems that is not the case.

Here is the code on how I send the results back. The reason I use a pipe is because RETURN won't return an GUID.

Pipe.Send(MyNewGUID.ToString)

I have tested the CLR sproc using SSMS and using server explorer in visual studio, and both work properly. The GUID is returned in the output window, but it always says "@RETURN_VALUE =", so that is why I think I have to catch the output a different way, I am just not sure how.

Any ideas on how to catch the results of the pipe?

Thank you for your help.

[Krenshau]

Nevermind. I just read that I have to add a handler to the connection.infomessage to receive the message when it comes back. That won't work for me, so I will have to find a different way to send the message back.

Thank you for your help.