Bulk Record Insert for Access

• Download demo project - 18Kb
• Download source - 89Kb

Introduction

This article show a method of inserting multiple new records(rows) into an MS Access database using OleDbDataAdapter.

Background

Many times here at Code Project, I have seen post asking for help on a 'Mass Update' of new records to databases. I have also seen this question on other site as well. And in remembering when I first started learning to intact database this was a big question to me to. Sometimes you can find really good information out on the net or in book on how to solve a problem but sometimes you can't. So, I decided to write this article.

Using the code

This application will act as if it is taking electric meter readings that are in some file and load them into a Access database. The reading are taken every 15 minutes. There are four meters being read at the same time. These data is written to a file with 256 bits (or 8 different sets of readings) on one line in the file. Each meter reading is 8 bits long for a total of 32 bits for each set of readings.

So the problem: Read the file one line at a time breaking the line into sets of readings and placing them in a data table. After the file is read and data table is filled INSERT the new records into the database.

Sample of the data

0006100000000000000250000074800000062000000000000002400000752000

Here is the method that takes a line already read in by the Stream Reader and splits it up into the sets of readings and creates a new row(record DR). Then adds that set to the data table(DT).

        private void ProcessLine()
        {
            int Position = 0;    //Position in the StreamReader Line
            const int Skip = 8;  //Each Reading is 8 characters long
            while (Position < Line.Length)
            {
                DataRow DR = DT.NewRow();
                DR["Meter1"] = Convert.ToInt32(Line.Substring(Position, Skip));
                Position += Skip;
                DR["Meter2"] = Convert.ToInt32(Line.Substring(Position, Skip));
                Position += Skip;
                DR["Meter3"] = Convert.ToInt32(Line.Substring(Position, Skip));
                Position += Skip;
                DR["Meter4"] = Convert.ToInt32(Line.Substring(Position, Skip));
                Position += Skip;
                DT.Rows.Add(DR);
            }
        }

Let me step back and show you how I got the data table. Here I have a static method in my database class that returns a DataTable. I use a 'SELECT' SQL statement to retrieve the table that will receive the meter readings. If you look at the 'WHERE' clause you will see I am trying to retrive a record with the ReadingID of 0. It is important to know that ReadingID is a Autonumber/PK for the table. It will is not going to have a record where ReadingID = 0. This is just a quick way to get the table layout.

        public static DataTable LoadDT(string DB)
        {
            CheckTables(DB);
            DataTable DT = new DataTable();
            string ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DB +
                                ";User Id=admin;Password=;";
            string SQL = "SELECT * FROM MeterReadings WHERE ReadingID=0";

            OleDbConnection OleConn = new OleDbConnection(ConnString);
            OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL, OleConn);
            OleConn.Open();
            OleAdp.Fill(DT);
            OleConn.Close();
            return DT;
        }

Now after we have processed our file and are ready to INSERT the new records into our database. We need a quick and easy way of doing this. So, looking at the next block of code you will see that I create a Data Adapter with the same SQL SELECT as when I created my data table. This just helps to ensure everything is the same going into the database as it is coming out. I assign a SQL INSERT add my parameters and then execute the Data Adapters's Update method.

        public static void UpDataDB(string DB, DataTable DT)
        {
            string ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DB +
                                ";User Id=admin;Password=;";
            string SQL = "SELECT * FROM MeterReadings WHERE ReadingID=0";
            string INSERT = "INSERT INTO MeterReadings(Meter1, Meter2, Meter3, Meter4) " +
                            "VALUES (@Meter1, @Meter2, @Meter3, @Meter4)";

            OleDbConnection OleConn = new OleDbConnection(ConnString);
            OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL, OleConn);
            OleAdp.InsertCommand = new OleDbCommand(INSERT);
            OleAdp.InsertCommand.Parameters.Add("@Meter1", OleDbType.Integer, 8, "Meter1");
            OleAdp.InsertCommand.Parameters.Add("@Meter2", OleDbType.Integer, 8, "Meter2");
            OleAdp.InsertCommand.Parameters.Add("@Meter3", OleDbType.Integer, 8, "Meter3");
            OleAdp.InsertCommand.Parameters.Add("@Meter4", OleDbType.Integer, 8, "Meter4");
            OleAdp.InsertCommand.Connection = OleConn;
            OleAdp.InsertCommand.Connection.Open();
            OleAdp.Update(DT);
            OleAdp.InsertCommand.Connection.Close();
        }

Points of Interest

While I hope this might help someone. If one person can be reached then well, I guess slap 'em or say hi or something..stop looking at me!!!

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here