CODEWITHSUNDEEP
c#sql-serverdatabaseperformance
user7538827
user7538827

Reputation:

Slow performance importing MS Access database into SQL Server

I have a problem with importing items from an MS Access .mdb database file into SQL Server. I wrote a C# application in practice database that extrapolates the data in a .mdb database and places them in a table in a SQL Server database.

My problem is that the .mdb database contains about 300,000 articles which are to be inserted with all of the controls inside the SQL Server database. The .mdb file is selected by the user.

How can I speed up the import of the articles?

This is my C# code:

dbConn = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source=" + dialog.FileName + "; Persist Security Info = False; Jet OLEDB:Database Password = " + textBoxPwdComet.Text + "; Mode = Share Deny None");

// SqlConnection conn2 = db.apriconnessione();
try
{
    string query = "SELECT CODMARCA,CODART,DESCR,UM,PRZNETTO,PRZCASA,DATAAGG FROM ARTICOLI";

    string querycontalinee = "SELECT count(*) from ARTICOLI";

    OleDbCommand command = new OleDbCommand(query, dbConn);
    OleDbCommand commandcontalinee = new OleDbCommand(querycontalinee, dbConn);

    dbConn.Open();

    int linee = (int)commandcontalinee.ExecuteScalar();

    OleDbDataReader reader = command.ExecuteReader();
    Articolo a;

    labelstatoaggiornamento.Show();
    progressBarstatoaggiornamento.Show();

    progressBarstatoaggiornamento.Style = ProgressBarStyle.Continuous;
    progressBarstatoaggiornamento.Minimum = 0;
    progressBarstatoaggiornamento.Maximum = linee;
    progressBarstatoaggiornamento.Step = 1;

    SqlConnection conn = db.apriconnessione();

    while (reader.Read())
    {
        String CodMarca = "" + reader.GetValue(0).ToString();
        String CodArt = "" + reader.GetValue(1).ToString().Replace("'", ""); ;
        String Fornitore = "COMET";
        String Descrizione = "" + reader.GetValue(2).ToString();
        String UM = "" + reader.GetValue(3).ToString();
        String PrezzoNetto = "" + reader.GetValue(4).ToString();
        String PrezzoCasa = "" + reader.GetValue(5).ToString();
        DateTime DataAggiornamento = DateTime.Now;

        decimal Prezzo = Decimal.Parse(PrezzoNetto, System.Globalization.NumberStyles.Any);
        decimal PrezzoListino = Decimal.Parse(PrezzoCasa, System.Globalization.NumberStyles.Any);

        a = new Articolo(CodArt, CodMarca);

        a.db = db;

        if (a.ControlloDisponibilitàCOMET() == true)
        {
            string queryAggiornamento = "Update Articolo Set Descrizione='" + Descrizione + "', UM='" + UM + "', Prezzo='" + Prezzo + "',PrezzoListino='" + PrezzoListino + "',DataAggiornamento='" + DataAggiornamento + "',Stato='Aggiornamentoincorso' Where CodMarca = '" + CodMarca + "' AND CodArt = '" + CodArt + "' AND Importato = 'COMET' and Fornitore='COMET' ";

            SqlCommand commaggiorna = new SqlCommand(queryAggiornamento, conn);

            try
            {
                commaggiorna.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(" " + ex);
            }
       }
       else 
       {
           string query2 = "INSERT INTO Articolo (CodMarca, CodArt, Fornitore, Importato, Descrizione, UM, Prezzo, PrezzoListino, Stato) VALUES (@CodMarca, @CodArt, @Fornitore, @Importato, @Descrizione, @UM, @Prezzo, @PrezzoListino, @Stato)";

           SqlCommand myCommand = new SqlCommand(query2, conn);

           myCommand.Parameters.AddWithValue("@CodMarca", CodMarca);
           myCommand.Parameters.AddWithValue("@CodArt", CodArt);
           myCommand.Parameters.AddWithValue("@Fornitore", Fornitore);
           myCommand.Parameters.AddWithValue("@Importato", Fornitore);
           myCommand.Parameters.AddWithValue("@Descrizione", Descrizione);
           myCommand.Parameters.AddWithValue("@UM", UM);

           decimal PrezzoNetto2 = Decimal.Parse(PrezzoNetto, System.Globalization.NumberStyles.Any);
           myCommand.Parameters.AddWithValue("@Prezzo", PrezzoNetto2);

           decimal PrezzoCasa2 = Decimal.Parse(PrezzoCasa, System.Globalization.NumberStyles.Any);
           myCommand.Parameters.AddWithValue("@PrezzoListino", PrezzoCasa2);

           DateTime dt = Convert.ToDateTime(DataAggiornamento);
           myCommand.Parameters.AddWithValue("@Stato", "Aggiornamentoincorso");

           myCommand.ExecuteNonQuery();
       }

       progressBarstatoaggiornamento.PerformStep();

       int percent = (int)(((double)progressBarstatoaggiornamento.Value / (double)progressBarstatoaggiornamento.Maximum) * 100);

       progressBarstatoaggiornamento.CreateGraphics().DrawString(percent.ToString() + "%", new Font("Arial", (float)8.25, FontStyle.Regular), Brushes.Black, new PointF(progressBarstatoaggiornamento.Width / 2 - 10, progressBarstatoaggiornamento.Height / 2 - 7));
    }

    string queryNonDisponibili = "Update  Articolo Set Stato='Nondisponibile' where Stato!='Aggiornamentoincorso' AND Fornitore='COMET' AND Importato='COMET'";
    string queryNonDisponibili2 = "Update  Articolo Set Stato='Disponibile' where Stato='Aggiornamentoincorso' AND Fornitore='COMET' AND Importato='COMET'";

    SqlCommand comm = new SqlCommand(queryNonDisponibili, conn);
    SqlCommand comm2 = new SqlCommand(queryNonDisponibili2, conn);

    comm.ExecuteNonQuery();
    comm2.ExecuteNonQuery();

    Console.WriteLine("\n Passaggio Completato");
    conn.Close();

    db.chiudiconnessione();
    dbConn.Close();
}
catch (Exception ex)
{
    MessageBox.Show("La password è errata oppure " + ex);
}

Upvotes: 2

Views: 673

Answers (2)

M Omar
M Omar

Reputation: 31

I would use SqlBulkCopy ...

dbConn = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source=" + dialog.FileName + "; Persist Security Info = False; Jet OLEDB:Database Password = " + textBoxPwdComet.Text + "; Mode = Share Deny None");

 SqlConnection conn2 = db.apriconnessione();
 string query = "SELECT CODMARCA,CODART,DESCR,UM,PRZNETTO,PRZCASA,DATAAGG FROM ARTICOLI"; 
OleDbDataAdapter da = new OleDbDataAdapter(query,dbConn);
DataTable dt = new DataTable();
da.Fill(dt);
conn2.Open();
SqlBulkCopy bulk = new SqlBulkCopy(conn2);
bulk.DestinationTableName = "ARTICOLI";
bulk.WriteToServer(dt);
conn2.close();

Upvotes: 2

user4864425
user4864425

Reputation:

Consider using SqlBulkCopy. Since you are running sql queries I would suggest you'd work server side as much as possible. Create a temp table in Sql Server, add all records to a datatable or array of datarows and use SqlBulkCopy to import. I think that is the fastest way to move all records to Sql Server.

From there you can synchronize the two tables in Sql Server with only a few queries.

Upvotes: 2

Related Questions