Clean data from database

Question

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?

Thank but i want another way to do it. Clean the data from context. — Faisal Silitonga, Sep 12 '13 at 04:13

Damyan Bogoev · Answer 1 · 2013-09-12T09:55:15.890

(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'

it is applicable for SQL Server, I did not notice you are using SQL CE — Damyan Bogoev, Sep 12 '13 at 09:54

score 0 · Answer 2 · edited Sep 12 '13 at 04:18

Create scripts from database for drop and create.
Create stored procedure for the same with the code u have taken.
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

Clean data from database

2 Answers2