CODEWITHSUNDEEP
c#sql-server
julien27ph
julien27ph

Reputation: 13

How do I insert a record into multiple tables in a database

I'm self studying in C# and SQL Server 2005

I have 3 tables in SQL Server 2005 and I have a form in C# that accept inputs to be saved in the database.

My 3 tables are

and a junction table where all three tables meet

I set the relationship that Hotel No, Guest No, Room No are foreign key to Booking table

In my C# form, I have a code that fills the combo box for Hotel Name and Room Type

private void FillComboBoxHotelName()
{
   conn.Open();
   SqlCommand cmd1 = new SqlCommand("SELECT * FROM Hotel", conn);
   SqlDataAdapter da = new SqlDataAdapter(cmd1);
   da.SelectCommand.CommandText = cmd1.CommandText.ToString();
   DataTable dt = new DataTable();
   da.Fill(dt);
   cmbHotelName.DataSource = dt;
   cmbHotelName.DisplayMember = "Hotel Name";
   cmbHotelName.ValueMember = "HotelNo";
   conn.Close();
}

private void FillComboBoxRoomType()
{
   conn.Open();
   SqlCommand cmd = new SqlCommand("SELECT * FROM Room", conn);
   SqlDataAdapter da = new SqlDataAdapter(cmd);
   da.SelectCommand.CommandText = cmd.CommandText.ToString();
   DataTable dt = new DataTable();
   da.Fill(dt);
   cmbRoomType.DataSource = dt;
   cmbRoomType.DisplayMember = "Room Type";
   cmbRoomType.ValueMember = "RoomNo";
   conn.Close();
}

This code works and the records in my tables in Hotel and in Room fills the combo box in my form, my problem now is how do I store the input values in the Form to my database. Do I need to create a separate Insert query command for each table since the data will go to 3 tables, I am at lost.

My form looks like this:

Hotel Name : Combo Box
Guest Name: 
Guest Address:
Date From:
Date To:
Room Type:

Button (Save)

Please help I still cant post an image.

Upvotes: 1

Views: 2506

Answers (2)

Dnyanesh
Dnyanesh

Reputation: 2343

There are two approaches to achieve it Insert Statement and Stored Procedures.

Insert

 string sql = "INSERT INTO Hotel (Hotel No, Hotel Name, Location) values (@HotelNo,@HotelName,@Location)";
 conn.Open();
 SqlCommand cmd = new SqlCommand(sql, conn);
 cmd.Parameters.Add("@HotelNo", SqlDbType.Int);
 cmd.Parameters.Add("@HotelName", SqlDbType.VarChar);
 cmd.Parameters.Add("@Location", SqlDbType.VarChar); 

 cmd.Parameters["@HotelNo"].Value = HotelNo; // Hotel number name in Int
 cmd.Parameters["@HotelName"].Value = HotelName;//Hotel Name in string  
 cmd.Parameters["@Location"].Value = Location;//Location in form of text

 cmd.ExecuteNonQuery();

Note: Here in code I have made some assumptions that Hotel No is Int, Hotel Name is VarChar and Location is VarChar.

In similar way you can create Insert statement for rest of 2 tables and Insert code into 2 tables.

Stored procedure

Create stored procedure to all columns you want to insert into database. Like

CREATE PROCEDURE yourInsertOperation 
  -- Add the parameters for the stored procedure here
  @HotelNo INT, 
  @HotelName VARCHAR(20),
  @Location VARCHAR(20),
--......rest of all your parameter list goes here

AS
  BEGIN
     INSERT INTO resourceTable(column1, column2) VALUES (@HotelNo, @HotelName, @Location )
   --Other 2 insert statements will go here
  End
  Go

Once your stored procedure is in place in your Data Base. You can call that stored procedure along with parameters from C# code.

 SqlCommand cmd = new SqlCommand("yourInsertOperation",con);
 cmd.CommandType = CommandType.StoredProcedure;

 cmd.Parameters.Add("@HotelNo", SqlDbType.Int);
 cmd.Parameters.Add("@HotelName", SqlDbType.VarChar);
 cmd.Parameters.Add("@Location", SqlDbType.VarChar); 
 //Other columns to insert will go here 

 cmd.Parameters["@HotelNo"].Value = HotelNo; // Hotel number name in Int
 cmd.Parameters["@HotelName"].Value = HotelName;//Hotel Name in string  
 cmd.Parameters["@Location"].Value = Location;//Location in form of text
 //Values for columns will go here     

 cmd.ExecuteNonQuery();

To summarize I would suggest you to go with Insert query as it will easy and quick approach to insert into database. And in this you don't have to put efforts in creating SP. But still you have choice to use any or both approaches as your doing it for learning purpose.

Upvotes: 1

All Blond
All Blond

Reputation: 822

There is multiple way to solve your question:

  1. Create trigger on insert in table Booking which will populate rest of the related tables

  2. Put insert SQL in your C# for each involved tables, not the best way to do this but it can be done.

  3. Build store procedure to do insert in all 4 tables sequentially. Call this SP from your C# code.

I would prefer to use either 3 or 1 options in order of preference.

Upvotes: 0

Related Questions