这是我创建的完整可用的DynamicParameter:
public class OrganisationDynamicParameter : SqlMapper.IDynamicParameters
{
private readonly IOrganisation _orgModel;
public OrganisationDynamicParameter(IOrganisation orgModel)
{
_orgModel = orgModel;
}
public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
{
SqlParameter p;
var sqlCommand = (SqlCommand)command;
sqlCommand.CommandType = CommandType.StoredProcedure;
p = sqlCommand.Parameters.Add("@OrganisationXId", SqlDbType.UniqueIdentifier);
p.Value = _orgModel.OrganisationXId;
p = sqlCommand.Parameters.Add("@Enabled", SqlDbType.Bit);
p.Value = _orgModel.Enabled;
p = sqlCommand.Parameters.Add("@Timezone", SqlDbType.NVarChar, 50);
p.Value = _orgModel.TimeZone;
p = sqlCommand.Parameters.Add("@MinimumValue", SqlDbType.Float);
p.Value = _orgModel.MinimumValue;
List<SqlDataRecord> ruleList = _orgModel.Rules.Select(MapRuleData).ToList();
if (ruleList.Count > 0)
{
p = sqlCommand.Parameters.Add("@Rules", SqlDbType.Structured);
p.Direction = ParameterDirection.Input;
p.TypeName = "ReminderRuleType";
p.Value = ruleList;
}
}
protected SqlDataRecord MapRuleData(IReminderRule value)
{
var rec = new SqlDataRecord(new[]
{
new SqlMetaData("RuleId", SqlDbType.BigInt),
new SqlMetaData("OrganisationId", SqlDbType.BigInt),
new SqlMetaData("Name", SqlDbType.NVarChar, 200),
new SqlMetaData("OffsetDays", SqlDbType.Int),
new SqlMetaData("SubjectTemplate", SqlDbType.NVarChar, -1),
new SqlMetaData("BodyTemplate", SqlDbType.NVarChar, -1)
});
rec.SetInt64(0, value.RuleId);
rec.SetInt64(1, value.OrganisationId);
rec.SetString(2, value.Name);
rec.SetInt32(3, value.OffsetDays);
rec.SetString(4, value.SubjectTemplate);
rec.SetString(5, value.BodyTemplate);
return rec;
}
}
我这样使用:
public IOrganisation CreateOrganisation(IOrganisation organisation)
{
var dtoOrg = new OrganisationDynamicParameter(organisation);
return ExecuteSPReturningOrganisation("Organisation_Insert", dtoOrg);
}
protected IOrganisation ExecuteSPReturningOrganisation(string query, object parameters)
{
using (IDbConnection con = ConnectionFactory.CreateOpenConnection())
{
using (
SqlMapper.GridReader multi = con.QueryMultiple(query, parameters,
commandType: CommandType.StoredProcedure))
{
OrganisationModel org = multi.Read<OrganisationModel>().SingleOrDefault();
if (org != null)
{
org.Rules = multi.Read<ReminderRuleModel>().ToArray();
}
return org;
}
}
}
干杯
commandType: CommandType.StoredProcedure
? - Marc Gravell