Monday 29 April 2013

How to Increase performance of the windows application using background thread.

In Win-forms, it is always a performance challenge to retrieve data from database when data is more than ten thousand rows, as there is no pagination is involved. So question always comes that How to increase performance???  One of the solutions is to make use of background thread, which is  bring least number of rows from main thread and then use background thread to bring remaining data, until all data comes by background thread user will be busy in seeing main thread data rows.

Scenario:

In this example, I have to show 70 K rows in the datagridview, but to bring this much of data is always gives performance issues. So, here multithreading can be used. I am going to bring 1k rows using main thread then 69K rows using background thread. Until my background thread bring 69K of rows user will be busy with 1K of rows.


Using the Code:

Here, I have a datagridview and label, datagridview will display data and label will show message so that user will knows that still data is going to come from database.

Windows Forms is [STA] single-threaded Architecture. All operations on all windows and controls happen on the same thread, and all their events fire on that same thread. Because we are using another thread to access gridview and label which is own by main thread which created the button in the GUI, will throw an exception. So, we need to use BeginInvoke Method to use controls of another thread. For more reference http://msdn.microsoft.com/en-us/library/a06c0dc2.aspx

Here Row_Number() method is used to bring next 69K rows from database and merge it with 1k rows which is already present in dataset.

Steps to implement:
Step 1: AddàNew ProjectàWindows form application (in windows tab)
Step 2: Add Datagridview and label control in the form.
Step 3: After that, in form load method we are retrieving initially 1K rows from database.
Step 4: Now a thread will be created as a background thread. This background thread will bring all the remaining data from database.  
To assign dataset to gridview from background thread, need to use begin invoke method.

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Threading;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        /* Global variables*/
        string strSqlconnection = @"Data Source=VICKY-PC\sqlexpress;Initial Catalog=DB_NEW;Integrated Security=True";
        DataSet1 ds = new DataSet1();
        DataView dvds = new DataView();
        bool blnIsBackGround = false;

        /*form load */
        private void Form1_Load(object sender, EventArgs e)
        {
            /* this query will bring top 1000 rows from database.*/
            FillData("SELECT  top(1000) ROW_NUMBER() over (order by ID) as rowNumber,City_Code.CODE,City_Code.CITY FROM City_Code");

            dvds.Table = ds.Tables["City_Code"];
            dataGridView1.DataSource = ds.Tables["City_Code"];
            label1.Text = " Number of rows in gridview is " + dataGridView1.RowCount + "  Searching for more .......";

            /* Create new thread and creating handler of the thread*/
            Thread BackThread = new Thread(RunBackGroundThrad);
            /* Make thread as background thread*/
            BackThread.IsBackground = true;
            blnIsBackGround = true;
            /* Start background thread*/
            BackThread.Start();

        }

        /* this method will retrive data from database.*/
        public void FillData(string strSqlCommand)
        {
            SqlConnection questionConnection = new SqlConnection(strSqlconnection);
            questionConnection.Open();
            String sql = strSqlCommand;
            SqlDataAdapter adapter = new SqlDataAdapter(sql, questionConnection);
            if (blnIsBackGround)
                dataGridView1.BeginInvoke(new MethodInvoker(delegate
                { adapter.Fill(ds, "City_Code"); }));
            else adapter.Fill(ds, "City_Code");

        }

        /* this handler will be called by background thread as soon as background thread starts*/
        /* this method will retrive data from data base by background thread. */
        public void RunBackGroundThrad()
        {
            /* this query will bring all the rows after 1000 rows .*/
            FillData(@"select rowNumber,City_Code.CODE,City_Code.CITY from City_Code
                    inner join 
                    (
                    select ROW_NUMBER() over (order by ID) as rowNumber, *   from city_code) as a on City_Code.ID=a.ID
                    where rowNumber > 1000");

            /* as we are accessing form control from another thread, thats why we need to use beginInvoke method. */
            dataGridView1.BeginInvoke(new MethodInvoker(delegate
               {
                   dataGridView1.DataSource = null;
                   dataGridView1.Refresh();
                   DataView dvdsClone = new DataView();
                   dvdsClone.Table = ds.Tables["City_Code"];
                   dataGridView1.DataSource = dvdsClone;
               }));

            label1.BeginInvoke(new MethodInvoker(delegate { label1.Text = " Number of rows in gridview is " + dataGridView1.RowCount; }));

        }
    }
}

 










 

 

Multithreading is a really powerful feature. This article is only a small sample to demonstrate how you can improve the performance in windows application using a background thread.

About Author
Vinay Gupta works with Systems Plus and is working on Dot Net technology projects. He can be contacted at:vinay.g@spluspl.com

Wednesday 24 April 2013

Export SSRS report directly to PDF or Excel Format(without Report Viewer)

In this post, we are explaining how SSRS report can be directly exported to PDF or Excel.

Show PDF report accepts 3 parameters, report is the name of the report on the report server, reportParameters is the parameter required for the report to run, fileName is the name in which you want the report to be stored

public static void ShowPdfReport
(string report, Dictionary<string, object>  reportParameters, string fileName)
  {
    try
     {
      Screen screen;

      //Get the server and the server path from Web config

      string reportUrl = ConfigurationManager.AppSettings["server"];
      reportUrl += "?" + ConfigurationManager.AppSettings["serverPath"];
      string reportParametersQT = String.Empty;
     
     
      foreach (var entry in reportParameters)
      {
        reportParametersQT += "&" + entry.Key + "=" + entry.Value;
      }

/*Creating a web request using the reportUrl,the report,the format in which the report is to be displayed and the report parameters.
Here rs is a built in parameter of Reporting Services and I am instructing reporting services to render(rs:Command=Render)the report in PDF format(rs:Format=PDF),to export the report in Excel all you have to do is change the format to (rs:Format=Excel)*/

WebRequest req = WebRequest.Create(reportUrl + report +  "&rs:Command=Render&rs:Format=PDF" + reportParametersQT);

//Passing the credentials of the report server

req.Credentials = new NetworkCredential(ConfigurationManager.AppSettings["username"], ConfigurationManager.AppSettings["password"]);

//Creating a response object

      WebResponse response = req.GetResponse();
      Stream stream = response.GetResponseStream();
      screen.Response.Clear();
string enCodeFileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);

// The word attachment in Addheader is used to directly show the save dialog box in browser
screen.Response.AddHeader("content-disposition", "attachment; filename=" + enCodeFileName);
      screen.Response.BufferOutput = false;   // to prevent buffering
      screen.Response.ContentType = response.ContentType;
      byte[] buffer = new byte[1024];
      int bytesRead = 0;
      while ((bytesRead = stream.Read(buffer, 0, buffer.Length)) > 0)
       {
          screen.Response.OutputStream.Write(buffer, 0, bytesRead);
       }
       screen.Response.End();
     }
     catch (Exception e)
     {
     }
}

About Author
Saju M Verghese works with Systems Plus and is working on Dot Net technology projects.
He can be contacted at: saju.v@spluspl.com

Wednesday 17 April 2013

SEO - For Dummies

Although SEO is being talked about in today's Social World, still people do not have proper idea on what SEO is? and how it works? Do you not wonder how actually Search Engine (Google, Yahoo, Bing etc) show relevant results based on query you  have entered. I have written small article for people who wants to get a brief idea about SEO is and its importance in today’s digital life. Before we get started on this, let’s first know what SEO is and what SEO is not.

What Is SEO ?


Whenever you enter a query in a search engine and hit 'enter' you get a list of web results that contain that query term. Users normally tend to visit websites that are at the top of this list as they perceive those to be more relevant to the query. Did you ever wonder why some of these websites rank better than the others? Then you must know that it is because of a powerful web marketing technique called Search Engine Optimization (SEO).

Search Engine Optimization means a technique that allows a site to get more traffic from search engines (Google, Yahoo, Bing). SEO is a part of Internet Marketing. SEO techniques helps search engines to find and rank your site higher than the millions of other sites in response to a search query.

What SEO Is Not
Most of the people have misunderstanding that user has to register with Google to get their site listed in google by paying sum of money. But SEO is nothing to do with purchasing the number 1 sponsored link through Google and saying that you have a number 1 ranking on Google. Purchasing paid placements on search engines is nothing but Search Engine Marketing (SEM) and not SEO. Have you ever noticed first and last two web links on Google result page appear different than rest in the middle? It is because the first and last two web links are nothing but SEM results while links in middle section are nothing but result of SEO
      

Even though there are so many sites that has same information but SEO will not rank number 1 rank for your site name, because search engines are usually smart enough to award you that rank by default. Your site ranking depends on various factors and these factors are nothing but SEO. So let’s see how SEO works.

How SEO works
The first basic truth you need to know about SEO is that, search engines are not humans. Search engines are text-driven software’s. First search engines crawl the Web to see what is there. This task is performed by a piece of software called a crawler or a spider (or Googlebot in case with Google). Spiders follow links from one page to another and index everything they find on their way. Having in mind the number of pages on the Web (over 20 billion) it is impossible for a spider to visit a site daily just to see if a new page has appeared or if an existing page has been modified, so sometimes crawlers may not end up visiting your site for a month or two.

Now let’s see what a crawler sees from your site. As already mentioned, crawlers are not humans so they do not see images, Flash movies, JavaScript, frames, password-protected pages and directories, so if you have tons of these stuff on your site, your site will not viewable, they will not be spidered, not indexed, not processed, etc. in a word they will be non-existent for search engines.

After a page is crawled, the next step is to index its content. The indexed page is stored in a giant database from where it can later be retrieved. Essentially, the process of indexing is identifying the words and expressions that best describe the page and assigning the page to particular keywords. For a human it will not be possible to process such amounts of information but generally search engines deal just fine with this task. Sometimes they might not get the meaning of a page right. So we need to provide proper meta tags and description with our site doing this, it will be easier for crawler to classify your pages correctly and for you to get higher rankings. Uses of meta tags and descriptions with proper keywords, provide a quick summary of the main content on the page. In some cases, this may come up as the page description on a search result.


When a search request comes, the search engine processes it i.e. it compares the search string in the search request with the indexed pages in the database. Since it is likely that more than one page (practically it is millions of pages) contains the search string, the search engine starts calculating the relevancy of each of the pages in its index with the search string. There are various algorithms to calculate relevancy. Each of these algorithms has different relative weights for common factors like keyword density, links in sites, meta tags. That is why different search engines give different search results pages for the same search string. What is more important to known  is the fact that all major search engines like Yahoo!, Google, Bing, etc. periodically change their algorithms and if you want to keep your site at the top, you also need to adapt the latest changes to your pages. You should also know that search engines also track user behaviour on your website and when users stay on your website, they consider it to be more relevant. The more visitors you get to your site, the more often it will come up in searches.

The last step in search engines activity is retrieving the results. Basically, it is nothing more than simply displaying them in the browser – i.e. the endless pages of search results that are sorted from the most relevant to the least relevant sites.

Why SEO is Important in today’s business world?
SEO is nothing but a process of making your website visible on the 1st few page of major search engines mainly Google. Through SEO, you have the opportunity to beat your competitors by securing one of the top ten positions on page 1!
You may ask, so what if my website doesn’t appear on the 1st page? The answer is simple, studies shows that around 90% search engine users don’t look beyond the first page of the search results. So let’s say your product is searched 100 times a day. If your site doesn’t appear on the first page, then you will instantly lose around 90% of potential customers. In today’s cyber world 90% of internet users use search engines to find products that they want to buy. So if you are not on the 1st page, then most of your potential buyers on the World Wide Web will never even about your existence. So if you don’t appear on the first page, then you will lose an unimaginable amount of money and your competitors will enjoy tremendous exposure on the internet. That is why SEO is very very very very very important for every business these days.

As a small business owner, you probably have a tiny marketing budget and want to do marketing for your product/services on to wider audience then he should go for SEO. The beauty of search engine optimization is, it’s completely free if you are doing it by your own. Beyond this, search engine optimization will provide your business with a long history of leads without any recurring costs. For example, if you optimize your site effectively, visitors can find you years and years. If you actually spend money on advertising the same message in newspaper, magazine ads and radio commercials it would all been out of print or in the trash by same day or time.

In the end i would like to say Search Engine Optimization is not rocket science. Search engine algorithms change regularly, so the tactics that worked last year may not work this year. SEO requires a long-term outlook and commitment. SEO isn’t about instant gratification. Results often take months to see. As you can see… SEO is not that hard BUT it is absolutely critical to your success on the World Wide Web. You need to make sure that you create a powerful and viable SEO strategy for your website and reap the benefits of your hard work and efforts in the long run. Ultimately, you should consult with a professional search engine optimization service provider to make the most of your plans. We at Systems Plus can help you do that!

About Author:
Manish Chandak is social media enthusiast and works as business analysts with Systems Plus. He in free time reads and write on various technologies and process around social media, analysis and testing. He can be contacted at: manish.c@spluspl.com

Monday 8 April 2013

Excel VBA also known as Macros.

Now a day’s office programs are playing very vital part in our work and personal life.

We do use almost all office programs in our daily routine like Work, Excel, PowerPoint, and Outlook.
By using these programs we prepare lots of documents, presentations, huge excel files with so complicated formula to achieve what we want.

However, still sometimes we are unable to achieve few things even by using complex Excel Formula and to achieve those we spend lots of our time.
Those are actually pretty much quick and easy to achieve by using macros.

What is macro?
A macro is a set of commands that can be run in the back to perform a given task with a single click. These tasks may be something as simple as calculating 2 numbers into a cell or more complex.
So you can automate almost everything in office program, even you can perform such task which you might not even know they are possible.

So when we speak about the macros, people think that it’s programming and I am not from programming background or a developer to write fine macros.
Well pal you are not 100% right, but yes you are partially right MACROS are programming, but you do not need to be from programming background or a developer to use them.

Macros can be created in office programs and they are written in a language called Microsoft Visual Basic for Applications, usually know as VBA.

When and why we should use macros?
Macros save time and extend capabilities of the programs we use every day. You can use them to automate document production tasks, simplify bulky tasks. When you will be well-versed with VBA can use macros to create your own add-ins that can include templates, dialogue boxes, and even store information for frequent use.

Let’s take an example: For formatting multiple tables in a document. Let’s say that there are 50 tables in your document that need to be reformatted.
Even as a proficient user will take more than four hours for that one task but you can take just five minutes to format each table, that is. If you record a macro to format the tables and then edit that macro to repeat the changes throughout the document, you can complete that task in minutes rather than hours.

How to created macros?
In many Office programs, you can create a macro either by recording a series of actions or by writing the macro by yourself.

Record Macro
This is the simplest way to create macro, just turn tape recorder on (or off) while you are performing your actions.
When you use macro recorder, a macro is automatically written for you based on the actions you take.

In Office, you can record macros in Word, Excel, PowerPoint, Visio, and Project. I know it is exciting  for you are you are ready to kick start.

For example, let’s take a look at recording a macro for the first task as I mentioned at the beginning of article - formatting several tables in a Word document. You can apply a table style to accomplish a lot of table formatting, but you might also need to apply formatting that cannot be part of a style, such as the width of the tables or the height of table rows.

Let’s begin

To record this macro, start with your insertion point in the first table you want to format, and then do the following:
  • Office 2003 - On the Tools menu; click Macro and then click Record New Macro.
  • Office 2007 onwards – ON Developer tab click Record New Macro.
  • In the Record Macro dialogue box, as shown here, you can name the macro, assign it to a toolbar or keyboard shortcut for easy access, customise where the macro is saved, and add a description of the macro for later reference. Or, if you prefer, you can skip all of those steps for now and just click OK to begin recording.

  1.  You can rename the automatically assigned numeric name for your macro. Macro names can include letters and numbers, but no spaces.
  2. By default, new macros you record are saved in the global template named Normal.dot. You can also save the macro in the active document or template, or in another custom template.
  3. You can assign a macro to be accessible from a toolbar or with a keyboard shortcut. If you skip this part, you can make these assignments anytime after creating the macro.
  4. The macro description includes the date and name of the user who is recording the macro. You can edit this description as needed.


After you click OK, in office 2003 a small toolbar will open containing two buttons,

Stop Recording  and 


Pause Recording .
In office 2007 onwards we will get both these button in Developer tab

  • Take each of the steps you need to format your table. For example, you might apply a table style, set the width of the table to be 50% of the available page width, select the table, and then remove row height settings from all table rows. When you have finished applying any formatting that will be the same for all of the tables that you need to format, click the Stop Recording button.
  • You can now click into any table in your document and then run this macro to automatically repeat all of the actions you took in the first table.


Note we have not assigned the macro to a toolbar or keyboard shortcut, you can access it through the Macros dialogue box.
To do this
In office 2003 on the Tools menu, click Macro and then click Macros. Select your macro from the Macro name list and then click Run.
In office 2007 onwards, on Developer tab click Macros. Select your macro from the Macro name list and then click Run.

That’s it you have created your first macro and its quick a simple and fun too.

So let’s now start creating macro by other way, writing our own macro.

Tip A good way to begin learning VBA is to record a macro and then look at the macro in the Visual Basic Editor.
To do this, in office 2003 on the Tools menu click Macro, and then click Macros. Select your recorded macro from the Macro name list and then click Edit.
In 2007 onwards do to Developer tab and click on Visual Basic button.

Well before we start writing macro just security information as macros can be harmful to you system in case in case they are written by someone else and you do not know the use of it.

What about macro security?
It is a fact that, while most macros are both harmless and helpful, macros are an important security issue. When created with malicious intentions, macros can contain destructive code that causes harm to your documents or your system.

To protect your system and your files, do not enable macros from unknown sources. In order to have the option to enable or disable macros, but still have access to any macros you want to use, set macro security in your Office programs to Medium. This will provide you with the option to enable or disable macros anytime you open a file that contains a macro, but will allow you to run any macros you choose.

To set macro security in any Office program that offers VBA macros follow below steps
In Office 2003

  • On the Tools menu click Macro
  •  Click Security
  •  Select your preferred Security Level and then click OK.
Note that setting security to Low is not recommended.

In Office 2007 onwards

  •  On the Developer tab click Macro Security
  •  Select your preferred Security Level and then click OK.


So to begin with simple macro, we can open excel and write below code in macro editor (Visual Basic Editor).
To open macro
In office 2003 – Click on Tools, then select Visual Basic.
In office 2007 onwards – go to Developer tab and click Visual Basic.

This example will show Hello World text in cell A1.


Sub Macro1()
    'Showing “hello world” text in A1 cell
    Range("A1").Value = "Hello World :)"
End Sub


Another very simple addition of 2 numbers calculation using macros


Sub Macro1()
    'Adding 2 numbers to cell C1
    Range("C1").Value = Range("A1").Value + Range("B1").Value
End Sub


So similarly you can write your own macros and run them, you can also assign them to buttons and then you are just a click away from a magic.

The simplest way is to add buttons from the Forms Toolbar - display this from the View Menu > Toolbars & click Forms.
  • Click on the Button icon on the menu & the cursor will hang to +, click on the sheet to add a button
  •  The Assign Macro Dialogue will show listing available macros, in the "Macros in:" box select This Workbook for ease.
  • Now simply click on a macro to assign it.


I will show you more complex macros and more tips in my next article.

Till then enjoy writing MACROS

About Author:
Harshad Pednekar is budding technology geek, who helps Systems Plus with his creativity and research on technology. He works in Systems Plus and actively contributes to technology. To read more interesting articles from him , please follow: http://harshadpednekar.blogspot.in