1. refactor sqlhelper
There are several main methods in sqlhelper: SQL addition, deletion, modification, and execution (with an array of parameters) SQL query statements [the array of parameters may not exist]
However, the statements executed may be SQL statements or stored procedures.
So refactor sqlhelper, add commandtype to the method parameters, and specify whether the commandtype is text or storedprocedure in the method body.
Public datatable executequery (string SQL, sqlparameter [] paras, commandtype CT)
{
Datatable dt = new datatable ();
Cmd = new sqlcommand (SQL, getconn ());
Cmd. commandtype = CT;
Cmd. Parameters. addrange (paras );
Using (SDR = cmd. executereader (commandbehavior. closeconnection ))
{
DT. Load (SDR );
}
Return DT;
}
2. Stored Procedure
Similar to a function, you can write a series of Code together to display the call
For example, retrieve all news of this category by category number
Write the stored procedure:
Alter procedure [DBO]. [news_selectbycaid]
@ Caid int
As
Begin
Select N. ID, N. Title, N. createtime, C. Name as caname, N. caid from news n
Inner join category C on N. caid = @ caid and N. caid = C. ID
Order by N. createtime DESC
End
Code for calling a stored procedure:
Public datatable selectbycaid (string CAID)
{
Datatable dt = new datatable ();
String plain text = "news_selectbycaid ";
Sqlparameter [] paras = new sqlparameter [] {
New sqlparameter ("@ Caid", CAID)
};
Dt = sqlhelper. executequery (plain text, paras, commandtype. storedprocedure );
Return DT;
}
Another example is the storage process for updating news. variables are separated by commas (,). Note that the content is text.
Alter procedure [DBO]. [news_update]
@ ID int,
@ Title varchar (1000 ),
@ Content text,
@ Caid int
As
Begin
Update News
Set Title = @ title, content = @ content, caid = @ caid
Where id = @ ID
End
For example, when selecting hot news, pay attention to the group by statement.
Alter procedure DBO. news_selecthotnews
As
Begin
Select top 5 N. ID, N. Title, N. createtime, C. ID as Caid, C. [name] As caname, count (REM. ID) as comcount
From News n
Left join category C on C. ID = n. CID
Left join remark REM on rem. nid = n. ID
Group by N. ID, N. Title, N. createtime, C. [name], C. ID
Order by comcount DESC
End
3. The 17th sets show that if you use a custom template !!!
4. BLL layer: business logic layer
Write the BLL layer: Simply put, encapsulate the Dal layer!
Public class categorymanager
{
Categorydao cdao = NULL;
Public categorymanager ()
{
Cdao = new categorydao ();
}
# Region
Public datatable selectall ()
{
Return cdao. selectall ();
}
# Endregion
...
...
}