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.