在Web服务中动态创建SQL更新命令(Create a SQL Update Command Dynamically in a Web Service)

在Web服务中动态创建SQL更新命令(Create a SQL Update Command Dynamically in a Web Service)

所以我不知道这是否可能，甚至是可取的，但我试图找出如何在我的web服务中创建一个可以由用户动态构建的更新命令。 基本上，目标是有一个包含x个列的表，并且用户应该能够更新任意数量的列，而不必为未更新的列传递null或现有值。 例如，一个人可能想要跑步

UPDATE calendar_table SET title = 'Some new Title' WHERE (id = 12344)

另一个用户可能想要执行此操作

UPDATE calendar_table SET title = 'New Event', type = 'Meeting', note = 'Meet with new client' WHERE (id = 12344)

所以下面是我试图使用的，但我不断得到一个例外，说明“WHERE”附近有一个问题让我相信这是我试图处理更新字符串的方式

String MyConnStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString; SqlConnection connection = new SqlConnection(MyConnStr); SqlCommand Query = new SqlCommand("UPDATE dbo.calendar_table " + "SET @newText " + "WHERE (id = @appointmentID)", connection); Query.Parameters.Add("@newText", SqlDbType.VarChar).Value = note; Query.Parameters.Add("@appointmentID", SqlDbType.VarChar).Value = appointment; Query.CommandType = CommandType.Text; try { connection.Open(); Query.ExecuteNonQuery(); } catch (Exception ex) { Debug.WriteLine(ex); } finally { connection.Close(); Query.Dispose(); }

任何帮助或想法将不胜感激，因为我能想到的唯一其他选择是创建具有所有可能值的Web服务，然后传递未更改的现有值以及值。

So I don't know if if this is possible, or even advisable, but I was trying to figure out how I could create a Update Command in my webservice that can be built dynamically by the user. Basically, the goal is that there is a table that has x amount of columns and a user should be able to update any number of columns, without having to pass null or the existing values for the columns that aren't being updated. For example, one person may want to run

UPDATE calendar_table SET title = 'Some new Title' WHERE (id = 12344)

And another user may want to perform this action instead

UPDATE calendar_table SET title = 'New Event', type = 'Meeting', note = 'Meet with new client' WHERE (id = 12344)

So below is what I was trying to use, but I keep getting an exception stating that there is a problem near "WHERE" which is making me believe that it's something with how I'm trying to treat the update string

String MyConnStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString; SqlConnection connection = new SqlConnection(MyConnStr); SqlCommand Query = new SqlCommand("UPDATE dbo.calendar_table " + "SET @newText " + "WHERE (id = @appointmentID)", connection); Query.Parameters.Add("@newText", SqlDbType.VarChar).Value = note; Query.Parameters.Add("@appointmentID", SqlDbType.VarChar).Value = appointment; Query.CommandType = CommandType.Text; try { connection.Open(); Query.ExecuteNonQuery(); } catch (Exception ex) { Debug.WriteLine(ex); } finally { connection.Close(); Query.Dispose(); }

Any help or ideas would be greatly appreciated since the only other option I can think of is just create the web service with all the possible values and then either pass the existing values that aren't being changed along with the values.

最满意答案

您的代码将假定知道将为给定更新设置哪些字段。 因此，您可以将每个字段动态添加到SET子句和Parameters集合中。 使用每个字段的参数也可以避免注释中提到的SQL注入问题。

根据示例代码的语法，我假设它是C＃。 下面是一种可以处理更新用户定义的变量字段的方法。

假设存在包含用户输入的值的变量： userInputTitle ， userInputType ， userInputNote等。

id字段包含在SET子句中，用于删除是否在SET子句中的每个动态添加字段之前添加逗号的问题。 作为其副作用，即使用户未定义任何字段，也可以运行更新。

顺便说一句，使用use而不是显式尝试清理实现IDisposable接口的对象被认为是更好的做法，因此下面的示例会相应地重写。 using语句将关闭连接并处理连接和命令。 此外，使用Parameters.AddWithValue()可以避免指定参数的数据类型。

我还没有测试过这段代码。

String MyConnStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString; using (SqlConnection connection = new SqlConnection(MyConnStr)) { using (SqlCommand Query = new SqlCommand()) { try { Query.Connection = connection; String sql = "UPDATE dbo.calendar_table SET id = @appointmentID"; // The has-field-been-updated-by-user logic below assumes the // fields are strings. Different logic might be necessary // for other data types. if (!String.IsNullOrWhitespace(userInputTitle)) { sql += ", title = @title"; Query.Parameters.AddWithValue("@title", userInputTitle); } if (!String.IsNullOrWhitespace(userInputType)) { sql += ", type = @type"; Query.Parameters.AddWithValue("@type", userInputType); } if (!String.IsNullOrWhitespace(userInputNote)) { sql += ", note = @note"; Query.Parameters.AddWithValue("@note", userInputNote); } // Additional fields... sql += " WHERE (id = @appointmentID)"; Query.Parameters.AddWithValue("@appointmentID", appointmentID); Query.CommandText = sql; Query.CommandType = CommandType.Text; connection.Open(); Query.ExecuteNonQuery(); } catch (Exception ex) { Debug.WriteLine(ex); } } }

Your code will assumedly know which fields will be set for a given update. Therefore, you can dynamically add each field to the SET clause and to the Parameters collection. Using parameters for each field also avoids the SQL-injection issues mentioned in the comments.

Based on the syntax of your sample code, I'm assuming it's C#. Below is one way you could handle updating a variable, user-defined number of fields.

Assume that there are variables containing values that the user has input: userInputTitle, userInputType, userInputNote, etc.

The id field is included in the SET clause to remove the question of whether or not to add a comma before each dynamically added field in the SET clause. As a side effect of this, the update can be run even if the user hasn't defined any fields.

By the way, it's considered better practice to use using instead of explicitly trying to clean up objects that implement the IDisposable interface, so the example below is rewritten accordingly. The using statements will close the connection and dispose of the connection and command. Also, using Parameters.AddWithValue() avoids the need to specify the data types of the parameters.

I have not tested this code.

String MyConnStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString; using (SqlConnection connection = new SqlConnection(MyConnStr)) { using (SqlCommand Query = new SqlCommand()) { try { Query.Connection = connection; String sql = "UPDATE dbo.calendar_table SET id = @appointmentID"; // The has-field-been-updated-by-user logic below assumes the // fields are strings. Different logic might be necessary // for other data types. if (!String.IsNullOrWhitespace(userInputTitle)) { sql += ", title = @title"; Query.Parameters.AddWithValue("@title", userInputTitle); } if (!String.IsNullOrWhitespace(userInputType)) { sql += ", type = @type"; Query.Parameters.AddWithValue("@type", userInputType); } if (!String.IsNullOrWhitespace(userInputNote)) { sql += ", note = @note"; Query.Parameters.AddWithValue("@note", userInputNote); } // Additional fields... sql += " WHERE (id = @appointmentID)"; Query.Parameters.AddWithValue("@appointmentID", appointmentID); Query.CommandText = sql; Query.CommandType = CommandType.Text; connection.Open(); Query.ExecuteNonQuery(); } catch (Exception ex) { Debug.WriteLine(ex); } } }