Coded UI Data Driven automation - Working with excel data using OLEDB connection

//Create and excel sheet in your machine and copy the path to Data source in connection string if the HDR property is set to YES.Then the deader of the excel column can be specified in the SQL statements 

//The string below needs to be there for establishing OLEDB connection for .Xls files

string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myexcel.xls;" +
@"Extended Properties='Excel 8.0;HDR=Yes;'";

//Creating a Oledb connection and passing the connection string
using (OleDbConnection connection = new OleDbConnection(con))
{
connection.Open();//Need to open the connection first

//Create command object and pass connection object and sql statements as arguments to perform operations 

OleDbCommand command = new OleDbCommand("update [Hisham$C1:C6] set Salary = '25000'",connection);

//This statement is for executing the above statement and put the data in data reader object

OleDbDataReader dr = command.ExecuteReader()

//Below loop is just to read all dataq from the data reader and write on the console
while (dr.Read())//Loop used to output the data from data reader
{
       var row1Col0 = dr[0];
        var rowcol1 = dr[1];
       Console.WriteLine(row1Col0);
       Console.WriteLine(rowcol1);
}

connection.Close();//Close the connection or rather cleaning
}

}

----------------------------------------------------------------------------------------------------------------------------------

//Below statements are oledb statements using for crud operations in C# Excel data processing

//Select all the data from the sheet 1

//OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);

//Update the values in columns C1 to C6 in sheet1
OleDbCommand command = new OleDbCommand("update [Sheet1$C1:C6] set Salary = '25000'",connection);

//Fetch the data from column A4 of sheet1
//OleDbCommand command = new OleDbCommand("select * from [Sheet1$A4:A4]", connection);

//Update the Name and Salary of the employee with ID 's'
//OleDbCommand command = new OleDbCommand("Update [Sheet1$] Set salary='10000',name='Rajesh' where id='s'", connection);