Learn ADO.NET

Normalization of data: It is the number of times the data is repeated as much as possible to multiple tables as much as possible to minimize the same data. ADO.NET's main object elements: Data Source: Usually refers to a relational database, such as SQL Server, etc., a data supply: providing data warehouse communication, such as ODBC, etc. Connection object: establish a page program and database-driven communication pipe Command object: a tool DataReader / DataSet object containing read and write data instructions: A place that is read or written to data .NET control: Main refers to Connection object: Mainly used to connect data sources through open () Method Open the connection connection string in the connection string contains information about each portion: >> The first portion specifies the type of supply or driver to be used // server = localhost >> The second part specifies the database //// Database = MyDatabase >> Part III usually contains security information, including username, password, etc. // uid = foolboy; pwd = mypasswd command object and DataReader: read and modify data Command usage: objcommand = new Oledbcommand (strsql, objconnection) ObjdbDataReader = Objcommand.executeRead (); DataRead: 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 FAQ: >> Code contains References to the NULL of the ADO.NET objects >> Code requests are NULL does not exist >> Code connection string errors >> References containing unsuccessful columns or tables >> Nothing to provide the correct UserID and Password >> Code is the use of syntax incorrect SQL statement >> Network problem caused database connection problem processing method: Using TRY .... Catch capture error information Update data method: >> 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 covered? DataSet & DataTable & DataRow relations are as follows: ---------------------------------- ---- | DataSet | | | ------------------------- | | | | | | | | ---------------- | | | | | | ---------------- | | | | | | DataRow | | | | | | ------------------------------------- - | | | ------------------------------------ | DATATABLE = Dataset.tables ["TNAME "]; DATAROW = DATATABLE.ROWS; STRING STRFIRSTNAME = DATAROW [0] [" firstname "]; here is the realization method for Data set Dataset, not involving update update operations for data sources: 1 Add Record (Add Row) Add a record First, you need to declare the two variables DataTable, DataRow where the DataTable needs to instantiate a Table DataRow = DataTable.newrow () declared to Table to DataRow again to assign DataRow. Call DataTable.Rows.Add (DataRow) 2. Modify the record (edit line) First declare a variable DATAROW [] ObjRows to store the row to edit Objrows = DataTable.select ("Query Conditions); if it is a line Can be like this Objrows = DataTable.Rows [3]; then modify it, such as objrows [0] [field1] = "" Objrows [0] [Field2] = "

3. Deleting records as follows. DataTable.Rows [5] .delete (); Instructible: It should be, first stated that a variable DATAROW [] ObjRows is used to store the row Objrows = DataTable.select ("Query Conditions);

Update the data source method 1, Command object update required Properties: Connection Contains Detail of the Data Warehouse Connection CommandText To Run the Type SQL Character or Store Name of the SQL Character or Store SQL TableDirect Indicates Table Name StoredProcedure Represents Storage Process Name Parameters Parameters object A collection 2, DataAPter 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 process the query, add, delete, and therefore a modification of a four DataAdapter Command object attribute stores four kinds of attributes are as follows SelectCommand, UpdateCommand, InsertCommand, DeleteCommand 3, CommandBuilder objects OleDbCommandBuilder objBuilder objBuilder = new OleDbCommandBuilder ( DataAdapter) indicates to the command generator may take SelectCommand to where to establish other commands DataAdapter.UpdateCommand = objBuilder.GetUpdateCommand ();. DataAdapter.InsertCommand = objBuilder.GetInsertCommand (); DataAdapter.DeleteCommand = objBuilder.GetDeleteCommand (); Note that in this case, SelectCommand must have a primary key field 4. DataAdapter.Update () DataAdapter.Update (for example, the following code ensures that the deleted row in the table is processed, and then process updated The row is then handled inserted. [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., update the data warehouse work. [2003-05-28] 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 The data read in the middle does not have any difference, or any of the previous operations, ultimately as long as the result set Dataset is written to the XML or database can be from: 9CBS