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

w3digitalAsked:
Who is Participating?
 
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
 
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
 
w3digitalAuthor Commented:
Great, thanks. Points me firmly in the right direction.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.