May 19, 2009 at 2:17 pm
Hello,
I'm new to .net programming, in which I'm attempting to code a hardware inventory web app. I'm using Linq to SQL Classes.
I have three SQL 2005 tables.
* tblEmployee
* tblDesktop
* tblHardware
In each table there's a tblID master key (auto increment). Created master/foreign keys (TblEmployee's tblID is the master key and tblDesktop, tblHardware are foreign keys).
I'm utilizing the following code obtained from asp.net site:
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
System.Threading.Thread.Sleep(1000)
Dim db As New AssetsDBDataContext
Dim q = From b In db.tblEmployees _
Where b.LastName.Contains(txtSearch.Text.Trim()) _
And b.FirstName.Contains(txtSearch1.Text.Trim()) _
Select b
lv.DataSource = q
lv.DataBind()
End Sub
End Class
How it works: To see all hardware an employee is assigned with - Enter the employee's last and/or first name in the search boxes.
I successfully get results, but I would like to optimize the search/database.
Problem: I'm using tblID for the primary keys (auto increment) with relationships to the other tables.
I dont want to add duplicate hardware in the tables if they already exists.
Example: tblDesktop contains three columns - Manufacture, Model, and Serial.
Is there away to link the tblEmployee table to the hardware tables - yielding the desired search results?
Maybe: join tblEmployee, tblDesktop, tblLaptop to a new table (displaying all three data)?
May 21, 2009 at 1:40 pm
Did you checked with inner join on all 3?
May 22, 2009 at 12:53 pm
Create a view that has all the joins and fields you need then wire up your linq to that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply