Home

ADO.NET Visual Support: A Data Source

     

Introduction

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.

A data set is 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 Studio
  2. Create a Windows Forms Application named spr1
  3. In the Solution Explorer, right-click Form1.cs and click Rename
  4. Type RentalManagement.cs and press Enter
  5. Right-clkick the form and click Properties
  6. Click Text and type Solas Property Rental - Properties Listing
  7. 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.IO;
    using System.Data.SqlClient;
    
    namespace spr1
    {
        public partial class RentalManagement : Form
        {
            public RentalManagement()
            {
                InitializeComponent();
            }
    
            private void CreateRentalDatabase()
            {
                // We will use a directory named Solas Property Rental on the C: drive.
                // This will make it easy to delete the database and its files
                // when we don't need them anymore
                // If that directory was not yet created, create it
                Directory.CreateDirectory(@"C:\Solas Property Rental");
    
                using (SqlConnection cnnSolasPropertyRental =
                    new SqlConnection("Data Source=(local);" +
                                      "Integrated Security='SSPI';"))
                {
                    string strSolasPropertyRental =
                        "CREATE DATABASE SolasPropertyRental1 " +
                        "ON PRIMARY (NAME = RentalRecords, FILENAME = 'C:\\Solas Property Rental\\SolasPropertyRental1.mdf') " +
                        "LOG ON (NAME = RentalLog, FILENAME = 'C:\\Solas Property Rental\\SolasPropertyRental1.ldf')";
    
                    SqlCommand cmdSolasPropertyRental =
                        new SqlCommand(strSolasPropertyRental,
                            cnnSolasPropertyRental);
    
                    cnnSolasPropertyRental.Open();
                    cmdSolasPropertyRental.ExecuteNonQuery();
    
                    MessageBox.Show("The SolasPropertyRental1 database has been created",
                                    "Solas Property Rental",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (SqlConnection cnnSolasPropertyRental =
                new SqlConnection("Data Source=(local);" +
                    "Database='SolasPropertyRental1';" +
                    "Integrated Security='SSPI';"))
                {
                    string strSolasPropertyRental =
                        "CREATE TABLE Tenants " +
                        "( " +
                        " 	TenantID int identity(1,1) NOT , " +
                        " 	TenantCode nchar(16) , " +
                        " 	FullName nvarchar(50) , " +
                        " 	MaritalStatus nvarchar(40), " +
                        " 	ContactNumber nvarchar(20) " +
                        ");";
    
                    SqlCommand cmdSolasPropertyRental =
    		    new SqlCommand(strSolasPropertyRental, cnnSolasPropertyRental);
    
                    cnnSolasPropertyRental.Open();
                    cmdSolasPropertyRental.ExecuteNonQuery();
    
                    MessageBox.Show("The Tenants table has been created",
                                    "Solas Property Rental",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (SqlConnection cnnSolasPropertyRental =
                new SqlConnection("Data Source=(local);" +
                    "Database='SolasPropertyRental1';" +
                    "Integrated Security='SSPI';"))
                {
                    string strSolasPropertyRental =
                        "CREATE TABLE RentalProperties " +
                        "( " +
                        " 	RentalPropertyID int identity(1,1), " +
                        " 	PropertyCode nchar(16) , " +
                        " 	PropertyType nvarchar(32) , " +
                        "	Address nvarchar(50), " +
                        "	City  nvarchar(40), " +
                        "	State nchar(2), " +
                        "	ZIPCode nvarchar(12), " +
                        " 	Bedrooms tinyint, " +
                        " 	Bathrooms float, " +
                        "	HasCarpet bit, " +
                        "	HardWoodFloor bit, " +
                        "	IndoorGarage bit, " +
                        "	HasWasherDryer bit, " +
                        "	PetsAllowed bit, " +
                        " 	OccupancyStatus nvarchar(30)', " +
                        " 	MonthlyRent money);";
    
                    SqlCommand cmdSolasPropertyRental =
    		    new SqlCommand(strSolasPropertyRental, cnnSolasPropertyRental);
    
                    cnnSolasPropertyRental.Open();
                    cmdSolasPropertyRental.ExecuteNonQuery();
    
                    MessageBox.Show("The RentalProperties table has been created",
                                    "Solas Property Rental",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (SqlConnection cnnSolasPropertyRental =
                new SqlConnection("Data Source=(local);" +
                    "Database='SolasPropertyRental1';" +
                    "Integrated Security='SSPI';"))
                {
                    string strSolasPropertyRental =
                        "CREATE TABLE RentalAllocations " +
                        "( " +
                        " 	AllocationID int identity(1,1), " +
                        " 	AllocationCode nchar(16) , " +
                        " 	DateAllocated date, " +
                        " 	PropertyCode nchar(16) , " +
                        " 	TenantCode nchar(16) , " +
                        "   ContractLength nvarchar(50) , " +
                        " 	RentStartDate date, " +
                        " 	MonthlyRent money);";
    
                    SqlCommand cmdSolasPropertyRental =
    		    new SqlCommand(strSolasPropertyRental, cnnSolasPropertyRental);
    
                    cnnSolasPropertyRental.Open();
                    cmdSolasPropertyRental.ExecuteNonQuery();
    
                    MessageBox.Show("The RentalAllocations table has been created",
                                    "Solas Property Rental",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (SqlConnection cnnSolasPropertyRental =
                new SqlConnection("Data Source=(local);" +
                    "Database='SolasPropertyRental1';" +
                    "Integrated Security='SSPI';"))
                {
                    string strSolasPropertyRental =
                        "CREATE TABLE RentPayments " +
                        "( " +
                        " 	PaymentID int identity(1,1), " +
                        " 	ReceiptNumber nchar(16) , " +
                        " 	PaymentDate date, " +
                        " 	AllocationCode nchar(16) , " +
                        "   PaymentForMonth nvarchar(20), " +
                        "   PaymentForYear int, " +
                        " 	Amount money);";
    
                    SqlCommand cmdSolasPropertyRental =
    		    new SqlCommand(strSolasPropertyRental, cnnSolasPropertyRental);
    
                    cnnSolasPropertyRental.Open();
                    cmdSolasPropertyRental.ExecuteNonQuery();
    
                    MessageBox.Show("The RentPayments table has been created",
                                    "Solas Property Rental",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
    
            private void RentalManagement_Load(object sender, EventArgs e)
            {
                CreateRentalDatabase();
            }
        }
    }
  8. Press F5 to execute the application
  9. Click OK as many times as necessary
  10. Close the form and return to your programming environment
  11. Access the source code of the form and change it as follows:
    private void RentalManagement_Load(object sender, EventArgs e)
    {
        // CreateRentalDatabase();
    }
    
  12. Display the form

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:

  • On the main menu, you can click Data -> Add Data Source...
  • In the Data Source window:
    • You can click the Add New Data Source button Data Source
    • You can right-click a blank area in the window and click Add New Data Source
    • If the Add New Data Link appears (that is, if the current application does not have a data source yet), you can click it

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 specify whether you want to create a data set or an entity data model:

Data Source Configuration Wizard

The third 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, the Add Connection dialog box would come up. To specify the type of data source you want to use, click the Change combo box. This would display the Change Data Source dialog box:

Change Data Source

In the Change Data Source dialog box, you can select:

  • Microsoft Access Database File: Select this option if you want to use a Microsoft Access database of any version
  • Microsoft ODBC Data Source: Select this option if you had previously created a data source using the ODBC wizard from the Administrative Tools
  • Microsoft SQL Server: This is the prefered option for a Microsoft SQL Server database
  • Microsoft SQL Server Compact 3.5: This is used for mobile applications
  • Microsoft SQL Server Database File: This option is used if you will connect to a Microsoft SQL Server file that has the .mdf extension
  • Oracle Database: This is used to connect to an Oracle database

After making your selection, click OK. After specifying the type of data source, click the arrow of the Server Name combo box and select the name of the server:

Add Connection

If you are going to use a database on the same computer where you are creating the application, type (local). In the Log On To The Server section, click Windows Authentication to use the a trusted connection. If you want to use an account that must be authenticated, click the Use SQL Server Authentication radio button. In that case, you must provide a user name and a password.

Next, in the Connect to a Database section, to select a database, click the arrow of the Select or Enter a Database Name combo box to display the list of available database from the selected server. If you had selected Use SQL Server Authentication, the list would display only the databases that the user has access to. In this case, if the desired database doesn't appear in the list, you may have to first create a login account for the user for the database:

Add Connection

If you want to, or have to use a .mdf file, click the Add a Database File radio button, then click the Browse button and select the file. After making the selections, you can click Test Connection to make sure the connection is alright. Once everything is alright, click OK.

If you had selected the Use SQL Server Authentication option, the wizard would present two radio buttons that allow you specify whether you want the authentication information (the user name and the password) to be included in the connection string:

Choose a Data Source Type

After making the selection, click Next. 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 LearningPractical 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, make sure Dataset is selected and click Next
  4. In the third page of the wizard, click New Connection...
  5. In the Server Name combo box, select the server or type (local)
  6. In the Select or Enter a Database Name combo box, select SolasPropertyRental1
  7. Click Test Connection
     
    Add Connection
  8. Click OK twice
  9. On the Data Source Configuration Wizard, make sure the new connection is selected
    Click the + button of Connection String
     
    Data Source Configuration Wizard
  10. Click Next
  11. Change the connection string to csSolasPropertyRental and click Next
  12. Expand the Tables node and click the check box of RentalProperties
  13. Change the name of the data set to dsSolasPropertyRental
     
    Data Source Configuration Wizard
  14. 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 creates 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.

 

Home Copyright © 2010-2011 C#Key