The efficiency of stored procedure execution is higher than that of individual SQL statements.
Sample writing stored procedures? Stored procedures are in the programmable directory of the SQL Server 2005 counterpart database.
For example, to create a stored procedure
Copy Code code as follows:
CREATE PROCEDURE Procnewsselectnewnews
As
Begin
Select Top 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 a well-defined stored procedure
EXEC procnewsselectnewnews
The stored procedure returns a table
Copy Code code as follows:
Public DataTable Test (string procname)
{
DataTable dt=new DataTable ();
Cmd=new SqlCommand (Procname,getconn ()); Database connection and connection opening and closing, all in the Getconn () method
Cmd.commandtype=commandtype.storedprocedure//Define SQL statement command type as stored procedure
using (SDR = cmd.) ExecuteReader (commandbehavior.closeconnection));//method sends the SQL statement to SqlConnection and produces a SqlDataReader class object. The SqlDataReader object contains data returned by the SQL command
{dt. Load (SDR); Load query Dataread query Results}
return DT;
}
When a project uses both SQL statements and stored procedures, executing SQL statements and executing stored procedures is similar to a CommandType type, so if this happens, we can refactor the two methods of SQL statements and stored procedures
Copy Code code as follows:
Public DataTable executequery (string Sqltext,commandtype ct);//not only incoming SQL statements also pass in 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 the query method is written, you can write a method of the SQL statement or stored procedure.
For example: stored procedures
Copy Code code as follows:
Public DataTable selectnewnews ()
{
Return SqlHelper. ExecuteQuery ("Stored procedure name", CommandType.StoredProcedure)
}
SQL statement
Copy Code code as follows:
Public DataTable SelectAll ()
{
DataTable dt=new DataTable ();
String sql= "SELECT * from News";
Dt=sqlhelper. ExecuteQuery (sql,commandtype.text); ·
Return DT
}
