is this shared DbCommand object thread safe?

I don't see why I have to create a DbCommand object every time I need to call a stored procedure. So I'm trying to come up with a way to do that. I have tested my code (see below). But I would like to check with the community in case there is something I have missed. I would be using it with in an ASP.NET app. Is this code thread safe?

SharedDbCommand - wraps up the creation and storage of the DbCommand object

Db - the wrapper for the database, uses the SharedDbCommand via a static field and the ThreadStatic attribute

Program - the console app that starts threads and uses the Db object which

// SharedDbCommand.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace TestCmdPrepare {
    public class SharedDbCommand {
        [ThreadStatic]
        static DbCommand cmd;

        public SharedDbCommand(string procedureName, ConnectionStringSettings dbConfig) {
            var factory = DbProviderFactories.GetFactory(dbConfig.ProviderName);
            cmd = factory.CreateCommand();
            cmd.Connection = factory.CreateConnection();
            cmd.Connection.ConnectionString = dbConfig.ConnectionString;
            cmd.CommandText = procedureName;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            if (cmd is SqlCommand) {
                try {
                    cmd.Connection.Open();
                    SqlCommandBuilder.DeriveParameters(cmd as SqlCommand);
                } finally {
                    if (cmd != null && cmd.Connection != null) 
                        cmd.Connection.Close();
                }
            }
        }

        public DbParameter this[string name] {
            get {
                return cmd.Parameters[name];
            }
        }

        public IDataReader ExecuteReader() {
            try {
                cmd.Connection.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            } finally {
                cmd.Connection.Close();
            }
        }

        public void ExecuteNonQuery() {
     开发者_如何学C       try {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            } finally {
                cmd.Connection.Close();
            }
        }
    }
}

// Db.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Diagnostics;

namespace TestCmdPrepare {
    public class Db {
        ConnectionStringSettings dbSettings;
        DbProviderFactory factory;
        public Db() {
            dbSettings = ConfigurationManager.ConnectionStrings["db"];
            factory = DbProviderFactories.GetFactory(dbSettings.ProviderName);
        }
        IDataReader ExecuteReader(DbCommand cmd) {
            cmd.Connection.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        private DbConnection CreateConnection() {
            var c = factory.CreateConnection();
            c.ConnectionString = dbSettings.ConnectionString;
            return c;
        }

        DbCommand CreateCommand(string procedureName) {
            var cmd = factory.CreateCommand();
            cmd.Connection = CreateConnection();
            cmd.CommandText = "get_stuff";
            cmd.CommandType = CommandType.StoredProcedure;
            if (cmd is SqlCommand) {
                try {
                    cmd.Connection.Open();
                    SqlCommandBuilder.DeriveParameters(cmd as SqlCommand);
                } finally {
                    cmd.Connection.Close();
                }
            }
            return cmd;
        }


        [ThreadStatic]
        static DbCommand get_stuff;

        DbCommand GetStuffCmd {
            get {
                if (get_stuff == null)
                    get_stuff = CreateCommand("get_stuff");
                return get_stuff;
            }
        }

        public string GetStuff(int id) {
            GetStuffCmd.Parameters["@id"].Value = id;
            using (var reader = ExecuteReader(GetStuffCmd)) {
                if (reader.Read()) {
                    return reader.GetString(reader.GetOrdinal("bar"));
                }
            }
            return null;
        }

        [ThreadStatic]
        static SharedDbCommand get_stuff2;
        public string GetStuff2(int id) {
            if (get_stuff2 == null)
                get_stuff2 = new SharedDbCommand("get_stuff", dbSettings);
            get_stuff2["@id"].Value = id;
            using (var reader = get_stuff2.ExecuteReader()) {
                if (reader.Read()) {
                    Thread.Sleep(1000);
                    return reader.GetString(reader.GetOrdinal("bar"));
                }
            }
            return null;
        }
    }
}


// Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;
using System.Threading;

namespace TestCmdPrepare {
    class Program {
        static void Main(string[] args) {
            var db = new Db();
            var threads = new List<Thread>();
            for (int i = 0; i < 4; i++) {
                var one = new Thread(Run2);
                var two = new Thread(Run1);

                threads.Add(one);
                threads.Add(two);
                one.Start();
                two.Start();

                Write(db, 1);
                Write(db, 2);
            }
            Console.WriteLine("Joining");
            foreach (var thread in threads) {
                thread.Join();
            }
            Console.WriteLine();
            Console.WriteLine("DONE");
            Console.ReadLine();
            return;
        }

        static void Write(Db db, int id) {


       Console.WriteLine("2:{0}:{1}", Thread.CurrentThread.ManagedThreadId, db.GetStuff2(id));
        Console.WriteLine("1:{0}:{1}", Thread.CurrentThread.ManagedThreadId, db.GetStuff(id));
    }

    static void Run1() {
        var db = new Db();
        Write(db, 1);
    }

    static void Run2() {
        var db = new Db();
        Write(db, 2);
    }

    }
}

---

Bad idea for lots of reasons. Others have mentioned some of them, but I'll give you one specific to your implementation: using ThreadStatic in ASP.NET will bite you eventually (see here). You don't control the pipeline, so it's possible for multiple threads to end up servicing one request (think between event handlers on a page, etc- how much code is running that isn't yours?). It's also easy to orphan data on a request thread you don't own due to unhandled exceptions. Might not be a showstopping problem, but best case you're looking at a memory leak and increased server resource usage while your connection just sits there...

I'd recommend you just let the ConnectionPool do its job- it has some warts, but performance isn't one of them compared to what else is going on in the ASP.NET pipeline. If you really want to do this, at least consider storing the connection object in HttpContext.Current.Items. You can probably make this work in limited circumstances, but there will always be gotchas, especially if you ever start writing parallel code.

Just $0.02 from a guy who's been there. :)

---

This is not a good practice to keep DbCommand created. Also, this makes your application very complex because of thread handling logic.

As Microsoft suggests you should create and dispose connection and command objects right after you have executed your query. They are very lightweight to create. No need to keep memory used with them - dispose them when your query execution is complete and you have fetched all of the results.