40784

SQL Parameters Inside A Loop

Question:

i have a list that i am pulling things out of to insert into a database. This is not going to be a web app so i have just been doing as follows:

string sqlStorage = (null,"asd"), for (int i = 1; i < listsize; ) { sqlStorage = sqlStorage + "(null,someVariableFromLoop)"; i++ } string connString = "Server=localhost;..........."; MySqlConnection conn = new MySqlConnection(connString); MySqlCommand command = conn.CreateCommand(); command.CommandText = @"INSERT INTO table1 VALUES " + tempSQLStorage; etcetc...

However

<blockquote>

"someVariableFromLoop"

</blockquote>

is a large amount of text which includes all kinds of horrible code breaking characters. quotation marks etc etc.

So i looked into parameters (the way i should be doing SQL i know, i know), however i was unable to find a way to store these parameters inside the loop. i dont want to hit the DB every single iteration. I had a go at something along the lines of

<blockquote>

"@variable"+i.toString();

</blockquote>

but could not get it to work at all.

So does anyone have any idea how i would go about storing the parameters and the execute the query? Thanks in advance!

Answer1:

<em>So i looked into parameters (the way i should be doing SQL i know, i know), however i was unable to find a way to store these parameters inside the loop. i dont want to hit the DB every single iteration. I had a go at something along the lines of "@variable"+i.toString(); but could not get it to work at all.</em>

Well, what was the error you received? Because that's the way you do it. Here's an example for MSSQL and I know the technique works, because I've done similar before:

int i = 0; List<string> clauses = new List<string>() {"(@key0, @value0)"}; List<SqlParameter> paramList = new List<SqlParameter> { new SqlParameter("@key0", DBNull.Value), new SqlParameter("@value0", "asd") }; for (i = 1; i < listSize; i++) { clauses.Add("(@key" + i + ", @value" + i + ")"); paramList.Add(new SqlParameter("@key" + i, someKey)); paramList.Add(new SqlParameter("@value" + i, someValue); } SqlConnection conn = new SqlConnection(connString); SqlCommand command = new SqlCommand(conn, @"INSERT INTO table1 VALUES " + String.Join(", ", clauses); foreach(SqlParameter param in paramList) command.Parameters.Add(param); command.ExecuteNonQuery();

Note, above code is quick and dirty. Obviously using statements and various other best practices should be incorporated as well for production code.

Also look at this: <a href="https://stackoverflow.com/questions/6804657/how-do-you-use-the-mysql-in-clause" rel="nofollow">How do you use the MySql IN clause</a>. It has an example of dynamically creating and passing parameters to the query, but for an SELECT...IN clause vs. INSERT...VALUES.

Answer2:

To ensure secure code (and avoid malformed queries), use SQL Command objects with Parameters. There is nothing horribly wrong with executing the command once for every record - a little extra overhead for round-trips over the network, but if the text is long you might have to do this anyway since queries do have a character limit.

Recommend

  • Bash remove forward slash
  • Jackson Parser can't read backslash quotation marks in String
  • Cmd to powershell replace - special character
  • What happens technically when a file is required in Ruby?
  • ST3 swap priority of tab function within a snippet (nested snippets)
  • Question mark icons showing up for quotation marks when there's a UTF-8 character encoding
  • Creating inner query in hibernate
  • Java tree with columns
  • Sql Server 2008 sp_executesql syntax help - I think my quotes are not correct
  • How to enable Drag a Marker (Android Map Api v2) after a single Touch?
  • How do I fix “The program issued a command but the command length is incorrect.” error when calling
  • Error Processing Request: Mage registry key “_singleton/inchoo_notes/feed_updates” already exists
  • Cuda Clang and OS X Mavericks
  • KnockoutObservableArray with typed elements in TypeScript
  • SonarQube: Cannot deactivate rule with missing quality profile
  • pyodbc doesn't report sql server error
  • Unable to install Git-core+svn by MacPorts
  • Moving Android View and preventing onDraw to be called over and over again
  • Unable to decode certificate at client new X509Certificate2()
  • The plugin 'org.apache.maven.plugins:maven-jboss-as-plugin' does not exist or no valid ver
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • NetLogo BehaviorSpace - Measure runs using reporters
  • Spring security and special characters
  • Launch Runnable Jar from Web Start
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • MySQL WHERE-condition in procedure ignored
  • JSON with duplicate key names losing information when parsed
  • Web-crawler for facebook in python
  • Transpose CSV data with awk (pivot transformation)
  • trying to dynamically update Highchart column chart but series undefined
  • C# - Getting references of reference
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Append folder name and increment by 1 using batch script
  • Is there any way to bind data to data.frame by some index?
  • How can i traverse a binary tree from right to left in java?
  • Android Heatmap on canvas or ImageView
  • Conditional In-Line CSS for IE and Others?
  • java string with new operator and a literal