Create an item in two different table with auto-increment C# SQL [closed]

Question

First of all, let me apologize because I know my question will be very confusing since my English is pretty bad.

I am working on a project for a company, mainly create a system to take care for their good, using MVVM C#

My MS SQL database looks like this

So I have auto-increment for both Bike-id and Contact-id. Each time I want to retrieve database from 3 tables, I have to make a join with Linq then get data using webservice, pretty complicated since I can use Lambda for toffee. It works but it messed up.

Furthermore, it is getting more complicated when I want to create bikes from a Contact, I dont want to tell people "hey each time you create a bike, you have to give me a ContactId". Im currently using .Max() or .First() to get a most recent ID, but then it is not flexible. How could I implement that when people click on a List of Contact, if they want to use a contact for creating a new bike, they can just click on a button, the system will know they have chosen that ContactId?

Also, when people create a new bike, I will let them go to the page with full of text box to fill out, of course there could be a new ContactId with a new items, but they are 2 different tables, how do I let it know that I am creating an Item for this ContactId.

Again, Im sorry to confuse you guys since my question it is very unpercisely. But please help me out of this.

Answer 1

I have a couple of comments that might help:

I have to make a join with Linq

This sounds expensive - are you really pulling all records from all 3 tables into your program just so you can do a join? It would be much better to do that directly in SQL:

SELECT b.brand
    ,c.NAME
    ,p.city
FROM bikes b
JOIN Contact c ON b.ownerID = c.ownerID
JOIN Postcode p ON c.postCode = p.postCode
WHERE c.ownerID = @ownerID

If I were to create a Bike record, I would definitely say "hey, you have to tell me the ownerID". Of course, in my program I would create a drop down list with the name of the owner shown to the user and the ownerID as the value returned.

If you were using HTML, it might look something like

<select>
    <option value='1'>Bob Smith</option>
    <option value='2'>John Doe</option>
</select>

One final note - it is a very common problem to want to create a record then immediately get the primary key. In Sql Server you can do that with code like:

int newContactID = -1;
string name = "bob";
string phoneNo = "555-1212";
string connString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();

    string sql = @"
INSERT INTO Contact (
    NAME
    ,phoneNo
    ,...
    )
VALUES (
    @name
    ,@phoneNo...
    );

SELECT SCOPE_IDENTITY()
";
    using (SqlCommand cmd = new SqlCommand(sql, conn))
    {
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.Add(new SqlParameter("@name", name));
        cmd.Parameters.Add(new SqlParameter("@phoneNo", phoneNo));

        newContactID = Int32.Parse((cmd.ExecuteScalar()).ToString());
    }
}