public List <Salesperson> SearchSalesperson(string Name, string Surname)
{
List <Salesperson> list = new List <Salesperson>();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Name", Name),
new SqlParameter("@Surname", Surname)
};
using (DataTable table = DBHelper.ExecuteParamerizedSelectCommand("sp_SearchSalesperson", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
Salesperson salesperson = new Salesperson();
salesperson.SalespersonID = Convert.ToInt32(row["SalespersonID"].ToString());
salesperson.Name = row["Name"].ToString();
salesperson.Surname = row["Surname"].ToString();
salesperson.Username = row["Username"].ToString();
salesperson.Password = row["Password"].ToString();
salesperson.Privileges = row["Privileges"].ToString();
salesperson.EmployeeType = row["Employee Type"].ToString();
list.Add(salesperson);
}
}
}
return(list);
}
public int GetSalesPersonID(string salesPFName, string salesPLName)
{
int salesID = 0;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Name", salesPFName),
new SqlParameter("@Surname", salesPLName)
};
using (DataTable table = DBHelper.ExecuteParamerizedSelectCommand("sp_SearchSalesperson", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
Salesperson SP = new Salesperson();
SP.SalespersonID = Convert.ToInt32(row["SalespersonID"].ToString());
salesID = SP.SalespersonID;
}
}
}
return(salesID);
}
public Salesperson GetById(int id)
{
string query =
"SELECT * FROM Salespersons WHERE id = @id";
Salesperson found = null;
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.Add("@id", SqlDbType.Int).Value = id;
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
found = BuildSalesperson(reader);
}
}
}
}
catch (Exception e)
{
Console.WriteLine("Error:" + e.Message);
}
return(found);
}
public IEnumerable <Salesperson> GetAll()
{
string query =
"SELECT * FROM Salespersons";
List <Salesperson> found = new List <Salesperson>();
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(query, connection))
{
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Salesperson salesperson = BuildSalesperson(reader);
found.Add(salesperson);
}
}
}
}
catch (Exception e)
{
Console.WriteLine("Error:" + e.Message);
}
return(found);
}
public int Insert(Salesperson salesperson)
{
int lastId = 0;
string query =
"INSERT INTO Salespersons(name)" +
"Values (@name);" +
"SELECT CAST(scope_identity() as int)";
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = salesperson.Name;
Object newId = command.ExecuteScalar();
lastId = Convert.ToInt32(newId);
}
}
}
catch (Exception e)
{
Console.WriteLine("Error:" + e.Message);
}
return(lastId);
}
public int Update(Salesperson salesperson)
{
int rowsAffected = 0;
string query =
"UPDATE Salespersons SET name = @name WHERE id = @id";
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = salesperson.Name;
command.Parameters.Add("@id", SqlDbType.Int).Value = salesperson.Id;
rowsAffected = command.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
Console.WriteLine("Error:" + e.Message);
}
return(rowsAffected);
}
public bool AddSalesperson(Salesperson salesperson)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Name", salesperson.Name),
new SqlParameter("@Surname", salesperson.Surname),
new SqlParameter("@Username", salesperson.Username),
new SqlParameter("@Password", salesperson.Password),
new SqlParameter("@Privileges", salesperson.Privileges),
new SqlParameter("@EmployeeType", salesperson.EmployeeType)
};
return(DBHelper.ExecuteNonQuery("sp_AddSalesperson", CommandType.StoredProcedure, parameters));
}
public District BuildDistrict(DataSet dataSet)
{
District districtObject = null;
foreach (DataRow distRow in dataSet.Tables["Districts"].Rows)
{
int id = (int)distRow["id"];
string name = (string)distRow["name"];
districtObject = new District {
Id = id, Name = name
};
var primarySP = new Salesperson();
var primarySPRowData = dataSet.Tables["Salespersons"].Select("id = " + (int)distRow["primary_salesperson_id"]);
primarySP.Id = (int)primarySPRowData.FirstOrDefault()["id"];
primarySP.Name = (string)primarySPRowData.FirstOrDefault()["name"];
districtObject.PrimarySalesperson = primarySP;
var tempStoreList = new List <Store>();
foreach (DataRow distStoreRow in distRow.GetChildRows(dataSet.Relations["DistStore"]))
{
int storeId = (int)distStoreRow["id"];
string storeName = (string)distStoreRow["name"];
var tempStore = new Store {
Id = storeId, Name = storeName
};
tempStoreList.Add(tempStore);
}
districtObject.Stores = tempStoreList;
var tempSPList = new List <Salesperson>();
foreach (DataRow distSalesRow in distRow.GetChildRows(dataSet.Relations["DistrictJunc"]))
{
int spId = (int)distSalesRow.GetParentRow(dataSet.Relations["SalespersonJunc"])["id"];
string spName = (string)distSalesRow.GetParentRow(dataSet.Relations["SalespersonJunc"])["name"];
var tempSalesperson = new Salesperson {
Id = spId, Name = spName
};
tempSPList.Add(tempSalesperson);
}
districtObject.Salespersons = tempSPList;
}
return(districtObject);
}
public void Login(Salesperson salesperson)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@username", salesperson.Username),
new SqlParameter("@password", salesperson.Password)
};
using (DataTable table = DBHelper.ExecuteParamerizedSelectCommand("sp_Login", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
salesperson.SalespersonID = Convert.ToInt32(row["SalespersonID"]);
salesperson.Name = row["Name"].ToString();
salesperson.Surname = row["Surname"].ToString();
salesperson.Privileges = row["Privileges"].ToString();
salesperson.EmployeeType = row["EmployeeType"].ToString();
}
}
}
public List <Salesperson> GetAllSalespersons()
{
List <Salesperson> list = new List <Salesperson>();
using (DataTable table = DBHelper.ExecuteSelectCommand("sp_GetAllSalespersons", CommandType.StoredProcedure))
{
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
Salesperson salesperson = new Salesperson();
salesperson.SalespersonID = Convert.ToInt32(row["SalespersonID"].ToString());
salesperson.Name = row["Name"].ToString();
salesperson.Surname = row["Surname"].ToString();
salesperson.Username = row["Username"].ToString();
salesperson.Password = row["Password"].ToString();
salesperson.Privileges = row["Privileges"].ToString();
salesperson.EmployeeType = row["Employee Type"].ToString();
list.Add(salesperson);
}
}
}
return(list);
}
private Salesperson BuildSalesperson(SqlDataReader reader)
{
Salesperson salesperson = new Salesperson();
int idOrdinal = reader.GetOrdinal("id");
int nameOrdinal = reader.GetOrdinal("name");
try
{
if (!reader.IsDBNull(idOrdinal) && !reader.IsDBNull(nameOrdinal))
{
salesperson.Id = reader.GetInt32(idOrdinal);
salesperson.Name = reader.GetString(nameOrdinal);
}
else
{
return(null);
}
}
catch (Exception e)
{
Console.WriteLine("Error in BuildSalesperson: " + e.Message);
}
return(salesperson);
}
public int Delete(Salesperson salesperson)
{
int rowsAffected = 0;
string query =
"DELETE FROM Salespersons WHERE id = @id";
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.Add("@id", SqlDbType.Int).Value = salesperson.Id;
rowsAffected = command.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
Console.WriteLine("Error:" + e.Message);
}
return(rowsAffected);
}
