public static void Initialize(TestContext context)
{
var factory = DbProviderFactories.GetFactory(providerName);
var connection = factory.CreateConnection();
var gDbProvider = new MockDbProvider(connection, connectionString);
using (var adoTemplate = new AdoTemplate(gDbProvider))
{
try
{
adoTemplate.ExecuteNonQuery(createDummyTable);
adoTemplate.ExecuteNonQuery(
@"INSERT INTO DUMMY_TABLE (COL_ID, COL_INT, COL_LONG, COL_DOUBLE, COL_DECIMAL, COL_STRING, COL_CHAR, COL_DATE, COL_CLOB)
VALUES ('1', '2', '3', '4', '5', '6', '7', TO_DATE('2018-04-23 16:35:31', 'YYYY-MM-DD HH24:MI:SS'), '8')"
);
adoTemplate.ExecuteNonQuery(
@"INSERT INTO DUMMY_TABLE (COL_ID, COL_INT, COL_LONG, COL_DOUBLE, COL_DECIMAL, COL_STRING, COL_CHAR, COL_DATE, COL_CLOB)
VALUES ('2', '2', '3', '4', '5', '6', '7', TO_DATE('2018-04-23 16:35:31', 'YYYY-MM-DD HH24:MI:SS'), '8')"
);
adoTemplate.ExecuteNonQuery(
@"INSERT INTO DUMMY_TABLE (COL_ID, COL_INT, COL_LONG, COL_DOUBLE, COL_DECIMAL, COL_STRING, COL_CHAR, COL_DATE, COL_CLOB)
VALUES ('3', '2', '3', '4', '5', '6', '7', TO_DATE('2018-04-23 16:35:31', 'YYYY-MM-DD HH24:MI:SS'), '8')"
);
}
catch (Exception ex)
{ }
}
}
private static void executeUpdate(AdoTemplate jdbcTemplate, String sql, Object[][] args, string tableName)
{
jdbcTemplate.ExecuteNonQuery(CommandType.Text, String.Format("SET IDENTITY_INSERT {0} ON", tableName));
foreach (Object[] arg in args)
{
jdbcTemplate.ExecuteNonQuery(CommandType.Text, String.Format(sql, arg.Select(a => a ?? "NULL").ToArray()).Replace("'NULL'", "NULL"));
}
jdbcTemplate.ExecuteNonQuery(CommandType.Text, String.Format("SET IDENTITY_INSERT {0} OFF", tableName));
}
public int DeleteActivities(ActivitySearchParams searchParams, bool addFlowIsNullQuery)
{
List <object> whereValues;
string tableNames;
string whereColumns = ConstructSearchWhereValues(searchParams, addFlowIsNullQuery, out whereValues, out tableNames);
string selectIdText = CreateSelectSqlParamText(tableNames, whereColumns,
(whereValues == null) ? 0 : whereValues.Count,
null, "DISTINCT a.Id");
IDbParameters parameters = AppendDbParameters(null, whereValues);
string deleteSqlCommand = string.Format("DELETE FROM {0} WHERE Id IN ({1})",
TABLE_NAME, selectIdText);
string updateSqlCommand = string.Format("UPDATE {0} SET LastExecuteActivityId = NULL WHERE LastExecuteActivityId IN ({1})",
ScheduleDao.TABLE_NAME, selectIdText);
int deleteCount = 0;
TransactionTemplate.Execute(delegate
{
AdoTemplate.ExecuteNonQuery(CommandType.Text, updateSqlCommand, parameters);
deleteCount = AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteSqlCommand, parameters);
return(null);
});
return(deleteCount);
}
public void AddDelivery(string id, string femaleId, DeliveryWayEnum deliveryWay, MidwiferyReasonEnum deliverReason, string deliverReasonOtherDetail, int?liveMaleCount, int?liveFemaleCount, int totalCount, DateTime deliveryDate, string principalId, string operatorId, DateTime createTime, string remark)
{
string sql = "INSERT into \"T_Delivery\" (\"Id\",\"SheepId\",\"DeliveryWay\",\"DeliverReason\",\"DeliverReasonOtherDetail\",\"LiveMaleCount\",\"LiveFemaleCount\",\"TotalCount\",\"DeliveryDate\",\"PrincipalId\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id,:sheepId,:deliveryWay,:deliverReason,:deliverReasonOtherDetail,:liveMaleCount,:liveFemaleCount,:totalCount,:deliveryDate,:principalId,:operatorId,:createTime,:remark);update \"T_Mating\" set \"IsRemindful\"=:isRemindful where \"FemaleId\"=:mid;";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("sheepId", femaleId);
pms.AddWithValue("deliveryWay", (int)deliveryWay);
pms.AddWithValue("deliverReason", (int)deliverReason);
pms.AddWithValue("deliverReasonOtherDetail", deliverReasonOtherDetail);
pms.AddWithValue("liveMaleCount", liveMaleCount);
pms.AddWithValue("liveFemaleCount", liveFemaleCount);
pms.AddWithValue("totalCount", totalCount);
pms.AddWithValue("deliveryDate", deliveryDate);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("remark", remark);
pms.AddWithValue("isRemindful", false);
pms.AddWithValue("mid", femaleId);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public void AddMoveSheepfold(List <Sheep> sheeps, string targetSheepfold, string principalId, string operatorId, DateTime createTime, string remark)
{
IDbParameters pms = AdoTemplate.CreateDbParameters();
StringBuilder sbSql = new StringBuilder();
for (int i = 0; i < sheeps.Count(); i++)
{
if (sheeps[i].SheepfoldId == targetSheepfold)
{
continue;
}
sbSql.AppendFormat("UPDATE \"T_Sheep\" set \"SheepfoldId\"=:targetSheepfold{0} WHERE \"Id\"=:sId{0};", i);
sbSql.AppendFormat("INSERT into \"T_MoveSheepfold\" (\"Id\",\"SheepId\",\"SourceSheepfoldId\",\"DestinationSheepfoldId\",\"OperationDate\",\"PrincipalId\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id{0},:sheepId{0},:sourceSheepfoldId{0},:destinationSheepfoldId{0},:operationDate{0},:principalId{0},:operatorId{0},:createTime{0},:remark{0});", i);
pms.AddWithValue("targetSheepfold" + i, targetSheepfold);
pms.AddWithValue("sId" + i, sheeps[i].Id);
pms.AddWithValue("id" + i, Guid.NewGuid());
pms.AddWithValue("sheepId" + i, sheeps[i].Id);
pms.AddWithValue("sourceSheepfoldId" + i, sheeps[i].SheepfoldId);
pms.AddWithValue("destinationSheepfoldId" + i, targetSheepfold);
pms.AddWithValue("operationDate" + i, createTime);
pms.AddWithValue("principalId" + i, principalId);
pms.AddWithValue("operatorId" + i, operatorId);
pms.AddWithValue("createTime" + i, createTime);
pms.AddWithValue("remark" + i, remark);
}
AdoTemplate.ExecuteNonQuery(CommandType.Text, sbSql.ToString(), pms);
}
public void UpdateSheepParameters(List <UpdateSheepParameter> list, int deliveryRangeDays)
{
StringBuilder sb = new StringBuilder();
IDbParameters pms = AdoTemplate.CreateDbParameters();
for (int i = 0; i < list.Count; i++)
{
sb.AppendFormat("UPDATE \"T_SheepParameter\" SET \"Range\"=:range{0} where \"SettingsId\"=:sheepParameterSettingsId{0};UPDATE \"T_Settings\" SET \"Value\"=:value{0},\"IsRemaindful\"=:isRemaindful{0},\"Remark\"=:remark{0} where \"Id\"=:id{0};INSERT into \"T_SettingsLog\" (\"Id\",\"SettingsId\",\"Category\",\"NewValue\",\"OperatorId\",\"CreateTime\")VALUES(:logId{0},:settingsId{0},:category{0},:newValue{0},:operatorId{0},:createTime{0});", i);
pms.AddWithValue("range" + i, deliveryRangeDays);
pms.AddWithValue("sheepParameterSettingsId" + i, list[i].Id);
pms.AddWithValue("value" + i, list[i].Value);
pms.AddWithValue("remark" + i, list[i].Remark);
pms.AddWithValue("isRemaindful" + i, list[i].IsRemaindful);
pms.AddWithValue("id" + i, list[i].Id);
pms.AddWithValue("logId" + i, Guid.NewGuid());
pms.AddWithValue("settingsId" + i, list[i].Id);
pms.AddWithValue("category" + i, (int)list[i].Type);
pms.AddWithValue("newValue" + i, list[i].Value);
pms.AddWithValue("operatorId" + i, list[i].OperatorId);
pms.AddWithValue("createTime" + i, DateTime.Now);
}
AdoTemplate.ExecuteNonQuery(CommandType.Text, sb.ToString(), pms);
}
public void AddSecondAssess(string id, string sheepId, float breedFeatureScore, float genitaliaScore, float weight, float habitusScore, DateTime assessDate, string principalId, string operatorId, DateTime createTime, string remark)
{
string sql = "INSERT into \"T_Assess\" (\"Id\",\"SheepId\",\"Weight\",\"HabitusScore\",\"AssessDate\",\"PrincipalId\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id,:sheepId,:weight,:habitusScore,:assessDate,:principalId,:operatorId,:createTime,:remark);INSERT into \"T_SecondAssess\" (\"AssessId\",\"BreedFeatureScore\",\"GenitaliaScore\")VALUES(:assessId,:breedFeatureScore,:genitaliaScore); UPDATE \"T_Sheep\" SET \"GrowthStage\"=:growthStage where \"Id\"=:updateSheepId";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("sheepId", sheepId);
pms.AddWithValue("weight", weight);
pms.AddWithValue("habitusScore", habitusScore);
pms.AddWithValue("assessDate", assessDate);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("remark", remark);
pms.AddWithValue("assessId", id);
pms.AddWithValue("breedFeatureScore", breedFeatureScore);
pms.AddWithValue("genitaliaScore", genitaliaScore);
pms.AddWithValue("growthStage", (int)GrowthStageEnum.TemporaryStudSheep);
pms.AddWithValue("updateSheepId", sheepId);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public void SqlServerTest()
{
string errorCode = "544";
string[] errorCodes = new string[4] {"544", "2627", "8114", "8115"};
//Array.IndexOf()
//Array.Sort(errorCodes);
foreach (string code in errorCodes)
{
Console.WriteLine(code);
}
//if (Array.BinarySearch(errorCodes, errorCode) >= 0)
if (Array.IndexOf(errorCodes, errorCode) >= 0)
{
Console.WriteLine("yes");
}
else
{
Assert.Fail("did not find error code");
}
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
dbProvider.ConnectionString =
@"Data Source=MARKT60\SQL2005;Initial Catalog=Spring;Persist Security Info=True;User ID=springqa;Password=springqa";
AdoTemplate adoTemplate = new AdoTemplate(dbProvider);
try
{
adoTemplate.ExecuteNonQuery(CommandType.Text, "insert into Vacation (id) values (1)");
} catch (Exception e)
{
Console.Write(e);
throw;
}
}
public void UpdateSheep(string breedId, GenderEnum gender, GrowthStageEnum growthStage, OriginEnum origin, float?birthWeight, int compatriotNumber, DateTime?birthDay, float?ablactationWeight, DateTime?ablactationDate, string fatherId, string motherId, string sheepfoldId, SheepStatusEnum sheepStatusEnum, string principalId, string remark, string id)
{
string sql = "update \"T_Sheep\" set \"BreedId\"=:breedId,\"Gender\"=:gender,\"GrowthStage\"=:growthStage,\"SheepfoldId\"=:sheepfoldId,\"BirthWeight\"=:birthWeight,\"CompatriotNumber\"=:compatriotNumber,\"Birthday\"=:birthday,\"Origin\"=:origin, \"AblactationWeight\"=:ablactationWeight,\"AblactationDate\"=:ablactationDate,\"FatherId\"=:fatherId,\"MotherId\"=:motherId,\"Status\"=:status,\"PrincipalId\"=:principalId,\"Remark\"=:remark where \"Id\"=:id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("breedId", breedId);
pms.AddWithValue("gender", (int)gender);
pms.AddWithValue("growthStage", (int)growthStage);
pms.AddWithValue("sheepfoldId", sheepfoldId);
pms.AddWithValue("birthWeight", birthWeight);
pms.AddWithValue("compatriotNumber", compatriotNumber);
pms.AddWithValue("birthday", birthDay);
pms.AddWithValue("origin", (int)origin);
pms.AddWithValue("ablactationWeight", ablactationWeight);
pms.AddWithValue("ablactationDate", ablactationDate);
pms.AddWithValue("fatherId", fatherId);
pms.AddWithValue("motherId", motherId);
pms.AddWithValue("status", (int)sheepStatusEnum);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("remark", remark);
pms.AddWithValue("id", id);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public void AddCritical(List <UpdateCritical> listAdd)
{
StringBuilder sb = new StringBuilder();
IDbParameters pms = AdoTemplate.CreateDbParameters();
for (int i = 0; i < listAdd.Count; i++)
{
sb.AppendFormat("INSERT into \"T_Critical\" (\"SettingsId\",\"KindId\")VALUES(:criticalId{0},:kindId{0});INSERT into \"T_Settings\" (\"Id\",\"Value\",\"IsRemaindful\",\"Type\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id{0},:value{0},:isRemaindful{0},:type{0},:operatorId{0},:createTime{0},:remark{0});INSERT into \"T_SettingsLog\" (\"Id\",\"SettingsId\",\"Category\",\"NewValue\",\"OperatorId\",\"CreateTime\")VALUES(:logId{0},:settingsId{0},:category{0},:newValue{0},:logOperatorId{0},:logCreateTime{0});", i);
string settingsId = Guid.NewGuid().ToString();
pms.AddWithValue("criticalId" + i, settingsId);
pms.AddWithValue("kindId" + i, listAdd[i].KindId);
pms.AddWithValue("id" + i, settingsId);
pms.AddWithValue("value" + i, listAdd[i].Value);
pms.AddWithValue("isRemaindful" + i, listAdd[i].IsRemaindful);
pms.AddWithValue("type" + i, (int)listAdd[i].Type);
pms.AddWithValue("operatorId" + i, listAdd[i].OperatorId);
pms.AddWithValue("createTime" + i, DateTime.Now);
pms.AddWithValue("remark" + i, listAdd[i].Remark);
pms.AddWithValue("logId" + i, Guid.NewGuid());
pms.AddWithValue("settingsId" + i, settingsId);
pms.AddWithValue("category" + i, (int)listAdd[i].Type);
pms.AddWithValue("newValue" + i, listAdd[i].Value);
pms.AddWithValue("logOperatorId" + i, listAdd[i].OperatorId);
pms.AddWithValue("logCreateTime" + i, DateTime.Now);
}
AdoTemplate.ExecuteNonQuery(CommandType.Text, sb.ToString(), pms);
}
public void AddSheep(string id, string serialNumber, string breedId, GenderEnum gender, GrowthStageEnum growthStage, OriginEnum origin, float?birthWeight, int compatriotNumber, DateTime?birthDay, string fatherId, string motherId, string sheepfoldId, SheepStatusEnum sheepStatusEnum, string principalId, string operatorId, DateTime createTime, string remark)
{
string sql = "INSERT into \"T_Sheep\" (\"Id\",\"SerialNumber\",\"BreedId\",\"Gender\",\"GrowthStage\",\"SheepfoldId\",\"BirthWeight\",\"CompatriotNumber\",\"Birthday\",\"Origin\",\"FatherId\",\"MotherId\",\"Status\",\"PrincipalId\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id,:serialNumber,:breedId,:gender,:growthStage,:sheepfoldId,:birthWeight,:compatriotNumber,:birthday,:origin,:fatherId,:motherId,:status,:principalId,:operaterId,:createTime,:remark)";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("serialNumber", serialNumber);
pms.AddWithValue("breedId", breedId);
pms.AddWithValue("gender", (int)gender);
pms.AddWithValue("growthStage", (int)growthStage);
pms.AddWithValue("sheepfoldId", sheepfoldId);
pms.AddWithValue("birthWeight", birthWeight);
pms.AddWithValue("compatriotNumber", compatriotNumber);
pms.AddWithValue("birthday", birthDay);
pms.AddWithValue("origin", (int)origin);
pms.AddWithValue("fatherId", fatherId);
pms.AddWithValue("motherId", motherId);
pms.AddWithValue("status", (int)sheepStatusEnum);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("operaterId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("remark", remark);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public string GetSysSheepfoldId(string administrator, string operatorId)
{
string sql = "select sf.\"Id\" from \"T_Sheepfold\" sf where sf.\"SysFlag\"=true";
object obj = AdoTemplate.ExecuteScalar(CommandType.Text, sql);
string id;
if (obj == null)
{
id = Guid.NewGuid().ToString();
sql = "INSERT into \"T_Sheepfold\" (\"Id\",\"Name\",\"Administrator\",\"OperatorId\",\"CreateTime\",\"SysFlag\",\"Remark\")VALUES(:id,:name,(select u.\"Id\" from \"T_User\" u where u.\"UserName\"='admin'),:operatorId,:createTime,:sysFlag,:remark)";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("name", "系统预留");
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", DateTime.Now);
pms.AddWithValue("sysFlag", true);
pms.AddWithValue("remark", "系统预留，虚拟圈舍，死亡或者出售");
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
else
{
id = obj.ToString();
}
return(id);
}
public void UpdateFormulaNutrient(string name, float?dailyGain, float?CP, float?DMI, float?EE, float?CF, float?NFE, float?Ash, float?NDF, float?ADF, float?Starch, float?Ga, float?AllP, float?Arg, float?His, float?Ile, float?Leu, float?Lys, float?Met, float?Cys, float?Phe, float?Tyr, float?Thr, float?Trp, float?Val, float?P, float?Na, float?Cl, float?Mg, float?K, float?Fe, float?Cu, float?Mn, float?Zn, float?Se, float?Carotene, float?VE, float?VB1, float?VB2, float?PantothenicAcid, float?Niacin, float?Biotin, float?Folic, float?Choline, float?VB6, float?VB12, float?LinoleicAcid, float?Salt, string principalId, string remark, string id)
{
string sql = "UPDATE \"T_FormulaNutrient\" SET \"Name\"=:name,\"DailyGain\"=:dailyGain,\"CP\"=:cP,\"DMI\"=:dMI,\"EE\"=:eE,\"CF\"=:cF,\"NFE\"=:nFE,\"Ash\"=:ash,\"NDF\"=:nDF,\"ADF\"=:aDF,\"Starch\"=:starch,\"Ga\"=:ga,\"AllP\"=:allP,\"Arg\"=:arg,\"His\"=:his,\"Ile\"=:ile,\"Leu\"=:leu,\"Lys\"=:lys,\"Met\"=:met,\"Cys\"=:cys,\"Phe\"=:phe,\"Tyr\"=:tyr,\"Thr\"=:thr,\"Trp\"=:trp,\"Val\"=:val,\"P\"=:p,\"Na\"=:na,\"Cl\"=:cl,\"Mg\"=:mg,\"K\"=:k,\"Fe\"=:fe,\"Cu\"=:cu,\"Mn\"=:mn,\"Zn\"=:zn,\"Se\"=:se,\"Carotene\"=:carotene,\"VE\"=:vE,\"VB1\"=:vB1,\"VB2\"=:vB2,\"PantothenicAcid\"=:pantothenicAcid,\"Niacin\"=:niacin,\"Biotin\"=:biotin,\"Folic\"=:folic,\"Choline\"=:choline,\"VB6\"=:vB6,\"VB12\"=:vB12,\"LinoleicAcid\"=:linoleicAcid,\"Salt\"=:salt,\"PrincipalId\"=:principalId,\"Remark\"=:remark where \"Id\"=:id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("name", name);
pms.AddWithValue("dailyGain", dailyGain);
pms.AddWithValue("cP", CP);
pms.AddWithValue("dMI", DMI);
pms.AddWithValue("eE", EE);
pms.AddWithValue("cF", CF);
pms.AddWithValue("nFE", NFE);
pms.AddWithValue("ash", Ash);
pms.AddWithValue("nDF", NDF);
pms.AddWithValue("aDF", ADF);
pms.AddWithValue("starch", Starch);
pms.AddWithValue("ga", Ga);
pms.AddWithValue("allP", AllP);
pms.AddWithValue("arg", Arg);
pms.AddWithValue("his", His);
pms.AddWithValue("ile", Ile);
pms.AddWithValue("leu", Leu);
pms.AddWithValue("lys", Lys);
pms.AddWithValue("met", Met);
pms.AddWithValue("cys", Cys);
pms.AddWithValue("phe", Phe);
pms.AddWithValue("tyr", Tyr);
pms.AddWithValue("thr", Thr);
pms.AddWithValue("trp", Trp);
pms.AddWithValue("val", Val);
pms.AddWithValue("p", P);
pms.AddWithValue("na", Na);
pms.AddWithValue("cl", Cl);
pms.AddWithValue("mg", Mg);
pms.AddWithValue("k", K);
pms.AddWithValue("fe", Fe);
pms.AddWithValue("cu", Cu);
pms.AddWithValue("mn", Mn);
pms.AddWithValue("zn", Zn);
pms.AddWithValue("se", Se);
pms.AddWithValue("carotene", Carotene);
pms.AddWithValue("vE", VE);
pms.AddWithValue("vB1", VB1);
pms.AddWithValue("vB2", VB2);
pms.AddWithValue("pantothenicAcid", PantothenicAcid);
pms.AddWithValue("niacin", Niacin);
pms.AddWithValue("biotin", Biotin);
pms.AddWithValue("folic", Folic);
pms.AddWithValue("choline", Choline);
pms.AddWithValue("vB6", VB6);
pms.AddWithValue("vB12", VB12);
pms.AddWithValue("linoleicAcid", LinoleicAcid);
pms.AddWithValue("salt", Salt);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("remark", remark);
pms.AddWithValue("id", id);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public void AddFormula(string id, Dictionary <string, float> formulaFeed, string name, string applyTo, string sideEffect, bool isEnable, string principalId, string operatorId, DateTime createTime, string remark)
{
IDbParameters pms = AdoTemplate.CreateDbParameters();
StringBuilder sb = new StringBuilder();
sb.Append("INSERT into \"T_Formula\" (\"Id\",\"Name\",\"ApplyTo\",\"SideEffect\",\"PrincipalId\",\"OperatorId\",\"CreateTime\",\"IsEnable\",\"Remark\")VALUES(:id,:name,:applyTo,:sideEffect,:principalId,:operatorId,:createTime,:isEnable,:remark);");
pms.AddWithValue("id", id);
pms.AddWithValue("name", name);
pms.AddWithValue("applyTo", applyTo);
pms.AddWithValue("sideEffect", sideEffect);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("isEnable", isEnable);
pms.AddWithValue("remark", remark);
List <string> keys = formulaFeed.Keys.ToList();
for (int i = 0; i < keys.Count; i++)
{
sb.AppendFormat("INSERT into \"T_FormulaFeed\" (\"Id\",\"FormulaId\",\"FeedId\",\"Amount\")VALUES(:id{0},:formulaId{0},:feedId{0},:amount{0});", i);
pms.AddWithValue("id" + i, Guid.NewGuid());
pms.AddWithValue("formulaId" + i, id);
pms.AddWithValue("feedId" + i, keys[i]);
pms.AddWithValue("amount" + i, formulaFeed[keys[i]]);
}
AdoTemplate.ExecuteNonQuery(CommandType.Text, sb.ToString(), pms);
}
public void AddDelivery(string id, string femaleId, DeliveryWayEnum deliveryWay, MidwiferyReasonEnum deliverReason, string deliverReasonOtherDetail, int?liveMaleCount, int?liveFemaleCount, int totalCount, DateTime deliveryDate, string principalId, string operatorId, DateTime createTime, string remark, List <Chanyi.Shepherd.QueryModel.AddModel.BaseInfo.Sheep> lambList, string lambBreedId, string fatherId)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT into \"T_Delivery\" (\"Id\",\"SheepId\",\"DeliveryWay\",\"DeliverReason\",\"DeliverReasonOtherDetail\",\"LiveMaleCount\",\"LiveFemaleCount\",\"TotalCount\",\"DeliveryDate\",\"PrincipalId\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id,:sheepId,:deliveryWay,:deliverReason,:deliverReasonOtherDetail,:liveMaleCount,:liveFemaleCount,:totalCount,:deliveryDate,:principalId,:operatorId,:createTime,:remark);update \"T_Mating\" set \"IsRemindful\"=:isRemindful where \"FemaleId\"=:mid;");
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("sheepId", femaleId);
pms.AddWithValue("deliveryWay", (int)deliveryWay);
pms.AddWithValue("deliverReason", (int)deliverReason);
pms.AddWithValue("deliverReasonOtherDetail", deliverReasonOtherDetail);
pms.AddWithValue("liveMaleCount", liveMaleCount);
pms.AddWithValue("liveFemaleCount", liveFemaleCount);
pms.AddWithValue("totalCount", totalCount);
pms.AddWithValue("deliveryDate", deliveryDate);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("remark", remark);
pms.AddWithValue("isRemindful", false);
pms.AddWithValue("mid", femaleId);
int mc = liveMaleCount ?? 0;
int fc = liveFemaleCount ?? 0;
for (int i = 0; i < lambList.Count; i++)
{
sb.AppendFormat("INSERT into \"T_Sheep\" (\"Id\",\"SerialNumber\",\"BreedId\",\"Gender\",\"GrowthStage\",\"SheepfoldId\",\"BirthWeight\",\"CompatriotNumber\",\"Birthday\",\"AblactationWeight\",\"AblactationDate\",\"Origin\",\"FatherId\",\"MotherId\",\"Status\",\"PrincipalId\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id{0},:serialNumber{0},:breedId{0},:gender{0},:growthStage{0},:sheepfoldId{0},:birthWeight{0},:compatriotNumber{0},:birthday{0},:ablactationWeight{0},:ablactationDate{0},:origin{0},:fatherId{0},:motherId{0},:status{0},:principalId{0},:operatorId{0},:createTime{0},:remark{0});", i);
Chanyi.Shepherd.QueryModel.AddModel.BaseInfo.Sheep sheep = lambList[i];
pms.AddWithValue("id" + i, Guid.NewGuid().ToString());
pms.AddWithValue("serialNumber" + i, sheep.SerialNumber);
pms.AddWithValue("breedId" + i, lambBreedId);
pms.AddWithValue("gender" + i, (int)sheep.Gender);
pms.AddWithValue("growthStage" + i, (int)GrowthStageEnum.Lamb);
pms.AddWithValue("sheepfoldId" + i, sheep.SheepfoldId);
pms.AddWithValue("birthWeight" + i, sheep.BirthWeight);
pms.AddWithValue("compatriotNumber" + i, mc + fc);
pms.AddWithValue("birthday" + i, deliveryDate);
pms.AddWithValue("ablactationWeight" + i, null);
pms.AddWithValue("ablactationDate" + i, null);
pms.AddWithValue("origin" + i, (int)OriginEnum.HomeBred);
pms.AddWithValue("fatherId" + i, fatherId);
pms.AddWithValue("motherId" + i, femaleId);
pms.AddWithValue("status" + i, (int)SheepStatusEnum.Nomal);
pms.AddWithValue("principalId" + i, principalId);
pms.AddWithValue("operatorId" + i, operatorId);
pms.AddWithValue("createTime" + i, createTime);
pms.AddWithValue("remark" + i, sheep.Remark);
}
AdoTemplate.ExecuteNonQuery(CommandType.Text, sb.ToString(), pms);
}
public void DeleteMating(string id)
{
string sql = "update \"T_Mating\" set \"IsDel\"=true where \"Id\"=:id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms).ToString();
}
public void AuditOperation(string operationIdenfitier)
{
logger.Debug("Executing AUDIT operation.");
AdoTemplate.ExecuteNonQuery(CommandType.Text,
"insert into AuditTable (AuditId) values (@AuditId)",
"AuditId", DbType.String, 100, operationIdenfitier);
logger.Debug("AUDIT operation done.");
}
public void Handle(DeleteUserEvent evt)
{
string sql = "delete from t_user where id=@id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", evt.Id);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public int Insert(LectureCategoryItem item)
{
string query = "Insert into LectureCategory(name) VALUES(@Name)";
IDbParameters param = CreateDbParameters();
param.AddWithValue("Name", item.Name).DbType = DbType.String;
return(AdoTemplate.ExecuteNonQuery(CommandType.Text, query, param));
}
public void Update(TestObject to)
{
AdoTemplate.ExecuteNonQuery(CommandType.Text,
String.Format("UPDATE TestObjects SET Age={0}, Name='{1}' where TestObjectNo = {2}",
to.Age,
to.Name,
to.ObjectNumber));
}
public void delete(User model)
{
IDbParametersBuilder builder = CreateDbParametersBuilder();
builder.Create().Name("id").Type(DbType.Int32).Value(model.id);
AdoTemplate.ExecuteNonQuery(CommandType.Text, @"DELETE FROM tb_user WHERE id = :id", builder.GetParameters());
}
public int Delete(int id)
{
string query = "delete from LectureDetail where id= @Id";
IDbParameters param = CreateDbParameters();
param.AddWithValue("Id", id).DbType = DbType.Int32;
return(AdoTemplate.ExecuteNonQuery(CommandType.Text, query, param));
}
public int Update(LectureCategoryItem item)
{
string query = "UPDATE LectureCategory SET name = @Name where id = @Id";
IDbParameters param = CreateDbParameters();
param.AddWithValue("Id", item.Id).DbType = DbType.Int32;
param.AddWithValue("Name", item.Name).DbType = DbType.String;
return(AdoTemplate.ExecuteNonQuery(CommandType.Text, query, param));
}
public int Insert(ScoreRequirementItem item)
{
string query = "Insert into ScoreRequirement(accountId,cutline) VALUES(@AccountId, @Cutline)";
IDbParameters param = CreateDbParameters();
param.AddWithValue("AccountId", item.AccountId).DbType = DbType.Int32;
param.AddWithValue("Cutline", item.Cutline).DbType = DbType.Int32;
return(AdoTemplate.ExecuteNonQuery(CommandType.Text, query, param));
}
// private IList<T> QueryWithRowMapper<T>(CommandType cmdType, string query, IRowMapper mapper,IDbParameters parameters)
// {
// IDataReader reader = null;
// IList<T> list = new List<T>();
// int rowNum=0;1
// while (reader.Read())
// {
// T t =
// (T)mapper.MapRow(reader, rowNum++);
// list.Add(t);
// }
// return list;
// }
public int UpdateState(int accountId, string state)
{
string query = "UPDATE account SET state = @State where id = @AccountId";
IDbParameters param = CreateDbParameters();
param.AddWithValue("AccountId", accountId).DbType = DbType.String;
param.AddWithValue("State", state).DbType = DbType.String;
return(AdoTemplate.ExecuteNonQuery(CommandType.Text, query, param));
}
public int Insert(SectionItem item)
{
string query = "Insert into Section(lectureTypeId,name) VALUES(@LectureTypeId, @Name)";
IDbParameters param = CreateDbParameters();
param.AddWithValue("LectureTypeId", item.LectureTypeId).DbType = DbType.Int32;
param.AddWithValue("Name", item.Name).DbType = DbType.String;
return(AdoTemplate.ExecuteNonQuery(CommandType.Text, query, param));
}
public void DeleteUserPermission(string userId)
{
string sql = "delete from \"T_UserPermission\" where \"UserId\"=:id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", userId);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public void DeleteRolePermission(string roleId)
{
string sql = "delete FROM \"T_PermissionRole\" where \"RoleId\"=:id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", roleId);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public void TxTemplate()
{
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
dbProvider.ConnectionString = @"Data Source=SPRINGQA;Initial Catalog=CreditsAndDebits;User ID=springqa; Password=springqa";
//IPlatformTransactionManager tm = new ServiceDomainPlatformTransactionManager();
//IPlatformTransactionManager tm = new TxScopeTransactionManager();
IPlatformTransactionManager tm = new AdoPlatformTransactionManager(dbProvider);
AdoTemplate adoTemplate = new AdoTemplate(dbProvider);
TransactionTemplate tt = new TransactionTemplate(tm);
tt.PropagationBehavior = TransactionPropagation.Required;
tt.Execute(delegate(ITransactionStatus status)
{
if (System.Transactions.Transaction.Current != null)
{
Console.WriteLine("tx 1 id = " + System.Transactions.Transaction.Current.TransactionInformation.LocalIdentifier);
}
Console.WriteLine("tx 1 'IsNewTransaction' = " + status.IsNewTransaction);
adoTemplate.ExecuteNonQuery(CommandType.Text, "insert into Credits (CreditAmount) VALUES (@amount)", "amount", DbType.Decimal, 0, 444);
TransactionTemplate tt2 = new TransactionTemplate(tm);
tt2.PropagationBehavior = TransactionPropagation.RequiresNew;
tt2.Execute(delegate(ITransactionStatus status2)
{
if (System.Transactions.Transaction.Current != null)
{
Console.WriteLine("tx 2 = " + System.Transactions.Transaction.Current.TransactionInformation.LocalIdentifier);
}
Console.WriteLine("tx 2 'IsNewTransaction' = " + status2.IsNewTransaction);
adoTemplate.ExecuteNonQuery(CommandType.Text, "insert into dbo.Debits (DebitAmount) VALUES (@amount)", "amount", DbType.Decimal, 0, 555);
//throw new ArithmeticException("can't do the math.");
status2.SetRollbackOnly();
return(null);
});
if (System.Transactions.Transaction.Current != null)
{
Console.WriteLine("tx id1 = " + System.Transactions.Transaction.Current.TransactionInformation.LocalIdentifier);
}
return(null);
});
}
public void UpdateLoginErrorTimes(string userName, int errorTimes, DateTime lastErrorTime)
{
string sql = "update \"T_User\" set \"ErrorTimes\"=:errorTimes,\"LastErrorTime\"=:lastErrorTime where \"UserName\"=:userName";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("errorTimes", errorTimes);
pms.AddWithValue("lastErrorTime", lastErrorTime);
pms.AddWithValue("userName", userName);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
/// <summary>
/// 删除记录
/// </summary>
public int Delete(string tableName, string keycol, object obj)
{
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("delete from {0} where {1}=@{1}", tableName, keycol);
IDbParameters param = AdoTemplate.CreateDbParameters();
param.AddWithValue(keycol, obj);
return(AdoTemplate.ExecuteNonQuery(CommandType.Text, sbSql.ToString(), param));
}