C# MySqlParameter 通配符搜索

Question

我试图有一个函数，可以在给定参数时在数据库中搜索项目(在本例中为主板)，但如果没有给出参数，我希望能够获取所有主板。

查询是:

SELECT * FROM motherboard WHERE slot_type_cpu LIKE @cpu_slot AND hdd_num > @hdd_num AND sata_gen LIKE @sata_gen AND ram_pins LIKE @ram_pins AND ram_type LIKE @ram_gen AND ram_num > @ram_num AND gpu_num > @gpu_num AND gpu_slot LIKE @gpu_slot AND usb_num > @usb_num AND usb_gen LIKE @usb_gen;

这是当前功能:

    public Motherboard[] getMBs(string cpu_slot = "%", string gpu_slot = "%", string hdd_gen = "%", string sata_gen = "%", string ram_gen = "%", string usb_gen = "%", int hdd_num = 0, int ram_pins = 0, int ram_num = 0, int gpu_num = 0, int usb_num = 0)
            {
                try
                {
                    Motherboard[] mbList = new Motherboard[96];
                    Motherboard[] finishedList = new Motherboard[96];
                    MySqlConnection connect = new MySqlConnection(CONNECTION_STRING);

                    MySqlCommand useCmd = connect.CreateCommand();
                    MySqlCommand getMBCmd = connect.CreateCommand();
                    MySqlCommand getItemInfoCmd = connect.CreateCommand();

                    useCmd.CommandText = "use " + DEFAULT_DB + ";";
                    getMBCmd.CommandText = GET_MB_CMD;
                    getItemInfoCmd.CommandText = GET_ITEM_INFO_CMD;

                    connect.Open();

                    useCmd.ExecuteNonQuery();

                    getMBCmd.Parameters.AddWithValue("@cpu_slot", cpu_slot);
                    getMBCmd.Parameters.AddWithValue("@hdd_num", hdd_num);
                    getMBCmd.Parameters.AddWithValue("@sata_gen", sata_gen);
                    getMBCmd.Parameters.AddWithValue("@gpu_slot", gpu_slot);
                    getMBCmd.Parameters.AddWithValue("@hdd_gen", hdd_gen);
                    getMBCmd.Parameters.AddWithValue("@ram_gen", ram_gen);
                    getMBCmd.Parameters.AddWithValue("@usb_gen", usb_gen);
                    getMBCmd.Parameters.AddWithValue("@ram_pins", ram_pins);
                    getMBCmd.Parameters.AddWithValue("@ram_num", ram_num);
                    getMBCmd.Parameters.AddWithValue("@gpu_num", gpu_num);
                    getMBCmd.Parameters.AddWithValue("@usb_num", usb_num);

                    MySqlDataReader reader = getMBCmd.ExecuteReader();
                    reader.Read();

    //Here comes the trouble part.
                    if (reader.HasRows)
                    {
                        int i = 0;
                        while (reader.HasRows)
                        {
                            mbList[i] = new Motherboard(reader.GetString(0), "", "", 0.00, 0, reader.GetString(1), reader.GetString(3), new RAM("", "", "", 0, 0, reader.GetString(5), reader.GetInt32(4), 0), reader.GetString(8), reader.GetInt32(2), reader.GetInt32(6), reader.GetInt32(7), reader.GetInt32(10), reader.GetInt32(11), reader.GetInt32(12));
                            i++;
                            reader.Read();
                        }
                    }
                    reader.Close();

//It doesn't retrieve any items, even though I can retrieve them manually using the same query...

                    int y = 0;
                    foreach (Motherboard m in mbList)
                    {
                        getItemInfoCmd.Parameters.AddWithValue("@id", m.id()); //This gives an Object reference not set to an instance of an object error

                        reader = getItemInfoCmd.ExecuteReader();
                        reader.Read();

                        if (reader.HasRows)
                            finishedList[y] = new Motherboard(m.id(), reader.GetString(3), reader.GetString(4), reader.GetDouble(1), reader.GetFloat(2), m.CPU_Slot(), m.HDD_Conn(), m.RAM(), m.GPU_Slot(), m.HDD_num(), m.RAM_num(), m.GPU_num(), m.USB_num(), m.USB_gen(), m.CPU_num());

                        reader.Close();
                        getItemInfoCmd.Parameters.Clear();
                    }
                    connect.Close();

                    return finishedList;
                }
                catch (Exception e)
                {
                    Error error = new Error();

                    error.reportError(e.Message.ToString() + " " + e.StackTrace.ToString(), DateTime.Now.ToString(), "ItemRetrieval.getMBs: { } ");

                    return null;
                }

编辑:(我如何修复它)我最终做了什么并且效果很好(这可能就是 aleroot 的意思，但我无法解读他们所说的内容......抱歉。):

                if (cpu_slot == "%")
                    getMBCmd.CommandText = getMBCmd.CommandText.Replace("@cpu_slot", "\'%\'");
                else
                    getMBCmd.Parameters.AddWithValue("@cpu_slot", cpu_slot);

                if(gpu_slot == "%")
                    getMBCmd.CommandText = getMBCmd.CommandText.Replace("@gpu_slot", "\'%\'");
                else
                    getMBCmd.Parameters.AddWithValue("@gpu_slot", gpu_slot);

                if(hdd_gen == "%")
                    getMBCmd.CommandText = getMBCmd.CommandText.Replace("@hdd_gen", "\'%\'");
                else
                    getMBCmd.Parameters.AddWithValue("@hdd_gen", hdd_gen);

                if(sata_gen == "%")
                    getMBCmd.CommandText = getMBCmd.CommandText.Replace("@sata_gen", "\'%\'");
                else
                    getMBCmd.Parameters.AddWithValue("@sata_gen", sata_gen);

                if (ram_gen == "%")
                    getMBCmd.CommandText = getMBCmd.CommandText.Replace("@ram_gen", "\'%\'");
                else
                    getMBCmd.Parameters.AddWithValue("@ram_gen", ram_gen);

                getMBCmd.Parameters.AddWithValue("@usb_gen", usb_gen);
                getMBCmd.Parameters.AddWithValue("@hdd_num", hdd_num);
                getMBCmd.Parameters.AddWithValue("@ram_pins", ram_pins);
                getMBCmd.Parameters.AddWithValue("@ram_num", ram_num);
                getMBCmd.Parameters.AddWithValue("@gpu_num", gpu_num);
                getMBCmd.Parameters.AddWithValue("@usb_num", usb_num);

Answer 1

您可以传递 % 通配符作为主板的默认参数，因此如果您设置为默认 % 并使用类似的过滤器，在缺少主板参数的情况下您将获得所有主板...