记录一个使用mysql查询表单,多个参数.

public DataTable QueryData(SearchViewModel model)
{
DataTable dt = new DataTable();
/把'删除掉,在编写文档时,不加个',MySqlParameter显示不出来/
List<'MySqlParameter> param = new List<'MySqlParameter>();

    model.inputMonths = model.inputMonths == "All" ? "":model.inputMonths;
    model.inputFactory = model.inputFactory == "All" ? "":model.inputFactory;
    model.inputType = model.inputType == "All" ? "" :model.inputType;
    model.inputState = model.inputState == "All" ? "" :model.inputState;
    model.inputWhether = model.inputWhether == "All" ? "" :model.inputWhether;


    string sql = @$"select fromno,date_ntroduced,item_type,proposer,lcm_factory,months,model_name,prod_id,lcd_pnl_id,sn,location,vlrr_date,product_type,defect_code,level2_code,symptom,
    diameter,length,width,mura_level,failure_stage,erma_no,remark,lcm_grade ,status,
    edit_oqc_code,edit_errc_descr,edit_trans_date,edit_oqc_id,edit_oqn_name,edit_radio_type, edit_analysis,edit_reason,edit_countermeasures,
    reviewer,reviewer_date,reviewer_commt
    from should_be_detected where 1=1 ";

    if(!string.IsNullOrWhiteSpace(model.stareDate) && !string.IsNullOrWhiteSpace(model.endDate))
    {
        string startDate = DateTime.Parse( model.stareDate).ToString("yyyy-MM-dd");
        string endDate = DateTime.Parse( model.endDate).ToString("yyyy-MM-dd");
        sql += $"and date_ntroduced >=@START_DATE and date_ntroduced <=@END_DATE ";
        param.Add(new MySqlParameter("@START_DATE", startDate));
        param.Add(new MySqlParameter("@END_DATE", endDate));
    }

    if(!string.IsNullOrWhiteSpace(model.inputMonths))
    {
        sql += "and months=@MONTHS ";
        param.Add(new MySqlParameter("@MONTHS", model.inputMonths));
    }
    if(!string.IsNullOrWhiteSpace(model.inputFactory))
    {
        sql += "and lcm_factory=@FAB ";
        param.Add(new MySqlParameter("@FAB", model.inputFactory));
    }

    Dictionary<string, string> dicType =  new Dictionary<string, string>{ 
        { "V", "VLRR" },
        { "S", "OBA/SORTING" },
        { "C", "客訴" },
        { "M", "廠內委託品" }
    };

    if (dicType.ContainsKey(model.inputType))
    {
        model.inputType = dicType[model.inputType];
    }
    
    if(!string.IsNullOrWhiteSpace(model.inputType))
    {
        sql += "and item_type=@ITEM_TYPE ";
        param.Add(new MySqlParameter("@ITEM_TYPE", model.inputType));
    }

    if(!string.IsNullOrWhiteSpace(model.inputState))
    {
        sql += "and status=@STATE ";
        param.Add(new MySqlParameter("@STATE", model.inputState));
    }

    if(!string.IsNullOrWhiteSpace(model.inputWhether))
    {
        sql += "and edit_radio_type=@WHETHER ";
        param.Add(new MySqlParameter("@WHETHER", model.inputWhether));
    }


    if(!string.IsNullOrWhiteSpace(model.inputPnlID))
    {
        sql += "and lcd_pnl_id=@PNL_ID ";
        param.Add(new MySqlParameter("@PNL_ID", model.inputPnlID));
    }

    MySqlConnection mycon=new MySqlConnection(str);
    try
    {
        mycon.Open();
        MySqlCommand cmd=new MySqlCommand(sql,mycon);
        cmd.Parameters.AddRange(param.ToArray());
        MySqlDataReader myDr = cmd.ExecuteReader();
        dt.Load(myDr);
        mycon.Close();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }
    finally
    {
        mycon.Close();
    }
    return dt;
}`