Store Procedure Code In C#....
In ASPX.CS
protected void btn_login_Click(object sender, EventArgs e)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataReader dr = null;
try
{
//////////////make connection///////////////
cn = new SqlConnection(@"Data Source=UDAYKOTHARI-PC\SQLEXPRESS;Initial Catalog=DB_LOGIN;Integrated Security=True");
//////////get connection string///////////
/////////open cn/////////
cn.Open();
/////login logic///////////
cmd = new SqlCommand("DB_LOGIN",cn);
cmd.CommandType = CommandType.StoredProcedure;
//"SELECT COUNT (*) AS [RF] FROM dbo.tbl_login WHERE @username=username AND @password=password";
cmd.Parameters.AddWithValue("@username",txt_username.Text);
cmd.Parameters.AddWithValue("@password", txt_password.Text);
cmd.Parameters.Add("@cn", SqlDbType.Int);//("@password", SqlDbType.VarChar, 50);
cmd.Parameters["@cn"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
//cmd.Connection = cn;
//////////execute query////////////
//dr = cmd.ExecuteReader();
//if (dr.Read())
//{
if (Convert.ToInt32( cmd.Parameters["@cn"].Value)>0)
{
Response.Redirect("Forgetpassword.aspx");
}
else
{
Response.Write("INVALID LOGIN");
}
}
catch (Exception adoError)
{
Response.Write(adoError.ToString());
}
finally
{
//dr.Close();
cmd.Dispose();
cn.Close();
}
}
Logic In Store Procedure
USE [DB_LOGIN]
GO
/****** Object: StoredProcedure [dbo].[DB_LOGIN] Script Date: 09/01/2014 11:58:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[DB_LOGIN]
-- Add the parameters for the stored procedure here
(@username varchar(50),@password varchar(50),@cn int output)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @cn=COUNT (*)
FROM dbo.tbl_login
WHERE username=@username AND password=@password
if(@cn=1)
begin
return 1;
end
else
begin
return 0;
end
--return @cn;
END
In ASPX.CS
protected void btn_login_Click(object sender, EventArgs e)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataReader dr = null;
try
{
//////////////make connection///////////////
cn = new SqlConnection(@"Data Source=UDAYKOTHARI-PC\SQLEXPRESS;Initial Catalog=DB_LOGIN;Integrated Security=True");
//////////get connection string///////////
/////////open cn/////////
cn.Open();
/////login logic///////////
cmd = new SqlCommand("DB_LOGIN",cn);
cmd.CommandType = CommandType.StoredProcedure;
//"SELECT COUNT (*) AS [RF] FROM dbo.tbl_login WHERE @username=username AND @password=password";
cmd.Parameters.AddWithValue("@username",txt_username.Text);
cmd.Parameters.AddWithValue("@password", txt_password.Text);
cmd.Parameters.Add("@cn", SqlDbType.Int);//("@password", SqlDbType.VarChar, 50);
cmd.Parameters["@cn"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
//cmd.Connection = cn;
//////////execute query////////////
//dr = cmd.ExecuteReader();
//if (dr.Read())
//{
if (Convert.ToInt32( cmd.Parameters["@cn"].Value)>0)
{
Response.Redirect("Forgetpassword.aspx");
}
else
{
Response.Write("INVALID LOGIN");
}
}
catch (Exception adoError)
{
Response.Write(adoError.ToString());
}
finally
{
//dr.Close();
cmd.Dispose();
cn.Close();
}
}
Logic In Store Procedure
USE [DB_LOGIN]
GO
/****** Object: StoredProcedure [dbo].[DB_LOGIN] Script Date: 09/01/2014 11:58:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[DB_LOGIN]
-- Add the parameters for the stored procedure here
(@username varchar(50),@password varchar(50),@cn int output)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @cn=COUNT (*)
FROM dbo.tbl_login
WHERE username=@username AND password=@password
if(@cn=1)
begin
return 1;
end
else
begin
return 0;
end
--return @cn;
END
