public string addUserFromXml(User user)
{
string status = "";
string data = "";
SqlCommand cmd = getCommand("Custom_AddUserFromXml");
cmd.Parameters.AddWithValue("@xml", user.xml);
this.con.Open();
try
{
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
status = reader.GetValue(0).ToString();
data = reader.GetValue(1).ToString();
}
}
}
catch (SqlException e)
{
status = "Exception Occured while adding user " + user.userName + "\n Message : " + e.Message.ToString();
}
this.con.Close();
return status + "|" + data;
}
public string deleteUserFromXml(User user)
{
string status = "";
string data = "";
SqlCommand cmd = getCommand("Custom_DeleteUserFromXml");
cmd.Parameters.AddWithValue("@xml", user.xml);
this.con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
status = reader.GetValue(0).ToString();
data = reader.GetValue(1).ToString();
}
}
this.con.Close();
return status + "|" + data;
}
// This will add personal details of user.
public List<User> addPersonalDetails()
{
List<string> usersList = new List<string>();
List<User> users = new List<User>();
int passwordIndex = getColumnIndex("Password");
for (int rCnt = 2; rCnt <= this.xlRange.Rows.Count; rCnt++)
{
bool skipRow = true; //if user already exists, we dont need to iterate through the rows.
string username = Convert.ToString((this.xlRange.Cells[rCnt, 1] as Range).Value2);
if (!usersList.Contains(username)) //this means that the username is encountered for the first time.
{
usersList.Add(username);
//new user discovered and thus we won't skip iterating the row from excel.
skipRow = false;
}
if (!skipRow)
{
User user = new User();
for (int cCnt = 1; cCnt <= passwordIndex; cCnt++)
{
string columnName = Convert.ToString((this.xlRange.Cells[1, cCnt] as Range).Value2);
string cellValue = Convert.ToString((this.xlRange.Cells[rCnt, cCnt] as Range).Value2);
switch (columnName)
{
case "UserName":
user.userName = cellValue;
break;
case "FullName":
user.fullName = cellValue;
break;
case "EmailId":
user.email = cellValue;
break;
case "Password":
user.password = cellValue;
break;
}
}
users.Add(user);
}
}
return users;
}
public string validatePersonalDetails()
{
int rCnt = 0;
int cCnt = 0;
int passwordIndex = getColumnIndex("Password");
string log = "";
List<User> allUsers = new List<User>();
List<User> uniqueUsers = new List<User>();
List<string> usersList = new List<string>();
for (rCnt = 2; rCnt <= this.xlRange.Rows.Count; rCnt++)
{
User user = new User();
for (cCnt = 1; cCnt <= passwordIndex; cCnt++)
{
string columnName = Convert.ToString((this.xlRange.Cells[1, cCnt] as Range).Value2);
string cellValue = Convert.ToString((this.xlRange.Cells[rCnt, cCnt] as Range).Value2);
switch (columnName)
{
case "UserName":
user.userName = cellValue;
break;
case "FullName":
user.fullName = cellValue;
break;
case "EmailId":
user.email = cellValue;
break;
case "Password":
user.password = cellValue;
break;
}
}
string username = Convert.ToString((this.xlRange.Cells[rCnt, 1] as Range).Value2);
if (!usersList.Contains(username))
{
usersList.Add(username);
uniqueUsers.Add(user);
}
allUsers.Add(user);
}
foreach (User user in allUsers)
{
foreach (User uniqueUser in uniqueUsers)
{
if (user.userName.Equals(uniqueUser.userName))
{
bool errorFound = false;
if (!user.fullName.Equals(uniqueUser.fullName))
{
log += "FullName mismatch for user " + user.userName + " found" + Environment.NewLine;
errorFound = true;
}
if (!user.email.Equals(uniqueUser.email))
{
log += "Email mismatch for user " + user.userName + " found" + Environment.NewLine;
errorFound = true;
}
if (!user.password.Equals(uniqueUser.password))
{
log += "Password mismatch for user " + user.userName + " found" + Environment.NewLine;
errorFound = true;
}
if (errorFound)
{
break;
}
}
}
}
return log;
}
public List<User> storeUserDetails()
{
List<string> usersList = new List<string>();
List<User> users = new List<User>();
for (int rCnt = 2; rCnt <= this.xlRange.Rows.Count; rCnt++)
{
bool skipRow = true; //if user already exists, we dont need to iterate through the rows.
string username = Convert.ToString((this.xlRange.Cells[rCnt, 1] as Range).Value2);
if (!usersList.Contains(username)) //this means that the username is encountered for the first time.
{
usersList.Add(username);
//new user discovered and thus we won't skip iterating the row from excel.
skipRow = false;
}
if (!skipRow)
{
User user = new User();
user.userName = Convert.ToString((this.xlRange.Cells[rCnt, 1] as Range).Value2);
users.Add(user);
}
}
return users;
}
public int getHomeEntityCount(User user)
{
int homeEntityCount = 0;
foreach (Entity entity in user.entities)
{
if (entity.homeEntity == 1)
{
homeEntityCount++;
}
}
return homeEntityCount;
}