how to show 5 lakhs records into datagridview in less time

Question

hiiiii

i am using visual studio2005.i want to load 5 lakhs Records into data grid view in less time.

currently dataset is taking long time (1 and half min) to fill 5 lakhs records .

is there any way to solve my problem..

thanx in advance.

Answer 1

Hi,

as you are loading a huge amount of records in DataSet, it will definitely take time.

The possible way is to use DataSource Controls in Visual Studio like SqlDataSource and second allow paging in GridView to show paged records like 20 at a time, so the SqlDataSource or OleDbDataSource Control will only load the 10 or 20 records at a time and when you will move to other page, it will load next 20 and so on.

or you can create a StoreProcedure in your database server that  will allow you to retrieve paged records.

Answer 2

hi yazeem
thnx for reply

I have txt file ,having 5-6 lakhs records, i used StreamReader & create DataTable but also takes time.

i can use paging but i want to fill dataset coz this dataset will be used in crystal report as well as in Graph.
i dont want to requry againg for crystal report
& plz tel me with code example if u have.


thnx..

Answer 3

as you are reading huge text file, it will take time. i recommend you to use DataBase for reading huge amount of data.
you can import your data in SQL Server using DTS if the format matched with SQL server table.
if you don't want to use Database like SQL Sever or Oracle. then there is SQLite that many applications are using as a mean to store data.

this allows faster access then reading text files.
visit http://www.sqlite.org/

Answer 4

hi yazeem

thnx for reply.

i used Sqlite it works fine.bt another problem i m facing.that

i m using sqlitecommand .executenonquery statemant for insert query and i have 5 lakhs records so it is executed 5 lakhs times.

i tried a way to solve this problem

code is here

Sqlitecommand=Sqlconn.createcammand()

Sqlitecommand.commandtext-"Insert into tablename (columns names) values(?,?,?..);"

?,?

dim SqlPara as New Sqliteparamter

Sqlitecommand.addparamters(sqlPara)

now in while loop

sqlPara.value="abc"

Sqlitecommand.ExecuteNonQuery()

but results was same i have 34 columns and more than 5 lakhs records

so its time consuming.is there any bulk insert option.???

Answer 5

for bulk operations, you can create parameterized query

like insert into table(col1, col2, col3) values (1,'a',2.1) , (2,'b',4.6) , (3,'c',7.9) ,  (4,'d', 5.2), ..... and so on.

after your query is created , you can then pass this to command object and with single call to DB, you can update your records.

i have created such thing in MySQL but i don't know whether it will work with sqlite or not.. :)

Answer 6

for bulk operations, you can create parameterized query

like insert into table(col1, col2, col3) values (1,'a',2.1) , (2,'b',4.6) , (3,'c',7.9) ,  (4,'d', 5.2), ..... and so on.

after your query is created , you can then pass this to command object and with single call to DB, you can update your records.

i have created such thing in MySQL but i don't know whether it will work with sqlite or not.. :)

hiii

i used csvreader for displaying data through LumenWorks.Framework.IO.Csv.CachedCsvReader found on Net.and
and started a background thread that fills dataset.everthing is working fine.but another problem arise that on 256 RAM its not working ,cpu looks very busy..

thnx,,...