public UserInOrgInfo GetModel(Guid userId, Guid orgId)
{
UserInOrgInfo model = null;
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select top 1 AppCode,UserId,OrgId
from UserInOrg
where UserId = @UserId and OrgId = @OrgId " );
SqlParameter[] parms =
{
new SqlParameter("@UserId", SqlDbType.UniqueIdentifier),
new SqlParameter("@OrgId", SqlDbType.UniqueIdentifier)
};
parms[0].Value = userId;
parms[1].Value = orgId;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), parms))
{
if (reader != null)
{
if (reader.Read())
{
model = new UserInOrgInfo();
model.AppCode = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
model.UserId = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
model.OrgId = reader.IsDBNull(2) ? Guid.Empty : reader.GetGuid(2);
}
}
}
return(model);
}
public void InsertOrgStaff(StaffInfo model)
{
Insert(model);
var userInOrgInfo = new UserInOrgInfo(model.AppCode, model.UserId, model.OrgId);
_db.UserInOrg.Insert(userInOrgInfo);
}
public IList <UserInOrgInfo> GetList()
{
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select AppCode,UserId,OrgId
from UserInOrg
order by UserId " );
IList <UserInOrgInfo> list = new List <UserInOrgInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString()))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
UserInOrgInfo model = new UserInOrgInfo();
model.AppCode = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
model.UserId = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
model.OrgId = reader.IsDBNull(2) ? Guid.Empty : reader.GetGuid(2);
list.Add(model);
}
}
}
return(list);
}
public IList <UserInOrgInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(500);
sb.Append(@"select AppCode,UserId,OrgId
from UserInOrg ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.Append("order by UserId ");
IList <UserInOrgInfo> list = new List <UserInOrgInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
UserInOrgInfo model = new UserInOrgInfo();
model.AppCode = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
model.UserId = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
model.OrgId = reader.IsDBNull(2) ? Guid.Empty : reader.GetGuid(2);
list.Add(model);
}
}
}
return(list);
}
public IList <UserInOrgInfo> GetList(int pageIndex, int pageSize, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(500);
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by UserId) as RowNumber,
AppCode,UserId,OrgId
from UserInOrg " );
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <UserInOrgInfo> list = new List <UserInOrgInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
UserInOrgInfo model = new UserInOrgInfo();
model.AppCode = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
model.UserId = reader.IsDBNull(2) ? Guid.Empty : reader.GetGuid(2);
model.OrgId = reader.IsDBNull(3) ? Guid.Empty : reader.GetGuid(3);
list.Add(model);
}
}
}
return(list);
}
public int Update(UserInOrgInfo model)
{
StringBuilder sb = new StringBuilder(500);
sb.Append(@"update UserInOrg set AppCode = @AppCode
where UserId = @UserId and OrgId = @OrgId
" );
SqlParameter[] parms =
{
new SqlParameter("@UserId", SqlDbType.UniqueIdentifier),
new SqlParameter("@OrgId", SqlDbType.UniqueIdentifier),
new SqlParameter("@AppCode", SqlDbType.Char, 10)
};
parms[0].Value = model.UserId;
parms[1].Value = model.OrgId;
parms[2].Value = model.AppCode;
return(SqlHelper.ExecuteNonQuery(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), parms));
}
public int Insert(UserInOrgInfo model)
{
StringBuilder sb = new StringBuilder(300);
sb.Append(@"insert into UserInOrg (UserId,OrgId,AppCode)
values
(@UserId,@OrgId,@AppCode)
" );
SqlParameter[] parms =
{
new SqlParameter("@UserId", SqlDbType.UniqueIdentifier),
new SqlParameter("@OrgId", SqlDbType.UniqueIdentifier),
new SqlParameter("@AppCode", SqlDbType.Char, 10)
};
parms[0].Value = model.UserId;
parms[1].Value = model.OrgId;
parms[2].Value = model.AppCode;
return(SqlHelper.ExecuteNonQuery(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), parms));
}
