To complete this drill, you need:
Access servers with a Pubs SQL Server sample database.
The drill is divided into several smaller parts:
Create a Windows Form. Create and configure a data set that will be bound to it. This includes creating a query that fills the dataset through the database. Add a DataGrid control to the form and bind it to the data. Add code to populate the data set. Add the code that changes the dataset to the database back to the database.
Create projects and forms
The first step is to create a Windows form.
Create projects and forms
If there is already an open solution, select Close the Solution from the File menu.
Note In the finished project, you often contain multiple items in the same solution. But in this walkthrough, you will close any open solutions and create a new solution with the project so that there is no conflict between the operations executed with any existing form, data set, and the like.
From the File menu, point to "New" and click Project. In the Project Type pane, select "Visual Basic Project", "Visual C # item" or "Visual C Project". In the Template pane, select "Windows Applications" (if you are Visual Basic and Visual C # items), or select "Windows Form Application (.NET)" (if it is Visual C project). Give the project that meets the unique names for the named rules used. For example, you can name this item WalkthRough_SIMPLE1. When you assign a name and specify a new solution, click OK. Visual Studio creates a new project and displays a new form in the Windows Form Designer.
Create and configure data sets
Like most of the Data Access Schemes in the Windows Form, you will use the data set. The dataset is a container (cache) that saves the record you want to use.
Note that the use of data sets is just a way to access data, which is not the best choice in some cases. However, in a Windows Form Application, the data set is usually the correct choice, and a data set will be used in this exercise. For more information, see
Data Access Policy Suggestions.
In this exercise, you will add a data set to the form. However, it is not because the data set has not yet exists, you will add this data set to the form. What you should do is as follows:
Create a data adapter using the wizard. The adapter contains a SQL statement for reading and writing database information. The wizard helps you define the required SQL statement. The wizard also creates a connection with the database if necessary. Generate a data set architecture. In this process, you will make Visual Studio create a new type of data category based on the tables and columns you are accessing. When you generate a data category, you will also add an instance of such a class to your form.
Follow all the processes in this section. Otherwise, the form will not have a data set to be used in the subsequent part of this exercise.
For more information on the data adapter, see the data adapter. For more information on the data set, see the Data Set.
Configure data connection and data adapter
First, create a data adapter that contains the SQL statement used to fill the data set. As part of this process, the connection is defined to access the database. Use the wizard to configure the data adapter, which makes it easy to create the SQL statement required for creating data access.
Note that after the wizard is complete, you must continue the next part to generate a data set and complete the data access section of the form.
Safety instructions Store detailed information for the connection string (such as server name, user name, and password) may affect the security of the application. To control access to the database, a more secure method is to use Windows integration security. For more information, see Database Security
.
Create a data connection and data adapter
Drag the OLEDBDataAdapter object onto the form from the Data tab of Toolbox.
Note You can also use SqlDataAdapter, which is optimized for SQL Server 7.0 or higher. Using OLEDBDataAdapter in this walking because it is more common to provide ADO.NET access to any data source compatible with OLE DB.
"Data Adapter Configuration Wizard" started, it will help you create a connection and adapter. In this wizard, do the following:
In the second pane, create or select a connection to the SQL Server Pubs database. In the third pane, specify that you want to use the SQL statement to access the database. Create the following SQL statement in the fourth pane: SELECT AU_ID, AU_LNAME, AU_FNAME, CITY, State, Phone, Contract
From Authors to make it easy to generate SQL statements, click Query Builder to launch the Query Builder.
Note In this drill, the data set will be filled with all rows in the Authors table. In the finished application, it is usually optimized to optimize data access by creating a query that returns only the desired columns and rows. For example, see
Walkthrough: Use parameterized query to display data in a Windows Form.
Click Finish to complete the wizard. After the wizard is complete, you will get a connection (OLEDBConnection1 in Visual Basic, which is OLEDBConnection1 in Visual C # or Visual C ), which contains information related to how to access the database. You will also have a data adapter (OLEDBDATADAPTER1 in Visual Basic, OLEDBDataAdapter1 in Visual C # or Visual C ), which contains a query for defining which tables to access which tables and which columns are to access. Connect and then adaptors
After the wizard is completed, the data set is generated based on the SQL query created during the process. For more information, see the next section.
Create a data set
Establish a method of connecting to a database and specify the required information (after the SQL command in the data adapter), you can create a data set for Visual Studio. Visual Studio can automatically generate a data set based on the query specified for the data adapter. The data set is an instance of the DataSet class based on the corresponding XML architecture (.xsd file), which describes the elements of the class (table, column, and constraint). For more information on the relationship between the data set and the architecture, see Ado.net Data Access Introduction.
Generate data set
Select "Generate Data Set" from the Data menu.
Tip If you don't see the Data menu, click in the form; the form must have a focus, which will appear.
The Generate Dataset dialog is displayed. Select the New option to name the dataset DSAUTHORS. In the list below "Select Table to Add to Data Set", you should select the "Authors" table. Make sure the "Add this Data Set to Designer" is selected, and then click OK. Visual Studio generates a type of data set (DSAUTHORS) and a schema that defines the dataset. You will see the new architecture (DSAUTHORS.XSD) in the Solution Explorer. Tip In the Solution Explorer, click Show All Files to view the relevant .vb or .cs file of the architecture file, the file contains the code that defines the new data set.
Finally, Visual Studio adds an instance of the new data category (DSAUTHORS1 or DSAUTHORS1) to the form.
At this point, it has been completed to get information from the database and transfer information to all settings required for the data set. Ready to create a form that displays data.
Add DataGrid control to display data
In this exercise, you will add a control (ie DataGrid control) that can also display all records in the data set. Another method is to display a record at a single control using a text box. This strategy will then ask you to add navigation features to your form. Therefore, for the sake of simplicity, the data grid should be used.
Note Examples of how to display the record of the data set using a single text box, see
Walkthrough: Use parameterized query to display data in a Windows Form.
Data grids must be bound to the data set to display data.
Add a binding DataGrid control to the form
If there is not yet this, click the tab at the top of the current window to switch to the Form Designer. Drag the DataGrid control onto the form from the Windows Forms tab of Toolbox. Press the F4 key to display the Properties window. In the DataSource property, select DSAUTHORS1 (or DSAUTHORS1) as a data source. Do not choose DSAUTHORS1.AUTHORS (or DSAUTHORS1.AUTHORS). Select "Authors" in the DataMember property. Setting these two properties Bind the Authors data tables within the DSAUTHORS1 data set to the grid. Adjust the size of the grid so you can see all columns. Change its height so that you can see multiple authors records.
Fill DataGrid controls
Although the data grid is bound to the created data set, the data set itself will not be filled out. Instead, you must call the data adapter method to fill the data set. For more information on populating the data set, see the Data Set.
Fill DataGrid controls
Drag the Button control onto the form from the Windows Forms tab of Toolbox. Name this button btnload, change the title to "loading" by setting its text attribute. Double-click this button to create an event processing method for its Click event. In this method, the created dataset is cleared, then call the Fill method of the data adapter to transfer the data set to which you want to fill it. The following example shows the complete method: 'Visual Basic
Private sub btnload_click (byval sender as system.object, byval e as system.eventargs) Handles btnload.click
Dsauthors1.clear ()
OLEDBDataAdapter1.fill (dsauthors1)
End Sub
// C #
Private void btnload_click (Object Sender, System.EventArgs E)
{
DSAUTHORS1.CLEAR ();
OLEDBDataAdapter1.fill (dsauthors1);
// C
Private:
System :: void btnload_click (system :: object * sender,
System :: Eventargs * e)
{
DSAUTHORS1-> CLEAR ();
OLEDBDataAdapter1-> Fill (DSAUTHORS1);
}
Update database
When the user changes in the grid, the control automatically saves the updated record in the data set. In a Windows Form, the data binding structure writes the value of the data binding control into the data line bound to these controls.
Note that in a web form page, the data binding mode is slightly different. For step-by-step examples of data binding in the web form page, see
Walkthrough: Display data in the web form page
Exercise: Update the query update data using the database update in the web form.
However, when using a data set, update requires two steps. When the data is within the data set, you must still send it from the data set to the database. The data adapter can do this through its UPDATE method. This method checks each record in the data table specified in the dataset. If a record has changed, send the corresponding "update", "Insert" to the database. INSERT) or "Delete" command. For more information, see the Data Set Update Introduction.
In this walkthrough, a button will be added to the form, and when the user wants to send it to the database, you can press this button.
Update database
Drag the Button control onto the form from the Windows Forms tab of Toolbox. Name this button btNupdate, change the title to "Change to the Database" by setting its Text property. Double-click this button to create an event processing method for its Click event. In this method, the UPDATE method of the data adapter is called, and the data set is passed to the method, which contains updates to be sent to the database. Use the message box (MessageBox) object to display the confirmation text. The following example shows the complete method: 'Visual Basic
Private sub btnupdate_click (Byval E AS System.EventArgs) Handles btnupdate.click
OLEDBDATAADAPTER1.UPDATE (DSAUTHORS1)
Messagebox.show ("Database Updated!")
End Sub
// C #
Private void btnupdate_click (Object Sender, System.Eventargs E)
{
OLEDBDATAADAPTER1.UPDATE (DSAUTHORS1);
MessageBox.show ("Database Updated!");
}
// C
Private:
System :: void btnupdate_click (system :: object * sender,
System :: Eventargs * e)
{
OLEDBDATADAPTER1-> Update (dsauthors1);
Messagebox :: Show ("Database Updated!");
}
test
You can now test the form to make sure it displays the author data in the grid, and the user can update.
Test form
Press the F5 key to run the form.
Note that the type is usually obtained.
System.Data.sqlclient.sqlex Exceptions, which is probably caused by your database login credentials. For information on correcting the issue, see Databases that cannot be accessed during design at runtime.
After the form is displayed, click the "Load" button. Display a list of authors in the grid. Change a record in the grid. The changes will be retained when moving to another record in the grid. Please remember the changes you made in your heart. Click the "Load" button again. This will reload the dataset from the database and refresh your grid. Note that because the changes have not been saved from the dataset to the database, the changes you have made in step 3 are not retained. Change a record in the grid again. Click the "Save the changes to the database" button. Will see the display message box, but there is no change in the grid. Click the "Load" button again to reload the data from the database. This time, because the corresponding data has been saved to the database, the changes you made in step 5 are reserved.
