public void AddEmployee(string id, string name, GenderEnum gender, string idNum, DateTime entryDate, decimal salary, string serialNum, string dutyId, EmployeeStatusEnum status, string principalId, string operatorId, DateTime createTime, string remark)
{
string sql = "INSERT into \"T_Employee\" (\"Id\",\"Name\",\"Gender\",\"IdNum\",\"EntryDate\",\"Salary\",\"SerialNum\",\"DutyId\",\"Status\",\"PrincipalId\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id,:name,:gender,:idNum,:entryDate,:salary,:serialNum,:dutyId,:status,:principalId,:operatorId,:createTime,:remark)";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("name", name);
pms.AddWithValue("gender", (int)gender);
pms.AddWithValue("idNum", idNum);
pms.AddWithValue("entryDate", entryDate);
pms.AddWithValue("salary", salary);
pms.AddWithValue("serialNum", serialNum);
pms.AddWithValue("dutyId", dutyId);
pms.AddWithValue("status", (int)status);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("remark", remark);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public void AddAssessStudsheep(string id, string studSheepId, float matingAbility, 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_AssessStudsheep\" (\"AssessId\",\"MatingAbility\")VALUES(:assessId,:matingAbility)";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("sheepId", studSheepId);
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("matingAbility", matingAbility);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public Shipper Create(string name, string phone)
{
string sql = "INSERT INTO Shippers (CompanyName, Phone) VALUES (@CompanyName, @Phone) SET @ShipperID = SCOPE_IDENTITY()";
IDbParameters dbParameters = AdoTemplate.CreateDbParameters();
dbParameters.Add("CompanyName", SqlDbType.NVarChar, 40).Value = name;
//Can we add automatic nullable support?
if (phone.Length == 0)
{
dbParameters.Add("Phone", SqlDbType.NVarChar, 24).Value = DBNull.Value;
}
else
{
dbParameters.Add("Phone", SqlDbType.NVarChar, 24).Value = phone;
}
dbParameters.AddOut("ShipperID", SqlDbType.Int);
int id = AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, dbParameters);
return(new Shipper(id, name, phone)); //10
}
public void ExecuteAntiepidemicPlan(string id, string planId, string name, string vaccine, DateTime executeDate, string effect, string sheepFlock, string principalId, string operatorId, DateTime createTime, string remark)
{
string sql = "UPDATE \"T_AntiepidemicPlan\" SET \"IsExcuted\"=:isExcuted,\"ExcuteAntiepidemicId\"=:excuteAntiepidemicId where \"Id\"=:planId;INSERT into \"T_Antiepidemic\" (\"Id\",\"Name\",\"Vaccine\",\"ExecuteDate\",\"Effect\",\"SheepFlock\",\"PrincipalId\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id,:name,:vaccine,:executeDate,:effect,:sheepFlock,:principalId,:operatorId,:createTime,:remark)";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("isExcuted", true);
pms.AddWithValue("excuteAntiepidemicId", id);
pms.AddWithValue("planId", planId);
pms.AddWithValue("id", id);
pms.AddWithValue("name", name);
pms.AddWithValue("vaccine", vaccine);
pms.AddWithValue("executeDate", executeDate);
pms.AddWithValue("effect", effect);
pms.AddWithValue("sheepFlock", sheepFlock);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("remark", remark);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public void UpdateCritical(List <UpdateCritical> listUpdate)
{
StringBuilder sb = new StringBuilder();
IDbParameters pms = AdoTemplate.CreateDbParameters();
for (int i = 0; i < listUpdate.Count; i++)
{
sb.AppendFormat("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},:logOperatorId{0},:logCreateTime{0});", i);
pms.AddWithValue("value" + i, listUpdate[i].Value);
pms.AddWithValue("isRemaindful" + i, listUpdate[i].IsRemaindful);
pms.AddWithValue("remark" + i, listUpdate[i].Remark);
pms.AddWithValue("id" + i, listUpdate[i].Id);
pms.AddWithValue("logId" + i, Guid.NewGuid());
pms.AddWithValue("settingsId" + i, listUpdate[i].Id);
pms.AddWithValue("category" + i, (int)listUpdate[i].Type);
pms.AddWithValue("newValue" + i, listUpdate[i].Value);
pms.AddWithValue("logOperatorId" + i, listUpdate[i].OperatorId);
pms.AddWithValue("logCreateTime" + i, DateTime.Now);
}
AdoTemplate.ExecuteNonQuery(CommandType.Text, sb.ToString(), pms);
}
/// <summary>
/// 获取指定条件的数据
/// </summary>
/// <param name="tablename"></param>
/// <param name="hs"></param>
/// <returns></returns>
public List <T> GetList <T>(Hashtable hs)
{
List <T> list = new List <T>();
try
{
Type type = typeof(T);
List <PropertyInfo> propertys = type.GetProperties().ToList();
var attribute = type.GetCustomAttributes(typeof(TableMapAttribute), false).FirstOrDefault();
if (attribute == null)
{
throw new Exception("类" + type.Name + "必须添加'TableMapAttribute'属性");
}
string tableName = ((TableMapAttribute)attribute).TableName;
string sql = "select * from " + tableName + " where 1=1";
//对于有IsDelete属性的，过滤已删除的数据
if (propertys.Exists(p => p.Name.ToLower() == "isdelete"))
{
sql += " and IsDelete=0 ";
}
IDbParameters param = AdoTemplate.CreateDbParameters();
foreach (string key in hs.Keys)
{
sql += string.Format(" and {0}=?{0}", key);
param.AddWithValue(key, hs[key]);
}
DataTable dt = AdoTemplate.DataTableCreateWithParams(CommandType.Text, sql, param);
dt.TableName = tableName;
list = ObjectHelper.CopyToObjects <T>(dt).ToList();
return(list ?? new List <T>());
}
catch (Exception ex)
{
return(list);
}
}
public void Handle(CreateUserEvent evt)
{
string sql = "INSERT INTO T_user ( address, birthday, createrid, createtime, degree, employeenum, entrytime, gender, id, idnum, nationid, PASSWORD, phonenum, realname, STATUS, username ) VALUES ( @address ,@birthday ,@createrid ,@createtime ,@degree ,@employeenum ,@entrytime ,@gender ,@id ,@idnum ,@nationid ,@PASSWORD ,@phonenum ,@realname ,@STATUS ,@username )";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("address", evt.Address);
pms.AddWithValue("birthday", evt.Birthday);
pms.AddWithValue("createrid", evt.CreaterId);
pms.AddWithValue("createtime", evt.CreateTime);
pms.AddWithValue("degree", evt.Degree);
pms.AddWithValue("employeenum", evt.EmployeeNum);
pms.AddWithValue("entrytime", evt.EntryTime);
pms.AddWithValue("gender", evt.Gender);
pms.AddWithValue("id", evt.Id);
pms.AddWithValue("idnum", evt.IdNum);
pms.AddWithValue("nationid", evt.NationId);
pms.AddWithValue("PASSWORD", evt.Password);
pms.AddWithValue("phonenum", evt.PhoneNum);
pms.AddWithValue("realname", evt.RealName);
pms.AddWithValue("STATUS", evt.Status);
pms.AddWithValue("username", evt.UserName);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
protected virtual IDbParameters CreateDbParameters()
{
return(AdoTemplate.CreateDbParameters());
}
public void AddFormulaNutrient(string id, 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, bool isEditable, string principalId, string operatorId, DateTime createTime, string remark)
{
string sql = "INSERT into \"T_FormulaNutrient\" (\"Id\",\"Name\",\"DailyGain\",\"CP\",\"DMI\",\"EE\",\"CF\",\"NFE\",\"Ash\",\"NDF\",\"ADF\",\"Starch\",\"Ga\",\"AllP\",\"Arg\",\"His\",\"Ile\",\"Leu\",\"Lys\",\"Met\",\"Cys\",\"Phe\",\"Tyr\",\"Thr\",\"Trp\",\"Val\",\"P\",\"Na\",\"Cl\",\"Mg\",\"K\",\"Fe\",\"Cu\",\"Mn\",\"Zn\",\"Se\",\"Carotene\",\"VE\",\"VB1\",\"VB2\",\"PantothenicAcid\",\"Niacin\",\"Biotin\",\"Folic\",\"Choline\",\"VB6\",\"VB12\",\"LinoleicAcid\",\"Salt\",\"IsEditable\",\"PrincipalId\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id,:name,:dailyGain,:cP,:dMI,:eE,:cF,:nFE,:ash,:nDF,:aDF,:starch,:ga,:allP,:arg,:his,:ile,:leu,:lys,:met,:cys,:phe,:tyr,:thr,:trp,:val,:p,:na,:cl,:mg,:k,:fe,:cu,:mn,:zn,:se,:carotene,:vE,:vB1,:vB2,:pantothenicAcid,:niacin,:biotin,:folic,:choline,:vB6,:vB12,:linoleicAcid,:salt,:isEditable,:principalId,:operatorId,:createTime,:remark)";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
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("operatorId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("remark", remark);
pms.AddWithValue("isEditable", isEditable);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}