populate a datatable from a stored procedure thats in a foreach loop [Answered]RSS

2 replies

Last post May 15, 2017 03:25 AM by Cathy Zou

‹ Previous Thread|Next Thread ›

Shortcuts

trev52

Member

33 Points

161 Posts

populate a datatable from a stored procedure thats in a foreach loop

May 11, 2017 08:42 PM|trev52|LINK

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();

kaushalparik...

All-Star

31342 Points

7050 Posts

Re: populate a datatable from a stored procedure thats in a foreach loop

May 13, 2017 06:32 PM|kaushalparik27|LINK

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();
}

नमस्ते,
[KaushaL] Blog Twitter [MS MVP 2008 & 2009] [MCC 2011] [MVP Reconnect 2017]
Don't forget to click "Mark as Answer" on the post that helped you

Cathy Zou

Star

8650 Points

2882 Posts

Re: populate a datatable from a stored procedure thats in a foreach loop

May 15, 2017 03:25 AM|Cathy Zou|LINK

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();