Fresh Web Developer
ASP.NET MVC, Javscript, JQuery, Angular JS, HTML 5, CSS3
Web Services, Web Api, SQL Server, C#.NET, Azure
01 June 2017
12 October 2011
XML File Reading & Writing - Simplified
Simple Example of Reading & Writing a xml file
Sample XML File:
<?xml version="1.0" encoding="utf-8"?>
<Products>
<Product>
<Id>1</Id>
<Name>Froyo</Name>
<Cost>10.50</Cost>
</Product>
<Product>
<Id>2</Id>
<Name>Gingerbread</Name>
<Cost>20.60</Cost>
</Product>
<Product>
<Id>3</Id>
<Name>Ice Cream Sandwich</Name>
<Cost>30.70</Cost>
</Product>
</Products>
C# code to create such file:
XmlDocument doc = new XmlDocument();
XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null);
doc.AppendChild(dec);// Create the root element
XmlElement root = doc.CreateElement("Products");
doc.AppendChild(root);
//ProductList can be any collection or array etc
foreach (product p1 in productList)
{
XmlElement product = doc.CreateElement("Product");
XmlElement id = doc.CreateElement("Id");
id.InnerText = p1._id.ToString();
XmlElement name = doc.CreateElement("Name");
name.InnerText = p1._name;
XmlElement validation = doc.CreateElement("Validation");
validation.InnerText = p1._validation.ToString();
product.AppendChild(id);
product.AppendChild(name);
product.AppendChild(validation);
root.AppendChild(product);
}
string xmlOutput = doc.OuterXml;
C# code to read such a file:
if (File.Exists(FilePath))
{
XmlDocument doc = new XmlDocument();
doc.Load(FilePath);
foreach (XmlNode node in doc.DocumentElement.SelectNodes("Product"))
{
int pid = Convert.ToInt32(node.SelectSingleNode("Id").InnerText);
string pname = node.SelectSingleNode("Name").InnerText;
double cost= Convert.ToDouble(node.SelectSingleNode("Cost").InnerText);
}
}
Short an simple....something to remember :)
Sample XML File:
<?xml version="1.0" encoding="utf-8"?>
<Products>
<Product>
<Id>1</Id>
<Name>Froyo</Name>
<Cost>10.50</Cost>
</Product>
<Product>
<Id>2</Id>
<Name>Gingerbread</Name>
<Cost>20.60</Cost>
</Product>
<Product>
<Id>3</Id>
<Name>Ice Cream Sandwich</Name>
<Cost>30.70</Cost>
</Product>
</Products>
C# code to create such file:
XmlDocument doc = new XmlDocument();
XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null);
doc.AppendChild(dec);// Create the root element
XmlElement root = doc.CreateElement("Products");
doc.AppendChild(root);
//ProductList can be any collection or array etc
foreach (product p1 in productList)
{
XmlElement product = doc.CreateElement("Product");
XmlElement id = doc.CreateElement("Id");
id.InnerText = p1._id.ToString();
XmlElement name = doc.CreateElement("Name");
name.InnerText = p1._name;
XmlElement validation = doc.CreateElement("Validation");
validation.InnerText = p1._validation.ToString();
product.AppendChild(id);
product.AppendChild(name);
product.AppendChild(validation);
root.AppendChild(product);
}
string xmlOutput = doc.OuterXml;
C# code to read such a file:
if (File.Exists(FilePath))
{
XmlDocument doc = new XmlDocument();
doc.Load(FilePath);
foreach (XmlNode node in doc.DocumentElement.SelectNodes("Product"))
{
int pid = Convert.ToInt32(node.SelectSingleNode("Id").InnerText);
string pname = node.SelectSingleNode("Name").InnerText;
double cost= Convert.ToDouble(node.SelectSingleNode("Cost").InnerText);
}
}
Short an simple....something to remember :)
05 June 2011
Windows App - Detecting Running Instance
Code to detect if an windows application instance is already running or not:
static class Program
{
///
/// The main entry point for the application.
///
[STAThread]
static void Main()
{
if (IsPreviousInstanceRunning)
{
MessageBox.Show("Another instance of this application is already running...", "Application already running", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new frmMain());
}
}
private static bool IsPreviousInstanceRunning
{
get
{
// get the name of our process
string proc = Process.GetCurrentProcess().ProcessName;
// get the list of all processes by that name
Process[] processes = Process.GetProcessesByName(proc);
// if there is more than one process...
if (processes.Length > 1)
return true;
return false;
}
}
}
20 May 2011
16 May 2011
Dynamic Stored Procedures
Have you every felt the need to write a stored procedure that would be modified at runtime?
Well then let me show you how you can achieve this in SQL Server 2005 or upper version.
First of all let me tell you about the scenario where i had to write such a stored procedure:
Recently i was dealing with a large database (having more than 5 million records), and i had to provide the client with an option to search and filter data from a table with various search parameters in an asp.net web page.
Now few of the parameters when combined together in the where clause of the search query worked fine without much delay (mostly integer columns), but some the text columns took really long time especially when combined together in the where clause. For instance:
Query: select * from table 1 where
personame ='' or
cityid= '' or
state='' .....
In such a scenario i had to write an dynamic stored procedure which would accept parameters and form query according to the number of paramters passed.
Here is the stored procedure that i had wrote for fetching some patients information from an insurance database:
ALTER procedure [dbo].[sp_GetEligibility]
(@Insured nvarchar(50)=null,@GroupKey nvarchar(50)=null,MAX_RESULTS nvarchar(50)=null )
AS
BEGIN
if @MAX_RESULTS is null
begin
set @MAX_RESULTS=250
end
DECLARE @SQL nvarchar(MAX)
DECLARE @SQLWhere nvarchar(MAX)
set @SQLWhere = ''
set @Insured = LTRIM(RTRIM(@Insured))
set @GroupKey = LTRIM(RTRIM(@GroupKey))
if (@Insured is not null And @Insured <> '')
begin
--set @SQLWhere = @SQLWhere + ' and (I.ssn LIKE ''%' + @Insured + '%'' ) '
set @SQLWhere = @SQLWhere + ' and (I.ssn LIKE ''%'' + @Insured + ''%'' ) '
end
if (@GroupKey is not null And @GroupKey = '')
begin
set @GroupKey = null
end
Else if @GroupKey <> '0'
begin
--set @SQLWhere = @SQLWhere + ' and (E.fk_group = '+ @GroupKey + ')'
set @SQLWhere = @SQLWhere + ' and (E.fk_group = @GroupKey )'
end
IF not (@Insured is null and @GroupKey is null)
BEGIN
Select @SQL ='Select TOP(' + @MAX_RESULTS + ') * from (' +
'select TOP(' + @MAX_RESULTS + ') I.first_name,' +
'R.Relationship,' +
'I.last_name, ' +
'I.SSN,' +
'E.subscriber_id,' +
'I.first_name + '' ''+ I.last_name as Names,' +
'e.eff_date,' +
'e.term_date,' +
'G.group_name,' +
'B.benefitPlan_name, ' +
'convert(varchar,D.eff_date ,101) as dependEffDate,' +
'convert(varchar,d.Term_date ,101) as dependTermdate,' +
'I.pk_insured,' +
'(SELECT top 1 ' +
'(' +
' SELECT distinct(C.benefit_name + '', '')' +
' FROM EligibilityBenefitCodes B ' +
' left join BenefitPlanCodes C on c.pk_benefitplancodes = B.fk_benefitplancodes' +
' left join BenefitPlan BP on BP.pk_benefitplan= C.fk_benefitplan ' +
' WHERE C.pk_benefitplancodes = B.fk_benefitplancodes' +
' and B.fk_eligibility = E.pk_eligibility FOR XML PATH('''')' +
' )' +
')AS TypeofCoverage ' +
'from Eligibility E With(NOLOCK) ' +
'Inner Join Dependents D With(NOLOCK) ON e.fk_insured=D.fk_ insuredprimary ' +
'Inner Join Insured I With(NOLOCK) ON I.pk_insured=D.fk_ insuredDependent and E.fk_insured = I.pk_insured ' +
'Inner Join BenefitPlan B With(NOLOCK) ON E.fk_benefitplan=B.pk_ benefitplan ' +
'Inner Join Relation R With(NOLOCK) ON I.fk_relation=R.pk_relation ' +
'Inner Join [Group] G With(NOLOCK) ON E.fk_group = G.pk_group ' +
'where e.eff_date <= getDate() and E.IsArchived=0 ' +
@SQLWhere +
' UNION ' +
'select TOP(' + @MAX_RESULTS + ') I.first_name,R.Relationship,I. last_name, I.SSN, E.subscriber_id, ' +
'I.first_name + '' ''+ I.last_name as Names, e.eff_date, ' +
'e.term_date, G.group_name, B.benefitPlan_name, ' +
'convert(varchar,D.eff_date ,101) as dependEffDate, ' +
'convert(varchar,d.Term_date ,101) as dependTermdate, ' +
'I.pk_insured , ' +
'(SELECT top 1' +
' ( ' +
' SELECT distinct(C.benefit_name + '', '')' +
'FROM EligibilityBenefitCodes B ' +
'left join BenefitPlanCodes C on c.pk_benefitplancodes = B.fk_benefitplancodes ' +
'left join BenefitPlan BP on BP.pk_benefitplan= C.fk_benefitplan ' +
'WHERE C.pk_benefitplancodes = B.fk_benefitplancodes and B.fk_eligibility = E.pk_eligibility ' +
' FOR XML PATH('''') ' +
' )) AS BenefitCode ' +
'from Eligibility E With(NOLOCK) ' +
'Inner Join Dependents D With(NOLOCK) ON e.fk_insured=D.fk_ insuredprimary ' +
'Inner Join Insured I With(NOLOCK) ON I.pk_insured=D.fk_ insuredDependent ' +
'Inner Join BenefitPlan B With(NOLOCK) ON E.fk_benefitplan=B.pk_ benefitplan ' +
'Inner Join Relation R With(NOLOCK) ON I.fk_relation=R.pk_relation ' +
'Inner Join [Group] G With(NOLOCK) ON E.fk_group = G.pk_group ' +
'where e.eff_date <= getDate() and E.IsArchived=0 ' +
@SQLWhere +
')A ' +
' order by A.Relationship,A.first_name,A. last_name desc'
--EXEC(@SQL)
--For preventing SQL Injection use following method
EXEC sp_executesql @SQL, N'@Insured nvarchar(50),@GroupKey nvarchar(50)',@Insured,@ GroupKey
END
END
As you can see in this example, the stored procedure generates the sql query as string and at the end the parameters are passed to the generated final query.
This is pretty much similar to the if else conditions that we used to write in code behind to generate a query.
Well then let me show you how you can achieve this in SQL Server 2005 or upper version.
First of all let me tell you about the scenario where i had to write such a stored procedure:
Recently i was dealing with a large database (having more than 5 million records), and i had to provide the client with an option to search and filter data from a table with various search parameters in an asp.net web page.
Now few of the parameters when combined together in the where clause of the search query worked fine without much delay (mostly integer columns), but some the text columns took really long time especially when combined together in the where clause. For instance:
Query: select * from table 1 where
personame ='' or
cityid= '' or
state='' .....
In such a scenario i had to write an dynamic stored procedure which would accept parameters and form query according to the number of paramters passed.
Here is the stored procedure that i had wrote for fetching some patients information from an insurance database:
ALTER procedure [dbo].[sp_GetEligibility]
(@Insured nvarchar(50)=null,@GroupKey nvarchar(50)=null,MAX_RESULTS nvarchar(50)=null )
AS
BEGIN
if @MAX_RESULTS is null
begin
set @MAX_RESULTS=250
end
DECLARE @SQL nvarchar(MAX)
DECLARE @SQLWhere nvarchar(MAX)
set @SQLWhere = ''
set @Insured = LTRIM(RTRIM(@Insured))
set @GroupKey = LTRIM(RTRIM(@GroupKey))
if (@Insured is not null And @Insured <> '')
begin
--set @SQLWhere = @SQLWhere + ' and (I.ssn LIKE ''%' + @Insured + '%'' ) '
set @SQLWhere = @SQLWhere + ' and (I.ssn LIKE ''%'' + @Insured + ''%'' ) '
end
if (@GroupKey is not null And @GroupKey = '')
begin
set @GroupKey = null
end
Else if @GroupKey <> '0'
begin
--set @SQLWhere = @SQLWhere + ' and (E.fk_group = '+ @GroupKey + ')'
set @SQLWhere = @SQLWhere + ' and (E.fk_group = @GroupKey )'
end
IF not (@Insured is null and @GroupKey is null)
BEGIN
Select @SQL ='Select TOP(' + @MAX_RESULTS + ') * from (' +
'select TOP(' + @MAX_RESULTS + ') I.first_name,' +
'R.Relationship,' +
'I.last_name, ' +
'I.SSN,' +
'E.subscriber_id,' +
'I.first_name + '' ''+ I.last_name as Names,' +
'e.eff_date,' +
'e.term_date,' +
'G.group_name,' +
'B.benefitPlan_name, ' +
'convert(varchar,D.eff_date ,101) as dependEffDate,' +
'convert(varchar,d.Term_date ,101) as dependTermdate,' +
'I.pk_insured,' +
'(SELECT top 1 ' +
'(' +
' SELECT distinct(C.benefit_name + '', '')' +
' FROM EligibilityBenefitCodes B ' +
' left join BenefitPlanCodes C on c.pk_benefitplancodes = B.fk_benefitplancodes' +
' left join BenefitPlan BP on BP.pk_benefitplan= C.fk_benefitplan ' +
' WHERE C.pk_benefitplancodes = B.fk_benefitplancodes' +
' and B.fk_eligibility = E.pk_eligibility FOR XML PATH('''')' +
' )' +
')AS TypeofCoverage ' +
'from Eligibility E With(NOLOCK) ' +
'Inner Join Dependents D With(NOLOCK) ON e.fk_insured=D.fk_
'Inner Join Insured I With(NOLOCK) ON I.pk_insured=D.fk_
'Inner Join BenefitPlan B With(NOLOCK) ON E.fk_benefitplan=B.pk_
'Inner Join Relation R With(NOLOCK) ON I.fk_relation=R.pk_relation ' +
'Inner Join [Group] G With(NOLOCK) ON E.fk_group = G.pk_group ' +
'where e.eff_date <= getDate() and E.IsArchived=0 ' +
@SQLWhere +
' UNION ' +
'select TOP(' + @MAX_RESULTS + ') I.first_name,R.Relationship,I.
'I.first_name + '' ''+ I.last_name as Names, e.eff_date, ' +
'e.term_date, G.group_name, B.benefitPlan_name, ' +
'convert(varchar,D.eff_date ,101) as dependEffDate, ' +
'convert(varchar,d.Term_date ,101) as dependTermdate, ' +
'I.pk_insured , ' +
'(SELECT top 1' +
' ( ' +
' SELECT distinct(C.benefit_name + '', '')' +
'FROM EligibilityBenefitCodes B ' +
'left join BenefitPlanCodes C on c.pk_benefitplancodes = B.fk_benefitplancodes ' +
'left join BenefitPlan BP on BP.pk_benefitplan= C.fk_benefitplan ' +
'WHERE C.pk_benefitplancodes = B.fk_benefitplancodes and B.fk_eligibility = E.pk_eligibility ' +
' FOR XML PATH('''') ' +
' )) AS BenefitCode ' +
'from Eligibility E With(NOLOCK) ' +
'Inner Join Dependents D With(NOLOCK) ON e.fk_insured=D.fk_
'Inner Join Insured I With(NOLOCK) ON I.pk_insured=D.fk_
'Inner Join BenefitPlan B With(NOLOCK) ON E.fk_benefitplan=B.pk_
'Inner Join Relation R With(NOLOCK) ON I.fk_relation=R.pk_relation ' +
'Inner Join [Group] G With(NOLOCK) ON E.fk_group = G.pk_group ' +
'where e.eff_date <= getDate() and E.IsArchived=0 ' +
@SQLWhere +
')A ' +
' order by A.Relationship,A.first_name,A.
--EXEC(@SQL)
--For preventing SQL Injection use following method
EXEC sp_executesql @SQL, N'@Insured nvarchar(50),@GroupKey nvarchar(50)',@Insured,@
END
END
As you can see in this example, the stored procedure generates the sql query as string and at the end the parameters are passed to the generated final query.
This is pretty much similar to the if else conditions that we used to write in code behind to generate a query.
Subscribe to:
Posts (Atom)