create dll using vb.net and how to call it from sql server

[deepukng]

Hi friends,
Please someone how to create a dll in vb. net class library , how to register it and how to call it using sp_oacreate. Pls provide some sample codes.

[Teme64]

Not sure how detailed or "basic" help you need but let's start with this.

1) Create a VB.NET project and add a class in it.

Public Class Class1

  Public Sub New()
    ' Initialize class

  End Sub
  ' Add properties
  ' Add methods

End Class

I created a project ClassLibrary1 with Class1. Then compile it, you should now have ClassLibrary1.dll

2) Register assembly: regasm /tlb classlibrary1.dll If you get "... is not a valid .NET assembly" error, you're using wrong version of regasm. Fully qualify it: "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\regasm /tlb classlibrary1.dll" 3) Open regedt32 and search from HKCR your classlibrary1 (press CTRL+F to use Find). You should find it from a place similar to HKCR\TypeLib\{9529A0DE-7DCC-4587-B9B0-3C702F33AF6E}. {9529A0DE-7DCC-4587-B9B0-3C702F33AF6E} is now the ClassID you will use.

4) Check MSDN for the documentation of sp_oacreate. In my case the syntax would be: sp_OACreate '{9529A0DE-7DCC-4587-B9B0-3C702F33AF6E}' , myToken OUTPUT , 1 and now the integer variable myToken would be the object token to ClassLibrary1.Class1

Hope this helps.

Anyway, a few points if something goes wrong: change from the Class1 "COM Class = True" from the attributes. Now the registration (phase 2) changes to: regsvr32 classlibrary1.dll.

[deepukng]

Dear Friend,
Here is my Class

Public Class Class1
Public Sub New()
' Initialize class
End Sub

Public Function RunProcess(ByVal param() As String)
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim ds As DataSet
Dim da As SqlDataAdapter
con.ConnectionString = "Data Source= 'at-1096\deepak';Initial Catalog=MYDATA;Persist Security Info=True;User ID=sa;Password=Triumph1"

da = New SqlDataAdapter
ds = New DataSet
da.SelectCommand = New SqlCommand("Select * FROM BARCODE WITH(NOLOCK)WHERE RCPNUMBER='" & param(0) & "'", con)
Try
If da.Fill(ds) > 0 Then
Dim CfgStrmWriter As StreamWriter
CfgStrmWriter = File.CreateText("C:\BarCode\" & param(0) & ".txt")
For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
CfgStrmWriter.WriteLine(Trim(ds.Tables(0).Rows(i).Item("ITEMNO").ToString) & Trim(ds.Tables(0).Rows(i).Item("UNITCOST").ToString) & " " & Trim(ds.Tables(0).Rows(i).Item("DATE").ToString) & " " & Trim(ds.Tables(0).Rows(i).Item("RQRECEIVED").ToString))
Next
CfgStrmWriter.Close()
CfgStrmWriter = Nothing
End If
Catch ex As Exception
End Try
Return 0
End Function
End Class
i craeted dll with this class, and tried to register it. I am getting error Message. Please help me out. and u have given Second method, in which where i have to make "COM Class = True". please explain me.

[Teme64]

What was the exact error you get when registering? Did you use regasm? If so, did you notice my comment of fully qualifying the regams path.

The second approach was to make a COM-compatible component by setting from classes properties "COM Class = True", compile and use regsvr32. But if your error comes from regasm, please state the exact error message.

Your class itself seems to be ok, at a quick glance.

[deepukng]

Actually when tried to Register with Command "C:WINDOWSMicrosoft.NETFrameworkv2.0.50727regasm /tlb classlibrary2.dll"
i did not get Success full message..... Dos screen came for the fraction of seconds and i am not able to read the message. Whwn i tried to call DLL with my Trigger and with "sp_OACreate " i did not get any error message. My trigger is to INSERT Command. and my trigger is:

alter  TRIGGER [trgcom] ON [dbo].[BARCODE] 
FOR INSERT, UPDATE
AS
DECLARE @retVal int
DECLARE @comHandle INT
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
DECLARE @retString VARCHAR(100)
DECLARE @retTot int
DECLARE @nval1 int
DECLARE @nval2 int
DECLARE @ID int  
Select @nval1 = a, @nval2 = b,@ID=id,@retString = t 
from BARCODE 
EXEC @retVal = sp_OACreate '{0D39F056-DF63-3860-9E79-B57F6358FD4D}', @comHandle 
OUTPUT, 4
IF (@retVal <> 0)
BEGIN               
-- Error Handling 
EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription 
OUTPUT
SELECT [Error Source] = @errorSource, [Description] = @errorDescription
RETURN
END
-- Call a method into the component
EXEC @retVal = sp_OAMethod @comHandle, 'WriteToFile',NULL,  
@retString
IF (@retVal <> 0)
BEGIN               
-- Error Handling               
EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription 
OUTPUT              
SELECT [Error Source] = @errorSource, [Description] = @errorDescription             
RETURN
END
-- Call a method into the component
EXEC @retVal = sp_OAMethod @comHandle, 'AddTwoNumbers',@retTot 
OUTPUT, @nval1,@nval2 
IF (@retVal <> 0)
BEGIN                  
-- Error Handling               
EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription 
OUTPUT              
SELECT [Error Source] = @errorSource, [Description] = @errorDescription             
RETURN
END
-- Update table
update BARCODE  set [sum] = @rettot where id = @id
-- Release the reference to the COM object
EXEC sp_OADestroy @comHandle

When i tried to insert a row , this time a got a error message

`Error source: ODSOLE Extended Procedure      Description: Class not registered.`

Pls provide me some solution..

[Teme64]

Try to re-register the DLL-file. Open first command prompt from "Start"-button at lower left corner in Windows. Select "Run..." and type cmd and press enter. You should have command prompt open. Now navigate to folder where classlibrary2.dll is OR enter the full path for the classlibrary2.dll. Then re-try C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\regasm /tlb classlibrary2.dll (or <fullpath>\classlibrary2.dll). If any error occurs, you should now be able to see the whole error message.

Before you do that, check which version of .NET you are using (compiling your DLL). I assumed .NET 2.0. If you're using some other version, change "v2.0.50727" accordingly, like "v1.1.4322" for .NET 1.1.

[deepukng]

Dear sir,
I registered with the steps as said by u. i got a message "Types registered Successfully. Assembly exported to 'E:\Test Projects\ClassLibrary2\ClassLibrary2\bin\debug\classLibrary2.tlb', and the type library was registered successfully." in dis screen.

and whaen i tried to call a dll using sp_oacreate and sp_OAmethod i got a message
Error Source: ODSOLE Extended Procedure
Description: Class not registered

Please help me to solve this.

[Teme64]

Ok. I found this MS Help and Support article: How to run a DLL-based COM object outside the SQL Server process. Read this article, it has good tips when remoting COM with SQL Server.

But first, you have to make your class a COM component by setting the class attribute "COM Class = True", or directly in the code

<Microsoft.VisualBasic.ComClass()> Public Class Class1

  Public Sub New()
    ' Initialize class

  End Sub

  Public Sub MyMethod()

  End Sub

End Class

you'll have a COM component after compiling that, and you'll register COM component with regsvr32 <dllname>

I think this is more an SQL Server related issue than VB.NET. You could also re-post this question in "Web Development\MS SQL" forum here in DaniWeb.

[deepukng]

thank you very much for showing interest and giving me such valuable information. Can u tell me where can i make COM class='true' Exactly?

[Teme64]

You can use that <Microsoft.VisualBasic.ComClass()> Public Class Class1 declaration.

The same thing can be done from the IDE. Move the cursor to the line Public Class Class1 . Now the Properties window shows Class1 properties and there's the "COM Class" setting. If the properties window is not visible, go to "View\Properties Window" -menu or press F4 in the IDE. I would have added a picture here but that doesn't seem possible.

And you are using VB.NET, right? I noticed your other question about VB6 DLL...

[deepukng]

thank you for the information. Yes i am working in VB.net. when i was not able to register a dll created with vb.net i tried VB 6.0. Now VB6 dll is working fine. i am calling this dll using a trigger which is for INSERT record. But when my application inserts a record to the table, Trigger runs succefully but record is not inserted and gives this Error:
Description: Receipt. Database error (operation=INSERT, error=0). [Microsoft][SQL Native Client]Connection is busy with results for another command Please refer this error to your database administrator or see your database documentation for further assistance.
Source: 12:27:08

so i want to try , call a VB script from sql. if it possible and if u know about this, pls tell me how can i proceed.

[CornerAnalyst]

You will also need to give the user in SQL Server "special" permission to run external DLL's. See the documentation for sp_oacreate.

[Teme64]

You may want to check permissions like CornerAnalyst suggested. However, I think you would get a different error message if it were permissions.

Here's another MS TechNet forum thread. It's for VB.NET but I think the points in that thread can apply to VB6 too. The two points that might cause that error are inserting a null value to a "not null"-field and explicitly disposing data-adapter commands. So in the case of VB6 the issue may come from any previous command (or command object to be more precise).

Also, the SQLProfiler was mentioned. It might be worth to try to see what actually happens.

[CornerAnalyst]

Teme64 already showed you how to create a class in vb.Net, compile the code to a DLL, and register it.

If you are using VS just go to the BUILD menu item and it will be done for you automatically.

What problem are you having with Teme65's instructions?