How to insert data into sql server within a foreach loop in c#

Question

I'm doing my final project for some classes and in most test cases I'll be asked to insert lots of records into a database, the records will be given in an xml file. My question is how could i insert all those records from the xml into the database because what I've tried hasn't worked. Here's what I've done so far:

I'm doing the insertion from a stored procedure (It Is indeed a requirement for the final so i cant do a bulk insertion)

C#

XmlDocument xmldoc = new XmlDocument();
xmldoc.LoadXml(IdeaEvaluation.Properties.Resources.Cities);

XmlNode root = xmldoc.DocumentElement;
XmlNodeList nodeList = root.SelectNodes("/Ciudades/Ciudad");

string nombre = string.Empty;
string codigo_postal = string.Empty;

foreach (XmlNode node in nodeList)
{
    nombre = node["nombre"].InnerText;
    codigo_postal = node["codigo_postal"].InnerText;

    try
    {
        using (SqlCommand cmd = new SqlCommand("dbo.CreateCiudad", conn) { CommandType = CommandType.StoredProcedure })
        {
            cmd.Parameters.Add("@nombre", SqlDbType.VarChar, 10).Value = nombre;
            cmd.Parameters.Add("@codigo_postal", SqlDbType.VarChar, 6).Value = codigo_postal;
            string text = cmd.CommandText;
            cmd.ExecuteNonQuery();
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

Everything works fine until it gets to execute the insert and then i don't know what happens, it never inserts a row or anything.. i believe that is because it doesn't have the time to do the insert as the loop continues to loop very fast.

Here's the stored procedure

   CREATE PROC Createciudad(@nombre        VARCHAR, 
                         @codigo_postal VARCHAR) 
AS 
  BEGIN 
      IF NOT EXISTS(SELECT * 
                    FROM   dbo.codigo_postal 
                    WHERE  codigo_postal = @codigo_postal) 
        INSERT INTO dbo.codigo_postal 
                    (codigo_postal) 
        VALUES      (@codigo_postal); 

      IF NOT EXISTS(SELECT * 
                    FROM   dbo.ciudad 
                    WHERE  nombre = @nombre 
                           AND codigo_postal = @codigo_postal) 
        INSERT INTO dbo.ciudad 
                    (nombre, 
                     codigo_postal) 
        VALUES      (@nombre, 
                     @codigo_postal); 
  END;

Any idea on how to insert the data within the foreach loop or what other kind of loop or method should i use?

Getting errors? If so, which? It doesn't have to do with a 'too fast loop'. The next iteration will only be done when the code in the loop is completed. — Abbas
– Abbas, Commented May 14, 2014 at 10:24
it's run on synchronously so there is no concept of "too fast loop" of foreach — Dhaval Patel
– Dhaval Patel, Commented May 14, 2014 at 10:26
Is the code wrapped with a transaction? If so: complete the transaction or else your SqlCommand will be rolled back. — Dries Marckmann
– Dries Marckmann, Commented May 14, 2014 at 10:28

podiluska · Accepted Answer · 2014-05-14 10:40:42Z

1

You need to define the length of your parameters, or they'll be given the default length of 1 character

CREATE PROC Createciudad(@nombre        VARCHAR(50), 
                     @codigo_postal VARCHAR(50))

answered May 14, 2014 at 10:40

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HardCodeStuds Over a year ago

As son as i have access to my terminal i'll try defining the length of the parameters and i'll update this comment and accept as answer if working.

Collectives™ on Stack Overflow

How to insert data into sql server within a foreach loop in c#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related