Writing Data to Access Database with foreach

Question

I have an Access Database which I want to write data. I am successful in doing so during my tests, however am trying to convert my code to run iteratively and efficiently as I intend to process up to ~10K queries at a time from an API response.

What I've tried

I was able to successfully write to my Access database with the following

            using (OleDbConnection myCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Lenovo\\Desktop\\Weather Cruncher\\wcMainDB.accdb"))
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandText = "Insert into stations_NOAA " +
                    "([stationID],[stationName],[stationMinDate],[stationMaxDate],[stationDataCoverage]," +
                    "[stationLatitude],[stationLongitude],[stationElevation],[stationElevationUnit])" +
                    "values (?,?,?,?,?,?,?,?,?)";
                cmd.Parameters.AddWithValue("@stationID", "1000"); // Short Text data type
                cmd.Parameters.AddWithValue("@stationName", "2000"); // Short Text data type
                cmd.Parameters.AddWithValue("@stationMinDate", "3000"); // Short Text data type
                cmd.Parameters.AddWithValue("@stationMaxDate", "4000"); // Short Text data type
                cmd.Parameters.AddWithValue("@stationDataCoverage", 5000); // Number data type
                cmd.Parameters.AddWithValue("@stationLatitude", 6000); // Number data type
                cmd.Parameters.AddWithValue("@stationLongitude", 7000); // Number data type
                cmd.Parameters.AddWithValue("@stationElevation", 8000); // Number data type
                cmd.Parameters.AddWithValue("@stationElevationUnit", "9000"); // Short Text data type
                cmd.Connection = myCon;
                myCon.Open();
                cmd.ExecuteNonQuery();
            }

I am able to write the API's response to console:

                foreach (Station loc in noaa.results)
                {
                    Console.WriteLine(loc.id);
                    Console.WriteLine(loc.name);
                    Console.WriteLine(loc.mindate);
                    Console.WriteLine(loc.maxdate);
                    Console.WriteLine(loc.datacoverage);
                    Console.WriteLine(loc.latitude);
                    Console.WriteLine(loc.longitude);
                    Console.WriteLine(loc.elevation);
                    Console.WriteLine(loc.elevationUnit);
                    Console.WriteLine();
                }
                /* Output
                    GHCND:CA001165793
                    100 MILE HOUSE 6 NE, CA
                    1987-06-01
                    2019-11-09
                    1
                    51.6833
                    -121.2167
                    928
                    METERS

                    GHCND:CA001165791
                    100 MILE HOUSE, CA
                    1970-05-01
                    1999-11-30
                    0.9993
                    51.65
                    -121.3
                    1059
                    METERS
                */

My actual issue

After confirming the process, I then tried to iteratively write my API response with the following, which didn't work and threw no errors:

            using (OleDbConnection myCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Lenovo\\Desktop\\Weather Cruncher\\wcMainDB.accdb"))
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandText = "Insert into stations_NOAA " +
                    "([stationID],[stationName],[stationMinDate],[stationMaxDate],[stationDataCoverage]," +
                    "[stationLatitude],[stationLongitude],[stationElevation],[stationElevationUnit])" +
                    "values (?,?,?,?,?,?,?,?,?)";
                cmd.Connection = myCon;
                myCon.Open();

                // Populate Datagrid
                foreach (Station loc in noaa.results)
                {
                    cmd.Parameters.AddWithValue("@stationID", loc.id); // Short Text data type
                    cmd.Parameters.AddWithValue("@stationName", loc.name); // Short Text data type
                    cmd.Parameters.AddWithValue("@stationMinDate", loc.mindate); // Short Text data type
                    cmd.Parameters.AddWithValue("@stationMaxDate", loc.maxdate); // Short Text data type
                    cmd.Parameters.AddWithValue("@stationDataCoverage", loc.datacoverage); // Number data type
                    cmd.Parameters.AddWithValue("@stationLatitude", loc.latitude); // Number data type
                    cmd.Parameters.AddWithValue("@stationLongitude", loc.longitude); // Number data type
                    cmd.Parameters.AddWithValue("@stationElevation", loc.elevation); // Number data type
                    cmd.Parameters.AddWithValue("@stationElevationUnit", loc.elevationUnit); // Short Text data type

                }
            }

With break-points, I was able to watch it iterate through the above but found no data added to the Access Database. Something is wrong with my query- what though?

Mike Williams · Accepted Answer · 2019-12-29 17:07:30Z

2

The inner for each loop in the final example does not show you calling cmd.excecutenonquery() after setting the parameters

You probably should instantiate a fresh cmd object inside the inner for each loop.

answered Dec 29, 2019 at 17:07

Mike Williams

1939 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

richardsonwtr · Accepted Answer · 2019-12-29 17:52:16Z

   using (OleDbConnection myCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Lenovo\\Desktop\\Weather Cruncher\\wcMainDB.accdb"))
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = "Insert into stations_NOAA " +
                "([stationID],[stationName],[stationMinDate],[stationMaxDate],[stationDataCoverage]," +
                "[stationLatitude],[stationLongitude],[stationElevation],[stationElevationUnit])" +
                "values (?,?,?,?,?,?,?,?,?)";
            cmd.Connection = myCon;
            myCon.Open();

            // Populate Datagrid
            foreach (Station loc in noaa.results)
            {
                cmd.Parameters.AddWithValue("@stationID", loc.id); // Short Text data type
                cmd.Parameters.AddWithValue("@stationName", loc.name); // Short Text data type
                cmd.Parameters.AddWithValue("@stationMinDate", loc.mindate); // Short Text data type
                cmd.Parameters.AddWithValue("@stationMaxDate", loc.maxdate); // Short Text data type
                cmd.Parameters.AddWithValue("@stationDataCoverage", loc.datacoverage); // Number data type
                cmd.Parameters.AddWithValue("@stationLatitude", loc.latitude); // Number data type
                cmd.Parameters.AddWithValue("@stationLongitude", loc.longitude); // Number data type
                cmd.Parameters.AddWithValue("@stationElevation", loc.elevation); // Number data type
                cmd.Parameters.AddWithValue("@stationElevationUnit", loc.elevationUnit); // Short Text data type
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
        }

You forget to call cmd.ExecuteNonQuery();.

If this approach doesn't work you'll need to put the cmd variable definition inside the foreach loop.

Edit: as shown here, you should clear the parameters after each iteration.

Collectives™ on Stack Overflow

Writing Data to Access Database with foreach

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related