[ASP. NET development] ADO. NET Usage notes during the cainiao Period

In the previous article in this series, I wrote a little bit of notes about server controls.

Actually used by cainiao. after a long time of development, we will find that almost no one uses the server control provided by Microsoft, because everyone wants to customize the control they need in depth, in this way, the performance and requirements can be met. To put it bluntly, you can write your own controls.

Some Daniel expressed strong contempt and disdain for the knowledge of the word "control". They advocated ASP. net mvc and felt that this was the king.

I only know about. net, so I cannot tell the "correct" Development Mode of. net.

Now, I will share my accumulated notes during my use of ADO. NET.

Mild popularity: ADO. NET is used in.. NET. NET program needs to use ADO when it needs to exchange data with the database.. NET.

The basic procedure of using ADO. NET:

(1) Establish a connection to the database.

(2) Create the SQL statement to be executed.

(3) Open the connection and execute the SQL statement.

(4) A prompt message is returned when the connection is closed.

 

Body 1: namespace required to use ADO. NET Technology

System. Data

System. Data. SqlClient

 

2. The connection string required to connect to the database using the SqlConnection object

Authentication Mode:

@ "Server = Server name; Database = Database name; User ID = Database User Name; Password = Database Password ;"

Trust mode:

@ "Server = Server name; database = database name; Trusted_Connection = True ;"

 

3. concatenate a connection string

(1) string. Format ("Server = {0}; Database = {1}; User ID = {2}...", Textbox1.Text ....)

[Very insecure and cannot prevent SQL injection attacks]

Solution:Use parameterized SQL statements and stored procedures to improve security

Different data providers have corresponding Parameter to represent SQL statements or various parameters in stored procedures. Parameters correspond to the actual types of database fields. The values of all parameters are considered to be only one parameter. Therefore, it is meaningless to write any SQL statement in parameters.

Eg:

SqlCommand cmd=new Sqlcommand("Select count(*) from tbClass where ClassName=@ClassName",conn)cmd.Parameters.Add("@ClassName",SqlDbType.VarChar,50)cmd.Parameters["@ClassName"].Value=tb_ClassName.Text;

　　

SqlCommand cmd=new Sqlcommand("Select count(*) from tbClass where ClassName=@ClassName",conn)cmd.Parameters.Add("@ClassName",SqlDbType.VarChar,50)cmd.Parameters["@ClassName"].Value=tb_ClassName.Text;

 

 

 

Note:

All Parameters specified in SQL statements or stored procedures must correspond to all Parameters in the Parameters attribute.

There is also an AddWithValue () method that can assign values to parameters at the same time:

Cmd. Parameters. AddWithValue ("@ ClassName", tb_ClassName.Text );

This statement can replace the preceding two statements. It will automatically detect the parameter type and length.

The SqlParameter object has a ction method, which is used to specify the Direction of stored procedure parameters. Its value is defined by ParameterDirection enumeration. There are four in total: Input, InputOutput, Output, and ReturnValue.

　　

(2) Use the ConnectionStringBuilder class.

Eg:

　　SqlConnectionStringBuilder me=new SqlConnectionStringBuilder();　　me.DataSource=XX.Text;　　me.InitialCatalog=XX.Text;　　me.UserID=XX.Text;

 

SqlConnectionStringBuilder me=new SqlConnectionStringBuilder();me.DataSource=XX.Text;me.InitialCatalog=XX.Text;me.UserID=XX.Text;

 

　　SqlConnectionStringBuilder me=new SqlConnectionStringBuilder();　　me.DataSource=XX.Text;　　me.InitialCatalog=XX.Text;　　me.UserID=XX.Text;

 

Then, when instantiating SqlConnection, it is passed as a constructor parameter:

SqlConnection conn = new SqlConnection (me. ConnectionString );

 

4. The later you open SqlConnection, the better. The sooner you close SqlConnection, the better.

For SqlConnection, we can use the using {} statement block to automatically release the active connection:

　　using (SqlConnection conn=new SqlConnection(sConnectionString))　　{　　　　conn.Open();　　　　using(SqlCommand cmd=new SqlCommand(sSql,conn))　　　　{　　　　　　cmd.ExecuteNonQuery();　　　　}　　}

 

After using is used, the Connection object no longer needs to Close (). The using statement will automatically call the Dispose () method of the corresponding object at the end of the code block to release the object resource, for objects involving system resources, Microsoft recommends using

 

5. Use the Sqlcommand object to execute the Stored Procedure

The following is a brief example:

// Create a Command object and enter the name of the stored procedure SqlCommand cmd = new SqlCommand ("Stored Procedure name", conn); // modify the CommandType attribute of the Command object to execute the stored procedure, the default value is CommandType. text, used to execute the SQL statement cmd. commandType = CommandType. storedProcedure; // Add parameters and assign values to cmd. parameters. add ("@ ClassName", SqlDbType. varChar, 50); cmd. parameters ["@ ClassName"]. value = xx. text; // specify the parameter direction to cmd. parameters ["@ ClassName"]. direction = ParameterDirection. input;

 

 

6. Description of DataReader

(1) The DataReader object cannot be instantiated directly using the new keyword. You can use the ExecuteReader () method of the Command object to obtain a DataReader object.

(2) DataReader is a Read-only method, and a row reads records forward. Therefore, while (DataReader. Read () is often used to traverse all rows.

(3) Before reading a column value in a row, use the IsDBNull (column index number) method of DataReader to determine whether a column has a value, so as to avoid exceptions caused by the failure of type conversion.

(4) Only one DataReader can be used to read multiple tables by using the NextResult () method of DataReader.

 

VII. Brief examples of how to create DataSet

// Create a database DataSet Forum = new DataSet ("Forum"); // create a data table DataTable tbClass = new DataTable ("tbClass"); // Add the data table to the database Forum. tables. add (tbClass); // create a DataColumn ClassID = new DataColumn ("ClassID", typeof (System. string); // The ClassID Column cannot be empty. allowDBNull = false; // Add columns to the tbClass table. columns. add (ClassID); // set the primary key tbClass of the tbClass table. primaryKey = new DataColumn [] {ClassID}; // Add a row record for (int I = 1; I <= 5; I ++) {// instantiate the row DataRow tbClassRow = tbClass. newRow (); // assign tbClassRow ["ClassID"] = Guid to each column in the row. newGuid (); // Add rows to the tbClass table. rows. add (tbClassRow );}

 

Note:

DataTable cannot be used independently without adding DataSet. Similarly, DataColumn must be added to DataTable.

For the primaryKey attribute of DataTable, it needs to assign a DataColumn array, because the primary key can be composed of several columns.

DataRow cannot be directly instantiated because data rows belong to data tables and can only be obtained using the NewRow () method.

 

VIII. SqlDataAdapter & SqlConnection

For SqlDataAdapter, you do not need to open or close the Connection object. It manages related connections by itself.

 

IX. Data Binding on HTML pages

'<% # DataBinder. Eval (Container. DataItem, "Your Data variable") %>'

 

10. database connection time settings

By default, the database connection timeout is 15 seconds. You can adjust the connection string to change the read-only ConnectionTimeout attribute. You only need to add Connection Timeout = 2 when initializing the SqlConnection class variable to set the Timeout time to 2 seconds.

 

11. enumeration of all available data sources

You can use the SqlDataSourceEnumerator class (System. Data. SQL namespace) to implement enumeration. Example:

Note: ddl_Server is the Control ID of the drop-down list box. In addition, this operation is very slow and should be used with caution.

SqlDataSourceEnumerator instance =SqlDataSourceEnumerator.Instance;ddl_Server.DataSource=instance.GetDataSources();ddl_Server.DataTextField="ServerName";ddl_Server.DataBind();

 

 

12. Use DataAdapter to fill DataSet

After the DataSet is filled with DataAdapter, the names of tables in DataSet are Table, Table1, and Table2 by default. You can use the DataTableMapping class to map the DataTable and DataColumn with a friendly name. before using the Fill method, use the following code:

Example:

DataTableMapping dtmClass = adapter. TableMappings. Add ("Table", "new name 1"); dtmClass. ColumnMappings. Add ("ClassID", "new name ");

 

Note: The DataTableMapping class belongs to the System. Data. Common namespace.

 

13. Common SQL types

Uniqueidentifier is used for GUID primary key columns. Set the Data Type of the column to this, and set the default value to (newid () to implement GUID. If the primary key column is GUID, you do not need to assign a value to it.

The varchar/char length ranges from 1 to 2 ~ The difference between 8000 is that char is a fixed-length character data while varchar is a variable-length character data. The so-called fixed length is a fixed length. when the length of the input data does not reach the specified length, it is automatically filled with English spaces to make the length reach the corresponding length; the variable-length character data is not filled with spaces.

Int 4 bytes

Integer Data of bit 1 or 0. Stores boolean data.

Datetime January 1, 1753 ~ The date and time data between January 1, December 31, 9999, accurate to 30 ms.

Text stores variable-length non-Unicode data, with a maximum length of 2 to the power of 31-1 characters.