sam's blog

I have gathered some SQL queries and commands frequently used by developers (like me ☺) to reduce the googling time. For quick reference query titles are enlisted below with links: Commonly Used Queries Get all columns with specific name Get all connected tables Get all foreign key reference of a specific table Get all foreign key references of a specific column of a table Search across all tables for specific value Some Useful Syntax Get data with pagination Rotate data (PIVOT and UNPIVOT) Using common table expression (CTE) Ranking Data Get values from XML column Some Useful Commands Get database server information Get all database names Commonly Used Queries Get all columns with specific name This query could be useful when we've got a column name, but don't know which table it belongs to. -- Find All Columns From All Tables With Similar Name SEL...

SQL doesn't provide native support to translate query result into JSON format. But we can achive this with a little twisting with SQL :). Assume we have a "Computer" table in database with the following data: Now, we can get this data into JSON format using the query below: Here, I've used UNPIVOT operation to de-normalized the data and then constructed the JSON string. Notice that: the ProductCode has been used as the primary key of the objects in this query. You can also achive the same thing without using UNPIVOT; but in that case you have to construct the JSON string for every column you need by yourself. And if you are looking for more generic one to convert each row from a data set regardless of any primary key or without knowing the exact columns to be converted, following stored procedure could be helpful: I hope this will be helpful to someone looking for generating JSON feed directly from SQL. Cheers!

Using SignalR is pretty simple for implemeting push notification service. One thing to keep in mind is:  SignalR connections (including the connection underlying all Hub operations for a client) do not support Session state. So it's not possible to access session data from signalr hub. Now let's assume one scenario, where we need to perform a database operation on postback ( e.g. post action in an mvc controller ) based on a user action and after that we need to redirect to a listing/default page. But we want to let user know if the operation succeeded or not. Here is a Hub implemented to send client notification: Here is how it is initialized: And here is the client side script: The final thing is to invoke the client side  showNotification() function when the redirected view page is loaded. But problem is: the calling page and the redirected page are two different views and the session state is changed in the meantime. After redirection client hub in...

SSRS report paramters doesn't support runtime selection of operations by default. For example: we want to let users filter data by checking any criteria that equals, not equals or contains any specified value. W e can achieve this by using ssrs expressions. Below is outlined a step-by-step guide to do this. Following is a snapshot of a simple cube to be used in this example: The cube contains orders data including two measures: quantity of products and dollars of orders. It has four dimensions, but for this example lets focus on the Product and Quality dimensions only. The aim is to create a report to view total dollars received from the orders per product quality for specific product categories . Before starting we need to create the reporting project using SSRS and then add the data cube as a shared datasource. I've also added a dataset using query designer. It includes Dollars as measures and Product Name, Category, Quality to group the measure va...

We can achieve certain advantages by writing asynchronous methods. It's pretty simple to write them in C# code: we just need to use  async  and  await keywords properly. Grammar of these keywords can be found in detail in MSDN . In this post I will try to focus on writing few code to invoke a regular synchronous method asynchronously. Let's assume a scenario where we need to perform an expensive database operation when a particular information is changed from the front end. For example: I have an application to manage employee information and I've changed the basic salary for a particular grade using my application. Now I need to update salary of all the employees under that grade. The later operation will be slower one and I don't want this to block me from doing other stuffs. Here is a piece of synchronous code to update salary information: public   bool   UpdateEmployeeSalary ( string   grade ) {      try  ...