Accessing Web services using SQL CLR

Problem : wanted to trigger event from SQL server to windows service, rather windows service is looking for a data changed as we always do.

Solution : After doing R&D, proposed following solution, which is still having one problem.  This i am writing because, i couldn’t find any complete guide to do this on the internet. just part by part explaining articles were there. Here i am going to combine all those and create complete guide.

Step 1 : Create windows service with REST based web service. (Not going to explain REST services here and hope you are familiar with REST)

Step 2 : You should have running REST service in your machine. As a example i will get the service URL as http://localhost:8090/Sync/NotifyNow/ 

Now just entering this URL on the browser, you should be able to invoke service’s method.

Step 3 : Create  following class, This will handle call from SQL server and pass to windows service. simply call the URL of REST service

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;

public partial class NotificationClr
{
[SqlProcedure]
public static void InvokeNotificationService(SqlString webUrl, out SqlString outPut)
{
string commandResult = string.Empty;

try
{
HttpWebRequest wr = WebRequest.Create(new Uri(webUrl.ToString())) as HttpWebRequest;
wr.Method = “GET”;
WebResponse response = wr.GetResponse();

commandResult = ((HttpWebResponse)(response)).StatusCode.ToString();
}
catch (Exception ex)
{
commandResult = ex.Message;
}

outPut = commandResult;
}
};

STEP 4 : Now we need to generate library from this and register on SQL server.

csc /t:library NotificationClr.cs

If you consider only SAFE permission for the assembly, then above created DLL will be ok. but if you need External_Access or UNSAFE access, then you need to SIGN the dll (Which is explained here http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/10/29/deploying-sql-clr-assembly-using-asymmetric-key.aspx)

So once you have decided that your assembly needs to be enabled for “External_Access” or “Unsafe”, you have two options. Either set Trustworthy Property of a database to ON or to sign your assembly with a certificate or Asymmetric key . The easiest way is to set the Trustworthy Property of a database to ON, but it’s not good from security prospective. The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. But once you set it to ON , it might open up doors for malicious program. So the best way is to sign your assembly with a certificate or asymmetric key.

Then place the created DLL and  Key file together when you are doing below step.

STEP 5: Register this library on SQL server. here if we use SAFE permission(refer http://msdn.microsoft.com/en-us/library/ms345101.aspx), we don’t have to do much. but in our case we should have web access, which is not coming under SAFE permission.

So all the things we need to do for this i have created as one SQl query, which will create all the necessary things as well as it will handle already existing objects too.

–Important —– Change the DB name and DLL path before execute————–

–enable clr
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO

sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

PRINT (‘————–End enable clr————–‘)

PRINT (‘————–start deleting old notification clr if any————–‘)

USE DbName

GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’NotificationClrLogin’)
DROP USER [NotificationClrLogin]
GO

GO
IF object_id(‘InvokeNotification’) IS NOT NULL
DROP PROCEDURE [InvokeNotification]
GO

GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = ‘NotificationClrAssembly’)
DROP ASSEMBLY NotificationClrAssembly;
GO

GO
IF object_id(‘InvokeNotification’) IS NOT NULL
DROP PROCEDURE [InvokeNotification]
GO

———————————————————————————

USE [master]

GO
If Exists (SELECT loginname FROM master.dbo.syslogins WHERE name = N’NotificationClrLogin’ AND dbname = ‘master’)
DROP LOGIN [NotificationClrLogin]
GO

GO
IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = ‘NotificationDllKey’)
DROP ASYMMETRIC KEY NotificationDllKey
GO

PRINT (‘————–end deleting old notification clr if any————–‘)

———————————————————————————————-

USE [master]

GO
CREATE ASYMMETRIC KEY NotificationDllKey
FROM EXECUTABLE FILE = ‘D:\SQL\CLR\NotificationClr.dll’
GO

PRINT (‘—————NotificationDllKey done————–‘)

GO
CREATE LOGIN NotificationClrLogin FROM ASYMMETRIC KEY NotificationDllKey
GO

PRINT (‘—————NotificationClrLogin done————–‘)

GO
GRANT EXTERNAL ACCESS ASSEMBLY TO NotificationClrLogin
–GRANT UNSAFE ASSEMBLY TO NotificationClrLogin
GO

PRINT (‘—————GRANT EXTERNAL ACCESS done————–‘)

———————————————————————————————-
USE DbName

GO
CREATE USER NotificationClrLogin FOR LOGIN NotificationClrLogin
GO

PRINT (‘—————CREATE USER NotificationClrLogin done————–‘)

GO
CREATE ASSEMBLY NotificationClrAssembly
FROM ‘D:\SQL\CLR\NotificationClr.dll’
–WITH PERMISSION_SET = UNSAFE
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

PRINT (‘—————NotificationClrAssembly done————–‘)

GO
CREATE PROCEDURE [dbo].[InvokeNotification]
@weburl [nvarchar](4000),
@returnval [nvarchar](2000) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [NotificationClrAssembly].[NotificationClr].[InvokeNotificationService]
GO

PRINT (‘—————CREATE PROCEDURE done————–‘)

PRINT (‘—————-End of all commands—————‘)

—-Call WS
–GO
–Declare @Response NVARCHAR(2000)
–EXECUTE InvokeNotification ‘http://localhost:8090/Sync/NotifyNow’,
— @Response OUT
–SELECT
— @Response
–GO

Now you can call this SP in any place inside SQL server. Then it will call method on the DLL, that method will call REST service URL (here i am passing URL from SQL, so i don’t need to change the dll time to time)

Problem i am still having is, if web service(REST service) and SQL server are on same machine, then it works fine (so URL is localhost). if i tried to call remote web service (or website), then it works 2 times, JUST 2 TIMES. from third call, it starts to time out.

It starts to work again after i restarted sql server. (just 2 calls only). But in the same machine, this works perfectly.

I used following resources to complete this task

1. http://blogs.msdn.com/b/sqllive/archive/2008/06/18/accessing-rest-based-web-services-using-sql-clr.aspx

2. http://msdn.microsoft.com/en-us/library/ms345101.aspx

3. http://msdn.microsoft.com/en-us/library/ms189524.aspx

4. http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/10/29/deploying-sql-clr-assembly-using-asymmetric-key.aspx

If you have any questions about REST service, which i didn’t explained here, please ask in the comments, will explain in another post.

Advertisements

Blank.gif missing error on ReportViewer

Problem : While going through one of our web product, i got a report with a missing image on it. when i check the other reports also, all were having same issue. Reports were developed using RDLC. i was using google chrome. But this issue was not there on IE.  Might be first time i found that IE is working and all other browsers not working.

 

The html code was like this, related to missing image.

 

But i checked the report design and blank.gif image is not used anywhere. so i assumed that this should be an issue with report viewer.

I was correct when i checked on google. As mentioned on the below URl, issue with the report viewer.

https://connect.microsoft.com/VisualStudio/feedback/details/556989/

I used one of workaround, this link mentioned and it is fixed now. that was adding following code to global.asax.

void Application_BeginRequest(object sender, EventArgs e)
{
// Bug fix for MS SSRS Blank.gif 500 server error missing parameter IterationId
// https://connect.microsoft.com/VisualStudio/feedback/details/556989/
if (HttpContext.Current.Request.Url.PathAndQuery.StartsWith(“/Reserved.ReportViewerWebControl.axd”) &&
!String.IsNullOrEmpty(HttpContext.Current.Request.QueryString[“ResourceStreamID”]) &&
HttpContext.Current.Request.QueryString[“ResourceStreamID”].ToLower().Equals(“blank.gif”))
{
Context.RewritePath(String.Concat(HttpContext.Current.Request.Url.PathAndQuery, “&IterationId=0”));
}
}

 

 

 

Installshield LE Dependency dll build issue

I was trying to make an Win app installer package with installshield limited edition. I added the project primary output and the setup built successfully, it is installed correctly but now the problem is,

Three of the referred projects dlls are properly built, but one project  dll is not latest. meaning that an old version of that dll is built and copied in the setup.

After hours searching on the google, trying rebuilding and trying changing project build order, still couldn’t overcome the issue. But finally this was the solution.

1. Open File option on installshield project. (Setup project –> (2)Specify Application Data –> File)

2. Go to destination computer files.

3. Right click on Primary Output and select ‘Dependencies from scan at build’.

4. That’s It. anyway i hate this new Installshield LE, previous setup and deployment option was much simpler.

(Will add a screen later, right now i am getting error when try to attached image to post. )

Decimal TextBox with jQuery

Problem : I had to work with formatting decimal places for a quantity text box (or label) on a halfway developed project.  I wanted a solution, where i can handle this on one place and applies everywhere with quantity text box.

Solution :

1. Placed this code on the master page, This section will

• Format all the textboxes content with specific css (QuantityTextBox in this example) on page load,
• allow to enter only numeric values with one decimal mark. (onkeydown, will call the js function called OnlyDecimalNumbers).
• after entering the data, input will format on leave (on onblur event)

 var decimalPoints = Number(‘<%: DecimalPoints%>’); //getting number of decimal points from serverside, which user can //defines

$(“.QuantityTextBox”).each(function () {
var originalText = Number($(this).val());
$(this).val(originalText.toFixed(decimalPoints));
});

$(“.QuantityTextBox”).live(“keydown”, function (event) {
return OnlyDecimalNumbers(event, $(this));
});

$(“.QuantityTextBox”).live(“blur”, function () {
var stringValue = $(this).val();
var lastChar = stringValue[stringValue.length – 1];
if (lastChar == ‘.’) {
stringValue = stringValue.slice(0, stringValue.length – 1);
}

var originalText = Number(stringValue);
$(this).val(originalText.toFixed(decimalPoints));
});

2. Added following function on JS file.

//common function to use for allowing only numbers with decimal places. call on onkeydown
function OnlyDecimalNumbers(event, control) {
var existingValue = control.val();
var charCode = (event.which) ? event.which : event.keyCode;

if (event.shiftKey)
return false;

if (charCode == 46 || charCode == 8)
return true;

if (existingValue.indexOf(“.”) == -1 && charCode == 190)
return true;

if (charCode > 31 && (charCode < 48 || charCode > 57))
return false;

return true;
}

3.  Finally add “QuantityTexBox” css on all the quantity showing/adding textboxes.

Pros on this solution : Can handle all the quantity related formatting on one place, if we have added above css on all the qty textboxes.

Cons : If we need to different CSS per textboxes for styles, we cannot do this, on my situation we were not adding styles directly to textboxes.

EF Navigation Property Looping Issue

This is a problem i faced today, while working on a project which uses EF 4.0.

In my db, there are two tables, say Item and Status. Item’s status is referring status table. Note that there are another tables like ‘Category’, ‘Type’ which are also referred by ‘Item’ table. This table structure wasn’t changed recently and was working fine with EF (4.0 with ObjectContext).  Recently we loaded around 1 million items to Item table for testing. After that, item updating and inserting started to work abnormal way. when i closely look in to matter, it was happening when i assign a value to reference property. As a example,

exitingItem.ItemStatusId = itemStatus;
context.SaveChanges();

when i execute first line, it is going loop forever. What i wonder is, this is happening only when i assign value. so this is happening on context. even not before save to DB.

After goggling, i found the exactly same problem on stackoverflow.

http://stackoverflow.com/questions/6361272/ef-navigation-property-looping-issue

This explains what is happening with POCO generated class.

I have a another project, there i used EF5 with DbCotext. when i test the same scenario with same DB, it was working fine. Reason is DbContext does not generate Fixup methods.

Solution : As above link explained, solutions are

• Remove the Person collection from your AddressStatusLookup class (as you already tried with success). It’s usefulness is questionable anyway.
• Or: Don’t work with lazy loading
• Or: Remove the fixup methods from the generated classes. (Perhaps it’s possible to tweak the T4 templates and create a custom template to suppress generating these methods in the first place, but I am not sure.)

With the time frame, disabling the Lazy loading is not possible with us.

removing the navigation property i didn’t consider.

I went with the Removing fixup methods. This was also not straight forward. One article explained to delete fixup generating sections on POCO as this.

If you open the .tt fle, you’ll notice region.Begin("Association Fixup"); (line 303) which ends on line 632. You might want to delete that area and see what you’ll get.

But i had to delete calls to the fixup methods from the property setters, registration of CollectionChanged handlers, and the _settingFK flag from POCO to successfully generate the classes. This way update and insert is happening as normally with 1 million data.

(purpose of fixup is to ensure that each end of a bidirectional relationship stays in sync with the other, but since we use entities via EF context, not interdependently we don’t have to worry for these fixups) 

Debugging Windows Service

I was working with a windows service nearly one year and hopefully I will be working with this in next couple of months, may be next couple of years due to working on a product based company.  🙂

As most of the developers have experienced, it was a headache to debug a windows service.  So think about working with a same windows service around a yearL. Very beginning I found a solution. That was

1. Have a key on app.config  as “DebugMode=true”
2. In the code, I have put following line of code, where I mostly want to debug. (if I want new place, I add same code there) DebugMode value will be assigned based on app.config key.

if (DebugMode)
System.Diagnostics.Debugger.Launch();

1. This was fine. But issue was  with this way is,

Every time I had to compile the code, prepare the setup, uninstall and install on machine. If single line of code changed, had to follow same steps. (Installed version of code and IDE code version should be exactly match)

So today I came up with a better solution. Simple steps. But it saves time, especially when release is nearby :).

1. Get rid of config key 🙂
2. Above debug mode property populated by this way. This will get value based on configuration. If we select debug, this will be true and if we select release, this will be false.

public static bool DebugMode

{

get

{

bool isDebugMode = false;

#if DEBUG

isDebugMode = true;

#endif

return isDebugMode;

}

}

1. I will add here whole Main method of Program.cs class. Only we need to change is, select the correct configuration. After finish the debug, select configuration as ‘release’ and build the setup. Don’t be confused with the code. what I do here is,
   1. If debug mode is true, do exactly what I do in the windows service class and start the application. You have to change the project type to ‘console application’ to open the console. But what I wanted here is not to see console, I just wanted to hit the break point without going through set of steps.
   2. If debug mode is false, call the windows service as normally.

static void Main()

{

if (DebugMode)

{

try

{

string serverURL = string.Format(“http://localhost:{0}/”, ConfigurationManager.AppSettings[“ServerPort”]);

AppHost _appHost = new AppHost();

MiddleWareEngine = new MiddleWareEngine();

MiddleWareEngine.StartEngine();//starting middleware

//Now run the Services.

_appHost = new AppHost();

_appHost.Init();

_appHost.Start(serverURL);

Console.WriteLine(string.Concat(“Middleware Server Started on “, serverURL));

}

catch (Exception ex)

{

Console.WriteLine(“Error: ” + ex.Message);

}

}

Else //Release mode

{

ServiceBase[] ServicesToRun;

ServicesToRun = new ServiceBase[] { new DominateRFIDAlertService() };

ServiceBase.Run(ServicesToRun);

}

}

Good Bye Visual Studio Installer Projects

As Microsoft had announced on 2010 , Visual Studio Installer Projects have been removed with latest Visual studio release. (VS 2012).  Recently i was trying to convert our existing VS 2010 solution to vs 2012, got the problem. All the other projects on the solutions were converted successfully and Setup project gave error as project type not installed. At first i thought that i have to create all the setup project from the beginning.  But with few steps, could convert all to ‘InstallShield Limited Edition’ projects.

1. InstallShield Limited Edition is not installing when we install VS 2012. (I am not sure with other versions, but i use VS 2012 ultimate and it didn’t had InstallShield Limited Edition installed)
2. Even after installing InstallShield Limited Edition, those setup project will not convert by opening it. (which i thought that way would work)
3. First open the Vs 2010 solution with VS 2012, it converts all other projects with error of setup project cannot convert.
4. Remove existing setup project from solution.
5. open add new project dialog for solution. select the ‘Other project type’ and select the ‘InstallShield Limited Edition Project’
6. It should not be installed if you didn’t install it previously. so when you try to open the project it will show a message on browser to download the InstallShield Limited Edition
7. Follow the instruction and download the InstallShield setup. they will send the key to activate to entered email address.
8. After install, you might have to restart the VS. enter received serial no and activate
9. Now same as step 5, add InstallShield setup project and on the InstallShield menu on main menu, select ‘Visual Studio Deployment Project Import Wizard…’
10. Select your vs 2010 setup project file.  Hopefully it will convert without error. i didn’t had to do any changes on converted project.