Below is the file I want to insert into SQL, I have loaded into a c# object (MyObject) and I am using Dapper, C#. My main table is Person ,child table Test and Staging table contains ID,PersonID,TestID composite key

Question1 : I am unclear how to insert the tags into Person, as tags is a list within Person object.

Question2 : How do I fix the below code, as I am not clear how to loop through the entity Test within the for each loop. I was referencing Dapper many-to-many insert

{
    "_id": "1",
      "name": "Leiz Gab",
    "tags": [
      "tagA",
      "tagB",
      "tagC"
    ],
    "tests": [
      {
        "id": 0,
        "name": "A bsd"
      },
      {
        "id": 1,
        "name": "S Bsd"
      }
    ]
  }

My code:

class Program
    {
        static void Main(string[] args)
        {
            var cs = @"Server=DESKTOP-FSNG2D1;Database=msdb;Trusted_Connection=True;";
            using var con = new SqlConnection(cs);
            con.Open();

            List<MyObject> processList = new List<MyObject>();
            MyObject obj = new MyObject();           

            obj.A = "AB";
            obj.B = "CD";

            Test tst = new Test();
            tst.Firstname = "A";
            tst.Surname = "Avd";
            Test tst1 = new Test();
            tst1.Firstname = "B";
            tst1.Surname = "Brgu";

            obj.D.Add(tst);
            obj.D.Add(tst1);

            string pINSERT = "INSERT INTO [msdb].[dbo].[AzureBlob] VALUES(@A, @B, @C,@D)";

            string tINSERT = "INSERT INTO [msdb].[dbo].[Test] VALUES(@Surname, @Firstname)";

            string aINSERT = "INSERT INTO [msdb].[dbo].[Staging] (PersonID, TestID) VALUES(@PersonID, @TestID)";

            var res = con.ExecuteScalarAsync(pINSERT);

            foreach (var a in obj.D)
            {
                var testID = con.ExecuteScalarAsync<int>(tINSERT, a);

                var arows =  con.ExecuteAsync(aINSERT, new { PersonID = res, TestID = testID });
            }

        }
    }

    public class MyObject
    {
        public string A { get; set; }
        public string B { get; set; }
        public List<string> C { get; set; }
        public List<Test> D new List<Test>();

    }

    public class Test
    {
        public int ID { get; set; }

        public string Surname { get; set; }
        public string Firstname { get; set; }

    }

    public class Staging
    {
        public int ID { get; set; }
        public int PersonID { get; set; }
        public int TestID { get; set; }

    }

CodePudding user response：

In your MyObject set the list property to as below. You need to initialize you list before using it. public List D { get; set; } = new List();

CodePudding user response：

If you make your second query:

INSERT INTO [msdb].[dbo].[Test] VALUES(@Surname, @Firstname);
SELECT SCOPE_IDENTITY()

This should mean that your code will received the generated id, and work from there