Insert data into multiple tables in one query

Question

I'm currently learning ASP.NET, and I'm stuck with storing data on multiple tables. The database model is shown below:

What I want to achieve is when I add a new contact to the contact table to get the last insert ID and automatically insert data for Phone / Tag / Email tables (if there is some data for these tables). Is it possible to do this in one query or do I need to run a new query for each table?

Here is the model used in the controller:

 public partial class Contact
    {
        public Contact()
        {
            this.Emails1 = new HashSet<Email>();
            this.Phones1 = new HashSet<Phone>();
            this.Tags1 = new HashSet<Tag>();
        }

        public int id { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string address { get; set; }
        public string city { get; set; }
        public Nullable<byte> bookmarked { get; set; }
        public string notes { get; set; }

        public virtual ICollection<Email> Emails1 { get; set; }
        public virtual ICollection<Phone> Phones1 { get; set; }
        public virtual ICollection<Tag> Tags1 { get; set; }
    }
}

Here is a model for phone / tags / mail tables (it is the same with another name in one column)

public partial class Email
    {
        public int id { get; set; }
        public int id_contact { get; set; }
        public string email1 { get; set; }

        public virtual Contact Contact1 { get; set; }
    }

Here is a controller class that adds a new row to the database:

 public string AddContact(Contact contact)
        {
            if (contact != null)
            {

                db.Contacts.Add(contact);
                db.SaveChanges();
                    return "Contact Added";
            }
            else
            {
                return "Invalid Record";
            }
        }

Answer 1

"" , , db.SaveChanges() .

public string AddContact(Contact contact)
{
    if (contact != null)
    {
        db.Contacts.Add(contact);

        contact.Emails1.Add(new Email { Email1 = "[e-mail]"})
        contact.Emails1.Add(new Email { Email1 = "[e-mail]"})

        contact.Phones1.Add(new Phone1 { PhoneNumber = "1234516123"})

        db.SaveChanges();
        return "Contact Added";
    }
    else
    {
            return "Invalid Record";
    }
}

, , , .

Answer 2

, Entity:

//Create student in disconnected mode
Student newStudent = new Student() { StudentName = "New Single Student" };

//Assign new standard to student entity
newStudent.Standard = new Standard() { StandardName = "New Standard" };

//add new course with new teacher into student.courses
newStudent.Courses.Add(new Course() { CourseName = "New Course for single student", Teacher = new Teacher() { TeacherName = "New Teacher" } });

using (var context = new SchoolDBEntities())
{
    context.Students.Add(newStudent);
    context.SaveChanges();

    Console.WriteLine("New Student Entity has been added with new StudentId= " + newStudent.StudentID.ToString());
    Console.WriteLine("New Standard Entity has been added with new StandardId= " + newStudent.StandardId.ToString());
    Console.WriteLine("New Course Entity has been added with new CourseId= " + newStudent.Courses.ElementAt(0).CourseId.ToString());
    Console.WriteLine("New Teacher Entity has been added with new TeacherId= " + newStudent.Courses.ElementAt(0).TeacherId.ToString());
}

scope_identity(), .

, , . , DataContext.

Answer 3

:

CRUD ::

Entity Framework LINQ to Entities Entity SQL, , . . , // .

, , DbContext. , DDL context.SaveChanges().

• sp_InsertStudentInfo
• sp_UpdateStudent

• sp_DeleteStudent .

  CREATE PROCEDURE [dbo].[sp_InsertStudentInfo]
      -- Add the parameters for the stored procedure here
      @StandardId int = null,
      @StudentName varchar
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
       INSERT INTO [SchoolDB].[dbo].[Student]([StudentName],[StandardId])
       VALUES(@StudentName, @StandardId)
  
      SELECT SCOPE_IDENTITY() AS StudentId
  
  END
  
   CREATE PROCEDURE [dbo].[sp_UpdateStudent]
      -- Add the parameters for the stored procedure here
      @StudentId int,
      @StandardId int = null,
      @StudentName varchar
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      Update [SchoolDB].[dbo].[Student] 
      set StudentName = @StudentName,StandardId = @StandardId
      where StudentID = @StudentId;
  
  END
  
  CREATE PROCEDURE [dbo].[sp_DeleteStudent]
      -- Add the parameters for the stored procedure here
      @StudentId int
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      DELETE FROM [dbo].[Student]
      where StudentID = @StudentId
  
  END
  

, EDM , " " , "" .

,

, EDM, "" " ", :

,. , . sp_InsertStudentInfo Insert, :

sp_InsertStudentInfo StudentId. , Student Entitys StudentID, :

Insert, Update Delete, :

, , . "" "" , :

, , :

using (var context = new SchoolDBEntities())
{
    Student newStudent = new Student() { StudentName = "New Student using SP"};

    context.Students.Add(newStudent);
    //will execute sp_InsertStudentInfo 
    context.SaveChanges();

    newStudent.StudentName = "Edited student using SP";
    //will execute sp_UpdateStudent
    context.SaveChanges();

    context.Students.Remove(newStudent);
    //will execute sp_DeleteStudentInfo 
    context.SaveChanges();
}

SaveChanges():

exec [dbo].[sp_InsertStudentInfo] @StandardId=NULL,@StudentName='New Student using SP'
    go

    exec [dbo].[sp_UpdateStudent] @StudentId=47,@StandardId=NULL,@StudentName='Edited student using SP'
    go

    exec [dbo].[sp_DeleteStudent] @StudentId=47
    go

Note . After a context call to SaveChagnes, after adding a new student, it assigns the StudentID property to the StudentID property of the Student object, because sp_InsertStudentInfo returns StudentId. It is necessary to use this object of the object for further operation.

See here for more details. practically.