Executing SQL statements with stored procedures in asp.net

Stored procedure: A set of SQL statements that are compiled and stored in a database in order to complete a specific function. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures are an important object in a database, and any well-designed database application should use stored procedures. by Google

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

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

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

Public DataTable executequery (string Sqltext,commandtype ct); Not only incoming SQL statements 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 the query method is written, you can write a method of the SQL statement or stored procedure.

For example: stored procedures

Public DataTable selectnewnews ()

{

Return SqlHelper. ExecuteQuery ("Stored procedure name", CommandType.StoredProcedure)

}

SQL statement

Public DataTable SelectAll ()

{

DataTable dt=new DataTable ();

String Sql= "SELECT * from News";

Dt=sqlhelper. ExecuteQuery (sql,commandtype.text); ·

Return DT

}