Can we execute multiple insert statements into MS Access DB 2016 using OLEDB without closing connection

Question

can we execute multiple insert statements into MS Access DB 2016 using OLEDB without closing connection/Keeping session alive? I tried moving the OleDB command instantiation, con.open() and con.close() outside of foreach loop but it does not work. Any suggestions?

  foreach (var list in lsobj)
                {
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    con.Open();
                    cmd.CommandText = "INSERT INTO tbl(file_dt, name, created_dt)";
                                      "VALUES (@file_dt,@name,@inserted_dt)";

                    cmd.Parameters.AddWithValue("@file_dt", list.file_dt.ToString());
                    cmd.Parameters.AddWithValue("@name", list.name);
                    cmd.Parameters.AddWithValue("@inserted_dt", list.inserted_dt.ToString());
                    
                    cmd.ExecuteNonQuery();
                    con.Close();
                }

**1.** `AddWithValue` may not match data type perfectly and you get implicit conversion in DB that hurts performance. **2.** If you need to add all or nothing - open transaction and save using transaction. **3.** Don't recreate parameters. Pass parameters values. Add parameters once and then just change the values — T.S., Sep 01 '22 at 02:33

Albert D. Kallal · Answer 1 · 2022-09-01T02:14:59.140

Yes, you can. As a general rule, before .net days, it was quite much the "norm" to open a connection, and keep it open. However, .net system tends to cache and re-use the connections you made, even when closed.

but, for the most part, yes, you can execute multiple statements, and do them with the same connection.

So, say this code example:

    void TestFun()
    {
        using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
        {
            using (OleDbCommand cmdSQL = new OleDbCommand("", conn))
            {
                conn.Open();
                // save picture as bytes to DB
                byte[] fData = File.ReadAllBytes(txtFile.Text);
                string strSQL =
                    @"INSERT INTO MyPictures (FileName, PictureData)
                    VALUES(@File, @Data)";
                cmdSQL.CommandText = strSQL;
                cmdSQL.Parameters.Add("@File", OleDbType.VarWChar).Value = txtFileName.Text;
                cmdSQL.Parameters.Add("@Data", OleDbType.Binary).Value = fData;
                cmdSQL.ExecuteNonQuery();

                // display data in our grid
                cmdSQL.CommandText = "SELECT ID, FileName FROM MyPIctures";
                cmdSQL.Parameters.Clear();
                DataTable rstData = new DataTable();
                rstData.Load(cmdSQL.ExecuteReader());
                dataGridView1.DataSource = rstData;

                // do more commands - still same connection

            }
        }
    }

Now, you can't execute multiple SQL statements in one "go" like you can with SQL server (just separate several statements by a ";".

However, you can certain create one connection (and even one command object), and re-use it over and over.

And since the connection and cmdSQL object are inside of a using block, then both objects, and including your connection will be closed and tidy up after you are done.

FYI: USE Parameters . Add, NOT add with value.

Now, in your case? Since it is the same command over and over - but ONLY the parameters change?

Then this (air code warning)

        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        con.Open();
        cmd.CommandText = @"INSERT INTO tbl (file_dt, name, created_dt)
                            VALUES (@file_dt,@name,@inserted_dt)";


        cmd.Parameters.Add("@file_dt", OleDbType.VarWChar);
        cmd.Parameters.Add("@name", OleDbType.VarWChar);
        cmd.Parameters.Add("@inserted_dt", OleDbType.VarWChar);

        foreach (var list in lsobj)
        {

            cmd.Parameters["@file_dt"].Value = list.file_dt.ToString();
            cmd.Parameters["@name"].Value = list.name;
            cmd.Parameters["@inserted_dt"].Value =  list.inserted_dt.ToString());
            cmd.ExecuteNonQuery();
        }

        con.Close();

Now, I always wrap above in a using block. But, as above again shows, we are free to create a command object, and use it for "many" commands as per first example. and in the 2nd example, we ONLY have to setup the parameters one time, and then use the same connection, same command object over and over.

Can we execute multiple insert statements into MS Access DB 2016 using OLEDB without closing connection

1 Answers1