public BusinessRole CreateBusinessRole(string name, string businessId)
{
BusinessRole br = null;
try
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[BusinessRole] (name, businessId) OUTPUT Inserted.id VALUES (@Name, @BusinessId);", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name.Trim());
cmd.Parameters.AddWithValue("@BusinessId", businessId.Trim());
con.Open();
Guid businessRoleId = (Guid)cmd.ExecuteScalar();
if (businessRoleId != null)
{
br = new BusinessRole(businessRoleId.ToString(), name, businessId, false);
br.EmployeeCount = 0;
}
}
}
}
catch (Exception ex)
{
// log?
Console.WriteLine(ex.Message, ex.ToString());
return(br);
}
return(br);
}
public List <BusinessRole> GetBusinessRoles(string businessId)
{
List <BusinessRole> businessRoles = new List <BusinessRole>();
try
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString))
{
using (SqlDataAdapter sda = new SqlDataAdapter("SELECT br.id, br.[name], br.businessId, br.deleted, count(bea.id) employeeCount FROM BusinessRole br LEFT JOIN BusinessEmployeeAccess bea ON br.id = bea.roleId WHERE br.businessId = @BusinessId AND br.deleted = 0 GROUP BY br.id, br.[name], br.businessId, br.deleted;", con))
{
DataSet ds = new DataSet();
sda.SelectCommand.CommandType = CommandType.Text;
sda.SelectCommand.Parameters.AddWithValue("@BusinessId", businessId);
sda.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
string id = row["id"].ToString();
string name = row["name"].ToString();
bool deleted = (bool)row["deleted"];
int employeeCount = (int)row["employeeCount"];
BusinessRole br = new BusinessRole(id, name, businessId, deleted);
br.EmployeeCount = employeeCount;
businessRoles.Add(br);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message, ex.ToString());
// log?
}
return(businessRoles);
}
public BusinessRole GetBusinessRole(string brId)
{
BusinessRole br = null;
try
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM [dbo].[BusinessRole] WHERE [id] = @Id", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Id", brId);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
string name = sdr["name"].ToString();
string id = sdr["id"].ToString();
bool deleted = Convert.ToBoolean(sdr["deleted"]);
br = new BusinessRole(id, name, brId, deleted);
br.EmployeeCount = 0;
}
}
}
}
catch (Exception ex)
{
// log?
Console.WriteLine(ex.Message, ex.ToString());
return(br);
}
return(br);
}