Accessing the database

1. Connect to the database

　　In doing the project, we all need to access SQL Server through the program

by using the technology of ADO, we can submit a bunch of classes that execute SQL statements to the database in the program.

Native Access direct access to "Windows Authentication", but the general project is a separate database server, the program on another computer connected to SQL Server, security operations.

Do not enable the SA account during the project, but create a dedicated account, which is based on security considerations.

　　

　　How to enable the SA account: After entering the Properties page, you can enable the SA account and change the password

　　　

2. Strings connected to the database　　

In C # code, the string format used to connect to the database:

Data source=127.0.0.1,1433;

Initial Catalog=mydb;

User Id=sa;

password=123456

　　Data Source to connect to the database IP address and port, IP address, port, IP address must be filled out, the port can not be filled in. Connect the machine . or 127.0.0.1 on the other machine, fill in the IP address, such as 192.168.1.100.

Initial Catalog The name of the database to access

User ID username

Password Password

3. Connect to the database using code

　　To use the SqlConnection class, you must refer to the following namespaces: using System.Data.SqlClient;

　　Create a connection to SQL Server through the SqlConnection class, SqlConnection represents a database connection.

string " Data source=127.0.0.1,1433;initial catalog=mytest; User Id=sa; password=sqlserver2012";     using New SqlConnection (strconn))
{    Conn. Open ();
// Do something          }

　　Why is a keyword using used when connecting to a database?

　　1. By using the use, the purpose of simplifying the release of resources can be achieved, and all classes that inherit the IDisposable interface can achieve the purpose of automatically releasing resources through using. The method that frees the resource is automatically executed when it is executed. We don't have to write close (), Dispose (), and so on to shut down and release resources. This is also a way for Microsoft to simplify the release of resources.

2. If the connection to the database is always occupied, the server will be overwhelmed if there are many clients. In addition, because of network instability, it is easy to cause unnecessary trouble. So when you need to operate the database, to open the database connection, do not need time to close. This allows the server to tolerate more client access at the same time.

　　

　　

1             stringstrconn ="Data source=127.0.0.1,1433;initial catalog=mytest; User Id=sa; password=sqlserver2012";2             using(SqlConnection conn =NewSqlConnection (strconn))3             {4 Conn. Open ();5                 //issuing directives to the database through the SqlCommand class6                 using(SqlCommand cmd =Conn. CreateCommand ())7                 {8                     //A SQL injection vulnerability attack occurs after entering 1 ' or ' 1 ' = ' 1 in a query statement that directly stitching strings9                     //cmd.commandtext = "Select Salary from t_staff WHERE Name = '" + tboxstaffname.text + "'";Ten  One                     //can have multiple parameters ACmd.commandtext ="Select Salary from t_staff where Name = @Name and age = @Age"; -                     //multiple parameters need to be assigned sequentially -Cmd. Parameters.Add (NewSqlParameter ("@Name", Tboxstaffname_copy.text)); theCmd. Parameters.Add (NewSqlParameter ("@Age", Tboxstaffage.text)); -  -                     //class used to read data from the server -                     using(SqlDataReader reader =cmd. ExecuteReader ()) +                     { -                          while(reader. Read ()) +                         { AMessageBox.Show (Tboxstaffname_copy.text +"The Age is"+ Tboxstaffage.text +", the salary is:"+ reader["Salary"]); at                         } -                     } -                 } -}

4. SqlCommand class

　　Classes that issue directives to the database

The SQL statement to execute is stored in the CommandText property.

Common methods:

1. void ExecuteNonQuery () is typically used to execute an SQL statement of update, Delete, insert type, that is, an SQL statement that executes a non-query type. 　　　

　　　2. Object ExecuteScalar () returns a value of type object that is typically used to execute an SQL statement with only one row or column of return values.

3. SqlDataReader ExecuteReader () is used to execute SQL statements with multiple query results.

4. SqlDataReader class　

With it, we can read the data from the server, which is generally quite large. The result of the query is placed in the database, and if the data is transmitted to the client, it consumes too much memory from the client. so this class is used when the query result data is large.

How to read this data:

We can get the data by means of indexers or getstring, GetInt32 and so on. Note that when reading data through an indexer, the type of data obtained is the type object, in which the data is strongly converted to the corresponding type.

 while (reader. Read ())    {            Console.WriteLine (reader[0])://Gets the first column of Data            Console.WriteLine (reader[ " Name " ]):   //Gets the data Console.WriteLine column named Name            (reader. GetString (1)):// 2nd Column string type, gets the number of the 2nd, string type

Initially, before the pointer points to the first data, each call to the reader pointer moves down one cell, and the return value of Read () is true as long as it is not moved to the last block.

　　　　

5. DataSet class

It is equivalent to a complex set, such as a List

Unlike SqlDataReader, we need to transfer data from the server to the client's dataset before the client gets the data directly from it. therefore, using a dataset can reduce the burden on the server when the amount of data being queried is small.

To read data using a dataset:

1             stringstrconn ="Data source=127.0.0.1,1433;initial catalog=mytest; User Id=sa; password=sqlserver2012";2             using(SqlConnection conn =NewSqlConnection (strconn))3             {4 Conn. Open ();5                 using(SqlCommand cmd =Conn. CreateCommand ())6                 {7Cmd.commandtext ="SELECT * from T_staff";8 9                     //SqlDataAdapter is a class that helps us populate the SqlCommand query results into a datasetTenSqlDataAdapter adapter =NewSqlDataAdapter (cmd); One                     //datasets are equivalent to a complex set of local (just like list) ADataSet DataSet =NewDataSet (); -                     //executes a query statement and populates the query result into a dataset - adapter. Fill (DataSet); the  -                     //put the results of the query into the table -DataTableCollection Tablecoll =Dataset.tables; -DataTable table = tablecoll[0]; +                     //The data in each row is put in the collection -DataRowCollection Rowscoll =table. Rows; +  A                     //use loops to get the data for each row at                     foreach(DataRow rowinchrowscoll) -                     { -Lstaff.add (NewStaff () -                         { -Name = (string) row["Name"], -Age = (int) row[" Age"], inSex = (BOOL) row["Sex"], -Height = (decimal) row["Height"], toSalary = (decimal) row["Salary"], +Department = (string) row["Department"] -                         }); the                     } *                 } $}

　　

7. SQL statement Parameterized Query

　　A method of directly splicing SQL statement strings

Cmd.commandtext = "Select Salary from t_staff WHERE Name = '" + tboxstaffname.text + "'";

The tboxstaffname.text here is usually the content on the client's text box, which is prone to SQL injection vulnerability attacks.

Cmd.commandtext = "Select Salary from t_staff WHERE Name = ' 1 ' or ' 1 ' = ' 1 '";

After the execution of the statement, all employees in the table to find out the wages, resulting in the leakage of data.

To avoid such problems, we use parameterized queries.

1. First put the parameter into the SQL statement, the format of the parameter: @Name, must begin with @

2. Assigning values to parameters

                    //A SQL injection vulnerability attack occurs after entering 1 ' or ' 1 ' = ' 1 in a query statement that directly stitching strings//cmd.commandtext = "Select Salary from t_staff WHERE Name = '" + tboxstaffname.text + "'"; //can have multiple parametersCmd.commandtext ="Select Salary from t_staff where Name = @Name and age = @Age"; //multiple parameters need to be assigned sequentiallyCmd. Parameters.Add (NewSqlParameter ("@Name", Tboxstaffname_copy.text)); Cmd. Parameters.Add (NewSqlParameter ("@Age", Tboxstaffage.text));

　Note: Parameters cannot be used to replace keywords such as table name, field name, select, and so on.

Accessing the database