Do you have problems with Community Server Database size?
If you have your Community Server web site at a hoster, have you been confronted with the issue of a growing CS Database?
In a conversation with Keyvan the other day, he pointed me to a forum thread at communityserver.org, and hinted that this might be a good idea for a CSJob. 
In that thread a user wants to delete certain posts in the CS Database at certain intervals (although the question from the beginning is more like if mirrored blog posts does expire or if they have to be deleted manually).
Since I haven't had the time to find out which tables and which posts that would need to be deleted in this case, I thought I'd create a more general CSJob that could run any SQL Stored Procedure in the Community Server Database. This way this job could be used for much more that just deleting Mirrored Blog Posts. All you would need is to create a Stored Procedure for what you want to do.
So I created a CSJob, which I called CSSqlJob that takes one parameter. The name of the SQL Stored Procedure. You can't put any parameters on the Stored Procedure in this version. But I figured there might not be a great need for that.
So what is look like? To get a background on how to create a CSJob, you could take a look at Keyvan's article in the topic.
First I do the following imports to my code:
Click on the picture to see it in original size
Then in the Execute method of the CSJob, first read in the parameter for the SQL Stored Procedure (which you can set in communityserver.config where you declare the CSJob). Then run the Stored Procedure, and if anything goes wrong let's write that to the Community Server Event Log.
Click on the picture to see it in original size
The code for running the Stored Procedure just creates a connection object and a command object and executes the command. As you can see I also declare the type of the command to be a Stored Procedure.
Click on the picture to see it in original size
So how do I get the connection string? Well, in this case I've used the SQLCommonDataProvider.
Click on the picture to see it in original size
And to create an entry in the CS Event Log in case something goes wrong the following code is run.
Click on the picture to see it in original size
After this just compile the code, and put the DLL in you CS Bin folder, and put an entry like the following in the 'Jobs' section of your communityserver.config file.
Click on the picture to see it in original size
Now this code has been written pretty fast just as an example. I don't know if this is the best way to do it performance and/or security wise. But it works! Feel free to give any comments on the code.
If you don't want to build this DLL yourself, you can download it from my downloads area.
So with this CSJob, you should be able to do any job a Stored Procedure can do on your CS Database on a scheduled basis. Now it's just a matter of writing that Stored Procedure to do what you want!
If you enjoyed this post Subscribe to my feed via RSS or e-mail!