Hi, I have URL which returns Json, I want to read and insert

Question

Hi, I have URL which returns Json, I want to read and insert in sql table. please suggest me the possible way to insert in sql table.

Manas Mohapatra · Answer

First you have to convert the jsondata to .net custom object. Find below URL to implement: http://www.c-sharpcorner.com/article/json-serialization-and-deserialization-in-c-sharp/ After you got the object you can implement ADO.NET/EntityFramework to insert data in SQL SERVER.

Vishal Jadav · Answer

Hi, By mistake its posted twice. My bad. :)

Vishal Jadav · Answer

Hi, Sure i have one. I have an example that explains this easily. I cant explain it but its easy. Let me know if its not understandable. USER DEFINED TABLE TYPE ------------------------------------------------------------------------------- CREATE TYPE [dbo].[COMBOITEMTABLE] AS TABLE( [TABLEID] [int] NOT NULL, [ITEMID] [bigint] NULL, [GROUPID] [tinyint] NULL, [ISDEFAULT] [bit] NULL, [STATUS] [bit] NULL ) GO COMBO CLASS ----------------------------------------------- public class ComboItems { public Int64 ItemID { get; set; } public bool Status { get; set; } public bool IsDefault,IsVisibleInCombo; public ComboGroupIDs GroupID; public ComboItems() { } public ComboItems(Int64 _itmid, bool _stus, bool _IsDefault, ComboGroupIDs _GrpID) { this.ItemID = _itmid; this.Status = _stus; this.IsDefault = _IsDefault; this.GroupID = _GrpID; } } public enum ComboGroupIDs { One = 1, Two = 2, Three = 3, Four = 4, Five = 5 } PROCESS COMBO -------------------------- public Int64 ManageComboSubItems(List lstItems, Int64 ComboID) { Int64 k = 0; DataTable DtCombo = GetComboItemTable; int RowID = 1; foreach (Business.ComboItems item in lstItems) { DataRow NewRow = DtCombo.NewRow(); NewRow["TableID"] = RowID; NewRow["ItemID"] = item.ItemID; NewRow["Status"] = (item.Status == true ? "1" : "0"); NewRow["GroupID"] = item.GroupID.GetHashCode(); NewRow["IsDefault"] = (item.IsDefault == true ? "1" : "0"); DtCombo.Rows.Add(NewRow); DtCombo.AcceptChanges(); RowID += 1; } SqlParameter[] Param = new SqlParameter[2]; Param[0] = new SqlParameter("ComboID", ComboID); Param[1] = new SqlParameter("COMBOTABLE", SqlDbType.Structured); Param[1].Value = DtCombo; k = ExecuteProcedureReturnInteger("ADD_COMBOITEMS", Param).ValueFromSQL; return k; } DataTable GetComboItemTable { get { DataTable DT = new DataTable(); DT.Columns.AddRange(new DataColumn[] { new DataColumn("TableID",typeof(int)), new DataColumn("ItemID", typeof(Int64)), new DataColumn("groupid", typeof(int)), new DataColumn("Isdefault", typeof(string)), new DataColumn("Status", typeof(string)) }); return DT; } } Please let me know if you are not clear. Hope it helps. Thanks. :)

ketan borsdiya · Answer

Hi Nayeem, First you need to add Newtonsoft.Jason in reference. Now, Write below sentence, DataTable dtbl = Newtonsoft.Json.JsonConvert.DeserializeObject (result); result is your Json String. Now, add data from datatable to SQL.

Nayeem Mohammed · Answer

@Vishal jadav : Thanks for response. Actuall i need c# code for that. Thanks

Nayeem Mohammed · Answer

Thanks for the response, But i want to save the values present inside the json in to the table of sql server. please suggest me on this

Vishal Jadav · Answer

Hi, If your result is in a fixed pattern create a table with same schema returned from the result. Create a User defined table type in sql server with the same schema. From C# code get the result from the URL and de-serialize it then insert the values into a Datatable which again has the same schema as the userDefined table type resides in SQL server. Pass the whole table at once using stored procedure. In stored procedure you can insert those records by just using a single insert into [DESTINATION_TABLE] select columns from [@TABLE VARIABLE WITH USER DEFIEND DATA TYPE] That's it. If you need any example of it please let me know. Hope it helps. Thank you.

Nitin Sontakke · Answer

You can store it in a varchar(max) column, but it is not recommended because then you will not be able to query it. So another option is to extract the values from the json and store these values in table. However, if you insist on storing json in database, you should also consider using MongoDB as it natively stores json in database and you can even query it. Think about it.