How to look up values from database using multiple tables
I have a web application where the user can view certain test data. I have .cs page where it looks up the values in the sql database from table 1.
public class Test_Module : TestData
{
internal string m_fComponent1;
public string Component1 { get { return m_fComponent1; } set { m_fComponent1 = value; } }
internal string m_fComponent2;
public string Component2 { get { return m_fComponent2; } set { m_fComponent2 = value; } }
internal string m_fComponent3;
public string Component3 { get { return m_fComponent3; } set { m_fComponent3 = value; } }
internal double m_sParameter1;
public double Parameter1 { get { return m_sParameter1; } set { m_sParameter1 = value; } }
internal double m_sParameter2;
public double Parameter2 { get { return m_sParameter2; } set { m_sParameter2 = value; } }
internal double m_sParameter3;
public double Parameter3 { get { return m_sParameter3; } set { m_sParameter3 = value; } }
}
From the TestId it finds the data related to that Item
public static List<Test_Module> getTestData(int nTestId)
{
SqlCommand cmd = new SqlCommand("SELECT Item.Test_Id AS TestId, Test_Number, "
+ "Component1, Component2, Component3, Parameter1,Parameter2,Parameter3 FROM Items INNER JOIN Test_Module ON "
+ "Items.Test_Number = Test_Module.Item_Name "
+ "WHERE (Test_ID = @Item)");
cmd.Parameters.Add("@Item", SqlDbType.Int).Value = nTestID;
DataTable dt = InternalSalesDB.Instance.query(cmd).Tables[0];
List<TestEE_Module> lstData = new List<TestEE_Module>();
foreach (DataRow dr in dt.Rows)
{
TestEE_Module oData = new TestEE_Module();
fillTest_Module(oData, dr);
oData.Item = (string)dr["Test_Number"];
lstData.Add(oData);
}
return lstData;
}
internal static void fillTest_Module(Test_Module oData, DataRow dr)
{
oData.m_fComponent1 = (string)dr["Component1"];
oData.m_fComponent2 = (string)dr["Component2"];
oData.m_fComponent3 = (string)dr["Component3"];
oData.m_fParameter1 = (string)dr["Parameter1"];
oData.m_fParameter2 = (string)dr["Parameter2"];
oData.m_fParameter3 = (string)dr["Parameter3"];
}
The part I'm stuck on is that once it gets the data from Test_Module, I need to use that value to find Item_Number again from Items table. But it's for the Components that I need to look up the value again in the database but not for the Parameters.
For example: Component 1 returns 1284567899 so I need to search that value in the database. But, I have to separate that value using - so in the database; I would search for 1284-567-899 in Items table so how do I write the command so it splits up the values from Component 1,2 and 3? It should split the values after reading the first (4 characters) - (3 characters) - (3 character)?
Lastly, it has to write those vales to the webpage of .aspx in Gridview
<asp:GridView ID="TestModule" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4"
DataSourceID="dsrcGetTestData" Font-Size="0.65em" ForeColor="Black" GridLines="Vertical" DataKeyNames="TestID">
<FooterStyle BackColor="#CCCC99" />
<Columns>
<asp:CommandField ShowDeleteButton="True" />
<asp:BoundField DataField="Item" HeaderText="Module" SortExpression="Item"> <ItemStyle Wrap="False" /> </asp:BoundField>
<asp:BoundField DataField="Component1" SortExpression="Component1" />
<asp:BoundField DataField="Component2" SortExpression="Component2" />
<asp:BoundField DataField="Component3" SortExpression="Component3" />
<asp:BoundField DataField="Parameter1" SortExpression="Parameter1" />
<asp:BoundField DataField="Parameter2" SortExpression="Parameter2" />
<asp:BoundField DataField="Parameter3" SortExpression="Parameter3" />
</Columns>
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>