Read Data From Excel files in C#.Net

Read Data From Excel files in C#.Net:

The below sample code will help you to get data from excel sheet. You can use same code in both Winforms and web applications.  Different type of provider available to read different type of the excel version. Here I have given two type of provider to read excel sheet. One is old file format XLS excel file reader (Excel 2007) and another one is latest file format of XLSX file reader (Excel 2010).

using System.Data.OleDb;

 //Reading from XLS file (Office 2007 and before)

        private void button1_Click(object sender, EventArgs e)

        {

            try

            {

                OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\FileName.xls;Extended Properties='Excel 8.0;HDR=Yes;'");

                OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);

                DataSet ds = new DataSet();

                da.Fill(ds);

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

        //Reading from XLSX file (Office 2007 & After)

        private void button2_Click(object sender, EventArgs e)

        {

            try

            {

                OleDbConnection con = new OleDbConnection(@"Provider= Microsoft.ACE.OLEDB.12.0;Data Source=D:\FileName.xlsx; Extended Properties='Excel 12.0;HDR=YES;'"); 

                OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);

                DataSet ds = new DataSet();

                da.Fill(ds);

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

The above given example of reading my local hard drives excel sheets file first page data to my C# data set. If you want to change sheet you can specify the select statement [Sheet2$].

Thanks to for giving this example  Mr.Antony.

most frequently used sql queries

In the SQL query frequently most of the developers used the below query’s for day to day needs.

Get the top Nth value in the table.

--Get top 3 rd value

select min (salary) from EmpTable where Salary in (select top 3 salary from EMPTable)

How to select duplicate records in SQL.

--Select Duplicate value

select CName,count(*) from MyTable group by CName having count(*) >1

How to DELETE duplicate value from the Data TABLE using SQL Query.

--Delete dublicate value

delete from MyTable where ID not in(select MIN(ID) from MYTable group  by CName)

Create batch file to run exe file

I want to run my .Net application in the client system. But I don’t know client have .NetFramework or not.  Without .NetFramework  my application will not start. To solve this problem we can use two different types. One is while creating the setup file we need to add the prerequisites,  Another one is creates a batch file and run the necessary installations.

The below Batch file will help you to run the entire exe file in your folder.  In the below example NewFolder have all my pre installation setups. Like My application (MyApp.msi) and SQLExpress2005.exe. So I want to run my entire prerequisite one by one. For that the below Batch file will help me to achieve this.


Install.bat file:
---------------------------------------------------------------------------------
ECHO Installation Started
START  /WAIT  c:\windows\system32\notepad.exe  
CD NewFolder
START  /WAIT  "\NewFolder\"  MyApp.msi
START  /WAIT  "\NewFolder\"  SQLExpress2005.exe
cd..
START  /WAIT  setup.exe

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


Explain:

Run from the direct exe path:
START  /WAIT  c:\windows\system32\notepad.exe  

Run in the root folder: 
START  /WAIT  MyApp.msi

Run inside the folder:
CD   NewFolder
START  /WAIT  "\NewFolder\"  MyApp.msi

Go back to root folder:
CD..

Windows application interview questions

Winforms interview questions :

The below are the interview questions for 5 and 6+ experience person's in visual studio.Net..

1. About constrains.
2. WSDL
3. About Assemply
4. How to use Delay in C#
5. What is Aggration, Composition, Association in C#. Net
6. About Application pool
7. Difference between Dock and anchor
8. How to release unmanaged code in C#. Net
9. What is Com Component
10. Difference between Data Reader and Data Adapter
11. How to export data to Excel in C#
12. How to configure Proxy in your application
13. What will happen the below SQL trigger.
Trigger--> Inside one more Trigger --> And one more trigger
If trigger 3 have some expection means what will happen.

14. Tell me Background workers Three events ?
15. Write simple print string function using Oops concept in C#. Net .
16. What is Statict class?
17. With out constructer can i call the class ?.
18. Explain the type of Webservices.
19. Explain SDLC

In MS SQL Interview Questions

TableA

ID Name

1 vijay

2 antonu

3 yuvan

4 sekar

TableB

Id Address

1 Saidapet

2 Matha Nagar

2 Madipakkam

3 DPM

What was the output of Below Query?

select * from TableA,TableB

Result: 16 Rows 

What was the output of below query?

select A.ID,A.Name,B.Address from TableA as A

LEFT OUTER JOIN TableB as B on A.ID=B.Id

Result: 5 Rows 

What was the Result of below SQL query?

select ID Name from TableA

Result: Column Name ID changed to Name



ASP.NET Interview question:

What is JQuery?
What is Lambda expression?
How to do Unit Testing?
Are You using Any Design patterns?
Explain your Project architecture

How to use filter in datatable using C#.Net?

Some time we need to use filter in our normal DataSet or DataTable to get some values.For that the below example will explain how to use filter in DataTable using C#.Net.

 private void dtFilter()
{
    DataTable dt = new DataTable();
    dt.Columns.Add(new DataColumn("ID"));
    dt.Columns.Add(new DataColumn("Name"));

    dt.Rows.Add("2", "Vijay");
    dt.Rows.Add("1", "Kumar");
    dt.Rows.Add("3", "Yuvan");
    dt.Rows.Add("3", "Vijay");


Filter the records from a datatable in C#:

    DataRow[] dtRow = dt.Select("Name='Vijay'");
    System.Text.StringBuilder str = new System.Text.StringBuilder();

    foreach (DataRow dr in dtRow)
    {
        str.Append(dr["Name"].ToString()+",");
    }
    Response.Write("Filter Output :: " + str.ToString());


Delete records from a datatable using C#.Net:

    foreach (DataRow dr in dtRow)
    {
        dtRow.Rows.Remove(dr);
    }


}

Call javascript function in code behind using c#.Net

Some time we need to call javascript function in .CS(Code behind) page. The below example will help you to understand the calling javascript function in code behind.  In .CS file you need to register the startup script using script manager. Below example I have register in two ways.


In ASPX.CS File

ScriptManager.RegisterStartupScript(Page, this.GetType(), "callFunctionStartupScripts", "loadMyFunction();", true);

(OR)

string command = "loadMyFunction();";
string script = "";
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "showsmyfunction", script, false);


In the design page (.ASPX) you have to create Javascript function loadMyFunction. While running the program the loadMyFunction will fire.

In ASPX file

<script type="text/javascript">

function loadMyFunction(container) {

try   {

      alert('Test');

} catch (ex) {  }

</script>

Dynamically change iframe src query string using javascript

Some timewe need to assign iframe src query string dynamically. For that we can use document.writeln javascript method. Using window.location.search will give you the query string value. So you need to assign that value to iframe in page load.

HTML Code:

<html>

<script type="text/javascript">

document.writeln('');

</script>

</html>