Thursday 18 february 2010
4
18
/02
/Feb
/2010
13:05
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();
}
By Balavardhan Reddy
-
Posted in: ASP.Net
9
Thursday 18 february 2010
4
18
/02
/Feb
/2010
12:09
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
By Balavardhan Reddy
-
Posted in: ASP.Net
26
Friday 5 february 2010
5
05
/02
/Feb
/2010
08:29
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
By Balavardhan Reddy
-
Posted in: SQL Server2005
2
Wednesday 27 january 2010
3
27
/01
/Jan
/2010
10:13
Here I will show you how to get these custom aggregates on a matrix report and will
cover a few interesting reporting techniques along the way
Cells-by-Cells technique – This helps us achieve a bit more usability with the somewhat limited framework of the SSRS Matrix control by allowing us to add as many subcolumns within a column as we’d
like
Custom aggregates – The heart of this article, and probably the very reason you are still reading. Going beyond the standard SUM, we’ll use the powerful inspection expression InScope() to provide
nearly limitless calculations.
Custom coloring – We’ll add some data visualization to the report, but we’ll modify the chart coloring at runtime.
Here I had taken Sales of each Item on Year and Monthly basis
Here the Data structure is like
Item Name Year Month Total Sales
Item1 2008 Jan 120
Item2 2008 Jan
150
Item1 2009 Jan 230
Item2 2009 Jan 250
etc..
Here I designed the Cross tab by Year, Month basis for every Item. Add a matrix control to the RDL page and set the Dataset for the Matrix Control. set Column Group and Row group as per the my
Previous post.
After settting the Row and Column groups, add another Column Group by right clicking on the first row of the Matrix, here set the Month name for the Column Group 2
-- Adding New Column Group
-- Set the Field for Column Group 2
-- The result Matrix will appear like below
Click on the Preview tab to see the Result, here you will find the Year and month wise report like below, and it will display the Total of the Sales for each Column and Row.
in the Above, the color of Total sales and each individual sales is Same, if we want provide the different color for Column totals, Row totals and Individuals Slaes, her wee need to use
Inscope() method to found the each Column and Row then provide the Color as you like.
Select the Data Column, goto the Properties and select the Background Color and set the expression.
then the Output will display in below
Insetead of Sales total, here by I am Caluclating Avg for the Sales, to caluclate the avg, right click on the Data Column textbox, select the Expression and write your formula to find the Avg in
the expression window, here also we should use Inscope() method to find the Total displaying Row, then instead of Total put the formula for Avg,
Formula for Avg :
Format(Sum(Fields!Sales.Value)*100 / Sum(Fields!Sales.Value, "DataSet1"),"#0.0#;(#0.0#);-\0-") + "%" ),
Then the report will appear like.
--
By Balavardhan Reddy
-
Posted in: SSRS
2
Thursday 21 january 2010
4
21
/01
/Jan
/2010
13:50
In SQL Reporting Services,
the Matrix control provides a crosstab view of data, similar in behavior to a PivotTable. Rows and columns will have intersecting points of data which is often useful in time based reporting.
Matrix components are great for visualizing data that might normally be exported to Excel, or in a Pivot Table. Year over year analysis is a good example. But if an analyst wants to view monthly
sales, year over year, then he'd need to add some custom sorting so the months line up as expected. Additionally, we don't want to add the values between years, we instead need to display a
difference in the totals, and perhaps in percentage growth (or decline).
To add a matrix
Open the client report definition (.rdlc) file in graphical design mode. In the Toolbox, click Matrix. Click the design surface of the report.
After the matrix is on the report, you can move it by pointing to the shaded border of the matrix and dragging it to a new location. When you first create a matrix, the matrix displays four cells.
The upper-left cell is the corner cell. You can use the corner cell to display a label for the matrix, or you can leave it empty. The upper-right cell is a column header, which can contain a field
or expression by which to group the data. The lower-left cell is a row header, which also can contain a field or expression by which to group the data. The lower-right cell contains an aggregate
expression for the detail data.
After you add a matrix, you can add fields to the matrix. Each cell in the matrix contains a text box by default. You can type any expression into any cell, or you can change the item within the
cell to another item (i.e. to Image ot Chart).
Each matrix on a report is associated with a dataset. If the report contains a single dataset, the matrix is automatically associated with that dataset when you place it on the report. If the
report contains multiple datasets, you must associate the matrix with the correct dataset.
Here I had taken Dataset for the Slaes on each Item for every year. the result of the Dataset is like
Item Year Sales
Item1 2007 150
Item2 2007 200
Item1 2008 180
Item2 2008 160
Item3 2007 510
Item3 2008 320
After Adding the Required Dataset set to the Matrix control, you need to set the Column Grouping and Row Grouping Fields for Cross tab
Add the required Fileds to Column Header, and Row Header in the Matrix Control and also the Aggregate Expression to display the Data. to set these fileds, Right clikc on the each textbox (i.e.
Header Group, Row group and Data), select the properties and set Fileds Name.
Here I added Year for Column Header, Item for Header, Aggregate function like Sum of the Sales to the Data textbox
Here, we can set the different Colours to each section of the Matrix control. select the every textbox and set the Colour font nad etc.. from its properties explorer, and see the preview of the
report.
Adding Total's to Cross tab
In the matrix control we can also add the Sub total to each Column on and Each Row.
It shows like Total Items Sales in the every Row, and it shows Total Sales for every Year.
Select the Column Header and right click on the Header, here you can get the Sub Total in the Right Click Context menu, do the same above for Row Header also.
After adding the Subtotal to the Matrix, see the preview of the report, it looks like below.
-- Advanced Matrix Control next...
By Balavardhan Reddy
-
Posted in: SSRS
28
Recent comments