Nov 9 2010

Required Reading for Creating and Deploying a WCF REST Service on IIS7

Deploying a REST service with Windows Communication Foundation (WCF) is great, because it’s very powerful, but it can also be enormously frustrating.

First of all, start by reading the whitepaper.

This will give you a great overview on how to construct your methods, and how to label them in order to have them exposed correctly by REST.

Next, you need to create your web.config file. Use webhttpbinding for REST.

Lastly, deployment:

How to use https for WCF services in IIS7

How to rewrite your URLs to get rid of the lame .svc extension

I am posting a small demo project, hopefully it’s useful to someone. Simply implement the API interface as you see fit, and the rest basically does itself. You’ll notice the web.config includes a SOAP binding for legacy Microsoft clients to use if they wish.

Link: API_Template

Aug 11 2010

Select DataSet and number of total rows with one stored procedure

when you want to write a search using .net and MSSQL, it’s a pain. This is because you’re forced to select every row in the table and then only display a small subset of it. This works okay for tables that have a few hundred rows, as query caching can make this faster. But what happens when you’re searching a table with half a million rows?

Unless you’re a complete masochist, you’re doing to want to split this into a more manageable data set, otherwise you’re gonna eat all the memory on your server. But this means that you can no longer use the DataSet.Tables[0].Rows.Count property to figure out how many rows you have. You can write a second stored procedure that’ll count the rows. But who wants to clog up their database with tons of stored procedures for no reason? Let’s consolidate it into one.

So what does this look like?

First: the stored procedure.

We’ll use output parameters to pass the row count back to our code

create procedure [dbo].[Search]
@searchText varchar(512), @recordsToReturn INT, @pageNumber INT, @numberofrows INT OUTPUT

-- get the page we want to view
select * from
select *, ROW_NUMBER() OVER (ORDER BY creation_timestamp DESC) AS row from [table] where [table].columnName like '%' + @searchText + '%'
AS results WHERE row between (@pageNumber - 1) * @recordsToReturn + 1 and @pageNumber*@recordsToReturn;

-- get the total number of rows, not just the subset we want
set @numberofrows = (select count(*) from [table] where [table].columnName like '%' + @searchText + '%')


Now the C# (this’ll work in VB too, but feel free to convert it yourself)

SqlConnection conn = new SqlConnection();
conn.ConnectionString = ".....your connection string here.....";

DataSet returnData = new DataSet();

SqlDataAdapter da = new SqlDataAdapter( "SearchMessages", conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

da.SelectCommand.Parameters.Add("@searchText", SqlDbType.VarChar).Value = "bob";
da.SelectCommand.Parameters.Add("@recordsToReturn", SqlDbType.Int).Value = 10;
da.SelectCommand.Parameters.Add("@pageNumber", SqlDbType.Int).Value = 1;

//number of rows
SqlParameter outputParameter = new SqlParameter("@numberofrows", SqlDbType.Int, 2);
outputParameter.Direction = ParameterDirection.Output;


da.Fill(returnData, "theData");

int numberOfRowsInDataSet = (int)outputParameter.Value;


Best of luck! As always, leave a message in the comments if you have questions

Jun 17 2010

Batch Delete Performance SQL Server

Deleting old records from a table with > 3 000 00 rows. What’s the best way to do this?

It seems the fastest way to do this is simply to:

delete from [table] where creation_timestamp < dateadd (mm, -6, getdate())

(deleting anything older than 6 months)

It took 3 hours (10916 seconds to be exact) to delete 1.6 million (1,619,433) records this way. (148.35 / second).

We needed to do a second batch the next day, but wanted to split it into batches to try to get better performance.


delete from [table] where pk_id in(
select pk_id from (
SELECT ROW_NUMBER() OVER (ORDER BY creation_timestamp desc) AS RowNumber, pk_id
FROM [table]
where creation_timestamp < '2009-12-16 13:52:08.673') _objectsToDelete WHERE RowNumber between 1 and 100000)

takes 12 minutes. (732 seconds) (136.61 / second).

Strangely, using the TOP command with a subquery takes the longest:

delete from [table] where pk_id in (select TOP 100000 pk_id from [table] where creation_timestamp < '2009-12-16 13:52:08.673')

15 minutes (904 seconds) (110.61 / second)

Have a better way? Let me know in the comments!

Mar 24 2010

New Favourite Terminal Command

Best command ever:

sudo !!

Tells the terminal to run the last command again, but to throw sudo in front of it.

obligatory XKCD link:

Mar 5 2010

ASP.Net 301 Redirect

<script runat="server">
private void Page_Load(object sender, System.EventArgs e)
Response.Status = "301 Moved Permanently";

Jan 20 2010

mysql command doesn’t work in OS X snow leopard

So you install mySql on your mac, and it’s working great, but then you go into terminal to run a mysqldump or something, and it doesn’t work!

you get a message something like:

-bash: mysqldump: command not found

so how do you fix this? well, copy and paste the following into your terminal window and press enter:

echo export PATH="/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:$PATH" >> ~/.profile

source ~/.profile

whoa, whoa, you say. what is this doing? It makes it so that when you type any command into the terminal, it does a check of these folders before giving you the “command not found” error.

If you want to undo it, you can always edit your .profile, or just delete it.

Jan 12 2010

WordPress on Snow Leopard: Unable to Connect to Database

If an “unable to connect to database” comes up while installing WordPress on a Snow Leopard machine, try adding :3306 to the end of the DB_HOST in wp-config.php

Assuming everything else works, this should solve your problem!

Aug 13 2009

VirtualHostX and mod_rewrite

To enable mod_rewrite manually, see: OS X server tips but if you are using VirtualHostX it changes your default config file. Add the following code to the Directives box:

Options FollowSymLinks
AllowOverride All AuthConfig
Order deny,allow
Deny from all

if you’re using the new version of VirtualHostX, make sure that you select Directory from the dropdown, then use:

Options FollowSymLinks
AllowOverride All AuthConfig

Feb 24 2009

Recursive FTP

Fun thing I found today: how to copy a whole pile of files/folders over from one place to another using ftp. Especially useful on two remote computers in terminal when security isn’t a concern!
wget -r ftp://username: password@domainname