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 IsDefaultIsVisibleInCombo; 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(TableIDtypeof(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.

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