Sql_server four kinds of execution ExecuteReader, ExecuteNonQuery, ExecuteScalar, Dataset.docx

C # data query output

2012-07-17 17:07

1. Using ExecuteReader () to operate the database 2. Using ExecuteNonQuery () to operate the database 3. Using ExecuteScalar () to operate the database 4. Insert records and update data using DataSet DataSet. 1, the use of ExecuteReader () operation of the database, to perform the query operation of the weapon ExecuteReader compared to datasets, DataReader has faster access and can use fewer server resources. The DataReader object provides a read method in the form of a cursor, and when a row is read from the result, the cursor continues to read to the next line. The Read method can be used to determine if the data has the next row, and if there is data, continue to run and return true, otherwise false. DataReader can improve execution efficiency, and ordinal-based queries can use DataReader. When you use ExecuteReader () to manipulate the database, you encounter a situation where you know the name of a column without knowing the number of a column. This situation can be obtained by using the GetOrdinal () method of the DataReader object to obtain the corresponding column number. This method receives a column name and returns the column number that contains the column name. Example code: String str = "server= ' (local) ';d atabase= ' mytable '; uid= ' sa ';p wd= ' sa '"; Create a connection string SqlConnection con = new SqlConnection (str); Create a Connection object Con. Open (); Open connection String strSQL = "SELECT * from Mynews where id=1 the ORDER by id DESC"; To create an Execute SQL statement SqlCommand cmd = new SqlCommand (strSQL, con); To create a Command object SqlDataReader rd = cmd. ExecuteReader (); Create a DataReader Object int id = Rd. GetOrdinal ("title"); To get the column number of the title column using the GetOrdinal method while (Rd. Read ())//Traverse DataReader Object { Label1.Text = "News ID is" + rd["id"]; The value of the output object } Using the ExecuteReader () operations database, typically using ExecuteReader () for database query operations, querying the database using ExecuteReader () can improve query efficiency, and if database transactions are required, The ExecuteReader () method is not an ideal choice. 2, the use of ExecuteNonQuery (), ExecuteScalar () operation of the database, to perform additions and deletions to operate the weapon ExecuteNonQuery () is typically preferred for database transactions, when an INSERT, delete, update, and other operation is required, the preferred ExecuteNonQuery (), ExecuteNonQuery () execution succeeds is to return an affected number of rows, for " CREATE table and DROP table statements, the return value is 0, and for all other types of statements, the return value is-1. ExecuteNonQuery () When manipulating data, you can directly change the data in the database without using a dataset. The ExecuteScalar () method is also used to execute the SQL statement, but the return value after ExecuteScalar () executes the SQL statement is not the same as ExecuteNonQuery (), ExecuteScalar () The data type of the return value of the method is the object type. If the executed SQL statement is a query statement (SELECT), the result is the first column of the first row after the query, and if the executed SQL statement is not a query statement, an object that is not instantiated is returned and must be displayed by type conversion. Typically, a value is returned after the ExecuteNonQuery () operation, and an object is returned after the ExecuteScalar () operation, and ExecuteScalar () is often used when a single value needs to be returned. For example, when inserting a piece of data, you often need to know immediately what value you just inserted, you can use the ExecuteScalar () method. Example code: String str = "server= ' (local) ';d atabase= ' mytable '; uid= ' sa ';p wd= ' sa '"; Create a connection string SqlConnection con = new SqlConnection (str); Create a Connection object Con. Open (); Open connection String strSQL = "INSERT into mynews values (' What is the ID just inserted? ') SELECT @ @IDENTITY as ' bh '; Insert statement SqlCommand cmd = new SqlCommand (strSQL, con); EXECUTE statement Label1.Text = "The ID of the row you just inserted is" + cmd. ExecuteScalar (); return assignment The code above uses the SELECT @ @IDENTITY syntax to get the ID value just after the update was performed, and then gets the value of the first column of the first row after the update by using the ExecuteScalar () method. 4. Insert records and update data using DataSet DataSet. Insert statements enable data insertions, and data insertions can be done using DataSet objects. In order to populate the data in the database into a dataset, you must first use the method of the DataAdapter object to implement the population, and when the data is populated, the developer can add records to the DataSet object and then use the Update method to insert the records into the database. The steps to update a record using a dataset are as follows: Q Create a Connection object. Q Create a DataAdapter object. Q Initialize the adapter. Q Use the Fill method of the data adapter to execute the SELECT command and populate the dataset. Q Creates a new row using the NewRow method provided by the DataTable object. Q Sets the field of the data row to the inserted value. Q Use the Add method of the Datarowadd class to add data rows to the data table. Q Set the InsertCommand property of the DataAdapter class to the INSERT statement where the record needs to be inserted. Q Use the Update method provided by the data adapter to insert new records into the database. Q Use the AcceptChanges method provided by the DataSet class to keep the database consistent with the in-memory data. Insert Sample code: String str = "server= ' (local) ';d atabase= ' mytable '; uid= ' sa ';p wd= ' sa '"; Create a connection string SqlConnection con = new SqlConnection (str); Create a Connection object Con. Open (); Open connection String strSQL = "SELECT * from Mynews"; Writing SQL statements SqlDataAdapter da = new SqlDataAdapter (strSQL, con); Create an Adapter SqlCommandBuilder build = New SqlCommandBuilder (DA); Constructing SQL statements DataSet ds = new DataSet (); Create a data set Da. Fill (ds, "DataTable"); Populating data sets DataTable TB = ds. Tables["DataTable"]; Create a table Tb. PrimaryKey = new datacolumn[] {TB. columns["id"}; Create a primary key for a table DataRow row = ds. Tables["DataTable"]. NewRow (); Create a DataRow row["title"] = "Insert new row with DataSet"; Assigning new columns row["id"] = "15"; Ds. Tables["DataTable"]. Rows.Add (row); Da. Update (ds, "DataTable"); Update Sample code: String str = "server= ' (local) ';d atabase= ' mytable '; uid= ' sa ';p wd= ' sa '"; Create a connection string SqlConnection con = new SqlConnection (str); Create a Connection object Con. Open (); Open connection String strSQL = "SELECT * from Mynews"; Writing SQL statements SqlDataAdapter da = new SqlDataAdapter (strSQL, con); Create an Adapter SqlCommandBuilder build = New SqlCommandBuilder (DA); Constructing SQL statements DataSet ds = new DataSet (); Create a data set Da. Fill (ds, "DataTable"); Populating data sets DataTable TB = ds. Tables["DataTable"]; Create a table Tb. PrimaryKey = new datacolumn[] {TB. columns["id"}; Create a primary key for a table DataRow row = tb. Rows.find (1); row["title"] = "new title"; Da. Update (ds, "DataTable");

Sql_server four kinds of execution ExecuteReader, ExecuteNonQuery, ExecuteScalar, Dataset.docx