Ado. NET Common objects

First, Connection object
The connection object is also known as a database connection object, and the functionality of the Connection object is responsible for connecting to the data source. The base class for all connection objects is the DbConnection class.
The connection object has two important properties:
(1) ConnectionString: Represents the string used to open the SQL Server database;
(2) State: Indicates the status of Connection, closed and open two states.
There are two important ways to connection objects:

(1) Open () method: indicates open database;

(2) Close () method: Indicates that the database is closed.

1234567

SqlConnection connection = newSqlConnection( "server=localhost;database=pubs;uid=sa;pwd=‘‘";); //注意，DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")是将当前时间格式化为类似于2008-10-09 00:00:03的形式的字符串 Response.Write("时间"+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")+"当前数据库连接状态是:"+connection.State +"<br/>"); connection.Open(); Response.Write("时间" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当前数据库连接状态是:"+ connection.State + "<br/>"); connection.Close(); Response.Write("时间"+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当前数据库连接状态是:"+ connection.State + "<br/>");

Tips uses the Visual Studio data source control to generate a database connection string. After the wizard creates a new connection, a dialog box with Add Connection appears, where you can change the data source as needed. This connection wizard configuration connects to an Access database file, an ODBC data source, a SQL Server database, a SQL Server Mobile database, a SQL Server database file, an Oracle database file, and other databases. Here we select the Microsoft SQL Server option to connect to the SQL Server database. If we are on a local area network, we can click the Refresh button in the "Add Connection" screen to see which SQL Server databases are available to connect to the LAN. You can connect to a database in the following ways:

(1) If the database server to be connected with the developer's machine in the same LAN, you can use the LAN IP address or the computer host name on the LAN;
(2) If the database server to be connected to the developer's machine is not in the same LAN, then requires the database server must have a public IP, we can use the public IP to connect, if the database server also has an Internet domain name, then the Internet domain name is also possible.
(3) If the database server you are connecting to is the same machine as the one used by the developer, you can use either of the following methods: "(local)" or "." or "127.0.0.1". It is important to note that if you run a different version of the same database on a single machine, such as SQL 2000, SQL 2005, and SQL Express three versions on the "XXXXX" host, the Windows service names they use are " SQL2000 "," SQL2005 "and" SQLExpress ", then we want to connect to the SQL 2000 database used on the server name should be filled in" xxxxx\sql2000 "such as" host name \ Instance name "or" host Ip\ instance name ", This situation is common with friends who have both Visual Studio and SQL installed, because SQL Express is a free version of the student's limited functionality, and this version of the database is installed by default when installing Visual Studio. Click on the "Test Connection" button, if the popup connection is successful, the message indicates that the database connection is available. Click the "OK" button to go back to the "Configure Data Source" screen, then click the "+" button next to the connection string to see the connection string information of the database.

Second, Command object
The command object is also known as a Database command object, which mainly executes commands that include operations to add, delete, modify, and query data. can also be used to execute stored procedures. The CommandType property of the Command object needs to be set to CommandType.StoredProcedure when the stored procedure is executed, by default CommandType The CommandType.Text property is the normal SQL statement that is executed.
There are three main methods of command:
ExecuteNonQuery (): executes an SQL statement that returns the number of rows affected, which is primarily used to perform an add, update, delete operation on the database, noting that this method is not called when querying.

12345678

OleDbConnection conn = newOleDbConnection();conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+            "Data Source="+ Server.MapPath("person.mdb");conn.Open();stringstrSQL = "insert into grade values(12,‘女‘,‘小张‘,78,86,98)";OleDbCommand Comm = newOleDbCommand(strSQL, conn);Comm.ExecuteNonQuery();conn.Close();

ExecuteReader (): executes a query's SQL statement, returning a DataReader object.

12345678

sqlconnection Conn = new  sqlconnection (); conn.connectionstring = "server=localhost;database=pubs;uid=sa;pwd=" " conn.open (); sqlcommand Comm = new  sqlcommand ( "SELECT * from Authors" sqldatareader dr = Comm.executereader (); DG. DataSource = Dr; DG. DataBind (); conn.close ();

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

123456789

OleDbConnection conn = newOleDbConnection();conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+"Data Source="+ Server.MapPath("person.mdb");conn.Open();string strSQL = "select avg(数学) from grade";OleDbCommand Comm = newOleDbCommand(strSQL, conn);doubled = (double)Comm.ExecuteScalar();Message.Text = "所有人数学的平均成绩为："+d.ToString()+"分";conn.Close();

"Note" In operating the database, in order to improve performance, all follow a principle: Database connection objects should be opened as late as possible, close as early. In the above example, the database connection object is opened before the command object needs to perform a database operation, and the database connection object is closed immediately after the database operation is performed.

Third, DataReader object
The DataReader object is a read-only stream of rows that binds data more than it does with data sets because it is read-only, so if you want to modify the data in the database, you need to save the changes to the database by using other methods.

The DataReader object cannot be instantiated directly, and an instance must be created with the associated command object, such as ExecuteReader () with an instance of SqlCommand method to create a SqlDataReader instance. Because the DataReader object needs to remain connected to the database when it reads the data, the close () method should be called immediately after the DataReader object has finished reading the data, and the associated connection object should also be closed. A method is provided in the. NET class library that automatically closes the associated connection object while closing the DataReader object, using this method to specify a parameter for the ExecuteReader () method, such as: SqlDataReader Reader =command. ExecuteReader (commandbehavior.closeconnection); CommandBehavior is an enumeration that uses the CloseConnection value of the CommandBehavior enumeration to close the corresponding SqlConnection object when SqlDataReader is closed.

And there are three ways that DataReader objects Read data:
One is to read the column values in a specified manner by the index of the column at the time of the query, without having to convert, such as getbyte (int i), which reads the value of column I and converts it to a value of type byte. The advantage of this method is to directly read the column directly after specifying the column, no need to convert, the disadvantage is that once the specified column can not be converted in the specified way when the exception is thrown, such as the type of the field in the database is a string type or the value of the field is empty, according to GetByte (i) This method of reading throws an exception.

The second way is to read by the column index, do not perform value conversion when reading, such as: Reader[5] is reading the value of the 5th column (where reader is an instance of a reader object), so that the resulting value is an object type of value, which is also very well understood, Because the database may store various types of values, and object is the base class for all classes, this method does not throw an exception. If you want to get the correct type, you also need to convert it according to the fields in the database.

The last one is to read in the form of a column name and not convert it when reading, resulting in a value of type object. The first three ways are the most direct, but it is possible to throw an exception, the second way is slightly more flexible than the first one, We can do this by reading to a value of NULL (represented by the DBNull class in. NET, which can represent null values of any data type in the database), and we do not make the appropriate type conversions to avoid exceptions. The third method reads the data according to the name of the column, and it needs to be converted in the second way. In terms of performance, the first is the highest, the second is slightly lower, the third is the lowest (this is very well understood, assuming that in a hotel to find someone straight through the room number can be found faster than by name), flexibility is the third most flexible, second, the first is the least flexible (if you later write the SQL statement changes the column index, The first and second are likely to be problematic). Actual development in accordance with the actual situation to choose the right way.

To retrieve data using DataReader:
1. Create Command Object
2. Call ExecuteReader () to create the DataReader object
3. Read the data row by line using the Read () method of DataReader
4, read a column of data, (type) datareader[]
5. Close the DataReader object

Note: DataReader must be closed after use

Reference code:

1234567891011

stringsql = "SELECT StudentName FROM Student               WHERE StudentName LIKE ‘李%‘";SqlCommand command = newSqlCommand(sql, connection);connection.Open();SqlDataReader dataReader = command.ExecuteReader();Console.WriteLine("查询结果：");while(dataReader.Read()){      Console.WriteLine((string)dataReader["StudentName"]);}dataReader.Close();

Iv. DataAdapter Objects
The DataAdapter object is also known as a data adapter object, and the DataAdapter object uses the data source connected by the database connection object (Connection) to retrieve the data from the data source using the operations specified by the database command object to send the data to the DataSet object ( DataSet), or send the edited data in the dataset back to the data source. The data adapter calls the method fill () When the data is populated into the dataset, with the following statement:

Direct Fill Table
Dataadapter1.fill (dataTable);
Or
Populate the "Products" table in the DATASET11 data set
Dataadapter1.fill (dataSet11, "products");

When DataAdapter1 calls the Fill () method, the row is retrieved from the data source using the SELECT statement specified by the command component associated with it. The data in the row is then added to the DataTable object in the DataSet or directly to an instance of the DataTable, and the object is created automatically if the DataTable object does not exist. When you execute the SELECT statement above, the connection to the database must be valid, but you do not need to open the Connection object with a statement. If the connection to the database is closed before the fill () method is called, it is automatically opened to retrieve the data and is automatically closed after execution. If the connection object is open before the fill () method is called, it remains open after retrieval.
Note: Multiple data tables can be placed in a single data set. However, each data adapter can only correspond to a single data table.

V. DataSet object
A DataSet object is also known as a DataSet object, and a DataSet object is used to represent data stored in memory, which is equivalent to an in-memory database. It can include multiple DataTable objects and DataView objects. Datasets are primarily used to manage data stored in memory and to disconnect data. Because the DataSet object provides an offline data source, it relieves the burden of the database and the network, and when designing the program, you can use the DataSet object as the data source for the program.

VI. DataTable Object
A DataTable is a core object in the ADO repository, just like a table in a normal database, it also has rows and columns. It mainly includes DataRow and DataColumn, respectively, representing rows and columns.
(1) Data line (DATAROW)
A data row is a row of data in a given data table, or a record in a data table. It may represent a student, a user, an order, or a piece of data related to a shipment. The method of a DataRow object provides the ability to insert, delete, update, and view data in a table. The statements that fetch rows from the data table are as follows:
DataRow dr = dt. Rows[n];
Where: DataRow represents the data row class; Dr is the data row object; DT represents the data table object; n is the ordinal of the row (ordinal starting from 0).
(2) data column (DataColumn)
A data column (also called a field) in a data table defines the data structure of a table, for example, it can be used to determine the type and size of data in a column, and other properties can be set. For example, determine if the data in a column is read-only, whether it is a primary key, whether null values are allowed, and so on, and the column can be automatically multiplied on the basis of an initial value, and the increment step can be defined by itself. The value of a column needs to be based on the data row. The statements are as follows:

123	stringdc = dr.Columns["字段名"].ToString();或者stringdc = dr.Column[i].ToString();//i表示对应的列索引

If you want to remove the Name field from the 3rd record in the datasheet (DT) and put the value of the field in an input box (TEXTBOX1), the statement can be written as:

12	DataRow dRow = dt.Rows[2 ];   // 从数据表提取行 stringtextBox1.Text=dRow["CompanyName"].ToString();  // 从行中取出字段的值

A basic example

12345678910111213141516171819

//实例化Connection对象 SqlConnection connection = newSqlConnection("Data Source=(local);                           Initial Catalog=数据库名;Persist Security Info=True;User ID=sa;Password=sa"); connection.open();//要执行查询，则先需要实例化Command对象，SqlCommand command = newSqlCommand("select * from UserInfo where sex=0", connection); SqlDataAdapter adapter = new SqlDataAdapter(command); /* 下面的被注释掉的代码与上面的代码是等效的 SqlDataAdapter adapter = new SqlDataAdapter("select * from UserInfo where sex=0",connection); */DataTable data = newDataTable();         adapter.Fill(data); /* 下面的被注释掉语句与上面填充DataTable的效果是一样的，我更倾向于没有注释掉的部分 DataSet ds = new DataSet();//实例化DataSet adapter.Fill(ds, "UserInfo");//填充ds中的"UserInfo"表 DataTable data = ds.Tables["UserInfo"]; */connection.close();

Reference: http://www.cnblogs.com/aito/archive/2010/08/25/1808471.html

Ado. NET Common objects