/// <summary> /// Saves <see cref="Company"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="company">Information about <see cref="Company"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, Company company) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (company.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO Company (Acronym, MapAcronym, Name, URL, LoadOrder, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " + "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", "acronym", "mapAcronym", "name", "url", "loadOrder", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, company.Acronym.Replace(" ", "").ToUpper(), company.MapAcronym.Replace(" ", "").ToUpper(), company.Name, company.URL.ToNotNull(), company.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE Company SET Acronym = {0}, MapAcronym = {1}, Name = {2}, URL = {3}, LoadOrder = {4}, " + "UpdatedBy = {5}, UpdatedOn = {6} WHERE ID = {7}", "acronym", "mapAcronym", "name", "url", "loadOrder", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, company.Acronym.Replace(" ", "").ToUpper(), company.MapAcronym.Replace(" ", "").ToUpper(), company.Name, company.URL.ToNotNull(), company.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, company.ID); } return "Company information saved successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Loads <see cref="Company"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="keys">Keys of the companies to be loaded from the database.</param> /// <returns>Collection of <see cref="Company"/>.</returns> public static ObservableCollection<Company> Load(AdoDataConnection database, IList<int> keys) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string query; string commaSeparatedKeys; Company[] companyList = null; DataTable companyTable; int id; if ((object)keys != null && keys.Count > 0) { commaSeparatedKeys = keys.Select(key => key.ToString()).Aggregate((str1, str2) => str1 + "," + str2); query = string.Format("SELECT ID, Acronym, MapAcronym, Name, URL, LoadOrder FROM Company WHERE ID IN ({0})", commaSeparatedKeys); companyTable = database.Connection.RetrieveData(database.AdapterType, query); companyList = new Company[companyTable.Rows.Count]; foreach (DataRow row in companyTable.Rows) { id = row.ConvertField<int>("ID"); companyList[keys.IndexOf(id)] = new Company() { ID = id, Acronym = row.Field<string>("Acronym"), MapAcronym = row.Field<string>("MapAcronym"), Name = row.Field<string>("Name"), URL = row.Field<string>("URL"), LoadOrder = row.ConvertField<int>("LoadOrder") }; } } return new ObservableCollection<Company>(companyList ?? new Company[0]); } finally { if (createdConnection && database != null) database.Dispose(); } }