November 2006 - Posts - Jon Galloway

November 2006 - Posts

Animated GIF Plugin for Cropper + some .NET Animated GIF code

WHAT'S ALL THIS, THEN?

Cropper is a great free screen capture program. It has a cool plugin system which lets you send the screenshots anywhere you can write code to send it. I wrote a plugin to save a portion of the screen to an Animated GIF.

I originally wrote it for use in a weblog post, but got so caught up in the ins and outs of image processing that I never posted the article that got this all started or the plugin I'd written. I just realized last week when I was using the plugin that I still needed to publish it, so here it is.

BUT, WHY?

There are some inexpensive screen recorders that save to animated GIF, but I really like working with Cropper and figured an Animated GIF plugin just made sense. I was just talking with a friend who recently started a technical blog (a very good one, I might add), and we both agreed that the way Cropper simplifies the "screenshot / open in editor / crop / convert format / upload" cycle is huge. Screenshots really enhance the readability of a blog1, and keeping the friction as low as possible helps make sure you'll do it. I like the simplicity of using one tool for all my screenshots.

CREDITS AND TECH STUFF

I made use of some code from a CodeProject article, NGif. This code was a direct Java to .NET port of Kevin Weiner's AnimatedGifEncoder, which in turn was based on a bunch of public domain code dating back as far as 1994. The GIF Quantization uses NeuQuant, an interesting Neural Network quantizer. NeuQuant is a little slower than the Octree quantization that's so hot these days, but NeuQuant generally produces better quality images for the same file size. Don't your animated GIF images deserve the neural network treatment?

My plugin code is published under public domain license. Based on my research on the origin of the code above, I believe all the NGif code is public domain as well. I did some significant refactoring to the NGif code; the CodeProject version was a direct port from Java and didn't leverage .NET Framework classes like System.Drawing. Bottom line - after a good amount of research, I believe that all this code is under public domain license.

I wrote about all this license silliness previously.

I'm pretty happy with the way the plugin turned out. My first pass at it was pretty simple - take a picture every tenth of a second and add it to the animated GIF. I added an optimization which cut the file size way down, but took a bit more work - I compare each picture with the previous image and only added to the GIF if the image has changed. Since the Animated GIF format requires you to include the time duration of each image as it is added, I end up having to keep the image until the next one is added or capturing is stopped so I can calculate the duration that goes with the image.

I referenced this CodeProject article for comparing two images by using an MD5 hash, although my code just grabs a string hash and stores it rather than comparing two images directly.

Here's the main brains of the code:

 

private void HandleImage(Image image)
{
string currentHash = GetHashFromImage(image);
DateTime timeStamp
= DateTime.Now;

//Check if image has changed from previous
if(currentHash != _previousHash)
{
if(_previousTimestamp != DateTime.MinValue)
{
//This is not the first image being added
AddImageToAnimation(timeStamp);
}
StoreImage(image, currentHash, timeStamp);
}
}

private void StoreImage(Image image, string imageHash, DateTime timeStamp)
{
_previousImage
= (Image)image.Clone();
_previousHash
= imageHash;
_previousTimestamp
= timeStamp;
}

private void AddImageToAnimation(DateTime timeStamp)
{
TimeSpan timeSpan
= timeStamp.Subtract(_previousTimestamp);
_AnimatedGifEncoder.SetDelay(timeSpan.Milliseconds);
this._AnimatedGifEncoder.AddFrame(_previousImage);
_previousImage
= null;
}

DOWNLOAD

You can download the code and source for this plugin from http://www.codeplex.com/cropperplugins. (note: updated)

INSTALLATION

To install, copy all files to your Croppper\Plugins directory.

OTHER RANDOM TIPS

  • You can edit your animated GIF's with Microsoft GIF Animator.
  • It can be a little tricky to start recording, as a commenter noted. I use Cropper in "Always on top" mode and use the "S" key while Cropper is focused to start recording. "S" again stops recording.
  • You can host animated GIF's on Flickr, but only the original size will show the animation. You can find that by clicking on the All Sizes button when you're viewing the picture, then pick the largest size (which should be the default).
  • I highly recommend the Flickr Output Plugin for Cropper.
  • You can combine TimeSnapper's history browser and Cropper Animated GIF captures for some fun results.

1I'll readily agree that the animated GIF overload on this one particular post makes it much tougher to read. Please use your powers for good. I think this is a special case - I can't really blog about animated GIF's without including a few, can I?

Posted by Jon Galloway | 5 comment(s)
Filed under: , , ,

Handling "GO" Separators in SQL Scripts - the easy way

If you've ever had to execute one or more SQL scripts from ADO.NET, you've likely run into the GO batch terminator issue. Any SQL script that does anything worthwhile has more than one batch, separated by a GO terminator. The problem is that "GO" isn't valid T-SQL, it's just a command used by the SQLCMD, OSQL and ISQL utilities that can also be used within Query Analyzer and the Query Editor window. If you try to execute T-SQL scripts with GO commands in them via ADO.NET SqlCommand.ExecuteNonQuery, you'll get an error that says something like:

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch

Until recently, there have been two ways to handle this problem - execute SQL scripts by shelling to OSQL, or splitting the script on GO separators and running them in sequence. Both solutions kind of worked, but SQL Server Management Objects (SMO) has a better solution for us: Server.ConnectionContext.ExecuteNonQuery(), which parses T-SQL statements and "gets" the GO statement as a batch separator. And the crowd goes wild!!!

I'm telling you, if you're doing anything with SQL Server from .NET code, you really have to look at SMO. 

Here's a simple sample app that iterates SQL scripts in a directory and executes them with that fancy ConnectionContext.ExecuteNonQuery - the one that rocks a house party at the drop of a hat, while retaining the ability to beat a biter down with an aluminum bat:

using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;

//Microsoft.SqlServer.Smo.dll
using Microsoft.SqlServer.Management.Smo;
//Microsoft.SqlServer.ConnectionInfo.dll
using Microsoft.SqlServer.Management.Common;

public class RunAllSqlSriptsInDirectory
{
public static void Main()
{
string scriptDirectory = "c:\\temp\\sqltest\\";
string sqlConnectionString = "Integrated Security=SSPI;" +
"Persist Security Info=True;Initial Catalog=Northwind;Data Source=(local)";
DirectoryInfo di
= new DirectoryInfo(scriptDirectory);
FileInfo[] rgFiles
= di.GetFiles("*.sql");
foreach (FileInfo fi in rgFiles)
{
FileInfo fileInfo
= new FileInfo(fi.FullName);
string script = fileInfo.OpenText().ReadToEnd();
SqlConnection connection
= new SqlConnection(sqlConnectionString);
Server server
= new Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(script);
}
}
}

SQL Puzzle #1 - Answer

Here's my solution to the puzzle in my previous post. If you haven't seen the challenge, go back and read that first.

Are you reading this before trying to solve the puzzle on your own? Unacceptable! Your IP is being logged! C'mon, give it a try! 

My Solution

I used 29 lines and 3 temp tables. It's reasonably straightforward - I set up a rank table (#temp), cross join it with the Student table to get ten rows for each student (a cartesian product), then join it to the grades table in order and select out the result.

The one little trick is the ranking in the selct for #temp3 - I used the GradeID as the tie-breaker if the student gets the same grade more than once. Without a tie-breaker, a duplicate score would result duplicating one Row number and  skipping the next (e.g. 1,2,3,4,5,5,7,8,9,10).

How would you solve this?  

use [test]
go

IF OBJECT_ID('tempdb..#temp') IS NOT NULL drop table #temp
create table #temp (Row int)
declare @counter int
set @counter = 0
while @counter < 10
begin
set @counter = @counter + 1
insert into #temp (Row) values (@counter)
end

IF OBJECT_ID('tempdb..#temp2') IS NOT NULL drop table #temp2
create table #temp2 (Row int, StudentID int)
insert into #temp2 select Row, StudentID from Student, #temp order by StudentID, Row

IF OBJECT_ID('tempdb..#temp3') IS NOT NULL drop table #temp3
select *, (SELECT COUNT(*) FROM Grade pt2 WHERE pt2.Grade + (.001*pt2.GradeID) >= pt.Grade + (.001*pt.GradeID) AND pt2.StudentID = pt.StudentID) AS Row
into #temp3
from Grade pt order by StudentID, Grade desc

select a.Row, a.StudentID, s.FirstName, s.LastName, b.Grade from
#temp2 a
left join #temp3 b on a.StudentID = b.StudentID and a.Row = b.Row
inner join Student s on a.StudentID = s.StudentID

drop table #temp
drop table #temp2
drop table #temp3
Posted by Jon Galloway | with no comments
Filed under: ,

SQL Puzzle #1

Background

I had to write a semi-interesting SQL query this past week and thought it might make for a fun SQL puzzle (for very small values of "fun").

I'm working on a bio-tech business intelligence application, but I simplified things way down to two tables in a SQL Server 2000 database: Student and Grade. The student may have any number of grades, and may of course may get the same grade several times.

StudentID FirstName LastName
1 Bill Smith
2 Bobby Brown
3 Derek Zoolander
GradeID StudentID Grade
1 2 61
2 3 98
3 1 87
... ... ...
18 1 58
19 2 82
20 3 68

 

Task

Prepare a report that shows the top ten grades for each student. You must show exactly ten rows for each student, so if they have less than ten grades you should show a null. Remember that you need to handle the case where the same student gets the same score more than once (e.g.- in my results below, check out Bobby Brown's top two grades are both 82).

Row StudentID FirstName LastName Grade
1 1 Bill Smith 94
2 1 Bill Smith 87
3 1 Bill Smith 82
4 1 Bill Smith 67
5 1 Bill Smith 62
6 1 Bill Smith 58
7 1 Bill Smith NULL
8 1 Bill Smith NULL
9 1 Bill Smith NULL
10 1 Bill Smith NULL
1 2 Bobby Brown 82
2 2 Bobby Brown 82
3 2 Bobby Brown 72
7 3 Derek Zoolander 53
8 3 Derek Zoolander NULL
9 3 Derek Zoolander NULL
10 3 Derek Zoolander NULL

My answer will not use cursors and will run on standard SQL 2000 T-SQL, but if you'd like to submit answers using the SQL 2005 ranking functions, go for it. Post your answers as comments; my answer will be in the next post.


Here's a SQL script with some sample data. You'll need to create a "test" database to run this. 

 

use [test]
go

if exists (select * from information_schema.tables where table_name = 'Grade') drop table Grade
if exists (select * from information_schema.tables where table_name = 'Student') drop table student

create table Student(
StudentID
int identity(1,1) primary key,
FirstName
varchar(50),
LastName
varchar(50)
)

insert into student(firstname, lastname) values ('Bill','Smith')
insert into student(firstname, lastname) values ('Bobby','Brown')
insert into student(firstname, lastname) values ('Derek','Zoolander')

create table Grade(
GradeID
int identity(1,1),
StudentID
int,
Grade
float
)
ALTER TABLE Grade WITH CHECK ADD CONSTRAINT FK_Grade_Student FOREIGN KEY([StudentID]) REFERENCES Student(StudentID)

declare @counter int
declare @grade int
set @counter = 0
while @counter < 20
begin
set @counter = @counter + 1
set @grade = cast(50*rand(cast(cast(newid() as binary(8)) as int)) as int) + 50
insert into Grade (StudentID,Grade) values (@counter % 3 + 1,@grade)
end
select * from Grade
go
Posted by Jon Galloway | 2 comment(s)
Filed under: ,
More Posts « Previous page