public List <CountryCity> getCountryCity() { SqlConnection connection = new SqlConnection(connectionString); // string query = "Select DISTINCT cn.CountryName AS CountryName,cs.CityName AS CityName,cs.About AS AboutCity , cs.NoOfDwellers AS NoOfDwellers," + // "cs.Location AS Location,cs.Weather AS Weather,cn.About AS AboutCountry from Countries cn join cities cs ON cn.CountryId = cs.CountryId order by cityName ASC"; string query = "Select * from Countries cn join cities cs on cs.countryId = cn.countryId"; SqlCommand command = new SqlCommand(query, connection); List <CountryCity> countryCityList = new List <CountryCity>(); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { CountryCity countryCity = new CountryCity(); countryCity.CityName = reader["CityName"].ToString(); countryCity.AboutCity = reader["About"].ToString(); countryCity.NoOfDwellers = (int)reader["NoOfDwellers"]; countryCity.Location = reader["Location"].ToString(); countryCity.Weather = reader["Weather"].ToString(); countryCity.CountryName = reader["CountryName"].ToString(); countryCity.AboutCountry = reader["About"].ToString(); countryCity.CountryId = (int)reader["CountryId"]; countryCityList.Add(countryCity); } reader.Close(); connection.Close(); return(countryCityList); }
public List <CountryCity> GetCountryCityByCountryName(string countryName) { SqlConnection connection = new SqlConnection(connectionString); List <CountryCity> countryCityByName = new List <CountryCity>(); string query = "Select * from Countries cn join cities cs on cs.countryId = cn.countryId WHERE CountryName LIKE '%" + countryName + "%'"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { CountryCity countryCity = new CountryCity(); countryCity.CityName = reader["CityName"].ToString(); countryCity.AboutCity = reader["About"].ToString(); countryCity.NoOfDwellers = (int)reader["NoOfDwellers"]; countryCity.Location = reader["Location"].ToString(); countryCity.Weather = reader["Weather"].ToString(); countryCity.CountryName = reader["CountryName"].ToString(); countryCity.AboutCountry = reader["About"].ToString(); countryCityByName.Add(countryCity); } reader.Close(); connection.Close(); return(countryCityByName); }
public ActionResult SavePlant(Plant plant) { string long1 = Request.Form["long1"]; string long2 = Request.Form["long2"]; string long3 = Request.Form["long3"]; double value = 0; double longValue = 0; double.TryParse(long1, out longValue); value += longValue; double.TryParse(long2, out longValue); value += (longValue / 60); double.TryParse(long3, out longValue); value += (longValue / 3600); plant.longitude = value; value = 0; longValue = 0; string lat1 = Request.Form["lat1"]; double.TryParse(lat1, out longValue); value += longValue; string lat2 = Request.Form["lat2"]; double.TryParse(lat2, out longValue); value += (longValue / 60); string lat3 = Request.Form["lat3"]; double.TryParse(lat3, out longValue); value += (longValue / 3600); plant.latitude = value; plant.longitudeString = string.Format("{0},{1},{2}", long1, long2, long3); plant.latitudeString = string.Format("{0},{1},{2}", lat1, lat2, lat3); if (string.IsNullOrEmpty(plant.pic)) { plant.pic = string.Empty; } plant.userID = UserUtil.getCurUser().id; CountryCity area = CountryCityService.GetInstance().GetCity(plant.country); plant.area = area == null ? string.Empty : area.weather_code; int plantid = plant.id; if (plant.id > 0) { PlantService.GetInstance().UpdatePlantInfo(plant); } else { plantid = PlantService.GetInstance().AddPlantInfo(plant); PlantUserService.GetInstance().AddPlantUser(new PlantUser { plantID = plantid, userID = int.Parse(plant.userID.ToString()) }); //添加电站时,向电站用户关系表中加记录 } UserUtil.ResetLogin(UserUtil.getCurUser()); return(Redirect("/newregister/addplantcontrol?plantid=" + plantid)); }
public List <CountryCity> CountryCityList() { SqlConnection connection = new SqlConnection(connectionString); string query = "Select cs.CityName AS CityName, cs.NoOfDwellers AS NoOfDwellers," + "cn.CountryName AS CountryName " + "from Countries cn join cities cs " + "ON cn.CountryId = cs.CountryId order by cityName ASC"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <CountryCity> countryList = new List <CountryCity>(); CountryCity countryCity = null; while (reader.Read()) { countryCity = new CountryCity(); countryCity.CountryName = reader["CountryName"].ToString(); countryCity.CityName = reader["CityName"].ToString(); countryCity.NoOfDwellers = (int)reader["NoOfDwellers"]; countryList.Add(countryCity); } reader.Close(); connection.Close(); return(countryList); }
public int Save(CountryCity city) { if (city.id > 0) { return(_countryCityDao.Update(city)); } return(_countryCityDao.Insert(city)); }
public override StandardValuesCollection GetStandardValues(ITypeDescriptorContext context) { List <string> cities = new List <string>(); CountryCity cc = context.Instance as CountryCity; if (cc != null) { if (cc.Country == null) { cities.AddRange(cityList.Select(x => x.City)); } else { cities.AddRange(cityList.Where(x => x.Country == cc.Country) .Select(y => y.City)); } } return(new StandardValuesCollection(cities)); }
public List <CountryCity> GetCountry() { SqlConnection connection = new SqlConnection(connectionString); List <CountryCity> countryCityByName = new List <CountryCity>(); string query = "Select Distinct CountryName from Countries order by countryName"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { CountryCity countryCity = new CountryCity(); countryCity.CountryName = reader["CountryName"].ToString(); countryCityByName.Add(countryCity); } reader.Close(); connection.Close(); return(countryCityByName); }
public bool Create(City model, int countryId, int stateId) { using (var context = new CourseContext()) { City entity = model; if (countryId != 0) { Country country = context.Country.Find(countryId); if (country != null) { CountryCity countryCity = new CountryCity() { Country = country, City = entity }; context.Add(countryCity); } } if (stateId != 0) { State state = context.State.Find(stateId); if (state != null) { StateCity stateCity = new StateCity() { State = state, City = entity }; context.Add(stateCity); } } if (countryId == 0 && stateId == 0) { context.City.Add(entity); } context.SaveChanges(); return(true); } }
public List <CountryCity> GetCountryByName(string countryName) { SqlConnection connection = new SqlConnection(connectionString); List <CountryCity> getCountryByNameList = new List <CountryCity>(); string query = "select cn.CountryName AS CountryName,cn.About AS AboutCountry,COUNT(cs.cityName) AS CityName,SUM(cs.NoOfDwellers) AS NoOfDwellers from Countries cn join Cities cs ON cs.countryId = cn.CountryId WHERE countryName LIKE '%" + countryName + "%' group by cn.CountryName,cn.About"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { CountryCity countryCity = new CountryCity(); countryCity.CountryName = reader["CountryName"].ToString(); countryCity.AboutCountry = reader["AboutCountry"].ToString(); countryCity.CityName = reader["CityName"].ToString(); countryCity.NoOfDwellers = (int)reader["NoOfDwellers"]; getCountryByNameList.Add(countryCity); } reader.Close(); connection.Close(); return(getCountryByNameList); }
static void LINQToEntities() { using (NorthwindContext db = new NorthwindContext()) { // Filtro var q1 = from c in db.Customers where c.Country == "Mexico" select c; var q1x = db.Customers. Where(c => c.Country == "Mexico"); //var result = q1.Count(); //var any = result.Any(); foreach (var item in q1x) { Console.WriteLine($"{item.CustomerID} {item.CompanyName} {item.ContactName} {item.Country}"); } } // * Proyecciones using (NorthwindContext db = new NorthwindContext()) { var q2 = from c in db.Customers select c.Country; var q2x = db.Customers.Select(c => c.Country); // Usando tipos anónimos var q2y = from c in db.Customers select new { c.CustomerID, c.ContactName }; var q2z = db.Customers.Select(c => new { Id = c.CustomerID, c.ContactName }); var q2a = from c in db.Customers select new Category() { CategoryName = c.ContactName }; var q2w = db.Customers.Select(c => new Category() { CategoryName = c.ContactName }); Console.Clear(); foreach (var item in q2z) { Console.WriteLine($"{item.Id}, {item.ContactName}"); } } // SelectMany using (NorthwindContext db = new NorthwindContext()) { var q4 = db.Customers. Where(c => c.Country == "Mexico"). SelectMany(c => c.Orders); // Esto aplica si solo se usar .Select //foreach (var c in q4) //{ // foreach (var o in c) // { // } //} var q4x = db.Orders. Where(o => o.Customer.Country == "Mexico"); Console.Clear(); foreach (var item in q4x) { Console.WriteLine($"{item.CustomerID}, {item.OrderID}"); } } // * Ordenamiento using (NorthwindContext db = new NorthwindContext()) { var q5 = from c in db.Customers where c.Orders.Count > 5 orderby c.Country descending select c; var q5x = db.Customers. Where(c => c.Orders.Count > 5). OrderByDescending(c => c.Country); Console.Clear(); foreach (var item in q5) { Console.WriteLine($"{item.CompanyName}, {item.Country}"); } var q6 = from c in db.Customers orderby c.CompanyName, c.ContactTitle, c.ContactName select c; var q6x = db.Customers.OrderBy(c => new { c.CompanyName, c.ContactTitle }). ThenBy(c => c.ContactName); Console.Clear(); foreach (var item in q6) { Console.WriteLine($"{item.CompanyName}, {item.Country}"); } } // Agrupamiento using (NorthwindContext db = new NorthwindContext()) { var q7 = from c in db.Customers group c by c.Country into CustByCountry select CustByCountry; var q7x = db.Customers.GroupBy(c => c.Country); Console.Clear(); foreach (var grupo in q7) { Console.WriteLine($"{grupo.Key}, {grupo.Count()}"); foreach (var c in grupo) { Console.WriteLine($"\t{c.ContactName}"); } } var q7y = from c in db.Customers group c by new { c.Country, c.City } into CountryCity where CountryCity.Count() > 1 select new { Country = CountryCity.Key.Country, City = CountryCity.Key.City, Count = CountryCity.Count(), Items = CountryCity }; var q7y2 = db.Customers.GroupBy(c => new { c.Country, c.City }). Where(g => g.Count() > 1). Select(g => new { Country = g.Key.Country, City = g.Key.City, Count = g.Count(), Items = g }); Console.Clear(); foreach (var item in q7y) { Console.WriteLine($"{item.Country}, {item.City}, {item.Count}"); foreach (var c in item.Items) { Console.WriteLine($"\t{c.ContactName}"); } } } // Join using (NorthwindContext db = new NorthwindContext()) { var q8 = from c in db.Customers join o in db.Orders on c.CustomerID equals o.CustomerID select new { c, o }; // new { c.CustomerID, c.Country } //equals new { o.CustomerID, Country = o.ShipCountry } var q8x = db.Customers.Join( db.Orders, c => c.CustomerID, o => o.CustomerID, (c, o) => new { c, o }); Console.Clear(); foreach (var item in q8) { Console.WriteLine($"{item.c.CustomerID}, {item.o.OrderID}"); } // Join agrupado var q8y = from c in db.Customers join o in db.Orders on c.CustomerID equals o.CustomerID into CustomerOrders select new { c, Orders = CustomerOrders }; //select CustomerOrders; foreach (var ordenes in q8y) { //foreach (var orden in ordenes) //{ //} } // Left Ourter Join var q8z = from c in db.Customers join o in db.Orders on c.CustomerID equals o.CustomerID into CustomerOrders from detalle in CustomerOrders.DefaultIfEmpty() select new { Customer = c, Order = detalle }; foreach (var item in q8z) { if (item.Order == null) { Console.WriteLine($"Customer {item.Customer.CustomerID} with NO orders!"); } } } // Conjuntos using (NorthwindContext db = new NorthwindContext()) { var q9 = db.Customers. Select(c => c.Country).Distinct(); var q10 = db.Customers.Except( db.Customers.Where( c => c.Country == "Mexico")). Select(c => c.Country).Distinct(); var q11 = db.Customers.Where(c => c.Country == "Germany").Union(db.Customers.Where(c => c.Country == "Italy")); Console.Clear(); foreach (var item in q11) { Console.WriteLine($"{item.ContactName} {item.Country}"); } } // * Partición (paginación) using (NorthwindContext db = new NorthwindContext()) { var q11 = db.Customers. OrderBy(c => c.CustomerID). Skip(10); // Tomar los primero 10 elementos var q12 = db.Customers. OrderBy(c => c.CustomerID). Take(10); // Segunda página de 5 elementos var q13 = db.Customers. OrderBy(c => c.CustomerID). Skip(5).Take(5); // SELECT* // FROM[dbo].[Products] AS[Extent1] // ORDER BY row_number() OVER(ORDER BY[Extent1].[ProductID] ASC) // OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY var q14 = db.Customers. OrderBy(c => c.CustomerID). TakeWhile(c => c.Country == "Mexico"); Console.Clear(); foreach (var item in q13) { Console.WriteLine($"{item.CustomerID}, {item.CompanyName}"); } } // * Modificación de consulta using (NorthwindContext db = new NorthwindContext()) { var q14 = db.Customers. Where(c => c.Orders.Count > 5); Console.Clear(); Console.WriteLine(q14.Count()); q14 = q14.Where(c => c.Country == "Mexico"); Console.WriteLine(q14.Count()); q14 = q14.OrderByDescending(c => c.ContactName); foreach (var item in q14) { Console.WriteLine(item.ContactName); } } // * Métodos útiles using (NorthwindContext db = new NorthwindContext()) { var o1 = db.Customers.First(); o1 = db.Customers.FirstOrDefault(); o1 = db.Customers.Where(c => c.CustomerID == "ALFKI") .Single(); o1 = db.Customers.Where(c => c.CustomerID == "ALFKI"). SingleOrDefault(); o1 = db.Customers.SingleOrDefault(c => c.CustomerID == "ALFKI"); var o2 = db.Customers.All(c => c.Orders.Count > 5 && c.Country == "Mexico"); o2 = db.Customers. Any(c => c.Orders.Count > 5); var sum = db.OrderDetails. Sum(od => od.Quantity * od.UnitPrice); var avg = db.OrderDetails. Average(od => od.Quantity * od.UnitPrice); } }
public CountryCity Get(CountryCity cc) { return(_countryCityDao.Get(cc)); }
static void LINQToEntities() { using (NWContext db = new NWContext()) { // Filtro var q1 = from c in db.Customers where c.Country == "Mexico" select c; var q1x = db.Customers. Where(c => c.Country == "Mexico"); //var result = q1.Count(); //var any = result.Any(); foreach (var item in q1x) { Console.WriteLine($"{item.CustomerId} {item.CompanyName} {item.ContactName} {item.Country}"); } } // Proyecciones using (NWContext db = new NWContext()) { var q2 = from c in db.Customers select c.Country; var q2x = db.Customers.Select(c => c.Country); var q2y = from c in db.Customers select new { c.CustomerId, c.ContactName }; var q2z = db.Customers.Select(c => new { Id = c.CustomerId, c.ContactName }); var q2w = db.Customers.Select(c => new Categories() { CategoryName = c.ContactName }); Console.Clear(); foreach (var item in q2z) { Console.WriteLine($"{item.Id}, {item.ContactName}"); } } // SelectMany using (NWContext db = new NWContext()) { var q4 = db.Customers. Where(c => c.Country == "Mexico"). SelectMany(c => c.Orders); var q4x = db.Orders. Where(o => o.Customer.Country == "Mexico"); Console.Clear(); foreach (var item in q4) { Console.WriteLine($"{item.CustomerId}, {item.OrderId}"); } } // Ordenamiento using (NWContext db = new NWContext()) { var q5 = from c in db.Customers where c.Orders.Count > 5 orderby c.Country descending select c; var q5x = db.Customers. Where(c => c.Orders.Count > 5). OrderByDescending(c => c.Country); Console.Clear(); foreach (var item in q5) { Console.WriteLine($"{item.CompanyName}, {item.Country}"); } var q6 = from c in db.Customers orderby c.CompanyName, c.ContactTitle, c.ContactName select c; var q6x = db.Customers.OrderBy(c => new { c.CompanyName, c.ContactTitle }). ThenBy(c => c.ContactName); Console.Clear(); foreach (var item in q6) { Console.WriteLine($"{item.CompanyName}, {item.Country}"); } } // Agrupamiento using (NWContext db = new NWContext()) { var q7 = from c in db.Customers group c by c.Country into CustByCountry select CustByCountry; var q7x = db.Customers.GroupBy(c => c.Country); Console.Clear(); foreach (var item in q7) { Console.WriteLine($"{item.Key}, {item.Count()}"); foreach (var c in item) { Console.WriteLine($"\t{c.ContactName}"); } } var q7y = from c in db.Customers group c by new { c.Country, c.City } into CountryCity where CountryCity.Count() > 1 select new { Country = CountryCity.Key.Country, City = CountryCity.Key.City, Count = CountryCity.Count(), Items = CountryCity }; var q7y2 = db.Customers.GroupBy(c => new { c.Country, c.City }). Where(g => g.Count() > 1). Select(g => new { Country = g.Key.Country, City = g.Key.City, Count = g.Count(), Items = g }); Console.Clear(); foreach (var item in q7y) { Console.WriteLine($"{item.Country}, {item.City}, {item.Count}"); foreach (var c in item.Items) { Console.WriteLine($"\t{c.ContactName}"); } } } // Join using (NWContext db = new NWContext()) { var q8 = from c in db.Customers join o in db.Orders on c.CustomerId equals o.CustomerId select new { c, o }; // new { c.CustomerID, c.Country } //equals new { o.CustomerID, Country = o.ShipCountry } var q8x = db.Customers.Join( db.Orders, c => c.CustomerId, o => o.CustomerId, (c, o) => new { c, o }); Console.Clear(); foreach (var item in q8) { Console.WriteLine($"{item.c.CustomerId}, {item.o.OrderId}"); } // Join agrupado var q8y = from c in db.Customers join o in db.Orders on c.CustomerId equals o.CustomerId into CustomerOrders select new { c, Orders = CustomerOrders }; //select CustomerOrders; foreach (var ordenes in q8y) { //foreach (var orden in ordenes) //{ //} } // Left Ourter Join var q8z = from c in db.Customers join o in db.Orders on c.CustomerId equals o.CustomerId into CustomerOrders from detalle in CustomerOrders.DefaultIfEmpty() select new { Customer = c, Order = detalle }; foreach (var item in q8z) { if (item.Order == null) { Console.WriteLine($"Customer {item.Customer.CustomerId} with NO orders!"); } } } // Conjuntos using (NWContext db = new NWContext()) { var q9 = db.Customers. Select(c => c.Country).Distinct(); var q10 = db.Customers.Except( db.Customers.Where( c => c.Country == "Mexico")). Select(c => c.Country).Distinct(); Console.Clear(); foreach (var item in q10) { Console.WriteLine($"{item}"); } } // Partición (paginación) using (NWContext db = new NWContext()) { var q11 = db.Customers. OrderBy(c => c.CustomerId). Skip(10); // Tomar los primero 10 elementos var q12 = db.Customers. OrderBy(c => c.CustomerId). Take(10); // Segunda página de 10 elementos var q13 = db.Customers. OrderBy(c => c.CustomerId). Skip(10).Take(10); Console.Clear(); foreach (var item in q13) { Console.WriteLine($"{item.CustomerId}, {item.CompanyName}"); } } // Modificación de consulta using (NWContext db = new NWContext()) { var q14 = db.Customers. Where(c => c.Orders.Count > 5); Console.Clear(); Console.WriteLine(q14.Count()); q14 = q14.Where(c => c.Country == "Mexico"); Console.WriteLine(q14.Count()); q14 = q14.OrderByDescending(c => c.ContactName); foreach (var item in q14) { Console.WriteLine(item.ContactName); } } // Métodos útiles using (NWContext db = new NWContext()) { var o1 = db.Customers.First(); o1 = db.Customers.FirstOrDefault(); o1 = db.Customers.Where(c => c.CustomerId == "ALFKI") .Single(); o1 = db.Customers.Where(c => c.CustomerId == "ALFKI"). SingleOrDefault(); var o2 = db.Customers.All(c => c.Orders.Count > 5 && c.Country == "Mexico"); o2 = db.Customers. Any(c => c.Orders.Count > 5); var sum = db.OrderDetails. Sum(od => od.Quantity * od.UnitPrice); } }
public ActionResult SavePlant(Plant plant) { //设置电站的经纬度 string long1 = Request.Form["long1"]; string long2 = Request.Form["long2"]; string long3 = Request.Form["long3"]; double value = 0; double longValue = 0; double.TryParse(long1, out longValue); value += longValue; double.TryParse(long2, out longValue); value += (longValue / 60); double.TryParse(long3, out longValue); value += (longValue / 3600); plant.longitude = value; value = 0; longValue = 0; string lat1 = Request.Form["lat1"]; double.TryParse(lat1, out longValue); value += longValue; string lat2 = Request.Form["lat2"]; double.TryParse(lat2, out longValue); value += (longValue / 60); string lat3 = Request.Form["lat3"]; double.TryParse(lat3, out longValue); value += (longValue / 3600); plant.latitude = value; plant.longitudeString = string.Format("{0},{1},{2}", long1, long2, long3); plant.latitudeString = string.Format("{0},{1},{2}", lat1, lat2, lat3); if (string.IsNullOrEmpty(plant.pic)) { plant.pic = string.Empty; } //设置电站的创建者 plant.userID = UserUtil.getCurUser().id; CountryCity area = CountryCityService.GetInstance().GetCity(plant.country); plant.area = area == null ? string.Empty : area.weather_code; int plantid = plant.id; if (plant.id > 0) { PlantService.GetInstance().UpdatePlantInfo(plant); } else { plant.PaymentLimitDate = DateTime.Now.AddMonths(PaymentDelayMonth); plantid = PlantService.GetInstance().AddPlantInfo(plant); //添加电站时,向电站用户关系表中加记录,shared=false表示是自己创建的 PlantUserService.GetInstance().AddPlantUser(new PlantUser { plantID = plantid, userID = plant.userID, shared = false, roleId = Role.ROLE_SYSMANAGER }); } UserUtil.ResetLogin(UserUtil.getCurUser()); return(Redirect("/newregister/addplantcontrol?plantid=" + plantid)); }