Today I connected my blog to GOOGLE+ . Its cool.
Reference site for Sitecore and Dot NET. Call at +91-9910045174 for any Sitecore corporate trainings and workshops.
Surendra Sharma
Search This Blog
Tuesday, July 16, 2013
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.
Monday, July 15, 2013
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!!!
Thursday, July 11, 2013
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.
Wednesday, July 10, 2013
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.
Tuesday, July 9, 2013
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
Subscribe to:
Posts (Atom)