Execute SQL statements using stored procedures in ASP. NET

The execution efficiency of stored procedures is higher than that of separate SQL statements.
How to Write a stored procedure? The stored procedure is stored in the programmable directory of the database corresponding to SQL Server 2005.
For example, create a stored procedure CopyCode The Code is as follows: Create procedure procnewsselectnewnews
As
Begin
Select top 10 N. ID, N. Title, N. createtime, C. name from news n
Inner join category C on N. caid = C. ID
Order by N. createtime DESC
End

execute the defined Stored Procedure
exec procnewsselectnewnews
the stored procedure returns a table copy Code the code is as follows: public datatable test (string procname)
{< br> datatable dt = new datatable ();
cmd = new sqlcommand (procname, getconn (); // The database connection and connection are both in the getconn () method.
cmd. commandtype = commandtype. storedprocedure; // defines the SQL statement command type as stored procedure
using (SDR = cmd. executereader (commandbehavior. closeconnection); // method sends an SQL statement to sqlconnection and generates a sqldatareader class object. The sqldatareader object contains the data returned by the SQL command
{DT. load (SDR); // load query dataread query result}
return DT;
}

When both SQL statements and stored procedures are used in a project, the methods for executing SQL statements and stored procedures are similar, that is, there is a commandtype difference, therefore, in this case, we can reconstruct the two methods about SQL statements and stored procedures.Copy codeThe Code is as follows: public datatable executequery (string sqltext, commandtype CT); // not only the SQL statement but also a command type
{
Datatable dt = new datatable ();
Cmd = new sqlcommand (sqltext, getconn ());
Cmd. commandtype = CT;
Using (SDR = cmd. executereader (commandbehavior. closeconnection ))
{DT. Load (SDR );}
Return DT;
}

After writing the query method, you can write SQL statements or stored procedure methods.
For example, stored procedureCopy codeThe Code is as follows: public datatable selectnewnews ()
{
Return sqlhelper. executequery ("Stored Procedure name", commandtype. storedprocedure)
}

SQL statement copy Code the code is as follows: public datatable selectall ()
{< br> datatable dt = new datatable ();
string SQL = "select * From News";
dt = sqlhelper. executequery (SQL, commandtype. text); ·
return dt
}