Marius Vasile

Marius Vasile

  • 552
  • 1.9k
  • 145.7k

asp.net c# sql server cascade copy

Nov 23 2023 6:08 PM

I have three tables tblActivitate(AID) tblEvaris(EID) and tblMasura (MID) connected in this order one-to-many. For some data in tblActivitate, all existing data in other tables may match so instead of writing again I want to be able to cascade copy in second and third table. Kind of, copy from old MID to new MID, new EID as FK. From tblActivitate to tblEvaris I do it with

using (SqlConnection conn = new SqlConnection(connString))
{
    string sqlQuery = "INSERT INTO tblEvaris (Componenta, FactorRisc, FormaManifestare, Gravitate, ClsGravitate, Probabilitate, ClsProbabilitate, Risc, AID) " +
      "SELECT Componenta, FactorRisc, FormaManifestare, Gravitate, ClsGravitate, Probabilitate, ClsProbabilitate, Risc, @AIDNew " +
      "FROM tblEvaris WHERE AID = @AID;SELECT SCOPE_IDENTITY();";
    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
    {
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddWithValue("@AID", ddlAN.SelectedItem.Value);
        cmd.Parameters.AddWithValue("@AIDNew", txtAD.Text);

        conn.Open();

        int result = Convert.ToInt32(cmd.ExecuteScalar());
        if (result > 0)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Record Inserted Successfully')", true);
        }

        conn.Close();

    }
}

and is working perfectly. Now, to write in the third table I tried with a stored procedure, easy to see mistakes in code

CREATE PROCEDURE [dbo].[AddDuplicate]

@AIDNew as int,
@AID as int

AS

DECLARE @EID int
BEGIN
	BEGIN
		INSERT INTO tblEvaris(Componenta,FactorRisc,FormaManifestare,Gravitate,ClsGravitate,Probabilitate,ClsProbabilitate,Risc,AID) 
		SELECT Componenta,FactorRisc,FormaManifestare,Gravitate,ClsGravitate,Probabilitate,ClsProbabilitate,Risc,@AIDNew 
		FROM tblEvaris WHERE AID = @AID
		SELECT @EID = SCOPE_IDENTITY();
	END
DECLARE @MapMasura TABLE(OldMID int, NewMID int);
	BEGIN
		MERGE tblMasura as tm USING (SELECT MID,Masura,GravitateF,ClsGravitateF,ProbabilitateF,ClsProbabilitateF,RiscF,@EID AS EID
		FROM tblMasura WHERE EID = @EID) AS S
		ON 1 = 0 WHEN NOT MATCHED THEN
		INSERT (Masura, GravitateF, ClsGravitateF, ProbabilitateF, ClsProbabilitateF, RiscF, EID) 
		VALUES (S.Masura,S.GravitateF,S.ClsGravitateF,S.ProbabilitateF,S.ClsProbabilitateF,S.RiscF,S.EID) OUTPUT inserted.MID INTO @MapMasura;
	END
END 

but is not working as intended. Any help, please?


Answers (2)