Detailed examples of SQL stored procedures in Asp.net (C #)

Record set
Create procedure getarticle
As
Select * From article_content

Go
Asp.net call Method

Sqlconnection conn = new sqlconnection ();
Conn. connectionstring = data. connstr ();
Conn. open ();
Sqldataadapter SDR = new sqldataadapter ();
SDR. selectcommand = new sqlcommand ("getarticle", Conn );
SDR. selectcommand. commandtype = commandtype. storedprocedure;
Dataset rs = new dataset ();
SDR. Fill (RS );
SDR. Dispose ();
Conn. Dispose ();
Response. Write (Rs. Tables [0]. Rows. Count );

Bytes -------------------------------------------------------------------------------------
Delete record (with input parameters)
Create procedure delarticle
@ ID int
As
Delete from article_content where id = @ ID

Go

Asp.net call Method
If (request. querystring ["ID"]! = NULL)
{
Sqlconnection conn = new sqlconnection ();
Conn. connectionstring = data. connstr ();
Conn. open ();
Sqlcommand cmd = new sqlcommand ("delarticle", Conn );
Cmd. commandtype = commandtype. storedprocedure;
Cmd. Parameters. Add ("@ ID", sqldbtype. Int, 4). value = int. parse (request. querystring ["ID"]. tostring ());
Response. Write (CMD. executenonquery ());
Cmd. Dispose ();
Conn. Dispose ();
}

Bytes -------------------------------------------------------------------------------------

Add a record (with output parameters). Update is used to modify the record.

Set two output parameters to give you a bit of understanding about multiple output parameters.

Create proc add_channel
@ Channel_title nvarchar (50 ),
@ Channel_name nvarchar (50 ),
@ Channel_ename nvarchar (50 ),
@ Channel_templet int,
@ Keyword nvarchar (100 ),
@ RTV int output,
@ Rtv1 int output,
@ Kdescription nvarchar (200)
As
If exists (select * from channel where channel_name = @ channel_name)
Begin
Set @ RTV = 0
Set @ rtv1 = 5
End
Else
If exists (select * from channel where channel_ename = @ channel_ename)
Begin
Set @ RTV = 1
Set @ rtv1 = 6
End
Else
Begin
Insert into channel (channel_title, channel_name, channel_ename, channel_templet, keyword, kdescription) values (@ channel_title, @ channel_name, @ channel_ename, @ channel_templet, @ keyword, @ kdescription)
Set @ RTV = 2
Set @ rtv1 = 7
End

 
Go

Asp.net call Method

Public partial class default2: system. Web. UI. Page
{
Public String RVs (string Str) {// return the corresponding prompt information based on the parameter value
If (STR = "0 "){
Return "Duplicate name ";
}
Else if (STR = "1 "){
Return "duplicate English names ";
}
Else {
Return "added successfully! ";
}
}
Protected void page_load (Object sender, eventargs E)
{

}
Protected void button#click (Object sender, eventargs E)
{
Sqlconnection conn = new sqlconnection ();
Conn. connectionstring = data. connstr ();
Conn. open ();
Sqlcommand cmd = new sqlcommand ("add_channel", Conn );
Cmd. commandtype = commandtype. storedprocedure;
Cmd. Parameters. Add ("@ channel_title", sqldbtype. nvarchar, 50). value = channel_title.text.trim ();
Cmd. Parameters. Add ("@ channel_name", sqldbtype. nvarchar, 50). value = channel_name.text.trim ();
Cmd. Parameters. Add ("@ channel_ename", sqldbtype. nvarchar, 50). value = channel_ename.text.trim ();
Cmd. Parameters. Add ("@ channel_templet", sqldbtype. Int, 4). value = int. parse (channel_templet.text.trim ());
Cmd. Parameters. Add ("@ keyword", sqldbtype. nvarchar, 100). value = keyword. Text. Trim ();
Cmd. Parameters. Add ("@ kdescription", sqldbtype. nvarchar, 200). value = kdescription. Text. Trim ();
Cmd. Parameters. Add ("@ RTV", sqldbtype. Int, 4). Direction = parameterdirection. output;
Cmd. Parameters. Add ("@ rtv1", sqldbtype. Int, 4). Direction = parameterdirection. output;
Cmd. executenonquery ();
Conn. Dispose ();
Conn. Close ();
Response. write ("<SCRIPT> alert (" + RVs (CMD. parameters ["@ RTV"]. value. tostring () + "[" + cmd. parameters ["@ rtv1"]. value. tostring () + "]); window. history. back (); </SCRIPT> ");

}
}