//insert the section item into the section DB table
private void insertSectionIntoDB(SqlConnection con, ExcelFileLayout section) //assumes associated line is already in DB
{
SqlCommand read = new SqlCommand("SELECT * FROM Lines WHERE Name = @Name");
read.CommandType = CommandType.Text;
read.Connection = con;
read.Parameters.AddWithValue("@Name", section.Line);
SqlDataReader reader = read.ExecuteReader();
if (reader.Read())
{
//SqlTransaction transaction;
//transaction = con.BeginTransaction("SectionTransaction");
SqlCommand cmd = new SqlCommand("INSERT INTO Sections (Name, LineId) VALUES (@Name, @LineId)");
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
//cmd.Transaction = transaction;
cmd.Parameters.AddWithValue("@Name", section.Section);
int lineId = reader.GetInt32(0);
cmd.Parameters.AddWithValue("@LineId", lineId);
reader.Close();
cmd.ExecuteNonQuery();
//transaction.Commit();
}
else
{
Console.WriteLine("Associated line not found for section.");
}
}
//insert the line item into the line DB table
private void insertLineIntoDB(SqlConnection con, ExcelFileLayout line)
{
SqlCommand cmd = new SqlCommand("INSERT INTO Lines (Name) VALUES (@Name)");
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Name", line.Line);
cmd.ExecuteNonQuery();
}
//insert block item into the block DB table
private void insertBlockIntoDB(SqlConnection con, ExcelFileLayout block)
{
SqlCommand read = new SqlCommand("SELECT Sections.SectionId, Sections.Name AS SectionName, Lines.Name AS LineName FROM Sections JOIN Lines ON Sections.LineId = Lines.LineId WHERE Sections.Name = @SectionName AND Lines.Name = @LineName");
read.CommandType = CommandType.Text;
read.Connection = con;
read.Parameters.AddWithValue("@SectionName", block.Section);
read.Parameters.AddWithValue("@LineName", block.Line);
SqlDataReader reader = read.ExecuteReader();
if (reader.Read())
{
//SqlTransaction transaction;
//transaction = con.BeginTransaction("BlockTransaction");
SqlCommand cmd = new SqlCommand("INSERT INTO Blocks (BlockNumber, SectionId, Length, Grade, Elevation, CumulativeElevation, SpeedLimit, Infrastructure, SwitchBlock, ArrowDirection) VALUES (@BlockNumber, @SectionId, @Length, @Grade, @Elevation, @CumulativeElevation, @SpeedLimit, @Infrastructure, @SwitchBlock, @ArrowDirection)");
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
//cmd.Transaction = transaction;
cmd.Parameters.AddWithValue("@BlockNumber", block.BlockNumber);
int sectionId = reader.GetInt32(0);
cmd.Parameters.AddWithValue("@SectionId", sectionId);
cmd.Parameters.AddWithValue("@Length", block.BlockLength);
cmd.Parameters.AddWithValue("@Grade", block.BlockGrade);
cmd.Parameters.AddWithValue("@Elevation", block.Elevation);
cmd.Parameters.AddWithValue("@CumulativeElevation", block.CumulativeElevation);
cmd.Parameters.AddWithValue("@SpeedLimit", block.SpeedLimit);
cmd.Parameters.AddWithValue("@Infrastructure", block.Infrastructure);
cmd.Parameters.AddWithValue("@SwitchBlock", block.SwitchBlock);
cmd.Parameters.AddWithValue("@ArrowDirection", block.ArrowDirection);
reader.Close();
cmd.ExecuteNonQuery();
//transaction.Commit();
}
else
{
Console.WriteLine("Associated section not found for block.");
}
}
//loads the excel file into a data view grid
private List <ExcelFileLayout> loadDvgIntoList(DataGridView excelFileData, List <ExcelFileLayout> excelFileList)
{
foreach (DataGridViewRow row in excelFileData.Rows)
{
if (row.Cells[0].Value != null)
{
ExcelFileLayout currEntry = new ExcelFileLayout(
row.Cells[0].Value.ToString(),
row.Cells[1].Value.ToString(),
Convert.ToInt32(row.Cells[2].Value),
Convert.ToDouble(row.Cells[3].Value),
Convert.ToDouble(row.Cells[4].Value),
Convert.ToInt32(row.Cells[5].Value),
row.Cells[6].Value.ToString(),
Convert.ToDouble(row.Cells[8].Value),
Convert.ToDouble(row.Cells[9].Value),
row.Cells[10].Value.ToString(),
row.Cells[11].Value.ToString());
excelFileList.Add(currEntry);
}
}
return(excelFileList);
}
