Thursday, December 31, 2009

Microsoft Ajax Minifier 1.1

There is a new release of microsoft ajax minifier on codeplex.com

What Ajax minifier does?

Ajax Minifier reduce the size of your JavaScript files to improve the performance of you Ajax enabled site.

What does it support?

The Microsoft Ajax Minifier supports two levels of minification: normal crunching and hypercrunching. Normal crunching refers to the process of removing unnecessary whitespace, comments, semicolons, and curly braces. Hypercrunching refers to the process of shortening the names of local variables and removing unreachable code.

 

You can review the project at

http://aspnet.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=35893

Wednesday, December 30, 2009

Dynamic Populate Extender example Ajax Control Toolkit, manual populate control

Ajax control toolkit have control "Dynamic Populate Extender".

Dynamic populate extender is a control which will replace html/content of any control based on the web service result.
To populate you need to create a web service method which have single parameter with name "contextKey". If you do not specify contextKey variable or you make its name different then your extender will fire error "Web Service call failed: 500".

There are 2 ways to populate content.

Dynamic Populate option 1) You can specify attribute PopulateTriggerControlID="YourControlId" and then you click on the control and your data will be populated

Dynamic Populate option 2) You can write a javascript code which will call the populate event of Dynamic Populate Extender.
Script is

   1:  <script type="text/javascript">
   2:  function UpdateControl(value) {
   3:  var behavior = $find('dp1');
   4:  if (behavior)   {
   5:       behavior.populate(value);   
   6:      }
   7:  }
   8:  </script>

Based on above script “value” will be passed as “contextKey” in the web service.

Html code to for this example is

   1:  Enter value
   2:  <input type="text" id="txtConetnt" onblur="UpdateControl(this.value);" />
   3:  <asp:Panel ID="Panel1" runat="server"></asp:Panel>
   4:  <ajaxToolkit:DynamicPopulateExtender ID="DynamicPopulateExtender1" runat="server" 
   5:      TargetControlID="Panel1" BehaviorID="dp1" ServiceMethod="GetValue">
   6:  </ajaxToolkit:DynamicPopulateExtender>

Web service code for this example is

   1:  'VB
   2:  <System.Web.Services.WebMethod()> _
   3:  <System.Web.Script.Services.ScriptMethod()> _
   4:  Public Shared Function GetValue(ByVal contextKey As String) As String
   5:    Return String.Format("<span style='font-family:courier new;font-weight:bold;'>{0}</span>", "You entered value " + contextKey)
   6:  End Function
   1:  //C#
   2:  [System.Web.Services.WebMethod]
   3:  [System.Web.Script.Services.ScriptMethod]
   4:  Public string GetValue(string contextKey)
   5:  {
   6:       return String.Format("<span style='font-family:courier new;font-weight:bold;'>{0}</span>", "You entered value " + contextKey);
   7:  }

 

Hope this example help you to use Dynamic Populate Extender.

Error during save changes of sql table design in SQL server management studio 2008.

In SQL Server Management Studio 2008,if we open table in design mode to make changes and then click save after making changes in table structure it will give error.

Error says.



this is due to there is "Prevent Saving changes that require table re-creation" option is enabled in SSMS-2008.



To work around this Just Un-Check "Prevent Saving changes that require table re-creation" option from Tools > Options >Designers.

hope this will help you.

Thanks.


Tuesday, December 29, 2009

Various URL rewriting methods in ASP.Net C# Overview


When we have multiple parameters in our URL, at the time it is possible that hackers can identify our logic based on parameter and try to get into the system with those parameters. To resolve this issue we can use URL rewriting. URL rewriting is the process of hiding our parameterized URL and make it simple, browser and crawler friendly URL.
Example:
URL rewriting is also useful for Search Engine Optimization (SEO). When you use parameter on your single page and load different data. Search engine (i.e. Google) will consider it as single page. But when you use URL rewriting it will consider that all pages are different.

Ways of Programming

In .Net there are 3 ways to achieve URL Rewriting.
1.       With Http handler factory
2.       With Http Module
3.       Global.asax

URL Rewriting with Http Handler Factory

Benefits

When you use handler based URL rewriting you can specify that for particular extension this handler will be called. So for each request your code will not execute. What handler factory does is select appropriate handler based on your code and returns it to compiler to handle the request.

Disadvantages

When you want to handle directory without any page you need to specify wild card for the handler factory. In this case each request will be go through your code. And for image or static files it will fire error (if not handled) that cannot parse file. To avoid this issue you can specify static file handler in web.config file above the wild card (such as .jpg, .gif), so your file will be treated as static.

URL Rewriting with Http Module

Benefits

When you are using this method you do not require to specify any extension or wild card in web.config. All file will be checked for rewrite and if any code is not available then it will load file as normal http request.

Disadvantages

In this case request for each either a page or static file like images will pass through the module. So it will cause more processing. If the code is not proper then it will execute each of your rule/code for all requests.

Global.asax

Benefits

This will work same as http module.

Disadvantages

This method does have one more disadvantage over http module. In IIS 7 some of methods are restricted so you either need to recode or need to go over http module.

Friday, December 18, 2009

Insert/Update/Delete data in sql-xml column

How to manipulate data in Untyped(sql-xml) column.

With advent of xml datatype in sql server, developer get a power of xml in sql server. with the help of "XML DML" we can insert,update node in sql-xml column.
this sql-xml column also known as Untyped column.

we can reduce number of records in table by using this xml column wisely.
for example let's say we have a "country" table which stores name and ID of country in SQL table.now if we want to add state for all country, we have a separate table "state" and for each state there will be separate entry in table along with same country id.

we can handle this situation by storing all state of that country in "country" table as xml nodes.

At many points you will find this sql-xml data type helpful and powerful.this datatype can be queried with almost same concept as xml query except some syntax variation.

declare @Country as table(ID int IDENTITY(1,1) PRIMARY KEY,Name varchar(20),States xml)
INSERT INTO @Country
select 'India','<states><state ID="1">Gujarat</state><state ID="2">Rajasthan</state><state ID="3">AP</state><state ID="4">MP</state><state ID="5">UP</state></states>'
union all
select 'USA','<states><state ID="1">Alaska</state><state ID="2">Alabama</state><state ID="3">Florida</state><state ID="4">Idaho</state></states>'
union all
select 'Uk','<states><state ID="1">Uk1</state><state ID="2">Uk-2</state></states>'


/*SELECT DATA:: this will select all state from table where ID=1(India)*/

declare @States xml
set @States=(select states from @Country where id=1)
select tab.col.value('@ID','int') as ID,tab.col.value('.','varchar(20)') as Name from @States.nodes('states/state') tab(col)
--where tab.col.value('@ID','int')=1
print 'State Selected'

/*INSERT DATA::this will insert a state where id=1(India) with Name "Arunachal Pradesh"*/

Update @Country
set States.modify('insert<state ID="6">Arunachal Pradesh</state>into (/states)[1]')
where ID=1
print 'State Inserted'

/*UPDATE DATA::this will update name of state with id=6 to Arunachal and country id=1*/

Update @Country
set States.modify('replace value of(/states/state[@ID=6]/text())[1] with "Aurnachal"')
where ID=1
print 'State Updated'

/*DELETE DATA::this will delete where Id=5 (UP) and countryid=1 from sql-xml column*/

Update @Country
set States.modify('delete (/states/state[@ID=5])')
where ID=1

print 'state Deleted'

/*
http://www.simple-talk.com/sql/t-sql-programming/xml-data-modification-language-workbench/
*/

hope this will help you.

thanks.

Monday, December 14, 2009

WrapText function in javascript.

Some times in highly user interactive sites like forums and community domain. User may enter continues text as long word and which will not be warped automatically.
To wrap text it needs White spaces between words, if it can't find space it will distort our design and looks of page will not remain as it is. to handle such condition we can split long word after specified characters.
we can do this server side or client side. i have written a function in Javascript.Called WrapText.
Regular expression is the best way to do this task with effective and minimal code.
you can use this as per you need ,or using same Regular expression you can write function in C# or VB.
here is the function in javascript.
<script type="text/javascript" language="javascript">
function WrapText(Input) {
var FillChar = "</br>";
var MaxLength = 10;
var pattern = new RegExp("([^\\s-]
{" + MaxLength + "})", "g");
var myString = Input;
return myString.replace(pattern, "$1"+FillChar);
}
alert(WrapText('HI_JAVSCRIPT_HOW_YOU_DOING'));
</script>

Thanks,

How to Insert/Update in XML File.

There isn't any direct update and insert methods available for XML document . we have logically to append and remove child nodes from xml document.
There is good stuff provided for managing xml document in "System.Xml" name space in .net.
here are the steps to perform this simple but most handy task whilse development.
(1) string xmlPath = Server.MapPath("~/states.xml"); //Get path of xml file
(2) XmlDocument xDoc = new XmlDocument(); //Use XmlDocument class to perform operation.
(3) xDoc.Load(xmlPath); //Load xml file in to xml document.

(4)
string xNodeFilter = "/states/state[@ID='" + StateID + "']"; //Create filter for Select Node if it it already Exists in File.

(5)
XmlNode xNodeState = xDoc.SelectSingleNode(xNodeFilter); //Select Node if it it already Exists in File.

(6)
if (xNodeState != null) { xDoc.ChildNodes[0].RemoveChild(xNodeState); } //Remove Node if it is already there in File

(7)
XmlAttribute xAttrID = xDoc.CreateAttribute("ID"); XmlAttribute xAttrRegionID = xDoc.CreateAttribute("RegionId"); XmlAttribute xAttrName = xDoc.CreateAttribute("Name"); XmlAttribute xAttrColor = xDoc.CreateAttribute("Colour");

//Create Attribute of node to be added.

(8)
xNodeState = xDoc.CreateNode(XmlNodeType.Element, "state", "");//Create node to be added.

(9)
xAttrID.Value = "1"; xAttrRegionID.Value = "0"; xAttrName.Value = "Gujarat"; xAttrColor.Value = "#00ff00";//Set Values Of attribute just created.

(10)
xNodeState.Attributes.Append(xAttrID); xNodeState.Attributes.Append(xAttrRegionID); xNodeState.Attributes.Append(xAttrName); xNodeState.Attributes.Append(xAttrColor); //Append Attribures to node.

(11) xNodeState.InnerXml="This is my mother land"; //Set Inner data of Node

(12) xDoc.ChildNodes[0].AppendChild(xNodeState); xDoc.Save(xmlPath); //Add child to document and Save document.
This are the steps. after putting this steps.please make sure that xml file is not read only.
hope this will help you.
Thanks.

Read XML In to the SQL table.

We can read whole xml file into the sql table using Bulk Copy command in SQL server. some time this command gives Exception "you do not have permission to use the bulk load statement". It says that you must have assigned rights to execute bulk load statement.this can be assigned by database administrator.

After reading file from specific location using bulk load command we can query it using built-in SQL commands, which are shipped with installation sql server 2005.

As sql server 2005 supports XML datatype as native datatype. it will be very easy to convert xml into SQL table.

Here is the code to read from xml file and out them as sql table.

declare @xml as xml

declare @xml xml SELECT @xml = BulkColumn FROM OPENROWSET(BULK 'c:\states.xml', SINGLE_BLOB) TempXML; SELECT Tab.Col.value('@ID','varchar(2)') as ID,Tab.Col.value('@Name','varchar(20)') from @xml.nodes('states/state') Tab(Col)

hope this will help. Thanks.

Tuesday, November 10, 2009

TimeZone Conversion in C#

hi.
.Net framework have some powerful classes for Date conversion.some time we need to convert date between different time Zones in code behind.In some cases difference in Time Zone leads difference in Date between locations.

To make this conversion .Net have very easy way.Just Provide leg/lead difference of hour/minute/seconds as argument in built in Function.

Suppose you want to convert Current date time to EST(GMT-5). this can be done by code.

DateTime currentDt = DateTime.Now.ToUniversalTime();//Current Converted to UTC
currentDt = DateTime.SpecifyKind(currentDt, DateTimeKind.Utc);//Say to runtime that this date is UTC date.

/*
we can convert between timezones with referance to UTC(GMT) time.
.Net Provides TimeZoneInfo class which provides Statis functions to make easy conversion between Time Zones.

Though It Takes many arguments ,Key point is ConvertTime method and TimeSpan
we can specify TimeSpan as diferance between GMT to EST,GMT to PST etc.
*/

lblGMT.Text = TimeZoneInfo.ConvertTime(currentDt, TimeZoneInfo.CreateCustomTimeZone("1", new TimeSpan(-5, 0, 0), "EST", "EST", "EDT", new TimeZoneInfo.AdjustmentRule[] { })).ToString();

you can read more about TimeZone and TimezoneInfo at

http://www.danrigsby.com/blog/index.php/2008/08/24/timezone-vs-timezoneinfo-in-net/

http://msdn.microsoft.com/en-us/library/system.timezoneinfo.aspx

hope this will help you.

thanks.

Saturday, November 7, 2009

Create XML document Using T-SQL Statement

Hi.

There are many ways in asp.net for generating RSS file.you can use string concatenation,loop iteration or using more advanced techniques you can Use LINQ-TO-XML for creating xml document for RSS feed.
i was searching a in SQL server by which i can generate RSS using T-SQL query. SQL Server is shiped with many new function and operators which help us to generate XML from SQL server.

There are many options Like RAW Mode ,AUTO Mode and EXPLICIT Mode.

auto and raw mode can be used for simple xml tag generation ,while Explicit mode is vary powerful mode to generate whole XML documents.

I Will show you the code how to Generate XML file for Rss Feed.

Let's say I have a table Name "myFeeds" which looks like:




Now i want to Create Rss Feed File Using data of this table ,which should look like:




and Here is The code which will give you content as xml which can be written Feed file.

Code:

select 1 as Tag,Null as parent,'2.0' as 'rss!1!version',Null as
'Link!2!rel',Null as [Channel!3!Title!element], Null as
[Channel!3!Link!element],null as [Channel!3!Description!element],Null as
[Channel!3!language!element], NULL as [Item!4!Title!element], NULL as
[Item!4!Url!element], NULL as [Item!4!PubDate!element]



UNION ALL



select 2,1,Null,'alternate' as 'Link!2!rel',Null as
[Channel!3!Title!element],Null as [Channel!3!Link!element], Null as
[Channel!3!Description!element],Null as [Channel!3!language!element],NULL as
[Item!3!Title!element], NULL as [Item!3!Url!element], NULL as
[Item!3!PubDate!element]



UNION ALL



SELECT 3,2,Null,Null, 'My Website feed channel title' as
[Channel!2!Title!element], 'http://www.dhameliya.blogspot.com' as
[Channel!2!Link!element], 'This is channel description' as
[Channel!2!Description!element], 'en-us' as [Channel!2!language!element], NULL
as [Item!3!Title!element], NULL as [Item!3!Url!element], NULL as
[Item!3!PubDate!element]



UNUNION ALL



SELECT 4, 3,Null,Null,Null,Null,Null,Null, sa.Title, sa.Url, sa.PubDate FROM
myFeeds sa



For XML EXPLICIT


--End of Code

at first site this is very complex code but when you run this without "For XML EXPLICIT" Statements.it is simple a transformation table which looks like :

Transformation Table:



When you look at Tag and Parent Column you can easily judge what actually happening in this query.


Hope will help you.

happy programming.

thanks.

Friday, November 6, 2009

How to select Top N Records from datatable

hi.
In a web application with asp.net, c# in i want to select Top N records fro datatable .this is possible in many ways.but preffered way are here.

We can select Top (N) records from data-table using LINQ.

//Suppose this is datatablse whic have 1000 records
DataTable dtAllRecords = new DataTable();
//(1)How to select Top 100
var AllRecords = from item in dtAllRecords.AsEnumerable()
select item;
var top100 = AllRecords.Take(100);
//(1)How To select Second 100 (records from 101 to 200)
var Second100 = AllRecords.Skip(100).Take(100);
/*
Using this Take and Skip method of LINQ we can easily select top N records from datatabse.
*/

Above code use two powerful methods of LINQ to select Top 100 and Second 100 records from datatabse. you can you Take and Skip methods to select Top(N) records from datatable

We can also select Top N records without using LINQ.
here is the sample code .

//Table with 20 records
DataTable dtDates = new DataTable();
dtDates.Columns.Add("Bdates");
for (int i = 0; i <>
{
dtDates.Rows.Add(DateTime.Now.AddDays(i));
}
//Want to select top 10 from above Datatatable
DataTable dtTop10 = dtDates.Clone();
Int32 N = 0;
while (N <>
{
dtTop10.ImportRow(dtDates.Rows[N]);
N++;
}


Hope this will help.

thanks.

Wednesday, November 4, 2009

How to read Feed from Remote path using Syndication

hi.
it is very hard task to read all type of Feeds and atoms Generically,because all feeds on web are not published with same standard.
Microsoft .Net framework 3.5 is shipped with new syndication library which provide very easy and reliable way to read this feeds in our system.

Then you can process them as you need using LINQ. here is the method the read feed as "SyndicationFeed".

Method to read Feed:

public SyndicationFeed ReadFeedFrom(string url)
{
XmlReaderSettings settings = new XmlReaderSettings();
settings.IgnoreWhitespace = true;
settings.CheckCharacters = true;
settings.CloseInput = true;
settings.IgnoreComments = true;
settings.IgnoreProcessingInstructions = true;
settings.ProhibitDtd = false;

try
{
using (XmlReader reader = XmlReader.Create(url, settings))
{
SyndicationFeedFormatter GenericFeedFormatter = null;
Atom10FeedFormatter atom = new Atom10FeedFormatter();
Rss20FeedFormatter rss = new Rss20FeedFormatter();

if (reader.ReadState == ReadState.Initial)
{
reader.MoveToContent();
}
//If Atom can read it is Atom feed
if (atom.CanRead(reader))
{
GenericFeedFormatter = atom;
}
if (rss.CanRead(reader))
{
GenericFeedFormatter = rss;
}
if (GenericFeedFormatter == null)
{
return null;
}
GenericFeedFormatter.ReadFrom(reader);
return GenericFeedFormatter.Feed;

}
}
catch
{
return null;
}
}

If feeds is formatted according to Atom/Rss it will Choose proper formatter and returns you as SyndicationFeed . after that this class can be iterated using LINQ.

Process them with LINQ:

SyndicationFeed feed = ReadFeedFrom("http://feeds.feedburner.com/mobileburn/rss2");
var q = from t2 in feed.Items select new { Title = t2.Title.Text, Url = t2.Links[0].Uri.ToString(), pubDate = t2.PublishDate.DateTime };

then you can bind this LINQ variable to Grid or save them to database as per requirement.

Hope this will help.

thanks.

Tuesday, November 3, 2009

Perform UNION on Datatables in LINQ

hi.
some times we need to merge two tables in C#.we can use merge method of Data table.but if you have complex data and you want to perform some editional operations like sorting etc. before merging them you can use LINQ to dataset.

you can see working example below.
Table-1:

public DataTable GetTable1()
{
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("BirthDate");
dt.Rows.Add("1", "Herry", "Thu, 18 Jan 2007 11:00:00 GMT");
dt.Rows.Add("1", "Mac", "Thu, 19 Jan 2007 11:00:00 GMT");
dt.Rows.Add("1", "Wisly", "Thu, 20 Jan 2007 11:00:00 GMT");
return dt;
}


Table-2:

public DataTable GetTable2()
{
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("BirthDate");
dt.Rows.Add("1", "July", "1/19/2009 4:28:24");
dt.Rows.Add("1", "Anna", "1/20/2009 4:28:24");
dt.Rows.Add("1", "Jeniffer", "1/21/2009 4:28:24");
return dt;
}

Now you can join both tables using below code.

DataTable tmpDatatable = GetTable1().Clone();
var all = (from boys in GetTable1().AsEnumerable()
orderby boys["BirthDate"] descending
select boys).Union(from girls in GetTable2().AsEnumerable()
orderby girls["BirthDate"] descending
select girls);
all.CopyToDataTable(tmpDatatable, LoadOption.OverwriteChanges);
grvAll.DataSource = tmpDatatable;
grvAll.DataBind();

Thanks.

Monday, October 26, 2009

How to use ListView Control of ASP.Net 3.5?

Hi Friends,

ASP.net 3.5 have one new control called ListView as compare to .Net framework 2.0.

Here i will mention a few benefits/use of ListView.

Now when we want to display items [our data] in repeated column but we need to break it to another row. for certain scenario we were previously using DataList of v2.0

What is difference between DataList and ListView
DataList renders table structure with  and  structure, while ListView have free form like repeater.

Now what is difference between Repeater and ListView
Repeater can repeat same ItemTemplate for each of your data item, while in ListView you will be able to repeate Same ItemTemplate and break it through GroupTemplate in LayoutTemplate.

LayoutTemplate is basically a template which will cover all of your items. In the LayoutTemplate you need to specify the GroupTemplate.

<LayoutTemplate >
  <div>
     <asp:placeholder ID="groupPlaceholder" runat="server">
  </asp:placeholder></div>
</LayoutTemplate >

LayoutTemplate must contain either GroupTemplate or ItemTemplate to bind and load the data.

Now we will have a question that what is GroupTemplate?
In GroupTemplate you will be able to specify your template of group of item.

<GroupTemplate >
  <ul>
     <asp:placeholder id="itemPlaceholder" runat="server">
  </asp:placeholder></ul>
</GroupTemplate >

In group template you must specify the itemPlaceholder in order to achive the items to be bound.

Now your will specify your item view in ItemTemplate
<ItemTemplate>
<!-- Your Item Code -->
  <li>
     <%# Eval("Name") %>
  </li>
</ItemTemplate>

Now you will say where would i define that how much item should be covered in one group. Here is the answer, you need to assign it to GroupItemCount property of ListView.

<asp:ListView ID="ListView1" runat="server" GroupItemCount="3">
///Your templates will go here LayoutTemplate, GroupTemplate, ItemTemplate
</asp:ListView>

Above code will repeat 3 item per group.

So output of this sample code will be like
<div>
  <ul>
     <li>Item 1</li>
     <li>Item 2</li>
     <li>Item 3</li>
  </ul>
  <ul>
     <li>Item 4</li>
     <li>Item 5</li>
     <li>Item 6</li>
  </ul>
</div>

Hope it helps.
I will post further details in another article.

Have a nice programming day.....

Tuesday, September 15, 2009

Common Issues durinng migration on IIS6

hi.
during migrating of well running website from one server to another server some times create common issues. this can be avoid by keeping some issues in mind.
common issues are like.

(1) Page not found Error on each request.
(2)Only .html are served not .aspx pages.
(3)URL rewriting not working.
(4) Loop of Request..
........etc.

one of the following may be the cause of these issues.

(1) Ensure proper version of .Net framework is set or not from IIS.

(2) Ensure that All Extensions are mapped in configuration section of IIS.

(3) Ensure that Proper Wild card is inserted in Configuration section of IIS.

-This may be issue if you have used URL-Rewriting.

(4) Make sure that you have use all Extension as per type of platform of server(32-bit/64-bit)

-if you have 64-bit you have to mapped .dll from Framework64 directory.

(5) Check that you haven't use IP address of old server in your Code.

(6) Host headers are not configured properly.

-Loop of request may occur if you have configured Host headers incorrectly.

Thanks.

Monday, September 7, 2009

Common things to consider during migration from IIS6-to-IIS7

We have to face many problems in the initial stage of moving from one version to another version.
Some times your application working smoothly in iis6 will not work on IIS7.

There may be one or many reasons of such problem. In such condition first of all check these things on your machine.

1) Whether or not asp.net installed on your machine.

Go to Control panel -> add remove windows component -> Expand Internet information server Select asp.net.

2) Check Application pool:

iis7 have two mode of operation

a) Classic-Pipeline mode.

b) Integrated -Pipeline mode

if your application have any code in Global Application file(Global.asax) . that is necessary to execute first (URL rewriting). configure application to use Classic pipeline mode.

3)Check all modules in iis7 are assigned according to type of resource.

Static handler in iis is not mapped properly: in that case static resources like Images ,JavaScript ,HTML page or stylesheet(css) not are not server by iis7.
Page handler not mapped :Dynamic(aspx) Pages will not server by IIS.

for more detail on iis please visit about setting up iis visit

http://forums.iis.net

Thanks.

Thursday, July 30, 2009

Encrypt/Decrypt data in .Net

hi.

this is all about how to encrypt and decrypt string data using RijndaelManaged Class in .Net.
I will provide you whole class to perform encryption and decryption.

This is The Code.

using System;
using System.Security.Cryptography;
using System.Text;
using System.IO;
public class CryptoUtils
{
const string Password = "
const string SaltValue = "_This_Is_My_Sault_";
RijndaelManaged RejManaged;
public CryptoUtils()
{
byte[] SaltBytes = Encoding.UTF8.GetBytes(SaltValue);
PasswordDeriveBytes password = new PasswordDeriveBytes(Password, SaltBytes);
RejManaged = new RijndaelManaged();
RejManaged.Key = password.GetBytes(RejManaged.KeySize / 8);
RejManaged.IV = password.GetBytes(RejManaged.BlockSize / 8);
}
public string EncryptText(string text)
{
try
{
byte[] inputByteArray = Encoding.UTF8.GetBytes(text);
MemoryStream memoryStream = new MemoryStream();
CryptoStream cryptoStream = new CryptoStream(memoryStream, RejManaged.CreateEncryptor(), CryptoStreamMode.Write);
cryptoStream.Write(inputByteArray, 0, inputByteArray.Length);
cryptoStream.FlushFinalBlock();
return Convert.ToBase64String(memoryStream.ToArray());
}
#region Catch Exceptions
catch (Exception ex)
{
throw ex;
}
#endregion
}

public string DecryptText(string text)
{

try
{
MemoryStream memoryStream = new MemoryStream();
byte[] inputByteArray = Convert.FromBase64String(text);
CryptoStream cryptoStream = new CryptoStream(memoryStream, RejManaged.CreateDecryptor(), CryptoStreamMode.Write);
cryptoStream.Write(inputByteArray, 0, inputByteArray.Length);
cryptoStream.FlushFinalBlock();
return Encoding.UTF8.GetString(memoryStream.ToArray());
}
#region Catch Exceptions
catch (Exception ex)
{
throw ex;
}
#endregion
}
}

Create class file and paste above code in that file.
here is the sample code about how to Use this.

CryptoUtils objCryptoUtils = new CryptoUtils();
Response.Write(objCryptoUtils.EncryptText("Haresh?><)(*&^%$#@!"));
Response.Write("
");

Response.Write(objCryptoUtils.DecryptText(objCryptoUtils.EncryptText("Haresh?><)(*&^%$#@!")));

Thanks.

Saturday, July 18, 2009

How to drop All tables from Database.

hi.
we can drop all table in one database using single line Query.There is an undocumented procedure which is very useful in seach Operation which seems complecated at first site.

Here is how to use this Procedure to drop all tables.

Use DROPTEST
EXEC sp_MSforeachtable @command1="drop table ?"


we can change statements inside @command1 to perform different operation on all tables.

Thanks.

Friday, July 17, 2009

Download file from server using Response.TransmitFile

hi.
we can user Response.TransmitFile to Allow users to download file.
here is the code to download file.You have to change Response.ContentType attribute as per type of your content.

Here is the sample code.

if (File.Exists(FilePath))
{
Response.ContentType = "image/jpeg"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + "FileName.jpeg"); Response.TransmitFile(FilePath);
Response.End();
}

Thanks.

Expost Data to Excel from asp.net GridView

Hi.
This is the code i Learn to Expost data to Excel file from GridView.
You Have to make EnableEventValidation="false" to run this code Without any error.
other wise it will Throw Exception RegisterForEventValidation can only be called during Render();
Here is the Code.

protected void btnExport_Click(object sender, EventArgs e)
{
this.grvCountry.AllowPaging = false;
this.grvCountry.AllowSorting = false;
this.grvCountry.EditIndex = -1;
Response.Clear();
Response.ContentType = "application/vnd.xls";
Response.AddHeader("content-disposition", "attachment;filename=Countries.xls");
StringWriter swriter = new StringWriter();
HtmlTextWriter hwriter = new HtmlTextWriter(swriter);
HtmlForm frm = new HtmlForm();
this.grvCountry.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(this.grvCountry);
frm.RenderControl(hwriter);
Response.Write(swriter.ToString());
Response.End();
}

Thanks.

Create Custom Paging For Batter Performance

Hi.
In this article i Create sample page for creating Custom paging with Searching and Sorting Functionality.

i Will put My .ASPX ,.ASPX.CS and SQL Query i have Used to Complete this Functionality,

I Will Also Put Script to generate table I have Used for My Paging.and also Insert some data in it.

hope this will help us.

(1)SearchCountry.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SearchCountry.aspx.cs" Inherits="SearchCountry" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Search Country</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnFirst" Text="First" runat="server" OnClick="btnFirst_Click" />
<asp:Button ID="btnNext" Text="Next" runat="server" OnClick="btnNext_Click" />
<asp:Button ID="btnPrev" Text="Prev" runat="server" OnClick="btnPrev_Click" />
<asp:Button ID="btnLast" Text="Last" runat="server" OnClick="btnLast_Click" />
</div>
<div>
<b>
<asp:Label ID="lblCurrentPage" runat="server"></asp:Label>
</b>
<asp:GridView ID="grvCountry" AllowSorting="True" AutoGenerateColumns="False" runat="server"
OnSorting="grvCountry_Sorting" CellPadding="4" ForeColor="#333333" GridLines="None">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="Country" SortExpression="Country" HeaderText="Country" />
<asp:BoundField DataField="BarrelsPerDay" SortExpression="BarrelsPerDay" HeaderText="BarrelsPerDay" />
<asp:BoundField DataField="Continent" SortExpression="Continent" HeaderText="Continent" />
<asp:BoundField DataField="Region" SortExpression="Region" HeaderText="Region" />
</Columns>
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</div>
<div>
<table>
<tr>
<td>
Country:
</td>
<td>
<asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Continent:
</td>
<td>
<asp:TextBox ID="txtContinent" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Region:
</td>
<td>
<asp:TextBox ID="txtRegion" runat="server"></asp:TextBox>
</td>
</tr>
</table>
</div>
<div>
<asp:Button ID="btnSearch" Text="Search" runat="server" OnClick="btnSearch_Click" />
</div>
</form>
</body>
</html>

(2)SearchCountry.aspx.cs


using System;
using System.Data;
using System.Web.UI.WebControls;
using DataAccess;
public partial class SearchCountry : System.Web.UI.Page
{
const int PageSize = 100;
public int PageNumber
{
get
{
int p = 0;
if (ViewState["PageNumber"] == null)
{
p = 1;
}
else
{
p = Convert.ToInt32(ViewState["PageNumber"]);

}
return p;
}
set
{
ViewState["PageNumber"] = value;
}

}
private string country
{
get
{
return txtCountry.Text;
}

}
private string Continent
{
get
{
return txtContinent.Text;
}

}
private string Region
{
get
{
return txtRegion.Text;
}

}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindCountrues(1);
}
}
private void BindCountrues(int Page)
{
Country c = new Country();
DataTable tab = c.SearchCountry(Page, PageSize, country, Continent, Region, "");
grvCountry.DataSource = tab;
grvCountry.DataBind();
if (tab.Rows.Count > 0)
{
ViewState["TotalPages"] = tab.Rows[0]["TotalPages"];
}
else
{
ViewState["TotalPages"] = 1;
}
lblCurrentPage.Text = "Viewing Page:" + (PageNumber) + "(1 To " + ViewState["TotalPages"].ToString() + ")";
}
protected void btnNext_Click(object sender, EventArgs e)
{
int MaxPage = Convert.ToInt32(ViewState["TotalPages"]);
if (PageNumber < MaxPage)
{
PageNumber = PageNumber + 1;
}
BindCountrues(PageNumber);
}
protected void btnPrev_Click(object sender, EventArgs e)
{
if (PageNumber > 1)
{
PageNumber = PageNumber - 1;
}
BindCountrues(PageNumber);
}
protected void btnSearch_Click(object sender, EventArgs e)
{
BindCountrues(PageNumber);
}
protected void btnFirst_Click(object sender, EventArgs e)
{
PageNumber = 1;
BindCountrues(PageNumber);
}
protected void btnLast_Click(object sender, EventArgs e)
{
int LastPage = Convert.ToInt32(ViewState["TotalPages"]);
PageNumber = LastPage;
BindCountrues(PageNumber);
}
protected void grvCountry_Sorting(object sender, GridViewSortEventArgs e)
{
Country c = new Country();
if (ViewState["SortOrder"] == null)
{
ViewState["SortOrder"] = " Desc";
}
else if (ViewState["SortOrder"].ToString().Equals(" Desc"))
{
ViewState["SortOrder"] = " Asc";
}
else
{
ViewState["SortOrder"] = " Desc";
}
DataTable tab = c.SearchCountry(PageNumber, PageSize, country, Continent, Region, e.SortExpression + ViewState["SortOrder"].ToString());
grvCountry.DataSource = tab;
grvCountry.DataBind();
}
}


(3)Business Layer Method.

public DataTable SearchCountry(int PageNo, int PageSize, string country,string Continent, string Region, string OrderBy)
{
SqlCommand cmd = GetSqlCommand();
cmd.CommandText = "SP_SearchCountry";
cmd.Parameters.AddWithValue("@Page", PageNo);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.AddWithValue("@country", country);
cmd.Parameters.AddWithValue("@Continent", Continent);
cmd.Parameters.AddWithValue("@Region", Region);
cmd.Parameters.AddWithValue("@OrderBy", OrderBy);
return ExecuteDataTable(cmd);

}


(4)Stored Procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER Procedure [dbo].[SP_SearchCountry]
(
@Page int=0,
@PageSize int=0,
@country as varchar(20)='',
@Continent as varchar(20)='',
@Region as varchar(20)='',
@OrderBy as varchar(1000)=''
)
as
BEGIN
--Local variables
Declare @Query as nvarchar(500)
Declare @Params as nvarchar(200)
Declare @Where as varchar(200)
Declare @Order as varchar(100)
select @Where='',@Order='',@OrderBy= replace(@OrderBy,'''',''),@country= replace(@country,'''',''),@Continent= replace(@Continent,'''',''),@Region= replace(@Region,'''','')

--Prepare Where
IF(len(@country)<>0)
BEGIN
set @Where=@Where+' AND country like ''%'+@country+'%'''
END
IF(len(@Continent)<>0)
BEGIN
set @Where=@Where+' AND Continent like ''%'+@Continent+'%'''
END
IF(len(@Region)<>0)
BEGIN
set @Where=@Where+' AND Region like ''%'+@Region+'%'''
END
--Prepare Order By
IF(len(@OrderBy)>0)
BEGIN
set @Order=' Order by '+@OrderBy
END
set @Query=';With FirtTable as
(
Select ROW_NUMBER() over(order by OilProducer_ID) as RowNo, country,Barrelsperday,Continent,Region from OilProducers where 1=1 '+@Where+'
)
select country,Barrelsperday,Continent,Region,((select count(*) from FirtTable)/@PageSize) TotalPages from FirtTable where RowNO>(@Page*@PageSize) and RowNO<(@Page*@PageSize)+@PageSize+1'+@Order
set @Params='@PageSize int,@Page int'

exec sp_executesql @Query,@Params,@PageSize=@PageSize,@Page=@Page

END

(5)Table Script:

CREATE TABLE [dbo].[OilProducers](
[OilProducer_ID] [int] IDENTITY(1,1) NOT NULL,
[country] [varchar](30) NOT NULL,
[BarrelsPerDay] [int] NOT NULL,
[Continent] [varchar](80) NOT NULL,
[Region] [varchar](80) NOT NULL
) ON [PRIMARY]

(6)Sample data.

INSERT INTO [OilProducers]([country],[BarrelsPerDay],[Continent],[Region]) VALUES('India',5000,'Asia','Extern-Asia')
INSERT INTO [OilProducers]([country],[BarrelsPerDay],[Continent],[Region]) VALUES('Pakistan',4000,'Asia','Extern-Asia')
INSERT INTO [OilProducers]([country],[BarrelsPerDay],[Continent],[Region]) VALUES('Afghanistan',6000,'Asia','Extern-Asia')
INSERT INTO [OilProducers]([country],[BarrelsPerDay],[Continent],[Region]) VALUES('Austrelia',6000,'Austrelia','Austrelia')
INSERT INTO [OilProducers]([country],[BarrelsPerDay],[Continent],[Region]) VALUES('Peru',6000,'Africa','Esat-Africa')

Thanks..

Monday, July 13, 2009

Using supportedRunTime Attribute.

we can configure our application to run under some specific version of .net framework.

If we won't provide this Attrubute.Runtime Will Automatically decide using
some predefined rules.

Rule-1: If version in which application is Build is present Application
Will run with that Version.

Rule-2:If version in which application is Build is NOT present.Then
Runtime will search for supportedRunTime attrubute.

a).if Attribute is NOT
present it will run With latest version available on the machine.

Rule-3:If version in which application is Build is NOT present.And
supportedRunTime is present in configuration file.

a).application will run
with version specified in supportedRunTime attribute.



This is how to specity Supported Runtime version in Configuration File.
<configuration>
<startup>
<supportedRuntime version="v1.1.4322"/>
<supportedRuntime version="v1.0.3705"/>
</startup>
</configuration>

Steps to Creating Windows service with timers.

Steps to Creating Windows service with timers.
we can use Windows service as the way to run task that run Constantly and doe not need any user interface. Here are the Steps to create Windows Service.

1).Open Visual Studio

2).File->New Project->Windows Service

3).In these Step

a).add code to run at start of service on OnStart Method
b).add code to run at Stop of service on OnStop Method

To run any task constantly we have to provide timer.Which Executes that task on
some intarval.

Here is the code to Implement Windows Service with Timer.

Replace Code in Service1.cs With Code Below

using System;
using System.ServiceProcess;
using System.Threading;
using System.IO;
namespace WinService
{

public partial class Service1 : ServiceBase

{

Timer t;
FileStream fs;

public Service1()
{
InitializeComponent();
}
private void WriteDate(object state)
{
fs = new FileStream(@"C:\MyService.txt", FileMode.Append, FileAccess.Write);

byte[] b = System.Text.ASCIIEncoding.ASCII.GetBytes(DateTime.Now.ToString() +
Environment.NewLine);
fs.Write(b, 0, b.Length); fs.Close();

}
protected override void OnStart(string[] args)
{
TimerCallback tc = new TimerCallback(WriteDate);
object state = new object(); t = new Timer(tc, state, 0, 5000);

fs = new FileStream(@"C:\MyService.txt", FileMode.Create, FileAccess.Write);
fs.Close();
}

protected override void OnStop()
{
t.Change(Timeout.Infinite, 1000); fs = null;
}

}

}

4).In Service1[Design] Mode ->Properties Set ServiceName=MyService
5).In Service1[Design] Mode ->Right Click ->AddInstaller
This Will Add.

a).Service Installer.

b).ServiceProcessInstaller.

This both are User to Install service on system and Add that service in Service
Control Manager.

6).Right Click on ServiceInstaller->Properties then.

Set DisplayName=MyServiceTest
Description=This is a test
StartupType=Automatic

7).Right Click on ServiceProcessInstaller->Properties then.

Set Account=LocalSystem. Your Service
will run Under Previlege of these account.

8).Now Build Your Service and resolve any error. after completing build
Successfully.it is time to install the service.

there will be Myservice.exe file in bin
directory of your project.
9).Open VisualStudio Command Prompt (Start->VisualStudio 200->VisialStudio
Tools->Command Prompt).

10).Type Comand:InstallUtil.exe -i path to Myservice.exe


This Will Install Service on your System. this will also
add Service to service control manager.

11).Goto Service Control Manager.

12).Start the service.

13).Monitor C:\MyService.txt to check
whether times work or not?

This is all to Use Windows Service.

Thanks.

Saturday, July 4, 2009

Error While Executing PIVOT Operator.

hi.
Is sql when we Use Pivot Operation Some times It Fires Incorrect syntax near '('.
This Exception Is Due to law Compatibility level. to resolve this error Just set compatibility level to higher than current level.

we can get status of compatibility level using stored procedure sp_dbcmptlevel.
to get current level : sp_dbcmptlevel @dbname ='DatabaseName'
to set new level: sp_dbcmptlevel @dbname ='DatabaseName', @new_cmptlevel = 80

List all countries against their region separated with comma.

On my free time i found one table which list countries with their region like(Figure-1),they were using simple string base query to put this data as Shown in (Figure-2).

Figure-1(Data)

Figure-2(Result)


All in above query is to List all country against their region separated with comma.
this is possible in many ways. One i found by googling is on http://www.simple-talk.com/sql/t-sql-programming/sql-server-grouping-workbench/
And another i will present is written by me and my friend prashant meghvani find out another using new XML Datatype is.
This is the Test Data:
you can use to test this method.just copy and paste it in your Query editor.

DECLARE @t TABLE
(
id INT,
country VARCHAR(30),
barrels INT,
region VARCHAR(30)
)
INSERT INTO @t
(
id,
country,
barrels,
region
)
SELECT 1,
'Australia',
541000,
'Australia and New Zealand'
UNION
SELECT 2,
'New Zealand',
10000,
'Australia and New Zealand'
UNION
SELECT 3,
'Cuba',
73500,
'Caribbean'
UNION
SELECT 3,
'Trinidad and Tobago',
15240,
'Caribbean'
UNION
SELECT 4,
'Virgin Islands',
11480,
'Caribbean'
UNION
SELECT 5,
'Belize',
14000,
'Central America'
UNION
SELECT 6,
'Mexico',
541000,
'Central America'
UNION
SELECT 7,
'Madagascar',
756000,
'Eastern Africa'
UNION
SELECT 8,
'Mongolia',
541000,
'Eastern Africa'
UNION
SELECT 9,
'Hungary',
541000,
'Eastern Europe'
UNION
SELECT 10,
'Poland',
188300,
'Eastern Europe'
UNION
SELECT 11,
'Papua New Guinea',
541000,
'Melanesia'
UNION
SELECT 12,
'Congo',
32900,
'Middle Africa'
UNION
SELECT 13,
'Chad',
1126000,
'Middle Africa'
UNION
SELECT 14,
'Equatorial Guinea',
10000,
'Middle Africa'
UNION
SELECT 15,
'Pakistan',
63000,
'South-central Asia'
UNION
SELECT 16,
'India',
819000,
'South-central Asia'
UNION
SELECT 17,
'Iran',
4081000,
'South-central Asia' ;


Solution:

SELECT * FROM @t
WITH FirstTable ( region, Countries )
AS ( SELECT region,
( SELECT country + ISNULL(NULL, ',')
FROM @t child
WHERE parent.region = child.region
FOR
XML PATH('')
)
FROM @t parent
GROUP BY parent.region
)
SELECT Region,
CASE WHEN LEN(Countries) > 0
THEN LEFT(Countries, LEN(Countries) - 1)
ELSE ''
END Countries
FROM FirstTable


Hope this will helps.
Thanks.

Thursday, July 2, 2009

Solution for TSQL Challenge 3-Reverse Without reverse

Hi.
My idle days lead me to solve another puzzle from Jacob.Which is on http://beyondrelational.com/blogs/tc/archive/2009/05/24/tsql-challenge-3.aspx.
in this challange i need to reverse string without sql Reverse Function.to solve this i use CTE .the great feature provided by sql server.

GivenData:

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'


Solution:

;WITH FirstTable AS
(
SELECT ID,Position=LEN(data),data,CAST(SUBSTRING(data,LEN(data)+1,1) AS VARCHAR(200)) AS ReverseData FROM @t
UNION ALL
SELECT ID,Position=Position-1,data,CAST(ReverseData+SUBSTRING(data,Position,1) AS VARCHAR(200)) AS ReverseData FROM FirstTable
WHERE Position>0
)
SELECT * FROM FirstTable WHERE LEN(ReverseData)=LEN(data) ORDER BY id desc


Hope this will help you.

Thanks.

RC SQL Challange Solution.- Part 2

hi.
you can view challange on http://weblogs.asp.net/ruicarvalho/archive/2009/03/28/rc-sql-challenge-01.aspx This is the another simple solution of this challange. In this solution we have to perform two Cross join and after that solution is very easy.I think this is good one regarding performance.

Solution:

WITH Pricetable
AS ( SELECT FT.ID,
FT.Product,
C.[NAME] FirstCoupen,
FT.SecondName,
FT.PRICE,
Discount_Pirce = ( CASE c.IS_PERCENT
WHEN 0
THEN FT.Discount_Pirce
- c.[VALUE]
ELSE FT.Discount_Pirce
- ( FT.Discount_Pirce
* c.[VALUE] ) / 100
END )
FROM ( SELECT T.ID,
T.[NAME] AS Product,
C.[NAME] AS SecondName,
C.ID AS Coupen1_ID,
T.PRICE,
Discount_Pirce = ( CASE c.IS_PERCENT
WHEN 0
THEN T.Price - c.[VALUE]
ELSE T.PRICE - ( T.PRICE * c.[VALUE] ) / 100
END )
FROM @T t
CROSS Apply @C c
) AS FT
CROSS Apply @C c
WHERE C.ID <> FT.Coupen1_ID
)
SELECT *
FROM ( SELECT *,
ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY Discount_Pirce ) AS RANK
FROM Pricetable
) AS FinalTable
WHERE Rank = 1


Thanks.

Tuesday, June 30, 2009

RC SQL Challange Solution.

hi.
i Found one more challange on a http://weblogs.asp.net/ruicarvalho/archive/2009/03/28/rc-sql-challenge-01.aspx.this challange was about Callulationg Minimum price after applying any Two of four coupens.we can solve this using CTE.

Problem:

(1) Product table(Given).
(2) Coupen Table(Give).
(3)Find Minimum Discount table by applying any two coupen.

Given Data:

DECLARE @T TABLE
(
ID INT IDENTITY,
NAME NVARCHAR(20),
PRICE MONEY
)
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 1', 100 )
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 2', 220 )
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 3', 70 )
DECLARE @C TABLE
(
ID INT IDENTITY,
NAME NVARCHAR(20),
VALUE INT,
IS_PERCENT BIT
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 1 : -15$',
15,
0
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES ( 'COUPON 2 : -5$', 5, 0 )
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 3 : -10%',
10,
1
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 4 : -12$',
12,
0
) ;


Solution:

DECLARE @T TABLE
(
ID INT IDENTITY,
NAME NVARCHAR(20),
PRICE MONEY
)
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 1', 100 )
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 2', 220 )
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 3', 70 )
DECLARE @C TABLE
(
ID INT IDENTITY,
NAME NVARCHAR(20),
VALUE INT,
IS_PERCENT BIT
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 1 : -15$',
15,
0
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES ( 'COUPON 2 : -5$', 5, 0 )
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 3 : -10%',
10,
1
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 4 : -12$',
12,
0
) ;
WITH FirstTable
AS ( SELECT ID,
[Name],
Price,
Discount_price = PRICE,
Cupen_count = 0,
0 Cupen_Id,
CAST('' AS VARCHAR(100)) Cupen_Name
FROM @T
UNION ALL
SELECT FT.ID,
FT.[Name],
FT.Price,
Discount_price = ( CASE c.IS_PERCENT
WHEN 1
THEN ( 100 - C.value )
* ( Discount_price / 100 )
ELSE Discount_price - C.[VALUE]
END ),
Cupen_count = Cupen_count + 1,
Cupen_Id = C.id,
Cupen_Name = CAST(Cupen_Name
+ CAST(c.ID AS VARCHAR(2)) + ' & ' AS VARCHAR(100))
FROM FirstTable FT,
@C c
WHERE Cupen_Id <> c.id AND Cupen_count<2)SELECT ID,[Name],Price,Discount_price,Cupen_NameFROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY Discount_price asc ) AS RN,*FROM FirstTable) AS T WHERE T.RN=1

Thanks.

Solution for T-SQL challange #10

Hi.
this was the very interesting challange.i enjoy most to solve it.i can't solve it easily and have to get help from friend. This is the solution for T-SQL challange #10.To view challange click here.
on this site jacob post good challnge regarding SQL server problems.you can find more challanges on link provided.
Problem:
(1) perform horizontal sorting on sql table.
(2)Using simple query.
Given Data.

DECLARE @t TABLE
(
c1 CHAR(1),
c2 CHAR(1),
c3 CHAR(1)
)
insert into @t ( c1, c2, c3 )
values ( '2', '1', '3' )
insert into @t ( c1, c2, c3 )
values ( '3', '2', '1' )
insert into @t ( c1, c2, c3 )
values ( 'Z', 'X', 'Y' )
insert into @t ( c1, c2, c3 )
values ( 'B', 'C', 'D' )
insert into @t ( c1, c2, c3 )
values ( 'Y', 'Z', 'X' )
insert into @t ( c1, c2, c3 )
values ( 'B', 'C', 'A' )
SELECT *
FROM @t ;


Solution :


WITH FirstTable
AS ( SELECT *
FROM ( SELECT c1,
c2,
c3,
ROW_NUMBER() OVER ( ORDER BY ( SELECT 0
) ) as 'RN'
FROM @t
) AS t
CROSS JOIN ( SELECT 1 AS part
UNION ALL
SELECT 2
UNION ALL
SELECT 3
) AS n
--Cross join to convert each row value in Diagonal column for further Use
) ,
SecondTable
AS ( SELECT RN,
c1,
c2,
c3,
CASE part
WHEN 1 THEN c1
WHEN 2 THEN c2
WHEN 3 THEN c3
END AS [Values]
FROM FirstTable
--Converts Each Row value as Single Column
) ,
ThirdTable
AS ( SELECT RN AS PK,
c1,
c2,
c3,
[VALUES],
ROW_NUMBER() OVER ( PARTITION BY RN ORDER BY [Values] ) as 'RN'
FROM SecondTable
--perform actual order by operation and set approprite Column number by Row_num function
)
SELECT DISTINCT
[1] AS c1,
[2] AS c2,
[3] AS c3
FROM ThirdTable PIVOT ( MIN([Values]) FOR RN IN ( [1], [2], [3] ) ) AS pv
ORDER BY c1,
c2,
c3 ;


Thanks.

Friday, June 26, 2009

Solution for "TSQL Challenge 8" -Recursive CTE.

HI.
this is my first successful affort to solve T-SQL challange.This challange was published on
http://beyondrelational.com/ .this website is authored by wel known MVP Jacob..
you can vision mpre challanges on this site. this is the solution of Challenge-8 .
Problem:
Retrive data as Hierarchy
Given Data:

DECLARE @Employees TABLE
(
EmpID INT,
EmpName VARCHAR(20),
ReportsTo INT
)
INSERT INTO @Employees
(
EmpID,
EmpName,
ReportsTo
)
SELECT 1,
'Jacob',
NULL
UNION ALL
SELECT 2,
'Rui',
NULL
UNION ALL
SELECT 3,
'Jacobson',
NULL
UNION ALL
SELECT 4,
'Jess',
1
UNION ALL
SELECT 5,
'Steve',
1
UNION ALL
SELECT 6,
'Bob',
1
UNION ALL
SELECT 7,
'Smith',
2
UNION ALL
SELECT 8,
'Bobbey',
2
UNION ALL
SELECT 9,
'Steffi',
3
UNION ALL
SELECT 10,
'Bracha',
3
UNION ALL
SELECT 11,
'John',
5
UNION ALL
SELECT 12,
'Michael',
6
UNION ALL
SELECT 13,
'Paul',
6
UNION ALL
SELECT 14,
'Lana',
7
UNION ALL
SELECT 15,
'Johnson',
7
UNION ALL
SELECT 16,
'Mic',
8
UNION ALL
SELECT 17,
'Stev',
8
UNION ALL
SELECT 18,
'Paulson',
9
UNION ALL
SELECT 19,
'Jessica',
10
DECLARE @manager VARCHAR(20) ;
SELECT @manager = 'smith' ;


Solution:


WITH Managers
as (
--initialization
SELECT EmpID,
EmpName,
CAST(EmpName + '' AS VARCHAR(200)) Hierarchy,
ReportsTo,
1 AS Position,
EmpName AS MainManager,
CAST(EmpID AS VARBINARY(500)) AS sort
FROM @Employees
UNION ALL
SELECT emp.EmpID,
emp.EmpName,
CAST(SPACE(4 * Position) + emp.EmpName AS VARCHAR(200)) Hierarchy,
emp.ReportsTo,
Position + 1,
MainManager,
CAST(sort + CAST(emp.EmpID AS BINARY(4)) AS VARBINARY(500)) AS sort
from @Employees emp
INNER JOIN Managers ON emp.ReportsTo = Managers.EmpID
)
SELECT Hierarchy
FROM Managers
WHERE MainManager = @manager
ORDER BY sort



Thanks.
Haresh Dhameliya

Monday, May 25, 2009

How to enable or disable LinkButton in Javascript.

we can make asp.net Linkbutton Enable\Disable in Javascript. normally disabled='disabled' attribute works only in internet explorer not in Mozilla. to resolve this problem we can develope a javascript function Which perform this.

(1) Suppose we have a LinkButton

<asp:LinkButton ID="lnkTest" runat="server" CommandArgument="1" CommandName="1x"
OnClick="lnkTest_Click">Test</asp:LinkButton>


(2)Style for enabling and Disabling is

<style>
.LnkEnabled
{
cursor: pointer;
}
.LnkDisabled
{
cursor: default;
color: Gray;
}
</style>


(3) and javascript fiuction is

<script language="javascript">
function EnableLinkButton(ID,flag)
{
document.getElementById(ID).onclick=function(){return flag;};
if(!flag)
{
document.getElementById(ID).setAttribute("disabled","disabled");
document.getElementById(ID).className="LnkDisabled";
}
else
{
document.getElementById(ID).setAttribute("disabled","");
document.getElementById(ID).className="LnkEnabled";
}
}
EnableLinkButton('<%= lnkTest.ClientID %>',false);
</script>


Calling above javascript function we can Enable or disable LinkButton from Javascript.

Thanks.

Friday, May 22, 2009

Scrolling div at fixed position on scrolling of window.

we can scroll div element and keep its location fixed by calculating its new left and top properties
and moving it to that position..
we have to find out new value for top style of that div element.
here is the script for moving div element to the new position..

<script type="text/javascript" language="javascript">
FloatElement();
window.onscroll=FloatElement;
window.onresize=FloatElement;
function FloatElement()
{
var myfloaingTop=500;
if(document.all)
{
document.getElementById("myfloaing").style.top=document.documentElement.scrollTop+myfloaingTop;
}
else
{
document.getElementById("myfloaing").style.top=window.pageYOffset+myfloaingTop+'px';
}
}
</script&ght;


here is the div element.

<div id="myfloaing" style="position: absolute; width: 200px; height: 50px; left: 500px;
top: 0px; padding: 16px; background: #FFFFFA; border: 2px solid #2266AA">

My Floating div.
</div>


thanks.

Tuesday, May 19, 2009

Using extenstion method with user define class.

we can convert fields and properties of any class as xml data using extension methods in c# 3.5.
to convert fields and properties as xml data we can use custom logic or we can use XmlSerializer to convert whole object as xml file.
here is the implementation of Toxml and SerilizeToXml method on user defined class Automobile.

Put this class and extension methods calss in App_Code folder as (Automobile.cs)

using System;
using System.Linq;
using System.Xml.Linq;
using System.Reflection;
using System.Xml.Serialization;
using System.IO;
///
/// Summary description for Automibile
///

public class Automobile
{
#region Fields
public string Name;
public DateTime LaunchDate;
public int Average;
public string[] ments;
#endregion
#region Properties
public int GetAverage
{
get
{
return Average;
}
set
{
Average = value;
}
}
#endregion
}
public static class AutomobileExtensions
{
#region Automobile Extension methods
public static string ToXml(this Automobile entity)
{
Type t = entity.GetType();
PropertyInfo[] pInfo = t.GetProperties();
FieldInfo[] fInfo = t.GetFields();
var fs = from f in fInfo
select new { Name = f.Name, value = f.GetValue(entity) };
var ps = from p in pInfo
select new { Name = p.Name, value = p.GetValue(entity, null) };
var str = new XElement(t.Name, from n in fs.Union(ps)
select new XElement(n.Name, n.value));
return str.ToString();
}
public static string SerilizeToXml(this Automobile entity)
{
XmlSerializer xmlSerilizer = new XmlSerializer(entity.GetType());
StringWriter strWriter = new StringWriter();
xmlSerilizer.Serialize(strWriter, entity);
return strWriter.ToString();
}
#endregion
}



Calling extension methods.


protected void Page_Load(object sender, EventArgs e)
{
Automobile Honda = new Automobile();
Honda.Name = "Delux";
Honda.LaunchDate = DateTime.Now;
Honda.Average = 60;
Honda.ments = new string[] { "H", "A" };
Response.Write("<br/>");
Response.Write("<b>ToXml</b>");
Response.Write("<br/>");
Response.Write("<br/>");
Response.Write(Server.HtmlEncode(Honda.ToXml()));
Response.Write("<br/>");
Response.Write("<br/>");
Response.Write("<b>SerilizeToXml</b>");
Response.Write("<br/>");
Response.Write("<br/>");
Response.Write(Server.HtmlEncode(Honda.SerilizeToXml()));
}


Thanks.