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.

How to check temporary table is exist or not in SQL Server

IF OBJECT_ID('tempdb..#tempEmployee') IS NOT NULL DROP TABLE #tempEmployee

     

--    Create temp table #tempEmployee

CREATE TABLE #tempEmployee  

(   

      ID   INTEGER NOT NULL,    

      EmpName     VARCHAR(50)  NOT NULL

);   

INSERT INTO #tempEmployee(ID, EmpName)

SELECT 1, 'AAA'

UNION

SELECT 2, 'BBB'

SELECT * FROM #tempEmployee -- Get two records

DROP TABLE #tempEmployee

SELECT * FROM #tempEmployee -- Error : Invalid object name '#tempEmployee'

Please leave your comments if it is useful for you.

How to download file async in ASP.NET

While developing ASP.NET site, its common feature to allow download of a file.

Along with download large file, site should allow user to do other tasks on the page i.e. allow user to download file async.

Here are the guidelines for doing it

• Create two web page - default.aspx and Download.aspx
• On default.aspx take one hidden frame and button to download as below

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

    <title></title>

 <script type="text/javascript" language="javascript">

     function DownLoadFile(uid) {

         var iframe = document.getElementById("ifrmDownloadframe");

            iframe.src = uid;

            return false;

        }

    </script>

</head>

<body>

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

    <iframe style="display: none" id="ifrmDownloadframe"></iframe>

    <div>

    Current Date Time is <asp:Label ID="Label1" runat="server" ></asp:Label>

    <br />

        <asp:Button ID="btnDateTime" runat="server" Text="Show Date Time"

            onclick="btnDateTime_Click" />

        <asp:Button ID="btnDownload" runat="server" Text="Download" />

    </div>

    </form>

</body>

</html>

• Set Download.aspx page with required query string as a frame source as below from default.aspx

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!Page.IsPostBack)

        {

            Label1.Text = DateTime.Now.ToString();

            btnDownload.Attributes.Add("onclick", DownloadShippedFile(100));

        }

    }

    protected void btnDateTime_Click(object sender, EventArgs e)

    {

        Label1.Text = DateTime.Now.ToString();

    }

    private string DownloadShippedFile(int Id)

    {

        string result = string.Empty;

        try

        {

            string key = string.Format("Type=DownloadEmpFile&EmployeeId={0}", Id);

            string strURL = Request.ApplicationPath + "/Download.aspx?" + key;

            result = "return DownLoadFile('" + strURL + "');";

        }

        catch (Exception ex)

        {

            result = string.Format("alert('{0}');return false;", ex.Message);

        }

        return result;

    }

}

• On Download.aspx, get filepath from query string and force file to download on browser

    protected void Page_Load(object sender, EventArgs e)

    {

        if (Request.QueryString.Count > 0 && Request.QueryString["EmployeeId"] != null)

        {

            //Based upon id get file name from database or from some where else. I am considering direct file name

            string filePathFromDatabase = @"\\xyz\abc.zip";

            DownloadFile(filePathFromDatabase);

        }

    }

    private void DownloadFile(string sFileName)

    {

        FileStream fileStream = new FileStream(sFileName, FileMode.Open, FileAccess.Read);

        byte[] buffer = new byte[(int)fileStream.Length];

        fileStream.Read(buffer, 0, (int)fileStream.Length);

        fileStream.Close();

        Response.Clear();

        Response.ClearContent();

        Response.ClearHeaders();

        if (sFileName.EndsWith(".html"))

            Response.ContentType = "text/html";

        else

            Response.ContentType = "application/octet-stream";

        Response.AddHeader("Content-Length", buffer.Length.ToString());

        Response.AddHeader("Content-Disposition", "attachment;filename=\"" + sFileName + "\"");

        Response.BinaryWrite(buffer);

        Response.Flush();

        Response.End();

    }

Please leave your comments if it is useful for you.

Income Tax In India

Most of the engineers don’t understand the financial terms. Every year they have to face income tax, deductions and investment related problems.

Here is link to which simply explain the basic terms of income tax

http://in.finance.yahoo.com/news/10-essential-terms-you-should-know-before-filing-taxes-111140688.html

How to create the 3 tier / layer architecture application in .NET

Most of the projects in .NET are developed using tier / layer architecture. Though tier and layer have different meaning, but for all .NET beginners I am using same term.

Below is typical diagram of 3 tier architecture.

1. Presentation Layer: It’s a User Interface Layer (UIL) where users are interacting with an application. It also contains additional application logic. This layer may be ASP.NET, windows or mobile application. This layer interacts with middle layer. It never directly access data layer.

2.     Business Layer: It’s a Business Logic Layer (BLL) which communicates both to Presentation layer or Data Layer.

3.     Data Layer: It’s a Data Access Logic Layer (DAL) which interacting with database.

Steps to create in Visual Studio using C#

1. Create one window / web project.
2. Right click on your solution, Add one new project as shown below

3. Select one class library project, specify its name as “DataAccessLayer”, as shown below

4. Similarly create another class library name as “BusinessLogicLayer”
5. Create classes in DataAccessLayer for StudentInfo table as shown below

using System; using System.Data; using System.Collections.Generic; using System.Text; namespace DataAccessLayer {     class StudentInfo     {         public DataTable LoadAll()         {             //Write code to load all the records of studentinfo Table             DataTable objDataTable = new DataTable();             return objDataTable;         }         //Write rest of the code for each SP - Insert, Update, delete     } }

6. Build DataAccessLayer Project.
7. Add the reference of DataAccessLayer in BusinessLogicLayer project as shown below

 

8. Try to access the DataAccessLayer’s StudentInfoDAL class in BusinessLogicLayer as shown below

using System; using System.Data; using System.Collections.Generic; using System.Text; using DataAccessLayer; namespace BusinessLogicLayer {     public class StudentInfoBLL     {         /// <summary>         /// Call the StudentInfoDAL class of DataAccessLayer         /// </summary>         /// <returns></returns>         public DataTable LoadAll()         {             StudentInfoDAL objStudentInfoDAL = new StudentInfoDAL();             return objStudentInfoDAL.LoadAll();         }         //Write rest of the code to call the Insert, Update, delete method of StudentInfoDAL     } }

9. Build BusinessLogicLayer Project.
10. Similarly Add the reference of BusinessLogicLayer in your window / web project.
11. Try to access the BusinessLogicLayer’s StudentInfoBLL class.

using System; using System.Collections; using System.Configuration; using System.Data; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using BusinessLogicLayer; namespace _3TierDemo {     public partial class _Default : System.Web.UI.Page     {         protected void Page_Load(object sender, EventArgs e)         {             if (!Page.IsPostBack)             {                 BindStudentInfoData();             }         }         public void BindStudentInfoData()         {             StudentInfoBLL objStudentInfoBLL = new StudentInfoBLL();             DataTable objDataTable = objStudentInfoBLL.LoadAll();             GridView1.DataSource = objDataTable;             GridView1.DataBind();         }     } }

Finally your 3-Tier application look like.

 

Hope this is helpful. Please leave your comments for any help / suggestion.

Unable to copy file "obj\Debug\xyzproj.exe" to "bin\Debug\xyzproj.exe". The process cannot access the file 'bin\Debug\xyzproj.exe' because it is being used by another process.

If you come across the error in visual studio like “Unable to copy file "obj\Debug\xyzproj.exe" to "bin\Debug\xyzproj.exe". The process cannot access the file 'bin\Debug\xyzproj.exe' because it is being used by another process.”

Solution: - It simply means that your EXE is already running. Close already running exe or window, then build / rebuild and run your application.

Daily Yoga Chart

I was involved with different kind of exercise - heavy weight, Cardio, running, jogging, Karate, cycling, dance etc.

Now I am doing YOGA from DEC 2012. It’s my personal experience that YOGA is the best among all the exercises.

Here you can find daily YOGA Chart for reference.

It covers daily Aaasans, Pranayam, Dhayan.

Aasans - It includes different body parts exercise which makes body flexible and healthy

Pranayama - It includes different breathing exercises

Dhayan - It includes Meditation which improve mind peace

If you want more details about any Aasans, Pranayama or Dhayan, please leave comments here.

Please share with your friends and leave your valuable feedback.

The application domain in which the thread was running has been unloaded

If you are getting error “The application domain in which the thread was running has been unloaded.” while building .NET project in Visual Studio.

Solution 1:  Rebuild the whole solution

Solution 2:  Rebuild individual class libraries and project.