Storing Multiple values in a SQL column using bit(&) operator.

There may be situation where we want to store multiple values against a customer, user etc.  Assume for a customer we need to store whether he  owns a House, Car, Bike, Flat, Laptop…….etc.
In normal scenario, what we do is to create separate column for these in the table and save them as Boolean values as below.

 By using the & bit operator in SQL we could eliminate create multiple columns for storing these type of values. Let us see first how we can store this data by using only a single column.

I believe all we know about binary representation like

Decimal  Binary equivalent
2               000010
4               000100
8               001000
16             010000
32             100000

The logic here to first represent the below values for each

Has House 2

Has Car  4
Has Bike 8
Has Flat 16
Has Laptop 32

(You may extent this to store n number using respective decimal/binary representation like 64,128,256……)

So if  we need to store data for a customer who has house and a flat, what we do is add the numbers against “Has House” and ”Has Flat” which will give 18 (2(Has House)+16(Has Flat)=18).  Store these values against the customer in a single column as shown below.

We could compare whether the customer has a house or a flat using the query below,

 

SELECT 'Has House'  FROM dbo.New_Cuatomer_Data WHERE Owns & 2 = 2

SELECT 'Has Car'    FROM dbo.New_Cuatomer_Data WHERE Owns & 4 = 4
SELECT 'Has Bike'   FROM dbo.New_Cuatomer_Data WHERE Owns & 8 = 8
SELECT 'Has Flat'   FROM dbo.New_Cuatomer_Data WHERE Owns & 16 = 16 
SELECT 'Has Laptop' FROM dbo.New_Cuatomer_Data WHERE Owns & 32 = 32 

How does it work?

The logic works here is that, the & operator checks whether the bit positions specified after the & operator in the query is on or not(1 or 0) .For example in the case of 18, it will binary represented as below.

Hence for

SELECT Owns & 2  FROM dbo.New_Cuatomer_Data
 It will return 2 if 2^nd position is on (i.e. if 1)

Binary Bit Positions	32	16	8	4	2	1
Decimal equivalent 18	0	1	0	0	1	0

Expiring a page on clicking browser back button in Asp.net?

It is most common in some scenarios for us to expire a page as soon as the user clicks the back button in the web browser. This will be needed in scenarios like, when registering a new user or company in our web application.


For all who need this, I thought of sharing the code which will accomplish the same.


Response.Expires = -1;
Response.Cache.SetNoServerCaching();
Response.Cache.SetAllowResponseInBrowserHistory(false);
Response.CacheControl ="no-cache";
Response.Cache.SetNoStore();


Just paste the above code in the Page_Load event of your asp.net web page for which you want to expire when user clicks browser back button.

How to find the port no of SQL server instance?

The default port of SQL server instance will be normally 1433, for more information please refer the below link


http://support.microsoft.com/kb/287932

But It is not necessary that the SQL server instance will operate in the default port in all client places .You can find the port of SQL server 2008 instance by going to .Start->programs->Microsoft SQL Server 2008 R2->Configuration Tools->SQL Server Configuration Manager.

In SQL Server Configuration Manager go to, SQL Server Network Configuration->protocols for MSQLSERVER->double click TC/IP.

Please refer the below link for more options

http://decipherinfosys.wordpress.com/2008/01/02/finding-the-port-number-for-a-particular-sql-server-instance/
Please see the screen shot below for more details

What will happened if continue statement is placed in a for Loop?

What will happened if continue statement is placed in a for Loop?


Continue statement is used in c# to set control to the beginning of the loop.

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
for (int i = 0; i <= 10; i++)
{
Console.WriteLine (i.ToString());
continue;
Console.WriteLine("Iam never printed");
}
}
}
}

For example in the below code the line Console.WriteLine("Iam never printed"); is never executed because the control is set back to the beginning of the loop when the execution reaches the continue; stetment.

Create your Business Object using SQL Query?

Today I was designing my application and I need to write about 30 classes as the business object layer. As per my design, the business objects in my application are exact replica of my databases table. In order to save time in writing these classes I came up with a tricky logic using SQL server query .I thought of putting in this; in my blog, as it may help some others who needs to do a same exercises.


For your reference please find below a sample of my table ('CountryMaster') in databases

Ignored to write the C# business objects, I used the below query which will gives me the property entry’s for my business objects class.

select 'public '+
case when system_type_id=56 then ' int '+sys.columns.name+' { get; set; }'
when system_type_id=167 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=239 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=231 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=104 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=61 then ' DateTime '+sys.columns.name+' { get; set; }'
when system_type_id=108 then ' double '+sys.columns.name+' { get; set; }'
when system_type_id=175 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=34 then ' byte[] '+sys.columns.name+' { get; set; }'
when system_type_id=99 then ' string '+sys.columns.name+' { get; set; }'
end AS 'Class Properties'
from sys.columns
inner join sys.tables on sys.tables.name='CountryMaster'
where sys.columns.object_id=sys.tables.object_id

 
On running the above query will be getting the entry for my business object as below.

Note: the above query may not be complete for all SQL data types, If your databases is having more data types columns, the please add the appropriate c# equal ant code in the case statements.

copy the results from SQL and paste in your C#.net projects in the corresponding class file as  below.

using System;

using System.Collections.Generic;
using System.Linq;
using System.Text;
///

/// Business Objects Class
///

/// Rajesh Kamalakshan
/// 16-12-2011
namespace FiesPackage.Country.BusinessObjects
{
public class Country
{
public int CountryId { get; set; }
public string CountryCode { get; set; }
public string CountryName { get; set; }
public string InBuilt { get; set; }
public int CompanyId { get; set; }
public string Misc { get; set; }
}
}

The highlighted portion has been pasted from the SQL result using the above query.

Type initializer for threw an exception


I thought of sharing this information to others as  it may help someone having the same issues .I have an application which is done in vb .net . It works fine when run from source. This application is a dll bases and it uses crystal report. Everything worked fine with the source.

But the real problem started when I compiled the source to exe and dll .In the compiled version whenever a dll in this application is called form an exe it was throwing an exception as “type initializer for threw an exception”, I googled a lot and found that this issues is due to missing of crystal report files in the clinet machine or Live Pc.

Note: This error can also happed due to some missing files, in my case it was Crystal reports’s supporting files. You can get more details about the missing reference from the stack trace which will be available in the exception.

The solution for this issue was to download and apply the crystal Report redistributable pack, which is relevant for your OS platform where the Application is running. We can get these packs from the below site of crystal report.

http://resources.businessobjects.com/support/additional_downloads/runtime.asp#09

Since my application was compiled in VS 2008, i downloaded the Crystal Reports Basic for Visual Studio .NET 2008

As per the version of crystal report used in your application ,you have to download the respective redistributable package.

Reverse percentage calculation?

How to find X, which give you 115000 when 15% is deducted(Reverse Percentage Calculation)?

eg ax + bx = c your eqution is
ax +b = c
where a , b and c are numerical values and x is the unknown!

For the time being we can phrase the equation as

x - 15%x = 115000

then your answer would be

x(1 - 0.15) = 115000 (factorizing x)

x (0.85) = 115000 then dividing by 0.85

x = 135294.1176

substitute for x and you will find this is a correct answer!

135294.1176 - 0.15(135294.1176) = 115000


http://uk.answers.yahoo.com/question/index?qid=20070420010452AAsnX7C