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;
}`
标签:string,edit,sql,表单,mysql,new,model,查询,inputType
From: https://www.cnblogs.com/qiuyublog/p/18058853