1

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

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?

EXAMPLE:

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")

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?

Improve this question
4
  • I suppose adding the parameters themselves is the easy part. But building the string for the actual query is going to take some logic. Do you have all of the information needed to build it? Show an example of the actual SQL query you're trying to build. For any "unknown" element in the actual query, where would you get the value for that? (Table name, column names, etc.) Commented Jan 11, 2016 at 17:00
  • I have add an example Commented Jan 11, 2016 at 17:04
  • And which components of that query are "unknown"? Presumably you'd have a table name as a string somewhere, as well as a collection of column names and values? You can build the string for the query using those components, mainly by looping over that collection of keys/values. (A simple incrementing value in the loop could serve as the parameter name.) What is that information that you'd be using to build the query? Commented Jan 11, 2016 at 17:07
  • Uhm well the incrementing index is a good idea to start, but as I said I don't know the query structure 'cause there is a lot of value inside this DataRow, so the incrementing index for create the parameter is only a partial solution. Commented Jan 11, 2016 at 17:13

1 Answer 1

Reset to default
2

(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)

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

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

query.Append(someTableName)

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))

(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. 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:

  • 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. 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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Okay for the table name, but how I can recognize the properly query? Let me explain better, how I can create a query that have the structure statement of the table x against table y and so on? How I said, in this list there is a lot of records of different tables, so the problem at this point is the query 'cause I don't know the structure of it. I don't know if I'm clear, exaple: Table 1, record to insert (name and description), Table 2, record to insert (value, duration, service). So how I can create difference string query? Should I pass also the query together the name of the table?
@Sandokan: Well, at some point you need to have enough information to logically build INSERT queries for any given table. If what you have is just a bunch of columns and aren't able to determine which tables they belong to, then you don't have enough information to build a query.
This string.Format("@a{0}", x)) contain "x" variable, but I don't see any valorization, could you exaplain?
@Sandokan: x is the variable used when iterating over the collection in LINQ. Take a look at some VB examples of LINQ extension methods online.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.