How to import MDB file read and write to SQL

Question

I am trying to import a MDB file, read this file and write to SQL. I’m already able to import the file and read, I just can’t yet take this data and write to SQL.

If you notice the code I even managed to show the data in a List.

Follow my code below:

protected void Btn_Importar(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                if (FileUpload1.PostedFile.ContentType == "application/msaccess")
                {
                    string filename = Path.GetFileName(FileUpload1.FileName);
                    FileUpload1.SaveAs(Server.MapPath("~/Upload/") + filename);
                    Label1.Text = "File uploaded successfully!";
                    //ReadMdb();
                    Insert();
                }
            }
        }


public void Insert()
        {
            string strFile = Server.MapPath("~/Upload/teste.mdb");
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFile;
            var myDataTable = new DataTable();
            using (var connection = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" + "data source=" + strFile))
            {
                connection.Open();
                var query = "SELECT * FROM BOLETO";
                var command = new OleDbCommand(query, connection);
                var reader = command.ExecuteReader();

                Conexaocs con = new Conexaocs();
                con.Conexao();

                SqlCommand sqlComm = new SqlCommand("INSERT INTO BOLETO (CODIGO,NF_CONTA,TEXTO) VALUES (@CODIGO, @NF_CONTA, @TEXTO)", conn);

                //if (reader.HasRows)
                //{
                //    while (reader.Read())
                //    {
                //        //ListBox1.Items.Add(reader.GetInt32(0).ToString() + " - " + reader.GetString(1));
                //    }

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    sqlComm.Parameters.AddWithValue("@CODIGO", reader[i]);
                    sqlComm.Parameters.AddWithValue("@NF_CONTA", reader[i]);
                    sqlComm.Parameters.AddWithValue("@TEXTO", reader[i]);
                    sqlComm.ExecuteNonQuery();
                }

                connection.Close();
            }
        } 

I put only these columns to test.

Answer 1

Try the following. Just be sure to adapt your INSERT with all available columns/fields.

     public void Insert()
     {
        string strFile = Server.MapPath("~/Upload/teste.mdb");
        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFile;
        var myDataTable = new DataTable();
        using (var connection = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" + "data source=" + strFile))
        {
            connection.Open();
            var query = "SELECT * FROM BOLETO";
            var command = new System.Data.OleDb.OleDbCommand(query, connection);
            var reader = command.ExecuteReader();

            Conexaocs con = new Conexaocs();
            con.Conexao();


            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    // Prepara um comando para cada insert
                    SqlCommand sqlComm = new SqlCommand("INSERT INTO BOLETO (CODIGO,NF_CONTA,TEXTO) VALUES (@CODIGO, @NF_CONTA, @TEXTO)", conn);

                    // Loop com os campos
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        // Adiciona um parametro para cada campo
                        sqlComm.Parameters.AddWithValue("@" + reader.GetName(i), reader[i]);                            
                    }

                    // Executa com os parâmetros adicionados
                    sqlComm.ExecuteNonQuery();
                }

            }


            connection.Close();
        }
    }