数据库优化 - Sql Server参数化查询优势

1.防止SQL注入

2.提高性能：可以复用查询计划，大数据量更能体现

3.参数化查询参数类型为可变长度时(varchar,nvarchar,char等)请指定参数类型，若为值类型(int,bigint,decimal,datetime等)则无需指定

4.值为varchar(max)或者nvarchar(max)时，参数长度指定为-1即可：

cmd.Parameters.Add(new SqlParameter("@LineItemID", SqlDbType.VarChar, -1) { Value = "0201f3ba-6bb5-427a-b5b0-80bace5d8260" });

示例：
private void btnQuery_Click(object sender, EventArgs e)
        {
            string mCode = "1' or '1'='1 ";
            
            
            string sql = "select * from sys_products where LineItemID=@LineItemID and MaterialCode=@MaterialCode";
            //DataSet ds = SqlHelper.ExecuteDataset(conStr, System.Data.CommandType.Text, sql, paras);
            using (SqlConnection con = new SqlConnection(conStr))
            {
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlParameter[] paras = new SqlParameter[]{
                    new SqlParameter("@LineItemID", SqlDbType.NVarChar, 50){ Value = "0201f3ba-6bb5-427a-b5b0-80bace5d8260" },
                    new SqlParameter("@MaterialCode", SqlDbType.NVarChar, 30){ Value = mCode }
                };
                cmd = AttachParameters(cmd, paras);
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
        }
        private SqlCommand AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
            return command;
        }

查看执行的查询计划

SELECT cacheobjtype,objtype,usecounts,sql,* FROM sys.syscacheobjects 
WHERE sql LIKE '%sys_products%'  and sql not like '%syscacheobjects%'