C# WinForm Excel check empty sheets function-works but too slow(15 sheets 30 seconds)

Question

I have a working Function that checks if sheets from an excel file are empty.

This function is called for every sheet. The function works perfectly. The only problem is that if my Excel file has 50 sheets, calling this function takes like 30 seconds which is too much.

What can i do to optimize this function, so the function to be called once for all sheets in excel? Shall i use linq or still OLEDB.

P.S I will not use Interlop as it triples times to check.

This is my function

public static bool isEmptySheet2(string nameSheet)
        {
            string filePath = Properties.Resources.ResourceManager.GetString("FilePath");
            string fileName = Properties.Resources.ResourceManager.GetString("FileName");
            string fileLocation = filePath + fileName;
            try
            {
                DataTable dt = new DataTable();
                string connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
    "Data Source=" + fileLocation + ";" + "Extended Properties=" + "\"" + "Excel 12.0 Xml;IMEX=1;HDR=NO;" + "\"";
                OleDbConnection conn = new OleDbConnection(connstr);
                string strSQL = "SELECT * FROM [" + nameSheet +"] WHERE [F17]='OK';";
                // string strSQL = "SELECT * FROM [" + "ThisIsSheet5$D4:D4" +"] WHERE [F17]='OK';";



                OleDbCommand cmd = new OleDbCommand(strSQL, conn);

                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(dt);


                foreach (DataRow row in dt.Rows)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        //test for null here
                        if (row[col] != DBNull.Value)
                        {
                            return false;

                        }
                    }
                }

                return true;




            }
            catch (Exception ex)
            {

                Console.WriteLine(ex);
            } return false;
        }

When calling function I enable a button for that sheet.

foreach (var i in sheets)
            {
        if (ExcelUtil.isEmptySheet2(result) == true)
                {

                    //set button false
                    i.btnFood = false;
                }
                else
                {
                    i.btnFood = true;
                }
            }

At the end, i want to enable buttons for that sheet that it is empty or not. The code above is working fine, but

-> I have to call that method for each sheet ->Looping through all that sheet-> opening->checking->closing->opening takes too much time

Answer 1

You open new connection for every sheet. It is not necessary at all. You can pass connection object to inner method. Or you can modify your query to get all sheets in one select, then check every sheet in the application layer from single dt object.

Edit: You can pass connection object like this:

public static bool isEmptySheet2(string nameSheet,OleDbConnection conn)
        {

        try
        {
            DataTable dt = new DataTable();
            string strSQL = "SELECT * FROM [" + nameSheet +"] WHERE [F17]='OK';";
            // string strSQL = "SELECT * FROM [" + "ThisIsSheet5$D4:D4" +"] WHERE [F17]='OK';";



            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);


            foreach (DataRow row in dt.Rows)
            {
                foreach (DataColumn col in dt.Columns)
                {
                    //test for null here
                    if (row[col] != DBNull.Value)
                    {
                        return false;

                    }
                }
            }

            return true;




        }
        catch (Exception ex)
        {

            Console.WriteLine(ex);
        } return false;

}

your outer method:

string filePath = Properties.Resources.ResourceManager.GetString("FilePath");
            string fileName = Properties.Resources.ResourceManager.GetString("FileName");
            string fileLocation = filePath + fileName;
    string connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
        "Data Source=" + fileLocation + ";" + "Extended Properties=" + "\"" + "Excel 12.0 Xml;IMEX=1;HDR=NO;" + "\"";
                    OleDbConnection conn = new OleDbConnection(connstr);
    foreach (var i in sheets)
                {
            if (ExcelUtil.isEmptySheet2(result,conn) == true)
                    {

                        //set button false
                        i.btnFood = false;
                    }
                    else
                    {
                        i.btnFood = true;
                    }
                }