query: Oledb database and a BindingList
-
goal: Display all records from a specified table in a Visual Foxpro database in a DGV. This would not be a problem, except, i only want those rows which have a record id contained by a BindingList TableId I've tried this:
OledbConnection = new OleDbConnection(connectionString); OledbCommand = new OleDbCommand(query,connection); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connection); foreach(string id in TableId) { query = "SELECT * FROM " + tableName + " WHERE (" + tableRecordIDColumn + " = '" + id + "') ORDER BY " + tableRecordIDColumn; command.CommandText = query; dataAdapter.SelectCommand = command; dataAdapter.Fill(dataTableFromDb); } bindingSource1.DataSource = dataTableFromDb; dataGridView1.AutoGenerateColumns = true; dataGridView1.DataSource = bindingSource1;Allthough this works, it's extremely slow. Especially since my BindingList TableId could contain 20 000 record id's. Would there be any way to create a dataset with two tables on wich i could excecute a select query wich an inner join on the record id's?? And then use the results as the DataGridView.DataSource? grts, thx -
goal: Display all records from a specified table in a Visual Foxpro database in a DGV. This would not be a problem, except, i only want those rows which have a record id contained by a BindingList TableId I've tried this:
OledbConnection = new OleDbConnection(connectionString); OledbCommand = new OleDbCommand(query,connection); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connection); foreach(string id in TableId) { query = "SELECT * FROM " + tableName + " WHERE (" + tableRecordIDColumn + " = '" + id + "') ORDER BY " + tableRecordIDColumn; command.CommandText = query; dataAdapter.SelectCommand = command; dataAdapter.Fill(dataTableFromDb); } bindingSource1.DataSource = dataTableFromDb; dataGridView1.AutoGenerateColumns = true; dataGridView1.DataSource = bindingSource1;Allthough this works, it's extremely slow. Especially since my BindingList TableId could contain 20 000 record id's. Would there be any way to create a dataset with two tables on wich i could excecute a select query wich an inner join on the record id's?? And then use the results as the DataGridView.DataSource? grts, thxsharp source, the problem is: you do to many connections to DB. I can propose two different solutions: 1) We can include all of this in one SQL: 'OR' version:
string queryCondition = ""; foreach (string id in TableId) { queryCondition += tableRecordIDColumn + " = '" + id + "' OR "; } // We need to cat ' OR ' from the end of the string (4 letters) if (queryCondition != "") queryCondition = queryCondition.Remove(queryCondition.Length - 4); string query = "SELECT * FROM " + tableName + " WHERE (" + queryCondition + "') ORDER BY " + tableRecordIDColumn;'In' version:string queryCondition = ""; foreach (string id in TableId) { queryCondition += "'" + id + "', "; } // We need to cat ', ' from the end of the string (2 letters) if (queryCondition != "") queryCondition = queryCondition.Remove(queryCondition.Length - 2); queryCondition = tableRecordIDColumn + " in (" + queryCondition + ")"; string query = "SELECT * FROM " + tableName + " WHERE (" + queryCondition + "') ORDER BY " + tableRecordIDColumn;Remark: You can get an exception like "the query is too long" (because of 2000 :omg: entries). You can split them to 100th packs, for example 2) Restricting the logic of data getting (I think it is better) I think you can find an way to get more simple selecting condition >>Would there be any way to create a dataset with two tables on wich i could excecute a select query wich an inner join on the record id's?? You can. You can load all data to data. Then you just need to filter the data by using BindingSource class. But this solution is valid when you have to filter data many times per minute. -- modified at 9:13 Thursday 7th June, 2007 -
sharp source, the problem is: you do to many connections to DB. I can propose two different solutions: 1) We can include all of this in one SQL: 'OR' version:
string queryCondition = ""; foreach (string id in TableId) { queryCondition += tableRecordIDColumn + " = '" + id + "' OR "; } // We need to cat ' OR ' from the end of the string (4 letters) if (queryCondition != "") queryCondition = queryCondition.Remove(queryCondition.Length - 4); string query = "SELECT * FROM " + tableName + " WHERE (" + queryCondition + "') ORDER BY " + tableRecordIDColumn;'In' version:string queryCondition = ""; foreach (string id in TableId) { queryCondition += "'" + id + "', "; } // We need to cat ', ' from the end of the string (2 letters) if (queryCondition != "") queryCondition = queryCondition.Remove(queryCondition.Length - 2); queryCondition = tableRecordIDColumn + " in (" + queryCondition + ")"; string query = "SELECT * FROM " + tableName + " WHERE (" + queryCondition + "') ORDER BY " + tableRecordIDColumn;Remark: You can get an exception like "the query is too long" (because of 2000 :omg: entries). You can split them to 100th packs, for example 2) Restricting the logic of data getting (I think it is better) I think you can find an way to get more simple selecting condition >>Would there be any way to create a dataset with two tables on wich i could excecute a select query wich an inner join on the record id's?? You can. You can load all data to data. Then you just need to filter the data by using BindingSource class. But this solution is valid when you have to filter data many times per minute. -- modified at 9:13 Thursday 7th June, 2007Your 2 first suggestions i allready tried. And as you say, i need to limit the data getting. Btw, it's not 2000 but 20 000 :wtf: rows.... So restricting the logic of data getting. How would i do that? So i create a dataset Add two DataTables OledbDataAdapter.Fill(DataTable1); How do i get the BindingList TableId into a table? And after I have a dataset with the two tables... I gues i can figure that out. Thx
-
Your 2 first suggestions i allready tried. And as you say, i need to limit the data getting. Btw, it's not 2000 but 20 000 :wtf: rows.... So restricting the logic of data getting. How would i do that? So i create a dataset Add two DataTables OledbDataAdapter.Fill(DataTable1); How do i get the BindingList TableId into a table? And after I have a dataset with the two tables... I gues i can figure that out. Thx
>> And as you say, i need to limit the data getting. Do you want to get a code with splitting by 1000 (for example) Ids? >> So restricting the logic of data getting. I meant "restructuring", sorry. So change the logic of the app. To group some ID into groups... I need to see you task to give you more ideas. >> How do i get the BindingList TableId into a table? >>And after I have a dataset with the two tables... I gues i can figure that out. No, you will have one table with full data in it. But you will display only necessary information. To do that you have to bind you table to DataGridView (for example) through BindingSource:
BindingSource bs = new BindingSource(someDataSet, "SomeMember"); someDataGridView.DataSource = bs; // then in an other place you can filter: Filter condition, like: bs.Filter = "SomeColumn = 'id1' OR SomeColumn = 'id2' OR ..... ";