Update Data using Windows Form and . NET Data Sets. Contents.
The ADO. NET Object Model. Database Updates from.
In this article we will learn how to perform CRUD operations in an ASP.NET MVC application. //新增记录,推荐使用,光标位置处于当前新增记录,且正处理编辑状态 DataRow thisRow = ((DataRowView)usersBindingSource.AddNew()).Row;. Computer Courses. Home; Beginners Computing; Word 2007 to 2013; Word 2000 to 2003; Excel 2007 to 2013; Excel to 2003; Excel VBA Programming for Beginners; Web Design.
Datasets. 3. Example. More Information on installing the . Net Framework. click here. Download full Visual Studio C#. NET Example from this Article. 1. Overview. The ADO. NET object model is rich, but at its heart.
The most important of these is the. Data. Set. The Data. Set represents a subset of the entire database, cached on your machine. Periodically, you'll reconnect the Data.
Set to its. parent database, update the database with changes you've made to the Data. Set, and update. the Data. Set with changes in the database made by other processes. This is highly efficient, but to be effective the. Data. Set must be a robust subset of the database, capturing not just a few rows from a.
Java2s.com | Email:info at java2s.com | © Demo Source and Support. All rights reserved.
This is, not surprisingly, what. ADO. NET provides.
The Data. Set is composed of Data. Table. objects as well as Data. Relation objects. These are accessed as properties of the Data. Set. object. The Tables property returns a Data.
Table. Collection, which in turn contains all the. Data. Table objects. Data. Tables and Data. Columns. The Data.
Table can be created programmatically or as. The Data. Table has a number of public. Columns collection, which returns the Data. Column. Collection. Data. Column objects. Each Data. Column object represents a. Data. Relations. In addition to the Tables collection, the Data.
Set. has a Relations property, which returns a Data. Relation. Collection consisting of.
Data. Relation objects. Each Data. Relation represents a relationship between two tables. Data. Column objects. For example, in the SQL Server 2. Northwind database the. Customers table is in a relationship with the Orders table through the Customer. ID. column. The nature of the relationship is one- to- many, or.
For any given order, there will be exactly one customer, but any given. Rows. Data. Table's Rows collection returns a set of rows. Use this collection to examine the results of queries against the. Programmers. experienced with ADO are often confused by the absence of the Record. Set with its move. Next. and move. Previous commands.
With ADO. NET, you do not iterate through the Data. Set; instead. access the table you need, and then you can iterate through the Rows collection. You'll see this in the first example in this. Data Adapter. The Data. Set is an abstraction of a relational. ADO. NET uses a Data. Adapter as a bridge between the Data.
Set and the data source. Data. Adapter provides the Fill( ) method to retrieve. Data. Set. The DBConnection object represents a connection to a. This connection can be shared among different command objects. The DBCommand. object allows you to send a command (typically a SQL statement or a stored procedure) to.
Often these objects are implicitly created when you create your Data. Set. but you can explicitly access these objects.
Rather than tie the Data. Set object too closely to. ADO. NET uses a Data. Adapter object to mediate between the. Data. Set object and the database.
This decouples the Data. Set from the database and allows. Data. Set to represent more than one database or other data source. Datasets Datasets are a fundamental part of the ADO. NET. architecture, providing both high- performance data access as well as scalability. Datasets store data in a disconnected cache.
The structure of a dataset is similar. In addition, it contains constraints and relationships defined for the. Two Step Process. Updating a data source via a dataset is a. The first step is to update the dataset with new.
If you are updating a data source (such as a. That is, the process of updating the dataset does not also write the changes. After changes have been made in a dataset, you can. Most commonly, you do this by calling the. Update method of a data adapter. The method loops through each record in a data. How an Update Is Transmitted to the Data.
Source. As an illustration of how updates are made, suppose. The application fetches. After the retrieval, the in- memory data table looks like. Row. State). First. Name Last.
Name. (Unchanged). Nancy. Buchanan (Unchanged). James. Wilson. Your application changes "James" to "Jim". As a. result of this change, the value of the Data. Row. Row. State property for that row changes. Unchanged to Modified.
The value of the Row. State property for the.
Unchanged. The data table now looks like this: (Row. State). First. Name Last. Name. (Unchanged). Nancy. Buchanan(Modified). Jim. Wilson. You application now calls the Update method to.
The method inspects each row in turn. For the first. row, the method transmits no SQL statement to the database, because that row has not. In a typical implementation, you might work your way through the.
Fill the tables for your Data. Set using a stored procedure or. SQL. Display the data in various Data.
Table objects within your. Data. Set by either binding to a control or looping through the rows in the tables. Change data in individual Data. Table objects by adding. Data. Row objects. Invoke the Get. Changes( ) method to create a second Data. Set that. features only the changes to the data.
Check for errors in the second newly created Data. Set by. examining the Has.
Errors property. If there are errors, check the Has. Errors property of. Data. Table in the Data. Set. If the table has errors, invoke the Get. Errors( ) method. Data. Table and get back an array of Data. Row objects with errors.
On each row you. can examine the Row. Error property for specific information about the error, which you. Merge the second Data Set with the first. Call the Update( ) method on the Data.
Adapter object and pass in. Data. Set. Invoke the Accept. Changes( ) method on the Data. Set, or invoke. Reject.
Changes( ) to cancel the changes. This process gives you very fine control over the update to your.
In the following example, we will create a dialog box that displays. Customer table in Northwinds. The goal is to test updating a record. The code is as simple as possible, which. This form consists of a list box, a button for Update, an. Delete button. There is also a set of eight text fields that.
New button. These text fields represent eight of the. Customers table in the Northwind database. There is also a label at the. Accessing the Data. First, create the Data. Adapter object and the Data. Set. as private member variables, along with the Data.
Table. A Data. Adapter may have four SQL. Right now, we have only one: data. Adapter. Select. Command.
The. Initialize. Commands( ) method creates the remaining three: Insert. Command, Update. Command.
Delete. Command. Initialize. Commands( ) uses the Add. Parms method to associate a column. SQL command with the columns in the modified rows: // The Data. Set. Data. Adapter, and Data.
Table are members// so that we can access them from any member method. Sql. Data. Adapter _data. Adapter; private Data. Set _data. Set; private Data. Table _data. Table; // **** Constructorpublic Cust. Trans(){// Create the visual Controls.
Initialize. Component(); // Setup DB Connection **** ADJUST IT FOR YOUR. USE ****string connection. String = "server=xeon; " +"uid=sa; pwd=xxxxxx. String = "Select * from Customers"; _data. Adapter = new Sql. Data. Adapter(command.
String. connection. String); // Create INSERT, UPDATE, DELETE "Template". Commands. Initialize. Commands(); // Populate Data. Set_data. Set = new Data. Set(); _data. Adapter.
Fill(_data. Set,"Customers"); // Fill the list box with columns from the. Datasetthis. Populate.
List. Box(); }Initialize. Commands( ) creates each SQL command in. Add. Parm( ). The. Data. Adapter uses these three commands to modify the table when you invoke Update(. Bind parameters to. Note: The params keyword lets you specify a method// parameter that takes an argument where the number// of arguments is variable. Add. Parms(Sql. Command cmd, params string[] cols){ // Add each parameter.
Note that each colum. Customers" is of type VARCHAR ! String column in cols){cmd. Parameters. Add("@" + column.
Sql. Db. Type. Char, 0, column); }}// *** Setup Data. Adapter. with expicit INSERT, UPDATE,// DELETE "Template" Commands.
Initialize. Commands(){// Reuse the Select. Command's. Connection.
Sql. Connection con =(Sql. Connection). _data. Adapter. Select. Command.
Connection; // Create an explicit, reusable INSERT. Adapter. Insert. Command = con. Create. Command(); _data. Adapter. Insert. Command. Command. Text ="INSERT INTO customers " +"(Customer.
Id, Company. Name, Contact. Name. Contact. Title, " +"Address, City, Postal. Code, Phone) " +"VALUES(@Customer.
Id, @Company. Name.
Populating a Data. Set from a Data. Adapter. The ADO. NETData. Set is a memory- resident representation of data that provides a consistent relational programming model independent of the data source.
The Data. Set represents a complete set of data that includes tables, constraints, and relationships among the tables. Because the Data. Set is independent of the data source, a Data.
Set can include data local to the application, and data from multiple data sources. Interaction with existing data sources is controlled through the Data.
Adapter. The Select. Command property of the Data. Adapter is a Command object that retrieves data from the data source. The Insert. Command, Update. Command, and Delete. Command properties of the Data.
Adapter are Command objects that manage updates to the data in the data source according to modifications made to the data in the Data. Set. These properties are covered in more detail in Updating Data Sources with Data. Adapters. The Fill method of the Data.
Adapter is used to populate a Data. Set with the results of the Select. Command of the Data. Adapter. Fill takes as its arguments a Data. Set to be populated, and a Data. Table object, or the name of the Data. Table to be filled with the rows returned from the Select.
Command. Note Using the Data. Adapter to retrieve all of a table takes time, especially if there are many rows in the table. This is because accessing the database, locating and processing the data, and then transferring the data to the client is time- consuming. Pulling all of the table to the client also locks all of the rows on the server.
To improve performance, you can use the WHERE clause to greatly reduce the number of rows returned to the client. You can also reduce the amount of data returned to the client by only explicitly listing required columns in the SELECT statement. Another good workaround is to retrieve the rows in batches (such as several hundred rows at a time) and only retrieve the next batch when the client is finished with the current batch. The Fill method uses the Data. Reader object implicitly to return the column names and types that are used to create the tables in the Data. Set, and the data to populate the rows of the tables in the Data. Set. Tables and columns are only created if they do not already exist; otherwise Fill uses the existing Data.
Set schema. Column types are created as . NET Framework types according to the tables in Data Type Mappings in ADO. NET. Primary keys are not created unless they exist in the data source and Data. Adapter. Missing. Schema. Action is set to Missing.
Schema. Action. Add. With. Key. If Fill finds that a primary key exists for a table, it will overwrite data in the Data. Set with data from the data source for rows where the primary key column values match those of the row returned from the data source. If no primary key is found, the data is appended to the tables in the Data. Set. Fill uses any mappings that may exist when you populate the Data.
Set (see Data. Adapter Data. Table and Data. Column Mappings). Note If the Select.
Command returns the results of an OUTER JOIN, the Data. Adapter does not set a Primary. Key value for the resulting Data. Table. You must define the Primary. Key yourself to make sure that duplicate rows are resolved correctly. For more information, see Defining Primary Keys.
The following code example creates an instance of a Sql. Data. Adapter that uses a Sql.
Connection to the Microsoft SQL Server Northwind database and populates a Data. Table in a Data. Set with the list of customers. The SQL statement and Sql. Connection arguments passed to the Sql. Data. Adapter constructor are used to create the Select.
Command property of the Sql. Data. Adapter. Example' Assumes that connection is a valid Sql.
Connection object. Dim query. String As String = _. SELECT Customer. ID, Company. Name FROM dbo. Customers".
Dim adapter As Sql. Data. Adapter = New Sql.
Data. Adapter( _. String, connection). Dim customers As Data.
Set = New Data. Set. Fill(customers, "Customers"). Assumes that connection is a valid Sql. Connection object. String =. "SELECT Customer. ID, Company. Name FROM dbo.
Customers". Sql. Data. Adapter adapter = new Sql. Data. Adapter(query. String, connection). Data. Set customers = new Data. Set(). adapter. Fill(customers, "Customers").
Note The code shown in this example does not explicitly open and close the Connection. The Fill method implicitly opens the Connection that the Data. Adapter is using if it finds that the connection is not already open. If Fill opened the connection, it also closes the connection when Fill is finished. This can simplify your code when you deal with a single operation such as a Fill or an Update.
However, if you are performing multiple operations that require an open connection, you can improve the performance of your application by explicitly calling the Open method of the Connection, performing the operations against the data source, and then calling the Close method of the Connection. You should try to keep connections to the data source open as briefly as possible to free resources for use by other client applications. Multiple Result Sets If the Data. Adapter encounters multiple result sets, it creates multiple tables in the Data.
Set. The tables are given an incremental default name of Table. N, starting with "Table" for Table. If a table name is passed as an argument to the Fill method, the tables are given an incremental default name of Table. Name. N, starting with "Table. Name" for Table. Name. Populating a Data.
Set from Multiple Data. Adapters Any number of Data.
Adapter objects can be used with a Data. Set. Each Data. Adapter can be used to fill one or more Data. Table objects and resolve updates back to the relevant data source. Data. Relation and Constraint objects can be added to the Data. Set locally, which enables you to relate data from dissimilar data sources. For example, a Data.
Set can contain data from a Microsoft SQL Server database, an IBM DB2 database exposed through OLE DB, and a data source that streams XML. One or more Data. Adapter objects can handle communication to each data source. Example The following code example populates a list of customers from the Northwind database on Microsoft SQL Server, and a list of orders from the Northwind database stored in Microsoft Access 2. The filled tables are related with a Data. Relation, and the list of customers is then displayed with the orders for that customer. For more information about Data.
Relation objects, see Adding Data. Relations and Navigating Data. Relations. ' Assumes that customer. Connection is a valid Sql. Connection object. Assumes that order.
Connection is a valid Ole. Db. Connection object. Dim cust. Adapter As Sql. Data. Adapter = New Sql.
Data. Adapter( _. SELECT * FROM dbo. Customers", customer. Connection). Dim ord. Adapter As Ole. Db. Data. Adapter = New Ole. Db. Data. Adapter( _.
SELECT * FROM Orders", order. Connection). Dim customer. Orders As Data. Set = New Data. Set(). cust. Adapter.
Fill(customer. Orders, "Customers"). Adapter. Fill(customer.
Orders, "Orders"). Dim relation As Data. Relation = _. customer. Orders. Relations. Add("Cust. Orders", _. Orders. Tables("Customers").
Columns("Customer. ID"), _. customer. Orders. Tables("Orders"). Columns("Customer. ID")). Dim p. Row, c.
Row As Data. Row. For Each p. Row In customer. Orders. Tables("Customers").
Rows. Console. Write. Line(p. Row("Customer. ID"). To. String()). For Each c. Row In p.
Row. Get. Child. Rows(relation). Console. Write. Line(vb.
Tab & c. Row("Order. ID"). To. String()). Assumes that customer.
Connection is a valid Sql. Connection object. Assumes that order. Connection is a valid Ole.
Db. Connection object. Sql. Data. Adapter cust. Adapter = new Sql. Data. Adapter(. "SELECT * FROM dbo. Customers", customer.
Connection). Ole. Db. Data. Adapter ord.
Adapter = new Ole. Db. Data. Adapter(. SELECT * FROM Orders", order. Connection). Data. Set customer. Orders = new Data. Set(). cust. Adapter.
Orders, "Orders"). Data. Relation relation = customer. Orders. Relations. Add("Cust. Orders". Orders. Tables["Customers"]. Columns["Customer. ID"]. customer. Orders.
Tables["Orders"]. Columns["Customer. ID"]). foreach (Data.
Row p. Row in customer. Orders. Tables["Customers"]. Rows). Console. Write. Line(p. Row["Customer.
ID"]). foreach (Data. Row c. Row in p. Row.
Get. Child. Rows(relation)). Console. Write. Line("\t" + c.
Row["Order. ID"]). SQL Server Decimal Type By default, the Data. Set stores data by using .
NET Framework data types.