SqlCommandBuilder.DeriveParameters 在 SqlTransaction 下的 Bug 的解决方法

我一向都是用开 SqlCommandBuilder.DeriveParameters 的。

但是那个方法有一个BUG。这个BUG导致该方法无法处理关联了SqlTransaction的SqlCommand

具体原因是DeriveParameters是创建一个新的SqlCommand来执行sp_procedure_params_rowset的。

但是临时的SqlCommand没有设置SqlTransaction，这样就直接导致错误。

下面是根据SqlCommandBuilder.DeriveParameters的实现复制出来的一个可用的版本：

namespace LostinetSample { using System; using System.Collections; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; class SqlStoredProcedure { static public void DeriveParameters(SqlCommand cmd) { if(cmd==null)throw(new ArgumentNullException("cmd")); if(cmd.CommandType!=CommandType.StoredProcedure) throw(new ArgumentException("cmd.CommandType!=CommandType.StoredProcedure","cmd")); if(cmd.Connection==null) throw(new ArgumentException("cmd.Connection==null","cmd")); if(cmd.CommandText==null||cmd.CommandText.Length==0) throw(new ArgumentException("cmd.CommandText==null||cmd.CommandText.Length==0","cmd")); string dbname=null; string owner=null; string spname; string[] parts=cmd.CommandText.Split(new char[]{'.'}); switch(parts.Length) { case 1: spname=parts[0]; break; case 2: owner=parts[0]; spname=parts[1]; break; case 3: dbname=parts[0]; owner=parts[1]; spname=parts[2]; break; default: throw(new ArgumentException("cmd.CommandText have too many '.'","cmd")); } string cmdtext="sp_procedure_params_rowset"; if(dbname!=null) { cmdtext=dbname+"."+owner+"."+cmdtext; } else if(owner!=null) { cmdtext=owner+"."+cmdtext; } ArrayList ps=new ArrayList(); #region Fetch Parameters using(SqlCommand dpcmd=cmd.Connection.CreateCommand()) { dpcmd.Transaction=cmd.Transaction; dpcmd.CommandText=cmdtext; dpcmd.CommandType=CommandType.StoredProcedure; SqlParameter ppname=new SqlParameter("@procedure_name",SqlDbType.NVarChar,500); dpcmd.Parameters.Add(ppname); ppname.Value=spname; using(SqlDataReader sdr=dpcmd.ExecuteReader()) { while(sdr.Read()) { SqlParameter param=new SqlParameter(); param.ParameterName=(string)sdr["PARAMETER_NAME"]; switch((short)sdr["PARAMETER_TYPE"]) { case 2: param.Direction=ParameterDirection.InputOutput; break; case 3: param.Direction=ParameterDirection.Output; break; case 4: param.Direction=ParameterDirection.ReturnValue; break; default: param.Direction=ParameterDirection.Input; break; } param.SqlDbType=GetSqlDbTypeFromOleDbType((short)sdr["DATA_TYPE"],(string)sdr["TYPE_NAME"]); if(param.SqlDbType==SqlDbType.Decimal) { param.Scale=(byte)(((short) sdr["NUMERIC_SCALE"])&0xff); param.Precision=(byte)(((short)sdr["NUMERIC_PRECISION"])&0xff); } object objcml=sdr["CHARACTER_MAXIMUM_LENGTH"]; if(objcml is int) { param.Size=(int)objcml; } ps.Add(param); } } } #endregion if(ps.Count==0) throw(new Exception("StoredProcedure Not Found")); foreach(SqlParameter param in ps) { cmd.Parameters.Add(param); } } static SqlDbType GetSqlDbTypeFromOleDbType(short dbType,string typeName) { #region Copy From System.Data.SqlClient.MetaType SqlDbType type1 = SqlDbType.Variant; OleDbType type2 = (OleDbType) dbType; if (type2 <= OleDbType.Filetime) { switch (type2) { case OleDbType.SmallInt: case OleDbType.UnsignedSmallInt: { return SqlDbType.SmallInt; } case OleDbType.Integer: { return SqlDbType.Int; } case OleDbType.Single: { return SqlDbType.Real; } case OleDbType.Double: { return SqlDbType.Float; } case OleDbType.Currency: { return ((typeName == "money") ? SqlDbType.Money : SqlDbType.SmallMoney); } case OleDbType.Date: case OleDbType.Filetime: { goto Label_0104; } case OleDbType.BSTR: { goto Label_0161; } case OleDbType.IDispatch: case OleDbType.Error: case OleDbType.IUnknown: case ((OleDbType) 15): case OleDbType.UnsignedInt: { return type1; } case OleDbType.Boolean: { return SqlDbType.Bit; } case OleDbType.Variant: { return SqlDbType.Variant; } case OleDbType.Decimal: { goto Label_0119; } case OleDbType.TinyInt: case OleDbType.UnsignedTinyInt: { return SqlDbType.TinyInt; } case OleDbType.BigInt: { return SqlDbType.BigInt; } } return type1; } if (type2 == OleDbType.Guid) { return SqlDbType.UniqueIdentifier; } switch (type2) { case OleDbType.Binary: { goto Label_0147; } case OleDbType.Char: { goto Label_00D6; } case OleDbType.WChar: { goto Label_0161; } case OleDbType.Numeric: { goto Label_0119; } case (OleDbType.Binary | OleDbType.Single): { return type1; } case OleDbType.DBDate: case OleDbType.DBTime: case OleDbType.DBTimeStamp: { goto Label_0104; } } switch (type2) { case OleDbType.VarChar: { goto Label_00D6; } case OleDbType.LongVarChar: { return SqlDbType.Text; } case OleDbType.VarWChar: { goto Label_0161; } case OleDbType.LongVarWChar: { return SqlDbType.NText; } case OleDbType.VarBinary: { goto Label_0147; } case OleDbType.LongVarBinary: { return SqlDbType.Image; } } return type1; Label_00D6:; return ((typeName == "char") ? SqlDbType.Char : SqlDbType.VarChar); Label_0104:; return ((typeName == "datetime") ? SqlDbType.DateTime : SqlDbType.SmallDateTime); Label_0119: return SqlDbType.Decimal; Label_0147:; return ((typeName == "binary") ? SqlDbType.Binary : SqlDbType.VarBinary); Label_0161:; return ((typeName == "nchar") ? SqlDbType.NChar : SqlDbType.NVarChar); #endregion } } class Class1 { [STAThread] static void Main(string[] args) { using(SqlConnection conn=new SqlConnection("server=server;trusted_connection=true;")) { conn.Open(); using(SqlTransaction trans=conn.BeginTransaction()) { using(SqlCommand cmd=new SqlCommand("sp_foreign_keys_rowset",conn,trans)) { cmd.CommandType=CommandType.StoredProcedure; SqlStoredProcedure.DeriveParameters(cmd); foreach(SqlParameter param in cmd.Parameters) { Console.WriteLine(param.ParameterName); } } } } } } }

(今天晚上回去看看 DotNet2.0 修正了这个问题没有。。)