Click here to Skip to main content
15,067,250 members
Articles / Programming Languages / C#
Article
Posted 5 Jan 2007

Stats

147.2K views
5.4K downloads
50 bookmarked

Bulk Record Insert for Access

Rate me:
Please Sign up or sign in to vote.
2.71/5 (16 votes)
5 Jan 20073 min read
This article demonstrates how perform a bulk 'Insert' with MS Access.

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

License

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

Share

About the Author

lost in transition
Web Developer
United States United States
I am a software, database, and gis developer. I love the challenge of learning new ways to code.

Comments and Discussions

 
GeneralMy vote of 1 Pin
Maciej Los2-May-19 9:13
mveMaciej Los2-May-19 9:13 
GeneralMy vote of 5 Pin
rey austral26-Dec-11 16:31
Memberrey austral26-Dec-11 16:31 
GeneralMy vote of 1 Pin
Member 809584527-Nov-11 20:33
MemberMember 809584527-Nov-11 20:33 
GeneralThis works with the following changes Pin
mrtwentysix28-Oct-10 4:36
Membermrtwentysix28-Oct-10 4:36 
GeneralMy vote of 1 Pin
Member 39753537-Oct-10 3:05
MemberMember 39753537-Oct-10 3:05 
GeneralMy vote of 1 Pin
Eric Daniel David27-Apr-10 7:19
MemberEric Daniel David27-Apr-10 7:19 
GeneralTable Does Not Update Pin
Bob Steel-Smith29-Mar-10 1:54
MemberBob Steel-Smith29-Mar-10 1:54 
GeneralRe: Table Does Not Update Pin
Bob Steel-Smith29-Mar-10 21:11
MemberBob Steel-Smith29-Mar-10 21:11 
GeneralSir,i have query please help me Pin
sonalisharma29-May-08 19:15
Membersonalisharma29-May-08 19:15 
QuestionUpdate won't work more than once per application execution Pin
purepremiumpulp14-Jan-08 18:01
Memberpurepremiumpulp14-Jan-08 18:01 
GeneralRe: Update won't work more than once per application execution Pin
purepremiumpulp15-Jan-08 12:44
Memberpurepremiumpulp15-Jan-08 12:44 
Generalproblem with code Pin
rajinder s8-Apr-07 22:02
Memberrajinder s8-Apr-07 22:02 
GeneralRe: problem with code Pin
lost in transition 13-Apr-07 4:55
Member lost in transition 13-Apr-07 4:55 
Generalhelp Pin
kholiwe4-Apr-07 23:16
Memberkholiwe4-Apr-07 23:16 
GeneralRe: help Pin
lost in transition 5-Apr-07 3:17
Member lost in transition 5-Apr-07 3:17 
GeneralA better SELECT statement for headers only Pin
John A. Gonzalez (FL)5-Jan-07 9:57
professionalJohn A. Gonzalez (FL)5-Jan-07 9:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.