How to search value from txt file through C#

Question

Dear, How can i search value from txt file without loop and if there is record more than million and how to search fast within one second. Please i need your help i am trying to dovelope inventory applicaiton in hend held scanner but record search should be fast and quick result.

Nitin Sontakke · Answer

You can try something as follows: private void uploadData() { using (StreamReader SourceFile = new StreamReader(path, true )) { string line = "" ; StringBuilder sb = new StringBuilder(); Int32 batchSize = 500; Int32 count = 0; while ((line = SourceFile.ReadLine()) != null ) { sb.Append( "Insert into MasterFile (barcode) Values ('" + line.Replace( "," , "','" ) + "')" + Environment.NewLine); // Why line.Replace? One will line have one barcode, I think! count++; if (count % batchSize == 0) { cmd = new SQLiteCommand(sb.ToString(), con); cmd.ExecuteNonQuery(); count = 0; sb = new StringBuilder(); } } } if(count != 0) { cmd = new SQLiteCommand(sb.ToString(), con); cmd.ExecuteNonQuery(); } MessageBox.Show( "Master File Updated Successfully" ); con.Close(); } Change batchSize as per requirement.

Amit Gupta · Answer

You can use sqliteshell command line argument to import your file. Coming to your snippet, it is heavily a bad performance to execute line by line. However, you can use a bulk insert (suppose 10 lines at a time or any) like Insert into tablename (columnname) vwlues( value1); Insert into table name (columnname) values (value2)... and so on ... Build your query upto 10 lines (as you wish) and then call the execute query command.

Amit Gupta · Answer

You should use some database file instead of txt file, it will give your result as expected

Feroz Khan · Answer

Dear Nitin Sir, I tried this code but still slow very slow data inserting very late more then two hours if there is data 79000 rows. @Nanddeep, i trying this code but problem visual studio 2008 that code not accepting not accepting file.readAllLine(); can you please check if there is another way to insert fast from txt file to database.

Nanddeep Nachan · Answer

LINQ should help you for faster search from text file. // Read all lines from text file string [] lines = File.ReadAllLines(path); // Get lines with text "Books" lines = lines.GroupBy(x => "Books" );

Nitin Sontakke · Answer

My response is going to rely on the version you are using. That's why I am asking. The latest the version you are using better the solution I can give. Hope you get that point.

Nitin Sontakke · Answer

How sure are you about this? Can you paste a screen shot here or something like that. Considering the latest version is 3.22 that sounds a bit too odd (and too old!) to me. In fact, when I check that version is not even listed here.... https://sqlite.org/chronology.html

Feroz Khan · Answer

i am using sqlite 0.8.3.2 version.

Nitin Sontakke · Answer

Which version of SQLite are you using?

Feroz Khan · Answer

Thank you so Much Nitin Sir and Mr.Amit I tried with ExecuteScalar its working perfect thanks once again. and one more thing before i was using txt file but now i am using database but problem is now i need to udpate txt in Database if there is data more than half million in txt file just one column how can i update in database faster. please need your advice. i am using below code but its very slow. StreamReader SourceFile = new StreamReader(path, true); string line = ""; Int32 counter = 0; while ((line = SourceFile.ReadLine()) != null) { if (counter > 0) { query = "Insert into MasterFile (barcode) Values ('" + line.Replace(",", "','") + "')"; cmd = new SQLiteCommand(query, con); cmd.ExecuteNonQuery(); } counter++; } MessageBox.Show("Master File Updated Successfully"); con.Close();

Amit Gupta · Answer

@Feroz As you have to check only its its existence then you can use IF EXISTS clause or use LIMIT 1 keyword. Moreover, have you tried indexing your table and checked running the query in SQLite IDE?. I believe that you will get much better performance. Do try above and let me know, if it works

Feroz Khan · Answer

Dear Amit and Nitin Sir, Yes there is tow columns and first one is primary column s_no. and second for barcode and yes i need to check just that barcode exit or not and if exist then i updating in another table with qty.

Amit Gupta · Answer

@Feroz Definitely, an indexed table can boost the performance and help to fetch data faster. As stated by @Nitin sir, you are having only 2 columns, there isn't any primary column? which is used to identify unique column. If you want single data to be fetched then use ExecuteScalar function. Have you checked fetching data using sqlite IDE, is it taking same timespan? If yes then do index your db that will slightly have much better performance. https://medium.com/@JasonWyatt/squeezing-performance-from-sqlite-indexes-indexes-c4e175f3c346

Nitin Sontakke · Answer

Another question is, are you just cheking if barcode exists or not? Is that right?

Nitin Sontakke · Answer

Okay. Understood. The moment you mention SQLite, it goes slightly out of my domain. Sorry! However, I would like to comment of couple of things. First, what is the column S_No in the table? Is it a primary key of the table? I quickly saw the documentation of SQLite and it says that every table MUST have a primary key. Is this some sort of identity column? What is the business purpose of that column? Second, do you have option of something like ExecuteScalar in SQLClient library. Hope you understand. If you have that option, please use that instead of Adaptor approach. Adaptor approach is heavy on memory. Imagine you are creating a data table just for one value.

Feroz Khan · Answer

attached file also.

Feroz Khan · Answer

Dear Nitin Sir, Actually i am developing application for hand held scanner for Inventory my simple question is when i scan item should be check in database and show me result fast within 1 or 2 second and i am using sqllite database and there just two column one for serial number and second for barcode and below simple code i am using for search record. query = @"select BarCode from Masterfile where barcode = '" + txt_Barcode.Text + "'"; oda = new SQLiteDataAdapter(query, con); dt = new DataTable(); oda.Fill(dt);

Nitin Sontakke · Answer

Issues such as these must be approached from wide variety of angles. You didn't mention which database you are using / tried. Was there a index on the table. How many columns were there in this table, etc. So where is data currently residing? and where is source coming from? It is not clear why you are saying 'value from txt file'. Please clarify a bit.

Amit Gupta · Answer

It won't take that much time, ut depends upon the way it fetched, you have to share, which database you are using and what code you had imolemented.

Feroz Khan · Answer

Hi Amit, Thank you for your reply i already checked with Database also but i can search record but its coming late in 12 to 13 second i want should be fast like 1 or 2 second if there is million records.

Khaja Moizuddin · Answer

i haven't worked on this , but can share you some references please go through it once https://stackoverflow.com/questions/12856471/c-sharp-search-string-in-txt-file http://www.codedigest.com/CodeDigest/121-Fetching-specific-words-from-a-Text-file-using-c-.aspx

Feroz Khan · Answer

Dear Nitin Sir, I think no issue in Sqllite DataBase actually i am using Visual Studion 2008 because i am doveloping application in Windows CE Device so i need just code for Update Barcode file in Database through C# code but i was trying with bulk insert which not accepting in Visual Studion 2008. please i need your help to insert txt file data in Database table there is only one column in database also and txt file also. please see picture database and txt file.

Nitin Sontakke · Answer

Okay. If the first column (S_No) has no business purpose, I suggest you only keep barcode and make it a primary key.