使用参数化和块语句来提高批处理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时，数据库的优化呀？

DBCommandFactory