I'm trying to build a crud application with visual studio 2022 and SQL server using ASP.Net core web application (framework) and C#.

-I need to create 2 tables, one for persons and the other for addresses. -To Link both tables. -Each person must have many addresses and a unique person ID so when i insert I can insert the same person once with many addresses but his ID is unique for him only.

Please I need help. Thank you!

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
    public partial class PersonsData : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        SqlConnection sqlcon1 = new SqlConnection("Data Source=(LocalDB)\\MSSQLLOCALDB;Initial Catalog=personsDB;Integrated Security=True");
        protected void Button3_Click(object sender, EventArgs e)
        {

            sqlcon1.Open();

            SqlCommand commToCheckpID = new SqlCommand("SELECT pID, pname FROM addresses, persons WHERE addresses.pID = persons.pname");
            SqlDataAdapter sd = new SqlDataAdapter(commToCheckpID);
            DataTable dt = new DataTable();
            if (TextBox1.Text == TextBox3.Text)
            {

                ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('ID Already Exist');", true);
            }
            else
            {
                String query1 = "insert into addresses (address, pID) VALUES ('"   TextBox2.Text   "', '"   TextBox3.Text   "')";
                SqlCommand cmd1 = new SqlCommand();
                cmd1.CommandText = query1;
                cmd1.Connection = sqlcon1;
                cmd1.ExecuteNonQuery();


                String query = "insert into persons (pname) VALUES ('"   TextBox1.Text   "')";
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = query;
                cmd.Connection = sqlcon1;
                cmd.ExecuteNonQuery();

                ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Data has been inserted successfully');", true);
                TextBox1.Text = "";
                TextBox2.Text = "";
                TextBox3.Text = "";

                sqlcon1.Close();
            }

        }
    }
}

CodePudding user response：

Your tables should look similar to the following:

Persons:

• ID (primary key)
• Name

Addresses:

• ID (primary key)
• PersonID (this should reference the ID field on Persons)
• Address

Use a SQL statement similar to this to retrieve all the addresses for a person:

SELECT Address FROM Addresses WHERE Addresses.PersonID = (Person's ID).

Use a SQL statement similar to this to add an address:

INSERT INTO Addresses (PersonID, Address) VALUES (Person's ID, Person's Address)

CodePudding user response：

You need to create a table with persons and one with addresses and a thrird table with relations, so person ID 1 has Address ID 1 and address ID 2 and address ID 23.

Afterwards, you need a join statement for retrieving all address of a giver person

A hacky approach would be to have an array of addresses IDs as a field on the person, but I'd advise for the 3 tables solution.

persons table
1  John
2  Maria

addresses table
   1 street fake
   2 street superfake
   3 real street
   4 dark alley

 relations (person ID, address ID)
    1 1
    1 2
    1 23