Insert data into multiple tables-Dynamic Query

If you have multiple related tables and need to insert data on those tables. You can write stored procedures or the dynamic query. I am going to explain here the dynamic query.

First, insert the data into primary table and read the auto generated ID from that table and insert other data and that ID where it is foreign key. Each different query should be separated by semicolon(;).

To find ID of the row when data is inserted on a table, run a sql command " scope_identity()" right after insert the data.

Hence the Dynamic query is:

string sql = " DECLARE @ID int;@ID1 int; @ID3 int; @ID4 int;@ID5 int;";
sql = sql + "Insert into table1(Url,head,foot) values ";
sql = sql + "(@url,@head,@foot);";
sql = sql + " SET @ID = SCOPE_IDENTITY();";
sql = sql + " Insert into table2(table2_Name) values(@table2Name);";
sql = sql + " SET @ID1 = SCOPE_IDENTITY();";
sql = sql + " Insert into table3(tabl3_Name) values(@table3Name);";
sql = sql + " SET @ID2 = SCOPE_IDENTITY();";
sql = sql + " Insert into table2_table3(table1_ID,table2_ID) values (@ID1,@ID2);";
sql = sql + " SET @ID3 = SCOPE_IDENTITY();";
sql = sql + " INSERT INTO dbo.table1_table2(table1_ID,table2_table3_ID) VALUES (@ID,@ID3);";

SqlConnection conn = new SqlConnection(GetConnectionString());
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;

IDbDataParameter userParam = cmd.CreateParameter();
userParam.ParameterName = "@url";
userParam.Value = formReserve.URL;
userParam.DbType = System.Data.DbType.String;
cmd.Parameters.Add(userParam);
IDbDataParameter userParam = cmd.CreateParameter();

......

.....

.....

cmd.ExecuteNonQuery();

}