C # Dynamic SQL INSERT ... SELECT

Question

I use the INSERT ... SELECT syntax to select existing rows from a table and then insert into another table. In addition to the existing data from each row, I also need to add BillingID and TimeStamp. Since these fields are in the SELECT part of the SQL statement, I cannot parameterize them. I solved the TimeStamp problem with the SQL NOW () function, however, I still remain with BillingID, which I added to the query via sting concatenation as follows:

static void UpdateMonthlyData(int BillingID, DateTime HistoryDate, int CompanyID) { String conString = ConfigurationManager.ConnectionStrings["xxx"].ConnectionString; MySqlConnection connection = new MySqlConnection(conString); String command = "INSERT INTO MonthlyData SELECT " + BillingID + ", d.*, NOW() " + "FROM CurrentData d WHERE d.CompanyID = @CompanyID AND d.HistoryDate = @HistoryDate"; MySqlCommand cmd = new MySqlCommand(command, connection); cmd.Parameters.Add(new MySqlParameter("@CompanyID", CompanyID)); cmd.Parameters.Add(new MySqlParameter("@HistoryDate", HistoryDate)); cmd.CommandType = CommandType.Text; cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } 

I'm not interested in SQL Injection, as it is a console application that runs in an automatic schedule and has no user interaction. (BillingID is automatically generated). Despite this, I do not like to use concatenated strings as they are not very readable. Is there a more elegant way to do this?

Edit:

To summarize, I thought that since you cannot do this:

 SELECT @field FROM @table 

I suggested that parameters are not allowed in the SELECT part of the SQL statement. However, since I am specifying a value in the select expression instead of selecting a column, as @cdhowie noted, I can use the parameter there. Essentially, my translation is translated as follows:

 SELECT 25 FROM table_name, not SELECT field FROM table_name 

So now thanks to @cdhowie I understand that the parameter can be anywhere, the literal value can be

Answer 1

The query parameter is valid wherever the literal value should be, assuming that the query parameter is of the correct type (e.g. ... LIMIT @Foo should work as long as you bind an integer) or something that SQL- the server can successfully convert to an integer - to the parameter Foo). Of course, this does not imply any particular quirks in the SQL dialect that you use.

In other words, there is no reason why you cannot pass BillingID using the query parameter.

Answer 2

You can use String.Format for any safe fields that cannot be added using parameterization:

 String command = String.Format("INSERT INTO MonthlyData SELECT {0}, d.*, NOW() FROM CurrentData d WHERE d.CompanyID = @CompanyID AND d.HistoryDate = @HistoryDate", BillingID); 

Answer 3

You can use .Net string.format

Example:

  string query = string.format{"insert into MonthlyData select {0} from CurrentData", BillingId); 

You can parameterize your query much more with cleaner code.

Good luck.