0

I'm trying to insert data into a SQL Server database using a console application. Data to be inserted is in a json object (it is the web api post method request body). Can anyone please tell me how to use json object to insert into a SQL Server database?

Here is the code:

namespace CreateEntityConsole
{
    class Entity
    {
        string domain = "DESKTOP-I4VK2LV";
        string userName = "AP-502";
        string password = "pass";
        string appID = "bbb";
        string locale = "en-US";
        string contenttype = string.Empty;

        // Create ENTITY
        public string CreateEntity()
        {
            string URI = "http://localhost:13490/agilepointserver/extension/createentity";

            string JsonRequestData = "{\"EntityName\":[\"AccountContact\":[\"PropertiesJSON\":[\"AName\": \"chaitratest2\",\"region\": \"India\"]]]}";

            HttpOperations ops = new HttpOperations(domain, this.userName, password, appID, locale);
            // HttpOperations ops = new HttpOperations(this.userName, password, appID, locale);

            return ops.InsertEntity(URI, JsonRequestData);
        }

        public void InsertIntoDB(string JsonRequestData)
        {
            using (SqlConnection sqlCon = new SqlConnection())
            {
                sqlCon.ConnectionString = "server=DESKTOP-I4VK2LV;Integrated Security=True;database=Entity";
                sqlCon.Open();
            }
        }
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mounika
  • 21
  • 1
  • 6
  • You have to create a layer in your application that has to properly insert each element in each correct table, check if it exists and has to be created / updated / deleted (could become complicated if this is an element that *contains* other element, in pure JSON or C# Object it's easy to do, but for RDBMS each sub-element has it's own table...). – Pac0 Feb 02 '18 at 11:18
  • That's why developpers use an ORM (Object relational manager), that usually handles this job of doing the bridge between your code and the DB model, and generate translate it to actual queries. It's quite a gap to learn this. For example, you can have a look at EntityFramework, or NHibernate – Pac0 Feb 02 '18 at 11:19
  • If you just have to do *simple* inserts in very specific scenarios, you *might* get away by doing the bridge yourself, but this will quickly get out of humanly/efficiently manageable, that's why I strongly recommend that you learn the *proper and scalable way to do it* by learning about how to use an ORM. – Pac0 Feb 02 '18 at 11:21
  • related : https://stackoverflow.com/questions/1279613/what-is-an-orm-and-where-can-i-learn-more-about-it – Pac0 Feb 02 '18 at 11:26
  • What do you want to save exactly ? In which table ? What is the relevant schema ? – Pac0 Feb 02 '18 at 13:50

1 Answers1

-2

Use Entity framework to save json objects instead of using sqlConnection Your can than save this to a new Ef Dbcontext(), in your case consider to deserializing json string into simple poco objects take a look at Deserializing JSON data to C# using JSON.NET 

namespace CreateEntityConsole
{
   public class Entity
   {
       private DbContext context;

       public Entity()
       {

          context = new DbContext();
       }

       public void InsertIntoDB(Object JsonRequestData)
       {  
          context.Entity.Add(JsonRequestData);
          context.SaveChanges();
       }

        //Other CRUD stuff
  }

}

Note that code first method is used, separating your the model from data access code is good practice 

public class Entity {

    string domain = "DESKTOP-I4VK2LV";
    string userName = "AP-502";
    string password = "pass";
}

public class DataAccessLayer{

   DbContext context=new DbContext();

   public void InsertIntoDB(Object JsonRequestData)
   {  
      //Save json object to Entity poco
      context.Entity.Add(JsonRequestData);
      context.SaveChanges();
   }

        //Other CRUD stuff

}
nyulan
  • 311
  • 3
  • 12