Three ways to operate databases

Three ways to operate databases

Database operations can be divided into three types: first, writing hard SQL code directly without parameters, second, writing hard Code directly, parameters, and third, calling the stored procedure.

Take a logon module as an example. Now there are two text boxes and a button to verify the user name and password. The main code for the first method is as follows:

SqlConnection conn = new SqlConnection

("Server =; database = news2; uid = sa; pwd = ");

Conn. Open ();

SqlCommand cmd = new SqlCommand ();

Cmd. CommandText = "select count (*) from users

Where name = '"+ this. textBox1.Text + "'and pwd ='" + this. textBox2.Text + "'"; cmd. connection = conn; int I = (int) cmd. executeScalar (); Response. write (I. toString (); if (I = 1) {Response. redirect ("add. aspx ");} else {Label1.Text =" error! "}

Second approach

SqlConnection conn = new SqlConnection ("server =; database = news; uid = sa; pwd = ");

Conn. Open (); // Open the database

SqlCommand cmd = new SqlCommand (); // create a command object

Cmd. CommandText = "select count (*) from users where and ";

Cmd. Connection = conn; // set the Connection

SqlParameter p = new SqlParameter ("@ name", SqlDbType. Char, 10 );

// Define parameters

P. Value = this. TextBox1.Text;

Cmd. Parameters. Add (p); // Add Parameters to the set

P = new SqlParameter ("@ pwd", SqlDbType. Char, 10 );

P. Value = this. TextBox2.Text;

Cmd. Parameters. Add (p );

Int I = (int) cmd. ExecuteScalar ();

If (I = 1)

{

Response. Redirect ("add. aspx ");}

Else

{

Label1.Text = "error! "

}

Third Approach

SqlConnection conn = new SqlConnection ("server =; database = news; uid = sa; pwd = ");

Conn. Open (); // Open the database

SqlCommand cmd = new SqlCommand (); // create a command object

Cmd. CommandText = "checkLogin"; // set the command text

Cmd. CommandType = CommandType. StoredProcedure;

// Set the text type

Cmd. Connection = conn; // set the Connection

SqlParameter p = new SqlParameter ("@ name", SqlDbType. Char, 10 );

// Define parameters

P. Value = this. TextBox1.Text;

Cmd. Parameters. Add (p); // Add Parameters to the set

P = new SqlParameter ("@ pwd", SqlDbType. Char, 10 );

P. Value = this. TextBox2.Text;

Cmd. Parameters. Add (p );

Int I = (int) cmd. ExecuteScalar ();

If (I = 1)

{

Response. Redirect ("add. aspx ");}

Else

{

Label1.Text = "error! "

}

Next, we will analyze the three methods:

The first method cannot prevent SQL injection attacks. For example, in the first text box, enter asd or's ='s. In the second text box, enter asd or's ='s, the verification is successful.

The second method is to directly write hard SQL code. In fact, not everyone can write excellent SQL code, which can be written by the database administrator or engineer. This reduces the workload of programmers on the one hand, on the other hand, the database and application can be kept independent, which is conducive to system migration and maintenance.

Of course, the third type is recommended. What are the benefits! This is what we wrote earlier.