private IdentityMember ExtractMemberData(IDataReader reader)
{
var entity = new IdentityMember();
entity.Id = Utils.ConvertToInt32(reader["Id"]);
entity.UserName = reader["UserName"].ToString();
entity.Email = reader["Email"].ToString();
entity.PhoneNumber = reader["PhoneNumber"].ToString();
entity.FullName = reader["FullName"].ToString();
entity.DisplayName = reader["DisplayName"].ToString();
entity.Avatar = reader["Avatar"].ToString();
entity.OTPType = reader["OTPType"].ToString();
entity.Birthday = (reader["Birthday"] == DBNull.Value) ? null : (DateTime?)reader["Birthday"];
entity.Sex = Utils.ConvertToInt32(reader["Sex"]);
entity.Address = reader["Address"].ToString();
entity.IDCard = reader["IDCard"].ToString();
entity.Note = reader["Note"].ToString();
entity.Status = Utils.ConvertToInt32(reader["Status"]);
if (reader.HasColumn("TotalCount"))
{
entity.TotalCount = Utils.ConvertToInt32(reader["TotalCount"]);
}
return(entity);
}
public IdentityMember GetById(int Id)
{
var info = new IdentityMember();
var sqlCmd = @"M_User_GetById";
var parameters = new Dictionary <string, object>
{
{ "@Id", Id }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
{
while (reader.Read())
{
info = ExtractMemberData(reader);
}
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute M_User_GetById. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(info);
}
/// <summary>
/// Deletes a claim from a user
/// </summary>
/// <param name="user">The user to have a claim deleted</param>
/// <param name="claim">A claim to be deleted from user</param>
/// <returns></returns>
public void Delete(IdentityMember member, Claim claim)
{
db.Connection.Execute(@"Delete from MemberClaim
where UserId = @memberId and @ClaimValue = @value and ClaimType = @type",
new {
memberId = member.Id,
ClaimValue=claim.Value,
type=claim.Type
});
}
/// <summary>
/// Deletes a claim from a user
/// </summary>
/// <param name="user">The user to have a claim deleted</param>
/// <param name="claim">A claim to be deleted from user</param>
/// <returns></returns>
public void Delete(IdentityMember member, Claim claim)
{
_context.Execute(@"Delete from MemberClaim
where UserId = @memberId and @ClaimValue = @value and ClaimType = @type",
new
{
memberId = member.Id,
ClaimValue = claim.Value,
type = claim.Type
});
}
/// <summary>
/// Inserts a new login in the UserLogins table
/// </summary>
/// <param name="user">User to have new login added</param>
/// <param name="login">Login to be added</param>
/// <returns></returns>
public void Insert(IdentityMember member, UserLoginInfo login)
{
db.Connection.Execute(@"Insert into MemberLogin
(LoginProvider, ProviderKey, UserId)
values (@loginProvider, @providerKey, @userId)",
new
{
loginProvider=login.LoginProvider,
providerKey=login.ProviderKey,
userId=member.Id
});
}
/// <summary>
/// Inserts a new login in the UserLogins table
/// </summary>
/// <param name="user">User to have new login added</param>
/// <param name="login">Login to be added</param>
/// <returns></returns>
public void Insert(IdentityMember member, UserLoginInfo login)
{
_context.Execute(@"Insert into MemberLogin
(LoginProvider, ProviderKey, UserId)
values (@loginProvider, @providerKey, @userId)",
new
{
loginProvider = login.LoginProvider,
providerKey = login.ProviderKey,
userId = member.Id
});
}
/// <summary>
/// Deletes a login from a user in the UserLogins table
/// </summary>
/// <param name="user">User to have login deleted</param>
/// <param name="login">Login to be deleted from user</param>
/// <returns></returns>
public void Delete(IdentityMember member, UserLoginInfo login)
{
db.Connection.Execute(@"Delete from MemberLogin
where UserId = @userId
and LoginProvider = @loginProvider
and ProviderKey = @providerKey",
new
{
userId=member.Id,
loginProvider=login.LoginProvider,
providerKey=login.ProviderKey
});
}
/// <summary>
/// Deletes a login from a user in the UserLogins table
/// </summary>
/// <param name="user">User to have login deleted</param>
/// <param name="login">Login to be deleted from user</param>
/// <returns></returns>
public void Delete(IdentityMember member, UserLoginInfo login)
{
_context.Execute(@"Delete from MemberLogin
where UserId = @userId
and LoginProvider = @loginProvider
and ProviderKey = @providerKey",
new
{
userId = member.Id,
loginProvider = login.LoginProvider,
providerKey = login.ProviderKey
});
}
public int Insert(IdentityMember identity, out int code)
{
var newId = 0;
var sqlCmd = @"M_User_Insert";
code = 1;
var parameters = new Dictionary <string, object>
{
{ "@UserName", identity.UserName },
{ "@Email", identity.Email },
{ "@PhoneNumber", identity.PhoneNumber },
{ "@DisplayName", identity.DisplayName },
{ "@PasswordHash", identity.PasswordHash },
{ "@Status", identity.Status }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters);
if (reader.Read())
{
code = Utils.ConvertToInt32(reader[0]);
}
if (reader.NextResult())
{
if (reader.Read())
{
newId = Utils.ConvertToInt32(reader[0]);
}
}
}
}
catch (Exception ex)
{
var strError = "Failed to M_User_Insert. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public List <IdentityMember> GetByPage(IdentityMember filter, int currentPage, int pageSize)
{
//Common syntax
var sqlCmd = @"M_User_GetByPage";
List <IdentityMember> listData = null;
//For paging
int offset = (currentPage - 1) * pageSize;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@Keyword", filter.Keyword },
{ "@Offset", offset },
{ "@PageSize", pageSize },
{ "@Status", filter.Status }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
{
listData = new List <IdentityMember>();
while (reader.Read())
{
var info = ExtractMemberData(reader);
listData.Add(info);
}
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute M_User_GetByPage. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(listData);
}
public bool Update(IdentityMember identity, out int code)
{
//Common syntax
var sqlCmd = @"M_User_Update";
code = 1;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@Id", identity.Id },
{ "@UserName", identity.UserName },
{ "@Email", identity.Email },
{ "@PhoneNumber", identity.PhoneNumber },
{ "@DisplayName", identity.DisplayName },
{ "@Status", identity.Status }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters);
if (reader.Read())
{
code = Utils.ConvertToInt32(reader[0]);
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute M_User_Update. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(true);
}
public bool Update(IdentityMember identity, out int code)
{
return(myRepository.Update(identity, out code));
}
public int Insert(IdentityMember identity, out int code)
{
return(myRepository.Insert(identity, out code));
}
public List <IdentityMember> GetByPage(IdentityMember filter, int currentPage, int pageSize)
{
return(myRepository.GetByPage(filter, currentPage, pageSize));
}
/// <summary>
/// Inserts a new role for a user in the UserRoles table
/// </summary>
/// <param name="user">The User</param>
/// <param name="roleId">The Role's id</param>
/// <returns></returns>
public void Insert(IdentityMember member, int roleId)
{
_context.Execute(@"Insert into AspNetUserRoles (UserId, RoleId) values (@userId, @roleId",
new { userId = member.Id, roleId = roleId });
}
/// <summary>
/// Inserts a new role for a user in the UserRoles table
/// </summary>
/// <param name="user">The User</param>
/// <param name="roleId">The Role's id</param>
/// <returns></returns>
public void Insert(IdentityMember member, int roleId)
{
db.Connection.Execute(@"Insert into AspNetUserRoles (UserId, RoleId) values (@userId, @roleId",
new { userId = member.Id, roleId = roleId });
}
