Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

code to improve

Posted on 2009-04-24
7
Medium Priority
?
302 Views
Last Modified: 2013-11-05
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

0
Comment
Question by:WebAppDeveloper
  • 3
  • 3
7 Comments
 
LVL 3

Accepted Solution

by:
Aleksei_Malkov earned 2000 total points
ID: 24231013
Hi,
1. You don't need to read total count of rows as separate action. If there is no rows  - then DataReader with slides will be empty.
2. Don't set connection string twice, don't open connection twice
3. don't resize array by setting row number.
4. If you use .net 2.0 - you can use generic class List<> - it can dynamically resized and converted to simple array on return.

Source code snipped is enclosed, may contain small misprints as i don't compile it
    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");
                
        List<AjaxControlToolkit.Slide> slides = new List<AjaxControlToolkit.Slide>();
        
        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();
                using (DbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        slides.Add(new AjaxControlToolkit.Slide("uploadedimages/" + dr["ImageFile"].ToString(), dr["ImageTitle"].ToString(), dr["ImageDesc"].ToString()));
                    }
                }
            }                
        }
        return slides.ToArray();
    }

Open in new window

0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24231130
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
0
 

Author Comment

by:WebAppDeveloper
ID: 24231517
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:WebAppDeveloper
ID: 24231585
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.


0
 
LVL 3

Assisted Solution

by:Aleksei_Malkov
Aleksei_Malkov earned 2000 total points
ID: 24231729
If ImageFile is a string (string cell in excel) to my mind it will be better to add this condition to sql query like:
where ImageFile <> '' and ImageFile is not null
or
where Len(ImageFile) >= 5

Here 5 represents minimum length of the ImageFileName - name + extension.

Thus you'll avoid empty strings.
0
 

Author Comment

by:WebAppDeveloper
ID: 24234094
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.
0
 
LVL 3

Assisted Solution

by:Aleksei_Malkov
Aleksei_Malkov earned 2000 total points
ID: 24234541
Hi,
If file can be missing you should check it on existance.
if (File.Exists(xlsPath))
{
...
}
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses
Course of the Month13 days, 3 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question