Can't retrieve data using ExecuteReader() when grouping queries

Question

I need to retrieve the information stored in a database of some thousand items. If I go one by one by this way it takes a large amount of time (tac is a 8-character string):

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DataBase\IMEIDB.accdb";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    connection.Open();
    using (OleDbCommand command = connection.CreateCommand())
    {
        OleDbDataReader reader;
        command.CommandText = "SELECT TAC, Name, Model, Year, Manufacturer, LTE FROM Terminales WHERE TAC = @tac";
        command.Parameters.AddWithValue("@tac", tac);
        reader = command.ExecuteReader();
        while (reader.Read())
        {
            ulong tac = Convert.ToUInt64(reader.GetString(0));
            if (this.DiccionarioTerminales.ContainsKey(tac))
            {
                DiccionarioTerminales[tac].inDB = true;
                DiccionarioTerminales[tac].Name = reader.GetValue(1).ToString();
                DiccionarioTerminales[tac].Manufacturer = reader.GetValue(2).ToString();
                DiccionarioTerminales[tac].Model = reader.GetValue(3).ToString();
                DiccionarioTerminales[tac].Year = reader.GetValue(4).ToString();
                DiccionarioTerminales[tac].LTE = reader.GetValue(5).ToString();
            }
        }
        command.Dispose();
    }
    connection.Dispose();
}

It works well (I know I must use ExecuteNonQuery() if it's only one record, but this example is only a test), but if I try to group the tac 10 by 10 (now tac is a string like 'xxxxxxxx','xxxxxxxx','xxxxxxxx','xxxxxxxx'...) with the next changes in my code...

OleDbDataReader reader;
command.CommandText = "SELECT TAC, Name, Model, Year, Manufacturer, LTE FROM Terminales WHERE TAC IN (@tac)";
command.Parameters.AddWithValue("@tac", tac);

It doesn't enter in the while loop and I don't know why...

Is there something that am I missing or maybe I need to use another method to retrieve those data?

EDIT: Change the format due to the Soner Gönül answer

What is the value being passed in `tac` parameter? By the way - how is it supposed to group anything? It is only used in `where` condition of your query. — Andrey Korneyev, Jan 25 '16 at 12:10
In the first example is a 8-character string, in the second example is a group of 10 8-character string separated by commas into a single string. — Fernando Gallego Fernández, Jan 25 '16 at 12:10
@TimSchmelter if I go tac by tac works, but it takes a long time because there're about 6K different tacs. I grouped it tying to reduce the amount of time spended. — Fernando Gallego Fernández, Jan 25 '16 at 12:21

score 5 · Answer 1 · edited May 23 '17 at 11:44

5

Because when you use this string in IN clause, it will seems like;

IN (xxxxxxxx,xxxxxxxx,xxxxxxxx,xxxxxxxx)

but the right syntax should be

IN ('xxxxxxxx','xxxxxxxx','xxxxxxxx','xxxxxxxx')

That's why it doesn't work. One solution might be, you can split your string with ,, format them with using single quotes and join them , again.

var str = "xxxxxxxx,xxxxxxxx,xxxxxxxx,xxxxxxxx";
var result = string.Join(",", str.Split(',').Select(s => string.Format("'{0}'", s)));

result will be 'xxxxxxxx','xxxxxxxx','xxxxxxxx','xxxxxxxx' and you can use it in your IN clause like;

...TAC IN (@tac)

and

command.Parameters.AddWithValue("@tac", result);

Also don't use AddWithValue as much as you can. It may generate unexpected and surprising results sometimes. Use Add method overload to specify your parameter type and it's size.

Related: Parameterize an SQL IN clause

edited May 23 '17 at 11:44

Community

1
1

answered Jan 25 '16 at 12:11

Soner Gönül

97,193
102
206
364

@FernandoGallegoFernández Only `Add(String, Object)` overload is obsolete. You can use _other_ overloads such as `Add(String, SqlDbType, Int32)`. – Soner Gönül Jan 25 '16 at 12:20
If I ise the `command.Parameters.AddWithValue("@tac", result);` the content of the result string is `"'35582006','35152207','01383700','35291306','86794902','35590206','35925406','35300107','35595206','35663305'"` (as an example) but still don't enter in the while loop – Fernando Gallego Fernández Jan 25 '16 at 12:24
@FernandoGallegoFernández Is it query returns any data in your database manager? Are you %100 sure your `TAC` column has one of _those_ values? – Soner Gönül Jan 25 '16 at 12:28
Yes, because if I go one by one as I told in the first example it works. Even if I use Access with the next query: `SELECT TAC, Name, Model, Year, Manufacturer, LTE FROM Terminales WHERE TAC IN ('35582006','35152207','01383700','35291306','86794902','35590206','35925406','‌35300107','35595206','35663305');` it returns me 2 diferent terminals... – Fernando Gallego Fernández Jan 25 '16 at 12:33
In fact, if I use the thext code in my program `command.CommandText = "SELECT TAC,Name,Model, Year, LTE FROM Terminales WHERE TAC IN (" + result +")";` it works! But this is not well constructed due to SQL-Injection... – Fernando Gallego Fernández Jan 25 '16 at 12:46

Can't retrieve data using ExecuteReader() when grouping queries

1 Answers1