insert existing json in db

Question

User932259438 posted

Hi,

My code:

var obj = new
        {
            data = new
        {
            post_info = new
            {
                ID = "1",
                UserId = HttpContext.Current.Request.Cookies["UserId"],
                date = DateTime.Now,
                imgpath = "none",
                videopath = "none",
                likecount = "1",
                sharecount = "1",
                likeperson = new { ID= "likeperson1" },
            },



        }

        };
             var post = JsonConvert.SerializeObject(obj);


             string conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
             using (SqlConnection con = new SqlConnection(conStr))
             {
                 using (SqlCommand cmd = new SqlCommand("INSERT INTO tbl_Posts VALUES(@Post)"))
                 {
                     cmd.CommandType = CommandType.Text;
                     cmd.Parameters.AddWithValue("@Post", post);
                     cmd.Connection = con;
                     con.Open();
                     cmd.ExecuteNonQuery();
                     con.Close();
                 }
             }

This code is on Button1 and insert correct.

In Button2 I need add another likeperson in db.  I need to find ID=1 and insert new likeperson:
likeperson = new { ID= "likeperson1" },
likeperson = new { ID= "likeperson2" }, - its new data-

Please help.

Answer 1

User475983607 posted

The table is not normalized as it needs a primary key.  Use the primary key to find the record.  This will somewhat improve performance.  But as designed, the entire table must be read and each JSON object deserialized to find a specific object within the table.  This is not a optimal design.

A better solution is crafting a table where the column have the same name as the JSON properties.