dapper 参数化查询

 C#

 

不同数据库，sql中参数前的占位符是不同的，在oracle中是 ：　　　　　　而在sql server中是@

 

以下为oracle中sql示例

            string sql = @"select * from EE t where (IS_EXIST = 'Y')
                                                        and pdate_number = :selectDate
                                                        and S_NUMBER = :selectShift
                                                        and e_id = :selectEquipment ";

 

下述oracle完整示例

public IActionResult Index(string selectEquipment, string selectDate, string selectShift, OeeQueryParameterModel oeeQueryParameterModel)
        {
            selectDate = selectDate.Replace("-", "");

            string sql = @"select * from EQUIPMENT_OEE_TRACE t where (IS_EXIST = 'Y')
                                                        and production_date_number = :selectDate
                                                        and SHIFT_NUMBER = :selectShift
                                                        and equipment_id = :selectEquipment ";
            using (var connection = new OracleConnection(strCon))
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("selectDate", selectDate);
                parameters.Add("selectShift", selectShift);
                parameters.Add("selectEquipment", selectEquipment);

                //以下几种种参数化写法均可行
                var lists=connection.QueryMultiple(sql, new { selectDate=selectDate, selectShift=selectShift, selectEquipment=selectEquipment }).Read<OeeTraceModel>().AsList();
                var list = connection.Query<OeeTraceModel>(sql, new { selectDate = selectDate, selectShift = selectShift, selectEquipment = selectEquipment }).AsList();
                var lis = connection.Query<OeeTraceModel>(sql, new { selectDate, selectShift, selectEquipment }).AsList();
                var result = connection.Query<OeeTraceModel>(sql, parameters);
                oeeModels.OeeTraceModels = lists;
                
                oeeQueryParameterModel.SelectEquipmentID = int.Parse(selectEquipment);

                oeeQueryParameterModel.SelectShift = int.Parse(selectShift);
                oeeModels.OeeQueryParameterModel = oeeQueryParameterModel;
                return View(oeeModels);
            }

        }