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!!!