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() {
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);
}
}
}
