/// <summary>
/// 查询功能列表
/// </summary>
/// <param name="funcCode"></param>
/// <param name="funcName"></param>
/// <returns></returns>
public DataSet QueryFunctions(string funcCode, string funcName)
{
string strSql = @" SELECT *
FROM dbo.TBLFUNCTION
WHERE 1 = 1 ";
if (!string.IsNullOrEmpty(funcCode))
{
strSql += " and p.functionkey = FunCode ";
}
if (!string.IsNullOrEmpty(funcName))
{
strSql += " and p.functionname = FunName ";
}
strSql += " ORDER BY functionorder ";
string[] paramNames = new string[2];
object[] paramValues = new object[2];
paramNames[0] = "FunCode";
paramNames[1] = "FunName";
paramValues[0] = funcCode;
paramValues[1] = funcName;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.ExecuteDataset(strSql);
broker.Close();
return(dst);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="lstOID"></param>
/// <returns></returns>
public bool DeleteFunction(List <string> lstOID)
{
string strSQL = @" DELETE FROM TBLFUNCTION WHERE oid=@OID ";
string[] paramNames = new string[1];
object[] paramValues = new object[1];
paramNames[0] = "OID";
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
foreach (string oid in lstOID)
{
paramValues[0] = oid;
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
}
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return(false);
}
finally
{
broker.Close();
}
return(true);
}
public PersonEntity GetPersonInfo(string userName, string passWord)
{
PersonEntity pe = new PersonEntity();
string strSql = @" SELECT *
FROM TBLPERSON where personaccount=@UserName and personpassword=@PassWord";
string[] paramName = new string[2];
object[] paramValue = new object[2];
paramName[0] = "UserName";
paramName[1] = "PassWord";
paramValue[0] = userName;
paramValue[1] = passWord;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.ExecuteDataset(strSql, CommandType.Text, paramName, paramValue);
broker.Close();
if (dst != null && dst.Tables[0] != null && dst.Tables[0].Rows.Count > 0)
{
return(this.DataRow2Person(dst.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
public PersonEntity GetPersonInfo(string personID)
{
PersonEntity pe = new PersonEntity();
string strSql = @" SELECT *
FROM TBLPERSON where oid=@OID ";
string[] paramName = new string[1];
object[] paramValue = new object[1];
paramName[0] = "OID";
paramValue[0] = personID;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.ExecuteDataset(strSql, CommandType.Text, paramName, paramValue);
broker.Close();
if (dst != null && dst.Tables[0] != null && dst.Tables[0].Rows.Count > 0)
{
return(this.DataRow2Person(dst.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 新增
/// </summary>
/// <param name="fun"></param>
/// <returns></returns>
public bool InsertFunction(FunctionEntity fun)
{
if (fun != null)
{
string strSQL = @" INSERT INTO TBLFUNCTION
( oid , functionkey , functionname , functionlevel , functionparentid , functionurl ,functionstatus ,
functionorder , functiontype , memo ,cuser ,cdate ,muser ,mdate ,addition1 ,addition2 )
VALUES ( @OID ,@functionkey ,@functionname ,@functionlevel ,@functionparentid ,@functionurl ,@functionstatus ,
@functionorder ,@functiontype , @memo ,@cuser ,GETDATE() , @muser ,GETDATE() ,NULL ,NULL) ";
string[] paramNames = new string[12];
object[] paramValues = new object[12];
paramNames[0] = "OID";
paramNames[1] = "functionkey";
paramNames[2] = "functionname";
paramNames[3] = "functionlevel";
paramNames[4] = "functionparentid";
paramNames[5] = "functionurl";
paramNames[6] = "functionstatus";
paramNames[7] = "functionorder";
paramNames[8] = "functiontype";
paramNames[9] = "memo";
paramNames[10] = "cuser";
paramNames[11] = "muser";
paramValues[0] = fun.OID;
paramValues[1] = fun.FUNCTIONKEY;
paramValues[2] = fun.FUNCTIONNAME;
paramValues[3] = fun.FUNCTIONLEVEL;
paramValues[4] = fun.FUNCTIONPARENTID;
paramValues[5] = fun.FUNCTIONURL;
paramValues[6] = fun.FUNCTIONSTATUS;
paramValues[7] = fun.FUNCTIONORDER;
paramValues[8] = fun.FUNCTIONTYPE;
paramValues[9] = fun.MEMO;
paramValues[10] = fun.CUSER;
paramValues[11] = fun.MUSER;
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return(false);
}
finally
{
broker.Close();
}
}
return(true);
}
public DataSet GetAllFunctions()
{
string strSql = " SELECT * FROM TBLFUNCTION ORDER BY functionorder ";
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.ExecuteDataset(strSql);
broker.Close();
return(dst);
}
public string GetChildMaxOrder(string oid)
{
string strSQL = @" SELECT COUNT(1)+1 FROM TBLFUNCTION WHERE functionparentid=@OID ";
string[] paramNames = new string[1];
object[] paramValues = new object[1];
paramNames[0] = "OID";
paramValues[0] = oid;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
return(broker.ExecuteScalar(strSQL, CommandType.Text, paramNames, paramValues));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="fun"></param>
/// <returns></returns>
public bool UpdateFunction(FunctionEntity fun)
{
if (fun != null)
{
string strSQL = @" UPDATE TBLFUNCTION SET functionkey=@functionkey,functionname=@functionname, functionurl=@functionurl ,
functionstatus=@functionstatus ,functionorder=@functionorder,memo=@memo,muser=@muser,mdate=GETDATE()
WHERE oid=@OID " ;
string[] paramNames = new string[8];
object[] paramValues = new object[8];
paramNames[0] = "OID";
paramNames[1] = "functionkey";
paramNames[2] = "functionname";
paramNames[3] = "functionurl";
paramNames[4] = "functionstatus";
paramNames[5] = "functionorder";
paramNames[6] = "memo";
paramNames[7] = "muser";
paramValues[0] = fun.OID;
paramValues[1] = fun.FUNCTIONKEY;
paramValues[2] = fun.FUNCTIONNAME;
paramValues[3] = fun.FUNCTIONURL;
paramValues[4] = fun.FUNCTIONSTATUS;
paramValues[5] = fun.FUNCTIONORDER;
paramValues[6] = fun.MEMO;
paramValues[7] = fun.MUSER;
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return(false);
}
finally
{
broker.Close();
}
}
return(true);
}
public FunctionEntity GetFunc(string oid)
{
string strSQL = @" SELECT * FROM TBLFUNCTION WHERE oid=@OID ";
string[] paramNames = new string[1];
object[] paramValues = new object[1];
paramNames[0] = "OID";
paramValues[0] = oid;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.ExecuteDataset(strSQL, CommandType.Text, paramNames, paramValues);
broker.Close();
if (dst != null && dst.Tables[0] != null && dst.Tables[0].Rows.Count > 0)
{
return(Datarow2Entity(dst.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
public DataSet QueryPersons(int pageIndex, int pageSize, out int rowCount, string userAccount, string userName)
{
string strSql = @" SELECT oid ,
personaccount ,
personname ,
personsex ,
personofficephone ,
personmobilephone ,
personemail ,
personmemo
FROM TBLPERSON where 1=1 ";
if (!string.IsNullOrEmpty(userAccount))
{
strSql += " and personaccount like @PersonAccount ";
}
if (!string.IsNullOrEmpty(userName))
{
strSql += " and personname like @PersonName ";
}
string[] paramNames = new string[2];
object[] paramValues = new object[2];
paramNames[0] = "PersonAccount";
paramNames[1] = "PersonName";
paramValues[0] = "%" + userAccount + "%";
paramValues[1] = "%" + userName + "%";
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.QueryPageFromSql(strSql, paramNames, paramValues, pageIndex, pageSize, out rowCount);
broker.Close();
return(dst);
}