Tag Archives: webservice

ASP.Net web services (ASMX) vs WCF web services

The following are the few things which distinguish ASMX services to WCF webservices:
–WCF web services provide many bindings
–WCF web services are written to implement contracts defined by interfaces.
–For ASMX web services, you add attributes to the methods. For WCF web services, you add attributes to the interfaces.
–ASMX web services must be hosted in IIS, but WCF web services can ei

Connecting WebService from SQL stored procedure

When I wanted to connect a web service and perform some tasks in database at regular intervals of time, my first choice was going for windows service. That was easy and I was familiar with the process. Due to some constraints in my organization, I had to look for alternatives.I was able to access the webservice from sql clr.Steps:1. Create a wsdl class file of the webservice.using in command prompt

 wsdl http://www.test.com/WebService/Testsvc.asmx /language:vb /out:C:\TestSvc.vb 

 Include this class file in the application where you are creating a sql clr. Below is an example:
Imports Microsoft.VisualBasic
Imports System
Imports System.data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Imports System.XmlPartial 

Public Class StoredProcedures_

  Public Shared Sub GetTest( ByRef value1 As SqlInt32, ByRef value2 As SqlString)
    Dim Connection As New SqlConnection("Context Connection = true")
    Dim iWeb As New com.WSMainTestDim result as Integer = iWeb.GetAdditionTest(value1, value2)Connection.Open()
    Dim insert As String = "Insert into tblResponse (Text) Values ('" + result + "')"
    Dim command1 As New SqlCommand(insert, Connection)
    command1.ExecuteNonQuery()
    Connection.Close()
    Connection.Dispose()
  End Sub
End Class3.

Once I created the clr built the application and turned this class into a dll and also web service is also created as dll. create sgen for webservice.4.Add all 3 dlls to the database on which you wanted to attach the sql and access the webservice. Get to the database, in the assembly folder add these 3 dlls. The main clr file which connects to dll need to have external access permission set.5.Once you add these 3 dlls to the assembly folder, create procedure for the stored procedure clr.

Create PROC [Stored Procedure definition]([Parameters])
   With EXECUTE AS CALLERASEXTERNAL NAME (dll name of the stored procedure).     (class name).(method name)

For my purpose I created a job to perform certain tasks at regular intervals of time.Hope this could help someone.Thanks,Kishore Patil