Calling web service from sql server using sql clr

Problem :

In my previous project I was been asked call web service from sql server stored procedure.

So I get it done using SQL CLR. So using CLR we can run managed code inside sql server.

Code that runs within the CLR is referred to as managed code.

We can create stored procedures, triggers, user defined types, and user-defined aggregates in managed code.

We can achieve significant performance increases because managed code compiles to native code prior to execution.

We can use SQL CLR in in SQL Server 2005 and later.

Why Sql CLR in sql server:

In some cases some tasks are not possible by T-SQL as my requirement.

So in that point we can go with SQL CLR.

What tools I have used in this post

Visual Studio 2015

Sql Server 2014

In Action:

  1. Create SQL Server Database project in VS 2015

newproj

  1. Add SQL CLR C# Stored Procedure

add-new-item

Name stored procedure As CallWebService

3. Add C# codes to Call Web service. I am using below web service to do test

http://www.webservicex.net/globalweather.asmx?op=GetCitiesByCountry


HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://www.webserviceX.NET//globalweather.asmx//GetCitiesByCountry?CountryName=Sri Lanka");

request.Method = "GET";

request.ContentLength = 0;

request.Credentials = CredentialCache.DefaultCredentials;

HttpWebResponse response = (HttpWebResponse)request.GetResponse();

Stream receiveStream = response.GetResponseStream();

// Pipes the stream to a higher level stream reader with the required encoding format.

StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8);

System.IO.File.WriteAllText("d://response.txt", readStream.ReadToEnd());

response.Close();

readStream.Close();

I am simply writing response to a text file.  You can do whatever in here.

4.Enable clr and set trust worthy on database. I am using AdventureWorks database


sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

alter database [AdventureDatabase] set trustworthy on;

5. Build Visual Studio Project

Then it will generate dll in bin folder

6. Register assembly in Database.

Go to AdventureWorks > Programmability > Assemblies

Right click on Assemblies and Click new Assembly

register-assembli

Set Permission to External access and browse for Our dll.  It is in bin folder of your project

Once you added we can see assembly registered in side Assemblies as below.

assembli

7. Create stored procedures to call assembly’s stored procedure

stored-procedure

Once you created a stored procedure you can see locked stored procedure.

locked-sp

8. So Now we have finished just execute the stored procedure. You can see text file is generated in drive

exec-sp

Source code

MERGE in Sql Server

From Sql server 2012 we can merge two tables. We can map data from one table to another table. If record exists can do a update and if not insert operation can be done.

Syntax


MERGE <target_table>
USING <table_source>
ON <Search>
WHEN MATCHED THEN UPDATE SET <set_clause>
WHEN NOT MATCHED THEN INSERT VALUES (<values_list>)

Create two tables and Insert some records


CREATE TABLE TargetTable([Id] INT, [Name] NVARCHAR(50))

CREATE TABLE SourceTable([Id] INT, [Name] NVARCHAR(50))

INSERT INTO TargetTable(Id,Name)
VALUES(1, 'John'), (2, 'Mezi'), (3, 'Tom')

INSERT INTO SourceTable(Id,Name)
VALUES(1, 'JohnNew'), (4, 'Peter'), (3, 'Tom-new')

Then do Merge


MERGE TargetTable AS T
USING SourceTable AS S
ON T.Id = S.Id
WHEN MATCHED THEN UPDATE SET T.Name = S.Name
WHEN NOT MATCHED THEN INSERT VALUES (S.Id, S.Name);

Now view TargetTable Data

merge

 

CHOOSE and IIF in Sql server

CHOOSE

  • Returns a list item based on its location
  • First parameter is index
  • Next parameters are list

SELECT CHOOSE(2,'item1','item2' )
-- this will return item2

SELECT CHOOSE(1,'item1','item2' )
-- this will return item1

SELECT
CHOOSE(1,[StandardCost],[ListPrice])
FROM [Production].[Product] p
WHERE p.ProductID = 680

--this will return StandardCost

IIF

  • Instant if
  • Three parameters
    • Boolean expression
    • Return value if true
    • Return value if false

SELECT IIF([ListPrice] > 0, [ListPrice], [StandardCost]) AS 'price'
FROM [Production].[Product] p
-- if ListPrice is greater than 0 it returns Listprice. Otherwise returns StandardCost

Sub Query in Sql

What is subquery?

Subquery is  just a query inside of a query.

Why Subquery?

  • Break down complex logic
  • Simplify reading

Subqueries can be replaced by joins. But some times Joins can be complex. Other thing is we are not allowed to put aggregates in WHERE. But we can put it aggreagates inside subquery .

Where to Use?

We can put sub queries in  three main places in query.

  • SELECT
  • FROM
  • WHERE

Using in SELECT

  • Access informations from tables  withing select query
  • Query must return a single(scalar) value

Creates a dynamic table. Useful for  breaking down queries. Query must be aliased.

Note : I have used Adventureworks 2012 database for following queries 


SELECT so.[SalesOrderID]
,so.OrderDate
,(SELECT COUNT([SalesOrderDetailID])
FROM [Sales].[SalesOrderDetail]sod
WHERE sod.SalesOrderID = so.SalesOrderID ) AS 'OrderLineCount'
FROM [Sales].[SalesOrderHeader] so

insideselect

Using with FROM


SELECT so.[SalesOrderID]
,so.OrderDate
,X.OrderQty AS 'TotalItems'
FROM [Sales].[SalesOrderHeader] so
INNER JOIN (SELECT
SUM([OrderQty]) AS 'OrderQty'
, [SalesOrderID]
FROM [Sales].[SalesOrderDetail] sod
GROUP BY [SalesOrderID]) AS X
ON so.[SalesOrderID] = X.[SalesOrderID]

infrom

With WHERE

Useful for comparing values from other tables

Predicates used with subqueries

IN

  • Confirm column value exist in subquery
  • Similar to inner join

SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE sc.CustomerID IN ( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )

EXISTS

  • Returns true if subquery returns value
  • Used with correlated queries
Correlated subquery

Pass outer query column into subquery

</h4>
SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE EXISTS ( SELECT 1
FROM [Sales].[SalesOrderHeader] soh
WHERE sc.CustomerID = soh.CustomerID )

ALL

  • Compares column values to all items returned by subquery
  • Subquery must return only one column

ANY or SOME

  • Compare column value to any item returned by subquery
  • Subquery must return only one column
  • ANY and SOME are identical

SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE sc.CustomerID = ANY ( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )


SELECT [AccountNumber] FROM [Sales].[Customer] sc

WHERE sc.CustomerID = SOME( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )

Error handling in sql server . Try / catch

Things can go wrong in sql operation. So how we handle Errors in sql server.

We can detect errors in two ways

  1. Try/catch
  2. @@ERROR

@@ERROR

If there is no errors based on the last operation @@ERROR will be set to 0 . If something went wrong it will be set to value other than 0. How ever this not best way to handle errors in sql server.

TRY/CATCH


BEGIN TRY

-- this is where we put our codes that can be generate errors

END TRY

BEGIN CATCH

-- this where we put code to handle errors

END CATCH

Big thing in TRY block is always we’re going to assume success because only way that we’re going to get to the next line of code is if the line before is succeeded

Transactions in sql server

Why transactions.

We generally works with multiple tables in one operation. May be we want to insert data to order header and order detail table in one operation. Then what happens if only part of the operation succeeds. Some statements can be failed.

So we are with lot of possible problems inside our database when it comes to data integrity . This is where we need transactions.

Transactions will allow us to control the entirety of an operation and ensure that everything’s going to succeed or everything is going to get rolled back should something fail.

Transaction must meet ACID.

ACID is comprised with four different components.

A -> Atomic

Entire operation must succeed or the entire operation must fail, and it must do so as a single unit

C -> Consistent

Once the operation is complete, database must be left in a consistent or in a valid state

I -> Isolated

Every transaction must also be isolated, simply meaning that somebody else’s operation at the same time that I’m working on my data, is not allowed to impact my operation.

D -> Durable

The database should be durable enough to hold all its latest updates even if the system fails or restarts

There are three main commands in managing transaction manually.

  1. BEGIN TRANSACTION

This will mark the start of the transaction

2.COMMIT TRANSACTION

We are going to execute this once we know everything is works fine.

3.ROLLBACK TRANSACTION

If some thing went wrong we are going to execute this one. We got some errors and we want to Rollback the transaction.

 

 

reading xml in sql server

So some times we want to read xml file in sql server. I am passing list of details to sql server using xml file format. It reduce several db calls when we passing list of data.

Suppose we have List of Students. In List of Student we have to loop it and fire db calls to save data. To overcome this I create xml of List of Student and Pass it to sql server with one db call and then in my stored procedure I will handle those data as follow.

DECLARE @Xml XML
DECLARE @HDoc INT

SET @Xml = '<ArrayOfStudent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Student>
<Id>1</Id>
<FirstName>jeevan</FirstName>
<LastName>JLast name</LastName>
</Student>
<Student>
<Id>2</Id>
<FirstName>john</FirstName>
<LastName>johnLast name</LastName>
</Student>
</ArrayOfStudent>'
EXECUTE SP_XML_PREPAREDOCUMENT @HDoc OUTPUT,@Xml

SELECT [Id],
[FirstName],
[LastName]
INTO #Temp
FROM OPENXML(@HDoc, '/ArrayOfStudent/Student', 1)
WITH (
[Id] INT 'Id',
[FirstName] VARCHAR(400) 'FirstName',
[LastName] VARCHAR(400) 'LastName'
)

EXECUTE SP_XML_REMOVEDOCUMENT @HDoc

SELECT  *  FROM  #Temp

xml reading