locked
I am not able to send API data to exist table by using table type param of store procedure. RRS feed

  • Question

  • User-1909855381 posted

    Hello Microsft team,

    the issue is after bind list data to datatable and when I pass to Table value parameter of a store procedure that SP is not executed. when tracing in the profiler.  

            public async System.Threading.Tasks.Task<string> ViewLeadSyncAPIConfigs()
            {
                try
                {
           
                    List<LeadItems> lstItems = new List<LeadItems>();
                    var Baseurl = "https://www.APIURL.com/";
                    using (var client = new HttpClient())
                    {
                        client.BaseAddress = new Uri(Baseurl);
                        client.DefaultRequestHeaders.Clear();
                        client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                        HttpResponseMessage Res = await client.GetAsync("url").ConfigureAwait(false);
                        if (Res.IsSuccessStatusCode)
                        {
                            var result = Res.Content.ReadAsStringAsync().Result;
                            lstItems = JsonConvert.DeserializeObject<List<LeadItems>>(result);
                        }
                    }
    
                    var tableData = ConvertToDataTable<LeadItems>(lstItems);
                   var dataResult =  AddFromTradeIndia(tableData);
    
                    return dataResult;
    
                }
                catch (Exception)
                {
    
                    throw;
                }
    
            }
    
       public DataTable ConvertToDataTable<T>(List<T> items)
            {
                DataTable dataTable = new DataTable(typeof(T).Name);
    
                //Get all the properties
                System.Reflection.PropertyInfo[] Props = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
                foreach (System.Reflection.PropertyInfo prop in Props)
                {
                    //Defining type of data column gives proper data table 
                    var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
                    //Setting column names as Property names
                    dataTable.Columns.Add(prop.Name, type);
                }
                foreach (T item in items)
                {
                    var values = new object[Props.Length];
                    for (int i = 0; i < Props.Length; i++)
                    {
                        //inserting property values to datatable rows
                        values[i] = Props[i].GetValue(item, null);
                    }
                    dataTable.Rows.Add(values);
                }
                //put a breakpoint here and check datatable
                return dataTable;
            }
      public string AddLeadFromTradeIndia(DataTable items)
            {
                try
                {
                    object[] obj = new object[]
                                            {"@ManageLeadType",items
                                            };
                    return _SqlHelper.ExecuteQuery(con, "InsertTradeIndiaData", obj);
                }
                catch (Exception)
                {
    
                    throw;
                }
            }

    Thank you ,

    Thursday, August 20, 2020 6:32 PM

All replies

  • User475983607 posted

    The code you shared above does not call the AddLeadFromTradeIndia() method which is way I assume you do not see the procedure run in the profiler.

    Thursday, August 20, 2020 8:37 PM
  • User1686398519 posted

    Hi santosh1502,

    I want to confirm a few points with you.

    1. What is the AddFromTradeIndia method? Is it the AddLeadFromTradeIndia method you gave? But their names seem to be different.
    2. Is _SqlHelper.ExecuteQuery(con, "InsertTradeIndiaData", obj) your custom method?If it is your custom method, you can give its code so that we can better help you.

    Best Regards,

    YihuiSun

    Friday, August 21, 2020 9:58 AM