How am I suppose to select records from database using list of id inside an array

Question

0.00/5 (No votes)

See more:

I wanted to retrieve database records of every id inside of

list<int> list_of_id = new list<int>();

then store all gathered record of every id that is inside of

list<int> list_of_id = new list<int>();

into

DataTable dt = new DataTable();

I'm looking forward to your suggestion and replies.

What I have tried:

Here is what I've done:
I'm already done with the first requirement which is retrieving all id of non-member from user_register_table and store all of it into

list<int> list_of_id = new list<int>();

using(MySqlConnection con = new MySqlConnection(connectionstring))
          {
           con.Open();
            using(MySqlCommand com = new MySqlCommand("SELECT * FROM 
                  user_register_table WHERE user_status='none-member'",con))
                  {
                    MySqlDataReader reader = com.ExecuteReader();
                    while (reader.Read())
                    {
                       list_of_id.Add((int)reader["register_id"]);
                    }
                    reader.Dispose();
                  }

I converted list<int> items into array;

int[] ints = all_joined_group_id.ToArray();

Last, is to fill

DataTable dt = new DataTable();

with records from

MySqlDataAdapter adp = new MySqlDataAdapter(com)

using (MySqlCommand com = new MySqlCommand("SELECT * FROM clientdebt_table WHERE 
            clientdebt_id IN (@unique_ids)", con))
            {
             com.Parameters.AddWithValue("@unique_ids",ints);
                    using (MySqlDataAdapter adp = new MySqlDataAdapter(com))
                    {
                        adp.Fill(dt);
                       //here is the problem: dt is supposed to have records of every
                       //id inside the ints array;
                    }
            }
      }//end of connection

Posted 24-Mar-18 20:47pm

Green Blanket

Updated 27-Mar-18 4:38am

v4

Add a Solution

3 solutions

Solution 3

Surely you'd do better to use a single query instead of two?

SQL

SELECT 
    * 
FROM 
    clientdebt_table 
WHERE 
    clientdebt_id IN 
    (
        SELECT register_id 
        FROM user_register_table 
        WHERE user_status = 'none-member'
    )

Or:

SQL

SELECT 
    clientdebt_table.* 
FROM 
    clientdebt_table 
    INNER JOIN user_register_table 
    ON user_register_table.register_id = clientdebt_table.clientdebt_id 
WHERE 
    user_register_table.user_status = 'none-member'

Posted 27-Mar-18 4:39am

Richard Deeming

Comments

Maciej Los 28-Mar-18 2:32am

5ed!

Solution 1

Convert Unique_IDs to a comma separated string and change the parameter data type to VARCHAR.

I'm pretty sure ints is not a valid data type in MySQL

Posted 24-Mar-18 21:36pm

Mycroft Holmes

Comments

Green Blanket 25-Mar-18 4:13am

I did this string ids = string.Join(",",ints.Select(x=>x.ToString()).ToArray());
it worked but only first ID gets to insert into table unlike declaring IN(1,2,3,4) where all of it was inserted into the table

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-03-24T22:13:00

Solution 2

Concatenating IDs to a string is a bad idea - it encourages SQL Injection.
A better method is to pass a Table Values parameter: Using Table-Valued Parameters in SQL Server and .NET[^] If you look at the section:

Quote:
Passing Table-Valued Parameters from ADO .NET

It shows how to do it.

Posted 24-Mar-18 22:13pm

OriginalGriff

Comments

Green Blanket 25-Mar-18 4:35am

Hey thank you so much about the link you gave to me!

OriginalGriff 25-Mar-18 4:41am

You're welcome!
(Sorry if you thought I feel like you are arrogant - that wasn't the intention, and if I have given offence, I apologize profusely!)

But you do have to watch out for SQL injection - when you concatenate strings to form an SQL command, you open up your DB to whoever entered the string data - and if that's a user, you are setting yourself up for a world of pain!

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

DROP TABLE MyTable;

A perfectly valid "delete the table" command

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
That's why I mentioned it - Mycroft's solution will work, but it sets a dangerous precedent and should be avoided.

Green Blanket 25-Mar-18 18:24pm

Thank you so much. I also jot down your latest reply. It's really surprising that I got very meaningful reply. This is a first so thank you so much.

OriginalGriff 26-Mar-18 1:49am

You're welcome!