/// <summary> /// 查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="model"></param> /// <returns></returns> public List <E_Task> GetList(E_Task model, ref int total) { if (model.PageIndex <= 0) { model.PageIndex = 1; } List <E_Task> list; StringBuilder strSql = new StringBuilder(); StringBuilder whereSql = new StringBuilder(" where 1=1 "); strSql.Append(" select ROW_NUMBER() OVER ( ORDER BY id desc) AS RID, task.*,i.PersonnelName as publishname from task left join dbo.tb_InPersonnel i on task.publishid=i.PersonnelID "); if (!String.IsNullOrEmpty(model.taskname)) { whereSql.Append(" and taskname like '%'+@taskname +'%'"); } strSql.Append(whereSql); string CountSql = "SELECT COUNT(1) as RowsCount FROM (" + strSql.ToString() + ") AS CountList"; string pageSqlStr = "select * from ( " + strSql.ToString() + " ) as Temp_PageData where Temp_PageData.RID BETWEEN {0} AND {1}"; pageSqlStr = string.Format(pageSqlStr, (model.PageSize * (model.PageIndex - 1) + 1).ToString(), (model.PageSize * model.PageIndex).ToString()); using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString())) { list = conn.Query <E_Task>(pageSqlStr, model)?.ToList(); total = conn.ExecuteScalar <int>(CountSql, model); } return(list); }
/// <summary> /// 获取首页任务列表 /// </summary> public List <E_Task> GetIndexTaskList() { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 7 * from task order by publishtime desc"); using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString())) { return(conn.Query <E_Task>(strSql.ToString())?.ToList()); } }
/// <summary> /// 查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="model"></param> /// <returns></returns> public E_Task GetInfoById(E_Task model) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from task where id=@id"); using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString())) { model = conn.Query <E_Task>(strSql.ToString(), model)?.FirstOrDefault(); } return(model); }
public List <tb_Base> GetBaseList() { List <tb_Base> list = null; StringBuilder strSql = new StringBuilder(); strSql.Append("select * from tb_Base "); using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString())) { list = conn.Query <tb_Base>(strSql.ToString())?.ToList(); } return(list); }
/// <summary> /// 查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="model"></param> /// <returns></returns> public List <E_DrugCheck> GetList(E_DrugCheck model, ref int total) { if (model.PageIndex <= 0) { model.PageIndex = 1; } List <E_DrugCheck> list; StringBuilder strSql = new StringBuilder(); StringBuilder whereSql = new StringBuilder(" where 1=1 "); strSql.Append(@"select ROW_NUMBER() OVER ( ORDER BY a.id desc) AS RID, c.*,a.putArea,a.validDate yxq,a.amount rukucount,(select SUM(amount) from dbo.tb_DrugOUT WHERE temp2=a.id) chukucount from dbo.tb_DrugIN a left join dbo.tb_DrugOUT b on b.temp2=a.id inner join dbo.tb_Drug c on a.drugId=c.id "); if (!string.IsNullOrEmpty(model.drugName)) { whereSql.Append(" and drugName like '%'+@drugName+'%'"); } if (!string.IsNullOrEmpty(model.putArea)) { whereSql.Append(" and a.putArea like '%'+@putArea+'%'"); } if (Utils.GetInt(model.riskLevel) > 0) { whereSql.Append(" and c.riskLevel=@riskLevel"); } if (model.drugType > 0) { whereSql.Append(" and c.drugType=@drugType"); } strSql.Append(whereSql); string CountSql = "SELECT COUNT(1) as RowsCount FROM (" + strSql.ToString() + ") AS CountList"; string pageSqlStr = "select * from ( " + strSql.ToString() + " ) as Temp_PageData where Temp_PageData.RID BETWEEN {0} AND {1}"; pageSqlStr = string.Format(pageSqlStr, (model.PageSize * (model.PageIndex - 1) + 1).ToString(), (model.PageSize * model.PageIndex).ToString()); using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString())) { list = conn.Query <E_DrugCheck>(pageSqlStr, model)?.ToList(); total = conn.ExecuteScalar <int>(CountSql, model); } return(list); }
/// <summary> /// 添加 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool Add(E_DrugLock model) { string sql = "INSERT INTO tb_DrugLock(temp2,pic,ischem,isdanger,locktypeid,lockName,mark,createUser,createDate,updateUser,updateDate,lockType,temp1) VALUES (@temp2,@pic,@ischem,@isdanger,@locktypeid,@lockName,@mark,@createUser,@createDate,@updateUser,@updateDate,@lockType,@temp1)"; using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString())) { int count = conn.Execute(sql, model); if (count > 0)//如果更新失败 { return(true); } else { return(false); } } }
/// <summary> /// 添加 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool Add(E_Task model) { string sql = "INSERT INTO task(taskname, director, publishid, remark, status, tasktime, publishtime, finishtime) VALUES (@taskname, @director, @publishid, @remark, @status, @tasktime, @publishtime, @finishtime)"; using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString())) { int count = conn.Execute(sql, model); if (count > 0)//如果更新失败 { return(true); } else { return(false); } } }
/// <summary> /// 删除 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool DeleteById(E_Task model) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete task where id=@id "); using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString())) { int count = conn.Execute(strSql.ToString(), model); if (count > 0)//如果更新失败 { return(true); } else { return(false); } } }
/// <summary> /// 更新 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool Update(E_Task model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update task set taskname=@taskname, director=@director, remark=@remark, status=@status, tasktime=@tasktime, finishtime=@finishtime where id=@id "); using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString())) { int count = conn.Execute(strSql.ToString(), model); if (count > 0)//如果更新失败 { return(true); } else { return(false); } } }
/// <summary> /// 更新 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool Update(E_DrugLock model) { StringBuilder strSql = new StringBuilder(); // strSql.Append("update dp_food set areaid=@areaid, classinfoid=@classinfoid,foodname=@foodname,pid=@pid,pname=@pname,pic=@pic,updatetime=getdate() where foodid=@foodid "); strSql.Append("update tb_DrugLock set " + Utils.SetUpdateSql(model, new string[] { "id" }) + " where id=@id "); using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString())) { int count = conn.Execute(strSql.ToString(), model); if (count > 0)//如果更新失败 { return(true); } else { return(false); } } }
/// <summary> /// 执行Sql和Oracle滴混合事务 /// </summary> /// <param name="list">SQL命令行列表</param> /// <param name="oracleCmdSqlList">Oracle命令行列表</param> /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns> public static int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { foreach (CommandInfo myDE in list) { string cmdText = myDE.CommandText; SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; PrepareCommand(cmd, conn, tx, cmdText, cmdParms); if (myDE.EffentNextType == EffentNextType.SolicitationEvent) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式"); //return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (isHave) { //引发事件 myDE.OnSolicitationEvent(); } } if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式"); //return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0"); //return 0; } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0"); //return 0; } continue; } int val = cmd.ExecuteNonQuery(); if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行"); //return 0; } cmd.Parameters.Clear(); } string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC"); bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList); if (!res) { tx.Rollback(); throw new Exception("Oracle执行失败"); // return -1; } tx.Commit(); return(1); } catch (System.Data.SqlClient.SqlException e) { tx.Rollback(); throw e; } catch (Exception e) { tx.Rollback(); throw e; } } }