Using SMO to script out Object definitions from SQL server database in .net in Parallel loop

Question

I am using SMO to script out my objects from Sql server database using .Net code. But as of now I am going through a sequential loop.

foreach(var table in TableCollection)
{
 var stringCollection=table.Script();
}

It is working fine. But when I convert the same loop to a Parallel.ForEach loop like:

Parallel.ForEach(TableCollection,table=>
{
 var stringCollection=table.Script();
});

It fails to script. Is there anybody who has used the same kind of approach or any other approach to script out objects from Sql server in parallel?

UPDATE :

I haven't been able to work out Parallel LOOP as of now but I have used below mentioned code :

 server.SetDefaultInitFields(true);                

It improves performance up-to some extent.

Eupatorium

How exactly does it fail? Does it throws some exception? – Sin

Yes, It throws exception that there is already an OPEN Data reader associated with this command.Probably when two threads in parallel trying to script the SERVER object(as same instance is being used) is causing the trouble. – Eupatorium

Answer 1

It seems SMO wasn't built in a thread-safe manner. When you call Script() on a Table, it uses some shared state from its Server, so you can't execute it on two tables from the same Server. But you can work around that by creating new Server object for each Table:

private static TableCollection GetTables()
{
    Server server = new Server(…);
    Database database = server.Databases[…];
    var tables = database.Tables;
    return tables;
}

…

Parallel.For(0, GetTables().Count,
    i =>
    {
        var stringCollection = GetTables()[i].Script();
        …
    });

This will make your queries parallel, but I have no idea whether it will make them actually faster.

EDIT: If you want to create one Server for each thread, you could use an overload of Parallel.For() that allows thread-local initialization. Something like:

Parallel.For(0, GetTables().Count,
    () => GetTables(),
    (i, _, tables) =>
    {
        var stringCollection = tables[i].Script();
        …
        return tables;
    },
    tables => { });

This way, each thread will have its own Server object, but only one.

Sin

Actually this way I know It would work..But the thing is I have tables,SPs and all other possible objects, so this approach seemed to be very costly and especially when no. of objects are running in 10s of thousand. – Eupatorium

In that case, you could just make sure that you have one Server per thread. – Sin

But I am not very sure about, how to access Threads in Parallel loops so that I can make sure I have only one Server Object per thread. Or you are pointing towards creating threads on our own.. – Eupatorium

@PanditA, see edit to my answer on how to do that using an overload of Parallel.For(). – Sin

@Svick..Thanx for the update..I also figured out something like this and also used above code as mentioned by you. But instead of increasing performance..It has degraded, because to use different server for each thread we need to have that much connection. I am still trying to figure out..But just a question is there any known approach to script out Object definitions from Sql server using .net code. – Eupatorium