I don't know how to import text file to datagridview then edit afterthat write to database with Linq.

Question

Hello friend and expert  I have text file like this 48200120420110830I around 5000 record . The text file from bar code swipe machine. The 48200 stand for employee code  12042011 stand for 12 April 2011  0830 stand for time 08:30 AM and I stand for In/Out I=In I coding to get this text file by stream reader as code below openFileDialog1.FileName = ; openFileDialog1.Filter = Text Files(*.txt)|*.txt; if(openFileDialog1.ShowDialog() == DialogResult.OK) { label1.Text = openFileDialog1.FileName; StreamReader sr = new StreamReader(openFileDialog1.FileName); After read the text file I would like to write the text file to Datagridview before write to Database. I design table in database like this. Emp_code nvarchar(2) Morning_IN datetime Morning_Out datetime Afternoon_In datetime Afternoon_Out datetime Evening_IN datetime Evening_Out datetime But I so confusing and I have question to asking for help as below. 1) How can I I write the text file (5000 record ) to Datagridview as my database table design ? Because the text file format have a lot of duplicate employee code ? ( please forgive me if I not a good explain ) Emp_code Morning_IN Morning_Out Afternoon_In Afternoon_Out Evening_In Evening_Out 2) I intend to use the Linq to SQL classes . Please teach me how to write the text file to Datagridview and then can allow to edit data at Datagridview. After that click at the button to save to database . Anyone help me please and this will me the most appreciated. Pang Foon (Thailand )

Vulpes · Answer

If you have 5000 records I wouldn't use LINQ to SQL to insert them one by one into the database which will be very slow. Instead I'd use SqlBulkCopy which will probably be fast enough for your purposes. I see that you've already created a table in the database. You'll also need to create a DataTable object to use as a conduit between the text file and the database. There's a C# example here ( http://weblogs.sqlteam.com/mladenp/archive/2006/08/26/11368.aspx )

Sam Hobbs · Answer

See: ADO.NET in C# There are over 400 articles there many of which will help you.

Morcos Adel · Answer

well as i told you before i am not much an expert in the Linq to sql classes but i could help you by giving you the concept to use : the first thing that comes to mind is that you can add the parameters to the update statement while reading the file....but i know its not the perfect solution for your case..but it i think it will work !!

natchaya khamkuna · Answer

Could you please help me more: If I now don't want to import to Datagridview but direct to Database . database easy create like this: id(bigint) auto increment Emp_code(varchar5) clock(datetime) IO(varchar 2) my code as below : private void btnOpenRead_Click(object sender EventArgs e) { try { openFileDialog1.FileName = ; openFileDialog1.Filter = Text Files(*.txt)|*.txt; if (openFileDialog1.ShowDialog() == DialogResult.OK) { label1.Text = openFileDialog1.FileName; StreamReader sr = new StreamReader(openFileDialog1.FileName); ClockRecord = sr.ReadLine(); while (ClockRecord != ) { day = ConvertNumber(ClockRecord.Substring(5 2)); month = ConvertNumber(ClockRecord.Substring(7 2)); year = ConvertNumber(ClockRecord.Substring(9 4)); hour = ConvertNumber(ClockRecord.Substring(13 2)); minute = ConvertNumber(ClockRecord.Substring(15 2)); dt = new DateTime(year month day hour minute 0); ClockRecord = sr.ReadLine(); } } }catch (Exception ex) { MessageBox.Show(ex.Message); } } static int ConvertNumber(string text) { int i; if(Int32.TryParse(textout i)) return i; MessageBox.Show(Invalid data:  text); return 0; } ______________________ I now can separated the string But I don't know how to write to database . for example I try : (Linq) Times t = new Times(); t.Emp_Code = ClockRecord.Readline.Substring(52); This will write to database only one record. I don't know how to make a loop . Since do not have line to count . Expert help me please .. Pang (Thailand)

Sam Hobbs · Answer

You do not provide enough details of the input format to be able to provide specific help with that but if it is a CSV format then you can use one of very many possible solutions. If the file is a CSV format then my Using CSV and Other Delimited Files in C# article is one possibility but there are many others. I suggest reading the file in and writing it to a table in the database. It will be easier to manage that way. You can use something like in my Database Table Update in a DataGridView without Writing Code article to edit the data and write it back.

Morcos Adel · Answer

Well... i think that i can help you till the part of the Update :) coz i am not much an expert in the linq to sql classes.... i think as far as i understood you already read the text file if the text file is devided line by line or is separated by commas you can read it line by line or till the next comma and Save the read value in a string or something and then cut it out by using .substring(start_indexLength) that is if the numbers count you provided is constant for all employees. after deviding into strings you can add the data you have into a data table like the following: String x=here I am; DataTable Data=new DataTable(); // Create Data Table DataColumn Column=new DataColumn(column name); //Creating Columns Data.Columns.Add(Column); //Adding Columns To Table DataRow Row=new DataRow(); //Creating Row Row.beginedit(); //Begin Row Editing Row[column name]=x; //Add The Value to The Row of [column name] Row.EndEdit(); //End Edit Data.Rows.Add(Row); // Adding the Rows Created to the DataTable datagridview.datasource=Data; // Bind the Data Grid and you can select the type of the column when creating it :) Soo i hope that this can solve your first problem of displaying the Text in the Grid