Analysis of core ADO. Net objects

ADO. NET is the generic name for class libraries used to operate databases in the. NET Framework SDK. It provides some core objects for database operations. These core objects have been roughly quoted in the previous blog. This blog mainly describes the main objects.

1. connection object

The connection object is a database connection object and is mainly responsible for connecting to the data source. The base class of all connection objects is the dbconnection class.
The connection object has two important attributes:
Connectionstring:Stores the string for opening the database;
State:Indicates the connection status, which can be closed or open.
The connection object has two important methods:
OpenMethod: used to open a database;
CloseMethod: used to close the database;

Using sqlCon As New SqlConnection("Server=longan-pc\mssqlserver2008;Database=Student;uid=sa;pwd=long")    sqlCon.Open()    Console.WriteLine(sqlCon.State.ToString)End Using

When using a connection object to connect to the database, we often define this object in the using statement. After using this object, the object is closed and released to reduce memory usage. If you do not use the using statement, you should call the close method to close the database after using the object, and then use the dispose method to release the object.
You can use the following method to connect to a database:
(1) If the database server to be connected is in the same LAN as the developer's machine, you can use the lan ip address or the computer host name in the LAN;
(2) If the database server to be connected is not in the same LAN as the developer's machine, the database server must have a public IP address. We can use a public IP address to connect to the database server, if the database server has an Internet domain name, you can also use an Internet domain name.
(3) If the database server to be connected is the same as the machine used by the developer, you can use either of the following methods: "(local)" or ". or 127.0.0.1 ". Note that if different versions of the same database are running on one machine, for example, SQL 2000, SQL 2005, and SQL express are running on the "XXXXX" host simultaneously, and the Windows service names used are "SQL2000", "sql2005", and "sqlexpress ", to connect to SQL 2000, enter the server name "XXXXX \ SQL2000", "Host Name \ Instance name", or "Host IP \ Instance name ", in this case, visual
It is common for studio and SQL friends because SQL Express is a free version with limited functions for students. By default, this version of database is installed together When Visual Studio is installed.

Ii. Command object

The command object is also called a database command object. The command object mainly executes commands for adding, deleting, modifying, and querying databases. When executing a stored procedure, you need to set the commandtype attribute of the command object to commandtype. storedprocedure. By default, the commandtype attribute is commandtype. Text, indicating that a common SQL statement is executed.
Common attributes of command objects:
Connection:Define a connection object. If this object is used, it must be bound to the connection object of the database; otherwise, the database cannot be operated;
Parameters:It is mainly used to store the parameters required for parameterized query of databases;
Commandtype:Set the Operation Command type;
Commandtext:Stores the Operation Command statements for the database.

      Public Sub CreateCommand(ByVal queryString As String,ByVal connectionString As String)            Using connection As New SqlConnection(connectionString)                Dim command As New SqlCommand()                command.Connection = connection                command.CommandTimeout = 15                command.CommandType = CommandType.Text                command.CommandText = queryString                        connection.Open()                Dim reader As SqlDataReader = command.ExecuteReader()                    While reader.Read()                    Console.WriteLine(String.Format("{0}, {1}",reader(0), reader(1)))                End While            End Using      End Sub

Main Methods of command object:
Executenonquery ():Execute an SQL statement that is not in the form of a query and return the affected number of rows. It is mainly used to add, update, and delete databases. This method cannot be called during query.

Using sqlcon as new sqlconnection ("Server = longan-PC \ mssqlserver2008; database = student; uid = sa; Pwd = long") sqlcon. open () console. writeline (sqlcon. state. tostring) dim sqlcom as new sqlcommand dim strsql as string = "insert into student_info values (12, 'female ', 'zhang', 78,86, 98)" sqlcom. commandtext = strsql sqlcom. connection = sqlcon sqlcom. executenonquery () End using

Executereader ():Execute a query statement and return a datareader object.

Using sqlcon as new sqlconnection ("Server = longan-PC \ mssqlserver2008; database = student; uid = sa; Pwd = long ") dim username as string = "Zhang Chen" dim I as integer console. writeline (sqlcon. state. tostring) dim sqlcom as new sqlcommand dim strqry as string = "select * From tblonwork where on_username = @ username" sqlcom. parameters. add (New sqlparameter ("@ username", username) sqlcom. commandtext = strqry sqlcom. connection = sqlcon. open () dim sqlreader as sqldatareader = sqlcom. executereader while (sqlreader. read () console. writeline (sqlreader. getstring (I) I = ++ I end while end using

Executescalar ():Retrieve a single value from the database. This method is mainly used for statistical operations. For SQL statement execution, the result set is one row or one column. This method only returns the first column of the first row of the query result set.

        Using sqlCon As New SqlConnection("Server=longan-pc\mssqlserver2008;Database=Student;uid=sa;pwd=long")                    Console.WriteLine(sqlCon.State.ToString)                    Dim sqlCom As New SqlCommand                    Dim strQry As String = "select * from student_info"                    sqlCom.CommandText = strQry                    sqlCom.Connection = sqlCon                    sqlCon.Open()                    TextBox1.Text = sqlCom.ExecuteScalar(2)        End Using

3. datareader object

The datareader object is a read-only stream that reads rows. It can only read databases and cannot be changed.
Datareader cannot be instantiated directly during use. You must use the related command object to create an instance. For example, you can use the executereader () method of the sqlcommand instance to create a sqldatareader instance. Because the datareader object needs to be connected to the database when reading data, you should call its close () method to close it after the datareader object is used to read data, before closing the connection object.
Three methods for reading data from datareader object:
One is to read the column value in the specified way by the column index during the query, and no conversion is required,For example, getstring (int I) reads the value of column I and converts it to a value of the string type. The advantage of this method is that after a specified column is specified, the column is directly read and no conversion is required, the disadvantage is that an exception is thrown when the specified Column cannot be converted according to the specified method. For example, if the field type in the database is double or the field value is null) in this way, an exception is thrown.

The second method is to read data based on the column index, and the value is not converted during reading,For example, reader [5] reads the value of the 5th column (here reader is an instance of the reader object). The obtained value is an object-type value, which is quite understandable, because the database may store various types of values, and the object is the base class of all classes, this method will not throw an exception. If you want to obtain the correct type, you also need to convert it according to the fields in the database.

The last one is to read data by column name, and the conversion is not performed during reading. The object type value is obtained.

The preceding three methods have their own characteristics. The first method is the most direct, but an exception may be thrown. The second method is slightly more flexible than the first method. We can leave the read value blank (in. net, which can represent null values of any data type in the database), we do not convert the corresponding types to avoid exceptions. The third method reads data based on the column name. The second method also requires certain conversions. In terms of performance, the first is the highest, the second is slightly lower, and the third is the lowest (this is easy to understand. If you want to find someone in a hotel, it is definitely faster than finding someone by name ), in terms of flexibility, the third is the most flexible, the second is the second, and the first is the least flexible (if the column index is changed in the later SQL statements, problems may occur in both the first and second types ). In actual development, select the appropriate method based on the actual situation.

To retrieve data using datareader:
1. Create a command object
2. Call executereader () to create a datareader object
3. Use datareader's read () method to read data row by row
4. Read the data of a column, (type) datareader []
5. Close the datareader object

Using sqlcon as new sqlconnection ("Server = longan-PC \ mssqlserver2008; database = student; uid = sa; Pwd = long ") dim username as string = "Zhang Chen" dim I as integer console. writeline (sqlcon. state. tostring) dim sqlcom as new sqlcommand dim strqry as string = "select * From tblonwork where on_username = @ username" sqlcom. parameters. add (New sqlparameter ("@ username", username) sqlcom. commandtext = strqry sqlcom. connection = sqlcon. open () dim sqlreader as sqldatareader = sqlcom. executereader while (sqlreader. read () console. writeline (sqlreader. getstring (I) I = ++ I end while end using

4. dataadapter object

The dataadapter object is also called a data adapter object. It is mainly used for coordination. It bridges the data sets in the database and memory and sends the edited data in the dataset back to the data source, you can also use the operations specified by the database command object (command) to retrieve data from the data source and send the data to the DataSet object (Dataset ).
The data adapter calls the method fill () when filling in the data set. The statement is as follows:

'Directly fill in the dataadapter1.fill (datatable) or 'fill in the "student" Table dataadapter1.fill (dataset11, "student") in the dataset11 dataset ")

When dataadapter1 calls the fill () method, It retrieves rows from the data source using the SELECT statement specified by the associated command component. Then, add the data in the row to the able object in dataset or directly fill it in the datatable instance. If the datatable object does not exist, the object is automatically created. When you execute the SELECT statement, the connection to the database must be valid, but you do not need to use the statement to open the connection object. If the connection to the database has been closed before the fill () method is called, it is automatically opened to retrieve data, and then it is automatically closed after execution. If the connection object has been opened before the fill () method is called, the connection object remains open after retrieval.
Note: multiple data tables can be placed in one dataset. However, each data adapter can only correspond to one data table.
Common attributes of dataadapter:
Selectcommand:Obtain or set an SQL statement or stored procedure to select records from the data source;
Insertcommand:Obtain or set an SQL statement or stored procedure to insert a new record into the data source;
Updatecommand:Obtain or set an SQL statement or stored procedure to update records in the data source.

Common Methods for dataadapter objects:
Fill:Want to fill in data in dataset;
Update:Returns operations on the dataset to the database.
Note: dbdataadapter has four attributes used to retrieve data from the data source and update data in the data source: The selectcommand attribute returns data in the data source; the insertcommand, updatecommand, and deletecommand attributes are used to manage changes in the data source. You must set the selectcommand Attribute before calling the fill method of dataadapter. Before calling the update method of dataadapter, you must set
The attributes of insertcommand, updatecommand, or deletecommand depend on the changes made to the data in the datatable. For example, if a row has been added, you must set insertcommand before calling update. When update is processing inserted, updated, or deleted rows, dataadapter uses the corresponding command attribute to process the operation. The current information about the modified rows is passed to the command object through the parameters set.

Public Function createsqldataadapter (byval connection as sqlconnection) as sqldataadapter dim adapter as sqldataadapter = new sqldataadapter adapter. missingschemaaction = missingschemaaction. addwithkey' creates the command statement adapter. selectcommand = new sqlcommand ("select customerid, companyName from customers", connection) adapter. insertcommand = new sqlcommand ("insert into MERs (customerid, companyName)" & _ "values (@ customerid, @ companyName)", connection) adapter. updatecommand = new sqlcommand ("Update MERs set customerid = @ customerid, companyName =" & _ "@ companyName where customerid = @ oldcustomerid", connection) adapter. deletecommand = new sqlcommand ("delete from MERs where customerid = @ customerid", connection) 'adds the property adapter. insertcommand. parameters. add ("@ customerid", _ sqldbtype. char, 5, "customerid") adapter. insertcommand. parameters. add ("@ companyName", _ sqldbtype. varchar, 40, "companyName") adapter. updatecommand. parameters. add ("@ customerid", _ sqldbtype. char, 5, "customerid") adapter. updatecommand. parameters. add ("@ companyName", _ sqldbtype. varchar, 40, "companyName") adapter. updatecommand. parameters. add ("@ oldcustomerid", _ sqldbtype. char, 5, "customerid "). sourceversion = _ datarowversion. original adapter. deletecommand. parameters. add ("@ customerid", _ sqldbtype. char, 5, "customerid "). sourceversion = _ datarowversion. original return adapter end Function

V. DataSet object

A DataSet object is also called a DataSet object. A DataSet object is used to indicate the data stored in the memory. It is equivalent to a database in the memory. It can contain multiple able objects and dataview objects. Dataset is mainly used to manage data stored in memory and to disconnect data. Because the DataSet object provides an offline data source, this reduces the burden on the database and the network. When designing a program, the DataSet object can be used as the data source of the program.

Using sqlcon as new sqlconnection ("Server = longan-PC \ mssqlserver2008; database = student; uid = sa; Pwd = long") console. writeline (sqlcon. state. tostring) dim sqlcom as new sqlcommand dim strqry as string = "select * From student_info" sqlcom. commandtext = strqry sqlcom. connection = sqlcon. open () dim sqladapater as new sqldataadapter sqladapater. selectcommand = sqlcom dim da as new dataset sqladapater. fill (DA, "student_info") dim SCB as sqlcommandbuilder = new sqlcommandbuilder (sqladapater) dim Dr as datarow = da. tables ("student_info "). newrow DR (0) = 100 DR (1) = "Zhang ran" Dr (2) = "female" Dr (3) = format (now, "short date") Da. tables ("student_info "). rows. add (DR) sqladapater. update (DA, "student_info") End using