/// <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);
}
