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

Advertisements

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

Concurrency and locks in sql server

Transactions in sql server must be Isolated. It means that if a transaction is running and somebody else comes along and there’re trying to work with data that previous transaction working with, they’re not allowed to touch that.

Sql server handle this problem using concurrency and locks.

Concurrency is the process of managing who has access to data. 

The way that access to data is managed by locks.

Locks:

  1. Shared Lock                                                                                                                                                Known as a read lock, This type of lock is obtained anytime when issue a select query. Shared lock can be shared with others who are trying to read that data.
  2. Exclusive locks                                                                                                                                          Whn ever going to modify data this lock is issued. We need to make sure nobody else working with that data, reading or trying to modify that. This lock prevents others from accessing that data.
  3. Intent lock                                                                                                                                                    If someone doing operation and we want make sure that nobody drops that table or nobody drops the database. This is where Intent lock is issued.

One of the issue with locks is it is bit of overhead.

Locks can be maintained at a few levels

1.Row level

2.Page level

3.Table level

Make sure that do locks as little data as possible and for as short a period of time as possible