 |
ADO.NET Visual Support: A Data Source |
|
|
|
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
Learning: Introducing Visual Database Support
|
|
- Start Microsoft Visual Studio
- Create a Windows Forms Application named spr1
- In the Solution Explorer, right-click Form1.cs and click Rename
- Type RentalManagement.cs and press Enter
- Right-clkick the form and click Properties
- Click Text and type Solas Property Rental - Properties
Listing
- 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();
}
}
}
- Press F5 to execute the application
- Click OK as many times as necessary
- Close the form and return to your programming environment
- Access the source code of the form and change it as follows:
private void RentalManagement_Load(object sender, EventArgs e)
{
// CreateRentalDatabase();
}
- Display the form
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
- 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
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:
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:
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:
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:
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:
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:
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 Learning: Adding a Data Source
|
|
- On the main menu, click Data -> Add New Data Source...
- On the first page of the wizard, make sure Database is selected and
click Next
- In the second page of the wizard, make sure Dataset is selected and
click Next
- In the third page of the wizard, click New Connection...
- In the Server Name combo box, select the server or type
(local)
- In the Select or Enter a Database Name combo box, select
SolasPropertyRental1
- Click Test Connection
- Click OK twice
- On the Data Source Configuration Wizard, make sure the new
connection is selected
Click the + button of Connection String
- Click Next
- Change the connection string to csSolasPropertyRental
and click Next
- Expand the Tables node and click the check box of RentalProperties
- Change the name of the data set to dsSolasPropertyRental
- 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.
|
|