reading n-th row in oledbdatareader in c#

Question

I have a excel sheet showing employees salary details in department wise. Just I need to save these details in to MySql .

When I use oledbdatareader,it reads from the first row. But I have to choose from different rows. Below im showing sample excel sheet

Dept    Software         
Name    Gross   Deductions  NetPay
AAA    10000    2000        8000
BBB    10000    1000        9000
Dept    HR       
Name    Gross   Deductions  NetPay
CCC    20000        1000     19000

Here each line is a row. I have to take third row ,fourth row and then last row (in this example).

AAA    10000    2000        8000
BBB    10000    1000        9000
CCC    20000    1000       19000

How can i achieve this? I tried like this.

protected void Button2_Click(object sender, EventArgs e)
  {           

   string path = "C:\\Payslip.xls";
   string query = "SELECT * FROM [Sheet3$]";
   OleDbConnection conn = new OleDbConnection();
   conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = '" + path + "'" + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";
   conn.Open();              

try
{
        OleDbCommand ocmd = new OleDbCommand(query, conn);
        OleDbDataReader odr = ocmd.ExecuteReader();             

        while (odr.Read())
        {                               
                name = odr[0].ToString();            
                gross = odr[1].ToString();     
                ded = odr[2].ToString();                
                net = odr[3].ToString();        

                connection = new MySqlConnection(connectionString);
                connection.Open();

                String sQuery = "insert into salary (EmployeeName, Gross) values(@a, @b)";

                MySqlCommand cmd = new MySqlCommand(sQuery, connection);
                cmd.Parameters.AddWithValue("a", name);
                cmd.Parameters.AddWithValue("b", gross); 

                cmd.ExecuteNonQuery();  
                connection.Close();
            }    
      }    
      catch (Exception ex)    
      {
          Label1.Text = ex.Message;                        
      }                 
}

No built in support from `DataReader` to read a specific row, you have to do modular arithmetic yourself to achieve this.. — Hari Prasad, Jun 05 '15 at 09:48

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

1

You can use OleDbDataAdapter to read all rows to fill a data set. And then select a row by index in the datatable of this dataset. An example to use OleDbDataAdapter is,

DataAdapter.Fill(Dataset)

edited May 23 '17 at 11:59

Community

1
1

answered Jul 02 '15 at 08:55

HarryQuake

163
1
13

reading n-th row in oledbdatareader in c#

1 Answers1