Clean data from database

1.2k views Asked by At

I using ORM telerik open access in my project. I can use it to create and add data to my database.

Now i want to clean all data from my database. I can do it by delete data from each table but it will take long code. I have google how to clean it using DBContext and found nothing. There another way to clean database but not looping to call delete function for each table in my DB?

2

There are 2 answers

1
Damyan Bogoev On

(SQL SERVER only) You could use the following sql script to clean up the data in your database:

/* 1. Disable all constraints and triggers*/ exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/* 2. Delete all of table data */ exec sp_MSforeachtable 'DELETE ?'

/* 3. Enable all constraints and triggers */ exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/* 4. Reset tables identity */ exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

0
Sasidharan On
  1. Create scripts from database for drop and create.
  2. Create stored procedure for the same with the code u have taken.
  3. Execute the stored procedure from code behind..

Drop and create query will be something like this..This is a sample..

/* Object: Table [dbo].[EmployeeList] Script Date: 09/12/2013 09:35:01 */

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeList]') AND type in (N'U'))
DROP TABLE [dbo].[EmployeeList]
GO

USE [dbname]
GO

/****** Object:  Table [dbo].[EmployeeList]    Script Date: 09/12/2013 09:35:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[EmployeeList](
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Category] [nvarchar](50) NOT NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
    [alphanumeric] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO