[MrYoung Tutorial: easy to learn] 2ADONET basic data presentation

I. Previous Review
In the first article of this series, we introduced the concept of relational databases, and then learned how to use the Enterprise Manager to create database tables and add data. Basic SQL statements: Query, add, modify, finally, we use the knowledge we have explained to implement a WINFORM login form.

Ii. Overview
This will be the second article in the [MrYoung Tutorial: easy to learn] ADONET basics series. In this article, we will focus on data presentation, how to retrieve data from the database and display it to our programs. The DATASET, SQLDATAADAPER, and dview controls are described in this section.

Iii. Main Content
3.1 USING usage.

3.2 DataSet introduction.

3.3 SqlDataAdapter introduction.

3.4 control data binding.

Iv. USING
4.1 Using (), as a statement, is mainly used to define a range, indicating that the object will be released at the end of this range. For example, in the above example, we instantiate the SqlConnection object. after use, we should call the CLOSE method of this object to ensure that the object is closed. Now we can use USING to instantiate it, in this way, the system will automatically release this object after use, for example:
 

View sourceprint? 1 // instantiate the object in the brackets. After the code in the curly brackets is executed, the system will help us release the sqlcon object.

2 using (SqlConnection sqlcon = new SqlConnection (connectionString ))

3 {

4 sqlcon. open ();

5 // DoSomeThing;

6}

View sourceprint?

4.2 The using statement ensures that Dispose is called, even if an exception occurs when calling the method on the object. In fact, try .. catch also applies the USING principle. If you are interested, you can learn more.

4.3 emphasize that Dispose is called and using is called, and try. catch is used for exception handling.

4.4 After learning USING, We will rewrite our BaseOperate and change all methods and fields to static. static members can be directly used without instantiation, first declare a static string connectionString to save the string connection, then write a static method getStrcon to get the string connection, declare a static constructor, so that when calling this class, ensure that the connectionString is correctly assigned a value, the Code is as follows:

 

View sourceprint? 01 /// <summary>

02 // database connection string

03 // </summary>

04 public static string connectionString = null;

05

06 // <summary>

07 // Constructor

08 // </summary>

09 static BaseOperate ()

10 {

11 connectionString = getStrcon ();

12}

13 /// <summary>

14 // obtain the database connection string

15 /// </summary>

16 /// <returns> </returns>

17 public static string getStrcon ()

18 {

19 return "packet size = 4096; data source = 127.0.0.1; persist security info = True; initial catalog = Db_Example; user id = sa ";

20}

 

Then, we changed the above getcom method to a static method, so that after using this method, the system will automatically help us release resources and objects:

 

View sourceprint? 01 /// <summary>

02 // execute the SQLCOMMAND

03 // </summary>

04 // <param name = "str_ SQL"> SQL statement to be executed </param>

05 public static void getcom (string str_ SQL)

06 {

07 using (SqlConnection sqlcon = new SqlConnection (connectionString ))

08 {

09 using (SqlCommand sqlcom = new SqlCommand (str_ SQL, sqlcon ))

10 {

11

12 sqlcon. Open ();

13 sqlcom. ExecuteNonQuery ();

14

15}

16}

17}

 

For the getread method, the SqlDataReader object must be returned, and the sqlcom. executeReader (CommandBehavior. closeConnection), so we do not use USING, but the closed connection shown by the caller after use. We also rewrite it to the static method as follows:

 

View sourceprint? 01 /// <summary>

02 // create a SQLDATAREADER object

03 // </summary>

04 // <param name = "str_ SQL"> SQL statement to be executed </param>

05 // <returns> return SQLDATAREADER object </returns>

06 public static SqlDataReader getread (string str_ SQL)

07 {

08 SqlConnection sqlcon = new SqlConnection (connectionString );

09 SqlCommand sqlcom = new SqlCommand (str_ SQL, sqlcon );

10 sqlcon. Open ();

11 SqlDataReader sqlread = sqlcom. ExecuteReader (CommandBehavior. CloseConnection );

12 return sqlread;

13

14}

V. DATASET
5.1 DataSet is the main component of the ADO. NET structure. It is the cache of data retrieved from the data source in the memory. In short, DATASET is the result set that is returned to us after the SELECT statement of the database query is executed. It contains the query results and is stored in the memory. However, DATASET is not limited to database queries.

5.2 For any data source, it provides a consistent relational programming model. In DataSet, the constraints and relationships between data tables are defined, and data in the data table can be sorted.

5.3 independence, both offline and real-time connections can be used to operate data in the database.

As shown in, a DATASET can contain multiple data tables, and a data table is equivalent to a table in our database, such as TB_USERINFO, which has rows and columns, after we execute the database query, we will get a DATASET. If we query the information of TB_USERINFO, there will be a table in the dataset and its sequence is 0 (note, generally, we start counting with 0 instead of 1), which is expressed as DATASET. TABLE [0]. In SQL, we can execute a query using the SqlDataAdapter class to obtain a DATASET object.
6. SqlDataAdapter
6.1 indicates a set of data commands used to fill the DataSet and update the SQL Server database and connect to a database.

 

6.2 SqlDataAdapter Constructor (String, SqlConnection), which accepts a SQL query command and a database connection object.

6.3 DbDataAdapter. Fill Method (DataSet). With this method, we can Fill in the input DATASET parameter to obtain the query result.

Next we will expand our BaseOperate class, add a query method, and return a DATASET, as shown below

View sourceprint? 01 /// <summary>

02 // execute the query statement and return DataSet

03 // </summary>

04 // <param name = "SQLString"> query statement </param>

05 /// <returns> DataSet </returns>

06 public static DataSet getds (string str_ SQL)

07 {

08 // use USING

09 using (SqlConnection sqlcon = new SqlConnection (connectionString ))

10 {

11 // instantiate a DATASET object

12 DataSet ds = new DataSet ();

13 sqlcon. Open ();

14 // instantiate a SqlDataAdapter object

15 SqlDataAdapter sqldata = new SqlDataAdapter (str_ SQL, sqlcon );

16 // fill ds

17 sqldata. Fill (ds );

18 return ds;

19}

20}

7. COMBOBOX Data Binding
In our previous logon interface, our user name is required