Details on Data Adapters

Fundamentals of a Data Adapter

We have used the data adapter so many times that you wonder if we still have to introduce it. In reality, the data adapater is one of the most useful and used objects in ADO.NET that we can hardly avoid. Now, we will go into more details about that object.

You probably know already that the DataSet class was developed to help you create and manage any type of list-based application, including collections, XMl, and databases. The high level of flexibility that this class offers also allows it to directly integrate with a data-based application, such as one created with Microsoft SQL Server. The elements of a DataSet object directly relate to those of a database application. As mentioned already, a DataSet object is primarily used to create a list, not a formal database in the strict sense. This means that a list-based application lead by a DataSet object is primarily a list. In order to read information of a formal database and use it in a DataSet list, you must "convert" or adapt it.

A data adapter is an object that takes data from a database, reads that data, and "fills" a DataSet object with that data. In reverse, a data adapter can get the data stored in, or manipulated by, a DataSet object and fill or update a database with that data. To be able to apply these scenarios to any database, the .NET Framework provides various data adapters, each adapted to a particular category of database (Microsoft SQL Server, Oracle, Microsoft Access, etc).

1. Start Microsoft Visual Studio
2. Create a new Windows Forms Application named SoloMusicStore2
3. In the Solution Explorer, right-click Form1.cs and click Rename
4. Type SoloMusicStore and press Enter twice
5. Double-click the body of the form
6. Change the document as follows:

   using System;
   using System.Collections.Generic;
   using System.ComponentModel;
   using System.Data;
   using System.Drawing;
   using System.Linq;
   using System.Text;
   using System.Threading.Tasks;
   using System.Windows.Forms;
   using System.Data.SqlClient;
   using System.IO;
   
   namespace SoloMusicStore2
   {
       public partial class SoloMusicStore : Form
       {
           public SoloMusic()
           {
               InitializeComponent();
           }
   
           private void CreateDatabase()
           {
               // Here We Go
           }
   
           private void SoloMusicStore_Load(object sender, EventArgs e)
           {
               CreateDatabase();
           }
       }
   }

7. From the resources that accompany these lessons, open the Solo Music Store file and select everything in it
8. Paste it by replacing the // Here We Go line above
9. Execute the application and click OK on the message boxes
10. Close the form and return to your programming environment
11. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
12. Set the Name to NewStoreItem and click Add
13. Design the form as follows:
     

    Control (Name) Text Other Properties Label   Item #:   Text Box txtItemNumber   Modifiers: Public Label   Category:   Combo Box cbxCategories   Modifiers: Public Label   Sub-Category:   Combo Box cbxSubCategories   Modifiers: Public Label   Item Name:   Text Box txtItemName   Modifiers: Public Label   Unit Price   Text Box txtUnitPrice   Modifiers: Public Button btnOK OK DialogResult: OK Button btnCancel Cancel DialogResult: Cancel Picture Box     SizeMode: Zoom

14. Click an unoccupied area of the form and, in the Properties window, change the following characteristics:
    AcceptButton: btnOK
    CancelButton: btnCancel
    FormBorderStyle: FixedDialog
    MaximizeBox: False
    MinimizeBox: False
    ShowInTaskbar: False
    StartPosition: CenterScreen
15. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
16. Set the Name to StoreItemMaintenance and click Add
17. Complete the design of the form as follows:
     

    Control (Name) Text Other Properties Label   Item #:   Text Box txtItemNumber     Button btnFind Find   Label   Category:   Combo Box cbxCategories     Label   Sub-Category:   Combo Box cbxSubCategories     Label   Item Name:   Text Box txtItemName     Label   Unit Price   Text Box txtUnitPrice     Button btnMaintain   Modifiers: Public Button btnClose Close   Picture Box     Size Mode: Zoom

18. Click an unoccupied area of the form and, in the Properties window, change the following characteristics:
    FormBorderStyle: FixedDialog
    MaximizeBox: False
    MinimizeBox: False
    ShowInTaskbar: False
    StartPosition: CenterScreen
19. Display the Solo Music form and design it as follows:
     

    Control (Name) Text Other Properties Label   Category   List Box lbxCategories     Label   Sub-Category   List Box lbxSubCategories     Label   Available Items   List View lvwAvailableItems   FullRowSelect: True GridLines: True View: Details     (Name) Text TextAlign Width colItemNumber Item #   90 colItemName Item Name   500 colUnitPrice Unit Price Right 105   Button btnNewStoreItem New Store Item ...   Button btnUpdateStoreItem Update Store Item ...   Button btnDeleteStoreItem Delete Store Item ...   Picture Box pbxSelectedItem   SizeMode: Zoom Button Close btnClose

20. Click the list view
21. In the Properties window, click Events and double-click ItemSelectionChanged
22. Implement the event as follows:

    private void lvwAvailableItems_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
    {
        string strPath = @"C:\Music Store";
        DirectoryInfo diStoreItems = new DirectoryInfo(strPath);
        FileInfo[] aryStoreItems = diStoreItems.GetFiles("jpg", SearchOption.AllDirectories);
    
        IEnumerable<FileInfo> pictureFiles = from picts
                                             in diStoreItems.GetFiles()
                                             select picts;
    
        string strFileName = @"C:\Music Store\si.jpg";
        foreach (var file in pictureFiles)
        {
            if (file.Name == e.Item.Text + ".jpg")
            {
                strFileName = file.FullName;
                break;
            }
        }
    
        pbxSelectedItem.Image = Image.FromFile(strFileName);
    }

In order to read information from a Microsoft SQL Server database and make it available to a DataSet object, you can use an object created from the SqlDataAdapter class. This class is defined in the System.Data.SqlClient namespace of the System.Data.dll library. The SqlDataAdapter class is derived from the DbDataAdapter class:

public sealed class SqlDataAdapter : DbDataAdapter, 
				     IDbDataAdapter,
				     IDataAdapter,
				     ICloneable

The DbDataAdapter class is derived from the DataAdapter class. The DbDataAdapter and the DataAdapter classes are defined in the System.Data.Common namespaces of the System.Data.dll library. There are two ways you can create a data adapter: visually or programmatically.

In early versions of Microsoft Visual Studio (2002 and 2003), the Toolbox was equipped with various data adapters (one for each category of database type). It was removed in the 2005 version. If you want to visually create a data adapter, you must manually add it to the Toolbox. To do this, you can right-click the Data section of the Toolbox and click Choose Items... In the .NET Framework Component property page, scroll down and put a check mark on a data adapter. For our lesson, this would be SqlDataAdapter:

After making the selection, click OK. This would add a SqlDataAdapter object to the Toolbox.

As mentioned already, there are various ways to create a data adapter. As we have done so far, to programmatically create a SQL data adapter, declare a variable of type SqlDataAdapter and initialize it using one of its constructors, such as the default constructor. Here is an example:

SqlDataAdapter sdaVideoCollection = new SqlDataAdapter();

After doing this, you can specify the behavior you expect from the data adapter. If you have a SqlDataAdapter control in the Toolbox, click its button and click the form.

To allow the data adapter to use values produced from reading a table, you must create a command and pass it to the data adapter. You have various options. You can (first) create a SqlCommand object and pass it to the data adapter. To support this, the SqlDataAdapter class is equipped with the following constructor:

public SqlDataAdapter(SqlCommand selectCommand);

Here is an example of using it:

 using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
    SqlCommand cmdEmployees = new SqlCommand(". . .", cntExercise);

    cntExercise.Open();
    cmdEmployees.ExecuteNonQuery();

    SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);

    . . .
}

The SqlDataAdapter class provides two other constructors. If you visually add a SqlDataAdapter from the Toolbox by clicking it and clicking a form, the Data Adapter Configuration would start. In the first page of the wizard, you must create or select the connection you will use:

After selecting the connection, you can click Next. In the second page of the wizard, you must specify the mechanism you will use to get your data. You have three options:

As mentioned already, a data adapter is used to act as an intermediary between a database and a data set. The data adapter is equipped to perform the regular DML operations of a database. These include creating a new record, selecting existing records, updating, and delecting records. To support these operations, the data adapter has the necessary constructors, methods, and properties. To perform some operations, the data adapter must get help from an object named the command builder.

For a Microsoft SQL Server database, the command builder that can assist the data adapter is named SqlCommandBuilder:

public sealed class SqlCommandBuilder : DbCommandBuilder

This class is equipped with two constructors, of which one is the default. The other constructor takes a data adapter as argument. Its syntax is:

public SqlCommandBuilder(SqlDataAdapter adapter);

This constructor takes as argument the data adapter on which you want to perform the operation.

To give you the ability to create a record, the SqlDataAdapter class is equipped with a third constructor whose syntax is:

public SqlDataAdapter(string selectCommandText, SqlConnection selectConnection);

This constructor takes as its first argument the INSERT statement used to create a record. The second argument specifies the connectin that will be used. Here is an example:

private void btnCreateStoreItem_Click(object sender, EventArgs e)
{
    DataSet dsMusicStore = new DataSet("StoreItemsSet");
    
    using(SqlConnection scMusicStore = new SqlConnection("Data Source=(local);" +
                                     "Database='SoloMusicStore1';Integrated Security=True;"))
    {
        SqlDataAdapter sdaMusicStore = new SqlDataAdapter("INSERT INTO Inventory.StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice) " +
                                                          "VALUES(N'902494', N'Traditional Instruments', N'Banjos', N'Rogue B30 Deluxe 30-Bracket Banjo with Aluminum Rim', 150)",
                                                          scSoloMusic);
    }
}

As another option, a data adapter has a property named InsertCommand, which is of type SqlCommand:

public SqlCommand InsertCommand { get; set; }

The actual way to create a record is to fill a data set with the necessary values and add that row to its parent table. Then pass the data adapter to a command builder.

A data adapter can be used to add a new record to a table. The other maintenance operations consist of editing or deleting records. All these operations cause changes on a table and its parent data set. When such an operation has been performed, the data set must be updated. To support it, the data adapter is equipped with a method named Update. That method is actually derived from the DbDataAdapter parent class. The DbDataAdapter.Update() method is overloaded with six versions. One of the versions takes a data table as argument. The other takes a data set as argument. Its syntax is:

public override int Update(DataSet dataSet);

Another version takes an array of records as argument.

1. Display the Solo Music Store form and double-click the New Store Item button
2. Change the document as follows:

   public partial class SoloMusicStore : Form
   {
       DataSet dsStoreItems;
       SqlConnection scSoloMusic;
       SqlDataAdapter sdaStoreItems;
       SqlCommand cmdCreateStoreItem;
       SqlCommand cmdSelectStoreItems;
   
       public SoloMusicStore()
       {
           InitializeComponent();
       }
           
       private void InitializeStoreItems()
       {
       }
   
       . . . No Change
       
       private void SoloMusicStore_Load(object sender, EventArgs e)
       {
           // CreateDatabase();
           InitializeStoreItems();
       }
   
       private void btnNewStoreItem_Click(object sender, EventArgs e)
       {
           dsStoreItems.Clear();
           lbxSubCategories.Items.Clear();
           lvwAvailableItems.Items.Clear();
           NewStoreItem nsi = new NewStoreItem();
   
           if (nsi.ShowDialog() == System.Windows.Forms.DialogResult.OK)
           {
               DataRow storeItem = dsStoreItems.Tables[0].NewRow();
               storeItem["ItemNumber"] = nsi.txtItemNumber.Text;
               storeItem["Category"] = nsi.cbxCategories.Text;
               storeItem["SubCategory"] = nsi.cbxSubCategories.Text;
               storeItem["ItemName"] = nsi.txtItemName.Text;
               storeItem["UnitPrice"] = nsi.txtUnitPrice.Text;
               dsStoreItems.Tables[0].Rows.Add(storeItem);
   
               SqlCommandBuilder scbStoreItem = new SqlCommandBuilder(sdaStoreItems);
               sdaStoreItems.Update(dsStoreItems);
           }
   
           InitializeStoreItems();
       }
   }

Filling a Data Set

Once a data adapter is ready, in order to use its records, you must pass them to a data set. This means that you would need a DataSet object. To support this operation, the SqlDataAdapter class inherits the Fill() method from the DbDataAdapter class. This method is overloaded with 8 versions. One of the versions takes a data set as argument. Its syntax is:

public override int Fill(DataSet dataSet);

This version takes as argument an object of type DataSet. After this call, the dataset argument would be filled with the records of the table read by the data adapter. Here is an example:

private void btnCreateStoreItem_Click(object sender, EventArgs e)
{
    DataSet dsMusicStore = new DataSet("StoreItemsSet");
    
    using(SqlConnection scMusicStore = new SqlConnection("Data Source=(local);" +
                                     "Database='SoloMusic3';Integrated Security=True;"))
    {
        SqlDataAdapter sdaMusicStore = new SqlDataAdapter("INSERT INTO Inventory.StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice) " +
                                                          "VALUES(N'902494', N'Traditional Instruments', N'Banjos', N'Rogue B30 Deluxe 30-Bracket Banjo with Aluminum Rim', 150)", scSoloMusic );

        sdaMusicStore.Fill(dsStoreItems);
        SqlCommandBuilder scbStoreItem = new SqlCommandBuilder(sdaStoreItems);
        sdaStoreItems.Update(dsStoreItems);
    }
}

Another version tables a data table as argument. Its syntax is:

public int Fill(DataTable dataTable);

If you declare your own DataSet variable, you would also eventually have to take care of some detailed operations such as reading from XML, writing to XML, or serializing.

The primary purpose of a data adapter is to get values from a table or view. If you are programmatically creating the data adapter, you can pass a SQL statement to the command. A data adapter (such as the SqlDataAdapter class) is equipped with a property named SelectCommand (of type SqlCommand for a SqlDataAdapter object). Another option to specify how data would be read consists of first creating a SqlCommand object that would carry a SQL statement:

SqlConnection cnnVideos = new SqlConnection(
    "Data Source=(local);Database='VideoCollection';Integrated Security=yes");

string strVideos = "SELECT * FROM Videos;";
SqlCommand cmdVideos = new SqlCommand(strVideos, cnnVideos);

Equipped with a SqlCommand object that holds a SQL statement, you can assign it to the SqlDataAdapter.SelectCommand property of your data adapter. This would be done as follows:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "SELECT * FROM Videos;";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    SqlDataAdapter sdaVideoCollection = new SqlDataAdapter();
    sdaVideoCollection.SelectCommand = cmdVideos;

    cnnVideos.Open();
    cnnVideos.Close();
}

If you don't want to use the default constructor and the SelectCommand property separately, you can use the second constructor of the SqlDataAdapter class. Its syntax is:

public SqlDataAdapter(SqlCommand selectCommand);

This constructor takes as argument a SqlCommand object. This time, instead of assigning the command to the SelectCommand property, you can pass that SqlCommand object to the SqlDataAdapter variable when declaring it. This would be done as follows:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "SELECT * FROM Videos;";
    SqlCommand cmdVideos = new SqlCommand(strVideos, cnnVideos);

    SqlDataAdapter sdaVideoCollection = new SqlDataAdapter(cmdVideos);

    cnnVideos.Open();
    cnnVideos.Close();
}

Notice that with both constructors reviewed above, you must pass the connection to a SqlCommand object. As an alternative, you can create a connection but pass it directly to the data adapter when declaring its variable. To do this, use the third constructor of the SqlDataAdapter class. Its syntax is:

public SqlDataAdapter(string selectCommandText,  SqlConnection selectConnection);

The first argument of this constructor expects a SQL statement, passed as string, that specifies how the data would be read. The second argument is a SqlConnection object that specifies how the connection to the database would be handled. Here is an example:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "SELECT * FROM Videos;";

    SqlDataAdapter sdaVideoCollection = new SqlDataAdapter(strVideos, cnnVideos);

    cnnVideos.Open();
    cnnVideos.Close();
}

Instead of separately defining a SqlConnection and a SqlDataAdapter objects, you can directly provide a connection string to the SqlDataAdapter object when declaring it. To do this, you can use the fourth constructor of the SqlDataAdapter class. Its syntax is:

public SqlDataAdapter(string selectCommandText, string selectConnectionString);

The first argument to this constructor is the statement that specifies how data would be read. The second argument is a connection string. Here is an example of declaring a data adapter using this version of the SqlDataAdapter class:

private void Form1_Load(object sender, System.EventArgs e)
{
    string strSelVideos = "SELECT * FROM Videos;";
    string strConVideos = "Data Source=(local);Database='VideoCollection';Integrated Security=yes";

    SqlDataAdapter sdaVideoCollection = new SqlDataAdapter(strSelVideos, strConVideos);
}

If you are visually creating the data adapter, in the second page of the wizard, click (or accept) the Use SQL Statements option and click Next. In the third page of the wizard, you will have the option to manually write your SQL statement or click the Query Builder button and construct the statement. After making the selection in the wizard, click Next. The rest of the pages of the wizard propose some options.

1. In the document code of the Solo Music Store form, implement the InitializeStoreItems() method as follows:

   private void InitializeStoreItems()
   {
       NewStoreItem nsi = new NewStoreItem();
       sdaStoreItems = new SqlDataAdapter();
       dsStoreItems = new DataSet("StoreItemsSet");
       scSoloMusic = new SqlConnection("Data Source=(local);" +
                                        "Database='SoloMusicStore1';Integrated Security=True;");
   
       cmdSelectStoreItems = new SqlCommand("SELECT ItemNumber, " +
                                            "       Category, " +
                                            "       SubCategory, " +
                                            "       ItemName, " +
                                            "       UnitPrice " +
                                            "FROM Inventory.StoreItems;",
                                            scSoloMusic);
       sdaStoreItems.SelectCommand = cmdSelectStoreItems;
       sdaStoreItems.Fill(dsStoreItems);
   
       foreach (DataRow storeItem in dsStoreItems.Tables[0].Rows)
           if( !(string.IsNullOrEmpty(storeItem["Category"].ToString())) && 
               !(lbxCategories.Items.Contains(storeItem["Category"].ToString())) )
               lbxCategories.Items.Add(storeItem["Category"].ToString());
   
       scSoloMusic.Close();
   }

2. Return to the Solo Music Store form and double-click the Category list box
3. Implement the event as follows:

   private void lbxCategories_SelectedIndexChanged(object sender, EventArgs e)
   {
       dsStoreItems.Clear();
       lbxSubCategories.Items.Clear();
       lvwAvailableItems.Items.Clear();
       sdaStoreItems = new SqlDataAdapter("SELECT ItemNumber, " +
                                          "       Category, " +
                                          "       SubCategory, " +
                                          "       ItemName, " +
                                          "       UnitPrice " +
                                          "FROM Inventory.StoreItems " +
                                          "WHERE Category = N'" + lbxCategories.SelectedItem.ToString() + "';",
                                          scSoloMusic);
       sdaStoreItems.Fill(dsStoreItems);
   
       foreach (DataRow storeItem in dsStoreItems.Tables[0].Rows)
           if( !(string.IsNullOrEmpty(storeItem["SubCategory"].ToString())) &&
               !(lbxSubCategories.Items.Contains(storeItem["SubCategory"].ToString())) )
               lbxSubCategories.Items.Add(storeItem["SubCategory"].ToString());
   }

4. Return to the Solo Music Store form and double-click the Sub-Category list box
5. Implement the event as follows:

   private void lbxSubCategories_SelectedIndexChanged(object sender, EventArgs e)
   {
       dsStoreItems.Clear();
       lvwAvailableItems.Items.Clear();
       sdaStoreItems = new SqlDataAdapter("SELECT ItemNumber, " +
                                          "       Category, " +
                                          "       SubCategory, " +
                                          "       ItemName, " +
                                          "       UnitPrice " +
                                          "FROM Inventory.StoreItems " +
                                          "WHERE SubCategory = N'" + lbxSubCategories.SelectedItem.ToString() + "';",
                                          scSoloMusic);
       sdaStoreItems.Fill(dsStoreItems);
   
       foreach (DataRow storeItem in dsStoreItems.Tables[0].Rows)
       {
           ListViewItem lviStoreItem = new ListViewItem(storeItem["ItemNumber"].ToString());
           lviStoreItem.SubItems.Add(storeItem["ItemName"].ToString());
           lviStoreItem.SubItems.Add(double.Parse(storeItem["UnitPrice"].ToString()).ToString("F"));
   
           lvwAvailableItems.Items.Add(lviStoreItem);
       }
   }

6. Display the New Store Item form and double-click an unoccupied area of its body
7. Change the document as follows:

   using System;
   using System.Collections.Generic;
   using System.ComponentModel;
   using System.Data;
   using System.Drawing;
   using System.Linq;
   using System.Text;
   using System.Windows.Forms;
   using System.IO;
   using System.Data.SqlClient;
   
   namespace SoloMusicStore2
   {
       public partial class NewStoreItem : Form
       {
           DataSet dsStoreItems;
           SqlConnection scSoloMusic;
           SqlDataAdapter sdaStoreItems;
           SqlCommand cmdSelectStoreItems;
   
           public NewStoreItem()
           {
               InitializeComponent();
           }
   
           private void NewStoreItem_Load(object sender, EventArgs e)
           {
               sdaStoreItems = new SqlDataAdapter();
               dsStoreItems = new DataSet("StoreItemsSet");
               scSoloMusic = new SqlConnection("Data Source=(local);" +
                                               "Database='SoloMusicStore1';" +
                                               "Integrated Security=True;");
   
               cmdSelectStoreItems = new SqlCommand("SELECT ItemNumber, " +
                                                    "       Category, " +
                                                    "       SubCategory, " +
                                                    "       ItemName, " +
                                                    "       UnitPrice " +
                                                    "FROM Inventory.StoreItems;",
                                                    scSoloMusic);
               sdaStoreItems.SelectCommand = cmdSelectStoreItems;
               sdaStoreItems.Fill(dsStoreItems);
   
               foreach (DataRow storeItem in dsStoreItems.Tables[0].Rows)
                   if (!(string.IsNullOrEmpty(storeItem["Category"].ToString())) &&
                       !(cbxCategories.Items.Contains(storeItem["Category"].ToString())))
                       cbxCategories.Items.Add(storeItem["Category"].ToString());
           }
       }
   }

8. Return to the New Store Item form and double-click the Category combo box
9. Implement the event as follows:

   private void cbxCategories_SelectedIndexChanged(object sender, EventArgs e)
   {
       dsStoreItems.Clear();
   
       cbxSubCategories.Items.Clear();
       sdaStoreItems.SelectCommand = new SqlCommand("SELECT ItemNumber, " +
   	                                         "       Category, " +
                                                    "       SubCategory, " +
                                                    "       ItemName, " +
                                                    "       UnitPrice " +
                                                    "FROM Inventory.StoreItems " +
                                                    "WHERE Category = N'" + cbxCategories.SelectedItem.ToString() + "';",
                                                    scSoloMusic);
       sdaStoreItems.Fill(dsStoreItems);
   
       foreach (DataRow storeItem in dsStoreItems.Tables[0].Rows)
           if (!(string.IsNullOrEmpty(storeItem["SubCategory"].ToString())) &&
               !(cbxSubCategories.Items.Contains(storeItem["SubCategory"].ToString())))
               cbxSubCategories.Items.Add(storeItem["SubCategory"].ToString());
   }

10. Return to the New Store Item form and click the Item # text box
11. In the Properties window, click Events, and double-click Leave
12. Implement the event as follows:

    private void txtItemNumber_Leave(object sender, EventArgs e)
    {
        string strPath = @"C:\Solo Music";
        DirectoryInfo diStoreItems = new DirectoryInfo(strPath);
        FileInfo[] aryStoreItems = diStoreItems.GetFiles("jpg", SearchOption.AllDirectories);
    
        IEnumerable<FileInfo> pictureFiles = from picts
                                             in diStoreItems.GetFiles()
                                             select picts;
    
        string strFileName = @"C:\Music Store\si.jpg";
        foreach (var file in pictureFiles)
        {
            if (file.Name == txtItemNumber.Text + ".jpg")
            {
                strFileName = file.FullName;
                break;
            }
        }
    
        pbxSelectedItem.Image = Image.FromFile(strFileName);
    }

13. Display the Store Item Maintenance form and double-click the Find button
14. Change the Document as follows:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    using System.Data.SqlClient;
    
    namespace SoloMusicStore2
    {
        public partial class StoreItemMaintenance : Form
        {
            DataSet dsStoreItems;
            SqlConnection scSoloMusic;
            SqlDataAdapter sdaStoreItems;
    
            public MaintainStoreItem()
            {
                InitializeComponent();
            }
    
            private void btnFind_Click(object sender, EventArgs e)
            {
                string strPath = @"C:\Music Store";
                sdaStoreItems = new SqlDataAdapter();
                dsStoreItems = new DataSet("StoreItemsSet");
                string strFileName = @"C:\Music Store\si.jpg";
                DirectoryInfo diStoreItems = new DirectoryInfo(strPath);
                FileInfo[] aryStoreItems = diStoreItems.GetFiles("jpg", SearchOption.AllDirectories);
    
                scSoloMusic = new SqlConnection("Data Source=(local);" +
                    "Database='SoloMusic1';Integrated Security=True;");
    
                sdaStoreItems.SelectCommand = new SqlCommand("SELECT ItemNumber, " +
                                                     "       Category, " +
                                                     "       SubCategory, " +
                                                     "       ItemName, " +
                                                     "       UnitPrice " +
                                                     "FROM Inventory.StoreItems " +
                                                     "WHERE ItemNumber = N'" + txtItemNumber.Text + "';",
                                                     scSoloMusic);
                sdaStoreItems.Fill(dsStoreItems);
    
                foreach (DataRow storeItem in dsStoreItems.Tables[0].Rows)
                {
                    cbxCategories.Text = storeItem["Category"].ToString();
                    cbxSubCategories.Text = storeItem["SubCategory"].ToString();
                    txtItemName.Text = storeItem["ItemName"].ToString();
                    txtUnitPrice.Text = storeItem["UnitPrice"].ToString();
                }
    
                IEnumerable<FileInfo> pictureFiles = from picts
                                                     in diStoreItems.GetFiles()
                                                     select picts;
    
                foreach (var file in pictureFiles)
                {
                    if (file.Name == txtItemNumber.Text + ".jpg")
                    {
                        strFileName = file.FullName;
                        break;
                    }
                }
    
                pbxSelectedItem.Image = Image.FromFile(strFileName);
            }
        }
    }

15. Display the Solo Music Store form and double-click Update Store Item
16. Implement the event as follows:

    private void btnUpdateStoreItem_Click(object sender, EventArgs e)
    {
        StoreItemMaintenance sim = new StoreItemMaintenance();
    
        sim.btnMaintain.Text = "Update Store Item";
        sim.ShowDialog();
    
        InitializeStoreItems();
        lbxSubCategories_SelectedIndexChanged(sender, e);
    }

17. Return to the Solo Music Store form and double-click Delete Store Item
18. Implement the event as follows:

    private void btnDeleteStoreItem_Click(object sender, EventArgs e)
    {
        StoreItemMaintenance sim = new StoreItemMaintenance();
    
        sim.cbxCategories.Enabled = false;
        sim.cbxSubCategories.Enabled = false;
        sim.txtItemName.Enabled = false;
        sim.txtUnitPrice.Enabled = false;
        sim.btnMaintain.Text = "Delete Store Item";
        sim.ShowDialog();
    
        InitializeStoreItems();
        lbxSubCategories_SelectedIndexChanged(sender, e);
    }

19. Execute the application

    

    

20. Create some records with the following values:
     
    

    Item #	Category	Sub-Category	Item Name	Unit Price
    737785	Accessory	Keyboard Stands	Stage Rocker Powered by Hamilton SR524200 Double X Style Keyboard Stand	29.95
    380205	Keybords	Organs	Hammond XK-1C Portable Organ	1500
    661418	Brass Instruments	Trumpets	Yamaha YTR-4335GII Intermediate Bb Trumpet	1425.50

21. Close the forms and return to your programming environment

Updating a record consists of changing one or more of its values. Normally, we already know how to do it in SQL, except that the Windows Forms application doesn't automatically save the changes. This is a good reason to use a data adapter. You have various options:

• The data adapter class provides the constructor that takes the DML's UPDATE statement. After initializing the data adapter with that constructor, fill the data set with the new records, pass the data adapter to a command builder, then call the Update() method of the data adapter
• To support the ability to update a record, the data adapter class is equipped with a property named UpdateCommand, which is of type SqlCommand. To use this approach, create a command takes the DML's UPDATE statement

Deleting a record consists of removing it from a table. We already know how to do it in SQL. In a Windows Forms application, you have various options:

• With the data adapter class, you can use the constructor that takes the DML's DELETE statement, call the Fill() method of the data adapter to pass the remaining records to the table, pass the data adapter to a command builder, finally call the Update() method of the data adapter
• To support the ability to update a record, the data adapter class is equipped with a property named DeleteCommand, which is of type SqlCommand

1. Display the Store Item Maintenance form and double-click the button on the left of Close
2. Implement the event as follows:

   private void btnMaintain_Click(object sender, EventArgs e)
   {
       if (btnMaintain.Text == "Update Store Item")
       {
           sdaStoreItems = new SqlDataAdapter("UPDATE Inventory.StoreItems " +
                              "SET Category = N'" + cbxCategories.Text + "', " +
                              "    SubCategory = N'" + cbxSubCategories.Text + "', " +
                              "    ItemName = N'" + txtItemName.Text + "', " +
                              "    UnitPrice = " + txtUnitPrice.Text + " " +
                              "WHERE ItemNumber = N'" + txtItemNumber.Text + "';",
                                    scSoloMusic);
   
           sdaStoreItems.Fill(dsStoreItems);
           SqlCommandBuilder scbStoreItem = new SqlCommandBuilder(sdaStoreItems);
           sdaStoreItems.Update(dsStoreItems);
       }
       else // if (btnMaintain.Text == "Delete Store Item")
       {
           if (MessageBox.Show("Are you sure you want to delete this item?",
               "Solo Music Store",
               MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
           {
               sdaStoreItems = new SqlDataAdapter("DELETE FROM Inventory.StoreItems " +
                                    "WHERE ItemNumber = N'" + txtItemNumber.Text + "';",
                                    scSoloMusic);
   
               sdaStoreItems.Fill(dsStoreItems);
               SqlCommandBuilder scbStoreItem = new SqlCommandBuilder(sdaStoreItems);
               sdaStoreItems.Update(dsStoreItems);
   
               MessageBox.Show("The item has been removed from the inventory.",
                       "Solo Music Store",
                       MessageBoxButtons.YesNo, MessageBoxIcon.Question);
           }
       }
   
       Close();
   }

3. Return to the form and double-click the Close button
4. Implement the event as follows:

   private void btnClose_Click(object sender, EventArgs e)
   {
       Close();
   }

5. Execute the application
6. Locate the item # 350250
7. Change its name to Fender FA-100 Acoustic Guitar
8. Change its price to 125.50
9. Click Update
10. Locate the item # 115599 and delete it
11. Close the forms and return to your programming environment

Instead of using the classic DML operators, a data adapter can use stored procedures. Of course, you must first create those procedures. If you are visually creating the data adapter, the second page of the wizard presents two options:

• Click the second radio button, Create New Stored Procedures, and click Next. In the third page of the wizard, you will have the option of manually typing the code for the procedure or selecting the columns. When you are ready, click Next, and click Finish. At the end, the wizard will generate the necessary the code for the stored procedure(s).
• Click the third radio button and click Next. The next page allows you to select the names of stored procedures that will be passed to the data adapter

After completing the wizard, you can use the data adapter as we have done so far.