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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy