Best number of world

The world's best number is 73 as

• 73 is 21^st prime number
• The number 21 = 7 X 3
• Binary representation of 21 is 10101 which is palindrome
• Its mirror is 37 which is also prime number. 37 is the 12^th prime number
• Binary representation of 73 is 1001001 which is palindrome

Human pee to charge cellphones

Scientists developed a technology from which human urine is used to charge small electric gazettes such as mobile. Beauty of this technology is that its eco-friendly and not dependent on external factors such as sun etc.

Please read complete article from here

http://www.techgig.com/tech-news/editors-pick/Now-human-pee-to-charge-cellphones-18915

World‘s most advanced computer dumber than a five-year old

Here is interesting article about Artificial Intelligence (AI) where engineers are trying to implement commonsense functionality in computer developed by MIT.

More details are here

http://www.techgig.com/tech-news/editors-pick/World-s-most-advanced-computer-dumber-than-a-five-year-old-18902

Connected my blog to GOOGLE+

Today I connected my blog to GOOGLE+ . Its cool.

How to get one desire record by using WHERE and ORDER BY clause

Create “tblSetting” using following query

/****** Object:  Table [dbo].[tblSetting]    Script Date: 07/16/2013 11:43:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblSetting](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [UniversityID] [int] NOT NULL,

      [CollegeID] [int] NOT NULL,

      [cItem] [varchar](50) NOT NULL,

      [cDescription] [varchar](200) NOT NULL,

      [cValue] [varchar](200) NOT NULL

CONSTRAINT [Pk_ID] PRIMARY KEY CLUSTERED

(

      [ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Get record according to College Level or university level or global level such that we get only one record

Conditions are

1. Get record according to college and University level i.e. where UniversityID AND CollegeID is non zero

2. Get record according to college level i.e. If 1st condition does not match then execute this where UniversityID is zero AND CollegeID is non zero

3. Get record according to University level i.e. If 1st, 2nd condition does not match then execute this where UniversityID is non zero AND CollegeID is zero

4. Get record according to global setting level i.e. If 1st, 2nd, 3rd condition does not match then execute this where both UniversityID AND CollegeID is zero

Get single record by using only one SELECT query.

I follow following way to get the desire result. Execute each condition one by one and check the result

Condition 4

Insert Following records where UniversityID AND CollegeID is zero

INSERT INTO dbo.tblSetting (UniversityID, CollegeID, cItem, cDescription, cValue) VALUES(0,0,'FileUploadPath','File Upload Path','\\XYZ\GlobalReports\')

SELECT TOP 1 * FROM tblSetting WHERE cItem = 'FileUploadPath' AND

(UniversityID = 177 OR UniversityID = 0) AND (CollegeID = 65 OR CollegeID = 0) ORDER BY CollegeID DESC , UniversityID DESC

Here our 1st, 2nd, 3rd conditions are not satisfied, however 4th condition is valid. So its output is

ID	UniversityID	CollegeID	cItem	cDescription	cValue
1	0	0	FileUploadPath	File Upload Path	\\XYZ\GlobalReports\

Condition 3

Insert Following records where UniversityID is non zero AND CollegeID is zero

INSERT INTO dbo.tblSetting (UniversityID, CollegeID, cItem, cDescription, cValue) VALUES(177,0,'FileUploadPath','File Upload Path','\\XYZ\UnivReports\')

SELECT TOP 1 * FROM tblSetting WHERE cItem = 'FileUploadPath' AND

(UniversityID = 177 OR UniversityID = 0) AND (CollegeID = 65 OR CollegeID = 0)ORDER BY CollegeID DESC , UniversityID DESC

Here 1st, 2nd, 4rd conditions are not satisfied, however 3rd condition is valid. So its output is

ID	UniversityID	CollegeID	cItem	cDescription	cValue
2	177	0	FileUploadPath	File Upload Path	\\XYZ\UnivReports\

Condition 2

Insert Following records where UniversityID is zero AND CollegeID is non zero

INSERT INTO dbo.tblSetting (UniversityID, CollegeID, cItem, cDescription, cValue) VALUES(0,65,'FileUploadPath','File Upload Path','\\XYZ\CollegeReports\')

SELECT TOP 1 * FROM tblSetting WHERE cItem = 'FileUploadPath' AND

(UniversityID = 177 OR UniversityID = 0) AND (CollegeID = 65 OR CollegeID = 0)ORDER BY CollegeID DESC , UniversityID DESC

Here our 1st, 3rd, 4th conditions are not satisfied, however 2th condition is valid. So its output is

ID	UniversityID	CollegeID	cItem	cDescription	cValue
3	0	65	FileUploadPath	File Upload Path	\\XYZ\CollegeReports\

Condition 4

Insert Following records where both UniversityID AND CollegeID is non zero

INSERT INTO dbo.tblSetting (UniversityID, CollegeID, cItem, cDescription, cValue) VALUES(177,65,'FileUploadPath','File Upload Path','\\XYZ\UnivCollegeReports\')

SELECT TOP 1 * FROM tblSetting WHERE cItem = 'FileUploadPath' AND

(UniversityID = 177 OR UniversityID = 0) AND (CollegeID = 65 OR CollegeID = 0)ORDER BY CollegeID DESC , UniversityID DESC

Here our 1st, 2nd, 3rd conditions are not satisfied, however 4th condition is valid. So its output is

ID	UniversityID	CollegeID	cItem	cDescription	cValue
4	177	65	FileUploadPath	File Upload Path	\\XYZ\UnivCollegeReports\

As you can analyze we are using same SELECT query for all output

SELECT TOP 1 * FROM tblSetting WHERE cItem = 'FileUploadPath' AND

(UniversityID = 177 OR UniversityID = 0) AND (CollegeID = 65 OR CollegeID = 0)ORDER BY CollegeID DESC , UniversityID DESC

I believe there are multiple ways of doing this in SQL Server. Let me know if you have any other way of achieving the desire result.

Please leave your comments / suggestion.

Bhaag Milkha Bhaag

Watched ‘Bhaag Milkha Bhaag' based on true story of Indian athlete Milkha Singh well known as 'The Flying Sikh' on 14-July-2013. Whattttt a movie.  

My rating - 5 out of 5.

It has everything that viewer want - love, emotions, jokes, fighting, army, patriotism, desi village life style, foreign land, achievements, etc.

It reminds me school days in my village where I used to carry Takhti (wooden square on which one can write), Dwat(Ink that one have to prepare from raw material), Kalam (Pen that you have to create from one plant), murga (school punishment),  Kurta Pazama (thats my school dress), farms and fields near to my school,  desert area around of my village, water canals near to village, all deshi people of hariyana, army base and camps.

Worth to see!!!

Transations in SQL Server and ADO.NET

When developing any database driven application in .NET, one has to deal with ADO.NET where scenario is that either saves all records or none.

How to do it?

• Option 1: Use .NET transaction as below

    public class Employee

    {

        public int EmployeeID { get; set; }

        public int EmployeeName { get; set; }

    }

    private static bool ExecuteQuery(List<Employee> empList, ref string error)

    {

        bool result = false;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["SQLCon"]))

        {

            connection.Open();

            SqlCommand command = new SqlCommand();

            SqlTransaction transaction = connection.BeginTransaction("EmployeeTran");

            command.Connection = connection;

            command.Transaction = transaction;

            try

            {

                foreach (Employee emp in empList)

                {

                    command.CommandText = string.Format("Insert into Employee (EmployeeID, EmpName) VALUES ({0}, '{1}')",

                        emp.EmployeeID, emp.EmployeeName);

                    command.ExecuteNonQuery();

                }

                transaction.Commit();

                result = true;

            }

            catch (Exception ex)

            {

                //Error occurred

                error = "Error 1:" + ex.Message;

                try

                {

                    transaction.Rollback(); // roll back the transaction.

                }

                catch (Exception ex2)

                {

                    error += "Error 2:" + ex2.Message;

                }

            }

        }

        return result;

    }

• Option 2:

          Pass all the Employee data in XML format to stored procedure and handle insert transaction in stored procedure.