What better way to insert an array into the database

Asked

Viewed 2,226 times

0

I would like to know how to best insert an array into a database table. I could use a foreach but I would like something performative because I will work with large number of data.

    foreach (var item in registros)
        {
            string CPF = String.Empty;
            string nome = String.Empty;
            string apto = String.Empty;

            CPF = item.Split('|')[0];
            nome = item.Split('|')[1];
            apto = item.Split('|')[2];
            SqlCommand cmd = new SqlCommand("INSERT INTO DATABASE.DBO.TABLE (CPF, NOME, APTO) VALUES (" + CPF + "," + nome + "," + apto + ")");
        }
c# sql sql-server

  • Depends. How is the database? How should the data be recorded? But I can already tell you that there is no magical command that makes operations huge and things fast.

  • It will be a table with three simple columns (CPF, NAME, STATUS). I would like the most performative form, I believe it is not the foreach.

  • Only with this information can it be very difficult to help you. But I can already tell you that if you are not doing it as fast as possible. That is, the difference if you use a for simple will be minimal (I think until it will be worse). Reinforcement that there is no magic solution. If you showed why you need more performance, the overall context, maybe you could suggest something different, but just with this information, I can only say it’s this or the for simple, test both and see which is faster. Probably the gain will not be worth the effort.

  • http://answall.com/a/53857/101 looks at how it doesn’t make much difference and how the foreach may even be the fastest.

  • I had a similar problem. I needed to save 100,000 records in my BD. Using the form you demonstrated, it was taking about 5 minutes. So instead of saving directly, I used foreach to save the data to a list, and I used Bulkinsert to enter the data. The task changed from 5 minutes to about 15 seconds. If you need it, let me know and I’ll get back to you later.

  • @Randrade exactly, if he provides more information of the whole process can to find other solutions, only with this stretch can not help much. Loop will be needed to mount the data but it need not be used to write the data.

  • 1

    @Jeangustavoprates, very important: do not concatenate the query values in this way, you are vulnerable to SQL Injection! Use parametrized queries.

Show 2 more comments

2 answers

2

What you are trying to do is a "Bulk Insert", that is, an Insert with a large number of lines at once. In the beginning, the best way to do it would be with a BULK INSERT, but can also go by the simplest method and make an instruction INSERT. This instruction will be built within the for and executed only once, after the same. Picking up your code:

string CPF = String.Empty;
string nome = String.Empty;
string apto = String.Empty;
string valuesString = String.Empty;
for(int i = 0; i < registros.count; i++) { 
    var item = registros[i];
    CPF = item.Split('|')[0];
    nome = item.Split('|')[1];
    apto = item.Split('|')[2];
    values += "(" + CPF + "," + nome + "," + apto + ")";
    if (i < registros.count-1) 
       values += ",";
}
if (!String.IsNullOrEmpty(valuesString)) {
   SqlCommand cmd = new SqlCommand("INSERT INTO DATABASE.DBO.TABLE (CPF, NOME, APTO) VALUES " + valuesString;
    ...
}

1


@Jeangustavoprates, you can perform insertion only after mounting all Inserts with SqlBulkCopy or the way down:

string stmt = "INSERT INTO DATABASE.DBO.TABLE (CPF, NOME, APTO) VALUES (@cpf, @nome, @apto)";

SqlCommand cmd = new SqlCommand(stmt, _connection);

cmd.Parameters.Add("@cpf", SqlDbType.VarChar, 100);
cmd.Parameters.Add("@nome", SqlDbType.VarChar, 100);
cmd.Parameters.Add("@apto", SqlDbType.VarChar, 100);

for (int i = 0; i < registros.count i++)
{
    var item = registros[i];
    string CPF = item.Split('|')[0];
    string nome = item.Split('|')[1];
    string apto = item.Split('|')[2];
    cmd.Parameters["@cpf"].Value = CPF;
    cmd.Parameters["@nome"].Value = nome;
    cmd.Parameters["@apto"].Value = apto;

    cmd.ExecuteNonQuery();
}

But anyway, I believe that for to recover the information from CPF, name and fit you will not be able to escape.

Very important: do not concatenate the query values in this way, you are vulnerable to SQL Injection! Use parametrized queries, as I demonstrated above.

Adapted from: https://stackoverflow.com/a/8218932/2387977

Browser other questions tagged c# sql sql-server

You are not signed in. Login or sign up in order to post.