Lesson 1

Data in the VirtualBookShelf database will change. Inventory and customers will be added and removed, quantity on hand, prices, customer information, and more will change. Therefore, Web pages are needed to provide these functions to users.
This module discusses how to use the recordset and data-bound Design Time Controls to:

1. Add an Update button to update existing data
2. Add an Insert button to insert new rows in a table
3. Add a Delete button to remove a table row

You will be surprised at just how little work is required to add these features to a Web page using Visual InterDev's drag-and-drop features.
Dragging and dropping Design Time Controls is quick and easy. However, you are not required to use them. In some cases you may want to use script instead. You will also see in this module how to use script directly with the Data Environment rather than using Design Time Controls.

In SQL Server, you can alter a recordset using data-bound design-time controls by using the following steps:

1. Connect to the database: Establish a connection to the SQL Server database where the recordset resides.
2. Create a DataSet: Create a DataSet object in your .NET project that represents the data from the recordset you want to alter.
3. Add a DataAdapter: Add a DataAdapter object to the DataSet. The DataAdapter will be used to populate the DataSet with data from the database and to update the database with changes made to the DataSet.
4. Configure the DataAdapter: Configure the DataAdapter by setting the SELECT statement that will be used to retrieve data from the database and the UPDATE, INSERT, and DELETE statements that will be used to update the database with changes made to the DataSet.
5. Bind the DataSet to a DataGridView control: Bind the DataSet to a DataGridView control using the DataSource property. This will allow you to view and edit the data in the DataSet.
6. Save Changes: When you make changes to the data in the DataGridView, the changes are automatically propagated back to the DataSet. Call the Update method of the DataAdapter to save the changes back to the database.

using System;
using System.Data;
using System.Data.SqlClient;

namespace RecordsetAlterationExample
{
  class Program
  {
      static void Main(string[] args)
      {
          // Connect to the database
          SqlConnection connection = new SqlConnection("Data Source=
	<server>;Initial Catalog=<database>;Integrated Security=True");
          connection.Open();
           // Create a DataSet and a DataAdapter
          DataSet dataSet = new DataSet();
          SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM Customers", connection);
           // Configure the DataAdapter
          SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
          dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
          dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
          dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();

          // Fill the DataSet with data from the database
          dataAdapter.Fill(dataSet, "Customers");

          // Bind the DataSet to a DataGridView
          DataGridView dataGridView = new DataGridView();
          dataGridView.DataSource = dataSet.Tables["Customers"];
          // Save changes back to the database
          dataAdapter.Update(dataSet, "Customers");
          connection.Close();
       }
   }
}

In this example, a connection is established to the SQL Server database, a DataSet and a DataAdapter are created, and the DataAdapter is configured with the appropriate SELECT, UPDATE, INSERT, and DELETE statements. The DataSet is then bound to a DataGridView, allowing you to view and edit the data. Finally, the changes are saved back to the database by calling the Update method of the DataAdapter.