Program Life series-news publishing system 12-17

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

...

...

}