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
Copy codeThe 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 codeThe Code is as follows:
Public DataTable test (string procName)
{
DataTable dt = new DataTable ();
Cmd = new SqlCommand (procName, GetConn (); // The GetConn () method is used to open and close database connections.
Cmd. CommandType = CommandType. StoredProcedure; // define the SQL statement command type as stored procedure
Using (sdr = cmd. ExecuteReader (CommandBehavior. CloseConnection); // sends an SQL statement to SqlConnection and produces a SqlDataReader Class Object. This SqlDataReader object contains the data returned by the SQL command
{Dt. Load (sdr); // load query the 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 procedure
Copy codeThe Code is as follows:
Public DataTable SelectNewNews ()
{
Return sqlhelper. ExecuteQuery ("Stored Procedure name", CommandType. StoredProcedure)
}
SQL statement
Copy codeThe Code is as follows:
Public DataTable SelectAll ()
{
DataTable dt = new DataTable ();
String SQL = "select * from news ";
Dt = sqlhelper. ExecuteQuery (SQL, CommandType. Text );·
Return dt
}