Ado.net Study Notes

Ado. Net Overview 1 ,.. NET data provider. Net Framework provides data providers: SQL client. NET data provider and ole db. NET data provider. the former is designed for communication between databases of SQL Server 7 and later versions, and the latter allows communication with various data storage through OLE DB providers. Regardless of the method used, the code is similar. Ii. Object Classification Overview 1. Connected object I. connection object: the connection to the data source. You can specify the type, location, and other attributes of the data source through different properties of the connection object to establish a connection or disconnect from the database. The connection object acts as a channel, and other objects such as dataadapter and command objects communicate with the database through it. Ii. Command object: queries a database, calls a stored procedure, or directly requests to return specific table content. You can use the command object to perform any query operation on the database. Iii. datareader object: datareder is used to retrieve the fastest data and check the rows returned by the query. You can use the datareader object to check the query results. When one row is queried, the content of the previous row is discarded when it is moved to the next row. Datareader does not support update operations. The data returned by datareader is read-only. Because the datareader object supports the smallest feature set, it is fast and lightweight. Iv. Transaction object: the connection object has a begintransaction method, which can be used to create a transaction object. This object can be used to submit or Cancel Changes to the database during the lifetime of the transaction object. V. Parameter Object: To use a parameterized command object, you can first create a parameter object for each parameter in the query and add them to the paramters set of the command object. The parameter object of ADO. Net exposes some attributes and methods to define the parameter data type and value. Vi. dataadapter object: The dataadapter object serves as a bridge between the disconnected objects in the database and the ADO. Net object model. The dataadapter object fills the tables in the DataSet object and can read the cached changes and submit them to the database. 2. Disconnected object I and able object: datatable object allows you to view data through the set of rows and columns. You can store the query results in the datatable by using the fill method of the dataadapter object. Once the data is read from the database and stored in the datatable object, the connection between the data and the server is disconnected. The datatable class contains a set of other disconnected objects. For example, datarow, datacolumn, and constraints. Ii. datacolumn object: each able has a columns set, which is the container of the datacolumn object. The datacolumn object stores information about the column structure. Iii. constraint object: The constraint object exists in the constraints set of the able object. You can create a constraint object to ensure that the values in one or more columns are unique in the datatable object. Iv. datarow Object: To access the actual values stored in the datatable object, you can use the rows set of the object, which contains a set of datarow objects. Datatable makes all data rows accessible through the collection of datarows. The datarow object is also the starting point of the update. V. dataset objects: DataSet objects can be considered as containers of some able objects. The DataSet object also has some attributes that can be written to a file or memory address, or read from the object. You can only save the content of the DataSet object, save only the structure of the DataSet object, or save both. Vi. datarelation object: The dataset class defines a Realtion attribute, which is a collection of datarelation objects. You can use the datarelation object to indicate the relationship between different able objects in the dataset. The datarelation object also introduces some attributes to enhance the integrity of the reference. VII. dataview object: Once you obtain the query results of the datatable object, you can use the dataview object to view data in different ways. You can use multiple dataview objects to view the same able at the same time. 3. A strong-type DataSet object and a strong-type DataSet object can help simplify the process of establishing a data access application. A strongly typed dataset is a class in Visual Studio. When Visual Studio creates it, it uses its attributes to make all the table and column information valid. The strong DataSet object also provides some custom methods for attributes such as creating new rows in the input. Connection object 1. Features description in the ADO. Net object model, the connection object represents the connection with the database. You can use the properties of the connection object to specify the user creden。 and the location of the data source. II. Introduction 1. Use string strconn = "Data Source = 192.168.1.81; initial catalog = northwind; persist Security info = false; user id = sa; Password = ;"; sqlconnection Cn = new sqlconnection (strconn); CN. open ();............... CN. close (); 2. Use the connection pool by default. If you do not want to use it, add "ole db service =-4" to the ole db connection string "; add "pooling = false" to the SQL connection string; 3. link with other objects I. Create commands string strconn = "……"; Sqlconnection Cn = new sqlconnection (strconn); CN. open (); sqlcommand cmd = cn. createcommand (); II. Start transaction string strconn = "……"; Sqlconnection Cn = new sqlconnection (strconn); CN. open (); sqltransaction txn = cn. begintransaction (); 3. Introduction to attribute method events 1. Attribute ⒈ connectionstring: connection string, which contains the source database name and other parameters required to establish the initial connection. The default value is an empty string. The attribute can only be set when connection is not connected to the data source. when connected to the data source, the attribute is read-only. ⒉ Connectiontimeout: the waiting time (in seconds) for the connection to open ). The default value is 15 seconds. External Database: the name of the current database or the name of the database to be used after the connection is opened. The default value is an empty string. ⒋ Datasource: the name of the SQL server instance to be connected. The default value is an empty string. ⒌ Onstate: a bitwise combination of connectionstate values. The default value is closed. The member name indicates that the connection between the broken and the data source is interrupted. This can only happen after the connection is opened. You can close the connection in this status and then open it again. 16 The closed connection is closed. 0 connecting the connection object is connecting to the data source. 2 executing the connection object is executing the command. 4. the fetching connection object is retrieving data. 8. The open connection is open. 1. ⒍ serverversion: the version of the SQL server instance. The version format is ########. The first two are the main version, the first two are the minor version, and the last four are the release version. The format of this string is major. Minor. Build, where major and minor must be two digits, and build must be four digits. Packet packetsize: The size (in bytes) of the network data packet. The default value is 8192. if the application performs a batch copy operation or sends or receives a large amount of text or image data, data packets larger than the default size can be used for higher efficiency, because it can reduce network read and write operations. If the application sends or receives a small amount of information, you can set the data packet size to 512 bytes, which is sufficient for most data transfer operations. For most applications, it is best to use the default packet size. Packetsize can be a value in the range of 512 bytes and 32767 bytes. If the value exceeds this range, an exception occurs. 2. Method ⒈ intransaction: Start the data transaction changedatabase: change the current database on an open connection closed: Close the Connection closed createcommand: Create a sqlcommand ready open for the current connection: enable connection 3. Event Response infomessage: This event occurs when SQL Server Returns a warning or informative message. An infomessage event is triggered when SQL Server Returns a message with a severity of 10 or less. Messages with a severity of between 11 and 20 may cause an error, and messages with a severity of over 20 may cause the connection to be closed. ⒉ Statechange: occurs when the connection status changes. As long as the state attribute value of the connection object changes, the statechange event of the object will be triggered. Sqldataadapter object 1. Features 1. A set of data commands used to fill the dataset and update the SQL Server database and connect to a database. 2. There is no direct connection between sqldataadapter and dataset. After sqldataadpater. Fill (Dataset) is called, the two objects are not connected. II. Introduction 1. Create sqldataadapter ...... String strsql = "select * from MERs"; sqlcommand cmd = new sqlcommand (strsql, CN); sqldataadapter da = new sqldataadapter (); DA. selectcommand = cmd; 2. sqldataadapter constrconn = "provider = ......"; String strsql = "select * from MERs"; sqldataadapter da = new sqldataadapter (strsql, strconn); ② string strconn = "provider = ......"; Sqlconnection Cn = new sqlconnection (strconn); sqldataadapter da = new sqldataadapter ("select * from MERs", CN); ③ string strconn = "provider = ......"; String strsql = "select * from MERs"; sqlconnection Cn = new sqlconnection (strconn); sqlcommand cmd = new sqlcommand (strsql, CN); sqldataadapter da = new sqldataadapter (CMD ); 3. obtain results from the query ① use the fill method ...... Sqldataadapter da = new sqldataadapter (strsql, strconn); dataset DS = new dataset (); DA. fill (DS); // The table name in DS is table 2. Use the fill method to create a datatable object and a datacolumn object ...... Sqldataadapter da = new sqldataadapter (strsql, strconn); DA. tablemapping. add ("table", "MERs"); dataset DS = new dataset (); DA. fill (DS); ③ use the overloaded fill method sqldataadapter. fill (dataset, "MERs"); sqldataadapter. fill (datatable); sqldataadapter. fill (dataset, intstartrecord, intnumrecords, "tablename"); ④ open and close connections if the fill method of a sqldataadapter object is called, and the connection of the selectcommand attribute is closed, then, the sqldataadapter opens a connection, and then submits queries, retrieves results, Close the connection. If the connection is enabled before the call, the connection remains open after the operation. ...... Sqldataadapter dacustomers, daorders; dacustomers = new sqldataadapter ("select * from MERs", CN); daorders = new sqldataadapter ("select * from orders", CN ); dataset DS = new dataset (); CN. open (); dacustomers. fill (DS); daorders. fill (DS); CN. close (); ⑤ call the fill method multiple times to refresh the data in the dataset. The simplest solution is to clear the dataset (or datatable) and then call the fill method of the dataadapter object again. 3. Introduction to attribute method events 1. Attribute ① acceptchangeduringfill: determines the rowstate of the row obtained by the dataadapter (true by default ). ② Deletecommand: gets or sets a Transact-SQL statement or stored procedure to delete records from the dataset. ③ Insertcommand: gets or sets a Transact-SQL statement or stored procedure to insert a new record into the data source. ④ Selectcommand: gets or sets a Transact-SQL statement or stored procedure to select a record from the data source. ⑤ Updatecommand: gets or sets a Transact-SQL statement or stored procedure to update records in the data source. ⑥ Tablemappings: sqldataadapter is used to map the query results to the dataset information set. 7. continueupdate: controls whether the sqldataadapter can submit changes after an error is encountered (the default value is false ). 2. Method ① fill: Execute the query stored in selectcommand and store the result in datatable. ② Fillschema: obtains schema information for queries stored in selectcommand. Obtain the name and data type of each column in the query. ③ Getfillparameters: obtains an array containing parameters for selectcommand. ④ Update: Submit changes stored in dataset (or datatable or datarows) to the database. This method returns an integer containing the number of rows successfully updated in the data storage. 3. Event ① fillerror: this event is triggered when the dataadapter encounters an error of filling the dataset or datatable. ② Rowupdated: triggered when a modified row is submitted to the database. ③ Rowupdating: A modified row is triggered before it is submitted to the database. DataSet object 1. Features 1. Processing offline data is useful in multi-tier applications. 2. You can view the content of any row in the dataset at any time and modify the query results. 3. Process hierarchical data 4. cache changes 5. xml Integrity: DataSet objects and XML documents are almost interchangeable. II. Introduction 1. Create a DataSet object: DataSet DS = new dataset ("datasetname"); 2. View and call sqldataadapter. the structure da created by fill. fill (DS, "orders"); datatable TBL = Ds. table [0]; foreach (datacolumn Col in TBL. columns) console. writeline (Col. columnname); 3. View data returned by sqldataadapter ①. datarow object datatable TBL = Ds. table [0]; datarow ROW = TBL. row [0]; console. writeline (ROS ["orderid"]); ② check the data datatable TBL = row in datarow. table; foreach (DA Tacolumn Col in TBL. columns) console. writeline (row [col]); ③ check the datarow object foreach (datarow row in TBL. rows) displayrow (ROW); 4. Validate data in dataset ①. Verify the attributes of datacolumn: readonly, allowdbnull, maxlength, unique ②. Set the constrains of the datatable object: uiqueconstraints, primarykey, foreignkeyconstraints generally does not have to deliberately create foreignkeyconstraints, because one is created when two able objects in the dataset are created. ③ Use sqldataadapter. fill mode to retrieve mode information 5. write code to create a able object ①. Create a datatable object: datatable TBL = new datatable ("tablename "); ② Add the able to the table set of the DataSet object dataset DS = new dataset (); datatable TBL = new datatable ("customers"); DS. tables. add (TBL); dataset DS = new dataset (); datatable TBL = Ds. tables. add ("customers"); The datatable object can only exist in at most one DataSet object. To add a able to multiple datasets, you must use the copy or clone method. The copy method creates a new able that has the same structure as the original able and contains the same rows. The clone method creates a new datatable that has the same structure as the original datatable but does not contain any rows. ③ Add the column able TBL = ds for the datatable. tables. add ("orders"); datacolumn Col = TBL. columns. add ("orderid", typeof (INT); Col. allowdbnull = false; Col. maxlength = 5; Col. unique = true; TBL. primarykey = new datacolumn [] {TBL. columns ["customersid"]}; when the primary key is set, allowdbnull is automatically set to false; ④. process the automatic incremental column dataset DS = new dataset (); da Tatable TBL = Ds. tables. add ("orders"); datacolumn Col = TBL. columns. add ("orderid", typeof (INT); Col. autoincrement = true; Col. autoincrementseed =-1; Col. autoincrementstep =-1; Col. readonly = true; ⑤. Add an expression-based column TBL. columns. add ("itemtotal", typeof (decimal), "quantity * unitprice"); 6. Modify the datatable content ①. Add a new datarow ROW = Ds. tables ["MERs"]. newrow (); row ["mermerid"] = "alfki"; DS. tables ["Custom ERS "]. rows. add (ROW); object [] avalues = {"alfki", "Alfreds", "Anders", "030-22222"}; DA. tables ["MERs"]. loaddatarow (avalues, false); ② modifying the content of the current row cannot automatically modify the corresponding content in the database, modifications to rows are considered to be pending changes that will be submitted to the database using the sqldataadapter object. Datarow rowcustomer; rowcustomer = Ds. tables ["custoemrs"]. rows. find ("Anton"); If (rowcustomer = NULL) // The else {rowcustomer ["companyName"] = "newcompanyname" is not found "; rowcustomer ["contactname"] = "newcontactname";} // datarow rowcustomer; rowcustomer = Ds. tables ["custoemrs"]. rows. find ("Anton"); If (rowcustomer = NULL) // no else {rowcustomer found. beginedit (); rowcustomer ["companyName"] = "newcom Panyname "; rowcustomer [" contactname "] =" newcontactname "; rowcustomer. endedit ();} // null indicates that obejct [] acustomer = {null, "newcompanyname", "newcontactname", null} datarow rowcustomer; rowcustomer = DS is not modified for this column. tables ["MERs"]. rows. find ("alfki"); rowcustomer. itemarray = acustomer; ③ process the null value of datarow // check whether it is empty datarow rowcustomer; rowcustomer = Ds. tables ["MERs"]. rows. find ("alfki"); If (rowcustomer. isnul L ("phone") console. writeline ("It's null"); else console. writeline ("it's not null"); // assign the null value rowcustomer ["phone"] = dbnull. value; ④. Delete datarow rowcustomer; rowcustomer = Ds. tables ["MERs"]. rows. find ("alfki"); rowcustomer. delete (); ⑤. Clear datarow rowcustomer = Ds. tables ["MERs"]. rows. find ("alfki"); rowcustomer. itemarray = acustomer; DA. tables ["MERs"]. remove (rowcustomer); or DS. tables ["MERs"]. removeat (intindex); 6. Use datarow. rowstate attributes: unchanged, detached, added, modified, deleted private void demonstraterowstate () {// run a function to create a datatable with one column. datatable mytable = maketable (); datarow myrow; // create a new datarow. myrow = mytable. newrow (); // detached row. console. writeline ("new row" + myrow. rowstate); mytable. rows. add (myrow); // new row. Console. writeline ("addrow" + myrow. rowstate); mytable. acceptchanges (); // unchanged row. console. writeline ("acceptchanges" + myrow. rowstate); myrow ["firstname"] = "Scott"; // modified row. console. writeline ("modified" + myrow. rowstate); myrow. delete (); // deleted row. console. writeline ("deleted" + myrow. rowstate);} 7. Check the pending changes to datarow rowcustomer in datarow; rowcustomer = Ds. tables ["MERs"]. rows. Find ("alfki"); rowcustomer ["companyName"] = "newcompanyname"; string strnewcompanyname, stroldcompanyname; console. writeline (rowcustomer ["companyName", datarowversion. current]); console. writeline (rowcustomer ["companyName", datarowversion. original]); 3. Introduction to attribute method events 1. dataset ①. Attribute casesensitive: used to control whether the string comparison in the datatable is case sensitive. Datasetname: name of the current dataset. If this parameter is not specified, the property value is set to "newdataset". If the dataset content is written to the XML file, datasetname is the root node name of the XML file. DesignMode: If dataset in the component is used during design, designMode returns true, otherwise false. haserrors: indicates whether the datarow object in the dataset contains an error. If you submit a batch of changes to the database and set the continueupdateonerror attribute of the dataadapter object to true, you must check the haserrors attribute of the dataset after submitting the changes to determine whether the update fails. Namespace and prefix: Specify the XML namespace and prefix relations: return a datarelationcollection object. Tables: Check the existing able object. Accessing able through Indexes has better performance. ② Methods acceptchanges and rejectchanges: accept or discard all pending changes in dataset. When acceptchanges is called, The rowstate attribute of all rows whose value is added or modified will be set to unchanged. Any datarow object marked as deleted will be deleted from the dataset. When rejectchanges is called, any datarow object marked as added will be deleted from dataset. Other modified datrow objects will return the previous state. Clear: clears all datarow objects in dataset. This method is faster than releasing a dataset and then creating a new dataset with the same structure. Clone and copy: The copy method creates a new dataset with the same structure and rows as the original dataset. The clone method creates a new dataset with the same structure, but does not contain any rows. Getchanges: returns a new dataset with the same structure as the original DataSet object, and contains all rows in the original dataset that are pending changes. Getxml and getxmlschema: Use the getxml method to obtain the string converted from the dataset content and her schema information to the XML format. If you only want to return schema information, you can use getxmlschema. haschange to indicate whether the dataset contains the datarow object for pending changes. Merge: load data from a set of datarow objects in another dataset, able, or existing dataset. Readxml and writexml: Use the readxml method to load XML data into dataset from a file, textreader, data stream, or xmlreader. Reset: Return dataset to the uninitialized status. If you want to discard the existing dataset and start to process the new dataset, it is better to use the reset method than to create a new dataset instance. ③ Event mergefailed: triggered when an exception occurs in the dataset merge method. 2. datatable ①, attribute ②, Method ③, and event columnchanged: trigger columnchangding after the column content is changed: trigger rowchanged, rowchanging, rowdeleted, and rowdeleting before the column content is changed. 3. datacolumn ①, attribute 4, datarow ①, and attribute haserror: Determine whether the row contains errors. Item: You can specify the number of columns in a row, the column name, or the datacolumn object to access the column content. Itemarray: obtains or sets the values of all columns in the row. Rowerror: returns a string containing row error information. Rowstate: returns the value in the datarowstate enumeration to indicate the current state of the row. Table: return the able. ②, method acceptchanges, and rejectchanges of the datarow object. Submit and discard pending changes. Beginedit, canceledit, and endedit clearerrors: Clear all errors in datarow. Delete: The delete method does not actually Delete the datarow from the row set of the datarow table. when the delete method of the datarow object is called, ADO. net mark the row as deleted, and then call the update method of the sqldataadapter object to delete the corresponding row in the database. If you want to completely delete datarow, you can call the delete method, and then call its acceptechanges method. You can also use the Remove Method of the datarowcollection object to complete the same task.