Insert ItemArray in SQL Server

Question

Insert ItemArray in SQL Server

Navigation

#1 by (3 votes)

0

I am trying to insert the record by the Excel registration to SQL, I have already loaded my excel, I go through it with a foreach in the following way:

SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = "Data Source=Localhost;Initial Catalog=DevLiverpool_DB; Integrated Security = True";
cnn.Open();
    foreach (DataRow row in dt.Rows)
    {
                SqlCommand cmnd = new SqlCommand("insert into Ciudad_tb (Id_ciudaddestino, Ciudadestino) values (" + row.ItemArray[0].ToString() + ",'" + row.ItemArray[1].ToString() + "'", cnn);
                cmnd.ExecuteNonQuery();
    }

But it sends me an error near my syntax, I already debugged it and I'm observing that just as I do the item array brings all the data and not just the one I want to insert, I do not know if it explains it well ... Greetings and thanks for the help

c# .net sql-server

asked by LuisMB92 28.12.2018 в 00:48

source

1 answer

I can not do post Node js Router Express Firebase: Save UID in the database

score 3 · Answer 1

You still have to close the paranthesis at the end ")", which is why it fails

But I would recommend that you use parameters

string connstring = "Data Source=Localhost;Initial Catalog=DevLiverpool_DB; Integrated Security = True";
SqlConnection cnn = new SqlConnection(connstring);
cnn.Open();

string query = "insert into Ciudad_tb (Id_ciudaddestino, Ciudadestino) values (@Id, @ciudad)";
SqlCommand cmnd = new SqlCommand(query, cnn);

foreach (DataRow row in dt.Rows)
{
    cmnd.Parameters.Clear();
    cmnd.Parameters.AddWithValue("@Id", Convert.ToInt32(row.ItemArray[0]));
    cmnd.Parameters.AddWithValue("@ciudad", row.ItemArray[1].ToString()));
    cmnd.ExecuteNonQuery();
}

This way you do not have to instantiate the command in each iteration of the foreach