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); }