/// <summary> /// Поиск рекламы по id /// </summary> /// <param name="id"></param> /// <returns></returns> public static Advertisement FindAdvert(int? id) { Advertisement adv = new Advertisement(); using (SqlConnection con = new SqlConnection(connectionString)) { string query = String.Format( "SELECT Description, MaintenanceTime, Location.ToString() as Location, Type, Height, Width, MonthlyCost FROM Advertisement WHERE Id = {0}", id); SqlCommand com = new SqlCommand(query, con); //Открываем соединение с БД con.Open(); SqlDataReader dr = com.ExecuteReader(); if (dr.HasRows) { foreach (DbDataRecord result in dr) { // Записываем в классы все рекламы adv.Id = Convert.ToInt32(id); adv.Description = result.GetString(result.GetOrdinal("Description")); adv.MaintenanceTime = result.GetDateTime(result.GetOrdinal("MaintenanceTime")); adv.Location = result.GetString(result.GetOrdinal("Location")); adv.Type = result.GetString(result.GetOrdinal("Type")); adv.Height = (float)result.GetDecimal(result.GetOrdinal("Height")); adv.Width = (float)result.GetDecimal(result.GetOrdinal("Width")); adv.MonthlyCost = (float)result.GetDecimal(result.GetOrdinal("MonthlyCost")); } } } return adv; }
/// <summary> /// Выбор всех реклам с БД /// </summary> /// <returns></returns> public static List<Advertisement> GetAllAdverts() { List<Advertisement> _lst = new List<Advertisement>(); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand com = new SqlCommand("SELECT Id, Description, MaintenanceTime, Location.ToString() as Location, Type, Height, Width, MonthlyCost FROM Advertisement", con); //Открываем соединение с БД con.Open(); SqlDataReader dr = com.ExecuteReader(); if (dr.HasRows) { foreach (DbDataRecord result in dr) { // Записываем в классы все рекламы Advertisement adv = new Advertisement(); adv.Id = result.GetInt32(result.GetOrdinal("Id")); adv.Description = result.GetString(result.GetOrdinal("Description")); adv.MaintenanceTime = result.GetDateTime(result.GetOrdinal("MaintenanceTime")); adv.Location = result.GetString(result.GetOrdinal("Location")); adv.Type = result.GetString(result.GetOrdinal("Type")); adv.Height = (float)result.GetDecimal(result.GetOrdinal("Height")); adv.Width = (float)result.GetDecimal(result.GetOrdinal("Width")); adv.MonthlyCost = (float)result.GetDecimal(result.GetOrdinal("MonthlyCost")); _lst.Add(adv); } } } return _lst; }
/// <summary> /// Редактирование реклам /// </summary> /// <param name="id"></param> /// <param name="advertisement"></param> /// <param name="lat"></param> /// <param name="lng"></param> /// <returns></returns> public static bool UpdateAdvert(int id, Advertisement advertisement, string lat, string lng) { bool flag = false; string _location = "POINT (" + lat + " " + lng + " 12)"; string query = string.Format( "UPDATE Advertisement " + "SET Description = N'{0}', " + "MaintenanceTime = convert(datetime, '{1}', 103)," + "Location = '{2}'," + "Type = N'{3}'," + "Height = {4}," + "Width = {5}," + "MonthlyCost = {6}" + "WHERE Id = {7}", advertisement.Description, advertisement.MaintenanceTime, _location, advertisement.Type, advertisement.Height.ToString("0.00").Replace(",", "."), advertisement.Width.ToString("0.00").Replace(",", "."), advertisement.MonthlyCost.ToString("0.00").Replace(",", "."), id); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand com = new SqlCommand(query, con); con.Open(); if (com.ExecuteNonQuery() == 1) { flag = true; } } return flag; }
/// <summary> /// Вставка новой рекламы /// </summary> /// <param name="advertisement"></param> /// <returns></returns> public static bool SetNewAdvert(Advertisement advertisement, string lat, string lng) { bool flag = false; string _location = "POINT (" + lat + " " + lng + " 12)"; string query = string.Format( "INSERT INTO Advertisement (Description, MaintenanceTime, Location, Type, Height, Width, MonthlyCost) VALUES (N'{0}', " + "convert(datetime, '{1}', 103), '{2}',N'{3}',{4},{5},{6})", advertisement.Description, advertisement.MaintenanceTime, _location, advertisement.Type, advertisement.Height.ToString("0.00").Replace(",", "."), advertisement.Width.ToString("0.00").Replace(",", "."), advertisement.MonthlyCost.ToString("0.00").Replace(",", ".")); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand com = new SqlCommand(query, con); con.Open(); if (com.ExecuteNonQuery() == 1) { flag = true; } } return flag; }