How to find query/stored procedure used in an SSRS report

If yor are troubleshooting an SSRS report which you have not created by yourself. Finding it’s location may take sometime even though you know that issue is with the query. Hence to find the query/stored procs used in the report we can directly query ReportServer database and get those details from it’s report dataset.

SELECT Name As ReportName,
CAST(CAST(Content AS VARBINARY(MAX)) AS XML) AS ReportInXMLFormat
FROM ReportServer.dbo.Catalog
where Type = 2 
order by Name

it will give an XML result which will look like this.

<DataSets>
    <DataSet Name="EmployeeDetails">
        <Query>
            <CommandType>StoredProcedure</CommandType>
            <CommandText>usp_getEmpDetails</CommandText>
            <QueryParameters>
                <QueryParameter Name="@EMPLOYEEID">
                    <Value>=Parameters!EMPID.Value</Value>
                </QueryParameter>               
            </QueryParameters>
            <DataSourceName>DVEMP02</DataSourceName>
        </Query>
        .
        .
        .
   </DataSet>
    <DataSet Name="Department">
        <Query>
            <DataSourceName>DVEMP02</DataSourceName>
            <CommandText>
                SELECT DepartmentId AS ID, DeparmentName 
                FROM Department               
            </CommandText>
            <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
        </Query>
    </DataSet>
    .

Query tag will tel you about the sql query and stored proc used in the reports. You can start troubleshooting right away if it’s a data or a query issue.

I hope it helps!

Advertisements

Moving Items from one ListBox to another ListBox using Jquery

Jquery can do this real quick and with good user experience. You might need to style it up a bit.

UI code:


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Test.aspx.cs" Inherits="Test" %>

 <!DOCTYPE html>

 <html xmlns="http://www.w3.org/1999/xhtml">
 <head runat="server">
  <title></title>
  <script src="Scripts/jquery-1.7.1.js"></script>

 <script>

 $(document).ready(function () {

 var productLineId = $("#ddlProductLine").val();
  var lBox = $('select[id$=lbCountriesAll]');
  var lBox2 = $('select[id$=lbSelectedCountries]');

 $("#btnSelect").click(function (e) {

 var selectedValues = $('#lbCountriesAll option:selected');
  if(selectedValues.length == 0)
  {
  alert('Please select a country.');
  e.preventDefault();
  }

 if (selectedValues.length > 3) {
  alert('You cannot select more than 3 countries.');
  e.preventDefault();
  }
  else {
  //debugger;
  var children = $('#lbSelectedCountries').children();
  if (children.length >= 3) {
  alert('You cannot select more than 3 countries.');
  e.preventDefault();
  }
  else {
  $('#lbSelectedCountries').append($(selectedValues).clone());
  $(selectedValues).remove();
  e.preventDefault();
  }

 }
  })

 $("#btnUnSelect").click(function (e) {
  //debugger;

 var selectedValues = $('#lbSelectedCountries option:selected');
  if (selectedValues.length == 0) {
  alert('Please select a country.');
  e.preventDefault();
  }

 $('#lbCountriesAll').append($(selectedValues).clone());
  $(selectedValues).remove();
  e.preventDefault();

 })

  });
  </script>

 </head>

 <body>
  <form id="form1" runat="server">
  <div>

 <table>

 <tr><td>

 <asp:ListBox ID="lbCountriesAll" runat="server" Width="200px" Height="350px" SelectionMode="Multiple" ></asp:ListBox></td>

  <td>
  <asp:Button ID="btnSelect" runat="server" Text=">>"></asp:Button><br />
  <asp:Button ID="btnUnSelect" runat="server" Text="<<"></asp:Button>
  </td>
  <td>
  <asp:ListBox ID="lbSelectedCountries" runat="server" Width="200px" Height="350px" SelectionMode="Multiple"></asp:ListBox></td>
  </tr>

 </table>

 </div>
  </form>
 </body>
 </html>

The above code will transfer the items from one Listbox to another and can also limit the number of items a user can transfer. You can remove that code if you do not need it.

Code Behind:The code below only binds the Listbox with some countries.


protected void Page_Load(object sender, EventArgs e)
 {
 if (!IsPostBack)
 {
 lbCountriesAll.DataSource = GetCountries();
 lbCountriesAll.DataTextField = "Name";
 lbCountriesAll.DataValueField = "ID";
 lbCountriesAll.DataBind();
 }

}

&nbsp;

public static List<Country> GetCountries()
 {
 List<Country> Countries = new List<Country>()
 {
 new Country() { ID=4, Name="Afghanistan"},
 new Country() { ID=918, Name="Åland Islands"},
 new Country() { ID=8, Name="Albania"},
 new Country() { ID=12, Name="Algeria"},
 new Country() { ID=16, Name="American Samoa"},
 new Country() { ID=20, Name="Andorra"},
 new Country() { ID=24, Name="Angola"},
 new Country() { ID=660, Name="Anguilla"},
 new Country() { ID=10, Name="Antarctica"},
 new Country() { ID=28, Name="Antigua and Barbuda"},
 new Country() { ID=32, Name="Argentina"},
 new Country() { ID=51, Name="Armenia"},
 new Country() { ID=533, Name="Aruba"},
 new Country() { ID=36, Name="Australia"},
 new Country() { ID=40, Name="Austria"},
 new Country() { ID=31, Name="Azerbaijan"},
 new Country() { ID=44, Name="Bahamas, The"},
 new Country() { ID=48, Name="Bahrain"},
 new Country() { ID=50, Name="Bangladesh"},
 new Country() { ID=52, Name="Barbados"},
 new Country() { ID=112, Name="Belarus"},
 new Country() { ID=56, Name="Belgium"},
 new Country() { ID=84, Name="Belize"},
 new Country() { ID=204, Name="Benin"},
 new Country() { ID=60, Name="Bermuda"},
 new Country() { ID=64, Name="Bhutan"},
 new Country() { ID=68, Name="Bolivia"},
 new Country() { ID=912, Name="Bonaire, Sint Eustatius and Saba"},
 new Country() { ID=70, Name="Bosnia and Herzegovina"},
 new Country() { ID=72, Name="Botswana"},
 new Country() { ID=74, Name="Bouvet Island"},
 new Country() { ID=76, Name="Brazil"},
 new Country() { ID=86, Name="British Indian Ocean Territory"},
 new Country() { ID=96, Name="Brunei"},
 new Country() { ID=100, Name="Bulgaria"},
 new Country() { ID=854, Name="Burkina Faso"},
 new Country() { ID=108, Name="Burundi"}

};

return Countries;

}

I am using a web application to demonstrate here. You can use the same code in MVC too, only thing you need to change is the URL you are pointing to in $.ajax call. It would point to an action method from a controller class.
Similarly, you can use the same code with HTML 5 app, only the Url will point to some Http service.

I hope it helps!

 

Gridview Model binding with Control attribute as parameter

Asp.Net MVC had enjoyed the benefit of Model binding since its inception. It helped in few things such as

  • UI and data model separation.
  • Unit testing of the data binding method. (great! isn’t it?)

With Asp.Net 4.5, Model binding is now available for Web forms as well. Moreover it is integrated in a seamless manner.

so here we go!

UI Code:

New Gridview has two properties to be noticed.

  • ItemType:
  • It needs a type name that you are binding to. It can be a dll which contains the actual method.

  • SelectMethod:
  • It needs a method which returns an IEnumerable or IQueryable type.

    Similarly we have UpdateMethod which is not covered in this post.

    <asp:DropDownList ID="ddlNameFilter" runat="server" AutoPostBack="true" Width="500px" >
                    <asp:ListItem Value="" Text="-- Select word name starts with --" Selected="True" />
                    <asp:ListItem Value="KE" Text="KE" />
                    <asp:ListItem Value="TE" Text="TE" />
                </asp:DropDownList>
    
    </br></br>
                
             <asp:GridView ID="gvPersons" runat="server" 
                    AllowPaging="True" AutoGenerateColumns="False" PageSize="20"             
                 ItemType="WebFormSamples.Person" SelectMethod="GetPersonList" 
                 OnRowDataBound="gvPersons_RowDataBound"
    
                    OnPageIndexChanging="gvPersons_PageIndexChanging">
                    <Columns>
                        <asp:BoundField DataField="BusinessEntityID" HeaderText="Product"
                            SortExpression="BusinessEntityID" />
                        <asp:BoundField DataField="PersonType" HeaderText="PersonType"
                            ReadOnly="True" SortExpression="PersonType" />
                        <asp:BoundField DataField="FirstName" HeaderText="FirstName"
                            SortExpression="FirstName" />
                        <asp:BoundField DataField="LastName" HeaderText="LastName"
                            SortExpression="LastName" />
                    </Columns>
                    <PagerStyle HorizontalAlign="Right" />
                    <PagerSettings Mode="Numeric" />
                </asp:GridView>
    

    Code Behind:

     
            // ddlNameFIlter is a dropdown which user selects and selected value is passed as parameter to the below //<strong>SelectMethod</strong> of Gridview. 
            public IQueryable<Person> GetPersonList([Control("ddlNameFilter")] string searchName)
            {
                List<Person> persons = new List<Person>();
                if ( string.IsNullOrEmpty(searchName))
                {
                    persons = Person.GetPersonList();
                }
                else
                    persons = Person.GetPersonList().Where(p => p.FirstName.ToLower().StartsWith(searchName.ToLower())).ToList();
    
                return persons.AsQueryable();
            }

    There are few more Value providers introduced in Asp.Net 4.5. [QueryString],[Cookie] are also very useful.

    I hope it helps!

    GridView with built in CustomPaging – Asp.Net 4.5

    Every web developer hates Gridview ViewState specially when its large in size. Solution to this is to create a custom paging solution. but it was too much of work. You need to Create a custom control and then handling all the combinations of click on page links. Styling of the pager was also not standard.

    Asp.Net 4.5 resolved this by providing a Gridview attribute AllowCustomPaging = “true” which allow us to utilize built in gridview paging with custom paging logic. There is one more property VirtualItemCount added to gridview which should be set to the total number of records.

    Below is the simple example. It seems so easy that you can do it right away in your existing code with minimal changes.

    UI Code:

    
     <asp:GridView ID="gvPersons" runat="server" 
      AllowCustomPaging="true"
      AllowPaging="True"           AutoGenerateColumns="False" PageSize="20"
      OnPageIndexChanging="gvPersons_PageIndexChanging">
    
     <Columns>
     <asp:BoundField DataField="BusinessEntityID" HeaderText="Product"
     SortExpression="BusinessEntityID" />
    
     <asp:BoundField DataField="PersonType" HeaderText="PersonType"
     ReadOnly="True" SortExpression="PersonType" />
    
     <asp:BoundField DataField="FirstName" HeaderText="FirstName"
     SortExpression="FirstName" />
    
     <asp:BoundField DataField="LastName" HeaderText="LastName"
     SortExpression="LastName" />
    
     </Columns>
     <PagerStyle HorizontalAlign="Right" />
     <PagerSettings Mode="Numeric" />
     </asp:GridView>
    
    

    Custom Paging Stored Procedure in Sql server 2012

    This query is the heart of this custom paging logic. Sql Server 2012 provides simply “Awesome” way of writing a very complex query (in earlier versions).

    
    CREATE PROCEDURE usp_GetPersonsPaged
    	@StartRowIndex  int,
    	@MaximumRows	int
    AS
    BEGIN
    	
    	SET NOCOUNT ON;
    	    DECLARE @lastRowIndex int
    		SET @lastRowIndex = (@StartRowIndex + @MaximumRows) - 1;
    		 
    
    	 SELECT   [BusinessEntityID]
    		 ,[PersonType]     
    		 ,[FirstName]     
    		 ,[LastName]  FROM [AdventureWorks2012].Person.Person
    	 ORDER BY BusinessEntityID
    	 OFFSET @StartRowIndex ROWS
    	 FETCH NEXT @lastRowIndex ROWS ONLY;	
    
    END
    
    

    Code Behind:

    Nothing changes here. Only your data access layer method returns Maximum rows equal to page size.

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    gvPersons.VirtualItemCount = Person.GetTotalPersonCount() ;
                    BindGrid(0,gvPersons.PageSize);                
                }
            }
    
            private void BindGrid(int pageIndex,int pageSize)
            {            
                gvPersons.DataSource = Person.GetPersonListPaged(pageIndex, gvPersons.PageSize);
                gvPersons.DataBind();
            }
    
            protected void gvPersons_PageIndexChanging(object sender, GridViewPageEventArgs e)
            { 
                gvPersons.PageIndex = e.NewPageIndex;
                BindGrid(gvPersons.PageIndex,gvPersons.PageSize);
            }
    

    Data Access Code:

    you can use AdventureWorks Sample DB for Sql server 2012 to check out performance.

    
    public class Person
        {
            public int BusinessEntityID { get; set; }
    
            public string PersonType { get; set; }
    
            public string FirstName { get; set; }
    
            public string LastName { get; set; }
    
    
            public static List<Person> GetPersonListPaged(int startIndex, int maxPazeSize)
            {
                List<Person> persons = new List<Person> ();
    
                SqlConnection con = new SqlConnection();
                SqlCommand cmd = new SqlCommand();
                try
                {
                    con.ConnectionString = "data source=.\\sqlserver2012;initial catalog=AdventureWorks2012;integrated security=True;";
    
                    cmd.Connection = con;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = "usp_GetPersonsPaged";
    
                    cmd.Parameters.AddWithValue("StartRowIndex", startIndex);
                    cmd.Parameters.AddWithValue("MaximumRows"  , maxPazeSize);
                    con.Open();
                    
                    SqlDataReader dr;
                    dr = cmd.ExecuteReader();
                    
                    while (dr.Read())
                    {
                        Person p = new Person() {BusinessEntityID=dr.GetInt32(0),PersonType=dr.GetString(1),FirstName=dr.GetString(2),LastName=dr.GetString(3) };
    
                        persons.Add(p);                    
                    }                
                                    
                    con.Close();
                }
                catch (Exception ex)
                {
    
                }
                finally
                {
                    con.Dispose();
                    cmd.Dispose();
                }
    
                return persons;
            }
    
    
    
            public static int GetTotalPersonCount()
            {
                int totalCount = 0;
    
                SqlConnection con = new SqlConnection();
                SqlCommand cmd = new SqlCommand();            
                try
                {
                    con.ConnectionString = "data source=.\\sqlserver2012;initial catalog=AdventureWorks2012;integrated security=True;";
                    
                    cmd.Connection = con;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = "select count(1) from [AdventureWorks2012].[Person].[Person];";
                    con.Open();
                    totalCount = (int)cmd.ExecuteScalar();
    
                    con.Close();
                }
                catch (Exception ex)
                {
    
    
                }
                finally
                {
                    con.Dispose();
                    cmd.Dispose();
                }
    
                return totalCount;
            }
        }
    }
    

    I hope it helps!!

    Reading XML using C# Linq – Short and Simple

    Reading an XML has become easier by new Linq to XML queries. It is as simple as querying a List though there are differences in query as per XML structure you are trying to read.

    Here is simple way to parse/read/query an XML file in the following format.

    XML file:

    <?xml version="1.0" encoding="utf-8" ?>
    <Babies>
      <Baby>
        <Name>Aabhas</Name>
        <Gender>M</Gender>
        <Meaning>Feeling</Meaning>
      </Baby>
      <Baby>
        <Name>Balwan</Name>
        <Gender>M</Gender>
        <Meaning>Strong</Meaning>
      </Baby>
      <Baby>
        <Name>Chandra</Name>
        <Gender>M</Gender>
        <Meaning>Moon</Meaning>
      </Baby>
    </Babies>
    

    Below is the C# class representation of XML file

    public class Baby
    {
     public string Name { get; set; }
     public string Meaning { get; set; }
     public string Gender{ get; set; }
    
    }
    

    Below is the C# code to read the nodes and reading it as List

    XDocument xdoc = XDocument.Load(xmlFileName);
    List<Baby> babies = new List<Baby>();
    babies = (from b in xdoc.Descendants("Baby")
    select new Baby() { Name = b.Element("Name").Value, Meaning = b.Element("Meaning").Value ,Gender=b.Element("Gender").Value }
    ).ToList();
    

    Obviously we can apply search filter to the list and do all the possible list operations.
    I did not provide any analysis for performance here as of now but will test and update this post later.

    I hope it helps!