sql server stored procedure used in the asp.net [Resolved]

Posted by Rajesh081725 under ASP.NET on 10/27/2012 | Points: 10 | Views : 5280 | Status : [Member] | Replies : 5
can any one tell me

How to sql server stored procedure used in the asp.net given some for sql server and also asp.net using some controls

Time is Gold
Thanks & Regards,
Rajesh Kumar,
9962038582.



Responses

Posted by: Rickeybglr on: 10/29/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi this is simple example of registration prcess using store proc.
<asp:TextBox ID="txtFName" runat="server" CssClass="form-text required">
</asp:TextBox> </br>
<asp:TextBox ID="txtEmail" runat="server" CssClass="form-text required"></asp:TextBox></br>

<asp:Button ID="btnSubmitRegist" runat="server" Text="submit" OnClick="btnSubmitRegist_Click"/>
--
.aspx code
protected void btnSubmitRegist_Click(object sender, EventArgs e)
{
registerNewUser();
}
public void registerNewUser()
{
objcUserInfo.Firstname = txtFName.Text;
objcUserInfo.Email = txtEmail.Text;
}
-----
public class cUserInfo
{
private string firstname;

public string Firstname
{
get { return firstname; }
set { firstname = value; }
}
private string email;

public string Email
{
get { return email; }
set { email = value; }
}
}

---dal layer

SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);

public void newRegistration(cUserInfo userInfo)
{
try
{
con.Open();
SqlCommand cmdInsert = new SqlCommand("USP_NewUserRegistration", con);
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.AddWithValue("@strFirstName", userInfo.Firstname);
cmdInsert.Parameters.AddWithValue("@strEmail", userInfo.Email);
int i = cmdInsert.ExecuteNonQuery();
}

store proc:

CREATE PROCEDURE [dbo].[USP_NewUserRegistration]
(
@strFirstName nvarchar(50),
@strEmail nvarchar(50)
}

AS
BEGIN
DECLARE @UID INT=0
IF @strusername NOT IN (SELECT UserName from userRegistrationTB)
BEGIN
BEGIN TRANSACTION

INSERT INTO userRegistrationTB
(
firstname,
useremail
}
VALUES
{
@strFirstName,
@strEmail
}
COMMIT
END
ELSE
BEGIN
SELECT 'user already exists pleae choose different username'
END

hope u can understnd ths...just call dal layer from ur aspx by creating class object . i have used only 2 field here for ur ease of understanding.




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

Posted by: Jayakumars on: 10/28/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

rajesh refer this

http://www.c-sharpcorner.com/UploadFile/gtomar/storedprocedure12052007003126AM/storedprocedure.aspx

Mark as Answer if its helpful to you

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

Posted by: Rajesh081725 on: 10/29/2012 [Member] Bronze | Points: 25

Up
0
Down
Hai Ricky,
This User Registration TB Procedure using If conditon
I Have one doubt here where u declared the name on UID and strusername
Can u explain to me

Time is Gold
Thanks & Regards,
Rajesh Kumar,
9962038582.

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

Posted by: Rickeybglr on: 10/29/2012 [Member] Starter | Points: 25

Up
0
Down
ignore that part i have other fields also around 15. for ur simplecity i have use here only two field (first name and email )
but for ur knowd. i tell u tht username is unique in my table
so in this line
IF @strusername NOT IN (SELECT UserName from userRegistrationTB)
its actualy checkn for presence of username if exists it willnot goin to insert data
same as uid is anothr variable used.
for as of now just ignore tht.. i forgot to remove tht line .

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

Posted by: Satyapriyanayak on: 1/24/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Stored procedure to insert records

CREATE PROCEDURE insert1
(@sid varchar(50),@sname varchar(50),@smarks int,@saddress varchar (50),@year varchar(50))
AS
insert student(sid,sname,smarks,saddress,year) values (@sid,@sname,@smarks,@saddress,@year)




<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Stored_procedure_to_insert._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="Id" Width="100px"></asp:Label>
<asp:TextBox ID="txtid" runat="server"></asp:TextBox><br />

<asp:Label ID="Label2" runat="server" Text="Name" Width="100px"></asp:Label>
<asp:TextBox ID="txtname" runat="server"></asp:TextBox><br />

<asp:Label ID="Label3" runat="server" Text="Marks" Width="100px"></asp:Label>
<asp:TextBox ID="txtmarks" runat="server"></asp:TextBox><br />


<asp:Label ID="Label4" runat="server" Text="Address" Width="100px"></asp:Label>
<asp:TextBox ID="txtaddress" runat="server"></asp:TextBox><br />


<asp:Label ID="Label5" runat="server" Text="Year" Width="100px"></asp:Label>
<asp:TextBox ID="txtyear" runat="server"></asp:TextBox><br />

<asp:Button ID="btn_insert" runat="server" Text="Insert" onclick="btn_insert_Click"
/>
<asp:Label ID="lblmsg" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>



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 Stored_procedure_to_insert
{
public partial class _Default : System.Web.UI.Page
{
string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand com;
SqlDataAdapter sqlda;
DataSet ds;



protected void btn_insert_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(strConnString);
con.Open();
com = new SqlCommand("insert1", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@sid", txtid.Text);
com.Parameters.AddWithValue("@sname", txtname.Text);
com.Parameters.AddWithValue("@smarks", int.Parse(txtmarks.Text));
com.Parameters.AddWithValue("@saddress", txtaddress.Text);
com.Parameters.AddWithValue("@year", txtyear.Text);
com.ExecuteNonQuery();
con.Close();
lblmsg.Text = "Data entered successfully!!!";
}




}
}



Thanks

If this post helps you mark it as answer
Thanks

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

Login to post response