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