Wednesday, October 30, 2013

SQL Server Reporting Services (SSRS) - Passing multi-valued parameters to DB2

I had a requirement where I wanted to pass multi-valued parameters in a report querying a DB2 database. Couldn't do it myself and posted a question in SQL Server Reporting Services, Power View forum. This nice guy, Charlie Liao came in and solved my problem and thought to share the answer with you all.

For SSRS what Microsoft says about passing multi valued parameters is, as far as you satisfy following requirements SSRS can pass multi-valued parameters.

You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:
  • The data source must be SQL Server, Oracle, or Analysis Services.
  • The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
  • The query must use an IN clause to specify the parameter.

Unfortunately my scenario did not satisfy above requirements. I was grabbing data from a DB2 database. But does this mean I can’t use multi valued parameters against a DB2 database? No, there is a workaround, using the approach I am going to describe below, I did able to accomplish the task easily.

Let’s take this example. Let’s say I have a report and I have two parameters. One is the Company and the other is the Supplier. Supplier is a multi-valued parameter (I should be able to select many Suppliers).

First of all you will have to slightly change how you design/write the query in the report data set. That is, in the dataset properties I am not going to use the Query Designer, instead I am clicking in “fx” and writing the query as a expression.

Untitiled
Dataset Properties
I am not using the “Parameters” tab to insert any parameters.
Untitiled2
Dataset Parameters
Untitiled1
Dataset query as an Expression
Here I have wrote the query as a expression. Now you might be wondering why I have used this way. The thing is when you are using a DB2 database, in your query you should leave the parameters as “?”. If I modify the above SQL statement, it should be like the following.
SELECT somefields
FROM   sometable
WHERE  (sometable.CompanyCode=?) AND (sometable.SupplierCode IN ?)
When you wrote the above SQL query, SSRS Report designer will automatically create two parameters named “Parameter1” and “Parameter2”. You can’t change the parameter names, but you can change the prompt value.

But the thing is you can’t use these type of parameters in a query where you have a IN operator(as in multi valued parameter). You will get a error saying "A buffer passed to a system call is too small to hold return data character data right truncation". The solution is to write the query as a expression. When you write the query as a expression, you have more flexibility over everything. You can change the parameter names, you can pass multi-valued parameters etc..

So hope this helps. Again big thanks goes out to Charlie Liao for the great help.

Happy Coding.

Regards,
Jaliya

Monday, October 28, 2013

N-Frequencies : Release 6

N-Frequencies : Release 6 is now in store introducing one of the nicest features which is recording of streaming music. Your recordings will be saved in “Music”.

Untitled
Default View
image
Recording in Progress
image
Recorded Clip
When playing a station if the record button is disabled, that particular station does not allow recording of streaming music.

Untitled
Recording Unavailable
Happy Coding.

Regards,
Jaliya

Thursday, October 17, 2013

Visual C# Technical Guru - September 2013

In another couple of hours Windows 8.1 will be released. While waiting for that moment got this nice message. Became the Visual C# Technical Guru for the month of September, 2013. What else to say, it’s a happy day it seems. The TechNet Guru Awards celebrate the technical articles on Microsoft TechNet.

Post in WikiNinjas Official Blog,
http://blogs.technet.com/b/wikininjas/archive/2013/10/16/technet-guru-awards-september-2013.aspx
image
Visual C# Technical Guru - September 2013
Happy Coding.

Regards,
Jaliya

Thursday, October 10, 2013

“The breakpoint will not currently be hit. No symbols have been loaded for this document” When Debugging Silverlight Applications

When debugging Silverlight applications, I am sure some of you have already faced this situation. You have put several breakpoints in your Silverlight project, but when you are debugging through the browser, breakpoints doesn’t get hit. When you hover on the breakpoint, you can see the message “The breakpoint will not currently be hit. No symbols have been loaded for this document.”.

I have faced this situation many times in the past years and somehow managed to fix it. Today after long time did some Silverlight programming and I happened to face this scenario which was a real pain. Unfortunately I can’t seem to remember what I have done past to resolve the issue. I am putting a post here mentioning some resolutions, so in future it might help someone.
  1. First of all make sure you are running the application using Internet Explorer. If you are using any other browser other than Internet Explorer, you will have to use Visual Studio's Attach to Process for debugging.
  2. In your web application which hosts the Silverlight application, make sure Silverlight debugger is enabled. (Right click on the web application->Properties. In Web tab, under Debuggers section make sure Silverlight is enabled.)
image
Make Sure Silverlight Debugger is Enabled.
Hope this helps.

Happy Coding.

Regards,
Jaliya

Monday, October 7, 2013

Overview of LINQ (.NET Language-Integrated Query)

First of all LINQ is not a new thing and it was introduced with Visual Studio 2008 and in .NET Framework 3.5. If I am not mistaken the released date of LINQ was back in year 2007 and now it’s almost the end of 2013.

Still I wanted to write about LINQ because I have seen some confusions in some of my fellow developers and even in myself over LINQ. So this is kind of an effort to clearing things out for all who has confusions and of course to myself too.

This is basically an overview of LINQ, I might not be digging deep of all the things I am mentioning here, just trying to clear the picture as a whole.

Let’s start over with a brief yet important introduction to LINQ.

Language-Integrated Query or LINQ is simply some set of statements which can be integrated into following two programming languages which are Visual C# and Visual Basic (LINQ does not directly support Java nor C++, but there are some implementations filling the language gap). So the language syntax of C# and Visual Basic are extended to query and update data, almost in any kind of data source.

Let’s take SQL as a simple example. Some of your queries to manipulate data in Microsoft SQL Server are different from the queries you write against Oracle. The beauty of LINQ is no matter what your data source, you keep writing your queries using the same syntax's.

Another most important thing is, If I take SQL query as an example, the query is expressed as simple strings. The Visual Studio does not support metadata (table names etc) and therefore there is no IntelliSense. There is no compile-time syntax checking because it is just a set of strings. But with LINQ you will get everything. Rich metadata is supported with IntelliSense, compile-time syntax checking is there and provides static typing to your result.

So as I have mentioned before LINQ can be used against almost any kind of data source which can be categorized as follows.
  1. LINQ to ADO.NET
    • LINQ to DataSet
    • LINQ to SQL
    • LINQ to Entities
  2. LINQ to XML
  3. LINQ to Objects

LINQ to ADO.NET

LINQ to ADO.NET enables you to query over any enumerable object in ADO.NET. And this can be further categorized into LINQ to DataSet, LINQ to SQL and LINQ to Entities.
LINQ to DataSet
LINQ to DataSet makes it easier and faster to query over data cached in a DataSet object. As you may already know DataSet is the key element is ADO.NET disconnected architecture. The advantage of LINQ to DataSet is we can write queries using the programming language (C#/Visual Basic) and not using the SQL.
LINQ to SQL
In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language. When executing queries LINQ to SQL, the LINQ query is transformed into SQL and executes in the database. When the query returns values LINQ to SQL transformed the result set into objects that we have used in the object model.
LINQ to Entities
LINQ to Entities provides writing LINQ queries against the Entity Framework model using C#/Visual Basic. Queries against the Entity Framework are represented by command tree queries (A query command tree is an object model representation of a query) which executes against the object context. LINQ to Entities converts LINQ queries to command tree queries, executes the queries against the Entity Framework, and returns objects that can be used by both the Entity Framework and LINQ.

LINQ to XML

LINQ to XML is a LINQ-enabled, in-memory XML programming interface that enables you to work with XML from within the .NET Framework programming languages.

LINQ to XML brings the XML document into memory. You can query and modify the document, and after you modify it, you can save it to a file or serialize it and send it over the Internet.

LINQ to Objects

LINQ to Objects refers to the use of LINQ queries with any IEnumerable or IEnumerable<T> (sometimes back I wrote this post about IEnumerable<T> and IQueryable<T>, it might help you understanding IEnumerable and IEnumerable<T>) collection directly, without the use of an intermediate LINQ provider or API such as LINQ to SQL or LINQ to XML.

At last it’s the end of the theory part. Now let’s have a look at LINQ in action. To demonstrate some action I am going to use LINQ to Objects here.

I am creating a console application and I have following two classes “Employee” and “Department” and some two helper methods returning values.
public class Department
{
    public int DepartmentId { get; set; }
    public string DepartmentDesc { get; set; }
 
    public static List<Department> GetDepartments()
    {
        return new List<Department>() 
        {
            new Department() 
            { 
                DepartmentId = 1, 
                DepartmentDesc = "Microsoft Visual Studio"
            },
            new Department() 
            { 
                DepartmentId = 2, 
                DepartmentDesc = "Microsoft SQL Server"
            }
        };
    }
}
 
public class Employee
{
    public int EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int DepartmentId { get; set; }
 
    public static List<Employee> GetEmployees()
    {
        return new List<Employee>() 
        {
            new Employee() 
            { 
                EmployeeId = 1, 
                FirstName = "Jaliya", 
                LastName = "Udagedara", 
                DepartmentId = 1
            },
            new Employee() 
            { 
                EmployeeId = 2, 
                FirstName = "John", 
                LastName = "Smith", 
                DepartmentId = 2
            },
            new Employee() 
            { 
                EmployeeId = 3, 
                FirstName = "Jane", 
                LastName = "Smith", 
                DepartmentId = 1
            }
        };
    }
}

Now in the Main method I am going to use LINQ to query these collections. A thing to note is when writing LINQ queries, there are two ways you can write them. Once is the Query Syntax and the other is Method Syntax.

First let’s have a quick look at both Query Syntax and Method Syntax.

Query Syntax and Method Syntax

Query syntax and Method syntax are semantically identical, but many people find Query syntax simpler and easier to read. When the LINQ query is written using Query syntax and when the application get compiled, The Query syntax will be transformed back into Method syntax. And those method calls will be sent to CLR. The one significant different between these two is, when using Method syntax, there will be a heavy use in Lamba expressions. I believe you have some knowledge in Lambda expressions here, because otherwise you will not be able to understand what happens in LINQ using Method syntax.

Now I am going to write some LINQ queries and I will be writing using both these ways.

I have following two collections.
List<Employee> employees = Employee.GetEmployees();
List<Department> departments = Department.GetDepartments();

Let’s say I want to select all the employees where the LastName is “Smith”.
IEnumerable<Employee> query = from e in employees
                              where e.LastName == "Smith"
                              select e;
IEnumerable<Employee> query = employees
                                .Where(e => e.LastName == "Smith");
Now let’s have a look at what’s happening here. My LINQ query is against the List of type Employee which is employees. And it’s returning me an IEnumerable of type Employee. When writing the query Visual fully supports IntelliSense, suggesting me the properties which the “Employee” has.

Deferred Execution and Immediate Execution

There is something important thing to note here. If you debug the code and examine the value of queries, you will see something like this.
image
Result View
For the Results View, you can see a message saying “Expanding the Results View will enumerate the IEnumerable”. What does this mean?. Actually this is called the deferred execution. The query variable itself only stores the query commands. The actual execution of the query is postponed until you iterate over the query variable in a foreach statement. But let’s say you want to immediately execute the query. For that you can write the query in the following way.
IEnumerable<Employee> query = (from e in employees
                                where e.LastName == "Smith"
                                select e).ToList();
IEnumerable<Employee> query = employees
                    .Where(e => e.LastName == "Smith").ToList();

Now if you debug and examine the result set, this is what you will see.
image
Result
Here to force immediate execution of any query and cache its results, you can call the ToList<TSource> or ToArray<TSource> etc. methods.

Data Transformation with LINQ

Now let’s have a look at another example which will demonstrate table join and one of the most powerful feature of LINQ queries which is the ability to create new types. This is accomplished in the select clause.

Let’s say I want write a query to get the Employees First Name, Last Name and the Description of Department where he is working on.
var query = from e in employees
            join d in departments 
            on e.DepartmentId equals d.DepartmentId
            select new 
            { 
                FName = e.FirstName, 
                LName = e.LastName, 
                DDesc = d.DepartmentDesc 
            };
var query = employees
            .Join(departments,
            d => d.DepartmentId,
            e => e.DepartmentId,
            (e, d) => new
            {
                FName = e.FirstName,
                LName = e.LastName,
                DDesc = d.DepartmentDesc
            });

This shows another nice feature in LINQ which is the ability to transform data (creating new types etc.). You can use select new object initializer with either a named object or an anonymous type. Here I am creating a anonymous type and I have used var as the type of the query, because the select statement returns an anonymous type.

So that's it. Hope you would find something interesting from the post. I am uploading the demo app to my SkyDrive.


Happy Coding.

Regards,
Jaliya

Thursday, October 3, 2013

N-Frequencies : Release 5

N-Frequencies : Release 5 is the newest release after changing the application name. This release will contain some new features as well as some improvements in existing functionalities.

Improved Functionalities

  • Improved UI Experience

New Features

  • Search
  • Application Notifications
    • When new app version is available in store
    • When new radio station list is available

Here are some of the screenshots of the new release.

Untitled1
Radio Stations
Untitled2
Daily Line Up
Untitled3
Snapped View
Untitled4
Search
Untitled5
Sharing


Happy Coding.

Regards,
Jaliya

Wednesday, October 2, 2013

Filter a DataTable based on a Collection

Today I had a requirement where I want to filter the rows in a data table based on some values in a collection. So this is how I managed to do it and thought to share with you all.

The reason for me to share this is, this will really show you the power of LINQ and Lambda Expressions (I am assuming you all know what LINQ and Lambda Expressions is, I might be covering these in a later post).

So let’s move into some action here. I am creating a console application. There I will have a data table with some values in it and I will have another collection which I am going to use for filtering my data table with.

I have following helper method which will return a data table with some values.
static DataTable PopulateDataTable()
{
    DataTable dtEmployee = new DataTable();
    dtEmployee.Columns.Add("EmpId", typeof(int));
    dtEmployee.Columns.Add("FirstName", typeof(string));
    dtEmployee.Columns.Add("LastName", typeof(string));
    dtEmployee.Columns.Add("DepartmentId", typeof(string));

    dtEmployee.Rows.Add(1, "Jaliya", "Udagedara", "DEPT1");
    dtEmployee.Rows.Add(2, "John", "Doe", "DEPT2");
    dtEmployee.Rows.Add(3, "Jane", "Doe", "DEPT3");
    dtEmployee.Rows.Add(4, "John", "Smith", "DEPT4");
    dtEmployee.Rows.Add(5, "Jane", "Smith", "DEPT1");
    return dtEmployee;
}
Actually there is nothing much to describe here, a simple data table with some columns and I have some data added.

Now I have a List of type string, which will contain some “DepartmentId”s. My requirement is I want to filter up my data table where it will only have rows where the "DepartmentId" is in my List.

So this is my collection and my datatable.
List<string> deptList = new List<string>() { "DEPT1", "DEPT3" };
DataTable dt = PopulateDataTable();
Now comes the interesting part. I want to get the rows where the "DepartmentId" is "DEPT1" or "DEPT3". This is the code to filter up my data table from values in the collection.
dt = dt.AsEnumerable()
    .Where(dr => deptList.Contains(dr["DepartmentId"].ToString()))
    .CopyToDataTable();
Let me show you the output first.
image
Output
It is just a one line in the code up there. I have used LINQ (method syntax) with Lambda Expressions. In here both the AsEnumerable() and CopyToDataTable() are two extension methods to the DataTable class. Actually these methods are the two key points which did let me do what I have done here.

The extension method AsEnumerable() returns an IEnumerable<T> object, where the T is a DataRow. And then I am filtering all my rows using a Lambda Expression. Finally calling CopyToDataTable() extension method I am returning a DataTable.

What CopyToDataTable() does is when we supply IEnumerable<T> where the T is a DataRow, it will return a DataTable that contain set of DataRows.

So that’s it. I am uploading the full sample to my SkyDrive and hope you will find this interesting.



Happy Coding.

Regards,
Jaliya