Learn MVC Step By Step Lab-1

Updatable View in SQL By Sagar Jaybhay

In this article we will understand how to Update view or What it means In SQL Server By Sagar Jaybhay.

Before Proceeding To this article Please Read Part 1 - https://sagarjaybhay.com/view-in-sql-by-sagar-jaybhay-2020/

Updatable View in SQL Server

By using view it is possible to update the base table.

By using view it is possible to update the base table.

Updatable View 2

Query for this

update empwithdepartment set full_name='flower blossam' where EmpID=2;

Updatable View 3

See the above image you will see the result the data is updated but it not present in view. It is updated in the underlying table so If you want to find this whether it is updated or not fire below query.

select top 4 * from empwithdepartment;

Updatable View 4

In
the above image, you will find the base table also gets updated.

Like the update, you can also delete data from view or insert data into view which turn deleted or inserted in the underlying table.

Updatable View 5

But in this case, we got an error why.

Msg 4405,
Level 16, State 1, Line 35

View or function 'empwithdepartment' is not updatable because the modification affects multiple base tables.

Because
our view is based on multiple tables so we are not able to insert records in
view or it might update records incorrectly but if our view is based on a
single table then we are able to add records in view which in turn added in a
base table.

When you trying to insert data into view and view finds that multiple tables contain these fields so it gets confused and throws an error.

Updatable View 6

In the above figure, you will understand we create a view that is based on a single table and we are able to insert records using the view.

create view simpleview
as
select EmpID,full_name,Salary,Gender from Employee where Gender='Male';

insert into simpleview values(1002,'Sagara',20000,'Male');

select * from simpleview where EmpID>1000;
select * from Employee where EmpID=1002;

View in SQL By Sagar Jaybhay 2020

In this article Sagar Jaybhay explain what is View. What is the use of View. what is the advantages of views in SQL server.

View

The view is saved SQL query or we can call it a virtual table.

View In SQL 1

We have these 2 tables and by joining these 2 tables we want to output.

View In SQL 2

By joining these 2 tables we get the above result. Now we want to create a view, like other create statement like create a table, create procedure we have to create view statement

Below is a query for creating a view

create view empwithdepartment
as
select e.EmpID,e.full_name,e.Salary,e.Gender,d.Department_Name from Employee as e
join Department as d on d.DepartmentID=e.DepartmentID;

View In SQL 3

To find a view in the database refer below image

How to find view

If you check the above query it just selects query and if you want to get data from the view you will able to treat it as a table and simple select * from view_name; by using this you can get data.

View doesn’t store any data and it is just saved select query.

Advantages of views

1. It is used to reduce the complexity of database schema
2. It provides a mechanism to implement column level and row-level security means if you want to give access to certain users with a limited number of rows and columns then put your query in view and given that view name to end-user so that he performs the operation on that considering this is a table. By doing this end-user doesn’t know the underlying base table.
3. It Is used to present aggregated data or detailed data.

To alter view you can use alter view syntax

Alter view view_name
As
-	your query syntax here

To drop the view you can use

Drop view view_name;

---

GitHub : https://github.com/Sagar-Jaybhay

---

Test Post from Sagar Jaybhay

Test Post from Sagar Jaybhay
https://sagarjaybhay.com

What is Idisposable interface and Finalize Dispose pattern By Sagar Jaybhay

Garbagecollector only cleans managed code and if any unmanaged code it finds it doesn’t reclaim its memory so to remove unused objects we need something. In this case, at least we have a responsibility to call these kinds of which free unmanaged code objects. In C# application there is a place where we can manage these unmanaged objects and this place is a destructor.

But in Garbage collection destructor creates some problem which we solve by using an IDisposable Dispose pattern.

When I write destructor then more and more objects are created in Gen1 and Gen2 so which is not good in performance point of view

In the above image, most of the objects are created in gen2. And code for this is below

public partial class Form1: Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            for (int i = 0; i < 1000000; i++)

            {

                XYZ x = new XYZ();

            }

            MessageBox.Show("Completed");

        }

    }

    class XYZ

    {

        public int i { get; set; }

        public string str { get; set; }

        public void append()

        {

            for (int i = 0; i < 1000; i++)

            {

                str += i;

            }

        }

        ~XYZ() { }

    }

In below class, we implement IDisposable pattern

public partial class Form1: Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            for (int i = 0; i < 0000001; i++)

            {

                XYZ x = new XYZ();

                x.Dispose();

            }

            MessageBox.Show("Completed");

        }

    }

    class XYZ: IDisposable

    {

        public int i { get; set; }

        public string str { get; set; }

        public void append()

        {

            for (int i = 0; i < 1000; i++)

            {

                str += i;

            }

        }

        ~XYZ() {

            //release unmanaged objects

            Dispose(false);

        }

        private void ReleaseUnmanagedResources()

        {

            // TODO release unmanaged resources here

        }

        protected virtual void Dispose(bool disposing)

        {

            ReleaseUnmanagedResources();

            if (disposing)

            {

            }

        }

        public void Dispose()

        {

            Dispose(true);

            GC.SuppressFinalize(this);

        }

    }

 GC.SuppressFinalize(this);

This will suppress any kind of destructor and clean object.