asked on

code to improve

Hi,

Can someone tell me if the attached code can be improved, shortened or optimized? If so, please make the necessary adjustments for me.

The code is located in a content page (of a master page).

Thanks very much in advance.

<asp:ScriptManager ID="ScriptManager1" runat="server" />
    
    <script runat="Server" type="text/C#">
    [System.Web.Services.WebMethod]
    [System.Web.Script.Services.ScriptMethod]
    public static AjaxControlToolkit.Slide[] GetSlides()
    {
        HttpContext context = HttpContext.Current;
        string xlsPath = context.Server.MapPath("~/uploadedFiles/slideshow.xls");
        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
        int totalRows = 0;
        
        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
        
        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = connectionString;
            
            using (DbCommand command = connection.CreateCommand())
            {                
                // Slides$ comes from the name of the worksheet
                command.CommandText = "SELECT COUNT(*) AS TotalRows FROM [Slides$]";
                connection.Open();
                
                using (DbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        totalRows = Convert.ToInt32(dr["TotalRows"].ToString());
                    }
                }
            }
        }
        
        AjaxControlToolkit.Slide[] slides = new AjaxControlToolkit.Slide[totalRows];
        
        if (totalRows > 0)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;
 
                using (DbCommand command = connection.CreateCommand())
                {            
                    command.CommandText = "SELECT (SELECT COUNT(*) FROM [Slides$] s2 WHERE s2.ImageFile <= s.ImageFile) AS RowNumber, ImageFile, ImageTitle, ImageDesc FROM [Slides$] s ORDER BY ImageFile";
                    connection.Open();
                    int i = 0;
                    
                    slides = new AjaxControlToolkit.Slide[totalRows];
                    
                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            slides[i] = new AjaxControlToolkit.Slide("uploadedimages/" + dr["ImageFile"].ToString(), dr["ImageTitle"].ToString(), dr["ImageDesc"].ToString());
                            i++;
                        }
                    }
                }                
            }
        }
        return slides;
    }
    </script>

Open in new window

ASKER CERTIFIED SOLUTION

Aleksei_Malkov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anurag Thakur

what i dont like is that you are doing the operations by using an excel file
instead i will recommed you to use an XML file and instead of 2 sections of querying on excel you can achieve the same quite easily and efficiently by using an XML file

Bobby X

ASKER

ragi0017,

Yes, I agree with you. The reason why I chose to use an excel file instead of an XML is that the third- party Admin Tool (written in C# that I'm modifying) does not accept any XML file upload; it only accepts excel format. So this excel file allows the user to enter/define their slideshow info in there along with the image name of each slideshow, and there's also an Admin Interface (Tool) that allows this same user to upload the corresponding slide images (as defined in the excel) into the system.

Bobby X

ASKER

Hi Aleksei_Malkov,

Wow, the code you revised for me is definitely much better and more optimized than mine. I have not tried the revised code yet, but I'll try it tomorrow. Is there a way to validate and make sure that the user entered a value in the "ImageFile" column of the excel spreadsheet? In other words, if a row in the spreadsheet does not have a value entered for "ImageFile", I don't want it to be in the array.

SOLUTION

Aleksei_Malkov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bobby X

ASKER

Aleksei_Malkov,

Great. Should I also check if the excel file "slideshow.xls" exists in the "~/uploadedFiles" folder before continuing with the operation?
---> string xlsPath = context.Server.MapPath("~/uploadedFiles/slideshow.xls");

Thanks.

SOLUTION

Aleksei_Malkov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial