public Straat MaakStraat(int straatId)
{
Straat straat = null;
SqlConnection connection = getConnection();
SqlConnection connection2 = getConnection(); //om meerdere readers tegelijk te kunnen uitvoeren!
string queryStraat = "Select * FROM dbo.straat WHERE id=@straatId";
string querySegment = "SELECT * FROM dbo.segment WHERE straatId=@straatId";
string queryBeginknoop = "SELECT * FROM dbo.knoop WHERE id=@beginknoopId";
string queryEindknoop = "SELECT * FROM dbo.knoop WHERE id=@eindknoopId";
string queryPunt = "SELECT * FROM dbo.punt WHERE segmentId=@segmentId";
using (SqlCommand command1 = connection.CreateCommand())
using (SqlCommand command2 = connection.CreateCommand())
using (SqlCommand command3 = connection2.CreateCommand())
using (SqlCommand command4 = connection2.CreateCommand())
using (SqlCommand command5 = connection2.CreateCommand())
{
connection.Open();
connection2.Open();
SqlTransaction transaction = connection.BeginTransaction();
command1.Transaction = transaction;
command2.Transaction = transaction;
//
SqlTransaction transaction2 = connection2.BeginTransaction();
command3.Transaction = transaction2;
command4.Transaction = transaction2;
command5.Transaction = transaction2;
try
{
//Straat maken
command1.Parameters.Add(new SqlParameter("@straatId", SqlDbType.Int));
command1.CommandText = queryStraat;
command1.Parameters["@straatId"].Value = straatId;
SqlDataReader dataReader = command1.ExecuteReader();
dataReader.Read();
string straatnaam = (string)dataReader["straatnaam"];
string gemeente = (string)dataReader["gemeente"];
string provincie = (string)dataReader["provincie"];
int graafId = (int)dataReader["graafId"];
float lengte = (float)dataReader["lengte"];
dataReader.Close();
//Segmenten
List <Segment> segmenten = new List <Segment>();
command2.Parameters.Add(new SqlParameter("@straatId", SqlDbType.Int));
command2.CommandText = querySegment;
command2.Parameters["@straatId"].Value = straatId;
dataReader = command2.ExecuteReader();
while (dataReader.Read())
{
int segmentId = (int)dataReader["id"];
int beginknoopId = (int)dataReader["beginknoop"];
int eindknoopId = (int)dataReader["eindknoop"];
//beginknoop
command3.Parameters.Add(new SqlParameter("@beginknoopId", SqlDbType.Int));
command3.CommandText = queryBeginknoop;
command3.Parameters["@beginknoopId"].Value = beginknoopId;
SqlDataReader reader = command3.ExecuteReader();
reader.Read();
string bx = (string)reader["x"];
string by = (string)reader["y"];
Punt beginknooppunt = new Punt(double.Parse(bx), double.Parse(by));
Knoop beginknoop = new Knoop(beginknoopId, beginknooppunt);
reader.Close();
command3.Parameters.Clear();
//eindknoop
command4.Parameters.Add(new SqlParameter("@eindknoopId", SqlDbType.Int));
command4.CommandText = queryEindknoop;
command4.Parameters["@eindknoopId"].Value = eindknoopId;
reader = command4.ExecuteReader();
reader.Read();
string ex = (string)reader["x"];
string ey = (string)reader["y"];
Punt eindknooppunt = new Punt(double.Parse(ex), double.Parse(ey));
Knoop eindknoop = new Knoop(eindknoopId, eindknooppunt);
reader.Close();
command4.Parameters.Clear();
//punten
List <Punt> punten = new List <Punt>();
command5.Parameters.Add(new SqlParameter("@segmentId", SqlDbType.Int));
command5.CommandText = queryPunt;
command5.Parameters["@segmentId"].Value = segmentId;
reader = command5.ExecuteReader();
while (reader.Read())
{
Punt punt = new Punt(double.Parse((string)reader["x"]), double.Parse((string)reader["y"]));
punten.Add(punt);
}
reader.Close();
command5.Parameters.Clear();
Segment segment = new Segment(segmentId, beginknoop, eindknoop, punten);
segmenten.Add(segment);
}
dataReader.Close();
Graaf graaf = new Graaf(segmenten, graafId);
straat = new Straat(straatId, straatnaam, graaf, provincie, gemeente, lengte);
return(straat);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
return(straat);
}
public Knoop(int knoopID, Punt punt)
{
Punt = punt;
KnoopID = knoopID;
}