locked
C# - Multiple OleDbCommands producing System Resource Exceeded RRS feed

  • Question

  • So basically I have a C# project, that iterates through every student (300 students) within a table called Student within a Microsoft Access 2016 database. In a single iteration for a single student by using other tables like Mathematics, Reading that have a 1-to-1 relationship with the Student table, to grab the data that belongs to that student.

                    try
                {
                    OleDbCommand allStudents = new OleDbCommand("SELECT [NSN]"
                     + " FROM [Student]; ");
    
                    allStudents.Connection = conn;
                    OleDbDataAdapter allData = new OleDbDataAdapter(allStudents);
                    DataTable allTable = new DataTable();
                    allData.Fill(allTable);
    
                    foreach (DataRow dr in allTable.Rows)
                    {
                        string NSN = dr["NSN"].ToString();
    
                        OleDbCommand cmd = new OleDbCommand("SELECT * "
                            + "FROM (((([Student] s "
                            + "INNER JOIN [Student Extra] se ON se.[NSN] = s.[NSN]) "
                            + "INNER JOIN [Reading] r ON r.[NSN] = s.[NSN])"
                            + "INNER JOIN [Writing] w ON w.[NSN] = s.[NSN])"
                            + "INNER JOIN [Mathematics] m ON m.[NSN] = s.[NSN]) "
    
                            + "WHERE s.[NSN] = '" + NSN + "'; ");
                        cmd.Connection = conn;
                        OleDbDataAdapter daa = new OleDbDataAdapter(cmd);
                        DataTable dtt = new DataTable();
                        daa.Fill(dtt);
    
    
                        foreach (DataRow drr in dtt.Rows)
                        {
                            firstName = drr["Preferred Name"].ToString();
                            gender = drr["Gender"].ToString();
                            room = drr["Room Number"].ToString();
                            NSAchieve = drr["National Standard Achieve"].ToString();
                            NSProgress = drr["National Standard Progress"].ToString();

    The above code is only a snippet of the code I have, but this is basically where the function will start.

    By using this data, I want to be able to go through several SELECT statements for other tables and compare them and produce a calculated value.

    Dictionary<string, OleDbCommand> d = new Dictionary<string, OleDbCommand>(); 
    cmd = new OleDbCommand("SELECT [Achievement Statement]"
            + " FROM [National Standard Codes]"
            + " WHERE [National Standard Code] = '" + readingNSAchievementCode + "'; ");
            d["readingNSAchievementOTJ"] = cmd;
    
            cmd = new OleDbCommand("SELECT [" + NSAchieve + "]"
           + " FROM [Reading National Standards]"
           + " WHERE [Assessment] = '" + readingFinalAssessment + "'; ");
            d["readingNSAchievementComp"] = cmd;
    
            cmd = new OleDbCommand("SELECT [Timeframe]"
            + " FROM [Reading Statements]"
            + " WHERE [Year Code] = '" + NSProgress + "'; ");
            d["readingNSProgressTimeframe"] = cmd;

    There are several more commands, (approx <150). I use a Dictionary to store my Commands, and then execute the commands in a FOREACH loop.

    foreach(KeyValuePair<string, OleDbCommand> pair in d)
            {
                try
                {
                    string v = pair.Key;
                    OleDbCommand dbCmd = pair.Value;
    
    
                    dbCmd.Connection = conn;
                    OleDbDataReader reader = dbCmd.ExecuteReader();
                    reader.Read();
                    readingDict[v] = reader.GetString(0);
    
                }
                catch (Exception e)
                {
                    MessageBox.Show("Error at " + pair.Key + "\n\n Here is message " + e);
                }
            }

    After executing and getting my value, I want to store my data into another table called Calculated.

                string insert1 = "INSERT INTO [Calculated] (";
            int i = 0;
            Dictionary<string, string> dict = createDictionary(NSN);
            int len = dict.Count / 2;
            foreach (KeyValuePair<string, string> pair in dict)
            {
                string field = pair.Key;
                string value = pair.Value;
    
                if (i == (len - 1))
                {
                    insert1 += "[" + field + "])";
                    break;
                }
                else
                {
                    insert1 += "[" + field + "], ";
                }
    
                i++;
            }
            insert1 += " VALUES (";
            i = 0;
            foreach (KeyValuePair<string, string> pair in dict)
            {
                string field = pair.Key;
                string value = pair.Value;
    
                if (i == len - 1)
                {
                    insert1 += "'" +  value + "')";
                    break;
                }
                else
                {
                    insert1 += "'" + value + "', ";
                }
    
                i++;
            }

    I build my INSERT INTO query, and then I execute using an OleDbCommand. This needs to repeat 300 times, but for development purposes currently I only have 5 students in my Student table. However when executing after the 4th student it will always consistently give me an error System Resources Exceeded always at a specific OleDbCommand. I have tested each command separately, so there is no issue with the way the OleDbCommands are written.

    I have tried searching on here, and tried to encase the first code snippet in a using statement, using using (OleDbConnection conn = new OleDbConnection(connectionStr)) but as I am still a novice at C#, I am unable to produce a solution.

    Tuesday, September 20, 2016 9:13 PM

Answers

  • Hi PunkyMonkey,

    Firstly, I would suggest you could use in clause on your where condition, which could avoid many database operations. like this:

    DataTable dt = new DataTable();
                string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Data\Access\Demo.accdb; Persist Security Info=False;";
                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    var selectSql = @"SELECT [NSN] FROM [Students]";
                    using (OleDbCommand com = new OleDbCommand(selectSql, conn))
                    {
                        try
                        {
                            conn.Open();
                            OleDbDataAdapter adapter = new OleDbDataAdapter(com);
                            adapter.Fill(dt);
    
                            var listnsn = dt.AsEnumerable().Select(t => t.Field<string>("NSN")).ToList();
    
                            string NSN = string.Join("','", listnsn.ToArray());
                            NSN = string.Format("'{0}'", NSN);
    
                            OleDbCommand cmd = new OleDbCommand("SELECT * "
                            + "FROM (((([Student] s "
                            + "INNER JOIN [Student Extra] se ON se.[NSN] = s.[NSN]) "
                            + "INNER JOIN [Reading] r ON r.[NSN] = s.[NSN])"
                            + "INNER JOIN [Writing] w ON w.[NSN] = s.[NSN])"
                            + "INNER JOIN [Mathematics] m ON m.[NSN] = s.[NSN]) "
    
                            + "WHERE s.[NSN] in (" + NSN + "); ");
                            cmd.Connection = conn;
                            OleDbDataAdapter daa = new OleDbDataAdapter(cmd);
                            DataTable dtt = new DataTable();
                            daa.Fill(dtt);
    
    
                            foreach (DataRow drr in dtt.Rows)
                            {
                                firstName = drr["Preferred Name"].ToString();
                                gender = drr["Gender"].ToString();
                                room = drr["Room Number"].ToString();
                                NSAchieve = drr["National Standard Achieve"].ToString();
                                NSProgress = drr["National Standard Progress"].ToString();

    Secondly, I would suggest that you could use dictionary saving the result instead of oledbcommand object, like this:

    Dictionary d = new Dictionary<string, string>();
    string query= "SELECT [Achievement Statement]"
           
    + " FROM [National Standard Codes]"
           
    + " WHERE [National Standard Code] = '" + readingNSAchievementCode + "'; ");
            d
    ["readingNSAchievementOTJ"; using (SqlCeCommand cmd = new SqlCeCommand(sql, dbConn)) { using (var reader = cmd.ExecuteReader()) { reader.Read();
    d["readingNSAchievementOTJ"] = reader.GetString(0);
    } }

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 21, 2016 9:05 AM
  • Hi PunkyMonkey,

    I can't determine if your problem is with the SELECT with the JOINs or if it's with the INSERT ... what you've written is a little ambiguous.

    However, I see one problem with your SELECTs ... you are defining *and* creating new instances of OleDbCommand and DataTable inside a loop, which could definitely run into resource problems with a lot of iterations. You should always define such variables outside a loop, like this:

    OleDbCommand cmd;
    DataTable dtt;
    foreach (DataRow dr in allTable.Rows)
    {
        string NSN = dr["NSN"].ToString();
    
        cmd = new OleDbCommand("SELECT * "
            + "FROM (((([Student] s "
            + "INNER JOIN [Student Extra] se ON se.[NSN] = s.[NSN]) "
            + "INNER JOIN [Reading] r ON r.[NSN] = s.[NSN])"
            + "INNER JOIN [Writing] w ON w.[NSN] = s.[NSN])"
            + "INNER JOIN [Mathematics] m ON m.[NSN] = s.[NSN]) "
    
            + "WHERE s.[NSN] = '" + NSN + "'; ");
        cmd.Connection = conn;
        OleDbDataAdapter daa = new OleDbDataAdapter(cmd);
        dtt = new DataTable();
        daa.Fill(dtt);
     
        // rest of your code
    

    Not sure if this is where your issue is, but hopefully my suggestion might solve the problem (unless, of course, it had to do with the INSERT, but you haven't shown us the code where you're actually executing those INSERT commands).


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 24, 2016 4:38 PM

All replies

  • Hi PunkyMonkey,

    Try specifying the fields, instead of using * in the query

    Thanks

    Wednesday, September 21, 2016 1:29 AM
  • Hi PunkyMonkey,

    Since your problem is more related to ADO.NET Managed Providers. I moved it to ADO.NET Managed Providers Forum for support.

    The Visual C# forum discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools.

    If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.

    Thanks for your understanding and cooperation.

    Best Regards,

    Wendy


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 21, 2016 5:29 AM
  • Hi PunkyMonkey,

    Firstly, I would suggest you could use in clause on your where condition, which could avoid many database operations. like this:

    DataTable dt = new DataTable();
                string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Data\Access\Demo.accdb; Persist Security Info=False;";
                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    var selectSql = @"SELECT [NSN] FROM [Students]";
                    using (OleDbCommand com = new OleDbCommand(selectSql, conn))
                    {
                        try
                        {
                            conn.Open();
                            OleDbDataAdapter adapter = new OleDbDataAdapter(com);
                            adapter.Fill(dt);
    
                            var listnsn = dt.AsEnumerable().Select(t => t.Field<string>("NSN")).ToList();
    
                            string NSN = string.Join("','", listnsn.ToArray());
                            NSN = string.Format("'{0}'", NSN);
    
                            OleDbCommand cmd = new OleDbCommand("SELECT * "
                            + "FROM (((([Student] s "
                            + "INNER JOIN [Student Extra] se ON se.[NSN] = s.[NSN]) "
                            + "INNER JOIN [Reading] r ON r.[NSN] = s.[NSN])"
                            + "INNER JOIN [Writing] w ON w.[NSN] = s.[NSN])"
                            + "INNER JOIN [Mathematics] m ON m.[NSN] = s.[NSN]) "
    
                            + "WHERE s.[NSN] in (" + NSN + "); ");
                            cmd.Connection = conn;
                            OleDbDataAdapter daa = new OleDbDataAdapter(cmd);
                            DataTable dtt = new DataTable();
                            daa.Fill(dtt);
    
    
                            foreach (DataRow drr in dtt.Rows)
                            {
                                firstName = drr["Preferred Name"].ToString();
                                gender = drr["Gender"].ToString();
                                room = drr["Room Number"].ToString();
                                NSAchieve = drr["National Standard Achieve"].ToString();
                                NSProgress = drr["National Standard Progress"].ToString();

    Secondly, I would suggest that you could use dictionary saving the result instead of oledbcommand object, like this:

    Dictionary d = new Dictionary<string, string>();
    string query= "SELECT [Achievement Statement]"
           
    + " FROM [National Standard Codes]"
           
    + " WHERE [National Standard Code] = '" + readingNSAchievementCode + "'; ");
            d
    ["readingNSAchievementOTJ"; using (SqlCeCommand cmd = new SqlCeCommand(sql, dbConn)) { using (var reader = cmd.ExecuteReader()) { reader.Read();
    d["readingNSAchievementOTJ"] = reader.GetString(0);
    } }

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 21, 2016 9:05 AM
  • Hi PunkyMonkey,

    I can't determine if your problem is with the SELECT with the JOINs or if it's with the INSERT ... what you've written is a little ambiguous.

    However, I see one problem with your SELECTs ... you are defining *and* creating new instances of OleDbCommand and DataTable inside a loop, which could definitely run into resource problems with a lot of iterations. You should always define such variables outside a loop, like this:

    OleDbCommand cmd;
    DataTable dtt;
    foreach (DataRow dr in allTable.Rows)
    {
        string NSN = dr["NSN"].ToString();
    
        cmd = new OleDbCommand("SELECT * "
            + "FROM (((([Student] s "
            + "INNER JOIN [Student Extra] se ON se.[NSN] = s.[NSN]) "
            + "INNER JOIN [Reading] r ON r.[NSN] = s.[NSN])"
            + "INNER JOIN [Writing] w ON w.[NSN] = s.[NSN])"
            + "INNER JOIN [Mathematics] m ON m.[NSN] = s.[NSN]) "
    
            + "WHERE s.[NSN] = '" + NSN + "'; ");
        cmd.Connection = conn;
        OleDbDataAdapter daa = new OleDbDataAdapter(cmd);
        dtt = new DataTable();
        daa.Fill(dtt);
     
        // rest of your code
    

    Not sure if this is where your issue is, but hopefully my suggestion might solve the problem (unless, of course, it had to do with the INSERT, but you haven't shown us the code where you're actually executing those INSERT commands).


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 24, 2016 4:38 PM