/// <summary> /// 获取数据集 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataSet getDataSetBySql(string sql) { bllPaging objbllPaging = new bllPaging(); DataSet ds = null; try { ds = objbllPaging.GetDataSetInfoBySQL(sql); } catch (Exception ex) { } finally { objbllPaging = null; } return(ds); }
/// <summary> /// 获取数据表 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataTable getDataTableBySql(string sql) { bllPaging objbllPaging = new bllPaging(); DataTable dt = null; try { dt = objbllPaging.GetDataTableInfoBySQL(sql); } catch (Exception ex) { } finally { objbllPaging = null; } return(dt); }
/// <summary> /// 获取当前排队信息 /// </summary> /// <example>1-3人 排队人数3 当前桌号A01</example> /// <param name="fiter">查询条件</param> /// <returns></returns> public DataTable GetDataTableInfoBySQL(string GUID, string UID, string fiter) { if (!CheckLogin(GUID, UID))//非法登录 { return(dtBase); } string sql = "select a.waittype,a.waitTime ,b.countall,a.sortNum ,c.minperosn,c.maxperson,c.Turncycle,iswait=1 FROM [busWait] as a " + " INNER join(select waittype,max(waitTime) as waitTime,count(waittype) as countall from [busWait] where status=0 " + fiter.Replace(" a.", " ");; sql += " group by waittype ) as b on a.waittype = b. waittype and a.waitTime = b.waitTime " + " inner join (select minperosn,maxperson ,Turncycle,linecode from [dbo].setlineUp where [status]='1' and isdelete='0' ) as c on a.waittype = c.linecode " + " where a.status ='0' " + fiter + " ORDER BY b.countall ASC "; DataTable returnDt = new DataTable(); DataTable dt = new bllPaging().GetDataTableInfoBySQL(sql); sql = "SELECT linecode AS waittype, waitTime=GETDATE() ,countall=0,sortNum='0', minperosn,maxperson ,Turncycle,iswait=1 from [dbo].setlineUp where [status]='1' and isdelete='0' "; DataTable dtall = new bllPaging().GetDataTableInfoBySQL(sql); if (dt.Rows.Count == 0) { returnDt = dtall; } else if (dt.Rows.Count != dtall.Rows.Count) { for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; DataRow[] drs = dtall.Select(" waittype = " + dr["waittype"]); if (drs != null && dtall.Rows.Count > 0) { dtall.Rows.Remove(drs[0]); } } dtall.Merge(dt); dtall.Select(" 1=1", " waittype asc "); returnDt = dtall; } else { returnDt = dt; } return(returnDt); }
/// <summary> /// 执行sql语句 /// </summary> /// <param name="sql">sql语句</param> /// <returns>返回影响的行数</returns> public int ExecuteSql(string sql) { bllPaging objbllPaging = new bllPaging(); StringBuilder Builder = new StringBuilder(); Builder.AppendLine(sql);//开始事务 try { int intResult = objbllPaging.ExecuteNonQueryBySQL2(Builder.ToString()); return(intResult); } catch (Exception ex) { } finally { objbllPaging = null; } return(0); }
/// <summary> /// 用事务执行SQL命令,并返回数据集 /// </summary> /// <param name="sql">sql语句(不包含事务语句)</param> /// <returns></returns> public DataSet ExecuteDataSetByTran(string sql) { bllPaging objbllPaging = new bllPaging(); StringBuilder Builder = new StringBuilder(); Builder.AppendLine(" BEGIN TRAN tan1"); //开始事务 Builder.AppendLine(sql); //开始事务 Builder.AppendLine(" if(@@error=0) begin commit tran tan1; end else begin rollback tran tran1 end"); //结束事务 DataSet ds = null; try { ds = objbllPaging.GetDataSetInfoBySQL(Builder.ToString()); } catch (Exception ex) { } finally { objbllPaging = null; } return(ds); }
/// <summary> /// 用事务执行SQL命令,并返回受影响的行数 /// </summary> /// <param name="sql">sql语句(不包含事务语句)</param> /// <returns>返回影响的行数</returns> public int ExecuteDataSetByTran2(string sql) { bllPaging objbllPaging = new bllPaging(); StringBuilder Builder = new StringBuilder(); Builder.AppendLine(" BEGIN TRAN tan1"); //开始事务 Builder.AppendLine(sql); //开始事务 Builder.AppendLine(" if(@@error=0) begin commit tran tan1; end else begin rollback tran tran1 end"); //结束事务 DataSet ds = null; try { int intResult = objbllPaging.ExecuteNonQueryBySQL2(Builder.ToString()); return(intResult); } catch (Exception ex) { } finally { objbllPaging = null; } return(0); }