Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

How do I script SQL Server database and table generation using C#

Simple question I know but I'm reasonably new to C# and used to use ASP 3 and manually setup SQL Server via Enterprise manager.

I am building a new super admin interface using C# and am creating reusable 'modules' (news, comments, traders etc) that I wish to be able to auto install via clicking checkboxes.

So, I have a standard user in SQL Server (lets say it's called "DBUser") and I wish to use a script to generate tables depending on the super admin's administration needs and choices.

So how would I script the following SQL Query in C# to be owned by "DBUser"?

Cheers.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dnc_news]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dnc_news]
GO

CREATE TABLE [dbo].[dnc_news] (
      [news_id] [int] IDENTITY (1, 1) NOT NULL ,
      [news_title] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [news_intro] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [news_article] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [news_image] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [news_date] [datetime] NOT NULL ,
      [down_filename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [down_size] [int] NULL ,
      [down_type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

0
w3digital
Asked:
w3digital
  • 2
1 Solution
 
b1xml2Commented:
sql file
====
if exists (select * from TestDB.dbo.sysobjects where id = object_id(N'[TestDB].[dbo].[dnc_news]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TestDB].[dbo].[dnc_news]
GO

CREATE TABLE [TestDB].[dbo].[dnc_news] (
     [news_id] [int] IDENTITY (1, 1) NOT NULL ,
     [news_title] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [news_intro] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [news_article] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [news_image] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [news_date] [datetime] NOT NULL ,
     [down_filename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [down_size] [int] NULL ,
     [down_type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

[c#]
StreamReader reader = new StreamReader(@"c:\table.sql");
string value = reader.ReadToEnd();
reader.Close();
SQLDMO.SQLServer server = new SQLDMO.SQLServer();
server.LoginSecure = false;
server.Connect("<server_name>","<user>","<password>");
/*
      server.LoginSecure = true;
      server.Connect("..",null,null);
*/
server.ExecuteImmediate(value,SQLDMO_EXEC_TYPE.SQLDMOExec_Default,null);
server.Close();

add a reference to the Microsoft SQLDMO Library Version 8.0
0
 
b1xml2Commented:
you will notice that the table name is explicit, including the database name.

just a note
======
amending the sql statement

if exists (select * from TestDB.dbo.sysobjects where id = object_id(N'[TestDB].[dbo].[dnc_news]'))
drop table [TestDB].[dbo].[dnc_news]
GO

CREATE TABLE [TestDB].[dbo].[dnc_news] (
     [news_id] [int] IDENTITY (1, 1) NOT NULL ,
     [news_title] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [news_intro] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [news_article] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [news_image] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [news_date] [datetime] NOT NULL ,
     [down_filename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [down_size] [int] NULL ,
     [down_type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

will allow the table to be dropped properly....

0
 
w3digitalAuthor Commented:
Great, thanks. Points me firmly in the right direction.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now