what do u mean by storedprocedure? [Resolved]

Posted by Jprathap under Sql Server on 8/6/2013 | Points: 10 | Views : 1072 | Status : [Member] | Replies : 5
How to use storedprocedure in sql ?
using login and registration,(crud) function.
clear my doubts.

Advance thanks




Responses

Posted by: Satyapriyanayak on: 8/7/2013 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Login Page Using Stored Procedure 


Stored Procedure

CREATE procedure login_pro
(
@UserName varchar(50),
@Password varchar(50)
)
as
declare @status int
if exists(select * from Login where UserName=@UserName and Password=@Password)
set @status=1
else
set @status=0
select @status


Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Login_Page_Using_Stored_Procedure._Default" %>

<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:Label ID="Label1" runat="server" Text="Name" Font-Bold="True"
Width="100px" BackColor="#FFFF66" ForeColor="#FF3300"></asp:Label>
<asp:TextBox ID="TextBox_user_name" runat="server" ForeColor="#993300" Width="100px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="TextBox_user_name" ErrorMessage="Please enter username"></asp:RequiredFieldValidator>
<br />
<asp:Label ID="Label2" runat="server" Text="Password" Font-Bold="True"
Width="100px" BackColor="#FFFF66" ForeColor="#FF3300"></asp:Label>
<asp:TextBox ID="TextBox_password" runat="server" ForeColor="#CC6600"
TextMode="Password" Width="100px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="TextBox_password" ErrorMessage="Please enter password"></asp:RequiredFieldValidator>
<br />
<asp:Button ID="btn_login" runat="server" Text="Login" Font-Bold="True"
BackColor="#CCFF99" onclick="btn_login_Click" /><br />
<asp:Label ID="lblmessage" runat="server" Font-Bold="True" ForeColor="#FF3300"></asp:Label><br />
</div>
</form>
</body>
</html>

Default.aspx.cs

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace Login_Page_Using_Stored_Procedure
{
public partial class _Default : System.Web.UI.Page
{
string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand com;
SqlParameter UserName, Password;

protected void btn_login_Click(object sender, EventArgs e)
{
UserName = new SqlParameter();
Password = new SqlParameter();
SqlConnection con = new SqlConnection(strConnString);

com=new SqlCommand();
com.Connection = con;
con.Open();
Session["UserName"] = TextBox_user_name.Text;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "login_pro";

UserName.SqlDbType = SqlDbType.VarChar;
UserName.Size = 50;
UserName.ParameterName = "@UserName";
UserName.Value = TextBox_user_name.Text.ToString();
UserName.Direction = ParameterDirection.Input;

Password.SqlDbType = SqlDbType.VarChar;
Password.Size = 50;
Password.ParameterName = "@Password";
Password.Value = TextBox_password.Text.ToString();
Password.Direction = ParameterDirection.Input;

com.Parameters.Add(UserName);
com.Parameters.Add(Password);

int status;
status = Convert.ToInt16(com.ExecuteScalar());

if (status == 1)
{
Response.Redirect("Welcome.aspx");
}
else
{
lblmessage.Text = "Invalid UserName and Password...";
}
con.Close();
}
}
}


Welcome.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Welcome.aspx.cs" Inherits="Login_Page_Using_Stored_Procedure.Welcome" %>

<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lb1" runat="server" Font-Bold="True" ForeColor="#FF3300"></asp:Label><br />
</div>
</form>
</body>
</html>

Welcome.aspx.cs


using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

namespace Login_Page_Using_Stored_Procedure
{
public partial class Welcome : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
lb1.Text = "WELLCOME :: " + Session["UserName"];
}
}
}


If this post helps you mark it as answer
Thanks

Jprathap, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 8/6/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Stored procedures:- Its nothing but a set of T-SQL statements combined to
Perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored Procedure, you actually run a set of statements. Stored Procedure are the precompiled set of sql command. Stored procedures means containing a precompiled block of code. if we call stored procedures they need not compiled, only execution takes place. With this advantage, work on database is less.


If this post helps you mark it as answer
Thanks

Jprathap, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/7/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Satyapriyanayal has posted "Login Page Using Stored Procedure" clearly...
I just want to tell you the CRUD operations using stored procedures.. I will give you sample stored procedures for CRUD operations
Lets say we have one table called Student with columns StudentID, StudentName, Marks
--C-Create and U-Update:
CREATE PROCEDURE CreateUSP

(
@P_StudentID int,
@P_StudentName VARCHAR(30),
@P_Marks int
)
AS
BEGIN
IF EXISTS ( SELECT StudentID FROM Student WHERE StudentID=@P_StudentID)
BEGIN
UPDATE Student SET StudentName = COALESCE(@P_StudentName, StudentName), Marks = @P_Marks
WHERE StudentID = @P_StudentID;
END
ELSE
BEGIN
INSERT INTO Student VALUES(@P_StudentID, @P_StudentName, @P_Marks)
END
END
GO


-- D= Delete
CREATE PROCEDURE DeleteUSP

(
@P_StudentID int
)
AS
BEGIN
DELETE FROM Student WHERE StudentID = @P_StudentID;
END
GO


-- R = Read/ Retrieve
CREATE PROCEDURE SelectUSP

(
@P_StudentID int
)
AS
BEGIN
SELECT * FROM Student WHERE StudentID = @P_StudentID;
END
GO


Please look into the btn_login_Click() button click event code posted by "Satyapriyanayak" to understand the usage of stored procedure in C#...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jprathap, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jprathap on: 8/7/2013 [Member] Starter | Points: 25

Up
0
Down
thanks a lot , its very easy to understand ..........

Jprathap, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/7/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Can you please mark it as answer....
and you posted another thread for searching for a text in stored procedure..
can you elaborate your need?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jprathap, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response