/// SELECT * FROM conidcategory WHERE categoryid = @CategoryId /// SELECT d.id depid, d.name depname, d.shortcall depshortcall FROM conidcategory c, department d WHERE c.departmentid = d.id AND c.categoryid = 1 /// SELECT d.id depid, d.name depname, d.shortcall depshortcall, c.category, c.shortcall FROM conidcategory cc, department d, category c WHERE c.id = cc.categoryid AND cc.departmentid = d.id AND cc.categoryid = public static ContractCategory GetCategory(int categoryId) { MySqlConnection con = DBTools.GetMySqlConnection(); MySqlCommand cmd; ContractCategory category = new ContractCategory(); try { con.Open(); cmd = con.CreateCommand(); cmd.CommandText = GET_CATEGORY_BY_ID_STR; cmd.Parameters.AddWithValue("@CategoryId", categoryId); MySqlDataReader sqlRead = cmd.ExecuteReader(); cmd.Dispose(); while (sqlRead.Read()) { category.Id = int.Parse(sqlRead["id"].ToString()); category.Category = sqlRead["category"].ToString(); category.CategoryShortCall = sqlRead["shortcall"].ToString(); } con.Close(); con.Dispose(); } catch (Exception) { throw; } finally { if (con.State == ConnectionState.Open) { con.Close(); } } return category; }
public static List<ContractCategory> QuerySDepartmentContractCategory(int departmentId) { MySqlConnection con = DBTools.GetMySqlConnection(); MySqlCommand cmd; List<ContractCategory> categorys = new List<ContractCategory>(); try { con.Open(); cmd = con.CreateCommand(); cmd.CommandText = QUERY_SDEPARTMENT_CONTRACTCATEGORY_STR; cmd.Parameters.AddWithValue("@DepartmentId", departmentId); MySqlDataReader sqlRead = cmd.ExecuteReader(); cmd.Dispose(); while (sqlRead.Read()) { ContractCategory category = new ContractCategory(); category.Id = int.Parse(sqlRead["id"].ToString()); category.Category = sqlRead["category"].ToString(); category.CategoryShortCall = sqlRead["categoryshortcall"].ToString(); categorys.Add(category); } con.Close(); con.Dispose(); } catch (Exception) { throw; } finally { if (con.State == ConnectionState.Open) { con.Close(); } } return categorys; }