使用参数化和块语句来提高批处理SQL语句的执行效率（2）

    在执行SQL语句时，有时我们必须使用Like语句，还需要动态依条件拼出SQL然后执行。此时你可以会因为一点点麻烦而拒绝使用参数化SQL，其实一点也不麻烦。

    如下面的代码：

        public static void TestMethod(int? a, string b)
        {
            StringBuilder sb = new StringBuilder("update t set v = 1 where 1=1 ");

            if(a != null)
            {
                sb.AppendFormat(" and a={0}",a);
            }

            if(b != null)
            {
                sb.AppendFormat(" and b like '%{0}%'", b.Replace("'","''"));
            }

            string conntionString = "Data Source=127.0.0.1;Initial Catalog=FoundwaySMS;Integrated Security=True";
            using(SqlConnection conn = new SqlConnection(conntionString))
            {
                conn.Open();

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sb.ToString();
                cmd.CommandType = CommandType.Text;

                cmd.ExecuteNonQuery();
            }

        }

可以替换为：

        public static void TestMethodFactoryParameters(int? a, string b)
        {
            StringBuilder sb = new StringBuilder("update t set v = 1 where 1=1 ");
            List<SqlParameter> prms = new List<SqlParameter>();


            if (a != null)
            {
                sb.Append(" and a=@a");
                prms.Add(new SqlParameter("@a", SqlDbType.Int, 4) { Value = a });
            }

            if (b != null)
            {
                sb.Append(" and b like '%' + @b + '%'");
                prms.Add(new SqlParameter("@b", SqlDbType.VarChar, 50) { Value = b });
                //sb.Append(" and b like concat('%',concat(:b,'%'))");   // for oracle
                /*
                 * sb.Append(" and b like @b"); 
                 *prms.Add(new SqlParameter("@b", SqlDbType.VarChar, 50) { Value = string.Format("%{0}%", b.Replace("'", "''")) });
                */
            }

            string conntionString = "Data Source=127.0.0.1;Initial Catalog=FoundwaySMS;Integrated Security=True";
            using (SqlConnection conn = new SqlConnection(conntionString))
            {
                
                conn.Open();

                SqlCommand cmd = DBCommandFactory.Instance.CreateCommand(conn, sb.ToString()) as SqlCommand;
                cmd.Parameters.AddRange(prms.ToArray());
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }

        }

这样以来，产生的SQL语句，也会发生变化：

--TestMethod 
update t set v = 1 where 1=1  and b like '%u''%'
go
exec sp_reset_connection
go
update t set v = 1 where 1=1  and a=1 and b like '%u''%'
go
exec sp_reset_connection
go
--TestMethodFactoryParameters
exec sp_executesql N'update t set v = 1 where 1=1  and b like ''%'' + @b + ''%''', N'@b varchar(50)', @b = 'u'''
go
exec sp_reset_connection
go
exec sp_executesql N'update t set v = 1 where 1=1  and a=@a and b like ''%'' + @b + ''%''', N'@a int,@b varchar(50)', @a = 1, @b = 'u'''
go

附上DBCommandFactory 的实现，从查询分析器中，看不到这个类存在的必要性。从哪里看重复执行SQL时，数据库的优化呀？

    public class DBCommandFactory
    {
        private ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private static DBCommandFactory instance = null;
        static  DBCommandFactory()
        {
            instance = new DBCommandFactory();
        }

        public static DBCommandFactory Instance
        {
            get{return instance;}
        }

        private Hashtable htCmd = new System.Collections.Hashtable();
        private IDbCommand GetCachedCommand(IDbConnection conn, string sql)
        {
            Type type = conn.GetType();
            if (!htCmd.ContainsKey(type))
                htCmd.Add(conn.GetType(), new Hashtable());

            Hashtable ht = (Hashtable)htCmd[type];
            return (IDbCommand)ht[sql];

        }

        private void AddCachedCommand(IDbCommand cmd)
        { 
            Type type = cmd.Connection.GetType();
            ((Hashtable)htCmd[type]).Add(cmd.CommandText, cmd) ;
            
        }

        public IDbCommand CreateCommand(IDbTransaction tran, IDbConnection conn, string sql)
        {
            IDbCommand cmd = CreateCommand(conn,sql);
            cmd.Transaction = tran;
            return cmd;
        }
        public IDbCommand CreateCommand(IDbConnection conn, string sql)
        {
            IDbCommand cmd = this.GetCachedCommand(conn,sql);
            if (cmd == null)
            {
                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Prepare();

                this.AddCachedCommand(cmd);
            }
            else
            {
                cmd.Parameters.Clear();
                if (cmd is ICloneable)
                {
                    cmd = (IDbCommand)((ICloneable)cmd).Clone();
                    cmd.Connection = conn;

                    cmd.Transaction = null;
                }
                else
                {
                    throw new InvalidCastException("IDbComand must implement ICloneable");
                }
            }

            return cmd;
        
        }




    }