private IEnumerable <IEnumerable <TurbineInfo> > GetTableNamesForTurbines(IEnumerable <TurbineInfo> turbinesIds)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
try
{
conn.Open();
using (SqlCommand command = new SqlCommand {
Connection = conn, CommandType = System.Data.CommandType.Text
})
{
List <List <TurbineInfo> > globalDT = new List <List <TurbineInfo> >();
foreach (TurbineInfo turbineIdRow in turbinesIds)
{
var partialTurbineTableName = "T_" + turbineIdRow.TurbineId;
command.CommandText = "SELECT TABLE_NAME " +
"FROM INFORMATION_SCHEMA.TABLES " +
"WHERE " +
"TABLE_TYPE = 'BASE TABLE' AND " +
"TABLE_CATALOG = 'WindMan' " +
"And TABLE_NAME like '%'+ @turbineTableName +'%'";
command.Parameters.Clear();
command.Parameters.AddWithValue("@turbineTableName", partialTurbineTableName);
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
List <TurbineInfo> turbineInfos = new List <TurbineInfo>();
foreach (DataRow item in dt.Rows)
{
TurbineInfo info = new TurbineInfo()
{
TurbineId = turbineIdRow.TurbineId, TableName = item["TABLE_NAME"].ToString()
};
turbineInfos.Add(info);
}
globalDT.Add(turbineInfos);
}
return(globalDT);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(new List <List <TurbineInfo> >());
}
}
}
private IEnumerable <TurbineInfo> GetTurbinesIds()
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
try
{
conn.Open();
using (SqlCommand command = new SqlCommand {
Connection = conn, CommandType = System.Data.CommandType.Text
})
{
command.CommandText = "Select park.ParkUnitSerialNumber, * from TParkUnit as park " +
"Join TUnitType as unitType on park.UnitTypeId = unitType.UnitTypeId " +
"Join TControllerUnit as controller on unitType.ControllerUnitId = controller.ControllerUnitId " +
"Where UnitTypeDevice = 'WTG' and UnitTypeManufacturer = 'Vestas Wind Systems'";
// "Where controller.ControllerUnitId = 28";
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
// var t = (from d in dt.AsEnumerable() select d.Field<int>("id"));
List <TurbineInfo> ids = new List <TurbineInfo>();
foreach (DataRow item in dt.Rows)
{
var turbineInfo = new TurbineInfo()
{
TurbineId = Convert.ToInt32(item["ParkUnitSerialNumber"])
};
ids.Add(turbineInfo);
}
return(ids.ToList());
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(new List <TurbineInfo>());
}
}
}
