.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> ASP.NET >> Post New Resource Bookmark and Share   

 Subscribe to Articles

My first asp application with stored procedures

Posted By:Gowthammanju       Posted Date: June 29, 2011    Points: 200    Category: ASP.NET    URL: http://www.dotnetspark.com  

These article explains you about adding of datas in to database using stored procedures.
 

Hi,

What is stored procedures?


A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, StoredProc, or SP) are actually stored in the database data dictionary.

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires the execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures, by executing one stored procedure from within another.



Benefits of using stored procedures:



The various benefits are

Reduced network usage between clients and servers

Enhanced hardware and software capabilities

Improved security

Reduced development cost and increased reliability

Centralized security, administration, and maintenance for common routines

Syntax for creating procedure as

CREATE PROCEDURE procedure_name 
	@param data_type = default_value, 
	@param data_type = default_value, 
	@param data_type = default_value 
	AS 
	-- statements for procedure here
 Dropping Stored Procedures

DROP PROCEDURE stored_procedure_name

Just follow this steps u will find the correct way

open the sql server 2005 or 2008

click the new query tab

copy and paste below coding for creation of table as

use ssg
	go 
	create table register(regid int identity(1,1),firstname varchar(100),lastname varchar(50),emailid varchar(50),mobileno varchar(20),qualification varchar(30),username varchar(10),pwd varchar(10),usertype varchar(10),regdte datetime,primary key (regid)) 
	go 
	create table logininf(logid int identity(1,1),memregid int references register(regid),lusername varchar(100),lpwd varchar(50),lusertype varchar(50),primary key(lusername) ) 
	go 
	create table autoregno(aid int identity(1,1),anumber varchar(20),amemid int references register(regid),primary key(anumber)) 	go 
Now for creating procedures copy the below codings as


02 //creating procedure with name

create proc proc_register

//declaring procedure parameters as datatype

@frstnme varchar(100),@lstnme varchar(50),@emailid varchar(50),@mobileno varchar(10),@qualify varchar(30),@usernme varchar(10),@pwd varchar(10),@usertype varchar(10),@dte datetime,@gender varchar(8),@autogenid varchar(20) 03
as 04 begin 05
declare @error int,@memberid int //declaring variables inside procedures 06
begin tran 07
//insert/update statements

insert into register values
(@frstnme,@lstnme,@emailid,@mobileno,@qualify,@usernme,@pwd,@usertype,@dte,@gender) 08
//for retriving particular values

set @memberid=(select regid from register where username=@usernme) 09

insert into logininf values(@memberid,@usernme,@pwd,@usertype) 10 insert into autoregno values(@autogenid,@memberid,GETDATE())/ 11 12
//stements for printing errors

set @error=@@ERROR 13 if @error<>0 14 begin 15 goto errors 16 end 17
//verifies whether transaction is suces or not

commit tran 18
//passing errors values

errors: 19
if @error<>0 20 begin 21 rollback tran 22 end 23 return @error 24 end 25 go
now in design page of aspx inside the form tag use coding as


div style="height: 486px"> 
002	      
003	         
004	             
005	                 
007	             
008	             
009	                 
011	                 
013	                 
017	             
018	             
019	                 
023	                 
025	                 
029	             
030	             
031	                 
033	                 
035	                 
038	             
039	             
040	                 
042	                 
044	                 
047	             
048	             
049	                 
051	                 
053	                 
058	             
059	             
060	                 
062	                 
064	                 
067	             
068	             
069	                 
071	                 
073	                 
076	             
077	             
078	                 
080	                 
082	                 
085	             
086	             
087	                 
089	                 
091	                 
097	             
098	             
099	                 
101	                 
104	             
105	             
106	                 
108	             
109	             
110	                 
112	                 
115	                 
119	             
120	             
121	                 
123	                 
125	                 
127	             
128	        
006 A SAMPLE REGISTRATION PAGE
010 012 username 014 015 016
020 021 022 024 choose list number 026 027 028
032 034 firstname 036 037
041 043 last name 045 046
050 052 gender 054 055 057
061 063 email id 065 066
070 072 mobile number 074 075
079 081 qualification 083 084
088 090 usertype 092 093 Admin 094 Member 095 096
100 102 103
107
111 113 114 116 118
122 124 126
129 130

next step is to call the procedures in to ur coding page as

try
02	        { 
03	           //calling of procedure name which was created above


cmd = new SqlCommand("proc_register", con);
04 // basic structure

cmd.CommandType = CommandType.StoredProcedure; 05
//passing variables to procedures as
// 06 //cmd.Parameters.Add("parametername", SqlDbType.datatype).Value = textboxvalue;
07
cmd.Parameters.Add("@frstnme", SqlDbType.VarChar).Value = textbox1.text; 08 cmd.Parameters.Add("@lstnme", SqlDbType.VarChar).Value = textbox2.text; 09 cmd.Parameters.Add("@emailid", SqlDbType.VarChar).Value =textbox3.text;
cmd.Parameters.Add("@mobileno", SqlDbType.VarChar).Value =tetxbox4.text; 10 cmd.Parameters.Add("@qualify", SqlDbType.VarChar).Value =textbox5.text; 11 cmd.Parameters.Add("@usernme", SqlDbType.VarChar).Value =textbox6.text; 12 cmd.Parameters.Add("@pwd", SqlDbType.VarChar).Value = textbox7.text; 13 cmd.Parameters.Add("@usertype", SqlDbType.VarChar).Value =textbox8.text; 14
//to add date time

DateTime dt1 = Convert.ToDateTime(r9); 15 cmd.Parameters.Add("@dte", SqlDbType.DateTime).Value = dt1; 16 cmd.Parameters.Add("@gender", SqlDbType.VarChar).Value = r10; 17 cmd.Parameters.Add("@autogenid", SqlDbType.VarChar).Value = r11; 18 con.Open(); 19 if (cmd.ExecuteNonQuery() > 0) 20 { 21 result = "yes"; 22 } 23 else { result = "no"; } 24 25 } 26 catch (Exception ex) { error = ex.Message; } 27 finally { con.Close(); }


hence finally run the program


u will find the solution..



thats it



cheers


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend