Database operation class

1, Command Object Update Required Properties: Connection Contains Detail of Data Warehouse Connection CommandText To Run the Type SQL Character or Store The name of the SQL Character or Store Procedure SQL TableDirect indicates that the table name StoredProcedure indicates the name of the stored procedure Parameters Parameters A collection of objects

2, DataAdapter object Note DataAdapter and Command's difference? >> Command is mainly used to run commands >> DataAPter is mainly used to provide a storage space for multiple commands, providing bidirectional interactions between data warehouses and DataSets. Oh, a Command object can only handle queries, add, delete, and modify one of the DataAdapter store four Command object properties as follows SelectCommand, UpdateCommand, INSERTCOMMAND, DeleteCommand

3, CommandBuilder objects OleDbCommandBuilder objBuilder objBuilder = new OleDbCommandBuilder (DataAdapter) indicates to the command generator may get to where SelectCommand, to create another command DataAdapter.UpdateCommand = objBuilder.GetUpdateCommand ();. DataAdapter.InsertCommand = objBuilder.GetInsertCommand () DataAdapter.deleteCommand = objbuilder.getDeleteCommand (); Note In this case, selectcommand must have a primary key field.

4. DataAdapter.Update () DataAdapter.Update (DataSet, "TablesName"); for example, the following code ensures that the deleted row in the table is processed, and then process the updated row and process the inserted row. [C #] DataTable Updtable = Custds.tables ["Customers"];

// first process deletes. Custda.Update (Updtable.select (Null, Null, DataViewRowState.deleted);

// next process updates. Custda.Update (Updtable.select (Null, Null, DataViewrowState.ModifiedCurrent);

// Finally, process inserts custDA.Update (updTable.Select (null, null, DataViewRowState.Added));. DataViewRowState view in which the operation attribute comprising Deleted, ModifiedCurrent, Added, Unchanged, etc. Thus, the update data warehouse complete. Using the stored procedure stored procedure is similar to a function in the code, it is stored on the data server and has a name. Why use stored procedures? 1. The huge complex SQL statement affects the program code reading 2. The stored procedure processed by the database server is faster and more efficient than the SQL statement directly, it is necessary to note that the CommandType is set to StoredProccess CommandText for the stored procedure Name EG: Objcmd.commandtext = "[Sales By Category]"; Objcmd.commandType = CommandType.StoreProcedure; Using XML Due to the ADO.NET design, it considers XML, which processing XML data is like these data comes from a database.

1. Write XML file objadapter.fill (objDataSet, "EMPLOYEES"); // Fill result set ObjdataSet.writeXML (Server.Mappath ("Employees.xml")); // Write to XML file Note Two points: 1 First, I first use the DataSet WriteXML () method, extract information from the DataSet and format XML 2, and server.mAppath () represents the generated file path, pointing to the current application directory 2, read the XML file ObjDataSet.Readxml (Server.) Mappath ("Employees.xml")); 3, convert XML to string strXml, strschema strxml = objDataSet.getxml () strschema = objDataSet.Getxmlschema () 4, once XML read into DataSet He is also from database There is no difference in data read in, or any operations can be performed, and ultimately, as long as the result set Dataset is written to the XML or database, it can be ADO.NET's main object elements: data source: usually refers to a relationship Database, such as SQLServer, etc., the function of providing data warehouse communication, such as ODBC, etc. Connection object: Create a page program and database-driven communication pipe Command object: a tool containing the read and write data instructions DataReader / DataSet object: Storage Local. NET controls for data .NET control: Main refers to Connection Object: Connect the data source to open the connection connection string in the connection string through the Open () method contains information about the portions of each part : >> The first part specifies the provision of the supplier or driver to use // server = localhost >> The second part specifies the database // Database = MyDatabase >> Part III usually contains security information, including the username, Password, etc. // uid = foolboy; pwd = mypasswd command object and DataReader: read and modify data Command usage: objcommand = new oledbcommand (strsql, objconnection); objdbdataareader = Objcommand.exe CuteReader (); DataReader: Storage Data Reading Method DataReader ["Field"] Data Binding: It is a process of creating a connection in the data source and data users. Mainly refers to the limitations that bind to DataReader on DataGrid: >> You can only read data, you cannot modify data >> You can only process the data "DataSet can only handle a table DataSet is his alternative or DataTable? ? Their main difference? DataSet and DataTable objects DataSet represent data in the database, different from DataReader it can store several tables and the relationship between them. In use, it is mainly used to use the following 4 objects: >> DataTable: Indicates the table itself >> DataSet: core object, establish an Adhoc relationship between multi-tables, you can associate a line in a table and another table> > DataAdapter: The result is transmitted to the DataSet from the Connection.

The Fill () method copies the data to the DataSet, and the update () method uses the data in the DataSet back to the data source. >> DataView: Specific views of DataTables in DataSet >> DataGrid:, etc., DataSource, eventually bound to a specific DataView on ADO.NET About SQL Server SQLConnection SQLCommand SqlDataAdapter data exception handling

Frequently Asked Questions: >> Code contains references to the ADO.NET objects that do not exist >> Code request data for NULL does not exist >> Connection string errors >> Contains non-existing columns or tables >> Nothing provided The correct userid and password >> Code is the use of syntax incorrect SQL statement >> Network problem caused database connection problem processing method: use try .... catch capture error message

Update data method

Question: >> How to update? Our modifications are based on disconnect, if the modified result is written to the database? >> How to handle synchronous updates? What should I do if the two have updated the same data? The result will be overwritten?

DataSet & DataTable & DataRow relations are as follows: -------------------------------------- | Dataset | | | ------------------------- | | | | | | | | | | | -------- ---------- | | | | | | | ------------------ | | | | | | | | | | ---------------- | | | | ------------------------- | | ----------------------------------- | DataTable = dataset.tables ["tname"]; DATAROW = DATATABLE .Row; string strfirstname = DATAROW [0] ["firstname"];