Overblog Follow this blog
Administration Create my blog
Balavardhan Reddy Narani

.text { mso-number-format:\@;..."> Export Gridview data to Excel

February 18 2010 , Written by Balavardhan Reddy Published on #ASP.Net

i)  Get the Data from the Database and Bind it to the Gridview
ii) Set Gridview Allowpaging property to "False"

Write the below code to export the entire Gridview data to Excel sheet


 private void ExportGridView()
        {
                string style = @"<style> .text { mso-number-format:\@; } </style> ";
                string attachment = "attachment; filename=Contacts.xls";
                Response.ClearContent();
                Response.AddHeader("content-disposition", attachment);
                Response.ContentType = "application/ms-excel";
                StringWriter sw = new StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);
          
                // Create a form to contain the grid
                HtmlForm frm = new HtmlForm();
                VerificationTrackingGridView.Parent.Controls.Add(frm);
                frm.Attributes["runat"] = "server";
                frm.Controls.Add(VerificationTrackingGridView);
                frm.RenderControl(htw);

                Response.Write(style);
                Response.Write(sw.ToString());
                Response.End();

        }
Read more

Dotnet 3.5 New features

February 18 2010 , Written by Balavardhan Reddy Published on #ASP.Net

New Improvements in Dotnet 3.5 and VS 2008:
    Below is the queck summary lis of sever of new improvements of Dotnet 3.5, there is lot of more thing, I haven't had chance to add them

  • Multi Targetting
    • VS 2008 supports to create the applications with different Dotnet Framework versions, whenever you createting a New Project on VS 2008, it will shows you select the framework version for that project.
  • Rich Web Designer and CSS Support, Split view mode
    • To design a Web Page in VS 2005 have only 2 view modes i.e. Source (shows the HTML code) and Design (design mode of the Page). In the VS 2008 we have 3 view modes Source, Design and Split (like Dreamweaver software)
  • CSS style Manager
    •  VS 2008 supports a new tool window inside the IDE called "Manage Styles".  This shows all of the CSS stylesheets, and their corresponding rules, for the page you are currently editing.  It can be used both when you are in design-view, as well as when you are in source view on a page
  • Fullly Nested Master Page support
    • The good news is that VS 2008 fully supports nested master pages, and makes using them super easy.  Going forward I recommend that almost all ASP.NET projects should advantage of this feature - since it can add tremendous flexibility to the UI of your projects.
  • Javascript Intelisence
    • One of the things you'll notice immediately when you start typing within a script block is the richer support that Visual Studio 2008 now has for JavaScript keywords and language features
    • Obviously you get full intellisense support within external JavaScript files, just like you do within script blocks inside .htm and .aspx files.
  • Java script Debugging
    • One of the other JavaScript features that in VS 2008 is the much-improved support for JavaScript debugging.
      VS 2008 makes this much better by adding new support that allows you to set client-side JavaScript breakpoints directly within your server-side .aspx and .master source files
  • Creating Re-Usable ASP.NET AJAX Behaviors, Controls and Libraries
  • Automatic Properties
    • (get and set Properties), Object Initializers (set the Properties values), and Collection Initializers (List collection initialization)
    • One of the things that I think we will really love that are some of the new language features and improvements that are coming to both VB and C#.  These language changes are going to improve our development experience in both subtle and dramatic ways, and will really improve productivity and reduce the amount of code we need to type.
  • Extension Methods
    • Extension methods allow developers to add new methods to the public contract of an existing CLR type, without having to sub-class it or recompile the original type
    • Ex :
      public static class myclass
      {
          public static Dataview tablesort(Datatable dt, string sortcolumn, string sortdirection)
          {
              dt.DefaultView.sort = sortcolumn + " " + sortdirection
              return dt.DefaultView
          }
         
         Public bool emptyornot(string st_text)
         {
             if(st_text == "" || st_text == NULL)
                return false;
             else
               return true;
         }
      }

      public class mainclass
      {
          public void bindgrid()
          {
              DataTable dt = getdata();
              gv_details.Datasource = dt.tablesort(dt, ViewState["sortColumn"].ToString(), ViewState["sortDirection"].ToString());
              dv_details.Databind();
          }

          public void validate()
          {
               bool result = txtuname.emptyornot();
          }
      }

  • Lambda Expressions
    • Provide a complete functinal syntax of writing for anonymous methods, these are useful when writing LINQ expressions
    • Ex : Class :

      public class address
      {
          public string hno {get; set;}
          public string street{get; set;}
          public string city {get; set;}
          public int pinno {get; set;}
      }


      add data

      List <address> addresses = new List<address>
      {
          new address {hno = "18/a/1",street="3/1",city ="Hyd",pinno =500080}
          new address {hno = "10/s/3",street="2/a/1",city ="Hyd",pinno =500004}
          new address {hno = "102/r/1",street="5",city ="Secbad",pinno =500102}
      };


      IEnumerable<address> result = addresses.where(a => a.city == "Hyd");

      here (a => a.city == "Hyd") is Lamda Expression, using this Lamda expression to specify the filter to use when retriving the address
      In C# a lambda expression is syntactically written as a parameter list, followed by a => token, and then followed by the expression


      Query Syntax (LINQ : Language Integrated Query )

      It's declarative shorthand for expressing the queries using LINQ query operators. It offers a syntax that increases the readability and clarity of expressing query in the code, and it can be easy to read and write correctly.


      public class address
      {
          public string hno {get; set;}
          public string street{get; set;}
          public string city {get; set;}
          public int pinno {get; set;}
      }


      add data

      List <address> addresses = new List<address>
      {
          new address {hno = "18/a/1",street="3/1",city ="Hyd",pinno =500080}
          new address {hno = "10/s/3",street="2/a/1",city ="Hyd",pinno =500004}
          new address {hno = "102/r/1",street="5",city ="Secbad",pinno =500102}
      };


      Qury syntax to fetch the address which have city startwith "H" and also sort by pinno

      IEnumerable<address> result = from a in addresses
                      where a.city.Startswith("H")
                      Orderby a.pinno descending
                      select a;

      from the above code, that want to perform a "LINQ" query aginnext the addreee collection, so that we used ther parameter "a" to represent the each item of Address.

      public class newaddress
      {
          public string fuladdress {get; set;}
          public int pinno {get; set;}
      }

      IEnumerable<address> result = from a in addresses
                      where a.city.Startswith("H")
                      Orderby a.pinno descending
                      select new newaddress {
                      fuladdress  = a.hno +", "+ a.street +", "+a.city,
                      pinno = a.pinno};  
       
  • Anonymous Types
    • Anonymous types are a convenient language feature of C# and VB that enable developers to concisely define inline CLR types within code, without having to explicitly define a formal class declaration of the type.

      we've introduced the "var" keyword, we can start to use it to refer to anonymous types.

      var addresses = new {hno = "18-10/2", street="3/1",city="Hyd",pinno=500080 }

      when ever use the addresses. then the hno,street,city,pinno properties appeared autometically for the addresses. the type of these properties is Anonymous
Read more

Defragmenting Indexes in SQL Server

February 5 2010 , Written by Balavardhan Reddy Published on #SQL Server2005

When data is inserted, deleted, or updated in a SQL Server tables, the indexes which are defined for that tables are automatically updated to reflect those changes. When these indexes are modified, the information stored in them becomes fragmented, resulting in the information being scattered across the data files. When this occurs, the logical ordering and physical order data doen't match, which can lead to a deterioration of query performance.

To fix this problem, you should ReOrganize or Rebuild the indexes periodically i.e. defragmented, so the physical order of the leaf-level pages matches the logical order of the leaf nodes.This means that you should analyze your indexes.

To analyze the Indexes in SQL Server 2005 or 2008, you should use the system function called as  sys.dm_db_index_physical_stats to determine which indexes are fragmented and the extent of that fragmentation. You can use the function to analyze all the indexes in SQL Server 2005 or 2008, all indexes in a database, all indexes defined on a table, or a specific index.

The sys.dm_db_index_physical_stats function takes the following parameters:
    Database ID
    Object ID
    Index ID
    Partition number
    Mode
  • Database ID: Represents the ID number of a database (Amallint value). If null is specified, the function retrieves index-related data of all databases from that SQL Server instance. If you specify null, you must also specify null for the object ID, index ID, and partition number.
  • Object ID: Represents the ID number of a table or view (Int value). If null is specified, the function retrieves index-related data for all the tables and views of specificed database or SQL Server instance. If you specify null, you must also specify null for the index ID and partition number.
  • Index ID: Represents the ID number of an index (int value). If null is specified, the function retrieves index-related data for all indexes defined on the specified table or view. If you specify null, you must also specify null for the partition number. Also, if the object ID refers to a heap, use 0 as the index ID.
  • Partition number: Represents the partition number of an index or heap (Int value). If null is specified, the function retrieves index-related information for all partitions related to a specific object.
  • Mode: The scan level used to obtain index-related information. Valid inputs include NULL, DEFAULT, or one of the following three modes:
You must specify all five parameters, even if their values are null.

The sys.dm_db_index_physical_stats function returns a number of values that provide details about the indexes you specify
  • avg_fragmentation_in_percent: Percentage of the logical index that is fragmented.
  • fragment_count: Number of fragments in the leaf level.
  • avg_fragment_size_in_pages: Average number of pages in a leaf-level fragment.
  • page_count: Number of index or data pages. etc.
Based on these index analysis, we can determine what action to take. As per Microsoft recommends that we reorganize when avg_fragmentation_in_percent  value of index is less than or equal to 30%, and rebuild the index if the value is greater than 30%.

We should still analyze the indexes regularly, and the sys.dm_db_index_physical_stats function is the best tool to use. So let’s take a look at an example of how to use the function to retrieve index-related statistics

SELECT
    object_id AS ObjectID, index_id AS IndexID, avg_fragmentation_in_percent AS PercentFragment,
    fragment_count AS TotalFrags, avg_fragment_size_in_pages AS PagesPerFrag,  page_count AS NumPages
    FROM
    sys.dm_db_index_physical_stats(DB_ID('CSCD'), NULL, NULL, NULL , 'DETAILED')
    WHERE
    avg_fragmentation_in_percent > 0


the result like below

ObjectID            IndexID      PercentFragment         otal Frags      PagesPerFrag                NumPages
76579361            1                46.8271621091195            3594             2.12659988870339           7643
76579361            1                39.2857142857143            28                1                                     28
76579361            2                60.071359065845             1865             1.65308310991957            3083
76579361            2                44.4444444444444            31                1.16129032258065            36
252579988          1                44                                    13                1.92307692307692            25

etc....


Here I used the sys.dm_db_index_physical_stats function to retrieve the four values described above, plus the object_id and index_id values. in the result you will get number of indexes, it grow quite large.
you might want to narrow down the result set by using the Object ID and other parameter values

SELECT
    b.name AS IndexName, a.avg_fragmentation_in_percent AS PercentFragment,
    a.fragment_count AS TotalFrags,a.avg_fragment_size_in_pages AS PagesPerFrag,
    a.page_count AS NumPages
    FROM
    sys.dm_db_index_physical_stats(DB_ID('CSCT'),OBJECT_ID('Data_collection'), NULL, NULL , 'DETAILED') AS a
    JOIN sys.indexes AS b
    ON a.object_id = b.object_id
    AND a.index_id = b.index_id
    WHERE a.avg_fragmentation_in_percent > 0



Result Like


IndexName                PercentFragment     TotalFrags       PagesPerFrag            NumPages
IX_01                           60.071359065845        1865               1.65308310991957        3083
IX_01                           44.4444444444444       31                  1.16129032258065        36
PK_DataEvaluation       46.8271621091195       3594               2.12659988870339        7643
PK_DataEvaluation       39.2857142857143       28                  1                                  28



Reorganizing Indexes

When we reorganize an index, SQL Server physically reorders the leaf-level pages to match the logical order of the leaf nodes. The process uses the existing pages only and does not allocate new ones, but it does compact the index pages. In addition, reorganization uses minimal resources and is automatically performed online, without blocking queries or updates. You should reorganize indexes only if they’re lightly fragmented, otherwise, you should rebuild them.

To reorganize an index, run an ALTER INDEX statement and include the keyword REORGANIZE

ALTER INDEX PK_DataEvaluation
  ON CSCT.dbo.Data_evaluation
REORGANIZE




Rebuilding Indexes

Rebuilding an index is generally more effective than reorganizing it because it drops the original index and builds a new one. As a result, the index starts clean with minimal fragmentation and the pages are compacted, and new pages are allocated as needed. In addition, you can choose to rebuild an index offline (the default) or online.

Rebuilding an index can be as simple as reorganizing one. Once again, use the ALTER INDEX statement, but specify the REBUILD keyword, rather than REORGANIZE,



ALTER INDEX IX_01
  ON CSCT.dbo.Data_evaluation
REBUILD


When you rebuild an index, you can include a WITH clause after the REBUILD keyword. In the WITH clause, you can specify one or more options that define how the index will be rebuilt

ALTER INDEX IX_01
  ON CSCT.dbo.Data_evaluation
REBUILD WITH (
  FILLFACTOR = 70,
  ONLINE = ON
)


In this case, the FILLFACTOR option is set to 70, which determines the percentage of how much the leaf level of each index page will be filled during index creation or alteration. As a result, the leaf level will be filled to only 70%. The ONLINE option is set to ON, which means that the StoreContact table and IX_StoreContact_ContactTypeID index will be available for queries and data modification during the index operation.

Another method you can use to rebuild an index is the CREATE INDEX statement. To use this statement, you must create an index by the same name as the original and include the DROP_EXISTING option in the WITH clause, as shown in the following example:

CREATE UNIQUE NONCLUSTERED IX_01
ON CSCT.dbo.Data_evalaution
WITH (
DROP_EXISTING = ON,
FILLFACTOR = 70,
ONLINE = ON
) ON [PRIMARY]

In this case, I’m rebuilding the AK_StoreContact_rowguid index, which is a unique, nonclustered index. Notice that the DROP_EXISTING option is set to ON, which means that the original index will be dropped and a new one created






Read more