How do I use a SQL CLR Method to call a Webpage?

Question

I have an asp.net mvc application that take a while to load on my production server. I would like to write a script to call my pages every 10 minutes to avoid the pages from being scrapped on the server, which would then cause the server to reload them.

I was thinking of using a SQL Server stored procedure to call my pages every 10 minutes to keep the pages alive.

I've read that I can do this using CLR, but I am not sure how. Does anyone have an example of how to call webpages in a SQL Stored Procedure using CLR?

John Saunders · Accepted Answer · 2011-01-11 15:12:54Z

6

I have no idea why you would want to use a stored procedure for this.

Just write a simple console application to "call" the page. Then use a scheduled task to run the console application.

answered Jan 11, 2011 at 15:12

John Saunders

162k26 gold badges252 silver badges403 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Over a year ago

I went ahead and built a windows service application to do this.

Jafin · Accepted Answer · 2011-01-13 03:55:46Z

1

Code untested but something like this should work.

You'll probably also need TRUSTWORTHY SET.

ALTER DATABASE Foo SET TRUSTWORTHY ON;

Code:

public partial class WebProc
{
   [SqlFunction()]
   public static string WebQuery()
   {
      WebRequest request = HttpWebRequest.Create("http://www.google.com");
      WebResponse response = request.GetResponse();
      Stream dataStream = response.GetResponseStream();
      StreamReader reader = new StreamReader (dataStream);
      string responseFromServer = reader.ReadToEnd();
      return responseFromServer;
   }
}

answered Jan 13, 2011 at 3:55

Jafin

4,4311 gold badge42 silver badges54 bronze badges

3 Comments

John Saunders Over a year ago

-1: especially inside of SQL Server, I think you'd better put response, dataStream, and reader inside of using blocks.

Jafin Over a year ago

@John. Sure the code can be made safe. This was merely to illustrate the "How do I" question. As I'm not answering the "Should I question".

John Saunders Over a year ago

far too many developers copy / paste what they see on SO and elsewhere. Fix the code and I'll remove the downvote.

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

I have no idea why you would want to use a stored procedure for this.

Just write a simple console application to "call" the page. Then use a scheduled task to run the console application.

You could do that (of course) but that's not what the question was asking ;)

Jafin's answer is correct for the question.

The best answer (IMHO) would be to fix your production server (i.e. reconfigure the settings) so that it doesn't "scrap" your pages every 10 minutes.

Why use a jackhammer (console app or .net inside the database or whatever) when a regular one will do?

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 21, 2011 at 6:50

Tod Thomson

4,9692 gold badges35 silver badges34 bronze badges

Collectives™ on Stack Overflow

How do I use a SQL CLR Method to call a Webpage?

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related