Views
|
Views |
Fundamentals of Views
Introduction
Consider a table created and filled up with records as follows:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void InitiateAction();
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
Load += new EventHandler(ExerciseLoad);
}
void CreateEmployees()
{
using (SqlConnection cntExercise =
new SqlConnection("Data Source='(local)';" +
"Database='Exercise1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdEmployees =
new SqlCommand("CREATE SCHEMA Personnel;", cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
using (SqlConnection cntExercise =
new SqlConnection("Data Source='(local)';" +
"Database='Exercise1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdEmployees =
new SqlCommand("CREATE TABLE Personnel.Employees(" +
"EmployeeNumber nchar(6) not null primary key, " +
"FirstName nvarchar(20), MiddleName nvarchar(20), " +
"LastName nvarchar(20), " +
"HourlySalary smallmoney, Status nvarchar(40));" +
"INSERT INTO Personnel.Employees " +
"VALUES(N'862804', N'Christopher', NULL, N'Larsen', 14.50, NULL), " +
" (N'293747', N'Henry', N'Donald', N'Jonathan', 12.85, N'Full Time'), " +
" (N'385807', N'Lance', N'James', N'Seagal', 16.95, N'Full Time'), " +
" (N'927405', N'Paula', N'Roberta', N'Ortez', NULL, N'Full Time'), " +
" (N'790875', N'Paul', NULL, N'Swanson', 10.90, NULL), " +
" (N'384096', N'Kristopher', N'Jude', N'Michaels', 12.85, N'Part Time'), " +
" (N'385968', N'Jennifer', NULL, N'Sanders', 15.00, N'Part Time'), " +
" (N'380696', N'David', N'Peter', N'Monahan', 13.05, N'Full Time');",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
MessageBox.Show("A table named \"Employees\" has been created in the Personnel schema.",
"Exercise",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
void ExerciseLoad(object sender, EventArgs e)
{
InitiateAction initiator = CreateEmployees;
initiator();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
When studying data analysis, we saw that a query was a technique of isolating a series of columns and/or records of a table. Although this is usually done for the purpose of data analysis, it can also be done to create a new list of items for any particular reason. Most of the time, a query is created temporarily, such as during data analysis while using a table, a form, or a web page. After using such a temporary list, it is then dismissed. Many database applications, including Microsoft SQL Server, allow you to create a query and be able to save it for later use, or even to use it as if it were its own table. This is the idea behind a view.
|
Definition |
A view is a list of columns or a series of records retrieved from one or more existing tables, or as a combination of one or more views and one or more tables. Based on this, before creating a view, you must first decide where its columns and records would come from. Obviously the easiest view is one whose columns and records come from one table.
|
Fundamentals of Creating a View |
To create a view, you can use the Server Explorer (Microsoft Visual Studio), the Database Explorer, or code. Before starting the view, you would have to specify the table(s) that would be involved. To create a view from the Server Explorer, expand the database, right-click Views and click Add New View. This would open a new View window:
You will then type the necessary code. Once you are ready, click the Update button.
Like every object in Microsoft SQL Server, a view must have a name. In our lessons, here are the rules we will use to name our views:
After saving a view, it becomes part of the Views node of its database: a node would be created for it and its name would appear in the Views node of its database.
|
Creating a View in SQL |
The primary formula to programmatically create a view in SQL is:
CREATE VIEW [Schema].ViewName AS SELECT Statement
The creation of a view starts with the CREATE VIEW expression followed by an optional schema and the name of the new view. If you don't specify a schema, the default dbo will be used. The name of a view follows the rules and suggestions we reviewed for views names. After the name of the view, use the AS keyword to indicate that you are ready to define the view.
Because a view is primarily a SQL statement, it is defined using a SELECT statement, using the same rules we studied for data analysis. Here is an example of a view:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnCreateIdentifications;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnCreateIdentifications = new Button();
btnCreateIdentifications.AutoSize = true;
btnCreateIdentifications.Location = new Point(12, 12);
btnCreateIdentifications.Text = "Create Identifications";
btnCreateIdentifications.Click += new EventHandler(CreateIdentifications);
Text = "Views";
Controls.Add(btnCreateIdentifications);
}
void CreateIdentifications(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE VIEW Personnel.Identifications " +
"AS " +
"SELECT EmployeeNumber, FirstName, LastName " +
"FROM Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
MessageBox.Show("A view named \"Identifications\" has been created in the Personnel schema.",
"Exercise",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
After creating the SQL statement that defines the view, you must execute the statement. If using a query window in Microsoft SQL Server Management Studio, you can do this by pressing F5. Once the statement is executed, its name is automatically added to the Views node of its database even if you do not save its code.
|
Executing a View |
After creating a view, it shares many of the characteristics of a table. For example, a view has its own columns although the columns are actually tied to the table(s) that hold(s) the original data. Treated as a table, you can access the columns of a view using a SELECT statement. This means that you can access one, a few, or all of the columns. Here is an example that accesses all columns of a view:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
DataGridView dgvIdentifications;
delegate void InitiateAction();
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
dgvIdentifications = new DataGridView();
dgvIdentifications.Location = new Point(12, 12);
dgvIdentifications.Size = new System.Drawing.Size(270, 250);
Controls.Add(dgvIdentifications);
Load += new EventHandler(ExerciseLoad);
dgvIdentifications.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ExerciseLoad(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT * FROM Personnel.Identifications;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvIdentifications.DataSource = dsEmployees.Tables[0];
}
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
|
Encrypting a View |
When creating a view, if you want, you can encrypt its entry in the database engine. The formula to follow to do this is:
CREATE VIEW [SchemaName.]ViewName WITH ENCRYPTION AS SELECT Statement
Hee is an example:
void CreateIdentifications(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE VIEW Personnel.EmployeesIdentifications " +
"WITH ENCRYPTION " +
"AS " +
"SELECT EmployeeNumber, FirstName, LastName " +
"FROM Personnel.Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
MessageBox.Show("A view named \"Identifications\" has been created in the Personnel schema.",
"Exercise",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
|
View Maintenance |
|
Renaming a View |
After creating and executing a view, you can change its name with little worries with regards to its functionality. To programmatically rename a view, use the following formula:
sp_rename CurrentViewName, NewName;
If the view uses a schema other than dbo, you must include it in the CurrentViewName. Here is an example:
void RenameView()
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdRename = new SqlCommand(
"sp_rename N'StaffMembers', N'EmployeesNames';",
cntExercise);
cntExercise.Open();
cmdRename.ExecuteNonQuery();
}
}
|
Modifying a View |
After a view has been created, either by you or someone else, you may find out that it has an unnecessary column, it needs a missing column, it includes unnecessary records, or some records are missing. Fortunately, you can change the structure or the code of a view. This is referred to as altering a view.
The basic formula to programmatically modify a view is:
ALTER VIEW [Schema.]ViewName AS SELECT Statement
You start the alteration with the ALTER VIEW expression followed by the optional schema and the name of the view. Continue with the AS keyword and the desired code of the view. For example, you can create a SELECT statement that includes a modification of the existing code or a completely new statement. Here is an example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnAlterView;
DataGridView dgvEmployees;
delegate void InitiateAction();
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
btnAlterView = new Button();
btnAlterView.Text = "Create View";
btnAlterView.Location = new Point(12, 12);
btnAlterView.Click += new EventHandler(btnAlterViewClicked);
dgvEmployees = new DataGridView();
dgvEmployees.Location = new Point(12, 44);
dgvEmployees.Size = new System.Drawing.Size(270, 215);
Controls.Add(btnAlterView);
Controls.Add(dgvEmployees);
Load += new EventHandler(ExerciseLoad);
dgvEmployees.Anchor = AnchorStyles.Left | AnchorStyles.Top
| AnchorStyles.Right | AnchorStyles.Bottom;
}
internal void ShowRecords()
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT * FROM Personnel.Identifications;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
}
void ExerciseLoad(object sender, EventArgs e)
{
ShowRecords();
}
void btnAlterViewClicked(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"ALTER VIEW Personnel.Identifications " +
"AS " +
"SELECT EmployeeNumber, FirstName, MiddleName, LastName " +
"FROM Personnel.Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
ShowRecords();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
|
Deleting a View |
Instead of modifying a view, if you find it altogether useless, you can remove it from its database. You have various options. To delete a view, in the Server Explorer in Microsoft Visual Studio, under the Views node of the database, right-click the view and click Delete. A message box would display, asking you whether you are sure you want to delete the view. You can decide to continue or change your mind
The formula to programmatically delete a view in SQL is:
DROP VIEW [Schema.]ViewName
On the right side of the DROP VIEW expression, enter the name of the undesired view and execute the statement. You will not be warned before the interpreter deletes the view. Here is an example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnDeleteView;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnDeleteView = new Button();
btnDeleteView.AutoSize = true;
btnDeleteView.Text = "Delete View";
btnDeleteView.Click += new EventHandler(DeleteView);
Text = "Views";
Controls.Add(btnDeleteView);
}
void DeleteView(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"DROP VIEW Personnel.Identifications;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
MessageBox.Show("A view named EmployeesRecords has been deleted.",
"Exercise",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
If you are programmatically creating a Windows application, of course you can use a conditional statement to assist the user with deciding whether to continue deleting the view or not.
|
Views and Schemas |
|
Introduction |
When creating a view, you have the option of specifying its schema. In reality, like every object in the database, a view must be owned by a schema. You have many options. By default, when you create a view, if you don't specify a schema, it would be owned by dbo.
Instead of using dbo, you can use another schema of your choice and assign it to the view. If you had already created a schema in your database, you can use it or create a new schema.
|
Binding a View to its Parent |
When you have created a view, you know that the records it shows are tied to its parent table. Consider the following example:
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 BindingViews
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void btnCreateObjects_Click(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE SCHEMA Personnel;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE TABLE Personnel.Employees( " +
"EmplNbr nchar(10), FirstName nvarchar(20), " +
"LastName nvarchar(20), Salary money,FullTime bit); " +
"INSERT INTO Personnel.Employees " +
"VALUES(N'524-880', N'Barbara', N'Grisby', 14.85, 1), " +
" (N'688-364', N'Terrence', N'North', 22.05, NULL), " +
" (N'461-852', N'Michael', N'Goldsmith', 22.14, 0), " +
" (N'264-853', N'David', N'Ecker', 20.04, 1), " +
" (N'207-025', N'Julie', N'Flanell', 36.55, 1), " +
" (N'684-946', N'Kevin', N'Rhems', 15.86, NULL);",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
MessageBox.Show("A table named Employees has been created.",
"Exercise",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdIdentifications = new SqlCommand(
"CREATE VIEW Personnel.Identifications " +
"AS " +
"SELECT EmplNbr, FirstName, LastName " +
"FROM Personnel.Employees;",
cntExercise);
cntExercise.Open();
cmdIdentifications.ExecuteNonQuery();
MessageBox.Show("A view named Identifications has been created.",
"Exercise",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void btnShowRecords_Click(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT * FROM Personnel.Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdIdentifications = new SqlCommand(
"SELECT * FROM Personnel.Identifications;",
cntExercise);
cntExercise.Open();
cmdIdentifications.ExecuteNonQuery();
SqlDataAdapter sdaIdentifications = new SqlDataAdapter(cmdIdentifications);
DataSet dsIdentifications = new DataSet("IdentificationsSet");
sdaIdentifications.Fill(dsIdentifications);
dgvIdentifications.DataSource = dsIdentifications.Tables[0];
}
}
}
}
This would produce:
For one reason or another, you may have to make modifications on the parent table. For example, you may want to change the data type and/or name of a column but without touching the view(s) that depend on that table. What would happen? Consider the following example that renames a column on a table:
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 BindingViews
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void btnRenameField_Click(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdRename = new SqlCommand(
"sp_rename N'Personnel.Employees.EmplNbr', N'EmployeeNumber', N'COLUMN';",
cntExercise);
cntExercise.Open();
cmdRename.ExecuteNonQuery();
}
}
}
}
This code would execute successfully and the column would be renamed. After renaming the column, you can use it in a SELECT statement of the table:
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 BindingViews
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void btnShowRecords_Click(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT * FROM Personnel.Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
}
}
}
Since the view would still be using the previous definition of the table, in this case the previous name of the column, if the user tries executing the view, the database engine would produce an error. Here is an example:
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 BindingViews
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void btnShowRecords_Click(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdIdentifications = new SqlCommand(
"SELECT * FROM Personnel.Identifications;",
cntExercise);
cntExercise.Open();
cmdIdentifications.ExecuteNonQuery();
SqlDataAdapter sdaIdentifications = new SqlDataAdapter(cmdIdentifications);
DataSet dsIdentifications = new DataSet("IdentificationsSet");
sdaIdentifications.Fill(dsIdentifications);
dgvIdentifications.DataSource = dsIdentifications.Tables[0];
}
}
}
}
The solution is to prevent any changes on a parent table if that change would affect the view(s) that depend(s) on that table. To set this, if you are creating the view with code, before the AS keyword, add the WITH SCHEMABINDING flag. The formula to follow is:
CREATE VIEW [SchemaName.]ViewName WITH SCHEMABINDING AS SELECT Statement
Hee is an example:
CREATE VIEW Personnel.EmploymentStatus
WITH SCHEMABINDING
AS
SELECT FirstName, LastName, FullTime
FROM Personnel.Employees;
You can then execute the view when necessary. Here is an example:
SELECT * FROM Personnel.EmploymentStatus;
Now, imagine you want to change something on the parent table. For this example, try to change the data type of the FullTime column:
ALTER TABLE Personnel.Employees ALTER COLUMN FullTime int;
If you try executing the code, you would receive an error. This means that the database engine will not allow you to change something on the table if that change will make the dependent view(s) to stop working; but you can change anything on the parent table as long as no dependent view would be affected.
|
New and Existing Records on Views |
|
Data Entry Using a View |
As seen so far, a view is a selected list of records from a table. As you may suspect, the easiest view is probably one created from one table as we saw already. Here is an example:
void btnCreateView_Click(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdIdentifications = new SqlCommand(
"CREATE VIEW Personnel.Identifications " +
"AS " +
"SELECT EmployeeNumber, FirstName, LastName " +
"FROM Personnel.Employees;",
cntExercise);
cntExercise.Open();
cmdIdentifications.ExecuteNonQuery();
}
}
On such a view that is based on one table, you can perform data entry, using the view, rather than the table. To do this, you follow the same rules we reviewed for table data entry. Here is an example:
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 Exercise2
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
internal void ShowRecords()
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT * FROM Personnel.Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
}
private void Exercise_Load(object sender, EventArgs e)
{
ShowRecords();
}
private void btnAddRecord_Click(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"INSERT INTO Personnel.Identifications " +
"VALUES(N'885274', N'Sharon', N'Schultz');",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
ShowRecords();
}
}
}
If you perform data entry using a view, the data you provide would be entered on the table from which the view is based. This means that the table would be updated automatically. Based on this feature, you can create a view purposely intended to update a table so that, in the view, you would include only the columns that need to be updated. Based on that, the above code would produce:
|
Updating Records Using a View |
Just as done for data entry, you can create a view whose main role is to provide a means of changing one or a few values from a record. To start, you must create a view that lists the necessary columns. Once the view exists, when creating the UPDATE statement, instead of a table, use the columns in the view.
A View as a Virtual Table
Introduction
A view is primarily a type of table. As such, it uses most of the same functionalities of its parent object(s). Unlike tables, not all views allow data entry.
|
Views' Columns and Alias Names |
By default, when you use a SELECT statement on a view, the database engine uses the name of each column as its caption. As done for queries in data analysis, if want, you can add an alias to any or each column in the SQL statement. Here are examples:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void InitiateAction();
Button btnCreateIdentifications;
DataGridView dgvIdentifications;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
btnCreateIdentifications = new Button();
btnCreateIdentifications.AutoSize = true;
btnCreateIdentifications.Location = new Point(12, 12);
btnCreateIdentifications.Text = "Create Identifications";
btnCreateIdentifications.Click += new EventHandler(btnCreateIdentificationsClicked);
dgvIdentifications = new DataGridView();
dgvIdentifications.Location = new Point(12, 44);
dgvIdentifications.Size = new System.Drawing.Size(270, 220);
Text = "Views";
Controls.Add(dgvIdentifications);
Controls.Add(btnCreateIdentifications);
Load += new EventHandler(ExerciseLoad);
dgvIdentifications.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ShowRecords(string @object)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT * FROM " + @object + ";",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvIdentifications.DataSource = dsEmployees.Tables[0];
}
}
void ExerciseLoad(object sender, EventArgs e)
{
InitiateAction initiator = () =>
{
using (SqlConnection cntExercise =
new SqlConnection("Data Source='(local)';" +
"Database='Exercise1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdEmployees =
new SqlCommand("CREATE SCHEMA Personnel;", cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
using (SqlConnection cntExercise =
new SqlConnection("Data Source='(local)';" +
"Database='Exercise1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdEmployees =
new SqlCommand("CREATE TABLE Personnel.Employees(" +
"EmployeeNumber nchar(6) not null primary key, " +
"FirstName nvarchar(20), MiddleName nvarchar(20), " +
"LastName nvarchar(20), " +
"HourlySalary smallmoney, Status nvarchar(40));" +
"INSERT INTO Personnel.Employees " +
"VALUES(N'862804', N'Christopher', NULL, N'Larsen', 14.50, N'Full Time'), " +
" (N'293747', N'Henry', N'Donald', N'Jonathan', 12.85, N'Full Time'), " +
" (N'385807', N'Lance', N'James', N'Seagal', 16.95, N'Full Time'), " +
" (N'927405', N'Paula', N'Roberta', N'Ortez', NULL, N'Full Time'), " +
" (N'790875', N'Paul', NULL, N'Swanson', 10.90, NULL), " +
" (N'384096', N'Kristopher', N'Jude', N'Michaels', 12.85, N'Part Time'), " +
" (N'385968', N'Jennifer', NULL, N'Sanders', 15.00, N'Part Time'), " +
" (N'380696', N'David', N'Peter', N'Monahan', 13.05, N'Full Time');",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
MessageBox.Show("A table named \"Employees\" has been created in the Personnel schema.",
"Exercise",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
};
initiator();
ShowRecords("Personnel.Employees");
}
void btnCreateIdentificationsClicked(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE VIEW Personnel.Identifications " +
"AS " +
"SELECT EmployeeNumber AS Empl#, FirstName AS [First Name], LastName AS [Last Name] " +
"FROM Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
ShowRecords("Personnel.Identifications");
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
In fact, if you create an expression in a view, you must give a name to that expression. Here is an example:
void btnCreateIdentificationsClicked(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"DROP VIEW Personnel.Identifications;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE VIEW Personnel.Identifications " +
"AS " +
"SELECT EmployeeNumber AS Empl#, " +
" LastName + N', ' + FirstName AS [Full Name], " +
" MiddleName AS [Middle Name] " +
"FROM Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
ShowRecords("Personnel.Identifications");
}
This would produce:
As mentioned for a table, if you decide to create a record using a view, you are not allowed to specify a value for the expression.
|
Views and Expressions |
Although both use expressions, one of the fundamental differences between tables and views is in the way each deals with expressions. In a table, you can create an expression that would combine either one or more columns of the table and one ore more external constants, or the same columns of the same table.
Among the differences is the way the expression is created for each. In a view:
Here is an example:
CREATE VIEW Personnel.Identifications
AS
SELECT EmployeeNumber,
LastName + N', ' + FirstName
FROM Employees;
If you create an expression in a view, you must specify a name for that expression. This is done using AS followed by a name.
|
Views and Functions |
To create more complex or advanced views, you can involve functions. As always, probably the easiest functions to use are those built-in. Here is an example:
void btnCreateIdentificationsClicked(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"DROP VIEW Personnel.Identifications;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE VIEW Personnel.Identifications " +
"AS " +
" SELECT EmployeeNumber AS Empl#, " +
" FirstName AS [First Name], " +
" LEFT(MiddleName, 1) AS [MI], " +
" LastName AS [Last Name] " +
" FROM Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
ShowRecords("Personnel.Identifications");
}
This would produce:
If there is no built-in function that performs the operation you want, you can create your own function. Here is an example:
CREATE FUNCTION Personnel.GetFullName ( @FName nvarchar(20), @MName nvarchar(20), @LName nvarchar(20) ) RETURNS nvarchar(50) AS BEGIN DECLARE @strResult nvarchar(50); IF @MName IS NULL SET @strResult = @FName + N' ' + @LName ELSE SET @strResult = @FName + N' ' + @MName + N' ' + @LName; RETURN @strResult; END
Once you have a function you want to use, you can call it in the body of your view as you judge it necessary. Here is an example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
DataGridView dgvIdentifications;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
dgvIdentifications = new DataGridView();
dgvIdentifications.Location = new Point(12, 12);
dgvIdentifications.Size = new System.Drawing.Size(270, 250);
Text = "Views";
Controls.Add(dgvIdentifications);
Load += new EventHandler(ExerciseLoad);
dgvIdentifications.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ShowRecords()
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT ALL * FROM Personnel.Identifications;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvIdentifications.DataSource = dsEmployees.Tables[0];
}
}
void CreateFullName()
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees =
new SqlCommand("CREATE FUNCTION Personnel.CreateFullName( " +
"@FName nvarchar(20), @MName nvarchar(20), @LName nvarchar(20)) " +
"RETURNS nvarchar(50) " +
"AS " +
"BEGIN " +
" DECLARE @strResult nvarchar(50); " +
" IF @MName IS NULL " +
" SET @strResult = @FName + N' ' + @LName " +
" ELSE " +
" SET @strResult = @FName + N' ' + @MName + N' ' + @LName; " +
" RETURN @strResult; " +
"END;", cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
}
void ExerciseLoad(object sender, EventArgs e)
{
CreateFullName();
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE VIEW Personnel.Identifications " +
"AS " +
"SELECT EmployeeNumber AS Empl#, " +
" Personnel.CreateFullName(FirstName, MiddleName, LastName) AS [Full Name] " +
"FROM Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
ShowRecords();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
|
Views and Joins |
As seen in our introduction to joins, you can create a view that involves more than one table or more than one view. Here is an example:
private void FunDepartmentStore_Load(object sender, EventArgs e)
{
// CreateDatabase();
SqlConnection cntFunDepartmentStore = null;
SqlCommand cmdFunDepartmentStore = null;
using (cntFunDepartmentStore =
new SqlConnection("Data Source=(local);" +
"Database='FunDS1a';" +
"Integrated Security=Yes"))
{
// This view shows an inventory of the items sold and not yet sold in the store.
// Unlike its parent table, this view shows the names of manufacturers and the categories of items.
cmdFunDepartmentStore =
new SqlCommand("CREATE VIEW Inventory.StoreInventory " +
"AS " +
"SELECT Inventory.StoreItems.ItemNumber, " +
" Inventory.StoreItems.DateEntered, " +
" Inventory.Manufacturers.Manufacturer, " +
" Inventory.Categories.Category, " +
" Inventory.SubCategories.SubCategory, " +
" Inventory.StoreItems.ItemName, " +
" Inventory.StoreItems.Size, " +
" Inventory.StoreItems.UnitPrice, " +
" Inventory.StoreItems.DiscountRate, " +
" Inventory.StoreItems.SaleStatus " +
"FROM Inventory.Categories " +
"INNER JOIN Inventory.StoreItems " +
" ON Inventory.Categories.CategoryID = Inventory.StoreItems.CategoryID " +
"INNER JOIN Inventory.Manufacturers " +
" ON Inventory.StoreItems.ManufacturerID = Inventory.Manufacturers.ManufacturerID " +
"INNER JOIN Inventory.SubCategories " +
" ON Inventory.StoreItems.SubCategoryID = Inventory.SubCategories.SubCategoryID;",
cntFunDepartmentStore);
cntFunDepartmentStore.Open();
cmdFunDepartmentStore.ExecuteNonQuery();
}
MessageBox.Show("The view has been created.",
"Fun Department Store",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
|
Views and Data Analysis |
|
Introduction |
The primary goal of a view is to hold a query that can be used over and over again. For this reason, a view is created from a SELECT statement. When creating a view, you can add as many columns as you want as long as those columns were already created in the table that holds the original records. As done for data analysis of a table, when you execute a view, you can select just one column from it. Here is an example:
SELECT EmployeeNumber FROM Personnel.Identifications; GO
In the same way, you can select as many columns as you want.
|
Introduction to Selecting Records in a View |
By default, when you execute a view, you would get all records. If you want, you can use TOP to specify a percentage of, or a number of, first records to select. Here is an example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void RecordSelector();
DataGridView dgvIdentifications;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
dgvIdentifications = new DataGridView();
dgvIdentifications.Location = new Point(12, 12);
dgvIdentifications.Size = new System.Drawing.Size(270, 250);
Text = "Views";
Controls.Add(dgvIdentifications);
Load += new EventHandler(ExerciseLoad);
dgvIdentifications.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ExerciseLoad(object sender, EventArgs e)
{
// There is no reason to use a delegate here, just for fun
RecordSelector ShowRecords = () =>
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT TOP 5 * FROM Personnel.Identifications;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvIdentifications.DataSource = dsEmployees.Tables[0];
}
};
ShowRecords();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
|
Views and Conditions |
Besides adding columns to a view, you can set a condition to restrict the resulting records. Of course, a condition is set using the WHERE keyword. To do this:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
DataGridView dgvIdentifications;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
dgvIdentifications = new DataGridView();
dgvIdentifications.Location = new Point(12, 12);
dgvIdentifications.Size = new System.Drawing.Size(270, 250);
Text = "Views";
Controls.Add(dgvIdentifications);
Load += new EventHandler(ExerciseLoad);
dgvIdentifications.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ShowRecords()
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT ALL * FROM Personnel.FullTimeEmployees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvIdentifications.DataSource = dsEmployees.Tables[0];
}
}
void ExerciseLoad(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE VIEW Personnel.FullTimeEmployees " +
"AS " +
"SELECT EmployeeNumber AS Empl#, " +
" LastName + N', ' + FirstName AS [Full Name], " +
" HourlySalary AS Salary, Status " +
"FROM Employees " +
"WHERE Status = N'Full Time';",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
ShowRecords();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
When executing a view that has a condition, you can simply apply the SELECT keyword to the name of the view as seen above. Just as done for tables, when SELECTing records from a view, whether it already has a condition or not, you can specify a condition to restrict the records it produces. Here is an example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
DataGridView dgvIdentifications;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
dgvIdentifications = new DataGridView();
dgvIdentifications.Location = new Point(12, 12);
dgvIdentifications.Size = new System.Drawing.Size(270, 250);
Text = "Views";
Controls.Add(dgvIdentifications);
Load += new EventHandler(ExerciseLoad);
dgvIdentifications.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ShowRecords()
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT ALL * FROM Personnel.FullTimeEmployees " +
"WHERE Empl# LIKE N'38%';", // Where the employee number starts with 38
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvIdentifications.DataSource = dsEmployees.Tables[0];
}
}
void ExerciseLoad(object sender, EventArgs e)
{
ShowRecords();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
Notice a different number of records.
All the rules we reviewed for data analysis on tables are also available on views:
CREATE VIEW Registration.UnknownParents
AS
SELECT FirstName,
LastName,
Gender,
ParentsNames
FROM Registration.Students
WHERE ParentsNames IS NULL;
GO
CREATE VIEW Registration.Girls
AS
SELECT FirstName,
LastName,
DateOfBirth,
ParentsNames
FROM Registration.Students
WHERE Gender = N'female';
GO
CREATE VIEW Registration.StudentsInSilverSpring
AS
SELECT FirstName,
LastName,
Gender,
ParentsNames
FROM Registration.Students
WHERE City = N'silver spring';
GO
USE Exercise;
GO
CREATE VIEW LastNamesThatIncludeAN
AS
SELECT [First Name],[Last Name], Salary
FROM Employees
WHERE [Last Name] LIKE N'%an%';
GO
USE Exercise;
GO
CREATE VIEW Personnel.EmployeesWhoEarnMoreThan15Dollars
AS
SELECT [Last Name], [First Name], Salary
FROM Personnel.Employees
WHERE Salary !< 15.00;
GO
CREATE VIEW Registration.StudentsInASingleParentHome
AS
SELECT FirstName,
LastName,
DateOfBirth,
EmergencyName,
EmergencyPhone
FROM Registration.Students
WHERE SingleParentHome = 1;
GO
CREATE VIEW Registration.StudentsBornIn1995
AS
SELECT FirstName,
LastName,
DateOfBirth,
EmergencyName,
EmergencyPhone
FROM Registration.Students
WHERE DateOfBirth >= N'01/01/1995';
GO
CREATE VIEW Registration.EmergencyInformation
AS
SELECT FirstName,
LastName,
Gender,
DateOfBirth
FROM Registration.Students
WHERE (EmergencyName IS NOT NULL) AND (EmergencyPhone IS NOT NULL);
GO
You cannot specify an option to sort records in a view unless the SELECT statement includes a TOP.
|
Selecting Distinct Records in a View |
Consider the following view:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void InitiateAction();
Button btnCreateIdentifications;
DataGridView dgvEmployees;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
btnCreateIdentifications = new Button();
btnCreateIdentifications.AutoSize = true;
btnCreateIdentifications.Location = new Point(12, 12);
btnCreateIdentifications.Text = "Create Identifications";
btnCreateIdentifications.Click += new EventHandler(btnCreateIdentificationsClicked);
dgvEmployees = new DataGridView();
dgvEmployees.Location = new Point(12, 44);
dgvEmployees.Size = new System.Drawing.Size(270, 220);
Text = "Views";
Controls.Add(dgvEmployees);
Controls.Add(btnCreateIdentifications);
Load += new EventHandler(ExerciseLoad);
dgvEmployees.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ExerciseLoad(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
InitiateAction initiator = () =>
{
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees =
new SqlCommand("CREATE SCHEMA Personnel;", cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
using (SqlConnection cntExercise =
new SqlConnection("Data Source='(local)';" +
"Database='Exercise1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdEmployees =
new SqlCommand("CREATE TABLE Personnel.Employees(EmployeeNumber nchar(10), [First Name] nvarchar(20), " +
"[Last Name] nvarchar(20), Salary money, [Full Time?] bit); " +
"INSERT INTO Personnel.Employees " +
"VALUES(N'29730', N'Philippe', N'Horsford', 20.05, 1), " +
" (N'28084', N'Joan', N'Shepherd', 12.72, NULL), " +
" (N'44179', NULL, N'Shepherd', 10.59, 1), " +
" (N'27924', N'Gregory', N'Hope', 12.85, 1), " +
" (N'79272', N'Joshua', N'Anderson', 18.26, 2), " +
" (N'22803', N'Gregory', N'Swanson', 15.95, NULL), " +
" (N'39742', NULL, N'Anders', 8.88, 2), " +
" (N'83084', N'Josephine', N'Anderson', 20.02, 1), " +
" (N'51508', N'James', N'Anders', 18.26, 1), " +
" (N'92485', N'John', N'Anderson', 12.49, NULL);;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
MessageBox.Show("A table named \"Employees\" has been created in the Personnel schema.",
"Exercise",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
};
initiator();
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT ALL * FROM Personnel.Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
}
void btnCreateIdentificationsClicked(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE VIEW Personnel.Identifications " +
"AS " +
"SELECT [First Name], [Last Name], Salary " +
"FROM Personnel.Employees;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT [Last Name] FROM Personnel.Identifications;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
Notice the number of records. If you have records that have a repeating value in a view, when selecting those records, you can ask the database engine to select them distinctively. This is done by using the DISTINCT. Here is an example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void InitiateAction();
DataGridView dgvEmployees;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
dgvEmployees = new DataGridView();
dgvEmployees.Location = new Point(12, 12);
dgvEmployees.Size = new System.Drawing.Size(270, 250);
Text = "Views";
Controls.Add(dgvEmployees);
Load += new EventHandler(ExerciseLoad);
dgvEmployees.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ExerciseLoad(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
InitiateAction ShowRecords = () =>
{
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT DISTINCT [Last Name] FROM Personnel.Identifications;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
};
ShowRecords();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
|
Views in Joins and Data Analysis |
After adding tables to a view, you can set a criterion by which the records would be selected and kept. If you are working visually, you must first select a column in the desired table or view, then use its corresponding box in the Filter column of the Criteria pane. Of course, you can manually write code in the SQL pane.
If you are writing code, you can also add a WHERE condition to your SELECT statement using the field(s) of your choice and/or a condition of your choice.
|
Data Maintenance Using a View |
|
Introduction |
You can use a view to perform such operations as updating records or removing records. To take care of such actions, the view must be based on only one table. Here is an example of such a view:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void InitiateAction();
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
Load += new EventHandler(ExerciseLoad);
}
void CreateObjects()
{
InitiateAction create = () =>
{
using (SqlConnection cntExercise =
new SqlConnection("Data Source='(local)';" +
"Database='Exercise1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdEmployees =
new SqlCommand("CREATE SCHEMA Personnel;", cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
using (SqlConnection cntExercise =
new SqlConnection("Data Source='(local)';" +
"Database='Exercise1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdEmployees =
new SqlCommand("CREATE TABLE Personnel.EmploymentStatus( " +
"StatusID int identity(1, 1), [Status] nvarchar(30) not null, " +
"Constraint PK_EmploymentStatus Primary Key(StatusID)); " +
"CREATE TABLE Personnel.Employees( " +
"[Empl'] nchar(6) not null, [First Name] nvarchar(20), " +
"[Last Name] nvarchar(20), Salary money, EmplStatus int null " +
"Constraint FK_EmploymentStatus Foreign Key " +
" References Personnel.EmploymentStatus(StatusID), " +
"Constraint PK_Employees Primary Key([Empl'])); " +
"INSERT Personnel.EmploymentStatus([Status]) " +
"VALUES(N'Full Time'), (N'Part Time'), (N'Contractor'), (N'Unknown'); " +
"INSERT INTO Personnel.Employees " +
"VALUES(N'29-730', N'Philippe', N'Horsford', 20.05, 1), " +
" (N'28-084', N'Joan', N'Shepherd', 12.72, NULL), " +
" (N'44-179', NULL, N'Shepherd', 10.59, 1), " +
" (N'27-924', N'Gregory', N'Hope', 12.85, 1), " +
" (N'79-272', N'Joshua', N'Anderson', 18.26, 2), " +
" (N'22-803', N'Gregory', N'Swanson', 15.95, NULL), " +
" (N'39-742', NULL, N'Anders', 8.95, 2), " +
" (N'83-084', N'Josephine', N'Anderson', 20.02, 1), " +
" (N'51-508', N'James', N'Anders', 18.26, 1), " +
" (N'92-485', N'John', N'Anderson', 12.49, NULL);",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
using (SqlConnection cntExercise =
new SqlConnection("Data Source='(local)';" +
"Database='Exercise1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE VIEW Personnel.PartTimers " +
"AS " +
"SELECT [Empl'], [First Name], [Last Name], EmplStatus " +
"FROM Personnel.Employees " +
"WHERE EmplStatus = 2;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
MessageBox.Show("The necessady objects have been created.",
"Exercise",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
};
create();
}
void ExerciseLoad(object sender, EventArgs e)
{
CreateObjects();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
|
Updating Records Using a View |
The operation of updating records using a view follows the same rules as those of a table. As you may know already, you can create a view that has a condition and only the records that follow that condition would be included in the result. The above view would produce:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void InitiateAction();
DataGridView dgvEmployees;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
dgvEmployees = new DataGridView();
dgvEmployees.Location = new Point(12, 12);
dgvEmployees.Size = new System.Drawing.Size(270, 250);
Text = "Views";
Controls.Add(dgvEmployees);
Load += new EventHandler(ExerciseLoad);
dgvEmployees.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ExerciseLoad(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
// We are using a delegate here just for fun
InitiateAction ShowRecords = () =>
{
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT ALL * FROM Personnel.PartTimers;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
};
ShowRecords();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
Notice the number of records. There are two ways you can update data: using a table or using a view. We have already seen how to update records using a table. If you use a table, after updating the records, the view would show the result. Consider the following example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void InitiateAction();
DataGridView dgvEmployees;
Button btnUpdateEmployees;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
btnUpdateEmployees = new Button();
btnUpdateEmployees.AutoSize = true;
btnUpdateEmployees.Location = new Point(12, 12);
btnUpdateEmployees.Text = "Update Employees";
btnUpdateEmployees.Click += new EventHandler(btnUpdateEmployeesClicked);
dgvEmployees = new DataGridView();
dgvEmployees.Location = new Point(12, 44);
dgvEmployees.Size = new System.Drawing.Size(270, 220);
Text = "Views";
Controls.Add(dgvEmployees);
Controls.Add(btnUpdateEmployees);
Load += new EventHandler(ExerciseLoad);
dgvEmployees.Anchor = AnchorStyles.Left | AnchorStyles.Top
| AnchorStyles.Right | AnchorStyles.Bottom;
}
void ShowRecords()
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
// We are using a delegate here just for fun
InitiateAction display = () =>
{
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT ALL * FROM Personnel.PartTimers;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
};
display();
}
void ExerciseLoad(object sender, EventArgs e)
{
ShowRecords();
}
void btnUpdateEmployeesClicked(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"UPDATE Personnel.Employees " +
"SET EmplStatus = 2 " +
"WHERE Empl# = N'22-803'; ",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
ShowRecords();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
In the same way, you can update records using a view. The advantage is that the view already contains a condition. You can simply SET the desired value. Here is an example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void InitiateAction();
DataGridView dgvEmployees;
Button btnUpdateEmployees;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnUpdateEmployees = new Button();
btnUpdateEmployees.AutoSize = true;
btnUpdateEmployees.Location = new Point(12, 12);
btnUpdateEmployees.Text = "Update Employees";
btnUpdateEmployees.Click += new EventHandler(btnUpdateEmployeesClicked);
dgvEmployees = new DataGridView();
dgvEmployees.Location = new Point(12, 44);
dgvEmployees.Size = new System.Drawing.Size(270, 220);
Text = "Views";
Controls.Add(dgvEmployees);
Controls.Add(btnUpdateEmployees);
Load += new EventHandler(ExerciseLoad);
dgvEmployees.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ShowRecords()
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
// We are using a delegate here just for fun
InitiateAction display = () =>
{
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT ALL * FROM Personnel.PartTimers;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
};
display();
}
void ExerciseLoad(object sender, EventArgs e)
{
ShowRecords();
}
void btnUpdateEmployeesClicked(object sender, EventArgs e)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"UPDATE Personnel.PartTimers " +
"SET [First Name] = '[Not Available]' " +
"WHERE [First Name] IS NULL; ",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
ShowRecords();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
|
Checking the Condition on a View Before Updating |
As mentioned already, if you update records using a view, the table would be updated. As a result, you use (a) criteria (criterion) in a view, the view's result may not show the record(s) that was (were) lost. If you decide to update one or more records using a view, you can ask the database engine to first check whether one or more records in the view would be lost.
To programmatically apply a check, add a WITH CHECK OPTION flag before the end of the statement. The formula to follow would be:
CREATE VIEW [SchemaName.]ViewName AS SELECT Statement WITH CHECK OPTION
Here is an example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void InitiateAction();
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
Text = "Views";
Load += new EventHandler(ExerciseLoad);
}
void CreateView()
{
InitiateAction create = () =>
{
using (SqlConnection cntExercise =
new SqlConnection("Data Source='(local)';" +
"Database='Exercise1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE VIEW Personnel.EarnLessThanMinimumWage " +
"AS " +
" SELECT Empl#, [First Name], [Last Name], Salary " +
" FROM Personnel.Employees " +
" WHERE Salary !> 12.50 " +
" WITH CHECK OPTION;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
MessageBox.Show("The necessady objects have been created.",
"Exercise",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
};
create();
}
void ExerciseLoad(object sender, EventArgs e)
{
CreateView();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
If the view was created already and you want to add the checking process to it, using code, ALTER the view and add a WITH CHECK OPTION flag at the end of the statement. The formula to follow would be:
ALTER VIEW [SchemaName.]ViewName AS SELECT Statement WITH CHECK OPTION
Here is an example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void InitiateAction();
DataGridView dgvEmployees;
Button btnCreateView;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnCreateView = new Button();
btnCreateView.AutoSize = true;
btnCreateView.Location = new Point(12, 12);
btnCreateView.Text = "Create View";
btnCreateView.Click += new EventHandler(btnUpdateEmployeesClicked);
dgvEmployees = new DataGridView();
dgvEmployees.Location = new Point(12, 44);
dgvEmployees.Size = new System.Drawing.Size(270, 220);
Text = "Views";
Controls.Add(dgvEmployees);
Controls.Add(btnCreateView);
Text = "Views";
Load += new EventHandler(ExerciseLoad);
dgvEmployees.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ShowRecords(string @object)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
// We are using a delegate here just for fun
InitiateAction display = () =>
{
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT ALL * FROM " + @object + ";",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
};
display();
}
void btnUpdateEmployeesClicked(object sender, EventArgs e)
{
InitiateAction create = () =>
{
using (SqlConnection cntExercise =
new SqlConnection("Data Source='(local)';" +
"Database='Exercise1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdEmployees = new SqlCommand(
"CREATE VIEW Personnel.EarnLessThanMinimumWage " +
"AS " +
" SELECT Empl#, [First Name], [Last Name], Salary " +
" FROM Personnel.Employees " +
" WHERE Salary !> 12.50 " +
" WITH CHECK OPTION;",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
MessageBox.Show("The necessady objects have been created.",
"Exercise",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
};
create();
ShowRecords("Personnel.EarnLessThanMinimumWage");
}
void ExerciseLoad(object sender, EventArgs e)
{
ShowRecords("Personnel.Employees");
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
After adding this flag, if you update one or more records using the view, if the updated record(s) is under the condition specified in the view, the update will work just fine. Here is an example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
delegate void InitiateAction();
DataGridView dgvEmployees;
Button btnUpdateEmployees;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnUpdateEmployees = new Button();
btnUpdateEmployees.AutoSize = true;
btnUpdateEmployees.Location = new Point(12, 12);
btnUpdateEmployees.Text = "Update Employees";
btnUpdateEmployees.Click += new EventHandler(btnUpdateEmployeesClicked);
dgvEmployees = new DataGridView();
dgvEmployees.Location = new Point(12, 44);
dgvEmployees.Size = new System.Drawing.Size(270, 220);
Text = "Views";
Controls.Add(dgvEmployees);
Controls.Add(btnUpdateEmployees);
Text = "Views";
Load += new EventHandler(ExerciseLoad);
dgvEmployees.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void ShowRecords(string @object)
{
SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();
csbExercise.DataSource = "(local)";
csbExercise.InitialCatalog = "Exercise1";
csbExercise.IntegratedSecurity = true;
// We are using a delegate here just for fun
InitiateAction display = () =>
{
using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand(
"SELECT ALL * FROM " + @object + ";",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
dgvEmployees.DataSource = dsEmployees.Tables[0];
}
};
display();
}
void btnUpdateEmployeesClicked(object sender, EventArgs e)
{
InitiateAction create = () =>
{
using (SqlConnection cntExercise =
new SqlConnection("Data Source='(local)';" +
"Database='Exercise1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdEmployees = new SqlCommand(
"UPDATE Personnel.EarnLessThanMinimumWage " +
"SET Salary = 12.25 " +
"WHERE [Empl#] = N'44-179';",
cntExercise);
cntExercise.Open();
cmdEmployees.ExecuteNonQuery();
}
};
create();
ShowRecords("Personnel.EarnLessThanMinimumWage");
}
void ExerciseLoad(object sender, EventArgs e)
{
ShowRecords("Personnel.Employees");
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
On the other hand, if you call the view to update the record(s) and if at least one record that must be changed is outside the condition specified in the condition of the view, the update will be dismissed. Here is an example:
UPDATE Personnel.EarnLessThanMinimumWage SET Salary = 14.05 WHERE EmplNbr = N'92485';
If you perform the same operation using a table, it would work fine and there would not be an error.
|
Deleting Records Using a View |
You can remove records from a database using either a table or a view. We already know how to delete records using a table. When it comes to a view, you can first create one that includes a condition that isolates one or more records.
If you delete a record using a table, a view that depends on that table will not show that record anymore. As seen for updating records, if you create a view that has a condition and you decide to delete the record(s) that follow the condition(s) in that view, you can simply call DELETE on that view and all of the records that view shows would be removed from the table.