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

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.

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

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.

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

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.

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

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.

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

Actually when tried to Register with Command "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\regasm /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..

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

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.

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

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.

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

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.

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

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?

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

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...