Home

Database Visual Support

 

A Data Source

 

Introduction

So far, we have introduced and used the primary tools that Microsoft SQL Server provides to create a database. We hardly did anything visually. To make database development user friendly and graphically-driven, Microsoft Visual Studio provides its own set of tools. Some of these tools are available from the Toolbox. Some other tools are provided as classes you can use as long as you are aware of them.

In Lesson 14, we defined a data set as a system of values. The values are kept in one or more lists. We also saw that, to support this system, the .NET Framework provides a class named DataSet. This class is represented in the Data section of the Toolbox of Microsoft Visual Studio by the object of the same name.

Practical LearningPractical Learning: Introducing Visual Database Support

  1. Start Microsoft Visual C# and create a Windows Application named spr2
  2. In the Solution Explorer, right-click Form1.cs and click Rename
  3. Type RentalProperties.cs and press Enter
  4. Double-click the middle of the form and implement the Load event 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.Data.SqlClient;
    
    namespace spr2
    {
        public partial class RentalProperties : Form
        {
    	public RentalProperties()
    	{
    	    InitializeComponent();
    	}
    
    	private void RentalProperties_Load(object sender, EventArgs e)
    	{
    	    using (SqlConnection cnnSolasPropertyRental =
    		new SqlConnection("Data Source=(local);" +
    				  "Integrated Security='SSPI';"))
    	   {
    		string strSolasPropertyRental =
    			"CREATE DATABASE SolasPropertyRental1;";
    
    		SqlCommand cmdSolasPropertyRental1 =
    			new SqlCommand(strSolasPropertyRental,
    				cnnSolasPropertyRental);
    
    		cnnSolasPropertyRental.Open();
    		cmdSolasPropertyRental1.ExecuteNonQuery();
    
    MessageBox.Show("The SolasPropertyRental1 database has been created");
    	    }
    
    	    using (SqlConnection cnnSolasPropertyRental =
    		new SqlConnection("Data Source=(local);" +
    			"Database='SolasPropertyRental1';" +
    			"Integrated Security='SSPI';"))
    	    {
    		string strSolasPropertyRental =
    		    "CREATE TABLE RentalProperties " +
    		    "( " +
    		    " 	RentalPropertyID int identity(1,1) NOT NULL, " +
    		    " 	PropertyCode char(7) NULL, " +
    		    " 	PropertyType varchar(32) NULL, " +
    		    " 	Bedrooms tinyint, " +
    		    " 	Bathrooms float, " +
    		    " 	MonthlyRent smallmoney, " +
    		    " 	OccupancyStatus varchar(30) " +
    		    ");" +
    
    	"INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," +
    	" 	Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" +
    	"VALUES('527-992', 'Apartment', 1, 1.00, 925.00, 'Available')" +
    
    	"INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," +
    	" 	Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" +
    	"VALUES('726-454', 'Apartment', 2, 1.00, 1150.50, 'Available')" +
    
    	"INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," +
    	" 	Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" +
    	"VALUES('476-473', 'Single Family', 5, 3.50, 2250.85, 'Occupied')" +
    
    	"INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," +
    	" 	Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" +
    	"VALUES('625-936', 'Townhouse', 3, 2.50, 1750.00, 'Available')" +
    
    	"INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," +
    	" 	Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" +
    	"VALUES('179-768', 'Townhouse', 4, 2.50, 1920.00, 'Available')" +
    
    	"INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," +
    	" 	Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" +
    	"VALUES('727-738', 'Single Family', 4, 2.00, 2140.50, 'Needs Repair')" +
    
    	"INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," +
    	" 	Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" +
    	"VALUES('371-801', 'Apartment', 3, 2.00, 1250.25, 'Available')" +
    
    	"INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," +
    	" 	Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" +
    	"VALUES('241-536', 'Townhouse', 3, 1.50, 1650.50, 'Occupied')";
    
    	    SqlCommand cmdSolasPropertyRental1 =
    		new SqlCommand(strSolasPropertyRental,
    			cnnSolasPropertyRental);
    
    	    cnnSolasPropertyRental.Open();
    	    cmdSolasPropertyRental1.ExecuteNonQuery();
    
    	        MessageBox.Show("The RentalProperties table has been created");
    	    }
        	}
        }
    }
  5. Execute the application
  6. Close the form and return to your programming environment
  7. Delete the whole code in the Load event

Adding a Data Source

Instead of using the DataSet object from the Toolbox, Microsoft Visual Studio provides a technique that allows you to automatically get a data set object by creating a connection to a database. The data set would be filled with the tables from the database. To use it, you can first display the Data Source window. To display the Data Source, on the main menu, you can click Data -> Show Data Sources.

To create a data source:

The first page of the Data Source Configuration Wizard allows you to specify the type of data source you want to create: Database, Web Service, and Object

Data Source Configuration Wizard

If you click Database and click Next, the second page of the wizard allows you to select an existing connection or create a new one. To select an existing connection, you can click the arrow of the combo box and select from the list:

Choose a Data Source Type

Select the connection and click Next. If you click the New Connection button, you would then have to select the server, the authentication, and the database.

In the third page of the wizard, you would specify a name for the connection string, and click Next.

In the fourth page of the wizard, you have the option of selecting one or more tables (and other types of objects) to include in your data set. To do this, you can click the check boxes in the list. If you do not specify the tables, you can click Finish and, later on, you can reconfigure the data source and select the tables (and/or other types of objects). After making your selection, you can click Finish.

Practical Learning Practical Learning: Adding a Data Source

  1. On the main menu, click Data -> Add New Data Source...
  2. On the first page of the wizard, make sure Database is selected and click Next
  3. In the second page of the wizard, click New Connection...
  4. In the Server Name combo box, select the server or type (local)
  5. In the Select or Enter a Database Name combo box, select SolasPropertyRental1
  6. Click Test Connection
     
    Add Connection
  7. Click OK twice
  8. On the Data Source Configuration Wizard, make sure the new connection is selected
    Click the + button of Connection String
     
    Data Source Configuration Wizard
  9. Click Next
  10. Change the connection string to strSolasPropertyRental and click Next
  11. Expand the Tables node and click the check box of RentalProperties
  12. Change the name of the data set to dsSolasPropertyRental
     
    Data Source Configuration Wizard
  13. Click Finish

The Characteristics of a Data Set

When you click the Finish button of the Data Source Configuration Wizard, Microsoft Visual Studio generates many classes (XML Schemas) and creates a data set object specially made and configured for your database. Practically, the studio would create a class named after the name you gave to the data set and this class would be derived from the DataSet class. To examine this created class, from the Solution Explorer, you can open the file that holds the name of the data set followed by .Designer.cs.

Among the objects created in the data set class is a class that represents the table (or each table) you selected in the last page of the wizard. This class for the table is derived from the DataTable class and implements the System.Collections.IEnumerable interface. In order to use this new table in your code, you must declare a variable for it. Once you have done that, you can access the characteristics (properties and methods) of the table or its parent.

Although the data set created from the Toolbox and the one generated from creating a data source have many differences, they still share the common definition of being data sets. As mentioned earlier, a data set created from adding a data source contains the table(s) (including its (their) column(s) and record(s), if any) you would have selected.  This allows you to access any of the characteristics we studied for a data set.

The Binding Source

 

Introduction

Microsoft SQL Server does not provide user-friendly objects that a user can use to perform the necessary operations of a regular application. That is why you use Microsoft Visual Studio to create an application made of good looking Windows controls. The controls of a Windows application are meant to serve all types of applications, not just databases. If you want a Windows control of your application to use the values of your database, you must create a type of link between the control and the column of a table. This process is referred to as binding. The object that serves this connection is referred to as a binding source.

Creating a Binding Source

To support binding sources, the .NET Framework provides the BindingSource class from the System.Windows.Forms namespace. To visually create a binding source, from the Data section of the Toolbox, you can click BindingSource and click the form or container of your application. Because it is a non-visual object, its label would be positioned under the form. You can then specify its characteristics in the Properties window.

To programmatically create a binding source, you can declare a variable of type BindingSource. The class is equipped with three constructors. The default constructor allows you to simply declare the variable. Here is an example:

private void btnBindingSource_Click(object sender, EventArgs e)
{
    BindingSource bsNames = new BindingSource();
}

The Data Source of a Binding Source

If you create a binding source, obviously you must give it a name. If you create it from the Toolbox, you can accept or change its name in the Properties window. Besides its name, the second most important detail of a binding source is the list that holds its values. This list is referred to as the data source. To make the binding source as flexible as possible, the data source can be almost any type of list, including an array. In reality, any class that implements the IList interface is a candidate to be a data source.

To support data sources, the BindingSource class is equipped with a property named DataSource, which is of type object. The idea of using the vague object type indicates that many types, but not all types, of objects or lists can be used as data sources.

To programmatically specify the data source of a binding source, first create your list, then assign it to the DataSource property of your BindingSource object. Here is an example:

private void btnBindingSource_Click(object sender, EventArgs e)
{
    BindingSource bsNames = new BindingSource();
    List<string> strNames = new List<string>();

    strNames.Add("Vicky Bisso");
    strNames.Add("Amy Warren");
    strNames.Add("Chrissie Childs");
    strNames.Add("Martial Otto");

    bsNames.DataSource = strNames;
}

To visually specify the data source of a binding source, access the Properties window of your BindingSource object. In the Properties window, click DataSource and click the arrow of its combo box:

Practical Learning Practical Learning: Creating a Binding Source

  1. In the Data section of the Toolbox, click BindingSource and click the form
  2. In the Properties window, change its name to bsSolasPropertyRental
  3. Click DataSource and click the arrow its combo box
  4. Expand the Other Data Sources node and the Project Data Sources node
  5. Click dsSolasPropertyRental

The Table Adapter

 

Introduction

After creating a data set and a data source, the database that holds the values is ready to make them available to your application. The next step is to indicate to each control where its data would come from. To assist you with this task, Microsoft Visual Studio includes a tool that allows you to create an object that can retrieve the values (data) from a table and make them available to the Windows controls. This object is called a table adapter.

Creating a Table Adapter

There are various ways you can create a table adapter:

Practical Learning Practical Learning: Creating a Table Adapter

  1. While the binding source is still selected under the form, in the Properties window, click DataMember and click the arrow its combo box to select RentalProperties
  2. Under the form, click rentalPropertiesTableAdapter and, in the Properties window, change its Name to tadRentalProperties
  3. Save the form

The Binding Navigator

 

Introduction

If you drag a table from the Data Source window and drop it on a form, Microsoft Visual Studio adds the necessary Windows controls to the form and binds them to the columns of the table. To move from one record to another, you would need a way to navigate among the records. You can manually take care of this if you want. Alternatively, the .NET Framework provides a class named BindingNavigator that contains all the necessary functionality for this task.

Creating a Binding Navigator

There are various ways you can create a binding navigator:

Practical Learning Practical Learning: Creating a Binding Navigator

  1. In the Data section of the Toolbox, click BindingNavigator and click the form
  2. In the Properties window, change its Name to bnRentalProperties
  3. Still in the Properties window, click BindingSource and select bsSolasPropertyRental
  4. In the Data section of the Toolbox, click DataGridView and click the form
  5. Still in the Properties window, click DataSource and select bsSolasPropertyRental
  6. Under the Properties window, click Edit Columns and configure the columns as follows:
     
    Column HeaderText Width
    RentalPropertyID Prop ID 50
    PropertyCode Prop Code 70
    PropertyType Property Type 90
    Bedrooms Beds 50
    Bathrooms Baths 50
    MonthlyRent Monthly Rent 80
    OccupancyStatus Status 90
  7. In the Properties window, change the following characteristics:
    (Name): dgvRentalProperties
    ColumnHeadersHeightSizeMode: EnableResizing
     
    Solas Property Rental
  8. Execute the application to see the result

    Solas Property Rental - Properties Listing
  9. Close the form and return to your programming environment

The Data Adapter

 

Introduction

In the previous sections, we reviewed some of the visual tools from the Toolbox. Besides these, Microsoft Visual Studio provides other database tools not available from the Toolbox.

You probably know already that the DataSet class was developed to help you create and manage any type of list-based application. 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 of Microsoft SQL Server, Microsoft Access, or Corel Paradox, etc. This means that a list-based application lead by a DataSet 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 for a particular category of database.

Creating a SQL Data Adapter

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, itself 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 previous versions of Microsoft Visual Studio (2002 and 2003), the Toolbox was equipped with v arious 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:

Adding a SqlDataAdapter Component

After making the selection, you can click OK. This would add a SqlDataAdapter object to the Toolbox. From there, you can click the SqlDataAdapter button and click the form.

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 dadVideoCollection = new SqlDataAdapter();

Commanding a Data Adapter

To allow the data adapter to use values produced from reading a table, you must create a command object for it.

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 a connection you will use:

Data Adapter Configuration Wizard

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

Data Adapter Configuration Wizard

In the next lesson and the remainder of the lessons, we will learn how to create the first option. In Lesson 40, we will study the issues of the other two options. After making the selection in the wizard, you can click Next. The rest of the pages of the wizard propose options we will start studying in the next lesson. After creating the data adapter, Microsoft Visual Studio would create a class for it and would equipped it with the necessary accessories, such as commands.

A data adapter (such as the SqlDataAdapter class) is equipped with a property named SelectCommand ( of type SqlCommand for a SqlDataAdapter object). To specify how data would be read, you can first create a SqlCommand object that would carry a SQL statement:

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

string strVideos = "Blah Blah Blah";
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 = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

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

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

If you do not 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 = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    SqlDataAdapter dadVideoCollection = 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, you can 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 = "Blah Blah Blah";

    SqlDataAdapter dadVideoCollection = 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 = "Blah Blah Blah";
    string strConVideos = "Data Source=(local);Database='VideoCollection';Integrated Security=yes";

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

Filling a Data Set

Before using a data set in your application, you would need a DataSet object. You can declare a DataSet variable. 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 = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

    DataSet setVideos = new DataSet("VideoCollection");

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

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.

After reading data using a SqlDataAdapter object, you can used it to fill 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. The first version of this method uses the following syntax:

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. When calling this method, you can pass it a DataSet variable created as described above. 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 = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);
    DataSet setVideos = new DataSet("VideoCollection");

    dadVideoCollection.Fill(setVideos);

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

Once a DataSet contains records, you can use it as a data source for Windows controls. For example, you can use it to populate a DataGridView control. 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 = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

    DataSet setVideos = new DataSet("VideoCollection");
    dadVideoCollection.Fill(setVideos);

    dataGridView1.DataSource = setVideos;

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

Once a DataSet object has received data from a data adapter, it is made aware of the table(s), the column(s), and the record(s) that belong to the SQL statement of the data adapter. Based on this, you can bind the Windows controls of your application's form to the columns of a DataSet.

The Tables of a DataSet

The tables of a DataSet object are stored in the DataSet.Tables property that is of type DataTableCollection. After filling up a DataSet, if the selection statement of the data adapter includes only one table (in future lessons, we will see that a SQL statement can include more than one table), as done in the above data adapter, the first table of the statement can be identified with the index of 0 as in DataTableCollection[0]. If the statement includes only one table, only a 0 index can be used. As the DataTableCollection[0] value allows you to identify a table, you can retrieve any table-related information with this information. For example, you can get the object name of the table and specify it as the DataMember property of a DataGrid control. 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 = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    cnnVideos.Open();
    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

    DataSet setVideos = new DataSet("VideoCollection");
    dadVideoCollection.Fill(setVideos);

    dataGridView1.DataSource = setVideos;
    dataGridView1.DataMember = setVideos.Tables[0].TableName;

    cnnVideos.Close();
}

Remember that the DataSet.Tables[Index] value gives you access to a table as an object and you can use it as necessary.

The Columns of a Table of a DataSet

Just as you can use the filled DataSet to locate a table by its index, inside of the identified table, you can also locate a particular column you need. As reviewed in previous lessons, the columns of a table are stored in the Columns property of a DataTable object and the Columns property is of type DataColumnCollection. Each column inside of the table can be identified by its index. The first column has an index of 0. The second has an index of 1, and so on. Once you have identified a column, you can manipulate it as you see fit. In the following example, since we (behave like we) don't know the name of the second column, a message box displays that information for us:

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

    string strVideos = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    cnnVideos.Open();
    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

    DataSet setVideos = new DataSet("VideoCollection");
    dadVideoCollection.Fill(setVideos);
    dataGrid1.DataSource = setVideos;
    dataGrid1.DataMember = setVideos.Tables[0].TableName;

    DataColumn colSecond = setVideos.Tables[0].Columns[1];
    MessageBox.Show("The name of the second column is " + colSecond.ColumnName);

    cnnVideos.Close();
}
 

Updating a Record Using the Data Adapter

When visiting the records of a table using a form of your application, if you provide the means for the user to move from one record to another, if the user gets to a record and changes something in it, that record would not be automatically updated when the user moves to another record. To update a record using the data adapter, the SqlDataAdapter class inherits the Update() method from its parent the DbDataAdapter. The Update() method is overloaded with 5 versions. One of its versions uses the following syntax:

public override int Update(DataSet dataSet);

This version takes a DataSet object as argument. This means that the data adapter would read the information stored in the DataSet and update the database with it. This is probably one of the easiest or fastest means of updating data of a table.

The Records of a Table of a Dataset

After filling out a DataSet with information from a data adapter, the records of the table(s) included in the selection statement become available from the DataSet object. As reviewed in Lesson 14, the records of a table are stored in the Rows property of the table. We have already seen how to locate a table and how to identify a column. To locate a record, you can use the techniques reviewed in Lesson 14.

Data entry with a data adapter is performed just a few steps once you have properly bound the controls of your form to a DataSet object. To start, you can access the form's BindingContext property to get its BindingContext.Item property. The second version of this property allows you to specify the data source and the table name. After specifying the DataSet object that holds the records and the table that holds the data, you can first call the EndCurrentEdit() method to suspend any record editing that was going on. After this, call the AddNew() method to get the table ready for a new record. This allows the user to enter values in the Windows control.


Previous Copyright © 2008-2009 Yevol.com Next