Home

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:

Add Table

You will then type the necessary code. Once you are ready, click the Update button.

The Name of a View

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:

  • A name will start with a letter
  • After the first letter, the name will have combinations of underscores, letters, and digits. Examples are n24, act_52_t
  • A name will not include special characters such as !, @, #, $, %, ^, &, or *
  • A name will not have spaces
  • If the name is a combination of words, each word will start in uppercase

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:

Executing a View

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;
    }
}

Altering a View

Altering a View

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:

Binding a View to its Parent

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];
            }
        }
    }
}

Binding a View to its Parent

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];
            }
        }
    }
}

Binding a View to its Parent

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:

Data Entry Using a View

Data Entry Using a View

 

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:

Views and Expressions

Views and Expressions

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:

Views and Expressions

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:

  • An expression is also created by combining columns with other columns or columns with constants
  • The expression is a member of a SELECT statement

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:

Views and Functions

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 Functions

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 Functions

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:

  • If you are creating a new view, add a WHERE condition. 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;",
                        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:

    Views and Conditions

  • If the view exists already, you can still ALTER it and add or modify the condition

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:

Views and Conditions

Notice a different number of records.

All the rules we reviewed for data analysis on tables are also available on views:

  • Creating a view that includes records with null values on a certain field. Here is an example (the SQL code ROSH database is available):
    CREATE VIEW Registration.UnknownParents
    AS
    SELECT FirstName,
           LastName,
           Gender,
           ParentsNames
    FROM Registration.Students
    WHERE ParentsNames IS NULL;
    GO
  • Creating a view that includes a condition tied to a column but omit that column in the result. Here is an example:
    CREATE VIEW Registration.Girls
    AS
    SELECT FirstName,
           LastName,
           DateOfBirth,
           ParentsNames
    FROM Registration.Students
    WHERE Gender = N'female';
    GO
  • Creating a view that has a condition that compares strings based on a certain field. Here is an example:
    CREATE VIEW Registration.StudentsInSilverSpring
    AS
    SELECT FirstName,
           LastName,
           Gender,
           ParentsNames
    FROM Registration.Students
    WHERE City = N'silver spring';
    GO
  • Creating a view that uses a pattern in the condition. Here is an example:
    USE Exercise;
    GO
    CREATE VIEW LastNamesThatIncludeAN
    AS
    SELECT [First Name],[Last Name], Salary
    FROM Employees
    WHERE [Last Name] LIKE N'%an%';
    GO
  • Creating a view that uses a condition based on a number-based field. Here is an example:
    USE Exercise;
    GO
    CREATE VIEW Personnel.EmployeesWhoEarnMoreThan15Dollars
    AS
    SELECT [Last Name], [First Name], Salary
    FROM Personnel.Employees
    WHERE Salary !< 15.00;
    GO
  • Creating a view that includes a condition based on a Boolean field. Here is an example:
    CREATE VIEW Registration.StudentsInASingleParentHome
    AS
    SELECT FirstName,
           LastName,
           DateOfBirth,
           EmergencyName,
           EmergencyPhone
    FROM Registration.Students
    WHERE SingleParentHome = 1;
    GO
  • Creating a view that uses a condition based on a date/time-based field. Here is an example:
    CREATE VIEW Registration.StudentsBornIn1995
    AS
    SELECT FirstName,
           LastName,
           DateOfBirth,
           EmergencyName,
           EmergencyPhone
    FROM Registration.Students
    WHERE DateOfBirth >= N'01/01/1995';
    GO
  • Creating a view that combines conditions. Here is an example:
    CREATE VIEW Registration.EmergencyInformation
    AS
    SELECT FirstName,
           LastName,
           Gender,
           DateOfBirth
    FROM Registration.Students
    WHERE (EmergencyName IS NOT NULL) AND (EmergencyPhone IS NOT NULL);
    GO
  • Creating a view that includes a BETWEEN or an IN operation
  • Creating a view that negates any of the above conditions

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:

Views and Conditions

Views and Conditions

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 and Data Analysis

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;
    }
}

Updating Records Using a View

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:

Updating Records Using a View

Updating Records Using a View

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:

Updating Records Using a View

Updating Records Using a View

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;
    }
}

Updating Records Using a View

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;
    }
}

Updating Records Using a View

Updating Records Using a View

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;
    }
}

Updating Records Using a View

Updating Records Using a View

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.

 
 
   
 

Home Copyright © 2014, FunctionX