0

My code : 

List<String> listeErreurCode = new List<string>();
String cmd = "INSERT INTO dbo.PMEELEMENT (CODEBIBLIO,CODE,REFERENCE) VALUES (@CODEBIBLIO,@CODE,@REFERENCE)";
using (SqlTransaction trans = connection.BeginTransaction())
{
    using (SqlCommand command = new SqlCommand(cmd, connection))
    {
        command.Transaction = trans;
        command.Parameters.Add("@CODEBIBLIO", SqlDbType.VarChar);
        command.Parameters.Add("@CODE", SqlDbType.VarChar);
        command.Parameters.Add("@REFERENCE", SqlDbType.VarChar);
        int totalElement = listeElement.Count;
        listeElementPresent = new List<PMEELEMENT>();
        foreach (PMEELEMENT element in listeElement)
        {      
            System.Threading.Thread.Sleep(1);
            if (this.listeBXmxMda.Where(elt => elt.Code == element.CODE).ToList().Count() > 1)
            {
                PMENATUREELEMENT nn = this.listeNatureElement.Where(lne => lne.CLE == element.CLENATURE).FirstOrDefault();
                if (nn != null)
                {
                    this.AddEquivalenceCode(element.CODE, element.CODE + "-" + nn.ABREVIATION);
                    element.CODE += "-" + nn.ABREVIATION;
                    element.REFERENCE += "-" + nn.ABREVIATION;
                }
                else
                {
                    listeCodeEnErreur.Add(element.CODE + ";" + "La nature n'existe pas dans SMDE");
                    continue;
                }
            }
            command.Parameters["@CODEBIBLIO"].Value = element.CODEBIBLIO;
            command.Parameters["@CODE"].Value = element.CODE;
            command.Parameters["@REFERENCE"].Value = element.REFERENCE;
            try
            {                               
                result += command.ExecuteNonQuery();                                
            }
            catch (Exception ex)
            {
                listeCodeEnErreur.Add("Code element : " + element.CODE + ";" + "Code famille : " + element.CODEFAMILLE + ";" + "Nature : " + element.CLENATURE + ";" + ex.Message + ";" + ex.InnerException);
            }
        }
    }
    trans.Commit();
}

For example, with this method, if I want to insert 200 000 records i must wait 4 - 5 hours before finish

Someone can help me ?

I don't know how can i improve my code and i search since 3 months.

Moussawi
  • 393
  • 1
  • 6
  • 22
  • 1
    Many more useful posts: https://www.google.com/search?q=insert+large+number+of+records+sql+server+c%23 – Patrick Hofman Oct 18 '17 at 07:16
  • 2
    Define the max size for all varchar parameters. This help Sql Server optimization engine to create a common and reusable code when it receives your query – Steve Oct 18 '17 at 07:17
  • I'm sure the duplicate will answer your questions, but I'd recommend against doing this: `this.listeBXmxMda.Where(elt => elt.Code == element.CODE).ToList().Count() > 1`. Instead do this: `this.listeBXmxMda.Where(elt => elt.Code == element.CODE).Any()`. That way, if the Where() returns 20 items, you don't need to convert them to a list object before you can check if there are any items. – ProgrammingLlama Oct 18 '17 at 07:29
  • you're adding them one by one, it is slow for sure, check linked question. – michal.jakubeczy Oct 18 '17 at 07:41

0 Answers0