We help IT Professionals succeed at work.
Get Started

Deleting Table Views

JenebyM
JenebyM asked
on
313 Views
Last Modified: 2021-04-21
using System.Data.SqlClient;

The application we have creates sql views dynamically based on the particular users web session ID. The view is then used as the data source for the crystal report reqested. This part of the apps works fine and ensures data requested is unique to a users session and another user cannot "accidentaly" view their information if running the same report at the same time.

All the views created by the application in this way are named vw_nsXXX_yyyyyyyy where XXX is the report and yyyyyy is the session ID (see image below).

A hundred views can be created in this way on a given day. To reduce the the number of active views i need to delete all views named like XXX that are older than one day. Users currently can maintain the session for one day.

Can I have some C# code that would delete from the database
1.All views[table objects] named like vw_nsXXX%
2.Based on create date older than "today"

or

Any alternative way that allows me to remove older views from the database dynamically.

To delete a single view I have used the code shown below, but this is simply used when users requests different data in the same session. (when the existing single view must be deleted first before creating a new one with same session ID)

Help.



cnDBAAA.Open();
            try
            {
                SQLString = "if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[" + SqlView + "]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[" + SqlView + "]";
                SqlCommand cmd = new SqlCommand(SQLString, cnDBAAA);
                cmd.CommandText = SQLString;
                SqlDataReader sqlr;
                sqlr = cmd.ExecuteReader();
                sqlr.Read();
            }
            catch (Exception ex)
            {
                myGlobal.ShowMessage(radMsgBox, "Stop", ex.Message);
            }
            finally
            {
                cnDBAAA.Close();
            }

Open in new window

Delete-View-Image.jpg
Comment
Watch Question
SQL Server Architect
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE