select OAUserID,limit,name,
(select DepartmentName From Department as d
INNER JOIN OAUser as o
ON d.DepartmentID=o.DepartmentID
where OAuserName='zhangsan') as departmentname,
(select PositionName From Position as p
INNER JOIN OAUser as o
ON p.PositionID=o.PositionID
Where OAUserName='zhangsan') as positionname
from OAUser where OAUserName='zhangsan' 这个语句在SQL可以查询出结果.我将这个放在vs里面. 要怎么搞呢?
StringBuilder strSql=new StringBuilder();
strSql.Append("select OAUserID,limit,name,(select DepartmentName From Department as d INNER JOIN OAUser as o ON d.DepartmentID=o.DepartmentID where OAuserName=@OAUserName) as departmentname ,");
strSql.Append("(select PositionName From Position as p INNER JOIN OAUser as o ON p.PositionID=o.PositionIDWhere OAUserName=@OAUserName) as positionname ,");
strSql.Append("from OAUser where OAUserName=@OAUserName");我这样子写获取不了数据.请问要怎么写?
OAUserID, limit ,name 在OAUSER表,有DepartmentID,PositionID是Department表,Position表的外键.现在要获取Department,positioin表的
Department,PositionName字段的值.
(select DepartmentName From Department as d
INNER JOIN OAUser as o
ON d.DepartmentID=o.DepartmentID
where OAuserName='zhangsan') as departmentname,
(select PositionName From Position as p
INNER JOIN OAUser as o
ON p.PositionID=o.PositionID
Where OAUserName='zhangsan') as positionname
from OAUser where OAUserName='zhangsan' 这个语句在SQL可以查询出结果.我将这个放在vs里面. 要怎么搞呢?
StringBuilder strSql=new StringBuilder();
strSql.Append("select OAUserID,limit,name,(select DepartmentName From Department as d INNER JOIN OAUser as o ON d.DepartmentID=o.DepartmentID where OAuserName=@OAUserName) as departmentname ,");
strSql.Append("(select PositionName From Position as p INNER JOIN OAUser as o ON p.PositionID=o.PositionIDWhere OAUserName=@OAUserName) as positionname ,");
strSql.Append("from OAUser where OAUserName=@OAUserName");我这样子写获取不了数据.请问要怎么写?
OAUserID, limit ,name 在OAUSER表,有DepartmentID,PositionID是Department表,Position表的外键.现在要获取Department,positioin表的
Department,PositionName字段的值.
public UserDetail GetModel(string username)
{ StringBuilder strSql=new StringBuilder();
strSql.Append("select OAUserID,limit,name,(select DepartmentName From Department as d INNER JOIN OAUser as o ON d.DepartmentID=o.DepartmentID where OAuserName=@OAUserName) as departmentname ,");
strSql.Append("(select PositionName From Position as p INNER JOIN OAUser as o ON p.PositionID=o.PositionIDWhere OAUserName=@OAUserName) as positionname ");
strSql.Append("from OAUser where OAUserName=@OAUserName");
//string sqlstring = "select OAUserID,limit,name from OAUser where OAUserName=@OAUserName";
SqlParameter[] parameters = {
new SqlParameter("@OAUserName", username)
};
parameters[0].Value = username ; UserDetail user = new UserDetail();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if(ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["OAUserID"] != null && ds.Tables[0].Rows[0]["OAUserID"].ToString() != "")
{
user.OAUserID = int.Parse(ds.Tables[0].Rows[0]["OAUserID"].ToString());
}
if (ds.Tables[0].Rows[0]["limit"] != null && ds.Tables[0].Rows[0]["limit"].ToString() != "")
{
user.Limit = int.Parse(ds.Tables[0].Rows[0]["limit"].ToString());
}
if (ds.Tables[0].Rows[0]["name"] != null && ds.Tables[0].Rows[0]["name"].ToString() != "")
{
user.RealName = ds.Tables[0].Rows[0]["name"].ToString();
}
if (ds.Tables[0].Rows[0]["departmentname"] != null && ds.Tables[0].Rows[0]["departmentname"].ToString() != "")
{
user.RealName = ds.Tables[0].Rows[0]["departmentname"].ToString();
}
if (ds.Tables[0].Rows[0]["positionname"] != null && ds.Tables[0].Rows[0]["positionname"].ToString() != "")
{
user.RealName = ds.Tables[0].Rows[0]["positionname"].ToString();
}
return user;
}
else
{
return null;
}
}这是代码.
p.PositionID=o.PositionIDWhere
这里 where 没分开
p.PositionID=o.PositionIDWhere
这里 where 没分开