locked
populate a datatable from a stored procedure thats in a foreach loop RRS feed

  • Question

  • User193749913 posted
    I'm wanting to loop thru my dictionary and call the stored procedure each time and then populate a repeater with the results. I'm not sure if
    what I have below is a start in the right direction or not. Thanks for any help.

    Dictionary<String, String> loadItems = new Dictionary<String, String>(); loadItems.Add("enty_menu", "qstring"); loadItems.Add("sys_maint", "qstring"); loadItems.Add("sys_reports", "qstring"); loadItems.Add("user_logoff", "qstring"); loadItems.Add("bf_pubforms", "qstring"); loadItems.Add("user_request", "qstring"); loadItems.Add("help", "qstring"); DataTable dtMenuItems = new DataTable(); Dictionary<string, string> myDict = new Dictionary<string, string>(); foreach (KeyValuePair<string, string> item in loadItems) { try { dtMenuItems = new general_system_functions().S_Pages_By_Key_Effective_Select(item.Key, DateTime.Now.ToShortDateString()); if (dtMenuItems.Rows.Count > 0) { } } catch (Exception ex) { sendErrors(ex.ToString(), "S_Pages_By_Key_Effective_Select"); } } rptMenu.DataSource = dtMenuItems; rptMenu.DataBind();

    Thursday, May 11, 2017 8:42 PM

Answers

  • User-1509636757 posted

    I suggest you keep one DataTable as aggregator for all result that you receive in form of temporary DataTable and finally bind Repeater with aggregator DataTable, Here is one example based on your code that you have posted:

    {
        Dictionary<String, String> loadItems = new Dictionary<String, String>();
        loadItems.Add("enty_menu", "qstring");
        loadItems.Add("sys_maint", "qstring");
        loadItems.Add("sys_reports", "qstring");
        loadItems.Add("user_logoff", "qstring");
        loadItems.Add("bf_pubforms", "qstring");
        loadItems.Add("user_request", "qstring");
        loadItems.Add("help", "qstring");
    
        DataTable dtMenuItems = new DataTable();
        Dictionary<string, string> myDict = new Dictionary<string, string>();
        foreach (KeyValuePair<string, string> item in loadItems)
        {
            try
            {
                DataTable tmpResult = new DataTable();
    
                if (dtMenuItems.Rows.Count == 0) //-- this is so that first time, stucture can be cloned to final DataTable, that will hold data for all dictionary entries
                    dtMenuItems = new general_system_functions().S_Pages_By_Key_Effective_Select(item.Key, DateTime.Now.ToShortDateString());
                else
                    tmpResult = new general_system_functions().S_Pages_By_Key_Effective_Select(item.Key, DateTime.Now.ToShortDateString());
    
                if (null != tmpResult && tmpResult.Rows.Count > 0)
                    tmpResult.AsEnumerable().ToList().ForEach(row => dtMenuItems.ImportRow(row));
            }
            catch (Exception ex)
            {
                sendErrors(ex.ToString(), "S_Pages_By_Key_Effective_Select");
            }
        }
    
        rptMenu.DataSource = dtMenuItems;
        rptMenu.DataBind();
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 13, 2017 6:32 PM

All replies

  • User-1509636757 posted

    I suggest you keep one DataTable as aggregator for all result that you receive in form of temporary DataTable and finally bind Repeater with aggregator DataTable, Here is one example based on your code that you have posted:

    {
        Dictionary<String, String> loadItems = new Dictionary<String, String>();
        loadItems.Add("enty_menu", "qstring");
        loadItems.Add("sys_maint", "qstring");
        loadItems.Add("sys_reports", "qstring");
        loadItems.Add("user_logoff", "qstring");
        loadItems.Add("bf_pubforms", "qstring");
        loadItems.Add("user_request", "qstring");
        loadItems.Add("help", "qstring");
    
        DataTable dtMenuItems = new DataTable();
        Dictionary<string, string> myDict = new Dictionary<string, string>();
        foreach (KeyValuePair<string, string> item in loadItems)
        {
            try
            {
                DataTable tmpResult = new DataTable();
    
                if (dtMenuItems.Rows.Count == 0) //-- this is so that first time, stucture can be cloned to final DataTable, that will hold data for all dictionary entries
                    dtMenuItems = new general_system_functions().S_Pages_By_Key_Effective_Select(item.Key, DateTime.Now.ToShortDateString());
                else
                    tmpResult = new general_system_functions().S_Pages_By_Key_Effective_Select(item.Key, DateTime.Now.ToShortDateString());
    
                if (null != tmpResult && tmpResult.Rows.Count > 0)
                    tmpResult.AsEnumerable().ToList().ForEach(row => dtMenuItems.ImportRow(row));
            }
            catch (Exception ex)
            {
                sendErrors(ex.ToString(), "S_Pages_By_Key_Effective_Select");
            }
        }
    
        rptMenu.DataSource = dtMenuItems;
        rptMenu.DataBind();
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 13, 2017 6:32 PM
  • User-707554951 posted

    Hi trev52,

    Based on your code and description, you want to loop thru your dictionary and call stored procedure each time.

    Then bind all result in each loop to a repeater.

    If that the case, I think that there is some logic problem in the code you provide.

    Your code only bind the last query result to repeater.

    I think that the logic in the following code may be helpful for you 

    //Commented code is a another solution 
    //  List<DataTable> dsMenuItems = new List<DataTable>();
    DataSet dsMenuItems = new DataSet(); Dictionary<string, string> myDict = new Dictionary<string, string>(); foreach (KeyValuePair<string, string> item in loadItems) { try { dsMenuItems.Tables.Add ( new general_system_functions().S_Pages_By_Key_Effective_Select(item.Key, DateTime.Now.ToShortDateString()));
    //dsMenuItems.Add(new general_system_functions().S_Pages_By_Key_Effective_Select(item.Key, DateTime.Now.ToShortDateString()));
    } catch (Exception ex) { sendErrors(ex.ToString(), "S_Pages_By_Key_Effective_Select"); } } //merge multiple table in dsMenuItems dataset to dtMenuItems table DataTable dtMenuItems = new DataTable(); if (dsMenuItems.Tables.Count > 0) { foreach(DataTable dt in dsMenuItems.Tables) { dtMenuItems.Merge(dt); } }
                //if (dsMenuItems.Count()>0)
                //{
                //    foreach(DataTable dt in dsMenuItems) {
                //        dtMenuItems.Merge(dt);
                //    }          
                //} rptMenu.DataSource = dsMenuItems; rptMenu.DataBind();

    Related links:

    https://www.google.com.sg/#q=merge+multiple+datatables+into+single+datatable+c%23&spf=1494817798498

    http://stackoverflow.com/questions/12178823/adding-a-datatable-in-a-dataset

    Best regards

    Cathy

    Monday, May 15, 2017 3:25 AM