1. DbCommand ObjectsTo read and write data, one needs valid data links, and the other is the need for DbCommand objects to pass SQL commands to the data source. The DbCommand object contains commands that can be either a DML data manipulation language or a DDL data definition language. The best way to create a dbcommand is to create a dbconnection first, then use the DbConnection CreateCommand () method, so that the DbCommand and DbConnection created most closely match. The two important properties required for DbCommand are CommandText and CommandType, and the sample code is as follows: static void Main () {var constring = Configurationmana Ger. connectionstrings["TestApp.Properties.Settings.toucaiConnectionString"]. ConnectionString; var conn = new Mysqlconnection (constring); var cmd = conn.CreateCommand(); Cmd.CommandType= CommandType.Text; Cmd.CommandText= "SELECT count (*) from product"; Conn. Open (); Long Count =(Long)Cmd. ExecuteScalar (); Console.WriteLine ("Total" + count + "article item record"); Conn. Close (); }2. DbParameter ObjectsIf the value of the CommandType property is Commandtype.procedure, it is mostly necessary to use the DbParameter object, because most SQL procedures require input or output parameters. For example, if there is a process product_name on the server, its code is as follows: CREATE definer= ' lqs2011 ' @ '% ' PROCEDURE ' product_name ' (in Oprice DECIMAL (8, 2)) BEGIN SELECT ProdName, outprice from product WHERE Outprice = oprice and brandid = 1; End can see that this process requires an input parameter, Oprice, to specify the retail price, the sample program is as follows: using (var conn = new Mysqlconnection (constring)) {var CMD = conn. CreateCommand (); Cmd.commandtype = CommandType.StoredProcedure; Cmd.commandtext = "Product_Name"; var param = cmd.CreateParameter(); Param.parametername= "@oPrice"; Param.Value= "398.00"; Cmd.Parameters.Add(param); Conn. Open (); var reader = cmd. ExecuteReader (); while (Reader. Read (){Console.WriteLine ("{0,-15}\t{1,15}", Reader[0], reader[1]); It is important to note that different data providers (Provider) have different requirements for parameters, such as SQL Server requires that the name of the parameter must match the name in the database procedure, so the order is unimportant, and OLE DB requires that the order of the parameters must match the parameters in the database procedure , the parameter name is not important. To meet the requirements of different data providers, our best name and order are consistent with the database process.3. DbCommand's law enforcementCommon command enforcement methods are ExecuteNonQuery (), ExecuteReader (), and ExecuteScalar () three, where:
- ExecuteNonQuery () as the name implies is law enforcement non-query statements, such as the DDL language to create, change or delete database objects, etc., this method does not return data rows, but will return a shaping data to display the number of rows affected.
- The ExecuteReader () method returns a DbDataReader instance, which is a read-only, pointer that exists on the server side. The code in article 2nd has demonstrated the enforcement of this method.
- The ExecuteScalar () method returns the value of the first row column, usually with only one row, which returns a header value rather than a Column object. As in the previous
The Long Count = (long)cmd. ExecuteScalar ()
Cmd. ExecuteScalar () Returns an object that is boxed and becomes a long-shaped value. Using this method can significantly improve performance when you need to return a certain value.
4. DbDataReader ObjectsAs mentioned earlier, DbDataReader is a read-only, server-side pointer that has excellent performance and is often used to populate control data such as ListBox, DropDownList, and so on. You can also use this object to obtain data from the remote server side to display when you run the report. Of course, because the data obtained by this object is read-only, if you need to modify the data and write the modified data back to the remote database, you can no longer use the DbDataReader object, but you should use the DbDataAdapter object. The primary method of DbDataReader is read (), which is used to load remote data into the buffer of this object. The sample code is as follows: using (var conn = new Mysqlconnection (constring)) { &N Bsp var cmd = conn. CreateCommand (); CMD. CommandType = commandtype.text; CMD. CommandText = "Select ProdName, Outprice from product"; Conn. Open (); var reader = cmd. ExecuteReader (); var tbproducts = new DataTable (); &NB Sp Tbproducts.load (reader, Loadoption.upsert); Cmbproducts.datasource = tbproducts; Cmbproducts.displaymember = "ProdName"; Cmbproducts.valuemember = "ProdName"; Note that when loading data using a DataTable, there is a load option, the LoadOption enumeration type, that defines the behavior of local data when inconsistent with remote data, with a total of 3 values, respectively:
- OverwriteChanges: Discard Local changes
- PreserveChanges: Keep local changes
- Upsert: The purpose is to download data remotely, with remote data as the main
5. DbDataAdapter ObjectsThis object is the core object of ADO for data synchronization and is used to synchronize data between local and remote data sources. When fetching data, DbDataAdapter has a SelectCommand property, and the SelectCommand property has a legitimate DbCommand object, and this DbCommand object has a legally available data link. The DbCommand object also has a ExecuteReader method, which is enforced to get a DbDataReader object that populates the DataTable object. DbDataAdapter also has InsertCommand, UpdateCommand, and DeleteCommand properties, which can all contain DbCommand objects, and if you simply read data from a remote data source without making any changes and returning the synchronization, You do not have to create these properties, but if you need to change and return the data, you need to create the above 4 commands (including SelectCommand). DbDataAdapter before the data synchronization will check the data link situation, if the link is not open, automatically open the link, the completion of work automatically close the link, but it is recommended to manually open and close the link, one is good habits, and two in the meantime there are multiple dbdataadapter work, Manually opening links can improve performance.6. Using the DbDataAdapter Fill () methodThis method is used to populate the remote dataset data into a local DataTable object. The example code is as follows: using (var conn = new Mysqlconnection (constring)) {var cmd = conn. CreateCommand (); Cmd.commandtype = CommandType.Text; Cmd.commandtext = "Select ProdName, outprice from product";//Initialize SelectCommandConn. Open (); var da = new Mysqldataadapter (cmd); var ds = new DataSet ();da. Fill (ds, "Product"); Used to populate data in a remote product table into a local datasetCmbproducts.datasource = ds. Tables[0]; Cmbproducts.displaymember = "ProdName"; Programming recommendations, if you need to return the modified data, it is best to create a separate DataAdapter for each DataTable, if you do not need to return data, then use DbDataReader is enough.7. Use the update () method to save changes to a remote data sourceThe update () method saves the process of modifying to a remote data source as follows:
- Find out what's changed from your local DataTable
- Use InsertCommand, UpdateCommand, or DeleteCommand to perform changes in a remote data source
- Execution changes are line-by-row (on a row-byrow basis)
- The update () method determines whether the row is changed by looking at the RowState property of the row, and if the row property is not unchanged, the change is executed
As already mentioned, to modify the writeback data, DbDataAdapter must have both SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand4 commands, there are 3 ways to create these 4 kinds of commands:
- Manually create DbCommand objects (not typically)
- using the DataAdapter Configuration Wizard (DataAdapter Automatically opens when DataAdapter is added to a form)
- uses DbCommandBuilder objects, but requires DataAdapter to have a legally available SelectCommand object
The sample code is as follows: using (var conn = new Mysqlconnection (constring)) { &N Bsp var cmd = conn. CreateCommand (); CMD. CommandType = commandtype.text; CMD. CommandText = "SELECT * from Product"; var da = new Mysqldataadapte R (CMD); var ds = new DataSet (); & nbsp var cmdbuilder = new Mysqlcommandbuilder (DA); & nbsp Conn. Open (); da. Fill (ds, "Product"); var dt = ds. tables[0]; var updateRow = DT. Select ("Innerbar = ' ZZZ002 '") [0]; updaterow["outprice"] = (decimal) 8.0; int count = da. Update (ds, DS. Tables[0]. TableName); if (Count > 0) &NBS P Console.WriteLine ("OK"); } 8. Package Commit changes to remote databaseBy default, the system commits changes to the remote database by setting the value of the UpdateBatchSize property of DataAdapter to 0, which allows the system to package as many rows of data as possible, thereby significantly improving performance.9. DbProviderFactory classSo far, the code we have seen is associated with specific provider, such as the Mysqlconnection object, which is provided by MySQL provider and can only be used to connect to the MySQL database, to implement code that is independent of the specific remote data source type , it needs to be DbProviderFactory class.
Ado. NET learning notes-read and write data
