Avatar of JenebyM
JenebyM
 asked on

Deleting Table Views

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
C#Microsoft SQL Server 2005SQL

Avatar of undefined
Last Comment
JenebyM

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
DBAduck - Ben Miller

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Anthony Perkins

>>A hundred views can be created in this way on a given day.<<
Don't take this the wrong way, but this seems a crazy approach.  Are you prepared to consider alternatives?
JenebyM

ASKER
Thank You,

I have had to implement using 3 steps. I needed to delete only views starting with the specific name vw_nsXXX shown on the jpg image and it was easy to adopt the code to do this by simply adding:

and [name] like 'vw_ns%'

Good solution

J
JenebyM

ASKER
acperkins:

<>

I would be delighted to hear from anyone how the code I sent can be modified to create a unique dataset for each crystal report requested  without creating multiple views.

I saw your comments after I have implemented  dbaduck's solution so I have posted a new question Titled
"Creating a single unique data source for crystal reports writer".

Anything to improve our approach is certainly welcome.

J

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Anthony Perkins

Aside from the maintenance nightmare, here is the main problem I have with your approach:
You need to grant users CREATE VIEW and DELETE VIEW permissions.  This in most shops is verboten.

I would approach it differently.  I would create a Stored Procedure that would generate the resultset based on the Session ID.  Without seeing your particular query and more details it is difficult to be more precise.

JenebyM

ASKER
acperkins:

I have followed up you comment by giving the code details in a new question titled:

"Creating a single unique data source for crystal reports writer".

Have you been able to review the code supplied in that new question. Have a look an let me know if there is additional info you need.

I first looked at the stored procedure approach, which supplied the different variables needed by the user based on session ID but I had trouble getting the crystal report to use a stored procedure as a data source and hence going down the track of views.

If you can see the followup question details and code please let me know.
DBAduck - Ben Miller

I certainly did not mean to advocate the use of this for having the users on the web or otherwise execute this.  I was merely trying to give the author they way he could put it in a job that runs under the correct credentials to keep things cleaned up.

I think that you all are going down the right road in finding ways to eliminate this dependency for the application.  I had hoped I was clear in answering the question and giving the information that helped the author to solve his problem.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
JenebyM

ASKER
dbaduck:

Please lookup the "thread" in the supplementary question I asked. i.e about the alternative to NOT using the solution you suggested. You will see that no other solution has been given based on the supplementary information i have provided.

The problem I have relates to the way crystal binds data for users when the same report is requested simultaneously and refreshed/page changed in by users after data-binding. I have found a problem that EE has not replicated and therefore no alternate solution.

My approach has been to implement your code as a daily cleanup task automatically performed by the system behind the scenes.

My approach is to use this as an effective way of controlling the number of views created until we can pinpoint why crystal reports databinding seems to work well for others using stored procedures and datasets for multiple web users simultaneously requesting the same report but behaves differently for us.

Until then we are using the solution provided.