ATTENTION ALL FANS!!! THIS BLOG HAS MOVED!!!
go to: http://www.taotekaching.com

Wednesday, March 25, 2009

A Stupid Anonymous Thread Trick, and Me…

Ok, the storyline:  A web site creates a temporary file to work with, then is supposed to delete it, because maybe space is low on the drive or whatever.  There are times where the file will still be locked from our work when we try to delete it.  Perhaps we did something like:

File.WriteAllBytes("newFile", File.ReadAllBytes("oldFile"));

And it hasn’t released the lock on “oldFile” when we get to the next statement.  Oh man, that’s a problem.  A BIG problem.  A HUGE problem.

Not anymore!  Introducing a stupid anonymous thread method pause trick we can do:

bool deleted = false;

int tries = 3;

while ((!deleted) && (tries > 0))

{

    try

    {

        File.Delete("theFile");

        deleted = true;

    }

    catch

    {

        Thread pauser = new Thread(

            new ParameterizedThreadStart(

                delegate(object o)

                {

                    System.Threading.Thread.Sleep(500);

                }));

        pauser.Start();

        while (pauser.IsAlive) ;

        tries--;

    }

}

Oh god yeah…

Ok, a quick run-through.  We try to delete our file.  An exception is caught, so to allow the current thread to finish any IO operations, we create a temporary thread (pauser), which we wait for to finish.  Once it’s done, we try again until either we succeed or just give up.

My sandbox for playing around with this idea is here.  I have a question, though, that needs answering desperately:

              Is this useful anywhere?

If so, please provide an example of where this would be great to use.  I just need to know I didn’t fully waste my time.

~ZagNut

Submit this story to DotNetKicks

Tuesday, March 17, 2009

Copying tables from one database to another, and me…

So, lately I had the dilemma of having done a bunch of preliminary SQL work in 2008 Express, then needing to port my results over to a staging SQL 2005 server at work.  I casually backed up the 2008 database and lo and behold, couldn’t restore it!  SSIS wasn’t set up, and I had to gets stuff done ASAP!  I searched on Google, and all I got were either “use SSIS” or “it can’t be done.”  In other words, those who’ve done this before haven’t shared with the rest of us.

So a proc I wrote came in handy…

I threw together this stored procedure, linked to the remote 2005 server, and used this proc to move all my data.  It’s very simple and WYSIWYG-ish right now, as it:

  • doesn’t copy over indexes
  • doesn’t copy over table schemas
  • just copies the tables into a new database with the same table names and data

A Quick Sample Run-Through

So, first let’s create a database named SimpleSample.  We’ll create two tables, one with prime numbers and one with the beginning of the Fibonacci sequence.

For our primes:

useSimpleSample

go

 

declare@table table

(

      primesint

)

declare@counter int

set@counter = 1

 

while (@counter< 1000)

begin

      insert into @table(primes) values(@counter)

      set @counter =@counter + 1

end

 

set@counter = 2

while ((selectcount(1) from @table where primes >@counter) >0)

begin

      delete from @table

      where (((primes % @counter) = 0) and (primes > @counter))

 

      select top 1 @counter= primes from@table where primes >@counter order byprimes

end

 

select*

intoPrimes

from@table

 

Now for Fibonacci:

useSimpleSample

go

 

setnocount on

declare@fibo table

(

      naccibigint

)

while (((selectmax(nacci) from @fibo) < 1000) or ((select COUNT(1) from @fibo) = 0))

begin

      if ((select count(1) from @fibo) > 1)

      begin

            insert into @fibo(nacci)

            select sum(i) as nacci from (

                  select top 2 nacci as i from @fibo order by nacci desc

            ) tops

      end

      else

            insert into @fibo(nacci) values(1)

end

 

select*

intoFibonacci

from @fibo

Now that we have two sample tables we’re going to copy, create a second database named SimpleSample2.  Our routine will be simple: get a list of the names of our source tables, and for each name in the list, SELECT from the table with that name in our source database INTO a new table with that same name in our destination database.

There are many ways to get the list of tables.  I ended up using the sys.sp_tables proc:

declare@temp table

(

      TABLE_QUALIFIERnvarchar(200)

      , TABLE_OWNER nvarchar(200)

      , TABLE_NAME nvarchar(200)

      , TABLE_TYPE nvarchar(200)

      , REMARKS nvarchar(max)

)

begin

 

declare@sourceDatabase varchar(100)

set@sourceDatabase = 'SimpleSample'

 

declare@spTables varchar(1000)

set@spTables = 'exec '+ @sourceDatabase +'.sys.sp_tables'

 

insertinto @temp

execsp_sqlexec @spTables

 

select* from @temp

 

end

After that, I cleaned out the data to just dbo created tables:

delete

from@temp

whereTABLE_TYPE <> 'TABLE'

 

declare@ourTables table

(

      idxint identity(1,1)

      , name nvarchar(200)

)

 

insertinto @ourTables

select TABLE_NAME from @temp

Finally, throw those tables into the destination database:

declare@name nvarchar(200)

declare@query varchar(2000)

declare@idx int

while ((selectCOUNT(1) from @ourTables) > 0)

begin

      select top 1 @idx = idx, @name = name from@ourTables

     

      set @query = 'SELECT * INTO ' +@toDb + '.dbo.['+ @name + '] FROM ' + @name

      exec sp_sqlexec @query

     

      delete from@ourTables where idx =@idx

end

The SQL sources are here.  I’m sure it can be spruced up to do many more great things, such as those I initially listed.  If you decide to add anything, please post in the comments or try to get your additions / changes to me and I’ll update the SQL.  You’ll even have the extra bonus of being credited on this immensely popular blog!

~ZagNut

Submit this story to DotNetKicks

Saturday, March 07, 2009

Duplicate Files, Hash Codes, SQLite, and Me…

My wife’s been getting on my case about having a gazillion different hard drives with everything and our mothers on them all around the house.  I mean, come on everybody, she just wants her pictures in one @%$!# spot!  She also “misused” Picasa, and now has a bunch of duplicates on her laptop (she doesn’t read my blog, so I ain’t worried she’ll read that).

So, out shopping for Little Liam last weekend, and we decide to pop into Circuit City’s closing-its-doors blowout sale.  I grabbed her a 500 GB Western Digital external drive and, when we got home, proceeded immediately on a simple solution to shut her pie hole.

The result: MyPicturesConsolidator!  It is a WYSIWYG image grabber, duplicate detector, and file-copier-consolidator all in one, gorgeous package!

Ok, this program is NOT a work of art, but may contain some good stuff you can use, and it works pretty solidly, so…

mpc

How it works:

First, you select where you want any pictures it finds to get copied to:

mpc_dest

Second, select the logical drive you want to scan for pictures.  I included a Refresh Drive List button for changing between USB drives:

mpc_src

Third, click Find My Pictures!  And you’re good!

Behind the scenes:

I wanted a “list” to be maintained that kept track of files we’ve gone through.  I decided to use a SQLite database that would hold MD5 and SHA1 hashes of the pictures.  A good side effect of this is, just take it with the exe and SQLite dll to another computer along with your destination drive (or network share path, etc.), and the duplicates list maintained in the SQLite db should work golden for you.

MD5 and SHA1 generation is, for lack of a better phrase, retardedly easy via .NET.  An MD5 hash of a file, for instance, can be had in one line of code:

byte[] md5Hash = new System.Security.Cryptography.MD5CryptoServiceProvider().ComputeHash(System.IO.File.ReadAllBytes(filename));

The code is here.  Go ahead and take a look.  There’s some dumb things I’m doing in there that deal with my wife’s needs (i.e. Picasa uses file creation dates, ergo I try to find the earliest for her when I can, etc.).

~ZagNut

Submit this story to DotNetKicks