public User AddNewUser(String userID, String password, String email, String role, String firstname = null,String lastname = null,String address = null
,String city = null,String province = null,String country = null)
{
User user = new User();
user.UserID = userID;
user.Password = password;
user.Email = email;
user.Role = role;
user.FirstName = firstname;
user.LastName = lastname;
user.Address = address;
user.City = city;
user.Province = province;
user.Country = country;
user.TheCognitoCredential = LetUsDealWithTheAWSCognitoIDStuff(userID);
int result = 0;
using (SqlConnection conn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand("[dbo].[AddUser]"))
{
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = userID;
cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = password;
cmd.Parameters.Add("@UserEmail", SqlDbType.NVarChar).Value = email;
cmd.Parameters.Add("@UserRole", SqlDbType.NVarChar).Value = role;
cmd.Parameters.Add("@FirsName", SqlDbType.NVarChar).Value = firstname;
cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = lastname;
cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = address;
cmd.Parameters.Add("@City", SqlDbType.NVarChar,50).Value = role.Trim();
cmd.Parameters.Add("@Province", SqlDbType.NVarChar).Value = province;
cmd.Parameters.Add("@Country", SqlDbType.NVarChar).Value = country;
cmd.Parameters.Add("@CognitoID", SqlDbType.NVarChar).Value = user.TheCognitoCredential.CognitoID;
cmd.Parameters.Add("@CognitoToken", SqlDbType.NVarChar).Value = user.TheCognitoCredential.CognitoToken;
SqlParameter outParam = cmd.Parameters.Add("@resultOutput", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
result = (int)outParam.Value;
if (result == 0)
{
user = new User();
user.FirstName = "Duplicated name or email";
user.LastName = "";
}
conn.Close();
}
}
return user;
}
public List<User> GetAllUsers()
{
List<User> users = new List<User>();
using(SqlConnection conn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand("[dbo].[GetAllUsers]"))
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(_ds);
foreach (DataRow row in _ds.Tables[0].Rows)
{
User user = new User();
user.UserID = (string)row["fldUserID"];
user.Email = (string)row["fldEmail"];
user.FirstName = DBNullConverterToString(row["fldFirstName"]);
user.LastName = DBNullConverterToString(row["fldLastName"]);
user.City = DBNullConverterToString(row["fldCity"]);
user.Province = DBNullConverterToString(row["fldProvince"]);
user.Country = DBNullConverterToString(row["fldCountry"]);
user.Address = DBNullConverterToString(row["fldAddress"]);
users.Add(user);
}
}
}
return users;
}
public User AuthenticateUser(string userID, string password)
{
User user = new User();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand("[dbo].[GetUserInfo]"))
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", SqlDbType.VarChar).Value = userID;
cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = password;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(_ds);
if (_ds.Tables[0].Rows.Count != 1)
{
User anonymousUser = new User();
anonymousUser.FirstName = "Anonymous";
anonymousUser.LastName = "User";
anonymousUser.Role = "AnonymousUser";
return anonymousUser;
}
else
{
DataRow row = _ds.Tables[0].Rows[0];
user.UserID = (string)row["fldUserID"];
user.Email = (string)row["fldEmail"];
user.FirstName = DBNullConverterToString(row["fldFirstName"]);
user.LastName = DBNullConverterToString(row["fldLastName"]);
user.City = DBNullConverterToString(row["fldCity"]);
user.Province = DBNullConverterToString(row["fldProvince"]);
user.Country = DBNullConverterToString(row["fldCountry"]);
user.Address = DBNullConverterToString(row["fldAddress"]);
user.Role = DBNullConverterToString(row["fldRole"]);
}
}
}
return user;
}