Monday, September 21, 2009

Stored Procedure with isnull function in SQL Server 2005

Create Procedure [dbo].[GetSupply]
as
select
i.ISBN as ISBN,
i.Title as Title,
i.Edition as Edition,
AvailableTitles=isnull(
(
select count(ISBN)
from Purchase
where ISBN=i.ISBN and Email=i.Email group by ISBN
),0 ),
FirstPurchDate= isnull
(
(
select cast(min(Purchase_date) as nvarchar(50))
from Purchase
where ISBN=i.ISBN group by ISBN
),
'Not Purchase Yet'
)
from INFO as i
where i.ISBN is not NULL
and i.ISBN <>''
and i.contacted ='W'
--and i.Title<>''
group by i.ISBN,i.Title,i.Edition,i.Email
order by i.ISBN

The AvailableTitles will show 0 and FirstPurchDate will show 'Not Purchase Yet' if query returns null for the both.

Thursday, September 17, 2009

Lots of Code....

Lots of Code.... now need some fun !
So what can we do...?
Ok listen some bengali songs....
Hmm ! Just go for it...


Link : http://valobasi-bangla.blogspot.com/2009/03/download-songs-from-movie-aalo-download.html

Wednesday, September 16, 2009

Monday, September 14, 2009

How to Create and retrieve data from cookies in ASP.Net C#

This is the example for remember a user in a system when he login for a particular site
--------------------------------------------------------------------
User objUser = new User();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//Retrieve data from cookie
//----------------------------------------
HttpCookie cookie = Request.Cookies["User"];
if(cookie!=null)
{
chkCookies.Checked = true;
objUser.User ID = cookie["ID"];
objUser.Password = cookie["Password"];
AdminLogin(objUser);
}
}
}


private void UserLogin(User objUsr)
{
Collection userDetails = BLCustomer.UserLogin(objUsr);
if (userDetails .Count > 0)
{
Session["User"] = objUsr.UserID;
if (chkCookies.Checked == true)
{
//Here cookie is create............................

HttpCookie ck = Request.Cookies["User"];
if (ck == null)
{
ck = new HttpCookie("User");
}
ck["ID"] = objUsr.UserID;
ck["Password"] = objUsr.Password;
ck.Expires = DateTime.Now.AddMonths(1);
Response.Cookies.Add(ck);
}
Response.Redirect("abc.aspx");
}
else
{
lblMsg.Text = "Login fail!";
}
}


Thursday, September 10, 2009

Stored Procedure for Import a .csv file with filename and table name as perameter

Many times there is a problem to pass a table name as a store procedure perameter, so this
procedure could help,

Store Procedure:
........................................
Create PROCEDURE [dbo].[sp_ImportCsvFile]
(
@tableName nvarchar(200),
@from nvarchar(300) -- .csv file path
)
AS
DECLARE @SQLString nvarchar(1000)
DECLARE @S2 nvarchar(1000)
SET @SQLString = 'BULK INSERT ' + @tableName + ' FROM '''+ @from
Select @S2 = CAST(@SQLString as nvarchar(1000))
EXECUTE (@S2 + ''' WITH(FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')')

..................................................................................................
Execute the procedure:
..........................................
exec sp_ImportCsvFile 'tbl_Forex','D:\20090908_forex.csv'