I have an array of objects. Each object contains around 20 members. I need to loop through the array and insert the data from the object into my database. Is there a way of doing this that does not require me to put an INSERT statement within the body of my loop? I am using C# and SQL Server.
for(int i =0; i < arr.length; i++)
{
strSQL = "INSERT INTO myTable (field1...field20) VALUES (" + arr[i].field1 + "..." + arr[i].field20)
sqlCmd.execute(strSQL,sqlConn)
}
Why don't you use linq ?
Yes there is way.
First, +1 for lollancf37's answer.
And second, you can use StringBuilder. Build your query parameter with 1 query using StringBuilder, and execute 1 time
Enjoy
SqlCommand sqlcmd = null;
SqlParameter pField1 = new SqlParameter("@Field1", System.Data.SqlDbType.VarChar, 255);
...
SqlParameter pField20 = new SqlParameter("@Field20", System.Data.SqlDbType.VarChar, 255);
try{
sqlcmd = new SqlCommand("INSERT INTO myTable (field1...field20) VALUES (@Field1,...,@Field20)",sqlConn)
sqlcmd.Parameters.Add(pField1);
...
sqlcmd.Parameters.Add(pField20);
for(int i =0; i < arr.length; i++)
{
pField1.value = arr[i].field1;
...
pField20.value = arr[i].field20;
sqlCmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
LogError(ex.message)
}
finally
{
if (sqlconn != null && sqlconn.State != System.Data.ConnectionState.Closed)
sqlconn.Close();
if (sqlcmd != null)
sqlcmd.Dispose();
}
For simple commands, you could use a format string, something like this:
//assuming that the first param is a number, the second one a string etc...
string insertFormat = @"Insert into myTable((field1...field20)
VALUES ({0},'{1}',..,19})"
for(int i =0; i < arr.length; i++)
{
//assuming that arr[i] is string[]
strSQL = string.Format(insertFormat, arr[i]);
sqlCmd.execute(strSQL,sqlConn)
}
You must be aware, however, that you are leaving yourself wide open for SQL Injection (mandatory xkcd). In order to avoid that, and other issues, you could take a look at "advanced| solutions (using a stored procedure, using reflection to map the fields, using some ORM tool, using Linq-to-sql, etc)
strSQL = "INSERT INTO MYTABLE (COL01, COL01...COL42)";
for(int i = 0; i < arr.length; i++)
{
strSQL += "SELECT (" +arr[0] +", " +arr[1] +"..." +arr[42] +")";
if(i < arr.length - 1) {
strSQL += " UNION ALL ";
}
}
sqlCommand.Execute(strSQL, conn); // I forget how this bit goes, I am not a C# programmer by trade...
I find that inserting more than 500 records at a time like this makes the database run pretty slow.
Parameterised stored procedures.
Write the insert statement down in the db as a stored procedure, with parameters.
Create a method to populate the stored procedure, passing in the object as the parameter.
Call the method in the loop, populating an passing in an object instance each time.
I had a similar problem at one point, the most effective way I found to deal with it was create a 2-dimensional array for your data, and store the column name for each line of data in the array in addition to storing the data itself.
From there, it's pretty trivial to loop through it and use a StringBuilder to assemble the query. Additionally, instead of simply throwing the insert values on, put a parameter name on them (I just used the column name). Then, in the same loop, you can create a new SqlParameter.
If you're using .NET 4, just create an SqlParameter array and add your created parameters to it in your loop. After your loop ends, you can use the "AddRange' method of your command's parameter collection to simply add the parameter array to your command.
This way, you're able to build a fully dynamic query that sanitizes inputs.
