Using .Net Class in Sql Server 2008R2

I have .net class  from SMS Service provider which i am using in my vb.net application to send individual sms to patients.  The class has method ( sendsms) which accepts relevent parameters and sends the sms thro the service provider gateway.

Now the requirement is auto sending of bulk  sms (outstanding payment)  to patients at predefined time. this i feel can be achieved easily if i can call this method from sql server stored procedure.  (.net stored procedure in sql)

Request expert help on this.
vensaliAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
Yes, that can be easily achieved.

1 - Turn on CLR Integration (https://msdn.microsoft.com/en-us/library/ms131048.aspx)
EXEC sp_configure ‘show advanced options’ , ‘1’;
reconfigure;

EXEC sp_configure ‘clr enabled’ , ‘1’ ;
reconfigure;

EXEC sp_configure ‘show advanced options’ , ‘0’;
reconfigure;

Open in new window


2 - Create a .NET library that calls this SMS library
Follow instructions here:
http://geekswithblogs.net/frankw/archive/2008/05/03/a-quick-walk-through-of-clr-integration-with-sql-server.aspx
http://www.codeproject.com/Articles/37377/SQL-Server-CLR-Functions
vensaliAuthor Commented:
I am getting  "deploy failed" when trying to deploy vs2010

When checked in output, error was

 Could not connect to server xx.xx.xx.xx Dbname : Login failed for user 'yy'.  I am not able to edit connection string. when I tried to edit and enter pwd ,pwd portion gets cleared

I copied the dll to local folder of the server and used the following command

CREATE ASSEMBLY KH_CLRAPP from 'F:\KH_CLRAPP.dll' WITH PERMISSION_SET = SAFE

it executed and am able to see the assembly under  assemblies node.  but sp is not visible in stored procedure node.

My question is

1. how to resolve vsstudio deployment error.
2. how to execute clr stored procedure in the assembly.
MlandaTCommented:
Which connection strong are you trying to edit? You should generally just use new SqlConnection("context connection=true") since the stored procedure runs in whatever database you are when you call it

Which stored procedure are you looking for? If you can see the assembly, then you are good to go. You can call is functions already
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

vensaliAuthor Commented:
It is Project connection settings.  

My SP in  CLR SP is as mentioned below

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub SendSms(ByVal m_mobileno1 As String, m_smstext1 As String)

        ' Add your code here

        Dim ObjTranSms As ClsSms

        ObjTranSms = New ClsSms

        Try


            Dim strmsg As String = ObjTranSms.SendSMS("", "", m_mobileno1, m_smstext1)

            SqlContext.Pipe.Send(strmsg)

        Catch ex As Exception

            SqlContext.Pipe.Send(ex.Message)

        Finally

            ObjTranSms = Nothing

        End Try

    End Sub
End Class

"ClsSMS"  has method which uses SMS service provider gateway to send the sms to the recipient mobile number.

I have to loop through the  mobileno & sms text from  the table in the DB  and execute this clr sp .
MlandaTCommented:
Oops! I misunderstood your meaning earlier.

1. how to resolve vsstudio deployment error.
I'd imagine you are putting in the wrong username and password in the connecion dialog.

2. how to execute clr stored procedure in the assembly.
Apart from creating the assembly, you also need to create the procedure:
CREATE PROCEDURE [dbo].[SendSms]
@m_mobileno1 NVARCHAR (MAX), @m_smstext1 NVARCHAR (MAX)
AS EXTERNAL NAME [KH_CLRAPP].[KH_CLRAPP.StoredProcedures].[SendSms]

Open in new window

And then you can execute the stored procedure as normal
EXEC [dbo].[SendSms]
		@m_mobileno1 = N'123123',
		@m_smstext1 = N'Hello world'

Open in new window

vensaliAuthor Commented:
Thanks for your valuable inputs.  I am able to configure the sp. now I am able to deploy from visual studio.  But when I run the sp , it is throwing the error "Unable to connect to the remote server"

For testing I have configured the clrsp in the local sql server 2008r2 instance. I tried to  step into sp. when I click on  step into sp on sql connection and enter values for the parameters ( mobile no , 'hello')  it exits the debug mode with the said error msg.  I have put break point on

  <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub SendSms(ByVal m_mobileno1 As String, m_smstext1 As String)
vensaliAuthor Commented:
Auto-attach to process '[3140] sqlservr.exe' on machine 'khhp' succeeded.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'KH_CLRAPP'
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\Microsoft.VisualBasic\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Unable to connect to the remote server
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[SendSms].
The thread 'khhp [55]' (0x132c) has exited with code 0 (0x0).
The thread '<No Name>' (0x132c) has exited with code 0 (0x0).
The program '[3140] [SQL] khhp: khhp' has exited with code 0 (0x0).
The program '[3140] sqlservr.exe: Managed (v2.0.50727)' has exited with code 259 (0x103).
MlandaTCommented:
when you use ClsSms, is there any additional configuration that you typically need to do?
Dim ObjTranSms As ClsSms
ObjTranSms = New ClsSms

Open in new window

Like setting IP addresses or hostnames, authentication and such? Out of interest, if you just created the stored procedure as this (below) does is show you the message when you execute it?:
   Public Shared Sub SendSms(ByVal m_mobileno1 As String, m_smstext1 As String)

        ' Put your code here
        SqlContext.Pipe.Send(String.Format("Sending message to: {0} => {1}", m_mobileno1, m_smstext1))

    End Sub

Open in new window

vensaliAuthor Commented:
Public Function SendSMS(ByVal User As String, ByVal password As String, ByVal Mobile_Number As String, ByVal Message As String, Optional ByVal SID As String = "SMSCntry", Optional ByVal MType As String = "N", Optional ByVal DR As String = "N") As String

        Dim stringpost As System.Object = "User=" & User & "&passwd=" & password & "&mobilenumber=" & _
                                           Mobile_Number & "&message=" & Message & "&SID=" & SID & _
                                           "&MTYPE=" & MType & "&DR=" & DR

        Dim functionReturnValue As String = Nothing
        functionReturnValue = ""

        Dim objWebRequest As HttpWebRequest = Nothing
        Dim objWebResponse As HttpWebResponse = Nothing
        Dim objStreamWriter As StreamWriter = Nothing
        Dim objStreamReader As StreamReader = Nothing

        Try
            Dim stringResult As String = Nothing

            objWebRequest = DirectCast(WebRequest.Create("http://www.xxx.com/SMSCwebservice_bulk.aspx?"), HttpWebRequest)
            objWebRequest.Method = "POST"

            If (objProxy1 IsNot Nothing) Then
                objWebRequest.Proxy = objProxy1
            End If

            ' Use below code if you want to SETUP PROXY.
            'Parameters to pass: 1. ProxyAddress 2. Port
            'You can find both the parameters in Connection settings of your internet explorer.

            'Dim myProxy As New WebProxy("YOUR PROXY", PROXPORT)
            'myProxy.BypassProxyOnLocal = True
            'wrGETURL.Proxy = myProxy

            objWebRequest.ContentType = "application/x-www-form-urlencoded"

            objStreamWriter = New StreamWriter(objWebRequest.GetRequestStream())
            objStreamWriter.Write(stringpost)
            objStreamWriter.Flush()
            objStreamWriter.Close()

            objWebResponse = DirectCast(objWebRequest.GetResponse(), HttpWebResponse)
            objStreamReader = New StreamReader(objWebResponse.GetResponseStream())

            stringResult = objStreamReader.ReadToEnd()


            objStreamReader.Close()
            Return (stringResult)
        Catch ex As Exception
            Return (ex.Message)
        Finally
            If (objStreamWriter IsNot Nothing) Then
                objStreamWriter.Close()
            End If
            If (objStreamReader IsNot Nothing) Then
                objStreamReader.Close()
            End If
            objWebRequest = Nothing
            objWebResponse = Nothing
            objProxy1 = Nothing
        End Try
    End Function


This was provided by the service provider which is perfectly working fine from vb.net application.
vensaliAuthor Commented:
I am not able to debug the stored procedure from vs2010.  I am getting the following error

Auto-attach to process '[3140] [SQL] khhp' on machine 'khhp' succeeded.
The thread 'khhp [54]' (0xd34) has exited with code 0 (0x0).
The thread 'khhp [54]' (0xd34) has exited with code 0 (0x0).
The thread 'khhp [55]' (0x2b60) has exited with code 0 (0x0).
The thread 'khhp [55]' (0x2b60) has exited with code 0 (0x0).
The thread 'khhp [55]' (0x2b60) has exited with code 0 (0x0).
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'KH_CLRAPP'
Auto-attach to process '[3140] sqlservr.exe' on machine 'khhp' succeeded.
'KHHP;.Net SqlClient Data Provider;6140' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\Microsoft.VisualBasic\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll'
'KHHP;.Net SqlClient Data Provider;6140' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll'
Unable to connect to the remote server
No rows affected.
(0 row(s) returned)
Finished running sp_executesql.
The thread 'khhp [55]' (0x2b60) has exited with code 0 (0x0).
The thread '<No Name>' (0x2b60) has exited with code 0 (0x0).
The program '[3140] [SQL] khhp: khhp' has exited with code 0 (0x0).
The program '[3140] sqlservr.exe: Managed (v2.0.50727)' has exited with code 259 (0x103).
vensaliAuthor Commented:
Both Visual stutdio2010 and sql server instance are on the same machine
MlandaTCommented:
I haven't tried to debug these stored procedures directly from VS, so I cannot really say. However, coming back to this:
But when I run the sp , it is throwing the error "Unable to connect to the remote server"
, are you able to pin-point exactly where the error is happening? Is there more information about this error available?
    <Microsoft.SqlServer.Server.SqlProcedure()>
    Public Shared Sub SendSms(ByVal m_mobileno1 As String, m_smstext1 As String)

        Try

            ' Put your code here
            SqlContext.Pipe.Send(String.Format("Sending message to: {0} => {1}", m_mobileno1, m_smstext1))

            Dim wc As New System.Net.WebClient
            SqlContext.Pipe.Send(wc.DownloadString("http://labs.bible.org/api/?passage=random"))

            SqlContext.Pipe.Send(wc.DownloadString(m_smstext1)) 'This will probably throw an exception!

        Catch ex As Exception
            SqlContext.Pipe.Send(ex.ToString)
        End Try

    End Sub

Open in new window

So I modified the example I sent you yesterday, to see whether I could connect to an internet resource at all. And I get an error
Sending message to: 123 => 234534
System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
   at System.Net.WebClient.DownloadDataInternal(Uri address, WebRequest& request)
   at System.Net.WebClient.DownloadString(Uri address)
   at SQLServerDatabase1.StoredProcedures.SendSms(String m_mobileno1, String m_smstext1)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Net.WebPermission
The Zone of the assembly that failed was:
MyComputer

(1 row(s) affected)

Open in new window

MlandaTCommented:
Ah! Now working. The way to resolve this is to:

1 - SET TRUSTWORTHY ON on your database
ALTER DATABASE [TestDB1001] SET TRUSTWORTHY ON

2 - CREATE THE ASSEMBLY WITH THE "EXTERNAL ACCESS" PERMISSION
You can do this in Visual Studio OR with a script.VS2015 screenshot. Setting EXTERNAL ACCESS
ALTER ASSEMBLY [SQLServerDatabase1]
WITH PERMISSION_SET = EXTERNAL_ACCESS

Open in new window


3 - GRANT EXTERNAL ACCESS permissions
The SQL or Windows users that will be running the stored procedure will need external access permissions.
grant external access assembly to [HALO\Tamayi]
grant external access assembly to sa

Open in new window


Please review other material on the security best practices. What I have given above is just a quickly put together thing that made everything work. You need to be aware of what security the implications are and what the other options for this would be:
CLR Integration Code Access Security
Deploy/Use assemblies which require Unsafe/External Access with CLR and T-SQL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vensaliAuthor Commented:
Thanks a lot for your valuable inputs. It is working fine. Just one more thing. i am not able to run the solution in debug mode. I googled a bit but could not find proper answer.  can you help me out on this.

or i need to raise a separate question
MlandaTCommented:
Oh... The running in debug... I haven't really done that before. I rely on logging. Perhaps google,... Or another question
vensaliAuthor Commented:
Can  you explain a bit on "Logging" and how it can substitute  running in debug mode
MlandaTCommented:
Like I did there, where I used a try...catch block, and wrote the error message back. That gave me enough information to determine where the error was occurring and led me to fixing it. That is an example of very basic logging of exceptions. You could write those messages to a text file for inspection later, or stuff like that.
vensaliAuthor Commented:
There is one more issue . Yesterday i tried with datacard . it worked.  

But when I tried in my office n/w (lan) or mobile 3g ( wifi hotspot) , i am getting the same error

"Unable to connect to the remote server"

What might be the issue
vensaliAuthor Commented:
Sorry for closing it late.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.