Methods for invoking stored procedures in asp.net-practical tips

The example in this article describes the method of calling a stored procedure in asp.net. Share to everyone for your reference, specific as follows:

First, establish and invoke a stored procedure with no parameters as follows:

CREATE PROCEDURE All students <dbo.selectUsers> as
SELECT * from student
go
EXEC all students

Establish and invoke a stored procedure with parameters as follows:

CREATE PROCEDURE Student Query 1
@SNAME VARCHAR (8), @SDEPT VARCHAR as
SELECT * FROM student WHERE name = @SNAME and the line = @SDEPT 
   
    go
EXEC student Query 1 ' John ', ' computer Department '


   

Or:

EXEC Student Query 1 @SNAME = ' john ', @SDEPT = ' computer Department '

(2) Delete stored procedures:

DROP procedure< Stored procedure name group >

Second, invoke the access process in the asp.net:

DBHelper.cs

No parameters public
static DataTable getlist (string sqldbo)
{
  DataSet ds = new DataSet ();
  SqlCommand cmd = new SqlCommand (sqldbo, Connection);
  Cmd.commandtype = CommandType.StoredProcedure; Specifies that the command type is stored procedure
  SqlDataAdapter da = new SqlDataAdapter (cmd);
  Da. Fill (DS);
  Return DS. Tables[0];
}
With parameters public
static DataTable getlist (string sqldbo,params sqlparameter[] values)
{
  DataSet ds = new DataSet ();
  SqlCommand cmd = new SqlCommand (sqldbo, Connection);
  Cmd.commandtype = CommandType.StoredProcedure; Specifies that the command type is stored procedure
   cmd. Parameters.addrange (values);
   Cmd. Parameters.addwithvalue ("@ Parameter 1", value 1); 
   Cmd. Parameters.addwithvalue ("@ Parameter 2", value 2);
  SqlDataAdapter da = new SqlDataAdapter (cmd);
  Da. Fill (DS);
  Return DS. Tables[0];
}

UsersService.cs

//without parameters public static ilist<users> getuserlist () {list<users> List = new
  List<users> ();
  DataTable table = dbhelper.getlist ("Stored procedure name"); foreach (DataRow row in table).
    Rows) {Users users = new users (); Users.
    id= (int) row["id"]; Users.
    Username= (String) row["UserName"]; Users.
    Password= (String) row["Password"]; List.
  ADD (users);
} return list; }///With parameters public static ilist<users> getuserlist (string username,string password) {list<users> List = new Lis
  T<users> (); Sqlparameter[] Para=new sqlparameter[] {new SqlParameter ("@userName", UserName), New SqlParameter ("@password", PA
  ssWOrd)};
  DataTable table = dbhelper.getlist ("Stored procedure name", para); foreach (DataRow row in table).
    Rows) {Users users = new users (); Users.
    id= (int) row["id"]; Users.
    Username= (String) row["UserName"]; Users.
    Password= (String) row["Password"]; List.
  ADD (users);
} return list; }

More interested readers of asp.net related content can view the site topics: "asp.net string operation tips Summary", "ASP.net Operation XML Skills summary", "asp.net file Operation skills Summary", "ASP.net Ajax Skills Summary topics" and " Summary of ASP.net caching operation techniques.

I hope this article will help you to ASP.net program design.