Program Life series-news publishing system 06-09

1. Three-tier architecture

Web; BLL; dal

Create a three-tier architecture in Vs, create a website Web under a unified folder, and add reference BLL and Dal

2. Perform more tests while performing the project, while thinking about how to implement them, and testing to see if there are any problems.

For example, test whether the database can be connected successfully. [Note: the correct connection string is displayed in the database attributes created by the server resource manager !]

3. sqlcommand. executenonquery Method

For update, insert, and delete statements, the returned value is the number of rows affected by the command. If a trigger exists on the table that is executing the insert or update operation, the returned value includes the number of rows affected by the insert or update operation and the number of rows affected by one or more triggers. For all other types of statements, the return value is-1. If rollback occurs, the return value is-1.

4. Using statement

Using (SDR = cmd. executereader (commandbehavior. closeconnection ))

{

DT. Load (SDR );

}

Equivalent:

SDR = cmd. executereader ();

DT. Load (SDR );

SDR. Close ();

Conn. Close ();

In fact, this method is similar to the try-catch statement block.

Public int executenonquery (string SQL)

{

Int res;

Try

{

Cmd = new sqlcommand (SQL, getconn ());

Res = cmd. executenonquery ();

}

Catch (system. Exception E)

{

Throw E;

}

Finally // use finally to close sqlconnection!

{

If (conn. State = connectionstate. open)

{

Conn. Close ();

}

}

Return res;

}

5. Reconstruction Method

① Define some private objects in sqlhelper for database operations, sqlconnection sqlcommand sqldatareader

Some objects are instantiated in the sqlhelper constructor.

Private sqlconnection conn = NULL;

Private sqlcommand cmd = NULL;

Private sqldatareader SDR = NULL;

Public sqlhelper ()

{

String constr = configurationmanager. connectionstrings ["connectionstring"]. connectionstring;

Conn = new sqlconnection (constr );

}

② Get the database connection object and write it into a method, and open the connection in the Method

Private sqlconnection getconn ()

{

If (conn. State = connectionstate. Closed)

{

Conn. open ();

}

Return conn;

}

6. SQL Injection

If you enter entertainment news 'when adding news categories) delete category where id = 3 --

The actual SQL statement is insert into category (name) values ('entertainment news ') delete category where id = 3 --')

Obviously, after the entertainment news is inserted, the news category numbered 3 is also deleted.

Solution: SQL Parameters

For example, the method executenonquery in sqlhelper is overloaded.

Public int executenonquery (string SQL, sqlparameter [] paras)

{

Int res;

Using (cmd = new sqlcommand (SQL, getconn ()))

{

Cmd. Parameters. addrange (paras );

Res = cmd. executenonquery ();

}

Return res;

}

The method called in categorydao to add methods of the news class.

Public bool insert (string caname)

{

Bool flag = false;

String SQL = "insert into category (name) values (@ caname )";

Sqlparameter [] paras = new sqlparameter [] {

New sqlparameter ("@ caname", caname)

};

Int res = sqlhelper. executenonquery (SQL, paras );

If (RES> 0)

{

Flag = true;

}

Return flag;

}