如何在不知道参数数量的情况下创建 MySqlCommand 对象? [英] How to create MySqlCommand object without know the number of parameter?

问题描述

我正在尝试创建 MySQLCommand 对象以对数据库表执行查询.现在我的问题是:

I'm trying to create MySQLCommand object for perform a query on database table. Now my problem is this:

所有记录都存储在这个DataRow列表中:Records，这个列表包含了不同表的记录字段.通常为了在表中添加结果，我定义了一个 MySqlCommand 对象，稍后使用参数化查询传递参数，但是，在这种情况下，我不知道参数的数量，我只有一个列表里面有很多记录.现在，存储此记录的表的名称包含在列表的第一个索引中，因此当我迭代列表时，只需跳过第一个索引并获取所有值.有人可以告诉我这是怎么做的吗?

All records are stored inside this DataRow list: Records, this list contains records field of different tables. Usually for add result inside a table I define a MySqlCommand object, later pass the parameter using the parametized query, but, in this case I don't know the number of parameter, I've only a list with a lot of record inside. Now the name of the table where store this records is contained into the first index of the list, so when I iterate the list just skip the first index and get all values. Someone could tell me how do this?

示例:

Dim query = "INSERT INTO table (name, description) VALUES (@namep, @descriptionp)"
MyCommand = New MySqlCommand(query, my connection here)
MyCommand.Parameters.AddWithValue("@namep", "some value")+
MyCommand.Parameters.AddWithValue("@descriptionp", "another value")

这是我平时做的MySqlCommand，现在想象一下，在我的列表中有很多记录，比如name、description，还有其他的参数询问.如何创建一个类似的结构来添加这个参数?

This is the MySqlCommand that usually I did, now imagine that in my list there is a lot of records like name, description, and also there is parameter of other query. How you can create a similar structure for add this parameter?

推荐答案

(我的VB很生疏，忍耐一下...)

(My VB is very rusty, bear with me...)

大概你有几个值.特别是一个表名和一组表示列名和列值的键/值对.使用这些，您可以为您的查询动态构建字符串.

Presumably you have a couple of values. Specifically a table name and a collection of key/value pairs representing column names and column values. Using these, you can dynamically build the string for your query.

从查询的基本结构开始.像这样:

Start with the basic structure of the query. Something like this:

Dim query As New StringBuilder()
query.Append("INSERT INTO ")
' TODO: append table name
query.Append(" (")
' TODO: append column names (loop)
query.Append(") VALUES (")
' TODO: append parameter names (loop)
query.Append(")")
Dim MyCommand As New MySqlCommand(query.ToString(), someConnection)
' TODO: append actual parameters (loop)

让我们一次一个地浏览每个 TODO 组件...

Let's go through each of the TODO components one at a time...

1. 表名是比较容易的部分，因为不涉及循环.假设它在一个变量中:

1. The table name is the easy part, since no looping is involved. Assuming it's in a variable:

query.Append(someTableName)

2. 您可以调整各种循环的性能，但为了简单起见，现在让我们仅对键/值对循环 3 次.(我们实际上将使用内部具有循环的内置方法，但重点仍然存在.)对于列名，假设您有一个字符串数组:

2. You can tweak performance of the various loops, but for simplicity right now let's just loop over the key/value pairs three times. (We'll actually use built-in methods which internally have loops, but the point remains.) For the column names, let's assume you have an array of strings:

query.Append(string.Join(",", someColumnNamesArray))

(注意:使用 string.Join() 而不是手动循环的原因是为了避免对是否包含 ","<执行一些潜在的丑陋逻辑/code> 第一个/最后一个条目的分隔符.)

(Note: The reason to use string.Join() instead of manually looping is to avoid having to do some potentially ugly logic for whether or not to include the "," separator for first/last entries.)

3. 对于参数名称，只需使用数字即可.我不知道参数是否允许以数字开头，所以为了安全起见，让我们随意让它们以 "a" 开头，然后是数字.您可以使用 Enumerable.Range() 来获取数组长度的整数列表.所以是这样的:

3. And for the parameter names, just use numbers. I don't know if parameters are allowed to begin with a digit, so to play it safe let's arbitrarily have them begin with an "a" and then the digit. You can use Enumerable.Range() to get a list of integers the length of your array. So something like this:

query.Append(string.Join(",", Enumerable.Range(0, someColumnNamesArray.Count()).Select(Function(x) string.Format("@a{0}", x))))

我承认这是一段看起来很复杂的代码.但它的主要作用是:

It's kind a complex-looking piece of code, I admit. But what it basically does is:

• 创建从 0 到列名数组长度的整数范围.
• 根据这些整数选择一个格式化的字符串，创建一个字符串列表，如 "@a0"、"@a1" 等.
• 使用 string.Join() 将该列表转换为单个逗号分隔的字符串.

• Create a range of integers from 0 to the length of the array of column names.
• Select a formatted string based on those integers, creating a list of strings like "@a0", "@a1", and so on.
• Use string.Join() to turn that list into a single comma-separated string.

如果它看起来更干净，请随意在多行上执行此操作.

Feel free to do that on multiple lines if it looks cleaner to you.

那时您应该有您的查询.像这样:

At that point you should have your query. Something like this:

"INSERT INTO someTable (someColumn, anotherColumn, andAnother) VALUES (@a0, @a1, @a2)"

4. 然后您可以遍历您的值数组以添加您的参数:

4. Then you can loop over your array of values to add your parameters:

For i As Integer = 1 To someValuesArray.Count()
    MyCommand.Parameters.AddWithValue(string.Format("@a{0}", i), someValuesArray[i])
Next

这篇关于如何在不知道参数数量的情况下创建 MySqlCommand 对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！