To add entries to Access, I use code:

string connStringAcc = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}", textBox2.Text); // textBox2.Text - путь к базе using (OleDbConnection connAcc = new OleDbConnection(connStringAcc)) { connAcc.Open(); OleDbCommand command1 = new OleDbCommand ("INSERT INTO " + textBox10.Text + // ТАБЛИЦА "(" + textBox15.Text + // ПОЛЯ ") VALUES" + "('" + pol_1 + "', '" + pol_2 + "')", connAcc); command1.ExecuteNonQuery();

Are there other ways to add entries to Access?

  • 3
    I don’t know what the OleDbConnection approach didn’t suit you, but if you put everything into separate methods and arrange it beautifully, it looks not so bad. Perhaps you will be interested in the following page: A practical guide. Insert new records into the database - Denis Bubnov
  • @Denis Bubnov While mastering, so the question may not be logical ... But I want to see how the database will work with the maximum number of records .. For this I want to write ~ 500,000 records .. Through the code I described, the records are added for a long time. Therefore, I ask: can there be other ways - koverflow
  • one
    for quick recording of a large number of records you need to use something like Bulk Insert - Denis Bubnov
  • one
    @DenisBubnov, in Access there is no Bulk. - iluxa1810
  • one
    @ iluxa1810, so I did not write what it is, I wrote something like Bulk Insert - Denis Bubnov

2 answers 2

There is quite a good guide on MSDN: A practical guide. Insert new records in the database , which has the following items:

Insert new entries using table adapters

At this point, everything is quite simple. There are two approaches for inserting data into the desired DataTable in a dataset using TableAdapter.Update and TableAdapter.Insert . To do this, you need an instance of DataTable , which will be used as a table adapter.

To insert new records into the database using the TableAdapter.Update method:

  1. Add new entries to the desired DataTable tables by creating new DataRow rows and adding them to the Rows collection. For more information, see the Practical Guide. Adding rows to a DataTable .
  2. After adding new rows to the DataTable call the TableAdapter.Update method. You can control the amount of data to update by transferring the entire DataSet , DataTable , DataRow array, or a single DataRow .

To insert new records into the database using the TableAdapter.Insert method:

  1. Call the Insert Table Adapter method, passing values ​​for each column as parameters.

Inserting new entries using command objects

That is, new entries will be inserted directly into the database using command objects. That is, create a SqlConnection , open a connection, execute a command, and close. In principle, what you already do at home.

To insert new entries into the database using command objects:

  1. Create a new command object by setting its Connection , CommandType and CommandText .

Note:

You must have access to the database to which you are trying to connect, as well as permission to insert records into the desired table. Well, I think this is understandable.

Regarding the approach with OleDbConnection , of course, at first glance, it seems inconvenient and cumbersome, but if everything is carried out into separate methods and beautifully decorated, for example, you can make universal methods for inserting, selecting and deleting objects and putting it into a separate class, something similarity Helper, you will not notice the bulkiness that envelops you now, it looks not so bad. I think it is from this and begin the search for other approaches.

Also, I think the following links will be helpful:

  1. Spreadsheet Adapter Overview
  2. Class SqlCommand
  3. Class DataTable

    Since the obvious ways are listed, I will give alternatives:

    microsoft dao 3.6 object

    If speed is important, then you can use the library microsoft dao 3.6 object library .

    Here is a proof of speed.

    I myself was convinced in practice that he was faster.

    Working with it is similar to your code, except for the fact that this assembly COM => you need to monitor the closure of connections.

    Interop

    You can also work with Access through Interop.

    The work will be similar to how you would write a VBA module in Access, but in C #.

    This method provides even more options, since you get access to Access objects, such as queries and modules.

    For example, you can execute an Access module from C # code.

    But again, you need to keep track of objects, since this is the same COM ...

    To work on the target machine must be Office or Access Engine.

    Dapper

    You can use Micro ORM Dapper, which works with objects that implement IDbConnection, including OleDbConnection.

    With it, the code looks clearer and more concise.