Home

Database Visual Support: A Data Source

 

Introduction

So far, we have introduced and used to 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.

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 SolasPropertyRental5
  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.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace SolasPropertyRental5
    {
        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:

  • 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 (this is if the current application does not have a data source yet), you can click it

The first page of the Data Source Configuration 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

If you want to use, and select, an existing connection, you can 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 others) 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 others). 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, 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.

 

Published on Friday 04 January 2008


Home Copyright © 2007 FunctionX, Inc. Next